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.
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 can query Snowflake, you need to enable the Wrappers extension and store your credentials in Postgres.
Enable Wrappers
Make sure the wrappers extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
Enable the Snowflake Wrapper
Enable the Wasm foreign data wrapper:
_10create foreign data wrapper wasm_wrapper_10 handler wasm_fdw_handler_10 validator wasm_fdw_validator;
Store 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;
Options
The full list of foreign table options are below:
-
table- Source table or view name in Snowflake, required.This option can also be a subquery enclosed in parentheses.
-
rowid_column- Primary key column name, optional for data scan, required for data modify
Entities
Snowflake Tables/Views
This is an object representing a Snowflake table or view.
Ref: Snowflake docs
Operations
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| table/view | ✅ | ✅ | ✅ | ✅ | ❌ |
Usage
_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 );
Notes
- Supports both tables and views as data sources
- Can use subqueries in
tableoption - Requires
rowid_columnfor data modification operations - Supports query pushdown for
where,order by, andlimitclauses - Column names must match between Snowflake and foreign table
- Data types must be compatible according to type mapping table
Query Pushdown Support
This FDW supports where, order by and limit clause pushdown.
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 |
Limitations
This section describes important limitations and considerations when using this FDW:
- Large result sets may experience slower performance due to full data transfer requirement
- Column names must exactly match between Snowflake and foreign table
- Foreign tables with subquery option cannot support data modify
- Materialized views using these foreign tables may fail during logical backups
Examples
Basic Example
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');
This example will create a "foreign table" inside your Postgres database and query its data.
_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 required 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;