2 snowflake tables, same data ,size different

Question

I have 2 snowflake tables, 1 stage table(ABC_Stg) and 1 regular table (ABC) with additional 2 columns , src_updt_time and md5 calculation values of remaining columns, MD5_Cal, so only 2 columns data extra compare to stage tables. However I see very huge size difference between these 2 tables for same rows.I do insert from stage to regular table and add systimestamp and md5 calculation value in my insert statment.

if stage table size is 300 KB for 2500 rows, Regular table has 1.5 MB as a size

Am I doing something wrong here which causes huge spike into regular table size ?

Thank you

There is a good chance that not only does your 2 additional columns add a decent amount of data to that table, but that they also change the compression of the micro-partitions underneath. — Mike Walton
I think you are right , I believe its MD5 value based on number of columns causes the difference in table size — user13766383

MMV MMV · Accepted Answer · 2020-08-03T12:24:33

Can't really answer this not seeing the tables. I would check the following:

the data type (and the size) of the columns. Because "same data" doesn't mean you're not storing numbers with higher precision for example and it takes more space.
where the space goes, because the regular table also has time travel and failsafe, so if any updates were done the difference can be even up to several TB in storage (ie if you have a table with 10TB of data and then truncate it to 5 rows the space needed for time travel and failsafe will still be 10TB until it expires)

2 snowflake tables, same data ,size different

1 Answers