Snowflake destination

Snowflake is a managed data platform. Supabase Pipelines writes an append-only change history for each replicated Postgres table to Snowflake.

Prepare Snowflake resources#

Create a dedicated Snowflake database, schema, role, and service user for Pipelines. Keep the schema otherwise empty to avoid ownership conflicts. Use unquoted identifiers for the service user and role. Pipelines converts the account and user names to uppercase during authentication.

Run the following as a Snowflake administrator. Change the example names as needed:

1
create role if not exists PIPELINES_ROLE;
2
3
create user if not exists PIPELINES_USER
4
  type = service;
5
6
grant role PIPELINES_ROLE to user PIPELINES_USER;
7
alter user PIPELINES_USER set default_role = PIPELINES_ROLE;
8
9
create database if not exists PIPELINES_DB;
10
create schema if not exists PIPELINES_DB.REPLICATED;
11
12
grant usage on database PIPELINES_DB to role PIPELINES_ROLE;
13
grant usage on schema PIPELINES_DB.REPLICATED to role PIPELINES_ROLE;
14
grant create table on schema PIPELINES_DB.REPLICATED to role PIPELINES_ROLE;
15
grant create pipe on schema PIPELINES_DB.REPLICATED to role PIPELINES_ROLE;

The pipeline role must own destination tables so it can alter, truncate, or drop them. Don't pre-create destination tables under another role.

The role also needs CREATE PIPE so Snowflake can create each table's managed default Snowpipe Streaming pipe, named <TABLE>-STREAMING, when Pipelines opens a channel. Pipelines does not need a virtual warehouse, stage, or manually created pipe.

Use a separate role and warehouse for downstream queries and transformations. The pipeline service role does not need query or transformation privileges.

Keep the SQL and streaming roles aligned#

Pipelines uses two Snowflake interfaces:

• SQL requests use the optional Role configured in the Dashboard. When Role is empty, they use the user's default role.
• Snowpipe Streaming uses the user's DEFAULT_ROLE. It does not use the optional Role setting.

Use one dedicated role for both interfaces. Set it as the service user's DEFAULT_ROLE. Leave Role empty in the Dashboard or set it to the same role. If the roles differ, SQL validation and table creation can succeed while streaming fails.

Generate a key pair#

Pipelines authenticates with an RSA key pair. Snowflake requires a key of at least 2048 bits and recommends PKCS #8. Generate an unencrypted private key:

1
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

To use a passphrase, generate an encrypted PKCS #8 private key:

1
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

Derive the public key:

1
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Register only the public-key body with the service user. Omit the BEGIN PUBLIC KEY and END PUBLIC KEY lines:

1
alter user PIPELINES_USER set rsa_public_key = '<public-key-body>';

Keep rsa_key.p8 and its passphrase secret. Don't commit them, paste them into logs, or send them to support. The Dashboard accepts unencrypted PKCS #1 or PKCS #8 keys, and encrypted PKCS #8 keys with a passphrase. It does not support encrypted PKCS #1 keys.

See Snowflake key-pair authentication to verify the public-key fingerprint and rotate keys with RSA_PUBLIC_KEY_2.

Find the account identifier#

Run this query in Snowflake:

1
select current_organization_name() || '-' || current_account_name();

Enter the result as Account ID, for example MYORG-MYACCOUNT. Do not enter a full URL or dotted locator-and-region hostname. Account IDs can contain up to 63 characters. Legacy one-part account locators are also accepted. See Snowflake account identifiers for details.

Configure Snowflake as a destination#

1. Navigate to the Database > Replication section of the Dashboard.
2. Click Add destination.
3. Select Snowflake. If it isn't available, request Early Access.
4. Select a Postgres publication and enter a destination name.
5. Enter the Snowflake settings:
   • Account ID: The organization and account identifier, such as MYORG-MYACCOUNT.
   • User: The dedicated unquoted service user, such as PIPELINES_USER.
   • Database: The destination database, such as PIPELINES_DB.
   • Schema: The dedicated destination schema, such as REPLICATED.
   • Role: Leave empty to use the user's DEFAULT_ROLE. If set, enter the same role.
   • Private key: The complete PEM-encoded private key, including its begin and end lines.
   • Private key passphrase: Required only for an encrypted PKCS #8 key.
6. Review the source table requirements and click Create and start pipeline.

Enter the database and schema identifiers exactly as stored in Snowflake. Unquoted identifiers are stored in uppercase. Managed Pipelines run in AWS eu-central-1 (Frankfurt). When possible, use a Snowflake account near Frankfurt.

How it works#

Pipelines uses Snowflake's SQL REST API to validate the database and schema, create, evolve, and reset destination tables, and apply source TRUNCATE operations. It sends initial and ongoing row data through Snowpipe Streaming. These operations do not use a virtual warehouse.

Validation checks authentication, database and schema visibility, and that QUOTED_IDENTIFIERS_IGNORE_CASE is FALSE. It does not verify that the role can create or own tables, create pipes, or write through Snowpipe Streaming.

Destination table names#

