Query mirrored data¶

Each mirror creates target tables and companion objects in the target database. This page covers how to query them, how to read the change feed, and how to use the $live view for low-latency access.

Roles and permissions¶

Reading mirrored data is governed by the <target_database>_admin database role inside the target database. This role owns SELECT on every target table and $live view in the mirror.

create_mirror grants this role to the invoker with WITH GRANT OPTION, so the invoker can delegate read access to other roles:

GRANT DATABASE ROLE POSTGRESMIRRORTOSNOWFLAKE.POSTGRESMIRRORTOSNOWFLAKE_admin TO ROLE analytics;

For the role that governs managing mirrors (create, alter, drop), see Manage and monitor mirrors.

Access the target database¶

Target tables live in <target_database>.<schema>.<table> and can be queried like any other Snowflake table:

SELECT COUNT(*) FROM POSTGRESMIRRORTOSNOWFLAKE.PUBLIC.READINGS;

Each source table has three derived objects in the target database:

• <schema>.<table>: The materialized target table, updated on the mirror’s refresh_interval.
• <schema>.<table>$changes: The change log table (Iceberg, auto-refreshed from the source). Contains system columns plus the source data columns.
• <schema>.<table>$live: A view that combines the target table with pending change log entries, so readers see every committed change from the source with minimal additional latency.

Query at low latency with $live¶

The $live view combines the target table with not-yet-merged rows from $changes, so readers see every committed source change without waiting for the next apply run.

SELECT COUNT(*) FROM POSTGRESMIRRORTOSNOWFLAKE.PUBLIC.READINGS$live;

A large gap between counts on the target table and the $live view means many changes are pending and will be merged on the next apply run.

The $live view refreshes as soon as auto-refresh picks up new rows in the underlying $changes table (every 30 seconds), so $live lag is independent of refresh_interval. The trade-off is query cost: $live has to scan all $changes rows not yet merged into the target, so queries get more expensive as the backlog grows. Pairing a small $live scan with a larger refresh_interval is a good way to keep apply costs down while keeping read lag low.

Query changes with the $changes table¶

Each mirrored table has a companion $changes table (e.g. orders_db.public.orders$changes). It is a rolling 7-day change feed: every insert, update, and delete that happens on the source PostgreSQL table shows up here as a queryable row, with a _change_type column (I for insert, D for delete; updates appear as a delete/insert pair).

You can query $changes directly to build pipelines, audit trails, or point-in-time reads.

Snapshotting¶

When a table is added to a mirror for the first time — whether by creating the mirror, adding a table explicitly, or creating a new table inside a schema that is already mirrored — the mirror needs a full baseline copy of any existing rows before the change feed can start. During this period the table shows as SNAPSHOTTING in list_mirrored_tables. No changes are lost; the feed starts automatically once the copy completes.

This is the only situation that triggers a full data copy. No DDL on an already-mirrored table causes a re-snapshot.

DDL updates in $changes¶

When you run a column-level DDL on a mirrored table, $changes is immediately re-pointed to a brand-new, empty table with the updated column layout. The previous change history is no longer visible in $changes from that point on.

In other words: after a column change, $changes only contains rows that were written after the DDL. Any rows from before the column change are gone from the feed.

The underlying data files from before the DDL are not immediately deleted — they continue to exist for up to 7 days before being cleaned up — but they are no longer exposed through $changes.

If you have a pipeline or query that reads $changes and depends on seeing the full 7-day history, be aware that a column DDL will truncate that window back to zero at the moment it is applied.

Check row version with _data_version¶

Every row in $changes carries a _data_version integer. Unlike the column DDL case above, these operations do not replace $changes with a new empty table — the feed keeps running and all existing rows remain visible. However, they still mark a new generation in the system: the _data_version value increments at the boundary, signalling that rows before and after that point should not be treated as part of the same continuous sequence. Consumers should treat a version bump as a cue to reset their bookmarks or deduplication state.