DuckDB on AWS: Build a Data Validation Pipeline with Amazon S3

Have you ever processed a CSV file only to discover that it contains duplicate IDs, missing values, or invalid records?

These data quality issues can affect dashboards, analytics pipelines, financial reports, and AI applications.

In this article, we'll explore DuckDB, an open-source analytical database, and build a small Python project that validates sales data using SQL. Then we'll look at how the same concept can be extended to Amazon S3 and AWS data-processing services.

A quick clarification: DuckDB is not a newly launched standalone managed AWS database service. AWS announced a planned acquisition of DuckLabs, the company behind DuckDB, in August 2026. AWS has also published an integration example using DuckDB with AWS Glue and Amazon S3 Tables.

References:

• AWS and DuckLabs
• DuckDB with AWS Glue and S3 Tables

What Is DuckDB?

DuckDB is an in-process analytical database that supports SQL queries over analytical data, including CSV and Parquet files.

Unlike a traditional database server, DuckDB can run directly inside a Python application or script. This makes it useful for lightweight analytics, data transformations, and data quality checks.

You can use DuckDB to:

• Validate incoming datasets.
• Find duplicate records and missing values.
• Filter and aggregate large files.
• Build lightweight ETL workflows.
• Prepare clean datasets for analytics.
• Process data before loading it into a warehouse.

When combined with AWS, DuckDB can complement Amazon S3, AWS Glue, Amazon Athena, and other data services.

Our Mini Project: Sales Data Validation

We'll build a simple pipeline:

sales.csv | v DuckDB + SQL Validation | +----> valid_sales.csv | +----> rejected_sales.csv 

Our validation rules will check:

1. Missing order IDs.
2. Missing customer IDs.
3. Duplicate order IDs.
4. Zero, negative, or missing amounts.
5. Invalid order statuses.

Step 1: Install DuckDB

Make sure Python is installed, then run:

pip install duckdb 

Create this project structure:

duckdb-aws-validation/ ├── sales.csv └── validate_sales.py 

Step 2: Create Sample Data

Create sales.csv:

order_id,customer_id,amount,status 1001,C01,250,COMPLETED 1002,C02,150,PENDING 1002,C03,300,COMPLETED 1004,,100,COMPLETED 1005,C05,-50,COMPLETED 1006,C06,90,UNKNOWN 

The file contains intentionally invalid records:

• Order 1002 appears twice.
• Order 1004 has a missing customer ID.
• Order 1005 has a negative amount.
• Order 1006 has an invalid status.

Step 3: Validate Data Using DuckDB

Create validate_sales.py:

import duckdb con = duckdb.connect("validation.duckdb") # Load the CSV file con.execute(""" CREATE OR REPLACE TABLE sales AS SELECT * FROM read_csv_auto('sales.csv') """) # Apply validation rules con.execute(""" CREATE OR REPLACE TABLE validated_sales AS SELECT *, CASE WHEN order_id IS NULL THEN 'Missing order ID' WHEN customer_id IS NULL OR trim(customer_id) = '' THEN 'Missing customer ID' WHEN amount IS NULL OR amount <= 0 THEN 'Invalid amount' WHEN status NOT IN ('COMPLETED', 'PENDING') THEN 'Invalid status' WHEN order_id IN ( SELECT order_id FROM sales WHERE order_id IS NOT NULL GROUP BY order_id HAVING COUNT(*) > 1 ) THEN 'Duplicate order ID' ELSE NULL END AS validation_error FROM sales """) # Export valid records con.execute(""" COPY ( SELECT * EXCLUDE (validation_error) FROM validated_sales WHERE validation_error IS NULL ) TO 'valid_sales.csv' (HEADER, DELIMITER ',') """) # Export rejected records with error reasons con.execute(""" COPY ( SELECT * FROM validated_sales WHERE validation_error IS NOT NULL ) TO 'rejected_sales.csv' (HEADER, DELIMITER ',') """) # Show validation statistics summary = con.execute(""" SELECT COUNT(*) AS total_rows, COUNT(*) FILTER ( WHERE validation_error IS NULL ) AS valid_rows, COUNT(*) FILTER ( WHERE validation_error IS NOT NULL ) AS rejected_rows FROM validated_sales """).fetchdf() print(summary) con.close() 

