chrishuffman5/duckdb

DuckDB for Analytics

This skill covers how DuckDB fits into analytics workflows and BI ecosystems -- not DuckDB internals. For core DuckDB expertise (SQL dialect, extensions, vectorized execution, Python/R/WASM integration, version-specific features), see the duckdb skill in the database plugin.

When to Use This Skill

Use this skill for DuckDB in an analytics context:

• "Use DuckDB as a local data warehouse replacement"
• "Connect DuckDB to Tableau / Power BI / Superset"
• "Run OLAP queries on Parquet files with DuckDB"
• "DuckDB vs a cloud data warehouse for my analytics workload"
• "Embedded analytics engine for a small team"
• "Local-first analytics without a server"

Route elsewhere when the question is about DuckDB itself:

• "DuckDB window functions", "DuckDB extension installation", "DuckDB 1.5 VARIANT type" -- the duckdb skill in the database plugin
• "DuckDB vs Tableau" or "which BI tool should I use?" -- the overview skill in this plugin (different categories; DuckDB is an engine, Tableau is a BI tool)
• DuckDB inside a dbt/Airflow/ingestion pipeline -- the duckdb skill in the etl plugin

Why DuckDB for Analytics

DuckDB occupies a unique position in the analytics stack: it is a query engine, not a BI tool. It replaces the warehouse layer for workloads that fit on a single machine, while BI tools sit on top for visualization.

• Local-first analytics -- No cloud account, no server, no credentials. Install a Python package or CLI binary and start querying files immediately. Ideal for data exploration, prototyping, and development environments.
• OLAP on files -- Query Parquet, CSV, JSON, and Excel files directly with full analytical SQL (window functions, CTEs, PIVOT, GROUPING SETS). No ingestion step required.
• Small-to-medium data warehouse replacement -- For datasets under ~200 GB, DuckDB on a single machine often outperforms cloud warehouses while costing nothing. Eliminates per-query cloud compute charges.
• Embedded analytics backend -- DuckDB runs in-process (Python, Node.js, WASM), making it suitable as the query engine behind lightweight dashboards, internal tools, and data apps without deploying a separate database server.
• Development and testing -- Use DuckDB locally to develop and test analytical queries before deploying them against a production warehouse.

BI Tool Integration

DuckDB connects to standard BI tools via JDBC and ODBC drivers:

| BI Tool | Connection Method | Notes | |---|---|---| | Tableau | JDBC (Generic ODBC/JDBC) | Use the DuckDB JDBC driver; supports live connection and extract | | Power BI | ODBC | DuckDB ODBC driver; DirectQuery or Import mode | | Apache Superset | SQLAlchemy (duckdb://) | Native DuckDB dialect via the duckdb-engine Python package | | Metabase | Community driver | Third-party DuckDB driver available | | Grafana | Plugin | Community DuckDB data source plugin | | Evidence | Native | First-class DuckDB support for code-driven reporting | | Rill | Native | Built on DuckDB; designed for fast exploratory dashboards | | Observable | DuckDB-WASM | Browser-based analytics with DuckDB running client-side |

Analytical Query Patterns

DuckDB excels at these common analytics patterns (see the database plugin's duckdb skill for full SQL reference):

• Ad-hoc exploration -- SUMMARIZE, DESCRIBE, SELECT * FROM 'file.parquet' LIMIT 100 for rapid data profiling
• Aggregation pipelines -- GROUP BY ALL, GROUPING SETS, CUBE, ROLLUP for multi-level summaries
• Window analytics -- QUALIFY clause, ranking, running totals, moving averages, lead/lag analysis
• Pivot reporting -- PIVOT / UNPIVOT for crosstab reports without complex CASE expressions
• File-based federation -- Join across Parquet, CSV, and JSON files in a single query; query S3/GCS/Azure via httpfs
• Time-series analysis -- ASOF JOIN for point-in-time lookups, generate_series for date spines, date_trunc / date_part for temporal grouping

When DuckDB Replaces a Data Warehouse

| Scenario | DuckDB Fits | Cloud Warehouse Fits | |---|---|---| | Data volume | Under ~200 GB | Over 200 GB or growing fast | | Concurrent users | 1-5 analysts | Dozens to hundreds | | Query latency SLA | Best-effort is fine | Sub-second guaranteed | | Budget | Zero or minimal | Enterprise budget available | | Data freshness | File-based, batch refresh | Real-time / streaming ingestion | | Governance | Lightweight / team-level | Enterprise (RBAC, audit, lineage) | | Deployment | Local, CI/CD, edge, embedded | Centralized cloud platform |

Architecture Patterns

Pattern 1: File-based analytics lakehouse

• Store data as Parquet in S3/local disk
• Query with DuckDB (via httpfs for remote, direct path for local)
• Visualize with Superset, Evidence, or Rill

Pattern 2: dbt + DuckDB for local analytics engineering

• Use dbt Core with the dbt-duckdb adapter
• Develop and test models locally against DuckDB
• Optionally promote to a cloud warehouse for production

Pattern 3: Embedded analytics in applications

• DuckDB runs in-process (Python/Node.js/WASM)
• Application loads Parquet files or receives Arrow data
• DuckDB executes analytical queries; results rendered in the UI
• No separate database server to deploy or maintain

Cross-References

| Scenario | Route To | |---|---| | Core DuckDB expertise (SQL, extensions, config, internals) | duckdb skill, database plugin | | DuckDB version-specific features | duckdb skill references, database plugin | | DuckDB for ETL / data transformation | duckdb skill, etl plugin | | BI tool selection (which tool, not which engine) | overview skill, this plugin | | Data pipeline feeding DuckDB analytics | etl plugin |