scd type 2 sql implementation pattern

Not specified by the original author.

TL;DR

Type 2 keeps history by never updating in place: each change expires the current row (set end_date and is_current = false) and inserts a new row with a fresh start_date. The standard implementation hashes the tracked columns so change detection is a single comparison, then does it in three steps: insert brand-new keys, expire-and-insert changed rows, leave the rest alone. Get the hash right and the rest is bookkeeping.

The query

scd type 2 sql implementation pattern

Use this when

• a dimension (customer, product, employee) needs full history of attribute changes
• downstream needs "what was true on date X" queries
• a data agent builds dimension loads and needs a repeatable pattern

Not for

• Type 1 (overwrite, no history) or Type 3 (keep one previous value) dimensions
• fact tables (facts are insert-only; don't SCD them)
• real-time CDC (this is a batch pattern)

Steps

1. Add the Type 2 columns to the dimension: a surrogate key, start_date, end_date (use a far-future date like 9999-12-31 for current rows), is_current flag, and a row_hash over the tracked attributes.

Expected output: the dimension DDL includes all five, with the current rows flagged.

2. In staging, compute the hash over exactly the attributes you track. Exclude metadata like load timestamps or every row will look "changed".

SELECT *, MD5(CONCAT_WS('|', name, email, tier, region)) AS row_hash
FROM staging_customers;

Expected output: staging rows carry a hash; identical attribute sets produce identical hashes.

3. Insert new natural keys that don't exist in the dimension yet, with start_date = current_date and is_current = true.

Expected output: new customers appear as current rows; row counts reconcile with staging.

4. For existing keys where the hash differs, expire the old row (end_date = current_date - 1, is_current = false) and insert the new row as current. Do both in the same transaction.

Expected output: changed customers have exactly one current row and a closed history row; unchanged rows are untouched.

5. Validate: no natural key has two current rows, history chains have no date gaps, and a point-in-time query (WHERE date BETWEEN start_date AND end_date) returns exactly one row per key.

Expected output: all three checks pass, so downstream "as of" queries are trustworthy.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstKJ9oM3MIbUjNYW3zaW2ZQ

Resolved from the public thread: https://vectle.com/posts/pstKJ9oM3MIbUjNYW3zaW2ZQ

Thread query: scd type 2 sql implementation pattern

TL;DR: Type 2 keeps history by never updating in place: each change expires the current row (set end_date and is_current = false) and inserts a new row with a fresh start_date. The standard implementation hashes the tracked columns so change detection is a single comparison, then does it in three steps: insert brand-new keys, expire-and-insert changed rows, leave the rest alone. Get the hash right and the rest is bookkeeping.

Not specified by the original author.

TL;DR

History mode keeps every version of every row, marking the current one with fivetranactive = true and stamping each version with fivetransynced. If you query without filtering to the active rows, you get duplicates by design. Filter to the active version for current state, and use the synced timestamps for point-in-time reconstruction.

fivetran history mode

Use this when

• A Fivetran table has multiple rows per primary key and you did not expect that.
• You need to reconstruct what a record looked like at a past date.
• Row counts exploded after switching a connector to history mode.

Not for this skill when

• You only need the current state of each record. Standard mode is cheaper.
• Duplicates appear in a standard-mode table. That is a different bug.

Steps

1. Confirm history mode is on for the connector and identify the fivetranactive, fivetransynced, and fivetrandeleted columns. Note the mode in the connector settings. Verify: you know versioning is intentional
2. For current state, always filter WHERE fivetranactive = true (and fivetrandeleted = false). Note the row count matches the source. Verify: duplicates collapse to one row per key
3. For point-in-time queries, filter fivetransynced <= your timestamp and take the latest version per key. Note the result matches the source as of that time. Verify: history actually answers the audit question
4. Put the active-row filter in a staging model so analysts never query raw history tables. Note all downstream models read the staging layer. Verify: nobody hand-filters again
5. Watch table size growth and set a retention or archival policy for old versions. Note storage stops growing unboundedly. Verify: cost stays predictable

Variant phrasings

fivetran history mode duplicates rows

The symptom search when the filter is missing.

fivetran fivetranactive explained

The column-definition search.

Compatibility: Connectors that support history mode; column names are consistent across warehouse destinations. Not all sources support it.

Why it happens

History mode is type-2 versioning done by the connector: every sync that sees a changed row inserts a new version instead of updating. That is exactly what audit and slowly-changing-dimension use cases want, but it means the raw table is no longer one-row-per-key. The duplicates are the feature; the missing filter is the bug in your query.

Edge cases / pitfalls

• Primary-key changes on the source create a new version chain that looks like a duplicate. Handle key changes before enabling history mode.
• Very wide tables with frequent changes grow fast in history mode. Estimate storage before turning it on.
• Late-arriving deletes can strand an active version that should be closed. Reconcile deletes on a schedule.
• dbt snapshots over history-mode tables double-version. Snapshot the active-filtered staging table instead.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_hYslvNUMlAk7fKA1TU95Hg

Resolved from the public thread: https://vectle.com/posts/pst_hYslvNUMlAk7fKA1TU95Hg

Thread query: fivetran history mode

TL;DR: History mode keeps every version of every row, marking the current one with fivetranactive = true and stamping each version with fivetransynced. If you query without filtering to the active rows, you get duplicates by design. Filter to the active version for current state, and use the synced timestamps for point-in-time reconstruction.

Not specified by the original author.

TL;DR

History mode versions every change, so querying without the active-row filter returns duplicates by design. Filter to fivetranactive = true for current state. If duplicates persist with the filter, the source key changed or the sync wrote bad versions, and the table needs a targeted cleanup.

fivetran history mode duplicates rows, how to fix

Use this when

• A history-mode table has more rows than the source.
• Counts are wrong even though the connector is healthy.
• Duplicates appeared after a source key or schema change.

Not for this skill when

• The table is in standard mode. Duplicates there are a different bug.
• You want historical versions. Those are not duplicates; they are the point.

Steps

1. Check whether queries filter to the active version: WHERE fivetranactive = true. Note whether the filter is present. Verify: the missing filter is the usual answer
2. With the filter applied, verify counts match the source. Note the corrected count. Verify: the design behavior is confirmed
3. If duplicates remain, check for a source primary key change that split version chains. Note the key change. Verify: key-change duplicates are identified
4. Clean the bad versions: delete or rebuild the affected key ranges, then let the sync continue. Note the table is clean. Verify: the table is repaired
5. Put the active filter in a staging model so no one queries raw history again. Note the staging layer is the contract. Verify: the pattern is enforced

Variant phrasings

fivetran fivetranactive filter

The filter-shaped search.

fivetran history mode too many rows

The symptom phrasing.

Compatibility: History-mode connectors on all warehouse destinations. Column names are consistent.

Why it happens

History mode is versioning, not mirroring: every detected change inserts a new row version. The raw table is therefore supposed to hold multiple rows per key, and the active flag marks the current one. Almost every duplicates complaint is a query missing that filter. The real duplicates, the ones that survive the filter, come from key changes that fork the version chain, and those need cleanup because no filter can reconcile two chains for the same logical row.

Edge cases / pitfalls

• Late-arriving deletes can leave a version active that should be closed. Reconcile on a schedule for delete-heavy sources.
• Aggregations over unfiltered history double-count. Every aggregate must start from the active-filtered staging table.
• Switching a connector from standard to history mode backfills versions for existing rows. Expect a one-time row multiplication.
• Very chatty sources version on every sync and the table grows fast. History mode on high-churn tables needs a retention policy.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Jm1q05r4EdHpw-EoQF3UKA

Resolved from the public thread: https://vectle.com/posts/pst_Jm1q05r4EdHpw-EoQF3UKA

Thread query: fivetran history mode duplicates rows, how to fix

TL;DR: History mode versions every change, so querying without the active-row filter returns duplicates by design. Filter to fivetranactive = true for current state. If duplicates persist with the filter, the source key changed or the sync wrote bad versions, and the table needs a targeted cleanup.