How to pass a range of date in where clause in snowflake while writing a query

Question

How to pass a range of date in where clause in snowflake while writing a query and avoiding duplicates also for the next run. I have below query and i have to fetch 15-days of data each day till previous day. The data would be passed to my final table from the given below query. My final data should not be having duplicate rows for a given all columns.

Data looks like below:

{ "location": "xyz", "metrics": [ { "name": "traffic_in", "data": [ { "group": { "start": "2020-07-05", "type": "date" }, "index": 0, "next_level": [ {"index": 0, "validity": "complete", "value": 1, "group": { "finish": "00:15", "start": "00:00", "type": "time" } } ] } ], } ], }

Below is the query which needs to be modified as per requirement:

create or replace TABLE TMP_RN_TC as
(select * from(
               select distinct
                   replace(D_NEXT : location , '"' , '')as  rn_loc_id,
                   mtr.value:name::VARCHAR as  metrics_name,
                   dta.value:group.start::DATE as metrics_event_date,
                   dta.value:index::numeric as metrics_date_index,
                   nxt.value:validity::VARCHAR as metrics_data_validity,
                   nxt.value:value::numeric as metrics_data_value,
                   nxt.value:group.start::time as metrics_data_start_tms,
                   nxt.value:index::numeric as metrics_time_index
           from STG_RN_TC stg,
           lateral flatten(input => stg.D_NEXT:metrics) mtr,
           lateral flatten(input => mtr.value:data) dta,
           lateral flatten(input => dta.value:next_level)nxt)
 ) ;

hawkhunt hawkhunt · Accepted Answer · 2020-07-17T16:49:29

The usual way is to stage your data in a staging table with additional timestamp column and later use that timestamp column to load data into your final table. There are other advance ways available for change data capture and table row versioning which you can explore and implement as per your use case here is the link

https://docs.snowflake.com/en/user-guide/streams.html

How to pass a range of date in where clause in snowflake while writing a query

1 Answers