An ETL pipeline extracts data from a source, transforms it into a clean shape, and loads it somewhere it can be queried. This tutorial builds one with Python and DuckDB 2.0, an in-process database that needs no server. We turn a messy sales CSV into a clean table and a Parquet file, showing the real output of every step.
Key takeaways
- DuckDB runs inside the Python process, so there is no server to install or manage, as the DuckDB documentation explains.
- Reading a CSV with all_varchar=true keeps every value as text, so a bad cast never drops a row silently.
- try_strptime with several formats parses mixed date styles in one pass, and coalesce picks the first format that works.
- QUALIFY with row_number removes duplicate order ids without a second table or a separate dedupe script.
- COPY TO exports the clean table to Parquet, which keeps column types and compresses well for the next step of an analysis.
What are we building?
We build a small sales pipeline with three stages. Extract reads a messy sales_raw.csv file. Transform cleans it in SQL. Load writes a clean DuckDB table, a Parquet file, and a revenue summary CSV. The whole pipeline is one Python file, about 60 lines, and it runs in under a second on a laptop.
The raw file is deliberately messy, the way real exports are messy. It has 12 rows and five problems: a duplicated order, a missing quantity, a negative quantity, dates in three different formats, and prices stored as text with dollar signs. Our job is to keep the 9 good rows and reject the 3 bad ones, with a count we can check.
What do you need before we start?
Python 3.10 or newer and nothing else. DuckDB installs as a single Python package, and the engine ships inside it. We ran this tutorial on Python 3.12 with DuckDB 2.0.0, installed with pip. Basic SQL helps, but every query below is explained line by line.
mkdir duckdb-etl && cd duckdb-etl
python -m venv venv
source venv/bin/activate
pip install duckdb
Check the install before going further:
python -c "import duckdb; print(duckdb.__version__)"
2.0.0
How do we extract the messy CSV?
First we create the raw file. Save this as sales_raw.csv. Note the duplicate order 1002, the empty quantity on order 1005, the negative quantity on order 1007, and the three date styles.
order_id,product,quantity,price,order_date,region
1001, Wireless Mouse ,2,"$25.99",2026-10-01,North
1002,KEYBOARD,1,"$49.50",01/10/2026,South
1003,Monitor Stand,1,"$89.00",Oct 3 2026,North
1004,Wireless Mouse,3,"$25.99",2026-10-03,East
1005,USB-C Hub,,"$34.99",2026-10-04,West
1002,KEYBOARD,1,"$49.50",01/10/2026,South
1006,Webcam HD,2,"$79.00",2026-10-05,north
1007,Monitor Stand,-1,"$89.00",2026-10-06,East
1008,Wireless Mouse,1,"$25.99",2026-10-07,South
1009,Desk Lamp,4,"$19.99",07/10/2026,West
1010,USB-C Hub,2,"$34.99",2026-10-08,North
1011,Webcam HD,1,"$79.00",Oct 9 2026,East
Now the extract step. We open a DuckDB file database and read the CSV with all_varchar=true, which forces every column to text. That is a deliberate choice. If DuckDB inferred types on read, the empty quantity and the dollar sign prices could fail the cast or drop rows before we ever see them. Reading as text first means we inspect everything, then decide what is valid.
import duckdb
con = duckdb.connect("sales.duckdb")
con.execute("""
CREATE OR REPLACE TABLE raw_sales AS
SELECT * FROM read_csv('sales_raw.csv', all_varchar = true)
""")
How do we clean the data in SQL?
The transform is a single SQL statement, and each line fixes one kind of mess. Product names get trimmed and lowercased, so " Wireless Mouse " and "KEYBOARD" become consistent values. Prices lose the dollar sign and any comma, then cast to a decimal with two places. Dates go through try_strptime three times, once per format, and coalesce keeps the first format that parses. A date that matches no format becomes null, which is honest: it tells us the row needs attention instead of guessing.
con.execute("""
CREATE OR REPLACE TABLE clean_sales AS
SELECT
order_id::INTEGER AS order_id,
lower(trim(product)) AS product,
quantity::INTEGER AS quantity,
replace(replace(trim(price), '$', ''), ',', '')::DECIMAL(10, 2) AS price,
coalesce(
try_strptime(order_date, '%Y-%m-%d'),
try_strptime(order_date, '%d/%m/%Y'),
try_strptime(order_date, '%b %d %Y')
)::DATE AS order_date,
lower(trim(region)) AS region,
quantity::INTEGER
* replace(replace(trim(price), '$', ''), ',', '')::DECIMAL(10, 2)
AS revenue
FROM raw_sales
WHERE quantity IS NOT NULL
AND quantity::INTEGER > 0
QUALIFY row_number() OVER (
PARTITION BY order_id ORDER BY order_date
) = 1
""")
Two lines do the quality control. The WHERE clause drops order 1005, which has no quantity, and order 1007, whose quantity is negative and looks like a return rather than a sale. The QUALIFY clause numbers rows within each order id and keeps only the first, which removes the duplicated order 1002. DuckDB supports QUALIFY directly on the query, so dedupe needs no temporary table.
How do we load the clean data?
The clean table already lives in sales.duckdb, which is the warehouse for this pipeline. The load step exports two artefacts from it: a Parquet file for the next analysis, and a summary CSV with revenue by product.
con.execute("COPY clean_sales TO 'sales_clean.parquet' (FORMAT parquet)")
con.execute("""
COPY (
SELECT product,
sum(quantity) AS units,
round(sum(revenue), 2) AS total_revenue
FROM clean_sales
GROUP BY product
ORDER BY total_revenue DESC
) TO 'sales_summary.csv' (HEADER, DELIMITER ',')
""")
Parquet is the right export format here because it keeps the column types we just fixed, dates stay dates, and it compresses far better than a second CSV. The summary CSV is for humans: a file anyone can open in a spreadsheet.
What did the pipeline actually produce?
We ran the full script, and this is the real output. Twelve rows went in, nine clean rows came out, and three were rejected for the reasons we predicted: one duplicate, one missing quantity, one negative quantity.
$ python etl.py
extracted rows: 12
clean rows: 9
rejected rows: 3
revenue by product:
webcam hd units=3 revenue=$237.00
wireless mouse units=6 revenue=$155.94
monitor stand units=1 revenue=$89.00
desk lamp units=4 revenue=$79.96
usb-c hub units=2 revenue=$69.98
keyboard units=1 revenue=$49.50
parquet read-back rows: 9
done.
We also checked the clean table row by row to confirm the dates parsed. Order 1002, which arrived as 01/10/2026, became 2026-10-01. Order 1003, which arrived as Oct 3 2026, became 2026-10-03. Every product name is lowercase, every region is lowercase, and every price is a real decimal. The Parquet read back gives 9 rows, which matches the clean table exactly.
(1001, 'wireless mouse', 2, 25.99, 2026-10-01, 'north')
(1002, 'keyboard', 1, 49.50, 2026-10-01, 'south')
(1003, 'monitor stand', 1, 89.00, 2026-10-03, 'north')
(1006, 'webcam hd', 2, 79.00, 2026-10-05, 'north')
(1011, 'webcam hd', 1, 79.00, 2026-10-09, 'east')
When should we not use DuckDB?
DuckDB fits one machine and one writer. Only one process can hold the write lock on a DuckDB file at a time, which the DuckDB documentation confirms, so two pipelines must not write the same file at once. DuckDB also caps memory use at 80 percent of physical RAM by default, according to the DuckDB memory management documentation, and larger queries spill to disk. When data stops fitting on one machine, or many writers need to write at the same time, that is the point to reach for Spark or a warehouse. For a daily CSV to Parquet job on a laptop, DuckDB is the simpler tool, and simpler wins.
What to try next
- Point the read at a folder of monthly files with a glob, like read_csv('sales_*.csv', all_varchar=true), and watch the same pipeline handle a year of extracts.
- Add a quality report step that counts nulls per column and writes rejected rows to a rejected.csv file, so nothing is ever dropped silently.
- Schedule the script with cron to run every morning, then query the DuckDB file directly from a notebook for the day's revenue chart.
Originally published on LogicStack. Run the code examples right in your browser with our free interactive Python and SQL playgrounds — no setup, no sign-up.
Top comments (0)