Introduction
In daily data processing workflows, raw data often contains dirty values, outliers, and formatting inconsistencies. Whether loading directly from CSV files or integrating data from multiple sources, data cleaning is a critical step in the ETL (Extract - Transform - Load) process.
DuckDB, as a high-performance analytical database, provides powerful built-in functions and type conversion capabilities that enable efficient data cleaning entirely within SQL, without relying on external programming languages. This article will walk through a practical business scenario, demonstrating how to use read_csv_auto, data type conversion, and outlier handling techniques to build a complete data cleaning ETL pipeline.
Scenario Background
Assume we are a data analyst at an e-commerce company, tasked with extracting clean sales data from daily sales record CSV files exported by the sales department. The raw data typically suffers from several common problems:
- Negative price values (system entry errors)
- Missing or null quantities
- Product names with leading/trailing whitespace
- Price fields containing non-numeric characters (e.g., “abc”, “-”)
- Inconsistent ID formats
Step 1: Quickly Load Raw Data Using read_csv_auto
DuckDB’s read_csv_auto function can automatically infer the structure of CSV files—including column names, data types, and delimiters—making it ideal for quickly exploring the initial state of dirty data.
-- Read the raw sales data table, automatically infer structure and type
SELECT read_csv_auto('data/sales_raw.csv') AS raw_sales;
This command returns a temporary result set where we can observe each column’s actual content and potential issues. For example, the price column might contain negative values like -10.50, non-numeric entries like abc, or quantity columns that are NULL.
To make the results persistent for further operations, we can store them in a table:
-- Store query results in a temporary table for further inspection
CREATE TEMP TABLE raw_sales AS
SELECT * FROM read_csv_auto('data/sales_raw.csv');
-- View data overview
SELECT
COUNT(*) AS total_rows,
COUNT(price) AS price_not_null,
COUNT(quantity) AS quantity_not_null,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM raw_sales;
┌───────────┬───────────────┬───────────────┬──────────┬──────────┐ │ total_rows │ price_not_null │ quantity_not_null │ min_price │ max_price │ ├───────────┼───────────────┼───────────────┼──────────┼──────────┤ │ 1000 │ 980 │ 950 │ -50.00 │ 9999.99 │ └───────────┴───────────────┴───────────────┴──────────┴──────────┘
The results show that 20 rows have negative prices (likely entry errors), 50 rows have NULL quantities—clear targets for our cleaning work.
## Step 2: Detect and Categorize Outliers
Using `CASE` statements combined with various condition checks, we can systematically identify different types of data quality issues.
```sql
-- Detailedly label issues in each row
SELECT
id,
product_name,
price,
quantity,
CASE
WHEN price < 0 THEN 'negative_price'
WHEN TRY_CAST(price AS DECIMAL) IS NULL THEN 'invalid_format'
ELSE 'ok' END AS price_status,
CASE
WHEN quantity IS NULL THEN 'missing_quantity'
WHEN quantity < 0 THEN 'negative_quantity'
ELSE 'ok' END AS quantity_status
FROM raw_sales;
┌────┬──────────────┬──────────┬────────────┬────────────────────┬──────────────────┐ │ id │ product_name │ price │ quantity │ price_status │ quantity_status │ ├────┼──────────────┼──────────┼────────────┼────────────────────┼──────────────────┤ │ A01│ Widget A │ 199.99 │ 5 │ ok │ ok │ │ A02│ Widget B │ -10.50 │ 2 │ negative_price │ ok │ │ A03│ Widget C │ 299.00 │ NULL │ ok │ missing_quantity │ │ A04│ Widget D │ abc │ 3 │ invalid_format │ ok │ └────┴──────────────┴──────────┴────────────┴────────────────────┴──────────────────┘
Through this view, we can clearly see which data rows need repair and which can be kept directly.
## Step 3: Data Transformation and Cleaning
Now we perform the actual cleaning operation. The goals are:
1. Set negative prices to NULL (rather than deleting, for audit purposes)
2. Fill missing quantities with 0
3. Trim leading/trailing spaces from product names
4. Convert prices that cannot be parsed into valid numbers to NULL
5. Keep only valid sales records
```sql
-- Create the cleaned target table
CREATE TABLE cleaned_sales AS
SELECT
TRIM(id) AS clean_id,
TRIM(product_name) AS clean_product,
-- Negative price becomes NULL, normal price preserved
CASE
WHEN price < 0 THEN NULL
ELSE TRY_CAST(price AS DECIMAL(10,2))
END AS clean_price,
-- Missing quantity defaults to 0
COALESCE(quantity, 0) AS clean_quantity,
-- Optional tracking info: sequence number
row_number() OVER () AS record_seq
FROM raw_sales
-- Only keep rows where price can be successfully parsed as numeric
WHERE TRY_CAST(price AS DECIMAL) IS NOT NULL;
-- Verify cleaning results
SELECT
COUNT(*) AS cleaned_rows,
COUNT(clean_price) AS prices_valid,
SUM(clean_quantity) As total_qty,
AVG(clean_price) AS avg_price,
MIN(clean_price) AS min_price,
MAX(clean_price) AS max_price
FROM cleaned_sales;
┌────────────┬────────────┬───────────────┬────────────┬──────────┬──────────┐ │ cleaned_rows │ prices_valid │ total_qty │ avg_price │ min_price │ max_price │ ├────────────┼────────────┼───────────────┼────────────┼──────────┼──────────┤ │ 960 │ 960 │ 4820 │ 245.67 │ 15.50 │ 899.99 │ └────────────┴────────────┴───────────────┼────────────┴──────────┴──────────┘
After cleaning, we filtered out 960 valid records from the original 1000, eliminating all negative and invalid format prices while filling missing quantities with 0.
## Step 4: Advanced Cleaning Technique — Rule-Based Outlier Marking
Beyond basic cleaning, we can add a column to mark previously occurring outliers, which is invaluable for audit and troubleshooting:
```sql
-- Final version with cleaning action annotations
CREATE TABLE audit_cleaned_sales AS
SELECT
TRIM(id) AS clean_id,
TRIM(product_name) AS clean_product,
price AS original_price,
quantity AS original_quantity,
CASE
WHEN price < 0 THEN NULL
ELSE TRY_CAST(price AS DECIMAL(10,2))
END AS clean_price,
COALESCE(quantity, 0) AS clean_quantity,
CASE
WHEN price < 0 THEN 'replaced_negative_with_null'
WHEN TRY_CAST(price AS DECIMAL) IS NULL THEN 'skipped_invalid_format'
WHEN quantity IS NULL THEN 'filled_missing_qty_with_0'
ELSE 'no_issue'
END AS clean_action
FROM raw_sales
WHERE TRY_CAST(price AS DECIMAL) IS NOT NULL;
-- View distribution of cleaning actions
SELECT clean_action, COUNT(*) AS count FROM audit_cleaned_sales GROUP BY clean_action;
┌──────────────────────────────────────┬──────┐ │ clean_action │ count│ ├──────────────────────────────────────┼──────┤ │ filled_missing_qty_with_0 │ 50 │ │ no_issue │ 910 │ │ replaced_negative_with_null │ 20 │ └──────────────────────────────────────┴──────┘
We've retained complete cleaning operation records, knowing exactly which data was corrected and which was originally clean.
## Step 5: ETL Pipeline Automation — Batch Processing Multiple Files
In production environments, you may need to process multiple CSV files daily. DuckDB supports wildcard reads and multi-file merging, enabling easy construction of batch cleaning pipelines:
```sql
-- Read all sales_*.csv files for today
CREATE TEMP TABLE daily_all_sales AS
SELECT *, '$FILE_NAME' AS source_file FROM read_csv_auto('data/sales_*.csv');
-- Apply same cleaning logic to batched data
INSERT INTO cleaned_sales
SELECT
TRIM(id),
TRIM(product_name),
CASE WHEN price < 0 THEN NULL ELSE TRY_CAST(price AS DECIMAL(10,2)) END,
COALESCE(quantity, 0),
record_seq
FROM daily_all_sales
WHERE TRY_CAST(price AS DECIMAL) IS NOT NULL;
-- Or create daily snapshot in one operation
CREATE OR REPLACE TABLE daily_snapshot AS
WITH batched AS (
SELECT *, row_number() OVER (PARTITION BY $FILE_NAME ORDER BY id) AS rn
FROM read_csv_auto('data/sales_*.csv')
)
SELECT
TRIM(id) AS clean_id,
TRIM(product_name) AS clean_product,
CASE WHEN price < 0 THEN NULL ELSE TRY_CAST(price AS DECIMAL(10,2)) END AS clean_price,
COALESCE(quantity, 0) AS clean_quantity,
$FILE_NAME AS source_file,
current_timestamp AS processed_at
FROM batched
WHERE TRY_CAST(price AS DECIMAL) IS NOT NULL;
```
## Step 6: Complete Workflow Example — End-to-End Script
Here's a complete DuckDB script that encapsulates the entire cleaning workflow:
```sql
-- file: etl_script.sql
-- Step 1: Configuration parameters
SET memory_limit = '2GB';
SET threads = 4;
-- Step 2: Read raw data
CREATE TEMP TABLE staging AS
SELECT * FROM read_csv_auto('input/sales_daily.csv');
-- Step 3: Data quality report (optional, output before cleaning)
SELECT
'quality_report' AS section,
'count_total' AS metric, COUNT(*) AS value FROM staging UNION ALL
SELECT
'quality_report', 'count_null_price', COUNT(price) FROM staging UNION ALL
SELECT
'quality_report', 'count_negative_price', COUNT(*) FROM staging WHERE price < 0 UNION ALL
SELECT
'quality_report', 'count_missing_qty', COUNT(*) FROM staging WHERE quantity IS NULL;
-- Step 4: Execute cleaning, write to target table
CREATE TABLE IF NOT EXISTS clean.sales_staging AS
SELECT
TRIM(id) AS clean_id,
TRIM(product_name) AS product,
CASE WHEN price < 0 THEN NULL ELSE TRY_CAST(price AS DECIMAL(10,2)) END AS price,
COALESCE(quantity, 0) AS qty,
current_timestamp AS cleaned_at
FROM staging
WHERE TRY_CAST(price AS DECIMAL) IS NOT NULL;
-- Step 5: Verification
SELECT
'cleaned_table' AS section,
'rows_inserted' AS metric, COUNT(*) AS value
FROM clean.sales_staging;
-- Step 6: (Optional) Export to Parquet for downstream use
EXPORT DATABASE 'output/cleaned_db/';
```
Invoke via Shell:
```bash
duckdb -c ".load duckdb.sql" \
-c "CALL ('etl_script.sql');"
```
Or execute the script using DuckDB's Python interface.
## Summary and Best Practices
Through this hands-on practice, we've mastered core skills:
1. **`read_csv_auto`'s exploratory value**: Quickly understand raw data structure and schema without pre-defining it
2. **`TRY_CAST` safe conversion**: Avoid query interruptions on failed conversions, replacing errors with NULL
3. **`CASE` statement conditional handling**: Precisely identify and categorize different issue types
4. **`TRIM` text cleanup**: Eliminate whitespace causing matching issues in strings
5. **`COALESCE` fill missing values**: Use defaults for NULLs ensuring accurate aggregations
6. **Audit trail retention**: Preserve original data + cleaning actions for traceability
When processing large-scale data in production environments, follow these principles:
- **Explore first, clean later**: Start with a quality report understanding data status
- **Keep raw backups**: Never modify raw data directly; always create new tables
- **Step-by-step approach**: Break cleaning into verifiable stages
- **Automate scheduling**: Put cleaning scripts in cron or Airflow for scheduled ETL
- **Monitor alerts**: Notify when outlier thresholds are exceeded
*Figure: Data Cleaning ETL Architecture — Complete flow from raw CSV to cleaned table*
*Figure: DuckDB CLI output executing data cleaning SQL examples*
For more DuckDB in-action tips, follow DuckDB Lab (duckdblab.org).