Mirror procedures reference¶

Reference for the mirror management procedures in the snowflake.postgres schema, the SQL objects a mirror creates on the source and target, and the column layout of every $changes change log table.

Object layout¶

Each mirror creates several objects across the source and target:

Object	Location	Name pattern	Purpose
Publication	Source PG	`<mirror_name>`	Drives logical replication
Replication slot	Source PG	`<mirror_name>`	Durable decoding cursor
Change log table	Source PG (Iceberg via pg_lake)	`snowflake_cdc_logs.snowflake_cdc_<...>`	Per-table CDC feed
Metalog table	Source PG (Iceberg via pg_lake)	`snowflake_cdc_logs.<mirror_name>`	Schema-change and batch queue
Target database	Snowflake	`<target_database>`	Holds materialized target tables
Target table	Snowflake	`<target_db>.<schema>.<table>`	Materialized copy of source
`$changes` table	Snowflake	`<target_db>.<schema>.<table>$changes`	Change log exposed via auto-refresh
`$live` view	Snowflake	`<target_db>.<schema>.<table>$live`	Target plus pending change log

Identifiers are folded to uppercase on Snowflake so that schema, table, and column names are case-insensitive in SQL.

Mirror management procedures¶

All procedures live in Snowflake in the snowflake.postgres schema and require the postgres_mirror_admin application role.

Procedure	Description
`CREATE_MIRROR(...)`	Create a new mirror. Sets up the target database, apply task, and publication on the source.
`ALTER_MIRROR(...)`	Change the refresh interval, or add/remove tables and schemas.
`DROP_MIRROR('<name>')`	Remove a mirror and clean up all associated infrastructure. The target database is left in place unless called with `DROP_TARGET_DATABASE => true`
`DESCRIBE_MIRROR('<name>')`	Return configuration and runtime state for a single mirror, including recent apply run durations.
`REFRESH_MIRROR('<name>')`	Apply pending changes to a mirror immediately, and clear last_apply_time.
`SUSPEND_MIRROR('<name>')`	Stop scheduling new apply runs for a mirror. Existing, in-flight run finishes naturally.
`RESTART_MIRROR('<name>')`	Suspend the task, wait for any in-flight execution to finish, tell the source Postgres to RESTART the publication, then resume the task.
`LIST_MIRRORS('<instance>')`	Return one row per mirror on the given Postgres instance.
`LIST_MIRRORED_TABLES('<name>')`	Return one row per source table with its replication state (`SNAPSHOTTING` or `REPLICATING`).
`QUERY_ADMIN_LOG(...)`	Query mirror-internal events with optional filters by mirror name, level (`INFO`, `WARN`, `ERROR`, `DEBUG`), and timestamp.
`REFRESH_CHANGES_TABLE(<mirror>, <table>)`	Refresh the Iceberg metadata for one mirrored table’s $changes table to reduce $live view lag.

CREATE_MIRROR parameters¶

Parameter	Required	Description
`mirror_name`	Yes	Identifier for the mirror, also used as the publication name. Must be an unquoted identifier of 50 characters or fewer.
`postgres_instance`	Yes	Name of the Postgres instance (case-sensitive).
`postgres_database`	Yes	Source database on the Postgres instance.
`target_database`	Yes	Snowflake database to create.
`postgres_tables`	One of `postgres_tables` or `postgres_schemas`	Fully qualified source table names (`schema.table`).
`postgres_schemas`	One of `postgres_tables` or `postgres_schemas`	Source schema names. All current and future tables in the schema are included.
`refresh_interval`	No	Merge cadence. Accepted values: `'30 seconds'`, `'1 minute'`, `'10 minutes'`, `'1 hour'`, `'1 day'`. Default: `'10 minutes'`.
`warehouse`	No	Run the apply task on this user-managed warehouse instead of serverless compute.

Change log schema¶

Every $changes table begins with system columns followed by the source table’s data columns:

Column	Type	Description
`_commit_lsn`	`bigint`	LSN of the commit that produced the row. Shared by every row in the same transaction.
`_lsn`	`bigint`	LSN of the row itself. Unique per change within a transaction.
`_xid`	`bigint`	Source transaction ID.
`_commit_time`	`timestamptz`	Commit timestamp of the source transaction.
`_change_type`	`char(1)`	`S` for initial snapshot, `I` for insert, `D` for delete. An `UPDATE` is emitted as a `D`/`I` pair.
`_is_update`	`boolean`	True on both the `I` and the `D` halves of an `UPDATE`; false on pure `INSERT` rows.
`_data_version`	`int`	Increments when the table’s data generation changes (`TRUNCATE`, add/drop primary key).

You can query the change log from the Snowflake side:

-- On Snowflake (auto-refreshed copy in the target database):
SELECT _commit_lsn, _change_type, _is_update, id, name
FROM orders_db.public.orders$changes
ORDER BY _commit_lsn DESC, _lsn DESC
LIMIT 20;