Skip to content

Updated

Snowflake destination

Replicate Supabase Postgres changes to Snowflake.

Snowflake is a managed data platform. Supabase Pipelines replicates each Postgres table to Snowflake as an append-only history of changes. It also creates and maintains companion views that return the current rows, including values preserved across partial updates.

To replicate data to Snowflake:

  1. Prepare a database, schema, role, service user, and key pair in Snowflake.
  2. Configure the Snowflake destination in the Supabase Dashboard.
  3. Query or materialize the replicated data in Snowflake.

Source table requirements

Every source table needs a stable, nondeferrable primary key, even with replica identity FULL. Publish every key column.

Postgres uses the Oversized-Attribute Storage Technique (TOAST) to store large values. The following replica identities support the companion view:

Replica identityStored updatesTradeoff
DEFAULT with a supported primary keyComplete rows or partial updates when Postgres omits unchanged large values stored using TOAST.Reduces repeated data transfer and storage; resolving omitted values adds query work.
FULL with a supported primary keyComplete new rows, including unchanged values.Increases write-ahead log (WAL) and ingestion volume; complete history allows a simpler query.
USING INDEX with exactly the primary-key columnsComplete rows or partial updates, as with DEFAULT.The identity index must use the same columns as the primary key.

For tables with large values that change infrequently, start with DEFAULT and query the companion view. Use FULL when you need complete update records in the history table. Compare ingestion and query costs for your workload.

To use the primary key as the replica identity:

alter table public.your_table replica identity default;

To provide complete update rows:

alter table public.your_table replica identity full;

Supported primary-key types are smallint, integer, bigint, oid, boolean, text, varchar, Postgres "char", char(n), name, uuid, bytea, numeric, date, timestamp, and timestamptz.

Tables without a supported primary key are unsupported, as are identity indexes that don't match the primary-key columns. Pipelines creates no companion view for a table with a source column whose name begins with _cdc_. Before you rely on the results as a replica, see Requirements for current-state queries.

If a table with a companion view stops meeting these requirements, the view becomes unavailable and Pipelines reports an error.

Prepare Snowflake resources

Before you create a pipeline, prepare a dedicated Snowflake database and an empty schema for the replicated tables. Pipelines also needs a service user and role that can create, alter, and drop the tables and companion views. Use unquoted identifiers for the service user and role so Snowflake stores them in uppercase. Pipelines also converts the account and user names to uppercase during authentication.

Run this setup as a Snowflake administrator, changing the example names as needed:

create role if not exists PIPELINES_ROLE;
create user if not exists PIPELINES_USER
type = service;
grant role PIPELINES_ROLE to user PIPELINES_USER;
alter user PIPELINES_USER set default_role = PIPELINES_ROLE;
create database if not exists PIPELINES_DB;
create schema if not exists PIPELINES_DB.REPLICATED;
grant usage on database PIPELINES_DB to role PIPELINES_ROLE;
grant usage on schema PIPELINES_DB.REPLICATED to role PIPELINES_ROLE;
grant create table on schema PIPELINES_DB.REPLICATED to role PIPELINES_ROLE;
grant create view on schema PIPELINES_DB.REPLICATED to role PIPELINES_ROLE;

In this example, Pipelines creates the destination tables and views using PIPELINES_ROLE. The role must retain ownership to alter, replace, truncate, or drop these objects when required. Do not create them in advance under another role. Snowflake requires CREATE VIEW on the schema to create a view, and ownership to replace an existing one.

Snowflake automatically creates a managed default pipe named <TABLE>-STREAMING for each table. You don't need to provide a virtual warehouse, stage, or pipe for ingestion. See Snowpipe Streaming access privileges for the required permissions.

To keep ingestion resources separate from downstream workloads, use a different role and warehouse for queries and transformations.

Keep the SQL and streaming roles aligned

Pipelines connects to Snowflake through separate interfaces for SQL requests and streaming. Both interfaces need to use the same role:

  • SQL requests use the optional Role under Advanced settings 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.

Grant the required permissions to a dedicated role such as PIPELINES_ROLE, then set it as the service user's DEFAULT_ROLE. In the Dashboard, either leave Role empty or enter the same role explicitly. This keeps SQL validation and streaming aligned.

Generate a key pair