Pipelines maps each Postgres schema and table pair to one Snowflake table name. It doubles existing underscores, joins the names with one underscore, and uppercases the result:

Postgres schema and table names cannot start or end with _ or contain " or ;. Names that differ only in case map to the same Snowflake name. Use lowercase Postgres names to avoid collisions. Source column names are preserved as quoted identifiers, except for the reserved metadata names below.

Append-only change history#

Each destination table contains the replicated source columns plus two VARCHAR NOT NULL metadata columns:

The metadata names are reserved and can't be used by source columns. Initial-sync rows use insert and the shared sequence number 0000000000000000/0000000000000000.

Snowflake tables are an event history, not a current-state replica:

• An insert appends the new row.
• An update appends the complete new row. It does not append a before image.
• A delete appends the complete old row for REPLICA IDENTITY FULL. For a primary-key or USING INDEX identity, it appends only the identity columns and sets all other source columns to NULL.
• A source TRUNCATE truncates the Snowflake table, resets its streaming state, and does not append a truncate event.

To derive current state, group by a stable source identity and select the row with the latest _cdc_sequence_number. Exclude identities whose latest operation is delete. See Query and materialize current state for SQL examples.

The sequence number is used for ordering and checkpointing. It is not a globally unique event ID. Pipelines provides at-least-once delivery, so consumers must tolerate duplicates. Snowpipe committed offsets suppress routine replay but do not change this guarantee.

Resetting a table drops and recreates its Snowflake table and managed streaming state. This erases its history. Removing a table from the Postgres publication stops new changes after the pipeline restarts. The existing Snowflake table remains.

Query and materialize current state#

Use the replicated change history to build a current-state dataset for reports and analytics. Pipelines maintains the history table. You create and maintain the queries, views, or dynamic tables that read it.

Before you start#

The examples use public.orders, replicated to PIPELINES_DB.REPLICATED.PUBLIC_ORDERS, with source columns id and status. Replace these names with your own. Wait for the table's initial sync to finish before treating the result as a complete replica.

Choose a unique, non-null identity that stays the same when a row is updated. The examples use id. For a composite key, include every key column in partition by, such as partition by "tenant_id", "id". Include those columns in the publication and in delete events. REPLICA IDENTITY FULL alone does not make rows unique.

Use a separate analytics role and warehouse, with a schema outside the Pipelines-managed REPLICATED schema for derived objects. The examples use ANALYTICS_ROLE, ANALYTICS_WH, and PIPELINES_DB.ANALYTICS. Ask your Snowflake administrator to prepare these resources and grant the analytics role:

• USAGE on the warehouse, database, and both schemas.
• SELECT on the replicated table.
• CREATE VIEW on the analytics schema to create a view, or CREATE DYNAMIC TABLE to create a dynamic table.

The role must be available to the Snowflake user running the examples. Keep ownership of the replicated table with PIPELINES_ROLE. See Snowflake's dynamic table access control for the full privilege requirements.

Query current state#

Run these statements in a Snowflake SQL worksheet with your analytics role:

1
use role ANALYTICS_ROLE;
2
use warehouse ANALYTICS_WH;
3
4
select "id", "status"
5
from PIPELINES_DB.REPLICATED.PUBLIC_ORDERS
6
qualify row_number() over (
7
  partition by "id" order by "_cdc_sequence_number" desc
8
) = 1
9
and "_cdc_operation" != 'delete';

The result contains one row per identity whose latest operation is not delete. Ordering by the fixed-width sequence string selects the latest change. Repeated copies of the same event produce one result row. Keep the double quotes around source and metadata column names because Pipelines creates them as case-sensitive identifiers.

Keep the delete condition in qualify. A where "_cdc_operation" != 'delete' condition would remove delete events before ranking and could bring back an older row. Snowflake's QUALIFY reference explains this evaluation order.

To reuse the query from an analytics tool, save it as a view:

1
create view PIPELINES_DB.ANALYTICS.ORDERS_CURRENT_VIEW as
2
select "id", "status"
3
from PIPELINES_DB.REPLICATED.PUBLIC_ORDERS
4
qualify row_number() over (
5
  partition by "id" order by "_cdc_sequence_number" desc
6
) = 1
7
and "_cdc_operation" != 'delete';

A regular view stores the query definition, not a separate copy of its results. Each read derives current state from the history available to that query. See Snowflake's comparison of views and dynamic tables.

Materialize with a dynamic table#

A dynamic table stores the query result and refreshes it as the replicated history changes. Use it when you want to query a maintained current-state dataset without defining a scheduled merge task.

1. Ask the owner of the replicated table to enable change tracking in Snowflake. This is a table setting, not a change to the replicated columns or data. Run as PIPELINES_ROLE, or another role that inherits ownership:

   1
   alter table PIPELINES_DB.REPLICATED.PUBLIC_ORDERS
   2
     set change_tracking = true;

   The analytics role does not own the replicated table, so it cannot enable change tracking automatically when creating the dynamic table. See Snowflake's change tracking requirements.

