Reports

This is a good example of range/non-equi joins. Here Join checks if a value falls within some range of values. Snowflake has a different join type to deal with non-equi, or range joins

reference docs - https://docs.snowflake.com/en/sql-reference/constructs/asof-join

select distinct
    t1.id,
    t1.segment_id,
    t2.device_id,
    case when t2.id_type = 'abc' then 1 else 0 end as device_type,
    any_value(date_trunc('minute', t2.timestamp)) as drive_time,
    any_value(t2.latitude) as latitude,
    any_value(t2.longitude) as longitude,
    any_value(t2.ip_address) as ip_address
from 
    small_table t1 
ASOF JOIN  big_table t2 
MATCH_CONDITION(t2.date_col >= t1.date_col)
ON t2.high_cardinality_col_tried_for_clustering = t1.equivalent_col
group by 
    all;

More background docs for ASOF Join

https://blog.greybeam.ai/snowflake-asof-join/

https://select.dev/posts/snowflake-range-join-optimization

79341010