Sales data often arrives with plenty of issues. Real-world exports can include duplicate entries, inconsistent date formats, missing values, and prices saved as text. Anyone learning practical data engineering or preparing for analytics work needs to know how to build a reliable ETL (Extract, Transform, Load) pipeline. This guide shows how to use Python and DuckDB 2.0 to process a messy sales CSV, clean it with SQL, and export the results for further analysis.
Why Use DuckDB for ETL in Python?
DuckDB is an analytical database built for local and embedded workloads, integrating directly into Python without needing a separate database server. The engine runs inside the Python process, so setup stays simple and lightweight. This design lets analytical queries run directly against local data sources, supporting laptop-based experimentation and lightweight ETL development. The DuckDB engineering team explains, "The first is to take fuller advantage of DuckDB’s query optimizations. The second is to use DuckDB for file-based data sources, reading files the way it already reads across connections." (DuckDB engineering blog). This setup works well for learners and developers who want to experiment with data pipelines on a laptop without extra infrastructure.
Preparing the Raw Sales Data
To reflect real-world challenges, the example sales_raw.csv file includes twelve rows with several problems: a duplicated order ID, a missing quantity, a negative quantity, dates in two different formats, and prices stored as text with dollar signs. The goal is to keep the nine valid sales records and filter out the problematic ones, making the cleaning process transparent and easy to check.
Extracting Data with Python and DuckDB
DuckDB’s official project materials list Python as a supported environment and describe the database as usable inside application processes. This model stands apart from conventional analytical systems that require a separately managed server or network connection. For ETL learners, Python can handle file ingestion, run SQL transformations, and write outputs through the same runtime (DuckDB Engineering Blog). Start by reading the CSV file using DuckDB's read_csv function with all_varchar=true. This setting keeps every value as text, so rows aren't silently dropped due to failed type conversions. The pipeline only needs the DuckDB Python package, which installs via pip.
Transforming Data with SQL
The transformation step uses a single SQL statement to fix each data issue. Product names are trimmed and converted to lowercase for consistency. Prices are stripped of dollar signs and commas, then cast to decimals. Dates are parsed using try_strptime with two formats, and coalesce selects the first successful parse. If a date can't be parsed, it becomes null, showing that the row needs review.
Quality control works in two ways. The WHERE clause removes rows with missing or negative quantities, such as order 1005 and order 1007. The QUALIFY clause uses row_number to deduplicate order IDs, keeping only the first occurrence. DuckDB supports QUALIFY directly, so no temporary tables or extra scripts are needed. The project’s engineering roadmap treats direct querying of files as a core feature, making it easier to inspect malformed records before saving cleaned results to a durable output table or file.
Loading Clean Data and Exporting Results
After transformation, the clean data is stored in a DuckDB table. The pipeline then exports two artifacts: a Parquet file and a summary CSV with revenue by product. Parquet keeps column types and compresses efficiently, making it suitable for downstream analytics. The summary CSV gives a quick overview for spreadsheet users.
Verifying the Pipeline Output
Running the complete script processes twelve input rows and produces nine clean records. Three rows are rejected for the expected reasons: one duplicate, one missing quantity, and one negative quantity. Dates are standardized, product and region names are lowercase, and prices are stored as decimals. Reading the Parquet file confirms that the output matches the clean table exactly.
Limitations and When to Consider Alternatives
DuckDB is built for single-machine workloads and supports only one writer at a time. Only one process can hold a write lock on a DuckDB file, so concurrent pipelines shouldn't write to the same database. Memory usage is capped at 80 percent of physical RAM by default, with larger queries spilling to disk. For larger datasets or multi-writer scenarios, tools like Spark or a data warehouse may fit better. For daily CSV-to-Parquet jobs on a laptop, DuckDB remains a practical and efficient choice.
Extending the Pipeline
To expand this workflow, try pointing the CSV reader at a folder of monthly files using a glob pattern, such as read_csv('sales_*.csv', all_varchar=true). Add a quality report step to count nulls per column and write rejected rows to a separate file. Scheduling the script with cron enables automated daily processing, and the DuckDB file can be queried directly from a notebook for quick revenue analysis.
DuckDB’s engineering team continues to develop features for robust CSV parsing and file-based analytics, as highlighted in their article "DuckDB’s CSV Reader and the Pollock Robustness Benchmark: Into the CSV Abyss." Ongoing improvements in query optimization and file handling remain central to the project’s evolution.
For those interested in automating document conversion tasks, the batch conversion guide offers another practical example of streamlining data workflows.
This tutorial is based on material originally published on LogicStack. For hands-on practice, LogicStack provides interactive Python and SQL playgrounds where you can run code examples directly in your browser.
