pg_lake: Postgres Meets the Data Lakehouse

Snowflake for Developers/Postgres/pg_lake: Postgres Meets the Data Lakehouse

On this page

pg_lake: Postgres Meets the Data Lakehouse

Postgres

Elizabeth Christensen

What is pg_lake?

pg_lake is an open source Postgres extension from Snowflake (released November 2025) that connects your transactional database to the data lakehouse. You can create and query Apache Iceberg tables, and read and write Parquet, CSV, and JSON files in object storage, all from regular Postgres SQL.

Key Capabilities

pg_lake adds a new table type: CREATE TABLE ... USING iceberg gives you a real Iceberg table that you query and modify with normal SQL. Analytics engines that speak Iceberg can read it too.

It also reads files in place. Point a foreign table at Parquet, CSV, or JSON files in cloud object storage and query them as if they were local.

And COPY works with object storage, so exporting a query result to Parquet is one statement.

The open source project works with S3-compatible storage, Google Cloud Storage, and Azure Blob Storage. In Snowflake Postgres, pg_lake uses Snowflake-managed storage, or your own S3 bucket or Azure container.

Why This Matters

The usual way to get operational data into a lakehouse is an ETL pipeline that somebody has to babysit. With pg_lake, Postgres writes the open format itself, so there's one less system in the middle.

Exporting Data to the Lakehouse

Use Cases for Export

Analytics offloading: move historical or aggregated data somewhere columnar engines can chew through it
Data sharing: publish datasets in open formats that other tools can read
Archival: move cold data out of your hot tables while keeping it queryable

Export to Parquet

-- Export a table to Parquet in S3
COPY (SELECT * FROM orders)
TO 's3://my-bucket/exports/orders.parquet';

pg_lake infers the format from the file extension. Swap in .csv or .json (or add a WITH (format ...) option) for other formats. Since the source is a query, you can filter, join, or aggregate on the way out. Parquet is columnar and compresses well, so analytical reads on the other end are fast.

Write to Iceberg

-- Create an Iceberg table and load it from an existing table
CREATE TABLE events_iceberg (LIKE events) USING iceberg;

INSERT INTO events_iceberg
SELECT * FROM events;

An Iceberg table behaves like any other Postgres table. You can INSERT, UPDATE, DELETE, and query it with transactions. Under the hood, the data lives in Parquet files, and Iceberg adds table-level features on top: schema evolution, snapshots for time travel, partition evolution, and atomic commits.

Incremental Exports

For ongoing sync, you don't want to re-export the whole table every time. Use pg_incremental, which runs on pg_cron, to track a watermark and only process rows added since the last run:

CREATE EXTENSION IF NOT EXISTS pg_incremental CASCADE;

-- A BRIN index on the watermark column keeps each run cheap
CREATE INDEX ON events USING brin (event_id);

-- Every run appends only the new range of event_id values
SELECT incremental.create_sequence_pipeline(
  'events-to-iceberg',
  'events',
  $$
    INSERT INTO events_iceberg
    SELECT * FROM events
    WHERE event_id BETWEEN $1 AND $2
  $$
);

Don't skip the index. Without it, every pipeline run is a sequential scan, and on a busy table that won't keep up. BRIN is tiny and works well when rows arrive in roughly sequential order, which is what you get with events and time-series data. pg_incremental also has time-interval pipelines if a timestamp is a better watermark for you.

aside negative Large initial exports take a while and generate a lot of I/O. Run the first bulk load during a low-traffic window.

Querying External Data from Postgres

pg_lake includes a foreign data wrapper for files in cloud storage. Queries against these foreign tables are pushed down and run with a columnar engine suited to the file format.

Create a Foreign Table

-- Create a foreign table over Parquet files in S3
CREATE FOREIGN TABLE historical_orders (
  order_id    bigint,
  customer_id bigint,
  total       numeric(10,2),
  created_at  timestamptz
)
SERVER pg_lake
OPTIONS (path 's3://my-bucket/archive/orders/*.parquet');

You can also leave the column list empty and let pg_lake infer the columns from the files.

Join Operational and Historical Data

This is where it gets useful. Join hot operational data in Postgres with cold historical data in the lake:

