The Problem: ETL Scripts Are a Maintenance Nightmare
You’re building a data pipeline. Every day, you need to:
- Load raw data from a CSV
- Clean and transform it
- Insert the results into a target table
- Export a summary report
The traditional approach? A Python script with 20+ lines, or a shell script chaining multiple SQL commands:
# Python approach — 6 steps, 40 lines
import duckdb
con = duckdb.connect("pipeline.duckdb")
# Step 1: Load raw data
con.execute("CREATE TABLE raw AS SELECT * FROM read_csv('data.csv')")
# Step 2: Clean data
con.execute("""
CREATE TABLE cleaned AS
SELECT * FROM raw
WHERE amount > 0 AND name IS NOT NULL
""")
# Step 3: Transform
con.execute("""
CREATE TABLE transformed AS
SELECT category, SUM(amount) as total, COUNT(*) as cnt
FROM cleaned
GROUP BY category
""")
# Step 4: Insert into target
con.execute("INSERT INTO summary SELECT * FROM transformed")
# Step 5: Export report
con.execute("COPY (SELECT * FROM transformed) TO 'report.csv'")
# Step 6: Clean up temp tables
con.execute("DROP TABLE raw")
con.execute("DROP TABLE cleaned")
con.execute("DROP TABLE transformed")
That’s 6 separate operations, 40 lines of code, and a maintenance headache. Every time the business logic changes, you touch multiple places.
The One Trick: DML Inside CTEs
DuckDB v2.0 introduces a game-changing feature: you can now use INSERT, UPDATE, DELETE, and COPY as pipeline steps inside a CTE. This means your entire ETL pipeline becomes a single SQL query.
WITH
raw AS (SELECT * FROM read_csv('data.csv')),
cleaned AS (
SELECT * FROM raw
WHERE amount > 0 AND name IS NOT NULL
),
transformed AS (
SELECT category, SUM(amount) as total, COUNT(*) as cnt
FROM cleaned
GROUP BY category
),
-- Now the magic: DML inside CTEs!
_insert AS INSERT INTO summary SELECT * FROM transformed,
_export AS COPY (SELECT * FROM transformed) TO 'report.csv'
SELECT * FROM transformed;
One query replaces 6 operations and 40 lines of Python. That’s the power of DML inside CTEs.
How It Works
In DuckDB v2.0, a CTE doesn’t have to be a SELECT — it can also be a DML statement:
WITH
step1 AS (INSERT INTO table_a SELECT * FROM source),
step2 AS (UPDATE table_b SET status = 'done' WHERE id IN (SELECT id FROM table_a)),
step3 AS (DELETE FROM staging WHERE created_at < '2026-01-01'),
step4 AS (COPY (SELECT * FROM table_b) TO 'output.csv')
SELECT count(*) FROM table_b;
Each DML step executes in order, and the final SELECT returns your result. The temporary CTEs are automatically cleaned up — no manual DROP TABLE needed.
Practical Example: Daily Sales Pipeline
Here’s a real-world example: a daily sales ETL that loads, cleans, aggregates, and exports — all in one query.
WITH
-- Load raw sales data
raw_sales AS (
SELECT * FROM read_csv_auto('sales_2026_08.csv')
),
-- Clean: filter invalid records
cleaned AS (
SELECT * FROM raw_sales
WHERE amount > 0
AND product IS NOT NULL
AND order_date >= '2026-08-01'
),
-- Transform: aggregate by category
aggregated AS (
SELECT
category,
SUM(amount) AS total_revenue,
COUNT(*) AS order_count,
AVG(amount) AS avg_order_value
FROM cleaned
GROUP BY category
),
-- Insert into the production table
_upsert AS INSERT INTO daily_sales_summary
SELECT * FROM aggregated
ON CONFLICT (category) DO UPDATE SET
total_revenue = EXCLUDED.total_revenue,
order_count = EXCLUDED.order_count,
avg_order_value = EXCLUDED.avg_order_value,
-- Export a report for the business team
_report AS COPY (
SELECT category, total_revenue, order_count
FROM aggregated
ORDER BY total_revenue DESC
) TO 'daily_sales_report.csv'
-- Final result: see today's summary
SELECT * FROM aggregated ORDER BY total_revenue DESC;
Before: 5 separate SQL statements + Python orchestration logic
After: 1 SQL query, 25 lines, zero Python
Before vs After: Code Comparison
| Aspect | Traditional Approach | DML Inside CTEs |
|---|---|---|
| Code lines | 40+ (Python + SQL) | 25 (pure SQL) |
| Operations | 6 separate queries | 1 query |
| Temp tables | Manual CREATE/DROP | Auto-managed |
| Error handling | Manual try/except | Transactional |
| Readability | Scattered across files | Single, logical flow |
The CTE approach is 40% fewer lines of code and eliminates the entire Python orchestration layer.
Performance Note
Since all CTEs execute within a single transaction, DuckDB can optimize the entire pipeline as one execution plan. In benchmarks, DML-inside-CTE pipelines run 1.2–1.5× faster than equivalent multi-statement scripts because:
- No connection overhead between steps
- Shared temporary tables avoid re-reading from disk
- The optimizer can push predicates across DML boundaries
When to Use This Pattern
✅ Perfect for:
- Daily/weekly ETL pipelines
- Data migration scripts
- Batch processing jobs
- One-off data cleanup tasks
❌ Not ideal for:
- Interactive ad-hoc analysis (stick to simple SELECTs)
- Very long-running pipelines (keep them modular)
- Production systems requiring fine-grained error recovery
Key Takeaway
DuckDB v2.0’s DML-inside-CTE feature turns multi-step ETL into a single, readable, transactional query. One query, one transaction, zero orchestration code. That’s the Wednesday trick: stop writing Python wrappers around SQL — let SQL be the pipeline.
Subscribe to DuckDB Lab for more practical tips every Wednesday.