Pipelines uses an RSA key pair to authenticate with Snowflake. The key must be at least 2048 bits, and Snowflake recommends the PKCS #8 format.

  1. Run one of the following commands from the directory where you want to store the key. Each command creates a private key named rsa_key.p8.

    For an unencrypted private key:

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

    Or, for a passphrase-protected private key:

    openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 \
    -inform PEM -out rsa_key.p8
  2. Create a public key from the private key. Snowflake uses it to verify connections signed with rsa_key.p8:

    openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
  3. Open rsa_key.pub and copy the text between the BEGIN PUBLIC KEY and END PUBLIC KEY lines. Replace <public-key-body> with the copied text and run this statement in Snowflake:

    alter user PIPELINES_USER set rsa_public_key = '<public-key-body>';
  4. When configuring the destination, paste or upload the complete rsa_key.p8 file into Private key. Preserve its PEM header and footer. For a passphrase-protected key, enter the passphrase into Private key passphrase.

Keep rsa_key.p8 and its passphrase secret. The Dashboard accepts unencrypted PKCS #1 and PKCS #8 keys. It also accepts encrypted PKCS #8 keys with a passphrase, but not 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:

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

Use the result as the Account ID, for example MYORG-MYACCOUNT. The field also accepts legacy one-part account locators, but not full URLs or dotted locator-and-region hostnames. Account IDs can contain up to 63 characters. See Snowflake account identifiers for details.

Configure Snowflake as a destination

Follow the steps in Set up Pipelines. When prompted to choose a destination, select Snowflake and enter the following settings:

FieldValue
Account IDMYORG-MYACCOUNT, for example; use an organization-account identifier
UserPIPELINES_USER, or your unquoted service user
DatabasePIPELINES_DB, or your destination database
SchemaREPLICATED, or your dedicated destination schema
RoleLeave empty to use the service user's default role, or enter that same role explicitly
Private keyPaste or upload the complete rsa_key.p8 private-key PEM file
Private key passphraseOnly for an encrypted PKCS #8 key

Enter the database and schema identifiers exactly as stored in Snowflake. Unquoted identifiers are stored in uppercase. Choose an account near the managed pipeline region.

Complete validation and review the initial sync estimate before confirming creation.

How it works

Pipelines uses Snowflake's SQL REST API to validate the database and schema. It also uses the API to create, alter, and drop destination tables and companion views, and to apply source TRUNCATE operations. Pipelines sends initial and ongoing row data through Snowpipe Streaming.

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 and views, or write through Snowpipe Streaming. Apply the required grants even if validation succeeds.

Destination table names

Pipelines combines each Postgres schema and table name into one Snowflake table name. For names without leading or trailing underscores, existing underscores are doubled and the names are joined with a single underscore. The result is converted to uppercase:

Postgres tableSnowflake table
public.ordersPUBLIC_ORDERS
sales_eu.order_itemsSALES__EU_ORDER__ITEMS

Source schema and table names cannot contain " or ;. If either name starts or ends with _, Pipelines uses _SSTT_<schema>_<table> instead, preserving the original underscores. SS and TT are the two-digit lengths of the schema and table names. For this format, both names must contain only ASCII letters, digits, and underscores. For example, public._orders becomes _0607_public__orders, or _0607_PUBLIC__ORDERS in Snowflake. Check the generated destination name before writing queries.

Names that differ only in case map to the same Snowflake name. Use distinct names after case conversion to avoid collisions. Source column names are preserved as quoted identifiers, except for the reserved metadata names.

Append-only change history

Each destination table contains the replicated source columns plus three change data capture (CDC) columns:

ColumnTypeMeaning
_cdc_operationVARCHAR NOT NULLLowercase operation: insert, update, or delete.
_cdc_sequence_numberVARCHAR NOT NULLSource change sequence. Use it to order ongoing changes.
_cdc_missing_columnsNullable ARRAYStable column IDs for values omitted from a partial update or a key-only delete. Complete records use an empty array; NULL also means complete.

The three metadata names are reserved. Initial-sync rows use insert and a shared baseline sequence number.

Pipelines records changes as follows:

  • An insert appends the new row.
  • An update appends the complete or partial new row, without a before image.
  • A delete appends the old row for FULL, or the key columns for a primary-key identity. Other fields in a key-only delete are NULL placeholders listed in _cdc_missing_columns.
  • Truncating the source table also truncates the Snowflake table and resets its streaming state. It does not append a truncate event.

An omitted value is stored as a NULL placeholder with its column ID in _cdc_missing_columns. The companion view carries forward its last supplied value. An explicit SQL NULL has no missing-column entry and replaces the previous value. Testing a cell for NULL alone cannot distinguish these cases.

The companion view resolves omitted values automatically.

The sequence number orders changes but is not a globally unique event ID. Initial-sync rows share the same value, so don't use it alone to deduplicate copied rows.

