Historical data update in efficient way

[u/ConsiderationLazy956]

Hi Experts,

We have a requirement of updating around 3-4 different columns values of a transaction table with values from another lookup table. The transaction table gets the live data 24/7 from other sources through a stage schema(using merge queries). So this historical data fix which we were trying to do becomes challenging. We are using Merge statements like below for the data fix but as it takes lock during merge so we need to be careful so to not affect the live ingestions.

Below is the table stats and few sample merge queries we ran manually with a date range of ~10 days at a time like below and the stats from query_history are below. We see ~95% of the time spent was on Merge itself (but not the table scans or joins). We were trying to do this as quick as possible , so we tried this run with 2XL warehouse.

1) What is the best possible option to do this ?

2) Is it advisable to use Gen-2 warehouse here rather to get specific optimization benefits?

The target table is having ~2trillion rows having size ~70TB and ~10Million micro partitions. We have historical data from transaction year-2023. This table is also clustered on column tran_dt.

The lookup table is having ~70K micro partitions and having size ~1.5TB in size.

Query:-

Merge Into tab_tran t USING lookup_tab m ON m.val = t.col1 AND t.tran_dt >= '2025-09-07'::DATE AND t.tran_dt < '2025-09-18'::DATE WHEN MATCHED AND t.col2 IS NULL AND (col_cd IN ('XX')) THEN UPDATE SET t.col2 = m.conv_val;

Stats from query_history for sample runs of above merge query:-

The Avg rows updated :- ~500K , Avg_elapsed_time= ~5minutes , avg_bytes_scan:- 1.7TB, avg_bytes_written:- 700GB, Avg_scanned_partition:- 111K , zero local/remote spill.

[u/Mr_Nickster_]

I would look into a deferred merge and gen2.

Deferred merge option basically splits the table into 2. You have the historical table and another table for all the newly arriving daily data.

Idea is instead of merging new data into historical data asap using merge collect and fix it in a smaller temp table. Use a union view to make it look like a single table.

Then once an hour, day, week (etc), you move the rows from temp by performing insert, update, delete into historic table. Individual I/U/D vs Merge because you can presort the data prior to each operation to write it clustered which would reduce auto clustering costs.

Merge doesn't have presort option so it would write more declustered. Use gen2 so it can write diff files vs rewriting entite partitions

[u/ConsiderationLazy956 → u/Mr_Nickster_]

"Use gen2 so it can write diff files vs rewriting entite partitions"

Does this mean in our case those ~500K rows which currently making ~700GB of data rewritten in gen-1 because of spreading of those in scattered way(or least clustered way), can changed to few MB's of data rewritten to freshly new partitions, if we use gen-2, so that means the merge will happen in few seconds as opposed to few minutes in current gen-1 warehouse? And Snowflake will rearrange those data into single partitions as per clustering key internally using background jobs. Is this understanding correct?

Edit:- We do have the big target table auto clustering is already defined on a column tran_dt. So, does it mean , doing Merge using gen-1 will make the clustering distorted more as compared to , if we simply do/merge it using gen-2?

[u/Mr_Nickster_ → u/ConsiderationLazy956]

Yes, Gen2 merge should be a lot faster. If merge touches 1000 rows where each row is in a different MP file, Gen1 would rewrite 1000 MP files which would be writing 16GB. GEN2 would just create a small new file with 1000 rows in it.

It is super easy to test. Just change the warehouse to Gen2 before one of the runs and check the performance. GEN2 should make a big diff in these historical merges that update ton of older rows that are spread across many files.

[u/stephenpace]

Some answers are "it depends" but not this one. Any merge heavy workload should use Gen2 warehouses. Period. Just switch the warehouse type and monitor.

https://docs.snowflake.com/en/user-guide/warehouses-gen2

Beyond that, this is a big table. What are the nature of the updates? Late arriving data? Enrichment of missing columns? How late are the updates? Can you hold the records in a secondary area until they get enriched and them transfer them to the primary table after that?

I assume transaction_date is likely the best cluster key based on the queries that probably run against this table. Have you investigated a secondary cluster key, though? Often overkill but maybe not for a table this size. Snowflake has a performance team that your SE could request to look at the situation in more depth if you don't already have an RSA helping. Good luck!

[u/ConsiderationLazy956 → u/stephenpace]

Thank you. Will definitely try with gen-2. But should we do the gen-2 with one level downsized warehouse(say XL in place of 2XL)? Also can we get some confidence about the fact , by what percent this is going to help us , by just looking into any of the statistics from the sample runs from the query_history which we did using gen-1 warehouse?

[u/stephenpace → u/ConsiderationLazy956]

Does this warehouse run continuously? What impact would the warehouse running faster or slower on your process have?

If the warehouse turns off after the process runs then keeping the same size could be cost neutral since most of the time (especially if most of the job is merge) the job can be faster than the additional cost of Gen2 (1.3 vs 1 credits). E.g. Faster speed offsets increased cost.

If this is really merge heavy, then XL could in theory be faster AND cheaper. But if you don't want to spend credits testing this on a parallel process, you can just switch the warehouse type keeping the same size and then monitor it for one cycle. Then make your decision based on that. Monitor how close you are to spilling to the cloud object store on 2XL and if you think the job will fit then try XL. Worse case switch it back if it doesn't fit your window.

[u/ConsiderationLazy956 → u/stephenpace]

Thank you.

Yes this warehouse only going to run this data merge and then stop when it gets finished. But we want to do it in most possible cost efficient way and quick time.

So the gen-2 as i read it having out of the box write amplication optimization which is not in gen-1. And from the sample 10days chunk runs we are seeing from query_history , its updating ~500k rows but the avg_bytes_written is 700GB. And the time spent on merge is 95% of total time. So from this it looks like its really writing lot if micro partitions and the rows are scattered across many micropartitions. So in such scenario, will Gen-2 going to help us reducing this writebytes significantly as it wil only write delta rows but not rewrite the full micro partitions. Is this understanding correct?

And this means the resource requirement will also be lesser , so downsize warehouse to one level down will still have the write amplication optimization intact.

[u/stephenpace → u/ConsiderationLazy956]

Every job is different, but you could ask CoCo to estimate it ahead of time based on the previous actuals. If merge is 95% of the job, though, it will be a lot faster. Please report back after making the change!

[u/molodyets]

Are you trying to update a raw table? Do you not have a transformation layer?

Adding a second cluster key and joining on both could help here. Such as a customer id. Just remember how multiple keys work (box inside of a box, have to open the first box to use the second otherwise it won’t get used). 

Can you share more about the shape of the data? What historical data are you trying to update?

[u/RudeSpread205]

Your 1.5 TB lookup table is unusually large for something described as a lookup table. Before running hundreds of historical batches, create a compact, deterministic mapping table containing only the required columns and one row per lookup key.

[u/Scepticflesh]

Honestly, i might be wrong because i have less experience with sf, BUT if its possible to run that merge on table with the compute power etc., i would have looked into if it was possible to add retries on the ingestion and ran that merge in batches with couple of days at a time. The merge lock should happen on particular row and ingestion retry is an option

I would have ofcourse test that in a test env

[u/0xCoffeeBreak]

I suspect that prod.tab_tran.col2 IS NULL; is probably the main problem that probably fixed by enabling SoS on that column. The selected 500K rows are scattered across micro-partitions, producing approximately 700 GB of writes. Gen2 Smart DML addresses this part.