Downloads 332this week

GitHub stars 6

Extension repository on GitHub

Extension descriptor (YAML)

Intelligent query result caching between DuckDB and Snowflake with TTL and smart refresh

Installing and Loading

INSTALL ducksync FROM community;
LOAD ducksync;

Example

-- Install and load the extension
INSTALL ducksync FROM community;
LOAD ducksync;

-- Option A: Use an existing DuckLake catalog
ATTACH 'ducklake:postgres:host=localhost dbname=ducklake user=postgres password=secret' AS my_lake (DATA_PATH '/data');
SELECT * FROM ducksync_init('my_lake');

-- Option B: Full setup (attaches DuckLake automatically)
SELECT * FROM ducksync_setup_storage(
    'host=localhost port=5432 dbname=ducklake user=postgres password=secret',
    '/data/ducksync'
);

-- Register Snowflake source (requires existing DuckDB secret)
SELECT * FROM ducksync_add_source('prod', 'snowflake', 'my_snowflake_secret');

-- Create a cache with 1-hour TTL
SELECT * FROM ducksync_create_cache(
    'customers_cache',
    'prod',
    'SELECT * FROM PROD.PUBLIC.CUSTOMERS',
    ['PROD.PUBLIC.CUSTOMERS'],
    3600
);

-- Query via smart routing (cache hit or passthrough)
SELECT * FROM ducksync_refresh('customers_cache');

-- Query via smart routing: cache hit → DuckLake, miss → Snowflake passthrough
SELECT * FROM ducksync_query(
    'SELECT * FROM PROD.PUBLIC.CUSTOMERS WHERE region = ''US''',
    'prod'
);

-- Or query the DuckLake table directly
SELECT * FROM ducksync.prod.customers_cache;

About ducksync

DuckSync provides intelligent query result caching between DuckDB and Snowflake. It uses DuckLake for storage (PostgreSQL catalog + Parquet files) and features transparent query routing, TTL-based expiration, and smart refresh based on source table metadata.

Core workflow:

1. Register a Snowflake source with ducksync_add_source
2. Define a cache with ducksync_create_cache (SQL query + monitor tables + TTL)
3. Populate with ducksync_refresh (fetches from Snowflake, stores in DuckLake)
4. Query with ducksync_query — automatically routes to cache or Snowflake

Features:

• Smart Query Routing: ducksync_query() automatically routes to cache or Snowflake
• Named Queries: Cache complex queries under friendly names
• TTL Support: Configurable cache expiration with automatic refresh
• Smart Refresh: Only refreshes when source tables have changed (checks last_altered)
• Direct Access: Query cached data as standard DuckLake tables
• AST-Based Rewriting: Safe query transformation that only modifies table references
• Configurable Metadata Schema: Pass an optional schema_name to ducksync_init or ducksync_setup_storage for multi-tenant environments

Prerequisites:

• PostgreSQL database (for DuckLake catalog)
• Snowflake account with a configured DuckDB secret
• ADBC Snowflake driver (see duckdb-snowflake setup)

Query Routing Logic:

1. Parses SQL using DuckDB's parser to extract table references
2. Checks if each table is cached (by cache name or monitored table)
3. If ALL tables cached → rewrites query to use local DuckLake tables
4. If ANY table not cached → passes entire query to Snowflake

For complete documentation, visit the extension repository.

Added Functions