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:

Metric	Count
Total records	6
Valid records	1
Rejected records	5

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?

Service	Main purpose
DuckDB	Lightweight analytical SQL and data validation
Amazon S3	Object storage for raw and processed data
AWS Glue	Managed data integration and ETL
Amazon Athena	SQL queries over supported data in S3
Amazon Redshift	Data warehousing and analytics

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