# Snowflake destination Replicate Supabase Postgres changes to Snowflake. Configure Snowflake as a Supabase Pipelines destination. Note: Supabase Pipelines is currently in public alpha. Features and behavior may change as we continue developing the product. Note: The Snowflake destination is in Early Access and available only to approved organizations. [Request access](https://supabase.com/go/supabase-pipelines-new-destinations) before following this guide. [Snowflake](https://www.snowflake.com/) 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: ```sql 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 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 `-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: ```bash openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt ``` To use a passphrase, generate an encrypted PKCS #8 private key: ```bash openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8 ``` Derive the public key: ```bash 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: ```sql alter user PIPELINES_USER set rsa_public_key = ''; ``` 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](https://docs.snowflake.com/en/user-guide/key-pair-auth) to verify the public-key fingerprint and rotate keys with `RSA_PUBLIC_KEY_2`. ### Find the account identifier Run this query in Snowflake: ```sql 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](https://docs.snowflake.com/en/user-guide/admin-account-identifier) for details. ## Configure Snowflake as a destination 1. Navigate to the [**Database > Replication**](https://supabase.com/dashboard/project/_/database/replication) section of the Dashboard. 2. Click **Add destination**. 3. Select **Snowflake**. If it isn't available, [request Early Access](https://supabase.com/go/supabase-pipelines-new-destinations). 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](#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 table | Snowflake table | | ---------------------- | ------------------------ | | `public.orders` | `PUBLIC_ORDERS` | | `sales_eu.order_items` | `SALES__EU_ORDER__ITEMS` | 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: | Column | Meaning | | ---------------------- | -------------------------------------------------------------------------------------------------------- | | `_cdc_operation` | Lowercase operation: `insert`, `update`, or `delete`. | | `_cdc_sequence_number` | Fixed-width hexadecimal commit LSN and transaction ordinal, such as `00000000016b3740/0000000000000002`. | 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`. 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. ## Source table requirements Required `REPLICA IDENTITY` depends on the operations enabled in the Postgres publication: | Published operations | Required replica identity | | -------------------- | -------------------------------------------------------------------------------------------------------------- | | `INSERT` only | No row identity is required. | | `DELETE` | A primary key, `REPLICA IDENTITY USING INDEX`, or `REPLICA IDENTITY FULL`. Identity columns must be published. | | `UPDATE` | `REPLICA IDENTITY FULL`. | Set full replica identity before publishing updates: ```sql 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: | Postgres type | Snowflake type | | --------------------------------------- | ------------------------------- | | `boolean` | `BOOLEAN` | | `smallint`, `integer`, `bigint` | `SMALLINT`, `INTEGER`, `BIGINT` | | `real`, `double precision` | `FLOAT`, `DOUBLE` | | `date`, `time` | `DATE`, `TIME` | | `timestamp`, `timestamp with time zone` | `TIMESTAMP_NTZ`, `TIMESTAMP_TZ` | | `json`, `jsonb` | `VARIANT` | | One-dimensional arrays | `ARRAY` | | `oid` | `BIGINT` | | Other types | `VARCHAR` | 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. Do not alter managed destination objects manually. If the pipeline remains failed after a restart, [contact support](https://supabase.com/dashboard/support/new). ## Troubleshooting | Symptom | What to check | | ----------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Authentication fails | Confirm the account identifier and user, the registered public-key fingerprint, the complete private-key PEM, and the passphrase. Don't provide a passphrase for an unencrypted key. | | Database or schema isn't found | Match the database and schema names exactly, including case. Confirm that the role has `USAGE` on both. | | Validation succeeds but table initialization fails | Confirm that the role has `CREATE TABLE` and `CREATE PIPE` on the schema. Check that another role does not own a table with the same name. Snowflake manages the default pipe. Use a dedicated empty schema. | | Validation or table creation succeeds but writes fail | Confirm that the service user's `DEFAULT_ROLE` is the pipeline role. Leave **Role** empty or set it to the same role. Confirm that the role has `CREATE PIPE`. Check that Snowflake network policies allow the account control endpoint and discovered Snowpipe ingest host. | | Updates or deletes fail | Check the publication's operations, replica identity, and included identity columns. Updates require `REPLICA IDENTITY FULL`. | | A row is rejected | Check for multi-dimensional arrays, non-finite numbers, serialized rows larger than 2 MiB, or source columns named `_cdc_operation` or `_cdc_sequence_number`. | | A schema change fails | Check the supported changes above. Snowflake DDL can be partially applied, so do not repair managed tables manually. [Contact support](https://supabase.com/dashboard/support/new) with the pipeline ID and error details. | Use [pipeline monitoring](https://supabase.com/docs/guides/database/replication/pipelines-monitoring) and [replication logs](https://supabase.com/dashboard/project/_/logs/replication-logs) to inspect table state, lag, and errors. ## Additional resources - [Snowflake key-pair authentication](https://docs.snowflake.com/en/user-guide/key-pair-auth) - [Snowflake account identifiers](https://docs.snowflake.com/en/user-guide/admin-account-identifier) - [Snowpipe Streaming default pipe](https://docs.snowflake.com/en/user-guide/snowpipe-streaming/snowpipe-streaming-pipe-object) - [Snowpipe Streaming operations and privileges](https://docs.snowflake.com/en/user-guide/snowpipe-streaming/snowpipe-streaming-operations)