Snowflake
Snowflake is a cloud-based data platform provided as a DaaS (Data-as-a-Service) solution with data storage and analytics service.
The Snowflake Wrapper is a WebAssembly(Wasm) foreign data wrapper which allows you to read and write data from Snowflake within your Postgres database.
Restoring a logical backup of a database with a materialized view using a foreign table can fail. For this reason, either do not use foreign tables in materialized views or use them in databases with physical backups enabled.
Supported Data Types
| Postgres Data Type | Snowflake Data Type |
|---|---|
| boolean | BOOLEAN |
| smallint | SMALLINT |
| integer | INT |
| bigint | BIGINT |
| real | FLOAT4 |
| double precision | FLOAT8 |
| numeric | NUMBER |
| text | VARCHAR |
| date | DATE |
| timestamp | TIMESTAMP_NTZ |
| timestamptz | TIMESTAMP_TZ |
Available Versions
| Version | Wasm Package URL | Checksum |
|---|---|---|
| 0.1.1 | https://github.com/supabase/wrappers/releases/download/wasm_snowflake_fdw_v0.1.1/snowflake_fdw.wasm | 7aaafc7edc1726bc93ddc04452d41bda9e1a264a1df2ea9bf1b00b267543b860 |
| 0.1.0 | https://github.com/supabase/wrappers/releases/download/wasm_snowflake_fdw_v0.1.0/snowflake_fdw.wasm | 2fb46fd8afa63f3975dadf772338106b609b131861849356e0c09dde032d1af8 |
Preparation
Before you get started, make sure the wrappers extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
and then create the Wasm foreign data wrapper:
_10create foreign data wrapper wasm_wrapper_10 handler wasm_fdw_handler_10 validator wasm_fdw_validator;
Secure your credentials (optional)
By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.
This FDW uses key-pair authentication to access Snowflake SQL Rest API, please refer to Snowflake docs for more details about the key-pair authentication.
_10-- Save your Snowflake private key in Vault and retrieve the `key_id`_10insert into vault.secrets (name, secret)_10values (_10 'snowflake',_10 E'-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----'_10)_10returning key_id;
Connecting to Snowflake
We need to provide Postgres with the credentials to connect to Snowflake, and any additional options. We can do this using the create server command:
_12create server snowflake_server_12 foreign data wrapper wasm_wrapper_12 options (_12 fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_snowflake_fdw_v0.1.1/snowflake_fdw.wasm',_12 fdw_package_name 'supabase:snowflake-fdw',_12 fdw_package_version '0.1.1',_12 fdw_package_checksum '7aaafc7edc1726bc93ddc04452d41bda9e1a264a1df2ea9bf1b00b267543b860',_12 account_identifier 'MYORGANIZATION-MYACCOUNT',_12 user 'MYUSER',_12 public_key_fingerprint 'SizgPofeFX0jwC8IhbOfGFyOggFgo8oTOS1uPLZhzUQ=',_12 private_key_id '<key_ID>' -- The Key ID from above._12 );
Note the fdw_package_* options are required, which specify the Wasm package metadata. You can get the available package version list from above.
Create a schema
We recommend creating a schema to hold all the foreign tables:
_10create schema if not exists snowflake;
Creating Foreign Tables
The Snowflake Wrapper supports data reads and writes from Snowflake.
| Integration | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Snowflake | ✅ | ✅ | ✅ | ✅ | ❌ |
For example:
_12create foreign table snowflake.mytable (_12 id bigint,_12 name text,_12 num numeric,_12 dt date,_12 ts timestamp_12)_12 server snowflake_server_12 options (_12 table 'mydatabase.public.mytable',_12 rowid_column 'id'_12 );
Foreign table options
The full list of foreign table options are below:
-
table- Source table or view name in Snowflake, required.This can also be a subquery enclosed in parentheses, for example,
_10table '(select * from mydatabase.public.mytable where id = 42)' -
rowid_column- Primary key column name, optional for data scan, required for data modify
Query Pushdown Support
This FDW supports where, order by and limit clause pushdown.
Examples
Some examples on how to use Snowflake foreign tables.
Let's prepare the source table in Snowflake first:
_17-- Create a database_17create database if not exists mydatabase;_17_17-- Run below SQLs on Snowflake to create source table_17create table mydatabase.public.mytable (_17 id number(38,0),_17 name varchar(16777216),_17 num number(38,6),_17 dt date,_17 ts timestamp_ntz(9)_17);_17_17-- Add some test data_17insert into mydatabase.public.mytable(id, name, num, dt, ts)_17values (42, 'foo', 12.34, '2024-05-18', '2024-05-18 12:34:56');_17insert into mydatabase.public.mytable(id, name, num, dt, ts)_17values (43, 'bar', 56.78, '2024-05-19', '2024-05-19 12:34:56');
Basic example
This example will create a "foreign table" inside your Postgres database and query its data. First, we can create a schema to hold all the Snowflake foreign tables.
_10create schema if not exists snowflake;
Then create the foreign table and query it, for example:
_14create foreign table snowflake.mytable (_14 id bigint,_14 name text,_14 num numeric,_14 dt date,_14 ts timestamp_14)_14 server snowflake_server_14 options (_14 table 'mydatabase.public.mytable',_14 rowid_column 'id'_14 );_14_14select * from snowflake.mytable;
Data modify example
This example will modify data in a "foreign table" inside your Postgres database, note that rowid_column option is mandatory for data modify:
_12-- insert new data_12insert into snowflake.mytable (id, name, num, dt, ts)_12values (42, 'hello', 456.123, '2024-05-20', '2024-05-20 12:34:56');_12_12-- update existing data_12update snowflake.mytable_12set name = 'new name', num = null, dt = '2024-01-01', ts = '2024-01-02 21:43:56'_12where id = 42;_12_12-- delete data_12delete from snowflake.mytable_12where id = 42;