In this blog post, I will show you what you need to pay special attention to when switching from Teradata to Snowflake.
We will analyze the impact of running DML statements (e.g., UPDATE or DELETE) in Snowflake and contrast it with Teradata’s Row Storage and Column Storage.
What happens when Teradata Rows are deleted from a table that is not Column-Partitioned?
u003cbru003eWhen deleting, the rows in the data blocks are released. Teradata uses an array of pointers in each data block that points to each row’s physical position within the data block.u003cbru003eu003cbru003eData blocks are merged if too few rows remain in a data block.u003cbru003eu003cbru003eAn UPDATE statement causes the data block to be assembled in memory. u003cbru003eDepending on the effect of the update on the used space within a data block, it is split into several blocks or merged.
What happens when Teradata Rows are deleted from a table that is Column-Partitioned?
If all rows or an entire row partition of a table is deleted, they are physically deleted. u003cbru003eu003cbru003eOtherwise, the rows to be deleted are only marked with a delete flag but continue to occupy space, which is necessary to keep the column containers synchronous.u003cbru003eu003cbru003eThe old Row-Based Engine comes into play during the UPDATE:u003cbru003eIt selects the needed rows and assembles the column containers into rows. The rows to be changed will be marked with a delete flag but not deleted. The changed rows are transformed into column format and saved.u003cbru003e
What happens when data is deleted or modified in Snowflake?
Snowflake stores data in micro partitions between 5 and 500 megabytes.u003cbru003e u003cbru003eEvery change (UPDATE, INSERT, or DELETE) to a micro partition means that Snowflake must replace the entire micro partition.u003cbru003eu003cbru003eMaking changes to an existing micro partition is not possible because Amazon S3 does not support this.u003cbru003eu003cbru003eMicro partitions are created when loading an empty table. If data is added, new micro partitions are created.u003cbru003eu003cbru003eIf a change through an UPDATE or DELETE statement is necessary, Snowflake deletes the existing micro partition and replaces it with a new one.u003cbru003eu003cbru003eI assume that micro partitions are not deleted immediately but remain available for the Time Travel feature for a while.
How can the performance of both systems be assessed?
I am currently missing more details about the implementation in Snowflake, but I will stay on it and update you as soon as I know more!
Related Services
🏗️ Planning a Data Platform Migration?
Architecture-first approach: we design before a single line of code is written. Zero data loss across every migration delivered.
Our Migration Services →
When an update is performed in Snowflake, the file belonging to this row is copied to a new one with the changes. Now you have 2 files. In the FoundationDB database, each table has a list of current files that belong to that table. During this update operation, the database is repointed to the new file. When a select operation is executed, this FoundationDB is checked for a current list of files, once that is retreived, the statistics on each file are applied to the query and only the files needed are read. Hope this helps.
Thank you very much for the explanation. Do you know maybe how joins are physically done in Snowflake? In Teradata, we can get information about all join methods, but I can’t find anything about Snowflake…