Step 4: Run the Project

Execute:

python validate_sales.py 

Expected results:

Both occurrences of order 1002 are rejected because the order ID is duplicated. This leaves only order 1001 as a valid record.

The script creates:

• validation.duckdb — the local DuckDB database.
• valid_sales.csv — records that passed validation.
• rejected_sales.csv — rejected records with their error reasons.

This approach makes data quality problems visible instead of silently passing them into downstream systems.

Note: This example reports the first matching validation error for each row. For production workloads, you may want to capture multiple errors per record and add stricter schema and type checks.

How Can We Use This with AWS?

Now let's extend the project into an AWS data pipeline.

Step 5: Store Raw Data in Amazon S3

A simple bucket layout could look like this:

s3://my-data-quality-bucket/ raw/ sales.csv validated/ valid_sales.csv rejected/ rejected_sales.csv 

Upload the source file using the AWS CLI:

aws s3 cp sales.csv \ s3://my-data-quality-bucket/raw/sales.csv 

This command requires the AWS CLI to be configured and appropriate S3 permissions.

Step 6: Integrate DuckDB into an AWS Workflow

A possible architecture is:

1. Upload raw CSV or Parquet data to Amazon S3.
2. Start a processing workflow.
3. Run DuckDB validation in a suitable compute environment.
4. Write valid and rejected records to separate S3 locations.
5. Monitor processing errors and validation counts.
6. Make curated datasets available to analytics services.

Depending on your requirements, you could run the validation code in an AWS Glue job, an ECS container, or another suitable compute environment.

AWS has demonstrated using DuckDB within AWS Glue to read Parquet data from S3 and write Apache Iceberg tables to Amazon S3 Tables.

This is especially interesting when your pipeline needs to combine SQL-based processing with modern lakehouse storage.

Step 7: Query Validated Data

After validation, you can use Amazon Athena to query supported data formats stored in S3.

For example, you could analyze accepted sales records:

SELECT status, SUM(amount) AS total_sales FROM sales GROUP BY status; 

In a real deployment, register the appropriate table in your AWS Glue Data Catalog and configure Athena to query the curated dataset.

For analytical workloads, consider storing validated data in Parquet rather than CSV. Parquet supports columnar storage and can reduce the amount of data scanned by analytical queries.

Where Does DuckDB Fit in the AWS Ecosystem?

These tools are complementary, not interchangeable. The right architecture depends on data volume, workload frequency, concurrency, operational needs, and cost.

Security and Cost Considerations

Before using this pattern in production:

• Use least-privilege IAM roles for S3 access.
• Never hardcode AWS credentials in your application.
• Encrypt sensitive data at rest and in transit.
• Separate raw, validated, and rejected datasets.
• Monitor job failures and processing duration.
• Configure retention policies for rejected records.
• Test with realistic data volumes and memory requirements.

DuckDB can reduce the need for additional infrastructure in some workloads, but it does not automatically make every AWS pipeline cheaper. Measure compute, storage, data transfer, and execution costs before making a decision.

Final Thoughts

DuckDB makes SQL-based data validation accessible without requiring a separate database server for every small processing task.

By combining DuckDB with Amazon S3 and appropriate AWS compute services, you can create a useful quality gate that catches invalid data before it reaches dashboards, analytics platforms, or AI applications.

The biggest lesson from this project is simple:

Don't trust data just because it was successfully uploaded. Validate it, explain why records fail, and only promote trusted data to downstream systems.

If you're building AWS data pipelines, what would you add next: schema validation, automated testing, or a fully automated AWS Glue workflow?

References

• DuckDB Documentation
• Amazon S3 Documentation
• AWS and DuckLabs
• DuckDB with AWS Glue and Amazon S3 Tables