Postgres Lakehouse Integration on Snowflake: 5 Powerful Ways

Most teams run two data worlds: PostgreSQL for the application, and a lakehouse of Parquet and Apache Iceberg files for analytics, joined by brittle export jobs that are always a little late and occasionally wrong. Postgres lakehouse integration removes that seam. With the open-source pg_lake extensions, PostgreSQL can query files in object storage, own transactional Iceberg tables, and hand those tables to Snowflake without a copy. This guide shows five ways we use it, with diagrams, working SQL and the limits you need to know first.

We wrote it for architects and DBAs evaluating the stack in late 2026: pg_lake on self-managed PostgreSQL 16, 17 or 18, and pg_lake on Snowflake Postgres, which reached general availability in February 2026.

Why Postgres lakehouse integration matters now

The classic pipeline (OLTP database, CDC or nightly dump, staging bucket, warehouse load) has three costs that compound. Every hop adds latency, so the dashboard is always behind the business. Every hop is a place where schema drift breaks silently. And every copy of the data is another thing to secure, govern and pay for.

Open table formats changed the economics. An Iceberg table is just Parquet files plus metadata in a bucket, and any engine that speaks Iceberg can read it. If the database that creates the data can also write it as Iceberg, inside its own transactions, the export job disappears. That is the idea behind pg_lake, and it is why integrating your data lakehouse with Postgres on Snowflake is now a design option rather than a science project.

What pg_lake is, in one paragraph

pg_lake is a set of PostgreSQL extensions, originally built by Crunchy Data and open-sourced by Snowflake under the Apache 2.0 licence in November 2025. It adds an Iceberg table access method, foreign tables over raw files, COPY to and from object storage, and a catalog that maps each Iceberg table to its current metadata file. The heavy columnar work is not done inside the Postgres backend: a separate process, pgduck_server, runs DuckDB and speaks the Postgres wire protocol locally, so a crash or memory spike in the analytical engine stays outside your database process.

Setting it up on self-managed PostgreSQL

On your own servers there are three steps: preload the extension framework, give pgduck_server a way to reach storage, and create the extension. The example below uses the cloud credential chain so no access keys ever land in a file.

# --- postgresql.conf : Postgres lakehouse integration prerequisites ---------------
# Self-managed PostgreSQL 16, 17 or 18. pg_extension_base is pg_lake's extension
# framework and must be preloaded, so this change needs a restart.
shared_preload_libraries = 'pg_extension_base'

# --- /etc/pgduck/init.sql : run by pgduck_server at start-up ----------------------
# A DuckDB secret that resolves credentials from the cloud credential chain
# (instance profile, workload identity or environment). No keys are written here.
CREATE SECRET lake_s3 (TYPE s3, PROVIDER credential_chain);

# --- start the DuckDB-backed query process next to Postgres -----------------------
# Run it under systemd in production. Memory limit and cache directory are
# illustrative values: size them to the host, not to this example.
pgduck_server --memory_limit 16GB --cache_dir /var/cache/pgduck --init_file_path /etc/pgduck/init.sql

-- Enable the whole extension family (table access method, Iceberg, COPY, engine).
CREATE EXTENSION pg_lake CASCADE;

-- Default home for new Iceberg tables in this database. A per-table
-- WITH (location = ...) still overrides it. Takes effect for new sessions.
ALTER DATABASE app SET pg_lake_iceberg.default_location_prefix = 's3://acme-lake/postgres';

Run pgduck_server on the same host as Postgres and size its memory limit deliberately. It shares the machine with shared_buffers and the OS page cache, and an analytical query that spills is far cheaper than an OLTP database that swaps.

Way 1: query lake files in place, without loading them

The fastest win is reading the landing zone directly. A foreign table with an empty column list lets pg_lake infer the schema from Parquet, CSV or newline-delimited JSON files, and lake_file.list shows what has arrived before you query it. Analysts can join yesterday's clickstream with live customer rows in one statement, and nothing is copied into the database.

-- Way 1: query landing-zone files where they already sit.
-- What has arrived this month? Wildcards are expanded against object storage.
SELECT path
FROM lake_file.list('s3://acme-landing/clickstream/2026/10/**/*.parquet');

