DuckDB Federated Query: Query All Databases with One SQL Without Moving Data

The most frustrating thing every day: production database is PostgreSQL, business logs are in MySQL, user profiles are in SQLite. Every time you need to analyze, it takes half an hour just to export the data.

Today I’ll show you a trick to make DuckDB directly “see through” all data sources. Data doesn’t need to move, SQL queries work directly.

1. Why Do You Need Federated Query?

1.1 Pain Points of Traditional Approaches

In most companies, data is scattered across different systems:

Traditional analysis workflow:

Step 1: Write Python scripts to connect to each data source
Step 2: Export data one by one to local machine
Step 3: Merge and clean in Pandas
Step 4: Analyze and generate reports

Problems with this approach:

• High maintenance cost: Connection logic for each data source
• Performance bottleneck: Large amounts of data loaded into local memory
• Poor timeliness: Data export takes time, you’re looking at stale data
• Not reusable: Change the analysis requirement, rewrite the code

1.2 DuckDB’s Federated Query Solution

DuckDB’s federated query feature lets you “move queries, not data”:

All Data Sources ──ATTACH──→ DuckDB Unified Query Engine ──→ One SQL Query

Core advantages:

• Zero ETL: Query directly without exporting data
• Real-time: Live connection, see the latest data
• Unified SQL: Query all data sources with the same SQL
• Columnar optimization: Only read needed columns, reduce network transfer

2. Core Principle: External Table Mechanism

DuckDB’s federated query essentially maps external database tables as DuckDB virtual tables. During queries, it fetches only the required data in real-time without copying.

2.1 Two Usage Methods

Method 1: ATTACH Syntax (Recommended, set and forget)

import duckdb

conn = duckdb.connect()

# Attach PostgreSQL database
conn.execute("ATTACH 'dbname=production host=localhost user=postgres' AS pg (READ_ONLY)")

# Attach MySQL database
conn.execute("ATTACH 'host=localhost port=3306 user=root password=secret dbname=logs' AS mysql (READ_ONLY, TYPE mysql)")

# Attach SQLite database
conn.execute("ATTACH 'users.db' AS sqlite (READ_ONLY)")

Method 2: read_ Functions (Query on the fly, for ad-hoc queries)*

# Query remote PostgreSQL directly (no ATTACH needed)
result = conn.execute("""
    SELECT * FROM read_parquet('s3://bucket/data/*.parquet')
""").fetchdf()

# Query JSON from HTTP API
result = conn.execute("""
    SELECT * FROM read_json_auto('https://api.example.com/data.json')
""").fetchdf()

3. Practical Scenarios

3.1 Scenario 1: Query PostgreSQL Directly

import duckdb

conn = duckdb.connect()

# Attach PostgreSQL (read-only mode ensures you won't accidentally modify production data)
conn.execute("ATTACH 'dbname=production host=localhost user=postgres' AS pg (READ_ONLY)")

# Cross-database query: orders + customer info
result = conn.execute("""
    SELECT 
        p.name, 
        SUM(o.amount) as total_sales,
        COUNT(DISTINCT o.id) as order_count
    FROM pg.public.orders o
    JOIN pg.public.customers c ON o.customer_id = c.id
    WHERE o.created_at >= '2026-07-01'
    GROUP BY p.name
    ORDER BY total_sales DESC
    LIMIT 10
""").fetchdf()

print(result)

Key point: The READ_ONLY mode ensures you won’t accidentally modify production data.

3.2 Scenario 2: Query MySQL

# Attach MySQL
conn.execute("""
    ATTACH 'host=localhost port=3306 user=root password=secret dbname=logs' 
    AS mysql (READ_ONLY, TYPE mysql)
""")

# Query log data
result = conn.execute("""
    SELECT 
        DATE(created_at) as day, 
        COUNT(*) as events,
        COUNT(DISTINCT user_id) as active_users
    FROM mysql.public.events
    WHERE created_at >= '2026-08-01'
    GROUP BY DATE(created_at)
    ORDER BY day
""").fetchdf()

print(result)

3.3 Scenario 3: Query SQLite

# Attach SQLite (no additional driver installation needed)
conn.execute("ATTACH 'users.db' AS sqlite (READ_ONLY)")

# Query user data
result = conn.execute("""
    SELECT * FROM sqlite.users 
    WHERE active = true 
    ORDER BY created_at DESC
    LIMIT 100
""").fetchdf()

3.4 Scenario 4: Cross-Database JOIN — The Killer Feature

This is where federated query shines: join data from different sources in a single SQL query!

# Assume:
# - pg.public.users: User table in PostgreSQL
# - sqlite.sessions: Session records in SQLite
# - sales/*.parquet: Sales data in Parquet format

result = conn.execute("""
    SELECT 
        p.name,
        s.total_sales,
        COUNT(sl.id) as login_count,
        AVG(sl.duration) as avg_session_duration
    FROM pg.public.users p
    JOIN sqlite.sessions sl ON p.id = sl.user_id
    JOIN read_parquet('sales/*.parquet') s ON p.id = s.user_id
    GROUP BY p.name, s.total_sales
    ORDER BY s.total_sales DESC
""").fetchdf()

print(result)

Breakdown:

1. pg.public.users → Read user info from PostgreSQL in real-time
2. sqlite.sessions → Read session records from SQLite
3. read_parquet('sales/*.parquet') → Read sales data from Parquet files
4. All three data sources JOIN in the DuckDB engine, returning results

Throughout this process, no data is exported. DuckDB automatically optimizes the query plan, fetching only the needed columns and rows.