2. Switch to the analytics role and create the dynamic table:

   1
   use role ANALYTICS_ROLE;
   2
   use warehouse ANALYTICS_WH;
   3
   4
   create dynamic table PIPELINES_DB.ANALYTICS.ORDERS_CURRENT
   5
     target_lag = '5 minutes'
   6
     warehouse = ANALYTICS_WH
   7
     refresh_mode = incremental
   8
     initialize = on_create
   9
   as
   10
   select "id", "status"
   11
   from PIPELINES_DB.REPLICATED.PUBLIC_ORDERS
   12
   qualify row_number() over (
   13
     partition by "id" order by "_cdc_sequence_number" desc
   14
   ) = 1
   15
   and "_cdc_operation" != 'delete';

   initialize = on_create populates the dynamic table before creation finishes. Explicit refresh_mode = incremental makes creation fail if your adapted query cannot refresh incrementally, instead of choosing a full refresh through AUTO. See Snowflake's refresh modes and CREATE DYNAMIC TABLE reference.

3. Check the refresh mode and read the materialized rows:

   1
   show dynamic tables like 'ORDERS_CURRENT'
   2
     in schema PIPELINES_DB.ANALYTICS;
   3
   4
   select "id", "status"
   5
   from PIPELINES_DB.ANALYTICS.ORDERS_CURRENT;

   Confirm that refresh_mode is INCREMENTAL and scheduling is running. Use Snowflake's refresh monitoring to check the last successful refresh and any errors. After an insert, update, or delete reaches the replicated table, the next successful refresh reflects it in ORDERS_CURRENT.

The five-minute target_lag is an example freshness target relative to the history in Snowflake. It is not a fixed refresh schedule or an end-to-end latency guarantee from Postgres. Pipeline replication lag and dynamic-table refresh lag both affect freshness. See Snowflake's target lag guide.

Dynamic-table refreshes consume warehouse compute, and the materialized results consume storage. These costs are additional to ingestion and querying. Start with a freshness target that meets your reporting needs and measure a representative workload. A dedicated warehouse helps isolate refresh costs. See Snowflake's dynamic table cost guide.

Maintain derived objects#

Pipelines maintains the replicated history table, but does not update your view or dynamic-table definitions.

Recreating a dynamic table initializes its contents again and uses compute. See Snowflake's dynamic table modification guide for changes that require reinitialization.

Use streams and tasks#

Snowflake streams and tasks can maintain a separate table with scheduled MERGE statements. Use this option when you need control over the update procedure or schedule. Snowflake's SCD Type 1 examples compare this approach with dynamic tables.

Adapt the merge to Pipelines' "_cdc_operation" and "_cdc_sequence_number" columns. A stream on the history table sees appended rows, including rows representing source updates and deletes. Your job must interpret those operations, load existing history, tolerate replay, and rebuild current state after a source truncate or pipeline table reset.

Source table requirements#

Required REPLICA IDENTITY depends on the operations enabled in the Postgres publication:

Set full replica identity before publishing updates:

1
alter table public.your_table replica identity full;

REPLICA IDENTITY FULL increases WAL volume, but lets Pipelines construct complete new rows when Postgres omits unchanged out-of-line TOAST values. The setting applies only to new WAL records. If retained WAL already contains an incompatible update, reset the affected table after changing the setting.

Type mapping#

Pipelines creates Snowflake columns with these mappings:

Pipelines uses VARCHAR for character and text types, numeric, time with time zone, interval, uuid, bytea, bit strings, and custom or unknown types. bytea values are lowercase hexadecimal strings. Pipelines stores these values in serialized form, not as native Snowflake types.

Additional limits apply:

• Multi-dimensional arrays aren't supported. Non-default lower bounds on one-dimensional arrays aren't preserved.
• Non-finite floating-point and numeric values are rejected.
• An uncompressed serialized row larger than 2 MiB is rejected.
• Source primary-key, unique, check, length, precision, and nullability constraints aren't copied. Only the two CDC metadata columns are NOT NULL.

Schema change support#

Snowflake schema change support is limited during Early Access.

Supported changes:

• Add a column.
• Rename a column.
• Drop a column.

Unsupported or limited changes:

• Changing a column type isn't supported.
• Source table and schema renames aren't supported.
• Changes to nullability or existing column defaults are ignored.
• Initial table creation can copy compatible literal defaults. Added columns can copy string, numeric, or boolean literal defaults. Other defaults are omitted.

Snowflake DDL changes existing history. Adding a column with a default can populate older rows. Renaming a column changes the historical schema. Dropping a column removes it from old events. Snowflake DDL is not transactional, so an interrupted multi-column change can leave a partially applied schema. Apart from enabling change tracking, do not alter managed destination objects manually. If the pipeline remains failed after a restart, contact support.

Troubleshooting#

Use pipeline monitoring and replication logs to inspect table state, lag, and errors.

Additional resources#

• Snowflake key-pair authentication
• Snowflake account identifiers
• Snowpipe Streaming default pipe
• Snowpipe Streaming operations and privileges