duckdb/scd1.sql

{{- /* scd1.sql — upsert in place; physically delete records no longer in staging.        */ -}}
{{- /* REWRITTEN from the legacy ON CONFLICT form (connectors#57): DuckDB unique indexes   */ -}}
{{- /* treat NULLs as DISTINCT, so ON CONFLICT (hpk, valid_to) never fires for active rows */ -}}
{{- /* (valid_to IS NULL) and the legacy template silently DUPLICATED changed rows — see   */ -}}
{{- /* truth_test.go. Tier B: the merge is explicit UPDATE + INSERT + DELETE, relying on   */ -}}
{{- /* no index conflict. All three statements run in ONE transaction (ExecStatements).    */ -}}
{{ template "duplicate_key_guard" . -}}
UPDATE "{{ .HistorySchema }}"."{{ .HistoryTable }}" hst
SET    {{ range $i, $col := .AttributeCols }}{{ if $i }},
       {{ end }}"{{ $col }}" = stg."{{ $col }}"{{ end }},
       amf_run_id = {{ .RunID }},
       amf_inserted_at = NOW(),
       amf_updated_at = NOW(),
       amf_valid_from = {{ template "valid_from_expr" . }},
       amf_hash_source_row_key = stg.amf_hash_source_row_key
FROM   "{{ .StagingSchema }}"."{{ .StagingTable }}" stg
WHERE  hst.amf_hash_primary_key = stg.amf_hash_primary_key
  AND  hst.amf_hash_source_row_key IS DISTINCT FROM stg.amf_hash_source_row_key;

INSERT INTO "{{ .HistorySchema }}"."{{ .HistoryTable }}" (
    {{ template "business_col_list" . }}, {{ template "amf_system_cols" . }}
)
SELECT {{ template "business_col_list" . }}, {{ template "amf_system_vals" . }}
FROM   "{{ .StagingSchema }}"."{{ .StagingTable }}" stg
WHERE  NOT EXISTS (
    SELECT 1
    FROM   "{{ .HistorySchema }}"."{{ .HistoryTable }}" hst
    WHERE  hst.amf_hash_primary_key = stg.amf_hash_primary_key
);

{{ template "absent_key_delete" . }}