I need to track the history of our "Customer" attributes, specifically their subscription tier and home address. I’m planning to use SCD Type 2 with Start_Date, End_Date, and a Current_Flag. However, our updates are coming in via a high-frequency stream. Is it better to handle this via standard SQL MERGE statements in Snowflake, or should I be looking at "Streams and Tasks" to automate the versioning of these records?
3 answers
For high-frequency updates in Snowflake, using "Streams and Tasks" combined with the MERGE command is the most robust approach. A Snowflake "Stream" will track the DML changes (inserts/updates) on your landing table. You can then schedule a "Task" to run every few minutes that executes a MERGE statement. The logic should first "expire" the old record by setting the End_Date to current_timestamp and Current_Flag to 'N', and then "insert" the new record with the updated attributes and a null End_Date. This pattern is highly efficient because Snowflake only processes the "change data" rather than scanning the entire table, which saves on credit consumption and keeps your dimension tables up to date in near real-time.
Have you considered the storage overhead of SCD Type 2 if your customer attributes change very frequently, like every few days?
We use the "dbt-snapshot" feature for all our SCD Type 2 logic. It handles the dates and flags automatically so we don't have to write complex MERGE scripts.
dbt-snapshots are a game changer! It makes the whole process declarative. We migrated all our manual SQL scripts to dbt last year and haven't looked back.
Steven, that is a concern. We have about 1 million customers, and if 10% change their status weekly, the table will grow quite fast. However, business requirements dictate that we must be able to report on "What the customer's status was at the time of the sale," so we can't just use Type 1. Is there a middle ground? Maybe I should only track "Critical" attributes in Type 2 and move the "Volatile" ones into a separate "Satellite" table to keep the main dimension table lean? I'm trying to optimize for query speed while still meeting the auditor's historical requirements.