DuckDB for ETL

This skill adds ETL-domain context on top of the primary DuckDB skill. For all core DuckDB expertise (SQL dialect, extensions, vectorized execution, Python/R/WASM integration, version-specific features), see the database plugin's duckdb skill. This skill focuses on how DuckDB fits into data transformation and pipeline workflows.

Primary skill: the database plugin's duckdb skill -- load this for any question about DuckDB internals, SQL syntax, extensions, configuration, or version-specific features.

When to Use This Skill

Use this skill when the question is about DuckDB in an ETL/ELT context:

• "Use DuckDB to convert CSV files to Parquet"
• "Replace Spark with DuckDB for our transformation pipeline"
• "DuckDB as a dbt adapter for local development"
• "Build a file-based ETL pipeline with DuckDB"
• "Query a data lake with DuckDB instead of Spark/Athena"
• "Orchestrate DuckDB transformations with Airflow"

See the primary DuckDB skill when the question is about DuckDB itself:

• "DuckDB read_parquet options" --> the database plugin's duckdb skill
• "DuckDB extension ecosystem" --> the database plugin's duckdb skill
• "DuckDB 1.4 MERGE statement" --> the database plugin's duckdb skill, references/versions/1.4.md

See the transformation skill for tool comparisons:

• "dbt vs Spark vs DuckDB" --> transformation skill
• "Which transformation tool for my pipeline?" --> transformation skill

DuckDB as a Transformation Engine

Why DuckDB for ETL

DuckDB fills a gap between manual scripting and distributed compute frameworks. It provides a full analytical SQL engine that runs in-process with zero infrastructure:

• Spark replacement for small-to-medium data -- Datasets under ~200 GB do not need a distributed cluster. DuckDB on a single machine processes them faster than Spark (no JVM startup, no shuffle overhead, no cluster management) at zero infrastructure cost.
• File-native ingestion -- Read Parquet, CSV, JSON, Excel, and Avro directly with read_parquet(), read_csv(), read_json(), read_xlsx(). Glob patterns and Hive partitioning are built in. No schema definition or import step needed.
• SQL-based transformations -- Full analytical SQL with CTEs, window functions, PIVOT, UNION BY NAME, and complex type handling. Data engineers who think in SQL can express transformations without learning DataFrame APIs.
• In-process execution -- No server to deploy, no ports to open, no credentials to manage. DuckDB runs as a library inside Python, Node.js, or CLI scripts. Ideal for CI/CD pipelines and containerized jobs.
• Zero-copy integration -- In Python, DuckDB queries Pandas DataFrames, Polars LazyFrames, and Arrow tables without copying data. This makes it a natural transformation layer in Python-based pipelines.

Data Ingestion Patterns

DuckDB handles the "E" and "L" of ELT natively (see primary agent for full syntax):

Source Format	Reader	Key Options
Parquet	read_parquet()	hive_partitioning, glob patterns, S3/GCS/Azure via httpfs
CSV/TSV	read_csv()	header, delim, columns, union_by_name, filename
JSON/NDJSON	read_json()	format, columns, auto-detection
Excel	read_xlsx()	sheet, range selection
Avro	read_avro()	Schema evolution support
SQLite	sqlite_scan()	Direct federation, no migration needed
PostgreSQL	postgres_scan()	Live query federation via postgres_scanner extension
MySQL	mysql_scan()	Live query federation via mysql_scanner extension

Transformation Patterns

Common ETL transformations expressed in DuckDB SQL:

• Format conversion -- COPY (SELECT * FROM 'input.csv') TO 'output.parquet' (FORMAT PARQUET, COMPRESSION ZSTD) converts any supported format to Parquet with compression in a single statement.
• Schema harmonization -- UNION ALL BY NAME merges files with different column sets by matching on column name rather than position. Critical for ingesting data from multiple sources with evolving schemas.
• Incremental processing -- Use read_parquet() with filename column and Hive partition filtering to process only new partitions. Combine with CREATE OR REPLACE TABLE for idempotent overwrites.
• Data quality checks -- SQL assertions (SELECT count(*) FROM staging WHERE id IS NULL with threshold checks) validate data inline. No external framework needed for basic quality gates.
• Partitioned writes -- COPY ... TO 'output/' (FORMAT PARQUET, PARTITION_BY (year, month)) produces Hive-partitioned output ready for downstream consumption by Spark, Athena, or other Parquet-aware tools.
• Cross-source joins -- Join a CSV lookup table against a Parquet fact table against a PostgreSQL dimension table in a single query, producing Parquet output.

dbt-duckdb Integration

The dbt-duckdb adapter makes DuckDB a first-class dbt target:

• Local development -- Develop and test dbt models against DuckDB locally before deploying to a cloud warehouse (Snowflake, BigQuery, Redshift). Fast iteration without cloud compute costs.
• CI/CD testing -- Run dbt build against DuckDB in CI pipelines for sub-minute test cycles. DuckDB reads seed files and source fixtures natively.
• Production for small workloads -- For datasets under ~50 GB, DuckDB can serve as the production warehouse itself, with dbt managing the transformation layer.
• External sources plugin -- The dbt-duckdb adapter supports external materializations and source plugins that read directly from Parquet/CSV files, S3 paths, or even Pandas DataFrames.

Pipeline Orchestration Patterns

DuckDB integrates cleanly into orchestration frameworks:

Orchestrator	Integration Pattern
Airflow	PythonOperator or BashOperator running DuckDB CLI/Python scripts. Lightweight -- no cluster provisioning or JDBC connections needed.
Dagster	DuckDB resource with I/O manager. Native Dagster integration via dagster-duckdb package.
Prefect	Python tasks using duckdb package directly. In-process execution fits Prefect's lightweight task model.
Shell scripts	duckdb < transform.sql for cron-scheduled jobs. Zero dependencies beyond the CLI binary.
CI/CD (GitHub Actions, etc.)	Install DuckDB CLI or Python package, run transformation scripts as pipeline steps.

When DuckDB Replaces Spark

Scenario	DuckDB Fits	Spark Fits
Data volume	Under ~200 GB	Over 200 GB or multi-TB
Compute model	Single machine	Distributed cluster
Language	SQL-first	Python/Scala DataFrame API
Startup time	Milliseconds	30-60 seconds (JVM, cluster)
Infrastructure	Zero (in-process)	Cluster manager (YARN, K8s, Databricks)
Use case	File conversion, SQL transforms, dbt models	ML pipelines, graph processing, streaming
Cost	Free	Cluster compute (EMR, Dataproc, Databricks)
Team skills	SQL-proficient data engineers	Python/Scala engineers with Spark experience

Cross-References

Scenario	Route To
Core DuckDB expertise (SQL, extensions, config, internals)	The database plugin's duckdb skill
DuckDB version-specific features (1.4, 1.5)	The database plugin's duckdb skill, references/versions/{version}.md
DuckDB for analytics / BI integration	The analytics plugin's duckdb skill
Transformation tool comparison (dbt vs Spark vs DuckDB)	transformation skill
ETL architecture and pipeline design	overview skill
dbt Core expertise	dbt-core skill
Orchestration (Airflow, SSIS)	orchestration skill