Delivery and replay

For ongoing row changes, Pipelines provides exactly-once ingestion into managed Snowflake tables. Recovery uses Snowpipe Streaming's committed offsets to skip already committed changes. This depends on preserving the managed table and its streaming state. An interrupted initial sync can instead discard its partial data and copy the table again.

Query the companion view for current rows, or the base table for change history. If you use that history to update another system, that system needs its own protection against repeated processing. See delivery guarantees for ordering and recovery limits.

Resetting a table erases its destination history. It copies existing source rows again only if the table is selected for Initial sync. By contrast, removing a table from the publication leaves its destination history in place but stops updating it.

Type mapping

Pipelines creates Snowflake columns with these mappings:

Postgres typeSnowflake type
booleanBOOLEAN
smallint, integer, bigintSMALLINT, INTEGER, BIGINT
real, double precisionFLOAT, DOUBLE
date, timeDATE, TIME
timestamp, timestamp with time zoneTIMESTAMP_NTZ, TIMESTAMP_TZ
json, jsonbVARIANT
One-dimensional arraysARRAY
oidBIGINT
Other typesVARCHAR

Pipelines maps character and text types, numeric, time with time zone, interval, uuid, bytea, bit strings, and custom or unknown types to VARCHAR. It serializes these values instead of storing them as native Snowflake types. For bytea, the serialized value is a lowercase hexadecimal string.

Additional limits apply:

  • Multi-dimensional arrays aren't supported. Non-default lower bounds on one-dimensional arrays aren't preserved.
  • Non-finite real and double precision values are rejected. Non-finite numeric values are preserved as strings in VARCHAR columns.
  • Each compressed streaming request must fit within 4 MiB, including its framing. Each initial-copy row and all values supplied together in an insert or update must fit this limit. Partial updates omit only unchanged TOAST values.
  • SQL NULL and JSON null remain distinct in json and jsonb columns. Arrays of these types cannot contain SQL NULL elements; JSON null elements and a whole SQL NULL array are supported.
  • Source primary-key, unique, check, length, precision, and nullability constraints aren't copied. Only _cdc_operation and _cdc_sequence_number are NOT NULL.

Schema change support

Pipelines supports adding, renaming, and dropping columns. The companion view updates to use the current column names.

Supported schema changes can remain pending until the table's next replicated row change. An idle table can retain its previous destination schema until then. Some schema changes can temporarily make the companion view unavailable while Pipelines updates it.

Replicated columns remain nullable in Snowflake, and changes to existing column defaults are not propagated. Pipelines can copy supported literal defaults when creating a table or adding a column. Other defaults are omitted, although Postgres still supplies source values through replication. Partial-update support does not backfill arbitrary default expressions for untouched rows.

Schema changes also affect stored history. A rename changes the column name in earlier events, and a drop removes its historical values. Adding a column with a supported default can populate older rows. Changing publication column selection can remove values or leave historical gaps; reset the table after such changes before relying on current-state results.

For type changes, unsupported changes, and interrupted schema changes, see the shared schema-change behavior and recovery. Keep the primary key and its values stable. Apart from enabling change tracking for a stream, leave managed table and view definitions unchanged. For additional processing, use separate derived objects.

Query replicated data

Query the automatically maintained companion view to read current rows. You don't need to generate SQL or create the view yourself. It resolves complete and partial updates, explicit NULL values, and deletes followed by reinserts.

Before you start

The examples use a source table named public.orders with columns id and status. Its history table is PIPELINES_DB.REPLICATED.PUBLIC_ORDERS and its companion view is PIPELINES_DB.REPLICATED.PUBLIC_ORDERS_CURRENT. Replace these names with your own.

Wait for initial sync to finish before treating the view as a complete replica. During initial sync or a table reset, it can expose rows while they are still being copied.

Ask your Snowflake administrator to grant your analytics role USAGE on the query warehouse, database, and schema. The owner of the view can grant read access:

grant select on view PIPELINES_DB.REPLICATED.PUBLIC_ORDERS_CURRENT
to role ANALYTICS_ROLE;

Keep ownership of managed tables and views with PIPELINES_ROLE. Creating the view does not automatically grant existing table readers permission to read it.

Query current state

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

use role ANALYTICS_ROLE;
use warehouse ANALYTICS_WH;
select "id", "status"
from PIPELINES_DB.REPLICATED.PUBLIC_ORDERS_CURRENT;

The companion view is an ordinary view: it runs a query when read and does not store results or run a background refresh. It requires a Snowflake warehouse. Query cost grows with retained history, table width, and the amount of partial-update history to resolve. For frequently repeated queries, consider a separately stored result.

