Optimisation of Fact Read & Write Snowflake

user_4936529 0
Viewed 41

All,

I am looking at some tips with regards to optimizing a ELT on a Snowflake Fact table with approx. 15 billion rows.

We get approximately 30,000 rows every 35 mins like the one below, we always will get 3 Account Dimension Key values i.e. Sales, Cogs & MKT.

Finance_Key Date_Key Department_Group_Key Scenario_Key Account_Key Value IsCurrent
001 2019-01-01 001 0012 SALES_001 100 Y
001 2019-01-01 001 0012 COGS_001 300 Y
001 2019-01-01 001 0012 MKT_001 200 Y

This is then PIVOTED based on Finance Key and Date Key and loaded into another table for reporting, like the one below

Finance_Key Date_Key Department_Group_Key Scenario_Key SALES COGS MKT IsCurrent
001 2019-01-01 001 0012 100 300 200 Y

At times there is an adjustment made and for 1 Account key.

Finance_Key Date_Key Department_Group_Key Scenario_Key Account_Key Value
001 2019-01-01 001 0012 SALES_001 50

Hence we have to do this

Finance_Key Date_Key Department_Group_Key Scenario_Key Account_Key Value IsCurrent
001 2019-01-01 001 0012 SALES_001 100 X
001 2019-01-01 001 0012 COGS_001 300 Y
001 2019-01-01 001 0012 MKT_001 200 Y
001 2019-01-01 001 0012 SALES_001 50 Y

And the resulting value should be

Finance_Key Date_Key Department_Group_Key Scenario_Key SALES COGS MKT
001 2019-01-01 001 0012 50 300 200

However my question is how do I go about optimizing the query to scan and update the Pivoted table for approx. 15 billion rows in Snowflake.

This is more of a optimization the read and write .

Any pointers

Thanks

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

So a longer form answer:

I am going to assume the Date_Key values being very is the past, is just a feature of the example date, because if you have 15B rows, and every 35 minutes you have ~10K (30K /3) updates to apply, and they span the whole date range of your data, then there is very little you can do to optimize it. Snowflake Query Acceleration Service might help with the IO.

But the primary way to improve total processing time, is process less.

For example in my old job we had IoT data, that could have messages up to two weeks old. And we duplicated all messages on load (which is effectively a similar process), as part of our pipeline error handling. We found that handling batches with a min date of -2 week against of full message tables (that also had billions of rows) used most of the time, reading/writing the tables. By altering the ingestion to sideline message older than a couple of hours, and deferring their processing until "midnight" we could get the processing of all the timely points in the batch done in under 5 minutes (we did a large amount of other processing, in that interval) for every batch, and we could turn the warehouse cluster size down out of core data hours, and use the saved credits to run a bigger instance at the midnight "catchup" to bring all the days worth of sidelined data on board. This eventually consistent approach worked very well for our application.

So if your destination table is well clustered, so the read/writes is just a fraction of the data then this should be less of a problem for you. But by the nature of asking the question I assume this is not the case.

So if the tables natural clustering is unaligned with the loading data, is that because the destination table needs to be a different shape for the critical read the tables is handling, at which point which is more cost/time sensitive. Another option is to have the destination table clustered by the date (again assuming the fresh data is a small window of time) and have a materialize view/table on top of that table, with a different sort, so that the rewrite is being done for you by Snowflake, now this in not a free-lunch. But it might allow faster upsert times, and faster usage performance. Assuming both a time sensitive.

user_439920
Related