postgres/snapshot.sql

{{- /* snapshot.sql — diff-based snapshot for PostgreSQL (state tables)                */ -}}
{{- /* Reads compare_rows: under full the _transform view (FULL OUTER JOIN staging ↔    */ -}}
{{- /* history_current), else the scoped compare computed once (ADR-161), to detect      */ -}}
{{- /* INSERT (1), UPDATE (2), DELETE (3) via amf_hash_primary_key + row_key compare.  */ -}}
{{- /* Rows with amf_cdc_action = 0 (NO_CHANGE) are skipped — zero writes when source */ -}}
{{- /* data is unchanged. Cost proportional to delta, not table size.                  */ -}}
{{- /*                                                                                 */ -}}
{{- /* Semantically identical to SCD2: both use hash-compare and only write changes.   */ -}}
{{- /* The difference is the source delivery model (full refresh vs deltas), not the    */ -}}
{{- /* history write logic. See DESIGN-015 §10, DESIGN-012 §D6.                        */ -}}
{{ 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" . }}