duckdb/scd2.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" . }}