-- Empty column list: pg_lake infers the columns from the files themselves.
-- filename 'true' adds a column holding each row's source file, which gives
-- free lineage when a bad file has to be traced and replayed.
CREATE FOREIGN TABLE landing_clicks ()
  SERVER pg_lake
  OPTIONS (path 's3://acme-landing/clickstream/2026/10/**/*.parquet', filename 'true');

-- Always read the inferred definition before you build queries on it.
\d landing_clicks

-- Lake data joined with live OLTP rows. The Parquet scan and its filter run in
-- the DuckDB engine; check with EXPLAIN which parts of a plan are delegated.
SELECT c.customer_id,
       count(*)          AS clicks_last_7d,
       max(o.created_at) AS last_order_at
FROM landing_clicks AS l
JOIN customers      AS c ON c.customer_id = l.customer_id
LEFT JOIN orders    AS o ON o.customer_id = c.customer_id
WHERE l.event_time >= now() - interval '7 days'
GROUP BY c.customer_id
ORDER BY clicks_last_7d DESC
LIMIT 20;

Two habits keep this safe in production. Read the inferred definition with \d before writing queries against it, because a single malformed file can change a column type. And keep path patterns narrow; a wildcard over an entire bucket makes every query list and open thousands of objects.

Way 2: Iceberg tables that live inside Postgres transactions

Creating a table with USING iceberg gives you a table that looks like any other in psql but stores its data as Parquet in object storage, with Postgres as its Iceberg catalog. INSERT, UPDATE and DELETE work, including modifying CTEs and joins in updates, and changes become visible at COMMIT like any other Postgres write.

-- Way 2: an Iceberg table that Postgres owns and catalogs.
-- Partition transforms are Iceberg's, so every Iceberg reader can prune with them.
CREATE TABLE sales.order_events (
    event_id     bigint,
    order_id     bigint,
    customer_id  bigint,
    event_type   text,
    amount       numeric(12,2),
    event_time   timestamptz
) USING iceberg
  WITH (partition_by = 'day(event_time), bucket(16, customer_id)');

-- Ordinary SQL inside an ordinary transaction: other sessions, and other
-- engines, see all of these changes at COMMIT or none of them.
BEGIN;
INSERT INTO sales.order_events
SELECT event_id, order_id, customer_id, event_type, amount, event_time
FROM sales.order_events_staging;

UPDATE sales.order_events
SET    amount = 0
WHERE  event_type = 'cancelled' AND amount <> 0;
COMMIT;

-- The catalog: each Iceberg table and the metadata file of its current snapshot.
SELECT table_name, metadata_location FROM iceberg_tables;

-- Adding a column is a metadata change in Iceberg; no data file is rewritten.
ALTER TABLE sales.order_events ADD COLUMN channel text;

The diagram explains a property worth remembering during incident reviews. Data files can be written before the transaction finishes, but the catalog pointer only moves at COMMIT. If the session rolls back or the server crashes, every reader, in Postgres or in another engine, keeps seeing the previous snapshot.

Choose partition transforms for the readers you expect. day() on an event timestamp lets Snowflake and Spark prune by date range; bucket() on a high-cardinality key spreads writes and speeds point lookups. Over-partitioning creates many small files, which is the most common performance problem in any Iceberg estate.

Way 3: hot rows in heap, cold rows in Iceberg

This is the pattern we reach for most often, and it needs no change to the application. Recent orders stay in the indexed heap table, where row locks, INSERT ... ON CONFLICT and MERGE work as always. A scheduled function moves rows past a retention boundary into a partitioned Iceberg table in a single transaction, so an order is never in both tiers and never in neither.

-- Way 3, part 1: the cold tier and a view that hides the split from readers.
-- max_snapshot_age keeps 7 days of snapshots (seconds) as a recovery window.
CREATE TABLE sales.orders_archive (
    order_id      bigint,
    customer_id   bigint,
    status        text,
    total_amount  numeric(12,2),
    created_at    timestamptz
) USING iceberg
  WITH (partition_by = 'month(created_at)', max_snapshot_age = 604800);

CREATE VIEW sales.orders_all AS
SELECT order_id, customer_id, status, total_amount, created_at FROM sales.orders
UNION ALL
SELECT order_id, customer_id, status, total_amount, created_at FROM sales.orders_archive;

