# Schema Changes > How Supabase ETL handles DDL and evolving table schemas. - Canonical HTML: https://supabase.github.io/etl/explanation/schema-changes/ - Agent-readable Markdown: https://supabase.github.io/etl/explanation/schema-changes.md - Source: https://github.com/supabase/etl/blob/main/site/content/docs/explanation/schema-changes.mdx Schema-change support is in public beta and is being expanded incrementally. The current implementation is intentionally conservative: the source-side event trigger captures a rich PostgreSQL-shaped snapshot, while ETL currently models well-understood column changes: **adds, drops, renames, and column default and nullability changes**, plus publication column-list changes for tables the running pipeline already tracks. A few known edge cases remain. Built-in destination support varies by destination DDL capabilities. **BigQuery, ClickHouse, DuckLake, and Snowflake** apply supported schema changes automatically; Iceberg is deprecated for new deployments and does not support schema-change DDL. ## Short Version [#short-version] For published permanent tables, ETL currently models these `ALTER TABLE` changes: | Source change | ETL interpretation | | ---------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------- | | Add a replicated column | Add column | | Drop a replicated column | Drop column | | Rename a replicated column | Rename column | | Change a replicated column default | Column default modification | | Drop a replicated column default | Column default removal | | Drop `NOT NULL` from a replicated column | BigQuery relaxes an existing `REQUIRED` column to `NULLABLE`; other built-in destinations currently leave nullability unchanged | | Set `NOT NULL` on a replicated column | Detected in the schema snapshot, but not applied to built-in destinations | | Several of the above in one statement | One schema snapshot, diffed into column additions, removals, and grouped column modifications | When several attributes of the same logical column change at once, ETL groups them into one column change with multiple modifications. For example, renaming a column and changing its default in one `ALTER TABLE` statement is treated as a single logical column change. ## How It Works [#how-it-works] ETL installs a PostgreSQL `ddl_command_end` event trigger named `supabase_etl_ddl_message_trigger`. When an `ALTER TABLE` statement or supported `ALTER PUBLICATION` change affects a published permanent table, the trigger emits a transactional logical message with prefix `supabase_etl_ddl`. PostgreSQL does not pass user-defined arguments to an event-trigger function. The function reads `TG_TAG` and the object addresses returned by `pg_event_trigger_ddl_commands()`, then resolves the post-DDL catalogs: * For `ALTER TABLE`, the object address identifies the table. The message has no publication name because a physical table change applies to every publication containing that table. * For per-table `ALTER PUBLICATION` changes, the object address identifies a surviving `pg_publication_rel` row, which links the explicitly named table to its publication. This path does not expand a named partition root into its effective leaves. * For publication-level `ALTER PUBLICATION` changes, the object address identifies a `pg_publication` row. The trigger does not parse which parameter changed; it expands the publication's complete post-command effective table set through `pg_publication_tables`. The publication name is therefore optional in the payload for table DDL but required for publication DDL. Logical decoding exposes custom messages to a slot independently of pgoutput's table filtering, so ETL accepts an `ALTER PUBLICATION` message only when its resolved name exactly matches the pipeline's configured publication. A missing name fails closed. That message is **internal plumbing**. Destinations do not receive it directly. Instead, ETL: 1. Parses the schema-change message. 2. Stores a new versioned table schema using a composite snapshot ID ordered by commit LSN and then message LSN. 3. Invalidates the in-memory relation state for that table. 4. Waits for PostgreSQL pgoutput to emit a fresh `RELATION` message before the next row event for that table. 5. Sends destinations a public `Event::Relation` with the new `ReplicatedTableSchema`. Snapshot IDs display both LSNs as decimal unsigned 64-bit values in `commit_lsn:message_lsn` form. Their ordering is the numeric tuple ordering of those two components, not the lexical ordering of the displayed string. `ALTER PUBLICATION ... DROP TABLE` intentionally emits no schema snapshot for the removed table because it is no longer part of the publication. PostgreSQL 14 may still send an empty `BEGIN`/`COMMIT` pair for that transaction; PostgreSQL 15 and later suppress empty logical-replication transactions. The empty pair contains no schema or row event and does not affect snapshot ordering. See the [PostgreSQL 15 release notes](https://www.postgresql.org/docs/15/release-15.html) and the [upstream change](https://github.com/postgres/postgres/commit/d5a9d86d8ffcadc52ff3729cd00fbd83bc38643c). For a table already known to the pipeline, a supported publication change creates a new snapshot ID even when the full physical table schema is unchanged. The following `RELATION` message supplies the current publication and identity masks. This gives successive mask changes distinct snapshot IDs and preserves their ordering. Multiple schema messages sharing one commit LSN are ordered by their message LSN. A newly published table that the running pipeline does not know is ignored until startup publication reconciliation creates its table state and initial sync. ### `ALTER PUBLICATION` support boundary [#alter-publication-support-boundary] The trigger's ability to observe an `ALTER PUBLICATION` command is not the same as runtime support for that publication change. The trigger emits schema snapshots; it does not add or remove ETL table state, start an initial sync, or change which relation OIDs the running apply worker owns. | Publication change | Trigger behavior | Runtime behavior | | ---------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------- | | Change the column list of an already tracked table | Emits a snapshot scoped to that table and publication | Supported. The next `RELATION` message installs the new replication mask before following row events. | | `ADD TABLE` or a membership-changing `SET TABLE` | Emits snapshots for surviving, explicitly identified `pg_publication_rel` rows. A named partition root is not expanded into leaves on this path. | Newly effective relation OIDs are ignored until startup reconciliation initializes and copies them. | | `DROP TABLE` | Emits no snapshot because the `pg_publication_rel` row is gone at `ddl_command_end`. | Removed table state is purged during startup reconciliation. Destination data is not automatically removed. | | Add, set, or drop `TABLES IN SCHEMA` | The current trigger does not resolve `pg_publication_namespace` events into table snapshots. | Effective membership is loaded during startup reconciliation. | | Change a row filter | May emit a per-table snapshot, but row-filter mutation is not part of the supported live-update contract. | Do not rely on changing row filters while the pipeline is running. | | Change a publication-level setting such as `publish` | A `pg_publication` event expands to one snapshot for every post-command effective table, even if physical schemas did not change. | Publication settings are not a supported live schema-update interface. | | Change `publish_via_partition_root` | Emits snapshots for the complete post-change effective set: the root when enabled, or the leaves when disabled. | Unsupported while running. The new relation OIDs are not dynamically initialized, and the previous destination relation layout is not migrated. | | Rename the publication or change its owner | These are publication-level events and may expand across the effective table set. A rename scopes messages to the new name. | Not supported as live schema changes. A configured pipeline does not automatically adopt a new publication name. | At startup, ETL calls `pg_get_publication_tables()` to load the same effective relation identities PostgreSQL uses for pgoutput. New identities enter initial sync and identities no longer returned by PostgreSQL have their ETL state purged. This startup process does not rename, merge, or delete destination tables created for an earlier root or leaf identity. > **Warning** > > Only publication column-list changes for already tracked tables are supported > while a pipeline is running. Other `ALTER PUBLICATION` changes can produce > skipped events, stale destination tables, or missing or duplicated data. Plan > a controlled change with source writes paused and the pipeline stopped, > followed by an explicit restart, table resynchronization, or new pipeline > instead of relying on live behavior. The important public boundary is: ```text ... -> internal DDL message -> Relation(new schema) -> Insert/Update/Delete ... ``` Destinations should treat `Event::Relation` as the point where the **active schema changes** for following row events. `Relation` is an ordered event, not a batch boundary. ETL batches calls to `write_events()` based on size and time, so a single destination batch may contain zero, one, or many schema changes, including multiple relation events for the same table. ## Destination-Specific DDL Behavior [#destination-specific-ddl-behavior] ETL has one shared schema-change signal, but **DDL behavior is implemented per destination**. A destination may choose to apply DDL automatically, reject a schema change, or require operator handling. For every built-in destination, a relation whose snapshot ID and replication mask exactly match the applied destination metadata is idempotent. BigQuery, ClickHouse, DuckLake, and Snowflake currently reject an older snapshot, or the same snapshot with a different replication mask, instead of attempting to infer schema ordering from later row events. A relation has no DML sequence key of its own, so destination row-replay deduplication does not prove that reverse or ambiguously ordered DDL is safe. Recovering from either rejection currently requires resynchronizing the table. This comparison applies only to metadata already marked `Applied`; it neither defines nor initiates recovery from an interrupted `Applying` state. | Destination | Current DDL behavior | | ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | BigQuery | Supports add, drop, rename, `REQUIRED` to `NULLABLE` relaxation, and supported literal default metadata. BigQuery requires added columns to be nullable and does not backfill existing rows for `ADD COLUMN ... DEFAULT`. PostgreSQL remains responsible for enforcing later `SET NOT NULL` changes because BigQuery cannot tighten an existing column in place. | | ClickHouse | Supports add, drop, rename, and supported literal defaults. `ReplacingMergeTree` rejects primary-key drops or renames because the ordering expression cannot be rewritten safely. ClickHouse default expressions are metadata-only unless explicitly materialized; ETL does not issue `MATERIALIZE COLUMN`. | | DuckLake | Supports add, drop, rename, and supported literal defaults. DuckLake records supported add-time defaults as metadata without rewriting existing data files. | | Snowflake | Supports add, drop, rename, create-table literal defaults, and literal add-column defaults. Literal defaults are included in `ADD COLUMN` so Snowflake can expose add-time default values for existing rows; non-literal defaults and later default changes are skipped with a warning. | | Iceberg | Deprecated. An identical relation is idempotent, but schema-change DDL is not supported and any newer schema is rejected. Older or ambiguously masked relations are rejected before row writes. | | Custom destinations | Destination authors decide which `Event::Relation` changes to apply, reject, or handle manually. | See [Destinations](https://supabase.github.io/etl/reference/destinations.md) for the canonical maturity status and broader limitations of every built-in implementation. ## Default Backfills [#default-backfills] When a source table adds a replicated column with a default, PostgreSQL can make pre-existing source rows read as though they already contain that default. ETL deliberately avoids **physical destination backfills** for those existing rows. Built-in destinations avoid operations such as `UPDATE`, `MERGE`, CTAS/swap rewrites, or ClickHouse `MATERIALIZE COLUMN` because those operations can rewrite large tables, block replication progress, and create destination-specific cost spikes. Instead, destinations apply the schema change in the cheapest safe form they support: * BigQuery adds the column as nullable, then sets supported literal default metadata for future writes. * ClickHouse may expose default values for pre-existing rows through default metadata, but ETL does not issue `MATERIALIZE COLUMN`. * DuckLake uses add-time initial default metadata for supported defaults; its schema evolution does not rewrite data files. * Snowflake uses `ADD COLUMN ... DEFAULT` for supported literal defaults. Snowflake exposes default values for existing rows and does not document this as a physical row rewrite, but defaults created this way cannot later be dropped. As a result, pre-existing destination rows might not match PostgreSQL's historical `ADD COLUMN ... DEFAULT` view unless the destination has a metadata-only initial-default mechanism and the source default is supported. This does **not** mean future replicated tuples lose their default values: PostgreSQL sends evaluated column values in row data after the relation change, and ETL writes those values normally. The limitation is only that unsupported defaults are not installed as destination schema default metadata, and existing destination rows are not rewritten by ETL. ## Supported Column Defaults [#supported-column-defaults] Column defaults are **best-effort metadata translations**, not a PostgreSQL expression evaluator. ETL reads the source default from PostgreSQL's `pg_get_expr` output, parses only deterministic literal defaults, and asks each destination whether that parsed default can be rendered safely in that destination's SQL dialect. If a default is not in the supported subset, ETL skips the destination default metadata with a warning. Replication does not fail, and future tuples still carry evaluated values from PostgreSQL. This is intentional: runtime-generated defaults can evaluate at different times or under different session settings in different systems, so installing a similar-looking destination default can create silent mismatches. ETL can add support for specific additional defaults later when their semantics can be preserved for the destination. The shared parser currently recognizes only these source default shapes: | Source default shape | Examples | | ---------------------------- | ---------------------------------------------------------------------------- | | String literals | `'pending'::text`, `('don''t'::text)` | | Numeric literals | `42`, `-1`, `'42.10'::numeric(10,2)` | | Boolean literals | `true`, `false`, `'true'::boolean` | | Date/time/timestamp literals | `'2026-01-01'::date`, `'12:30:00'::time`, `'2026-01-01 12:30:00'::timestamp` | | JSON literals | `'{}'::jsonb`, `'{"enabled": true}'::json` | | UUID literals | `'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid` | The parser is intentionally conservative. These PostgreSQL defaults are examples of **unsupported** expressions: ```sql default nextval('users_id_seq') default 'a' || 'b' default lower('USER' || '_ID') default concat('a', 'b') default md5('x') default random() default clock_timestamp() default now() default current_timestamp default current_user default gen_random_uuid() default uuid_generate_v4() default timezone('UTC', now()) default array['a', 'b'] default (select 'x') default current_setting('app.tenant_id') default 1e6 default interval '1 day 2 hours' ``` Unsupported defaults are skipped because translating arbitrary PostgreSQL expressions would require both a PostgreSQL parser and a destination-specific expression translator. Most destinations do not accept arbitrary PostgreSQL expressions as column defaults, and even similar-looking SQL can have different volatility, time zone, type coercion, or evaluation semantics. Skipping the destination schema default does not drop actual row values emitted by PostgreSQL. Destination support may be narrower than parser support: | Destination | Supported default behavior | | ----------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | BigQuery | Supports compatible string, numeric, boolean, date, time, timestamp, JSON, and UUID literals. Added columns are created nullable and supported defaults are set afterward for future writes. | | ClickHouse | Supports compatible string, numeric, boolean, date, time, timestamp, JSON, and UUID literals. Defaults are metadata only unless separately materialized. | | DuckLake | Supports compatible string, numeric, date, time, timestamp, JSON, and UUID literals. Boolean defaults are currently skipped by the DuckLake destination. | | Snowflake | `CREATE TABLE` supports compatible string, numeric, boolean, date, time, timestamp, JSON, and UUID literals. `ADD COLUMN` only receives the literal subset Snowflake allows for add-column defaults: string, numeric, and boolean literals. Later default changes on existing columns are skipped. | When changing a default from one supported expression to an unsupported expression, destinations that can safely remove defaults drop the old supported default to avoid leaving stale destination behavior behind. Snowflake is the exception for defaults introduced by `ADD COLUMN ... DEFAULT`, because Snowflake does not allow those defaults to be dropped safely. ## Diff Semantics [#diff-semantics] ETL stores schemas in **PostgreSQL column ordinal order** (`pg_attribute.attnum`) and computes destination schema diffs over replicated columns only. The current diff rules are: * Same ordinal position, different name: column rename. * Same ordinal position, different default expression: column default change. * Same ordinal position, different nullability: detected schema metadata change. * Old ordinal position missing from the new schema: column drop. * New ordinal position missing from the old schema: column add. This matches PostgreSQL's normal behavior for simple column operations: renames keep the same `attnum`, dropped columns disappear from the visible schema, and newly added columns receive new ordinal positions. ## Destination Handling [#destination-handling] Custom destinations should handle schema changes in `write_events()` by watching for `Event::Relation`. A practical flow is: 1. Iterate through the batch in order, treating each relation event as a possible schema transition for that table. 2. Flush any buffered rows/events for the old schema before processing the relation event. 3. Compare the old destination schema with the relation event's new `ReplicatedTableSchema`. 4. Mark destination metadata as `Applying` if the destination needs recovery bookkeeping for the DDL transition. 5. Apply supported destination DDL for adds, drops, renames, and default changes. 6. Mark destination metadata as `Applied` only after the destination schema is actually ready for following row events. 7. Process following row events with the new schema. The built-in BigQuery, ClickHouse, DuckLake, and Snowflake destinations follow this shape: they mark destination schema metadata as `Applying`, apply the supported DDL operations, then mark the schema as `Applied`. Because destination DDL is not always transactional, recovery is destination-specific. ClickHouse and DuckLake can retry an interrupted `Applying` operation. When an arriving relation drives that retry, it must exactly match the snapshot ID and replication mask recorded as the target; DuckLake startup recovery can instead reconstruct that exact target from durable schema state. BigQuery does not automatically repair `Applying` schema-change metadata, and Snowflake only automatically retries interrupted initial setup, not an interrupted schema change. Other interrupted states require resynchronization. Other destination modules may support a narrower schema-change surface. Treat `Event::Relation` as the stable ETL contract, then check the destination's status and implementation before relying on automatic destination DDL. ## Supported Scope [#supported-scope] The source event trigger intentionally observes a broad schema snapshot, but ETL currently supports the simplest safe cases: * `ALTER TABLE ... ADD COLUMN` for replicated columns. * `ALTER TABLE ... DROP COLUMN` for replicated columns. * `ALTER TABLE ... RENAME COLUMN` for replicated columns. * `ALTER TABLE ... ALTER COLUMN ... SET DEFAULT` where the destination supports setting compatible default metadata. * `ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT` where the destination supports removing default metadata safely. * Multi-subcommand `ALTER TABLE` statements composed of those simple changes. * Changes to published permanent tables only. The trigger ignores temporary tables, unpublished tables, generated columns, dropped-column catalog tombstones, extension-owned DDL, and non-logical-WAL databases. ## Known Beta Limitations [#known-beta-limitations] These behaviors are **not full destination DDL semantics** yet: * Only `ALTER TABLE` and supported `ALTER PUBLICATION` changes are captured by the ETL DDL trigger today. * Type changes, constraint changes, identity changes, and replica-identity changes may be visible in the emitted snapshot, but they are not yet interpreted as destination DDL operations. * Table create/drop/rename operations are outside the current schema-change contract. Publication membership and table cleanup remain separate pipeline lifecycle concerns. * Live publication membership, row-filter, operation-setting, publication-name, and `publish_via_partition_root` changes are unsupported. Although the source trigger may emit schema snapshots for some of these commands, it does not dynamically reconcile table ownership or migrate destination table identity. * If a table-sync worker decodes a DDL or publication-column change during catch-up but receives no following relation before its handover boundary, it cannot construct the complete decoder required by `SyncDone`. The DDL message supplies the physical schema, but only the relation supplies the exact publication and replica-identity masks for that WAL position. ETL cannot safely reuse older masks or read newer catalog state, so this correctness edge case fails the table sync closed. Retry or resynchronize the table after schema activity has settled. * A drop and re-add is not treated as a rename. It becomes a drop plus an add because PostgreSQL assigns a new ordinal position to the new column. * Destination defaults are best-effort metadata translations. Unsupported defaults are skipped with a warning instead of failing replication. This does not remove values PostgreSQL emits in future row events; it only means the destination schema default metadata is not set. If a previously supported default becomes unsupported, ETL removes the old destination default where the destination supports that operation so stale behavior is not left behind. Snowflake default changes on existing columns are skipped with a warning because `ALTER COLUMN SET DEFAULT` is documented only for existing sequence defaults, and defaults introduced by `ALTER TABLE ADD COLUMN ... DEFAULT` cannot be dropped safely. * Runtime-generated defaults are intentionally unsupported as destination schema defaults. Examples include `now()`, `clock_timestamp()`, `gen_random_uuid()`, `random()`, sequence defaults, and session-dependent expressions such as `current_user`. Those expressions can evaluate at different times or under different session settings in each destination. PostgreSQL still sends evaluated values for future row events, but existing destination rows are not physically backfilled by ETL. Avoid runtime-generated defaults for replicated schema changes when exact historical values matter. * `ADD COLUMN ... DEFAULT` semantics differ by destination. ETL intentionally avoids destination DDL that rewrites all existing rows. BigQuery leaves pre-existing destination rows null for newly added defaulted columns, while ClickHouse, DuckLake, and Snowflake can expose supported add-time defaults without ETL issuing a materialization rewrite. Snowflake only receives add-column defaults for source defaults that can be rendered as Snowflake literals. * BigQuery applies ongoing-replication `DROP NOT NULL` changes so future source NULL values remain writable. BigQuery cannot change an existing `NULLABLE` column to `REQUIRED`, so PostgreSQL enforces later `SET NOT NULL` changes while the destination column remains nullable. Other built-in destinations currently leave ongoing-replication nullability changes unchanged. Newly added columns remain nullable where the destination requires that for historical rows. * The trigger payload includes `current_query` for debugging only. It can contain literals and multiple statements, so it must not be treated as replayable DDL. * BigQuery, ClickHouse, DuckLake, and Snowflake reject stale or ambiguously ordered relation schemas instead of rewinding. An older snapshot could drive reverse DDL that drops newer columns and their data. An equal snapshot with a different replication mask is also rejected: supported publication column-list changes always receive a new composite snapshot ID, so the conflicting masks have no ordering with which to choose a safe winner. The pipeline fails with a schema-rewind error and the affected table must be resynchronized. * Sessions can set `supabase_etl.skip_ddl_log = 'true'` as an emergency opt-out while recovering a system. DDL executed with that setting enabled is not logged for ETL. ## Event Ordering [#event-ordering] Schema changes are transactional logical messages, so they appear in WAL order relative to row changes. ETL updates its stored schema when it decodes the DDL message, then waits for the next `RELATION` message to rebuild the runtime replication and identity masks for row decoding. This matters for custom destinations: row events after a relation event should be decoded and written using that relation event's schema. Row events before it belong to the previous schema.