UPDATE millions of rows, or DELETE/INSERT?

user_10331351 0
Viewed 2238

Sorry for the longish description... but here we go...

We have a fact table somewhat flattened with a few properties that you might have put in a dimension in a more "classic" data warehouse. I expect to have billions of rows in that table.

We want to enrich these properties with some cleansing/grouping that would not change often, but would still do from time to time.

We are thinking of keeping this initial fact table as the "master" that we never update or delete from, and making an "extended fact" table copy of it where we just add the new derived properties.

The process of generating these extended property values requires mapping to some fort of lookup table, from which we get several possibilities for each row, and then select the best one (one per initial row). This is likely to be processor intensive.

QUESTION (at last!):

Imagine my lookup table is modified and I want to re-assess the extended properties for only a subset of my initial fact table.

I would end up with a few million rows I want to MODIFY in the target extended fact table.

What would be the best way to achieve this update? (updating a couple of million rows within a couple of billion rows table)

  1. Should I write an UPDATE statement with a join?

  2. Would it be better to DELETE this million rows and INSERT the new ones?

  3. Any other way, like creating a new extended fact table with only the appropriate INSERTs?

Thanks

Eric

PS: I come from a SQL Server background where DELETE can be slow

PPS: I still love SQL Server too! :-)

snowflake-cloud-data-platform
1 Answers

Write performance for Snowflake vs. traditional RDBS behaves quite differently. All your tables persist in S3, and S3 does not let you rewrite only select bytes of an existing object; the entire file object must be uploaded and replaced. So, while in say SQL server where data and indexes are modified in place, creating new pages as necessary, an UPDATE/DELETE in snowflake is a full sequential scan on the table file, creating an immutable copy of the original with applicable rows filtered out (deleted) or modified (update), which then replaces the file just scanned.

So, whether updating 1 row, or 1M rows, at minimum the entirety of the micro-partitions that the modified data exists in will have to be rewritten.

I would take a look at the MERGE command, which allows you to insert, update, and delete all in one command (effectively applying the differential from table A into table B. Among other things, it should keep your Time-Travel costs down vs constantly wiping and rewriting tables. Another consideration is that since snowflake is column oriented, a column update in theory should only require operations on the S3 files for that column, whereas an insert/delete would replace all S3 files for all columns, which would lower performance.

user_136752340
Related