-- Join operational and historical data
SELECT c.name, sum(o.total) AS lifetime_value
FROM customers c
JOIN historical_orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY lifetime_value DESC;

customers is a local Postgres table and historical_orders is Parquet files sitting in S3. You don't need to load anything first.

Supported Formats

Format	Read	Write	Features
Iceberg	Yes	Yes	Schema evolution, time travel, partition pruning
Parquet	Yes	Yes	Columnar, compressed, widely supported
CSV	Yes	Yes	Simple interchange
JSON	Yes	Yes	Semi-structured data

Bidirectional Pipelines with Snowflake

pg_lake in Snowflake Postgres needs a STANDARD or HIGHMEM instance. Burstable instances don't support it. There are three ways to set it up.

Shared Iceberg is where most people should start. Postgres writes Iceberg tables and acts as the catalog. Snowflake reads them through a catalog integration (CATALOG_SOURCE = SNOWFLAKE_POSTGRES) using vended credentials, so you don't configure a bucket, IAM roles, or an external volume. These tables are read-only in Snowflake. You can reference them one at a time or create a catalog-linked database that discovers them automatically.

Stages give you file exchange in both directions. A POSTGRES_INTERNAL_STORAGE storage integration exposes your instance's managed storage as a Snowflake stage, and COPY FILES moves files between it and other stages.

Customer-managed storage is for when you need your own S3 bucket or Azure container. Attach a POSTGRES_EXTERNAL_STORAGE integration to the instance for pg_lake, and use a regular EXTERNAL_STAGE integration so Snowflake can read the same location.

Postgres to Snowflake

Send operational data to Snowflake for analytics, machine learning, and reporting. Here's the shared Iceberg setup on the Snowflake side:

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;

CREATE OR REPLACE ICEBERG TABLE events_from_postgres
  CATALOG = 'my_postgres_catalog'
  CATALOG_TABLE_NAME = 'events_iceberg'
  AUTO_REFRESH = TRUE;

Common patterns:

Incremental sync of transactional data for dashboards
Periodic snapshots for ML training datasets
Event streams for behavioral analytics

Snowflake to Postgres

Send enriched data and ML predictions back to Postgres for your application, using a stage or your own storage:

Enriched customer profiles from Snowflake ML models
Aggregated metrics for application display
Predictions and scores that drive application logic

Apache Iceberg as the Interchange Format

Postgres (through pg_lake) and Snowflake both read and write Iceberg, which makes for a clean integration point:

Postgres writes Iceberg tables
Snowflake reads those tables for analytics and ML
Snowflake writes results back out as Parquet or Iceberg in a shared location
Postgres reads the results with COPY or foreign tables

It's an open format in both directions, so there's no proprietary connector to keep alive.

If you want full Postgres tables kept in sync with Snowflake without managing exports yourself, look at data mirroring. It replicates Postgres tables into Snowflake continuously and is built on pg_lake under the hood.

Common Use Cases

IoT and Time-Series

Postgres handles real-time ingestion and queries on recent data. pg_lake archives older partitions to the lake, where they stay queryable and cost less to keep.

-- Archive a partition older than 90 days into an Iceberg table
INSERT INTO sensor_readings_archive
SELECT * FROM sensor_readings_2026_02;

-- Drop the local partition after confirming the row counts match
DROP TABLE sensor_readings_2026_02;

Here sensor_readings_archive is an Iceberg table created with USING iceberg. Check counts on both sides before you drop anything.

Feature Stores for ML

Keep training data and feature history in Iceberg for ML pipelines, and keep the serving features in Postgres for low-latency inference.

Regulatory Archival

Archive data to Iceberg tables in object storage to meet retention requirements. Iceberg snapshots give you point-in-time access for audits.

Multi-System Analytics

Because Iceberg is an open table format, Postgres, Snowflake, Spark, Trino, and other Iceberg-compatible engines can all work with the same data. For engines outside Snowflake, use customer-managed storage.

Conclusion

Next Steps

Explore the Scaling guide for partitioning strategies that pair well with lakehouse archival
Read the AI in Postgres guide to see how embeddings can move between Postgres and Snowflake
Compare pg_lake and data mirroring in Connect with Snowflake Analytics
Check out Snowflake Postgres Getting Started for initial setup

Updated Oct 27, 2026

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances