Home  >  Blog  >   Snowflake  > 

How to Avoid Gaps in Data in Snowflake

While storing the data, we may get some gaps in data. But, we should not have the gaps in the report data. So, by using Common Table Expressions(CTE), we create the series of time/date values that do not have any gaps. We will study to create a time/date values series in this blog.

Rating: 4.5
  
 
259
  1. Share:
Snowflake Articles

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;

 MindMajix YouTube Channel

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. 

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
Snowflake TrainingNov 26 to Dec 11
Snowflake TrainingNov 29 to Dec 14
Snowflake TrainingDec 03 to Dec 18
Snowflake TrainingDec 06 to Dec 21
Last updated: 22 November 2022
About Author
SaiKumar Kalla

Kalla Saikumar is a technology expert and is currently working as a content associate at MindMajix. Write articles on multiple platforms such as ServiceNow, Business Analysis, Performance Testing, Mulesoft, Oracle Exadata, Azure, and other courses. And you can join him on LinkedIn.