-- Way 3, part 2: move aged rows from heap to Iceberg in ONE transaction.
-- Safe by default: without p_dry_run => false it only counts and changes nothing.
CREATE OR REPLACE FUNCTION sales.archive_orders(
    p_cutoff  timestamptz,
    p_dry_run boolean DEFAULT true)
RETURNS TABLE (rows_qualifying bigint, rows_moved bigint)
LANGUAGE plpgsql AS $$
DECLARE
  v_qualifying bigint;
  v_moved      bigint := 0;
BEGIN
  -- Guard: whatever the caller passes, never archive the last 30 days.
  IF p_cutoff > now() - interval '30 days' THEN
    RAISE EXCEPTION 'cutoff % is too recent; refusing to archive', p_cutoff;
  END IF;

  -- Verification before: how many rows qualify right now.
  SELECT count(*) INTO v_qualifying
  FROM sales.orders
  WHERE created_at < p_cutoff;

  IF NOT p_dry_run THEN
    -- DELETE ... RETURNING feeds the Iceberg INSERT; both commit or neither does.
    WITH moved AS (
      DELETE FROM sales.orders
      WHERE created_at < p_cutoff
      RETURNING order_id, customer_id, status, total_amount, created_at
    )
    INSERT INTO sales.orders_archive (order_id, customer_id, status, total_amount, created_at)
    SELECT order_id, customer_id, status, total_amount, created_at
    FROM moved;
    GET DIAGNOSTICS v_moved = ROW_COUNT;

    -- Validation after: rows written to Iceberg must equal rows counted.
    -- Raising here rolls back the DELETE and the Iceberg write together.
    IF v_moved <> v_qualifying THEN
      RAISE EXCEPTION 'moved % rows but % qualified; rolling back', v_moved, v_qualifying;
    END IF;
  END IF;

  RETURN QUERY SELECT v_qualifying, v_moved;
END;
$$;

-- 1. Dry run: read the numbers first.
SELECT * FROM sales.archive_orders(date_trunc('month', now()) - interval '3 months');

-- 2. Real run, deliberately, after the dry run looks right.
SELECT * FROM sales.archive_orders(date_trunc('month', now()) - interval '3 months', false);

-- 3. Nightly schedule where pg_cron is available.
SELECT cron.schedule('archive-orders', '15 2 * * *',
  $job$SELECT * FROM sales.archive_orders(date_trunc('month', now()) - interval '3 months', false)$job$);

The function is deliberately conservative. It refuses cutoffs inside the last 30 days, counts the qualifying rows before acting, does nothing unless the caller passes p_dry_run => false, and raises an error, rolling back both the DELETE and the Iceberg write, if the moved count differs from the counted one. Keep that shape for any destructive data movement you automate.

After the first run, vacuum the heap table as usual: the deleted rows become free space, and indexes on the hot table shrink back towards the size of the working set, which is where the OLTP latency gain comes from.

Way 4: COPY as the lakehouse on-ramp and off-ramp

pg_lake extends the COPY command you already know to object storage. Exports pick the format from the file extension, imports handle gzip and zstd compression, and two table options create a table definition, or a table with its data, straight from a file.

-- Way 4: COPY as the lakehouse on-ramp and off-ramp.
-- Export yesterday's orders as Parquet; the format follows the file extension.
COPY (
  SELECT * FROM sales.orders
  WHERE created_at >= current_date - 1 AND created_at < current_date
) TO 's3://acme-lake/exports/orders/2026-10-04.parquet';

-- Load a partner's gzip-compressed CSV straight into a heap staging table.
COPY sales.partner_returns
FROM 's3://acme-landing/partners/returns_2026-10-04.csv.gz'
WITH (header true);

-- Let pg_lake derive a table definition from a file (the column list stays empty) ...
CREATE TABLE sales.supplier_catalog ()
  WITH (definition_from = 's3://acme-landing/suppliers/catalog.parquet');

-- ... or create and load in one statement.
CREATE TABLE sales.supplier_catalog_oct ()
  WITH (load_from = 's3://acme-landing/suppliers/catalog.parquet');

