Change Proposal Summary: atomic-ctas

🎯 Problem

The exasol__create_table_as macro emits two separate SQL statements:

CREATE OR REPLACE TABLE ... WITH NO DATA — auto-commits an empty table
INSERT INTO ... — populates the table

If the dbt process crashes between steps 1 and 2 (signal, network drop, OOM), the target table is left permanently empty. Downstream models silently produce zero-row results.

✅ Solution

Replace the two-step pattern with a single atomic CREATE OR REPLACE TABLE ... AS <sql> statement. The table either has the new data or the previous version is preserved unchanged.

Non-contract path: Single CREATE OR REPLACE TABLE t AS <sql> [DISTRIBUTE BY ...] [PARTITION BY ...]
Contract-enforced path: Atomic CTAS first, then ALTER TABLE for constraints (NOT NULL, PK, FK) — additive operations that can't cause data loss
Distribution/Partitioning: Moved inline with CTAS (no separate ALTER needed)

📦 Capabilities

NEW atomic-ctas

Guarantees create_table_as is atomic — populated with data or previous version preserved

MODIFIED relation-management

get_replace_table_sql now produces single atomic CTAS via delegation

⚖️ Key Design Decisions

D1: Single atomic CTAS (non-contract)
CREATE OR REPLACE TABLE t AS <sql> DISTRIBUTE BY ... PARTITION BY ... — one statement, inline distribution. If SELECT fails, existing table is preserved.

D2: CTAS + ALTER CONSTRAINT (contract path)
Exasol cannot combine column defs with AS subquery. Solution: atomic CTAS first, then ALTER TABLE ADD CONSTRAINT — safe because data is guaranteed present.

D3: PK via ALTER TABLE (not inline)
Primary keys applied after data load via ALTER TABLE ADD CONSTRAINT ... PRIMARY KEY (...). Actually safer — PK validated against real data.

D4: Macro restructuring
add_constraints split into inline clauses (DISTRIBUTE/PARTITION) and post-creation ALTERs (PK). |SEPARATEMEPLEASE| splitter retained only for ALTER statements.

⚠️ Risks & Trade-offs

Multi-node cluster reshuffling
Contract + distribute_by_config: ALTERs post-CTAS could trigger reshuffling. Mitigated: DISTRIBUTE BY is inline in CTAS even for contract path — only PK needs ALTER.

NOT NULL timing change
NOT NULL now applied after data load via ALTER TABLE MODIFY COLUMN ... NOT NULL. If data has NULLs, ALTER fails. Actually correct behavior — old pattern never validated against data.

Test fixture updates needed
exasol_expected_sql in constraint test fixtures hardcodes old pattern. Test-only change.

SQL output change BREAKING
Users/tools parsing emitted SQL will see different pattern. This is a bugfix — new pattern is strictly safer.

💥 Impact

Macros

adapters.sql — exasol__create_table_as
create_table_helpers.sql — add_constraints, partition_by_conf, distribute_by_conf

Materializations

table.sql, incremental.sql, snapshot.sql
All call create_table_as — fix applies transparently

Tests

constraints/fixtures.py — expected SQL update
Contract enforcement, table, incremental, snapshot tests

No Changes To

Public API / dependencies
connections.py splitter mechanism
View materializations

📋 Tasks (5 groups, 14 items)

1. Restructure create_table_helpers.sql macros

☐ 1.1 Refactor partition_by_conf — inline CTAS clause (no trailing semicolon)

☐ 1.2 Refactor distribute_by_conf — inline CTAS clause

☐ 1.3 Refactor add_constraints — post-creation ALTERs only (remove PARTITION/DISTRIBUTE)

2. Rewrite exasol__create_table_as macro

☐ 2.1 Non-contract path: single atomic CTAS + inline DISTRIBUTE/PARTITION + post ALTERs

☐ 2.2 Contract-enforced path: atomic CTAS + ALTER for NOT NULL, PK, FK

☐ 2.3 Remove all WITH NO DATA and INSERT INTO patterns

☐ 2.4 Remove temporary parameter handling distinction

3. Update contract test fixtures

☐ 3.1 Update exasol_expected_sql to match new pattern

☐ 3.2 Verify contract enforcement tests pass

4. Run existing test suites

☐ 4.1 Unit tests

☐ 4.2 Contract enforcement functional tests

☐ 4.3 Table materialization tests

☐ 4.4 Incremental materialization tests

☐ 4.5 Snapshot materialization tests

5. Add atomicity verification test

☐ 5.1 Test that rendered SQL has single CTAS (no WITH NO DATA + INSERT)

📄 Specs Summary

New Spec: `atomic-ctas` (6 requirements, 11 scenarios)

Atomic CTAS (non-contract) — single statement, no WITH NO DATA, works for temp relations too
Inline DISTRIBUTE BY / PARTITION BY — no separate ALTERs for distribution
Post-creation constraints (contract) — NOT NULL, PK, FK via ALTER after data load
No data loss on interruption — either new data or previous table preserved
Replace table uses atomic CTAS — get_replace_table_sql delegates to atomic macro

Modified Spec: `relation-management` (1 scenario changed)

Replace a table — now uses single atomic CTAS instead of WITH NO DATA + INSERT

🔄 Change Proposal: atomic-ctas