SCD Type 2 history tables

Most syncs keep the target identical to the source. Compliance, audit and slowly changing dimension (SCD) use cases need the opposite: when a customer's address, a driver's licence status or a location's region changes, the old row must survive next to the new one. write_strategy: scd2 does this in the Delta or Iceberg table itself, without a downstream job.

When to use it

• You need "what did this record look like on a given date" answers: pricing, customer attributes, employee assignments, reference data.
• Your dimension tables have primary keys (SCD2 requires them; keyless tables fall back to merge automatically).
• You accept that history tables grow with every change. Fact and event tables usually should not be SCD2.

Design used here

A HrDb database where every table is a dimension worth versioning, so scd2 is the database-wide default, with two exceptions overridden at the table level: an append-only log table and a large fact table that stays a plain merge. The output is Delta Lake on Azure; the same configuration works unchanged for Iceberg.

agent_version: 1.9.4
state_db_path: C:/dlh/state/dlh_agent_state.duckdb

connection_information:
  connection_type: sql_server
  server_name_or_ip: sql01.corp.local
  server_port: 1433
  database_names: [HrDb]

data_retrieval:
  historical_load: false
  output_format: delta
  direct_cloud_write: true
  storage_base_path: /data/delta_iceberg_tables
  output_path: C:/dlh/output
  parquet_compression: zstd
  # Default for every table in every database below. Tables without a
  # primary key fall back to merge with a warning in the run log.
  write_strategy: scd2
  on_schema_drift_action: migrate_target
  on_schema_drift_action_handling: fail_once
  databases:
    - name: HrDb
      sync_mode: ct
      schema_refresh_mode: 1D
      tables:
        - name: dbo.*
          all_columns: true
          no_pk_strategy: full
        - name: dbo.Employee
          all_columns: true
          masks:
            - column: NationalId
              algorithm: hash
              param: 16
            - column: Salary
              algorithm: round
              param: 1000
        - name: dbo.TimesheetEntry
          all_columns: true
          write_strategy: merge
        - name: dbo.AccessLog
          all_columns: true
          write_strategy: append

dlh_ref:
  org_guid: '<YOUR_ORG_GUID>'
  prj_guid: '<YOUR_PROJECT_GUID>'
  connection_guid: '<YOUR_CONNECTION_GUID>'
  target_schema_prefix: Site003
  api_key: '<YOUR_DLH_API_KEY>'

dlh_storage:
  storage_type: azure
  azure_storage_uri_scheme: azure

dlh_notifications:
  enabled: true
  platform_run_report: true

How the strategy is resolved per table, from most to least specific:

If you remove write_strategy: scd2 from data_retrieval, everything reverts to merge, the behaviour of agents before SCD2 existed. Existing configurations that never mention write_strategy are unaffected by this feature.

What the tables look like

Each SCD2 table has the source columns plus three metadata columns:

Example for one employee across three runs:

The third version exists because another column (say ManagerId) changed on 2 September; unchanged rows never create versions. If employee 1042 is deleted in the source, the current version is closed (_scd_valid_to set, _scd_is_current = false) and no new row is written.

First run and steady state

1. --config-check and --diagnose as usual.
2. The first run loads every table in full. Each row becomes the initial current version with _scd_valid_from set to that run's timestamp and _scd_valid_to = NULL.
3. Subsequent CT runs deliver only changed keys. For each key the agent compares the incoming values to the current version, closes it and inserts a new version if anything differs, skips it if not, and closes it without a replacement on a delete.
4. The run summary counts the changed rows per table as usual; Delta merge complete lines report the total row count, which for SCD2 tables grows with history.

Queries

Current state of the dimension, the view most reports should use:

CREATE OR REPLACE VIEW hr.employee_current AS
SELECT * EXCLUDE (_scd_valid_from, _scd_valid_to, _scd_is_current)
FROM raw.site003.employee
WHERE _scd_is_current = true;

State as of a date, for point-in-time reporting or restating a fact table against the dimension that was current at the time:

SELECT e.*
FROM raw.site003.employee e
WHERE e._scd_valid_from <= '2026-08-20 00:00:00'
  AND (e._scd_valid_to IS NULL OR e._scd_valid_to > '2026-08-20 00:00:00');

Changes in a period, for an audit report:

SELECT EmployeeId, Department, _scd_valid_from, _scd_valid_to
FROM raw.site003.employee
WHERE _scd_valid_from >= '2026-08-01' AND _scd_valid_from < '2026-09-01'
ORDER BY EmployeeId, _scd_valid_from;

Keys that no longer exist in the source:

SELECT EmployeeId
FROM raw.site003.employee
GROUP BY EmployeeId
HAVING SUM(CASE WHEN _scd_is_current THEN 1 ELSE 0 END) = 0;

In dbt, model the current view as above and build type-2 aware joins with the _scd_valid_from and _scd_valid_to range; you no longer need a dbt snapshot for these sources.

Variations

• Only a few tables need history: drop write_strategy from data_retrieval and set write_strategy: scd2 on those table entries only. This is the recommended shape when the database is mostly facts.
• Iceberg: set output_format: iceberg and the Iceberg options from CDC to Apache Iceberg on Amazon S3. The _scd_* semantics are identical.
• Full sync mode: a table with sync_mode: full and scd2 is treated as a snapshot each run: keys missing from the snapshot are closed, so do not use full on tables where the extract might be partial.
• Retention: SCD2 history is only as durable as the Delta or Iceberg table. Keep delta_vacuum_retention_hours at its default and take periodic storage-level backups if the history is a compliance record.

See Write strategies for the full reference, including delete+insert and incremental_key.