Use COPY for hand-offs with partners and other teams, and Iceberg tables for anything that must stay queryable and consistent over time. A folder of exported Parquet files has no snapshots, no schema history and no transactional guarantees.

Way 5: integrating your data lakehouse with Postgres on Snowflake

On Snowflake Postgres, pg_lake ships as a managed extension and the last mile to the warehouse is a catalog integration. Postgres writes Iceberg tables to storage that Snowflake manages; Snowflake reads them through a catalog integration of source SNOWFLAKE_POSTGRES, which uses vended credentials, so you do not configure an external volume or an IAM role for this shared-Iceberg path.

-- Way 5, step 1 (Snowflake SQL): a Snowflake Postgres instance that can run pg_lake.
-- pg_lake data movement needs a STANDARD or HIGH MEMORY tier, not BURSTABLE.
CREATE POSTGRES INSTANCE orders_pg
  COMPUTE_FAMILY           = 'STANDARD_M'
  STORAGE_SIZE_GB          = 200
  AUTHENTICATION_AUTHORITY = POSTGRES
  POSTGRES_VERSION         = 18
  HIGH_AVAILABILITY        = TRUE;

-- Step 2 (inside the Snowflake Postgres instance, through psql or any Postgres driver).
-- Credentials come from your secret manager, e.g. PGPASSWORD=${ORDERS_PG_PASSWORD}.
CREATE EXTENSION pg_lake CASCADE;

-- With shared Iceberg, no location or IAM setup is needed: storage is managed for you.
CREATE TABLE public.orders_lake (
    order_id      bigint,
    customer_id   bigint,
    status        text,
    total_amount  numeric(12,2),
    created_at    timestamptz
) USING iceberg
  WITH (partition_by = 'day(created_at)');

INSERT INTO public.orders_lake
SELECT order_id, customer_id, status, total_amount, created_at
FROM public.orders
WHERE created_at < current_date;

-- Step 3 (Snowflake SQL): integrate the Postgres catalog, using vended credentials.
CREATE OR REPLACE CATALOG INTEGRATION orders_pg_catalog
  CATALOG_SOURCE    = SNOWFLAKE_POSTGRES
  TABLE_FORMAT      = ICEBERG
  CATALOG_NAMESPACE = 'public'              -- the Postgres schema
  REST_CONFIG = (
    POSTGRES_INSTANCE      = 'orders_pg'
    CATALOG_NAME           = 'postgres'     -- the Postgres database
    ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS
  )
  ENABLED = TRUE;

-- Step 4: a read-only Snowflake Iceberg table over the Postgres-managed table.
CREATE OR REPLACE ICEBERG TABLE analytics.public.orders_from_pg
  CATALOG            = 'orders_pg_catalog'
  CATALOG_TABLE_NAME = 'orders_lake'
  AUTO_REFRESH       = TRUE;              -- picks up new snapshots by polling metadata

-- Step 5: warehouse analytics, joined with native Snowflake tables.
SELECT d.region,
       DATE_TRUNC('day', o.created_at) AS order_day,
       SUM(o.total_amount)             AS revenue
FROM analytics.public.orders_from_pg AS o
JOIN analytics.public.dim_customer   AS d ON d.customer_id = o.customer_id
GROUP BY d.region, order_day
ORDER BY order_day DESC, revenue DESC;

-- Freshness check: compare this with max(created_at) in Postgres.
SELECT MAX(created_at) AS newest_row_seen_by_snowflake
FROM analytics.public.orders_from_pg;

Three facts shape the design. First, Postgres-managed Iceberg tables are read-only in Snowflake; any write, correction or backfill goes through Postgres. Second, auto-refresh polls metadata rather than reacting to change notifications, so end-to-end freshness is the Postgres commit plus the refresh interval. Third, at the time of writing, the Snowflake Postgres catalog integration is documented for AWS only, and pg_lake data movement needs a STANDARD or HIGH MEMORY instance.

Choosing a data movement option on Snowflake Postgres

Operating pg_lake in production

Iceberg tables need housekeeping that heap tables do not. Frequent small commits create many small files, and old snapshots keep files alive. VACUUM (ICEBERG) compacts files and expires snapshots older than the retention setting, and pg_lake runs an Iceberg autovacuum by default, which a table can switch off with autovacuum_enabled = 'false' when a batch window is preferred.

