Implements Slowly Changing Dimensions Type 2 in DuckDB using MERGE INTO statements.
npx skills add https://github.com/mathisdrn/orca --skill duckdb-scd-mathisdrn
Please help me install this Agent Skill. Skill: duckdb-scd Source: https://github.com/mathisdrn/orca/tree/main/.agents/skills/duckdb-scd Command: npx skills add https://github.com/mathisdrn/orca --skill duckdb-scd-mathisdrn
High-intent search queries and answers about installing and using this skill.
Use the MERGE INTO statement (available in DuckDB v1.4.0+) to expire changed current rows, soft-delete missing records, and insert new records. Then run a follow-up INSERT to write the new current versions of the rows just expired.
Design the table with a surrogate key, business key, begin_date, end_date, and is_current flag. Each change creates a new row version while the old version is expired by setting end_date and is_current = false.
Yes, DuckDB supports MERGE INTO starting from version v1.4.0. It supports WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, and WHEN NOT MATCHED BY TARGET clauses, enabling full SCD Type 2 logic in a single statement.
The MERGE statement expires old versions and inserts brand-new records, but it cannot also insert the replacement versions of expired rows in the same pass. A second INSERT joins the source against newly expired rows to write their new current versions.
Yes, the pattern works in dbt models targeting DuckDB, since it is plain SQL. Wrap the MERGE and INSERT in a single transaction to keep the dimension consistent if either step fails.
databricks-solutionsDeduplicate Silver table business keys by timestamp before Delta MERGE.
PostHogBuild reusable data models as PostHog views or external dbt projects.
KemetraRuns live read-only validation checks against materialized retail tables in Postgres and maps findings to fixes.
SignalPilot-LabsManage dbt snapshots for SCD Type 2 with strategy selection and validation.
