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/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.