-- Running Postgres lakehouse integration day to day.
-- 1. Compaction plus snapshot expiry. Small files from frequent commits are
--    merged, and snapshots older than the retention setting are expired.
VACUUM (ICEBERG);

-- 2. The database-wide retention default, in seconds (1800 unless changed).
SHOW pg_lake_iceberg.max_snapshot_age;

-- 3. Where each table's current metadata lives: the first thing to check when
--    another engine reports it cannot see a recent write.
SELECT table_name, metadata_location
FROM iceberg_tables
ORDER BY table_name;

-- 4. File-count drift under a table's location (path from metadata_location):
--    a steadily growing count means commits are outpacing compaction.
SELECT count(*) AS parquet_files
FROM lake_file.list('s3://acme-lake/postgres/sales/order_events/**/*.parquet');

Watch three numbers: Parquet files per table (rising means commits outpace compaction), the age of each table's newest snapshot compared with the source rows (rising means a writer has stopped), and pgduck_server memory and cache-directory usage on the host. Include the object-storage bucket in your backup and disaster-recovery design; the Postgres catalog and the files must be recovered to a consistent point together.

Limits to plan around

• Iceberg tables do not support MERGE, INSERT ... ON CONFLICT or SELECT ... FOR UPDATE; keep upsert-heavy tables in heap and move them with the tiering pattern.
• ALTER TABLE ... ADD COLUMN on Iceberg tables cannot add generated columns, serial types or constraints.
• Snowflake sees Postgres-managed tables read-only, refreshed by polling.
• pgduck_server is a required companion process: monitor it, restart it with Postgres and include it in capacity planning.

When pg_lake fits, and when it does not

We are vendor-neutral. Our PostgreSQL consulting and Snowflake consulting teams also deliver lakehouses on Databricks and plain Iceberg, and we will tell you when pg_lake is not the right tool. For the source code and full documentation, see the pg_lake repository, the Apache Iceberg table specification and the Snowflake Postgres pg_lake documentation.

Frequently asked questions

What is pg_lake?

pg_lake is a set of open-source PostgreSQL extensions, Apache 2.0 licensed, that let Postgres query Parquet, CSV and JSON files in object storage, create and manage Apache Iceberg tables with Postgres as the catalog, and use COPY to move data to and from S3, Azure Blob and Google Cloud Storage.

Which PostgreSQL versions does pg_lake support?

Self-managed builds require PostgreSQL 16, 17 or 18. Snowflake Postgres instances can also be created on versions 16, 17 and 18, with pg_lake available as a managed extension.

Can Snowflake write to Iceberg tables created by Postgres?

No. Postgres-managed Iceberg tables are read-only in Snowflake. Writes, corrections and backfills go through Postgres, and Snowflake picks up new snapshots through auto-refresh, which polls the table metadata.

Does Postgres lakehouse integration replace a data warehouse?

Usually not. pg_lake removes the export pipeline and lets Postgres write open-format tables, but large concurrent BI workloads still belong on a warehouse engine reading the same Iceberg tables. The pattern makes Postgres a first-class producer for the lakehouse, not its only consumer.

Are Iceberg writes from Postgres transactional?

Yes. The Iceberg catalog pointer changes only when the Postgres transaction commits, so other sessions and other engines see all of a transaction's changes or none of them, and a rollback leaves the table at its previous snapshot.

What SQL is not supported on pg_lake Iceberg tables?

MERGE, INSERT ... ON CONFLICT and SELECT ... FOR UPDATE are not supported on Iceberg tables. Keep upsert-heavy data in heap tables and move settled rows to Iceberg with a transactional archive step.

All SQL, configuration and shell examples in this post are illustrative, pinned to pg_lake as released for PostgreSQL 16 to 18 and to Snowflake Postgres documentation current in October 2026; names, paths and sizes are examples. Test every change in a non-production environment first, verify backups of both the Postgres catalog and the object storage by restoring them, and maintain a robust disaster-recovery posture before applying anything to production.

Planning Postgres lakehouse integration on Snowflake? Book a working session with a MinervaDB principal architect, or email contact@minervadb.com.