# duckdb/scd2.sql

scd2.sql — two-statement SCD2 merge for DuckDB (SCD Type 2)

scd2.sql — two-statement SCD2 merge for DuckDB (SCD Type 2)

## Context fields

- `.HistorySchema`
- `.HistoryTable`

## Template

```sql
{{- /* scd2.sql — two-statement SCD2 merge for DuckDB (SCD Type 2) */ -}}
{{- /* Reads compare_rows: the _transform view under full, else the scoped compare      */ -}}
{{- /* computed once (ADR-161). Either computes amf_cdc_action, under full                */ -}}
{{- /* via FULL OUTER JOIN between staging and current history rows. Statement 1 closes  */ -}}
{{- /* changed/deleted active rows; statement 2 inserts new/changed versions. Both run   */ -}}
{{- /* in ONE transaction (rdbms.ExecStatements), so the close+insert pair is atomic.    */ -}}
{{ template "compare_prepare" . -}}
UPDATE "{{ .HistorySchema }}"."{{ .HistoryTable }}" hst
SET    amf_valid_to   = {{ template "valid_from_expr" . }},
       amf_updated_at = NOW()
FROM   {{ template "compare_rows" . }} stg
WHERE  stg.amf_cdc_action IN (2, 3)
  AND  hst.amf_hash_primary_key = stg.amf_hash_primary_key
  AND  hst.amf_valid_to IS NULL;

INSERT INTO "{{ .HistorySchema }}"."{{ .HistoryTable }}" (
    {{ template "business_col_list" . }}, {{ template "amf_system_cols" . }}
)
SELECT {{ template "business_col_list" . }}, {{ template "amf_system_vals" . }}
FROM   {{ template "compare_rows" . }} stg
WHERE  stg.amf_cdc_action IN (1, 2)
{{- template "compare_release" . }}
```

## Provenance

| | |
| --- | --- |
| Platform | `duckdb` |
| File | `duckdb/scd2.sql` |
| Version | `v1.3.1` |
| Lines | 22 |
| SHA-256 | `baa4555529a13318adfc9806aeeee852f5cb04ac33af05468c9d5428f20eabc2` |