How to Connect PostgreSQL to Snowflake with an Open-Source FDW

Galaxy Glossary

How do I connect PostgreSQL to Snowflake using open-source tools?

Leverage an open-source Foreign Data Wrapper (FDW) to query Snowflake tables directly from PostgreSQL.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Table of Contents

Why connect PostgreSQL to Snowflake with FDW?

Joining Snowflake facts with operational PostgreSQL tables in real time removes ETL lag and keeps analytics close to the app layer.

Which open-source FDW should I use?

Use odbc_fdw or the dedicated snowflake_fdw. Both rely on Snowflake’s ODBC driver and are MIT-licensed.

What is the installation workflow?

1) Install Snowflake ODBC driver. 2) Compile and install the FDW. 3) CREATE EXTENSION in the target database. 4) CREATE SERVER and USER MAPPING.5) IMPORT FOREIGN SCHEMA or CREATE FOREIGN TABLES.

How do I create the Snowflake FDW objects?

Run the syntax below, replacing connection details with your warehouse, database, role, user, and password.

How do I query Snowflake tables from PostgreSQL?

After importing tables, prefix them with the foreign schema (e.g., snowflake.products) and join them with local tables as usual.

What performance tips matter most?

Filter early. Only SELECT needed columns.Create statistics on foreign tables with ANALYZE so the planner can estimate row counts.

Which permissions are required?

The Snowflake user must have USAGE on database & schema plus SELECT on the target tables.PostgreSQL role needs USAGE on the FDW server.

What are common mistakes and fixes?

See the dedicated section below for quick resolutions.

Can I write back to Snowflake?

Yes—enable OPTIONS (updatable 'true') and grant INSERT/UPDATE/DELETE on Snowflake tables.

Common mistakes when using Snowflake FDW

Forgetting to set search_path

Always qualify foreign tables (snowflake.orders) or add the foreign schema to search_path.

Pulling entire tables

Push filters down with WHERE clauses; otherwise, large Snowflake tables will fully materialize in PostgreSQL.

Best practices recap

Store credentials in ~/.odbc.ini, limit warehouse auto-suspend to cut costs, and schedule ANALYZE on foreign tables.

FAQs

Is the FDW production-ready?

Yes—many teams run it in prod, but monitor query latency and Snowflake credits.

Does FDW support Snowflake stages?

No.Only tables and views are accessible; stages require external integrations.

.

Why How to Connect PostgreSQL to Snowflake with an Open-Source FDW is important

How to Connect PostgreSQL to Snowflake with an Open-Source FDW Example Usage


-- Join local and remote data in one statement
SELECT c.name,
       o.order_date,
       p.name  AS product_name,
       oi.quantity,
       oi.quantity * p.price AS line_total
FROM   Customers        c
JOIN   Orders           o  ON o.customer_id = c.id
JOIN   snowflake.OrderItems oi ON oi.order_id = o.id
JOIN   snowflake.Products   p  ON p.id        = oi.product_id
WHERE  c.id = 42;

How to Connect PostgreSQL to Snowflake with an Open-Source FDW Syntax


-- 1. Enable extension
CREATE EXTENSION IF NOT EXISTS odbc_fdw;

-- 2. Register Snowflake server
aCREATE SERVER snowflake_srv FOREIGN DATA WRAPPER odbc_fdw
OPTIONS (
  odbc_DSN 'SnowflakeDSN',    -- defined in odbc.ini
  host       'ab12345.us-east-1.snowflakecomputing.com',
  port       '443',
  database   'ECOMMERCE',
  warehouse  'DEV_WH',
  role       'ANALYST'
);

-- 3. Map a PostgreSQL role to Snowflake credentials
CREATE USER MAPPING FOR app_user
SERVER snowflake_srv
OPTIONS (
  username 'SF_APP_USER',
  password '••••••••'
);

-- 4. Import Snowflake tables into a dedicated schema
IMPORT FOREIGN SCHEMA PUBLIC LIMIT TO (customers, products, orders, orderitems)
FROM SERVER snowflake_srv INTO snowflake;

Common Mistakes

Forgetting to qualify foreign tables. FIX: Add the foreign schema to search_path or fully-qualify names (snowflake.products).
Missing WHERE filters causes full-table scans in Snowflake. FIX: Push predicates down and select only required columns.

Frequently Asked Questions (FAQs)

Is the FDW stable for production?

Yes, but monitor latency and credit consumption.

Can I write data back to Snowflake?

Yes—use OPTIONS (updatable 'true') and grant DML privileges.

Does FDW respect Snowflake roles?

Role selected in the SERVER definition applies to every session, so create separate servers for different roles.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.