4. Quick Reference Table

5. Performance Comparison: Federated Query vs Traditional ETL

Test environment: 8-core 16GB MacBook Pro, 1M rows each in PostgreSQL + MySQL + SQLite.

Performance advantages of federated query:

1. Predicate pushdown: WHERE conditions pushed to remote database, only results returned
2. Columnar read: Only read needed columns, reduce network transfer
3. Vectorized execution: DuckDB’s columnar engine accelerates computation
4. Streaming processing: Process while reading for large datasets, won’t overflow memory

6. Pitfall Guide

6.1 Always Use READ_ONLY for Production

# ✅ Correct: Always use READ_ONLY
conn.execute("ATTACH 'dbname=production host=localhost' AS pg (READ_ONLY)")

# ❌ Dangerous: Forgetting READ_ONLY might accidentally modify production data
conn.execute("ATTACH 'dbname=production host=localhost' AS pg")

6.2 Filter Before JOIN on Large Tables

# ✅ Correct: Filter first, then JOIN, reducing data transfer
result = conn.execute("""
    SELECT * FROM pg.public.orders 
    WHERE created_at >= '2026-08-01'
""").fetchdf()

result2 = conn.execute("""
    SELECT * FROM sqlite.users 
    WHERE active = true
""").fetchdf()

# Join in DuckDB memory
final = conn.execute("""
    SELECT * FROM result2 
    JOIN result ON result2.id = result.user_id
""").fetchdf()

# ❌ Dangerous: Direct cross-database JOIN on large tables causes heavy network transfer
result = conn.execute("""
    SELECT * FROM pg.public.orders o
    JOIN sqlite.users u ON o.user_id = u.id
""").fetchdf()

6.3 Handle Network Latency

When doing cross-database queries, network latency affects performance. Suggestions:

• Local development: Run databases in Docker to reduce network overhead
• Production: Consider importing frequently-used data into DuckDB files, use federated query as supplement

6.4 Reuse Connection Pools

# ✅ Correct: Reuse Connection object
conn = duckdb.connect()
conn.execute("ATTACH '...' AS pg (READ_ONLY)")
conn.execute("ATTACH '...' AS mysql (READ_ONLY)")

# Reuse same connection for multiple queries
for date_range in date_ranges:
    result = conn.execute(f"""
        SELECT * FROM pg.public.orders 
        WHERE created_at BETWEEN '{date_range[0]}' AND '{date_range[1]}'
    """).fetchdf()

# ❌ Wrong: Create new connection each time
for date_range in date_ranges:
    conn = duckdb.connect()  # Reconnect, poor performance
    conn.execute("ATTACH '...' AS pg (READ_ONLY)")
    ...

7. Complete Practical Project: Automated Competitor Price Monitoring

Combining with the price monitoring concept from earlier:

import duckdb
import schedule
import time

def daily_price_analysis():
    conn = duckdb.connect()
    
    # Attach all data sources
    conn.execute("ATTACH 'production.db' AS pg (READ_ONLY)")
    conn.execute("ATTACH 'competitor_prices.csv' AS comp (TYPE CSV)")
    
    # Analyze competitor price changes
    result = conn.execute("""
        SELECT 
            c.product_name,
            c.current_price,
            p.avg_price as our_price,
            ROUND((c.current_price - p.avg_price) / p.avg_price * 100, 2) as price_gap_pct
        FROM comp.competitor_prices c
        LEFT JOIN pg.public.our_prices p ON c.product_id = p.product_id
        WHERE c.updated_at >= CURRENT_DATE - INTERVAL '7' DAY
        ORDER BY price_gap_pct ASC
    """).fetchdf()
    
    # Output report
    print("📊 Competitor Price Analysis Report")
    print(result.to_string(index=False))
    
    # Export JSON for downstream use
    result.to_json("price_report.json", orient='records', indent=2)

# Run every day at 8 AM
schedule.every().day.at("08:00").do(daily_price_analysis)

while True:
    schedule.run_pending()
    time.sleep(60)

8. Comparison with Traditional Tools

9. Monetization Suggestions

9.1 Short-term Paths

1. Data Analysis Service: Provide “one-click multi-source integration” analysis for SMEs, ¥500-2000 per project
2. Automated Reports: Build automated daily/weekly report systems for clients, ¥299-999/month subscription
3. Price Monitoring SaaS: Build competitor price monitoring systems using federated query, SaaS ¥99-299/month

9.2 Mid-term Productization

1. Data Integration Tool: Develop a visual data source management tool with UI for ATTACH configuration
2. Federated Query Platform: Shared query platform for teams, collaborative multi-source analysis
3. Industry Data Products: Build industry analysis reports based on public data sources (government open data, APIs)

9.3 Long-term Commercialization

1. DaaS (Data as a Service): Package organized multi-source data as API services
2. Data Analysis Training: Record federated query tutorials for knowledge monetization
3. Enterprise Consulting: Provide data integration solution design services for large enterprises

10. Summary

DuckDB’s federated query feature makes “move queries, not data” a reality. You no longer need to write complex ETL pipelines—just one SQL query to analyze across all data sources.

Key takeaways:

1. ATTACH syntax is the foundation of federated query, supporting PostgreSQL, MySQL, SQLite and more
2. READ_ONLY mode ensures production data safety
3. Predicate pushdown lets remote databases filter first, returning only result sets with excellent performance
4. Cross-database JOIN is the killer feature—one SQL to join all data sources

Master this skill, and your data analysis efficiency will improve by an order of magnitude.

For more DuckDB tips, visit duckdblab.org.