# duckdb/fragments.sql

fragments.sql — shared {{ define }} blocks for all duckdb history templates

fragments.sql — shared {{ define }} blocks for all duckdb history templates

## Context fields

- `.BusinessCols`
- `.CompareScope`
- `.EventTimeCol`
- `.HasEventTime`
- `.HistoryTable`
- `.PKColumns`
- `.RunID`
- `.RunStartedAt`
- `.StagingSchema`
- `.StagingTable`

## Template

```sql
{{- /* fragments.sql — shared {{ define }} blocks for all duckdb history templates */ -}}

{{- define "business_col_list" -}}
{{- range $i, $col := .BusinessCols }}{{ if $i }}, {{ end }}"{{ $col }}"{{ end -}}
{{- end -}}

{{- define "valid_from_expr" -}}
{{- if .HasEventTime -}}stg."{{ .EventTimeCol }}"::timestamptz
{{- else -}}'{{ .RunStartedAt }}'::timestamptz
{{- end -}}
{{- end -}}

{{- /* Universal amf_* system columns — used by all history modes (DESIGN-012). */ -}}
{{- define "amf_system_cols" -}}
amf_run_id, amf_inserted_at, amf_updated_at, amf_valid_from, amf_valid_to, amf_hash_primary_key, amf_hash_source_row_key
{{- end -}}

{{- define "amf_system_vals" -}}
{{ .RunID }}, NOW(), NOW(), {{ template "valid_from_expr" . }}, NULL, stg.amf_hash_primary_key, stg.amf_hash_source_row_key
{{- end -}}

{{- define "pk_join_condition" -}}
{{- range $i, $col := .PKColumns }}{{ if $i }} AND {{ end }}stg."{{ $col }}" = hst."{{ $col }}"{{ end -}}
{{- end -}}

{{- /* Compare scope (ADR-161). compare_source is the SDK's relation of compare rows: the
     _transform view under full, read where it stands as before. scd2 and snapshot read
     the compare twice, to close and then to insert, so under any other scope it is
     computed once per apply into a temporary table: compare_prepare fills it, compare_rows
     names it and compare_release drops it. CREATE TABLE AS counts as neither inserted
     nor updated: the SDK counts the writes by statement kind. */ -}}
{{- define "compare_prepare" -}}
{{- if ne .CompareScope "full" -}}
{{ template "duplicate_key_guard" . -}}
CREATE OR REPLACE TEMP TABLE "{{ .HistoryTable }}_compare" AS
SELECT * FROM {{ template "compare_source" . }} cmp;

{{ end -}}
{{- end -}}

{{- define "compare_rows" -}}
{{- if eq .CompareScope "full" -}}{{ template "compare_source" . }}
{{- else -}}temp.main."{{ .HistoryTable }}_compare"
{{- end -}}
{{- end -}}

{{- define "compare_release" -}}
{{- if ne .CompareScope "full" }};

DROP TABLE temp.main."{{ .HistoryTable }}_compare"
{{- end -}}
{{- end -}}

{{- /* duplicate_key_guard fails a scoped apply whose batch carries one key twice. DuckDB's
     unique index treats NULLs as distinct (tier B), so no index stops a second active
     version of that key; error() does, and the transaction rolls the apply back. */ -}}
{{- define "duplicate_key_guard" -}}
{{- if ne .CompareScope "full" -}}
SELECT error('duplicate key in batch: the staging table carries one amf_hash_primary_key more than once')
FROM   "{{ .StagingSchema }}"."{{ .StagingTable }}"
GROUP  BY amf_hash_primary_key
HAVING count(*) > 1
LIMIT  1;

{{ end -}}
{{- end -}}
```

## Provenance

| | |
| --- | --- |
| Platform | `duckdb` |
| File | `duckdb/fragments.sql` |
| Version | `v1.3.1` |
| Lines | 66 |
| SHA-256 | `efe8061787a57b9892e3438597fc0d92cbfd33cfbe62e7211715e5e7c3037a09` |