How to Avoid Gaps in Data in Snowflake

Snowflake - Avoid the Gaps in Data

In Snowflake, we must group the data by time. However, we don’t require the gaps in our report data. We should create the series of time/date values that do not have gaps through the common table expression:

set start_date = ‘2022-06-02’ set end_date = ‘2022-06-30’ with cte_data (data_rec) as ( select to_date($start_date) union all select to_date(dateadd(day, 1, date_rec)) from cte_date1 where date_rec < $end_date ) select date_rec1 from cte_date1 date_rec1 2022-06-02 2022-06-03 2022-06-04 2022-06-05 2022-06-06 …. 2022-06-30

We left to join our data series against the gapless series. Creating the count of the sessions for every day:

set start_date = ‘2022-06-02’ set end_date = ‘2022-06-30’ with cte_date (date_rec) as ( select to_date($start_date) union all select to_date(dateadd(day, 1, date_rec)) from cte_date1 where date_rec < $end_date ) select cte_date.date_rec, count(s.id) as session_ct from cte_date left outer join sessions s on to_date(s. start_date) = cte_date.date_rec group by date_rec;

Conclusion

By creating a series of time or date values through common table expressions, we can avoid gaps in data. I hope this blog offers sufficient information about avoiding gaps.

Snowflake Related Articles

▶  Snowflake vs Redshift
▶  Snowflake vs BigQuery
▶  Snowflake vs Databricks
▶  Snowflake vs Azure
▶  Snowflake vs Hadoop

▶  Snowflake Time Travel

If you have any queries, let us know by commenting below.