Database

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.

Supported Data Types

Postgres Data TypeSnowflake Data Type
booleanBOOLEAN
smallintSMALLINT
integerINT
bigintBIGINT
realFLOAT4
double precisionFLOAT8
numericNUMBER
textVARCHAR
dateDATE
timestampTIMESTAMP_NTZ
timestamptzTIMESTAMP_TZ

Available Versions

VersionWasm Package URLChecksum
0.1.1https://github.com/supabase/wrappers/releases/download/wasm_snowflake_fdw_v0.1.1/snowflake_fdw.wasm7aaafc7edc1726bc93ddc04452d41bda9e1a264a1df2ea9bf1b00b267543b860
0.1.0https://github.com/supabase/wrappers/releases/download/wasm_snowflake_fdw_v0.1.0/snowflake_fdw.wasm2fb46fd8afa63f3975dadf772338106b609b131861849356e0c09dde032d1af8

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:


_10
create extension if not exists wrappers with schema extensions;

Enable the Snowflake Wrapper

Enable the Wasm foreign data wrapper:


_10
create 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`
_10
insert into vault.secrets (name, secret)
_10
values (
_10
'snowflake',
_10
E'-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----'
_10
)
_10
returning 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:


_12
create 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:


_10
create 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

ObjectSelectInsertUpdateDeleteTruncate
table/view✅✅✅✅❌

Usage


_12
create 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 table option
  • Requires rowid_column for data modification operations
  • Supports query pushdown for where, order by, and limit clauses
  • 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.

Examples

Basic Example

Let's prepare the source table in Snowflake first:


_17
-- Create a database
_17
create database if not exists mydatabase;
_17
_17
-- Run below SQLs on Snowflake to create source table
_17
create 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
_17
insert into mydatabase.public.mytable(id, name, num, dt, ts)
_17
values (42, 'foo', 12.34, '2024-05-18', '2024-05-18 12:34:56');
_17
insert into mydatabase.public.mytable(id, name, num, dt, ts)
_17
values (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.


_14
create 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
_14
select * 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
_12
insert into snowflake.mytable (id, name, num, dt, ts)
_12
values (42, 'hello', 456.123, '2024-05-20', '2024-05-20 12:34:56');
_12
_12
-- update existing data
_12
update snowflake.mytable
_12
set name = 'new name', num = null, dt = '2024-01-01', ts = '2024-01-02 21:43:56'
_12
where id = 42;
_12
_12
-- delete data
_12
delete from snowflake.mytable
_12
where id = 42;