Best method to get todays data through a view snowflake

user_17867413 0
Viewed 25

My warehouse details:

warehouse - XS
reading data external tables from s3 into snowflake
Refresh structure: SNS

I have the S3 folder structure as below

S3://eveningdtaa/2022-06-07/files -- contains parquet format
S3://eveningdtaa/2022-06-08/files -- contains parquet format
S3://eveningdtaa/2022-06-09/files -- contains parquet format

I am using external tables to read data from snowflake tables.

So tables- Has historical information
   views - Has daily data

My view defination is a below:

create view result_view as (
select * from table1 where date_part=(select max(date_part) from table 1)
)

My question our daily views are running slow and it has only 70k rows. Is there a way to rewrite my view to pick only the latest data instead of max of date? or able to run this view faster through some indexes?

Thanks, Xi

snowflake-schema snowflake-cloud-data-platform
1 Answers

It may be rewritten using QUALIFY:

create view result_view 
as 
select * 
from table1 
qualify date_part=max(date_part) over();

It is also worth adding partition on date: Partitioning Parameters

user_50708790
Related