Downloads 7.2kthis week

GitHub stars 8

Extension descriptor (YAML)

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

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

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

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

This extension does not add any settings.