Skip to content

ADR-0007 — Table column management — reorder, add, drop

Update date : 2026-05-27 21:44

Status: accepted Date: 2026-05-27

Context

When the column list in the manifest diverges from the live table, the provider must decide how to reconcile. The strategy depends on whether the table has active streams or downstream Dynamic Tables, signalled by CHANGE_TRACKING = TRUE.

ALTER TABLE cannot reorder columns. The only reorder mechanisms are CTAS (destructive) or SWAP WITH (atomic), both of which risk breaking streams and DTs.

Decision

Three strategies, passed as a CLI flag — not in the YAML (depends on environment, not manifest):

pinky-provider apply --env SANDBOX    --column-conflict reorder   # time travel lost
pinky-provider apply --env PRODUCTION --column-conflict preserve  # default, time travel preserved
Strategy New column Dropped column Order Time travel
preserve (default) ALTER TABLE ADD COLUMN Warning, no action unchanged Preserved
reorder SWAP WITH parallel table Warning, no action YAML order Lost
drop ALTER TABLE ADD COLUMN DROP COLUMN + confirmation unchanged Preserved

--column-conflict drop always requires interactive confirmation (or --yes for CI).

CHANGE_TRACKING = TRUE (table has streams or downstream DTs)

No reorder. Column order in the manifest is ignored — the live order is authoritative.

  • New columns: appended at the end via ALTER TABLE ADD COLUMN.
  • Removed columns: dropped via ALTER TABLE DROP COLUMN.
  • YML normalization: after apply, the provider outputs the corrected column block to stdout (columns in actual table order, new columns at the end). The user can paste it back, or the provider can overwrite the manifest directly. Required for idempotency — without it, the next plan sees a reorder diff and retries the same operation.

CHANGE_TRACKING = FALSE (no streams, no downstream DTs)

Reorder is safe. Use SWAP WITH:

CREATE TABLE {table}__swap AS
SELECT {new_column_order} FROM {table};

ALTER TABLE {table} SWAP WITH {table}__swap;

DROP TABLE {table}__swap;

SWAP is atomic — the table never disappears, no concurrent query sees it missing. Time travel on the new version starts from the swap.

Adding a NOT NULL column without default

Adding a NOT NULL column without a default value to a non-empty table fails at the Snowflake level. The provider must intercept this at plan time, not at apply time.

Rule: if a new column is NOT NULL and has no default declared in the manifest, and the table already exists with rows, the provider raises a blocking error:

[schema.table] cannot add NOT NULL column 'col_name' without a default value on a non-empty table.
Add a '_default' expression to the column definition in the yml.

The manifest must declare a backfill default:

columns:
  - name: STATUS
    type: VARCHAR
    nullable: false
    _default: "'UNDEFINED'"   # migration helper — applied for ADD COLUMN, not a persistent constraint

The _default is applied as ALTER TABLE ADD COLUMN ... DEFAULT .... It is a migration helper, not a schema constraint — can be dropped after backfill if business logic doesn't require it.

Consequences

  • CHANGE_TRACKING is the single signal — no per-object tracking needed.
  • YML normalization on CHANGE_TRACKING tables is required for idempotency — it prevents the next plan from generating a spurious reorder diff and consuming credits for nothing.
  • SWAP WITH is never used on CHANGE_TRACKING tables → no stream invalidation risk.
  • --yes flag bypasses confirmation prompts for CI. Non-blocking warning is the default for interactive use when columns are dropped.
  • YML normalization output uses a friendly tone: 🤑 save credits — reorder your yml.