# DuckLake destination

Replicate Supabase Postgres tables to DuckLake.

Configure DuckLake as a Supabase Pipelines destination.

Note: Supabase Pipelines is currently in public alpha. Features and behavior may change as we continue developing the product.

The DuckLake destination is in private alpha and available only to approved organizations. [Request access](https://supabase.com/go/supabase-pipelines-new-destinations) before following this guide.

Replicate Postgres tables to [DuckLake](https://ducklake.select/) for current-state lakehouse queries. [Prepare resources](#understand-the-ducklake-components), [configure the destination](#choose-a-configuration-mode), then [query replicated data](#query-the-destination).

## Source table requirements

Insert-only tables don't require a primary key or replica identity. Updates and deletes require a published Postgres row identity. See [supported replica identities](#replica-identity).

## Prepare DuckLake resources \[#understand-the-ducklake-components]

Prepare a Postgres catalog, object storage, and a compatible query engine:

| Component        | Purpose                                                                                                                             |
| ---------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| Postgres catalog | Stores DuckLake schemas, snapshots, file references, and other metadata. This is not a copy of the replicated table data.           |
| Object storage   | Stores Parquet data and delete files under an `s3://` path.                                                                         |
| Query engine     | Reads the catalog and object storage. Pipelines doesn't include a DuckLake query endpoint; use DuckDB or another compatible engine. |

You can query replicated tables, but treat them and their underlying catalog and object-storage state as read-only. Writes outside Pipelines can conflict with replication and background maintenance.

## Configure DuckLake as a destination \[#choose-a-configuration-mode]

Choose a mode below for its resource requirements and configuration steps.

Follow [Set up Pipelines](https://supabase.com/docs/guides/database/replication/pipelines#setup-overview), select **DuckLake**, then choose **Use Supabase** or **Custom parameters** for the catalog and storage.

### Use Supabase

Use this mode to back the DuckLake with Supabase projects. Pipelines provisions its own catalog and object-storage credentials when you create the destination.

Before you begin:

- Choose active, healthy, non-branch projects from the same organization for the catalog and storage. You can use the same project for both.
- Make sure your organization role can administer SQL in the catalog project and Storage in the storage project.
- Create a private standard Storage bucket, or create one from the destination form.
- Choose a metadata schema unique to this DuckLake. Use only letters, numbers, and underscores.
- Keep catalog and storage in the same region when possible, near the [managed pipeline region](https://supabase.com/docs/guides/database/replication/pipelines#region).

To configure the destination:

1. Select **Use Supabase**.
2. Choose the **Catalog project**, **Pool size**, and **Metadata schema**. Pool size allows `1` to `6` concurrent DuckDB connections; the default is `4`.
3. Choose the **Storage project** and private **Bucket**.
4. Click **Create and start pipeline** and complete the validation and cost confirmations.

Credential-provisioning warnings are expected before creation: catalog and Storage credentials are provisioned when you save the destination. Review the selected resources before proceeding.

### Custom parameters

Use this mode with a Postgres catalog and S3-compatible object storage that you control.

Prepare the following resources:

1. A Postgres database reachable from managed Pipelines. Create a dedicated user that can create and modify the DuckLake metadata schema and its tables.
2. An S3-compatible bucket and a dedicated prefix for this DuckLake.
3. Object-storage credentials that can list, read, write, and delete objects under that prefix. Delete access is required for managed file cleanup.

Use a new catalog metadata schema and data prefix for each destination. Reusing an existing schema or prefix can mix the metadata or files of different DuckLakes.

Configure these fields in the destination form:

- **Catalog URL**: A `postgres://` or `postgresql://` connection URL, including credentials and an `sslmode` appropriate for your provider
- **Data path**: An `s3://<bucket>/<prefix>` URL
- **Pool size**: From `1` to `6`; the default is `4`
- **S3 access key ID** and **S3 secret access key**: A credential pair for the data path
- **S3 region**: The storage provider's region
- **S3 endpoint**: The provider endpoint without `http://` or `https://`
- **S3 URL style**: `path` for Supabase Storage and many S3-compatible providers, or `vhost` for virtual-host-style addressing
- **Use SSL**: Keep enabled for production endpoints
- **Metadata schema**: A unique Postgres schema for DuckLake metadata, using only letters, numbers, and underscores

Click **Create and start pipeline** and complete the validation and cost confirmations.

The catalog URL and storage credentials are stored as secrets and aren't returned after creation. When editing the destination, leave a secret field empty to keep its stored value, or enter a new value to replace it.

## How replication works

Pipelines creates current-state tables, copies rows according to the [initial sync selection](https://supabase.com/docs/guides/database/replication/pipelines#choosing-which-tables-to-copy), then applies published changes and supported schema changes.

Source schema and table names are preserved, while ASCII uppercase letters in column names are converted to lowercase. DuckDB compares schema, table, and column identifiers without ASCII case distinctions, so don't publish names that differ only by ASCII case. Use distinct lowercase column names. Source column names matching the generated `supabase_etl_ducklake_dropped_<ordinal>_<hash>` shape are reserved for schema-change recovery.

A source `TRUNCATE` truncates the DuckLake table. A [table restart](https://supabase.com/docs/guides/database/replication/pipelines-monitoring#restarting-tables) drops and recreates it. [Removing a table from the publication](https://supabase.com/docs/guides/database/replication/pipelines#removing-tables-from-replication) leaves its destination data in place.

## Query replicated data \[#query-the-destination]

Connect DuckDB with its `ducklake` extension, or another compatible engine, to the same catalog and storage path. Query through the catalog; reading raw Parquet files can miss inlined changes, delete files, and the current snapshot.

For **Use Supabase** mode, create separate read credentials for the selected catalog and Storage projects. The writer credentials generated for Pipelines aren't exposed. For **Custom parameters**, use separate read-only credentials when your catalog and storage provider support them.

After attaching the catalog under an alias such as `my_ducklake`, source schemas and tables are available as qualified DuckLake tables:

```sql
select *
from my_ducklake.public.orders;
```

See the [DuckDB connection guide](https://ducklake.select/docs/stable/duckdb/usage/connecting) for the current `ducklake` extension and `ATTACH` syntax. If your query client can't read a Supabase-backed catalog during private alpha, [contact support](https://supabase.com/dashboard/support/new).

## Replica identity

| Source table setting                             | Guidance                                                                                                               |
| ------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------- |
| `REPLICA IDENTITY DEFAULT` with a primary key    | Include every primary-key column in the publication.                                                                   |
| `REPLICA IDENTITY USING INDEX`                   | Include every column from the replica-identity index in the publication.                                               |
| `REPLICA IDENTITY FULL`                          | Use when the table has no suitable key or the full old row is required. This increases source WAL volume.              |
| `REPLICA IDENTITY NOTHING`                       | Insert-only. Inserts replicate, but updates and deletes don't contain an identity that DuckLake can match safely.      |
| `REPLICA IDENTITY DEFAULT` without a primary key | Insert-only. Add a key, configure a replica-identity index, or use full identity before publishing updates or deletes. |

Replica-identity changes affect only new WAL. If retained updates or deletes still fail after a pipeline restart, [restart replication for the affected table](https://supabase.com/docs/guides/database/replication/pipelines-monitoring#restarting-tables).

## Type mapping

Pipelines creates DuckLake columns with these mappings:

| Postgres type                          | DuckLake type                     |
| -------------------------------------- | --------------------------------- |
| `boolean`                              | `boolean`                         |
| `smallint`                             | `smallint`                        |
| `integer`                              | `integer`                         |
| `bigint`                               | `bigint`                          |
| `real`                                 | `float`                           |
| `double precision`                     | `double`                          |
| Compatible `numeric(precision, scale)` | `decimal(precision, scale)`       |
| `date`                                 | `date`                            |
| `time without time zone`               | `time`                            |
| `timestamp without time zone`          | `timestamp`                       |
| `timestamp with time zone`             | `timestamptz`                     |
| `uuid`                                 | `uuid`                            |
| `json` and `jsonb`                     | `json`                            |
| `oid`                                  | `ubigint`                         |
| `bytea`                                | `blob`                            |
| Supported Postgres arrays              | Corresponding DuckLake array type |
| Other scalar and custom types          | `varchar`                         |

DuckDB decimals support precision from `1` to `38` and a scale between `0` and the precision. Postgres `numeric` values declared outside that range, unconstrained `numeric`, and numeric types with unsupported modifiers are stored as `varchar` to preserve their serialized value.

Numeric arrays use `varchar[]`, even when their declared precision and scale would fit a scalar DuckLake decimal.

## Schema change support

Pipelines supports:

- Adding, renaming, or dropping columns
- Dropping `NOT NULL` from an existing column
- Adding, changing, or removing supported column defaults
- Adding or removing published columns on tracked tables

New columns are created as nullable so existing destination rows remain valid. Adding `NOT NULL` keeps an existing destination column nullable. Supported defaults are stored in DuckLake metadata; other defaults are skipped. Postgres still supplies the source values through replication.

Previously excluded columns are added without a default, leaving historical rows `NULL`. Removing a published column drops its destination values; adding it again does not restore them.

For type changes, unsupported changes, and interrupted schema changes, see the shared [schema-change behavior and recovery](https://supabase.com/docs/guides/database/replication/pipelines#schema-change-support).

## Troubleshooting

| Issue                                    | Resolution                                                                                                                                                  |
| ---------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Credential-provisioning warnings         | Expected in [Use Supabase](#use-supabase) mode before saving. Review the selected resources.                                                                |
| Catalog or storage validation fails      | Check [custom parameters](#custom-parameters), credentials, connectivity, and permissions for the configured prefix. Local `file://` paths are unsupported. |
| Metadata schema exists                   | Choose a new schema, unless intentionally reusing the same DuckLake and its corresponding data path.                                                        |
| Inserts work but updates or deletes fail | Check [replica identity and published columns](#source-table-requirements).                                                                                 |
| Queries omit changes or deleted rows     | [Query through the catalog](#query-the-destination) with credentials for both catalog and storage.                                                          |
| A schema change fails                    | Review [supported changes](#schema-change-support). Don't modify catalog tables or files manually.                                                          |

Use [pipeline monitoring](https://supabase.com/docs/guides/database/replication/pipelines-monitoring) to inspect errors. For unresolved failures, [contact support](https://supabase.com/dashboard/support/new) with the pipeline ID and error details.

## Additional resources

- [DuckLake documentation](https://ducklake.select/docs/stable/)
- [DuckLake architecture](https://ducklake.select/manifesto/)
- [Monitor pipeline status](https://supabase.com/docs/guides/database/replication/pipelines-monitoring)