The view also returns diagnostic columns:

ColumnMeaning
_cdc_anchoredWhether the current row has an insert or initial-copy event after its last delete.
_cdc_unresolved_columnsCurrent column names whose values cannot be resolved from the retained history.
_cdc_last_sequenceSequence number of the latest event for the current row.

Inspect incomplete rows before relying on the view:

select "id", "_cdc_anchored", "_cdc_unresolved_columns"
from PIPELINES_DB.REPLICATED.PUBLIC_ORDERS_CURRENT
where not "_cdc_anchored" or array_size("_cdc_unresolved_columns") > 0;

Investigate these rows rather than hiding them with a filter. Empty diagnostics do not prove that all source changes were captured: the query cannot detect events excluded by a publication or arbitrary gaps in retained history.

Requirements for current-state queries

Both the companion view and custom current-state queries require:

  • A supported primary key whose values and representation remain stable. A complete update that changes a key value can leave the old identity in the result. Pipelines rejects a partial update that changes a key value.
  • An initial snapshot followed by uninterrupted publication coverage, including while replication is stopped. Keep publication actions, membership, and column selection unchanged, and do not use row filters.
  • All accepted history from that snapshot. Do not prune or manually edit events, or combine history from separate initial syncs.

After incompatible key, schema, or publication changes, or lost history, reset the affected table with the table selected for Initial sync to take a new snapshot. This erases the previous destination history. Restarting the replicator process or manually truncating a Snowflake table is not a substitute for a table reset.

Customize queries and materialization

For separately stored results, use Snowflake dynamic tables or streams and tasks. Create derived objects in a separate analytics schema. You control their refresh behavior, cost, and recovery; Pipelines updates only its managed companion view.

Custom history processing must distinguish omitted values from explicit NULL values and protect against repeated processing. See the ETL Snowflake reference for the history format and custom query requirements.

After a source truncate or table reset, rebuild separately stored results. Use the base history table for streams; replacing a managed companion view can make streams attached to it stale. Follow Snowflake's change-tracking requirements.

Maintain derived objects

Pipelines owns and refreshes the generated companion views. Do not edit their definitions or comments, transfer ownership, or attach custom policies or tags. For custom behavior, create separate analytics objects.

You maintain any additional queries, views, dynamic tables, or tasks:

ChangeWhat to do
Source TRUNCATEThe companion view reads the truncated history. Refresh or rebuild separately stored results.
Table resetWait for initial sync to finish. Recheck dependent objects, history-table grants, and change tracking.
Added, renamed, or dropped columnUpdate custom queries and derived objects that use the changed columns. The companion view updates automatically.
Source table rename followed by resetThe destination and companion view use the new source name. Update references and grant readers access to the new view.

Upgrade an existing pipeline

Before upgrading, make sure the Pipelines role has the required permissions, including CREATE VIEW, and retains ownership of its tables and views.

  1. Review consumers that read the history tables directly. They must support partial updates, or use the companion view for complete current rows. Pause jobs that assume every update contains a complete row.
  2. Open the pipeline's actions menu in the Dashboard and select Update available. Follow the prompts to update and restart the pipeline.
  3. Confirm replication resumes, grant consumers SELECT on the companion views, and update your own queries or derived jobs before resuming them.

A pipeline update normally preserves saved replication progress and destination history. If startup fails because a permission is missing, apply the grant and restart the pipeline. A table reset deletes history, so use it only when a rebuild is required.

Troubleshooting

Use these checks to diagnose replication and query failures:

SymptomWhat to check
Authentication failsAccount identifier, user, key fingerprint and PEM, and passphrase. Omit the passphrase for an unencrypted key.
Database or schema isn't foundExact identifier case and USAGE permissions on both objects
Validation passes but table or view creation failsGrants and ownership, including CREATE TABLE and CREATE VIEW
Tables are created but writes failSQL and streaming role alignment, table permissions, and network access to the account control and discovered Snowpipe ingest endpoints
Updates or deletes failSource replica identity and published columns
A row is rejectedType and row-size limits and reserved metadata columns
Current-state results contain unexpected NULL valuesUse the companion view for partial history; check diagnostic columns and history requirements.
Startup reports a conflicting companion viewUse a dedicated destination schema. Do not create objects that conflict with generated companion views or change their ownership or comments.
A schema change failsSupported changes; do not repair managed tables manually

Use pipeline monitoring to inspect errors. For unresolved failures, contact support with the pipeline ID and error details.

Additional resources