Connect with Snowflake Analytics
Snowflake Postgres runs your transactional workloads in a fully managed PostgreSQL environment. But operational data doesn't stay operational forever — teams need it for analytics, ML training, dashboards, and reporting. Snowflake gives you two built-in options to move data from Postgres to the Snowflake analytics engine, each designed for different use cases.
| pg_lake (Shared Iceberg) | Data Mirroring | |
|---|---|---|
| What moves | Tables you choose, on your schedule | Entire tables, continuously |
| Format | Apache Iceberg (open, portable) | Snowflake-managed replication |
| Control | You decide what to export and when | Automatic — keeps tables in sync |
| Best for | Selective sharing, lakehouse workflows, multi-engine access | Full-table analytics, dashboards, real-time reporting |
Option 1: pg_lake and Shared Iceberg
pg_lake is a Postgres extension that writes data from your Postgres tables directly to Apache Iceberg tables in object storage. Once in Iceberg format, the data is immediately queryable from Snowflake Analytics — and from any other Iceberg-compatible engine (Spark, Trino, Flink).
How It Works
- You control the export — call pg_lake functions from Postgres to write specific tables (or queries) to Iceberg.
- Data lands in your object storage — Iceberg tables live in S3 (or compatible storage) that you own.
- Snowflake reads the Iceberg tables — create an Iceberg table in Snowflake pointing to the same storage location. No data copying — Snowflake reads the files directly.
When to Use pg_lake
- You want fine-grained control over which data gets shared and when
- You need the data accessible from multiple engines (Snowflake, Spark, Trino) via the open Iceberg format
- You're building a lakehouse architecture where Postgres is the operational layer and Iceberg is the analytical layer
- You want to archive or offload historical data from Postgres without deleting it
- You need to transform or filter data before it reaches analytics
Example Workflow
-- In Postgres: export the orders table to Iceberg SELECT pg_lake.write_to_iceberg( 'public.orders', 's3://my-data-lake/iceberg/orders/', partition_by => ARRAY['date(created_at)'] );
-- In Snowflake: query the same data CREATE ICEBERG TABLE orders_from_postgres EXTERNAL_VOLUME = 'my_ext_volume' CATALOG = 'SNOWFLAKE' BASE_LOCATION = 'iceberg/orders/'; SELECT * FROM orders_from_postgres WHERE created_at >= '2026-01-01';
Key Characteristics
- Open format — data is stored as Apache Iceberg, readable by any compatible engine
- You own the storage — files live in your S3 bucket, under your control
- Selective — export exactly the tables, columns, or filtered subsets you need
- On your schedule — run exports on demand, via cron, or triggered by application events
- Bidirectional — pg_lake can also read Iceberg tables back into Postgres as foreign tables
Option 2: Data Mirroring
Data mirroring is Snowflake's built-in replication feature that automatically keeps Snowflake Analytics tables in sync with your Snowflake Postgres tables. You select which tables to mirror, and Snowflake handles the rest — initial load, ongoing change capture, and consistency.
How It Works
- Select tables to mirror — choose which Postgres tables should be replicated to Snowflake.
- Snowflake handles replication — changes are captured and applied automatically. No CDC tools, no Kafka, no Airflow.
- Query in Snowflake — mirrored tables appear as regular Snowflake tables, queryable with full warehouse power.
When to Use Data Mirroring
- You want whole tables available in Snowflake with minimal configuration
- You need continuous sync — analytics should reflect recent operational changes
- You're building dashboards or reports that need a complete, up-to-date copy of your Postgres data
- You prefer a managed, zero-maintenance approach over building your own pipelines
- You don't need the data to be accessible from non-Snowflake engines
Key Characteristics
- Fully managed — no pipelines to build or maintain
- Continuous — changes flow automatically as they happen in Postgres
- Complete tables — mirrors the full table, not a filtered subset
- Snowflake-native — mirrored data is stored as regular Snowflake tables with full query performance
- Simple setup — select tables and go, no storage configuration required
Choosing Between the Two
Use pg_lake when you need control, selectivity, or multi-engine access. It's the right choice when you're building a lakehouse architecture, want data in an open format, or need to share specific subsets of your operational data.
Use data mirroring when you want simplicity and completeness. It's the right choice when your analytics team needs full tables continuously synced to Snowflake without managing infrastructure.
You can use both together. A common pattern:
- Mirror your core operational tables (users, orders, products) for dashboards and reporting
- pg_lake for specialized exports — aggregated summaries, filtered datasets for ML training, or archival of historical data to your lakehouse
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
