❄️🦆 SnowDuck

Run Snowflake SQL locally, powered by DuckDB

SnowDuck is a lightweight, in-memory SQL engine that emulates Snowflake's behavior for development and testing. Write and test Snowflake SQL locally without cloud access or costs.

Why SnowDuck?

• 🚀 Fast Development - Test SQL queries instantly without waiting for cloud connections
• 💰 Zero Cloud Costs - Develop and test locally without Snowflake compute charges
• 🧪 Easy Testing - Mock Snowflake databases for unit tests and CI/CD pipelines
• ⚡ Lightning Fast - Powered by DuckDB's in-memory execution engine
• 🔌 Drop-in Compatible - Uses Snowflake's connector interface - just patch and go

Features

Core SQL Support

Function Support

Snowflake Semantics, Not Just Snowflake Syntax

SnowDuck matches Snowflake's behaviour, not only its function names. The cases below all return a different answer under a naive DuckDB translation, so they are pinned by an executable conformance suite (tests/conformance/):

Snowflake Identifier Semantics

Unquoted identifiers fold to upper case, quoted ones keep their case - the same rule Snowflake applies:

CREATE TABLE my_model (id INT);   -- creates MY_MODEL with column ID
CREATE TABLE "MixedCase" (x INT); -- creates MixedCase

This is what makes a name read back out of the catalog match the name a client builds for it. dbt-snowflake, for instance, lists a schema with SHOW OBJECTS and then looks each relation up under the upper-cased name its own Relation renders to.

SQL User-Defined Functions

cur.execute("""
    CREATE OR REPLACE FUNCTION is_valid_id(id VARCHAR)
    RETURNS BOOLEAN
    AS $$ LENGTH(id) = 11 AND REGEXP_LIKE(id, '[0-9]+') $$
""")

cur.execute("SELECT is_valid_id('12345678901')")  # -> True

Scalar and table UDFs are compiled to DuckDB macros, and the body is translated through the same pipeline as any other statement - so Snowflake functions used inside a UDF work too. Non-SQL UDFs (JavaScript, Python) raise a clear error rather than silently returning the wrong thing.

Cursor Methods

SnowDuck supports all standard Snowflake cursor methods:

• execute() - Execute SQL statements
• fetchone() - Fetch a single row
• fetchmany(size) - Fetch multiple rows
• fetchall() - Fetch all rows
• fetch_pandas_all() - Fetch all rows as pandas DataFrame
• fetch_pandas_batches() - Fetch rows as iterator of DataFrames
• get_result_batches() - Get Arrow record batches
• describe() - Get result schema without execution

Note: SnowDuck is designed for development and testing. Use production Snowflake for production workloads.

Quick Start

Installation

# Using uv (recommended)
uv pip install snowduck

# Or using pip
pip install snowduck

Basic Usage

import snowflake.connector
from snowduck import start_patch_snowflake

# Patch the Snowflake connector to use DuckDB
start_patch_snowflake()

# Use Snowflake connector as normal - it's now backed by DuckDB!
with snowflake.connector.connect() as conn:
    cursor = conn.cursor()
    
    cursor.execute("CREATE DATABASE my_database")
    cursor.execute("USE DATABASE my_database")
    
    cursor.execute("""
        CREATE TABLE employees (id INTEGER, name VARCHAR, salary INTEGER)
    """)
    
    cursor.execute("""
        INSERT INTO employees VALUES
        (1, 'Alice', 95000),
        (2, 'Bob', 75000),
        (3, 'Carol', 105000)
    """)
    
    cursor.execute("""
        SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank
        FROM employees
    """)
    
    for row in cursor.fetchall():
        print(f"{row[0]}: \${row[1]:,} (Rank: {row[2]})")

Data Persistence

# In-memory (default) - fast, isolated
start_patch_snowflake()

# File-based - persistent across restarts, including CREATE DATABASE
# (each database is stored beside it as my_data.<NAME>.duckdb)
start_patch_snowflake(db_file='my_data.duckdb')

# Fresh start - reset existing data
start_patch_snowflake(db_file='my_data.duckdb', reset=True)

Test Data Seeding

from snowduck import seed_table

with snowflake.connector.connect() as conn:
    # From dict
    seed_table(conn, 'customers', {
        'id': [1, 2, 3],
        'name': ['Acme', 'TechStart', 'DataCo']
    })
    
    # From pandas DataFrame
    seed_table(conn, 'orders', df)

Testing

Using the Decorator

from snowduck import mock_snowflake

@mock_snowflake
def test_query():
    conn = snowflake.connector.connect()
    cursor = conn.cursor()
    cursor.execute("SELECT 1")
    assert cursor.fetchone()[0] == 1

Using the Context Manager

from snowduck import patch_snowflake

def test_with_fixture():
    with patch_snowflake():
        conn = snowflake.connector.connect()
        # Test code here

pytest Fixture

import pytest
from snowduck import patch_snowflake

@pytest.fixture
def conn():
    with patch_snowflake():
        yield snowflake.connector.connect()

def test_feature(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT 1")

REST API Server

# Install with server extras
uv pip install snowduck[server]

# Start the server
uvicorn snowduck.server:app --reload

The server provides:

• Execute SQL queries via REST API
• Arrow IPC format responses
• Multi-session support

Architecture

┌─────────────────────┐
│  Your Application   │
│  (Snowflake code)   │
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│  SnowDuck Patch     │  ← Intercepts connector calls
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│  SQL Translator     │  ← AST rewrites, then sqlglot's
└──────────┬──────────┘    DuckDB generator
           │
           │
           ▼
┌─────────────────────┐
│   DuckDB Engine     │  ← Fast in-memory execution
└─────────────────────┘

Examples

See the examples/ directory for Jupyter notebooks demonstrating:

• Basic operations and queries
• String, date, and numeric functions
• JSON and array operations
• Window functions
• Advanced SQL patterns

Development

git clone https://github.com/hupe1980/snowduck.git
cd snowduck
uv sync
just test
just check

Contributing

Contributions welcome! See issues for areas where help is needed.

License

MIT License - see LICENSE for details.