Connect with Snowflake Analytics
Snowflake Postgres runs your transactional workloads. Sooner or later, someone wants that same data for analytics, ML training, dashboards, or reporting, and you don't want those queries running on your production database. Snowflake gives you two built-in ways to get Postgres data into the Snowflake analytics engine, and they fit different jobs.
| pg_lake (Shared Iceberg) | Data Mirroring | |
|---|---|---|
| What moves | The tables and rows you choose, when you choose | Whole tables or whole schemas, continuously |
| Format | Apache Iceberg (open, portable) | Snowflake tables by default, or Snowflake-managed Iceberg tables |
| Control | You decide what to write and when | Automatic, keeps target tables in sync |
| Best for | Selective sharing, lakehouse workflows, archiving, multi-engine access | Full-table analytics, dashboards, near real-time reporting |
Option 1: pg_lake and Shared Iceberg
pg_lake is an open source Postgres extension that lets Postgres create and write Apache Iceberg tables. In Snowflake Postgres, the shared Iceberg setup has Postgres act as the Iceberg catalog, with Snowflake reading the tables through a catalog integration. Snowflake manages the storage, so you don't need your own bucket, IAM roles, or an external volume.
How It Works
- You decide what gets written. Create Iceberg tables in Postgres with
USING iceberg, and fill them withINSERT ... SELECTfrom your regular tables, on whatever schedule you like. - In Snowflake, create a catalog integration with
CATALOG_SOURCE = SNOWFLAKE_POSTGRESthat points at your Postgres instance. - Create Iceberg tables in Snowflake that reference the Postgres tables, or a catalog-linked database that discovers them all. Snowflake reads the same files. Nothing gets copied.
pg_lake needs a STANDARD or HIGHMEM instance. Burstable instances don't support it.
When to Use pg_lake
- You want control over exactly which data gets shared and when
- You're building a lakehouse setup with Postgres as the operational layer and Iceberg as the analytical layer
- You want to archive or offload historical data out of your hot tables
- You want to transform, aggregate, or filter data before analysts see it
- You need the data in an open format. If other engines need to read it, pg_lake can also write to an S3 bucket or Azure container you own.
Example Workflow
-- In Postgres: enable pg_lake and create an Iceberg copy of recent orders CREATE EXTENSION IF NOT EXISTS pg_lake CASCADE; CREATE TABLE orders_iceberg USING iceberg AS SELECT * FROM orders WHERE created_at >= '2026-01-01';
-- In Snowflake: point a catalog integration at the Postgres instance CREATE OR REPLACE CATALOG INTEGRATION my_postgres_catalog CATALOG_SOURCE = SNOWFLAKE_POSTGRES TABLE_FORMAT = ICEBERG CATALOG_NAMESPACE = 'public' REST_CONFIG = ( POSTGRES_INSTANCE = 'my_postgres_instance' CATALOG_NAME = 'postgres' ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS ) ENABLED = TRUE; -- Reference the Postgres Iceberg table and query it CREATE OR REPLACE ICEBERG TABLE orders_from_postgres CATALOG = 'my_postgres_catalog' CATALOG_TABLE_NAME = 'orders_iceberg' AUTO_REFRESH = TRUE; SELECT * FROM orders_from_postgres WHERE created_at >= '2026-03-01';
To keep the Iceberg table current without re-exporting everything, use pg_incremental with pg_cron to append only new rows on a schedule. Put a BRIN index on the watermark column so each run doesn't scan the whole table. The pg_lake guide has the details.
Key Characteristics
- Data is stored as Apache Iceberg, an open table format
- Selective: export exactly the tables, columns, or filtered subsets you need
- You pick the schedule: on demand, via pg_cron, or from application events
- Tables shared this way are read-only in Snowflake. Postgres stays the writer.
- If you want files moving the other way, pg_lake stages and customer-managed storage support exchange in both directions
Option 2: Data Mirroring
Data mirroring is Snowflake's built-in replication from Snowflake Postgres into Snowflake. You choose tables or schemas to mirror, and Snowflake handles the initial snapshot, ongoing change capture, and applying changes in order.
How It Works
- Pick what to mirror. Name specific tables, or whole schemas so new tables get picked up automatically.
- Snowflake handles replication. Under the hood it uses a Postgres publication and logical replication slot, writes changes through pg_lake, and applies them with a serverless task. You don't run CDC tools, Kafka, or Airflow.
- Query in Snowflake. Mirrored tables show up as regular Snowflake tables. Each one also gets a
$liveview for the latest changes that haven't been applied yet and a$changesfeed covering the last 7 days.
When to Use Data Mirroring
- You want whole tables in Snowflake with minimal configuration
- Analytics should reflect recent operational changes, typically within about a minute
- You're building dashboards or reports that need a complete, current copy of your Postgres data
- You'd rather use a managed feature than build and maintain your own pipelines
Key Characteristics
- Fully managed: no pipelines to build or maintain
- Continuous: inserts, updates, deletes, and schema changes flow through automatically
- Transactional: changes from a single Postgres transaction become visible together
- Complete tables, not filtered subsets
- No storage configuration required
Choosing Between the Two
Use pg_lake when you want control and selectivity. It's the right call when you're building a lakehouse, want data in an open format, or only need to share specific slices of your operational data.
Use data mirroring when you want simplicity and completeness. It's the right call when your analytics team needs full tables kept in sync and nobody wants to own a pipeline.
You can use both. A pattern we like:
- Mirror the core operational tables (users, orders, products) for dashboards and reporting
- Use pg_lake for everything more specialized: aggregated summaries, filtered datasets for ML training, or archiving historical data
Related Resources
Deep dive into the pg_lake extension: architecture, configuration, and use cases.
Step-by-step quickstart for setting up pg_lake data sync.
Short video walkthrough of the data mirroring feature.
Full documentation for Snowflake Postgres features and configuration.
This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances
