AWS Big Data Blog

Orchestrate DuckDB analytics pipelines with Amazon MWAA

Apache Airflow orchestrates data pipelines ranging from data ingestion and machine learning to SQL-based analytics. Some analytical workloads, such as daily rollups and usage reports, process bounded datasets and can run within the resources of a single worker. For these workloads, deploying a distributed query cluster can add unnecessary infrastructure and operational complexity.

DuckDB is an open source in-process analytical database that runs within an application without requiring a separate database server. Its columnar, vectorized execution engine supports analytical SQL operations, including scans, joins, and aggregations.

Amazon Managed Workflows for Apache Airflow (Amazon MWAA) runs Apache Airflow as a managed service, so you can schedule, run, and monitor workflows without managing the underlying Airflow infrastructure. Combined with DuckDB, you can add SQL transformations to your pipelines that run inside the Airflow task itself.

In this post, you learn how to run DuckDB with an Amazon MWAA Provisioned environment.

How DuckDB and Amazon MWAA work together

In Apache Airflow, a directed acyclic graph (DAG) defines tasks and their dependencies, along with scheduling, retry, and concurrency settings. Amazon MWAA runs the tasks on workers and publishes configured task logs to Amazon CloudWatch Logs.

DuckDB runs in the Airflow task process, using the CPU, memory, and local storage of the Amazon MWAA worker container. Amazon MWAA orchestrates the workflow, while DuckDB performs the SQL processing.

This walkthrough uses DuckDB 2.0.0, which includes asynchronous I/O for reading from object storage and first-class VARIANT support for semi-structured data. DuckDB 2.0.0 also supports a client-server mode through the Quack protocol, which lets other processes connect to a DuckDB database over the network. This walkthrough runs DuckDB in-process because each Airflow task performs a self-contained transformation.

Introducing the Apache Airflow DuckDB provider

The Apache Airflow DuckDB provider provides the DuckDBExecuteQueryOperator. The operator runs DuckDB locally in the worker container and executes SQL without starting a separate query service.

The DuckDB integration in the Amazon provider adds the AWS aware duckdb_aws connection type. On supported Amazon MWAA Provisioned environments, the DuckDB packages are preinstalled, and Amazon MWAA creates the default connection, as described later in this post.

Solution overview

An engineering team operates a shared inference service that product teams use for their applications. As usage grows, the team needs to understand which applications and teams are driving inference activity and the estimated cost of that usage. Showback provides an itemized view of usage and estimated cost without charging teams directly.

To build the showback pipeline without a separate analytics cluster, the team uses Amazon MWAA to orchestrate Airflow tasks that run DuckDB. DuckDB reads source files from Amazon Simple Storage Service (Amazon S3), performs SQL transformations, and publishes the results as Apache Iceberg tables in Amazon S3 Tables. This pattern suits bounded, SQL-oriented workloads that run reliably within the resources of a single worker.

Five-stage architecture of the AI inference showback pipeline. Amazon S3 stores the source Parquet files. Amazon MWAA orchestrates two Airflow tasks in which DuckDB reads, cleans, combines, publishes, and validates the data. The pipeline commits the output to Apache Iceberg tables in Amazon S3 Tables.

Figure 1: Amazon MWAA orchestrates two DuckDB tasks across five stages, from reading Parquet data in Amazon S3 through publishing and validating Iceberg tables in Amazon S3 Tables

The workflow has five logical stages, implemented by two Airflow tasks, as shown in Figure 1.

  • Stage 1: Store the source data – An Amazon S3 bucket stores the Parquet files for inference events, model rates, and application ownership records.
  • Stage 2: Trigger the DAG – A manual action starts this example DAG. Amazon MWAA orchestrates both tasks, tracks their status, and sends task logs to Amazon CloudWatch Logs. You can configure a schedule for recurring runs.
  • Stage 3: Read, clean, and combine the records – The transform_and_publish_showback task uses DuckDB to read the Parquet files directly from Amazon S3, deduplicate events, and apply the model rate and ownership record effective at each invocation timestamp.
  • Stage 4: Publish the result – In the same transform_and_publish_showback task, DuckDB merges the processed records into the costed_invocations Iceberg table in Amazon S3 Tables and commits the update. The task also creates the daily_application_showback and team_budget_status summary tables.
  • Stage 5: Validate published tables – After publication, the second Airflow task, validate_showback, uses DuckDB to check the row count, confirm that event IDs are unique, and verify the expected allocation states.

The sample rates and cost_credits values are synthetic and are provided only to demonstrate allocation logic. They do not represent AWS pricing, Amazon Bedrock charges, or customer billing records.

Prerequisites

Before you begin, make sure you have the following resources and permissions:

  • An Amazon MWAA Provisioned environment running Apache Airflow 3.3.1 with Python 3.12. See Apache Airflow versions on Amazon MWAA for the currently supported versions.
  • An Amazon S3 bucket for the source Parquet files and an Amazon S3 Tables table bucket for the outputs.
  • An Amazon MWAA execution role with read access to the source bucket and permissions to create, read, and update tables and table data in the target table bucket. For the required actions, see Accessing Amazon S3 Tables using open source applications. If the table bucket uses a customer managed AWS Key Management Service (AWS KMS) key, the role also needs permission to use that key.
  • Network connectivity from the Amazon MWAA private subnets to Amazon S3 and Amazon S3 Tables. This sample installs the DuckDB Iceberg extension at runtime, so it also requires access to the official DuckDB extension repository unless the matching extension is already available.
  • The sample bundle containing the DAG, SQL, and synthetic-data generator used in this walkthrough.
  • Python 3, the dependencies listed in generator-requirements.txt, and the AWS Command Line Interface (AWS CLI) configured on the workstation where you generate and upload the sample data.

In the test environment used for this post, Amazon MWAA included apache-airflow-providers-amazon 9.38.0, apache-airflow-providers-duckdb 0.2.0, and DuckDB 2.0.0. These versions might change in future Amazon MWAA releases.

Use the built-in DuckDB providers and connection

Amazon MWAA Provisioned environments running Apache Airflow 3.3.1 include DuckDB, the Apache Airflow DuckDB provider, and the DuckDB integration in the Amazon provider. Amazon MWAA also creates a connection named duckdb_default with the connection type duckdb_aws. You do not need to add packages to requirements.txt or create the connection manually.

To confirm that the connection exists, choose Admin, then Connections in the Airflow UI. Look for duckdb_default.

Note: When a DuckDBExecuteQueryOperator task doesn’t specify conn_id, it uses duckdb_default. The AWS aware hook obtains temporary credentials from the Amazon MWAA execution role and creates a DuckDB S3 secret for the task. Don’t put AWS access keys in the DAG or create a separate S3 secret in the SQL, because doing so can conflict with the secret the hook creates. If duckdb_default doesn’t appear under Admin, Connections, update your Amazon MWAA environment without changing any settings to apply the latest Amazon MWAA image.

Prepare the sample data

Use generate_ai_inference_data.py from the sample bundle to create Parquet files containing inference events, model rates, and application ownership records. A fixed random seed makes the input reproducible. The dataset includes duplicate events and changes to rates and ownership, allowing you to demonstrate deduplication and effective-dated attribution.

Run the generator and upload the resulting files to Amazon S3:

python3 -m pip install -r generator-requirements.txt

python3 generate_ai_inference_data.py \
    --output sample_ai_inference \
    --start-date 2026-09-01 \
    --days 30 \
    --events-per-day 10000 \
    --seed 42

aws s3 cp sample_ai_inference/ \
    s3://<SOURCE-BUCKET>/ai-showback/ \
    --recursive

The default settings generate approximately 300,000 unique events across daily partitions. The generated manifest.json records the expected unique-event count for subsequent validation.

Build the DuckDB transformation

See publish_showback.sql in the sample bundle for the complete transformation SQL. The following excerpts highlight three operations used in the pipeline.

Read Parquet files and remove duplicates

The read_parquet function reads daily Parquet partitions directly from Amazon S3. The union_by_name option aligns columns across files with different schemas. A ROW_NUMBER window function retains the record with the most recent ingestion timestamp for each event ID.

CREATE OR REPLACE TEMP TABLE latest_events AS
SELECT *
FROM read_parquet(
    's3://<SOURCE-BUCKET>/ai-showback/'
    'inference_events/ingest_date=*/*.parquet',
    hive_partitioning = true,
    union_by_name = true
)
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY event_id
    ORDER BY ingested_at DESC
) = 1;

Apply rates and ownership at the event time

Model rates and application ownership can change over time. Using only the current reference record would misattribute historical events. The DuckDB ASOF LEFT JOIN selects the latest reference record whose effective timestamp is no later than the event timestamp.

CREATE OR REPLACE TEMP TABLE events_with_rates AS
SELECT
    e.*,
    r.input_credits_per_million,
    r.output_credits_per_million
FROM latest_events AS e
ASOF LEFT JOIN read_parquet(
    's3://<SOURCE-BUCKET>/ai-showback/reference/model_rates.parquet'
) AS r
ON e.model_id = r.model_id
AND e.event_ts >= r.effective_from;

CREATE OR REPLACE TEMP TABLE costed_batch AS
SELECT
    e.*,
    o.team_id,
    o.cost_center,
    (
        e.input_tokens * e.input_credits_per_million
        + e.output_tokens * e.output_credits_per_million
    ) / 1000000.0 AS cost_credits
FROM events_with_rates AS e
ASOF LEFT JOIN read_parquet(
    's3://<SOURCE-BUCKET>/ai-showback/'
    'reference/application_ownership.parquet'
) AS o
ON e.application_id = o.application_id
AND e.event_ts >= o.effective_from;

The full sample retains events without matching reference records and assigns an allocation status. This makes missing rate or ownership data visible during validation rather than excluding those events.

Publish retry-safe Iceberg output

Set the DuckDB session timezone to UTC, attach the Amazon S3 Tables table bucket as an Iceberg catalog, then merge the transformed records using a stable event ID:

INSTALL iceberg;
LOAD iceberg;
SET TimeZone = 'UTC';

ATTACH 'arn:aws:s3tables:<REGION>:<ACCOUNT-ID>:bucket/<TABLE-BUCKET>'
AS s3t (TYPE iceberg, ENDPOINT_TYPE s3_tables);

CREATE SCHEMA IF NOT EXISTS s3t.ai_showback;

MERGE INTO s3t.ai_showback.costed_invocations AS target
USING costed_batch AS source
ON target.event_id = source.event_id
WHEN MATCHED THEN UPDATE SET
    ingested_at = source.ingested_at,
    team_id = source.team_id,
    cost_center = source.cost_center,
    cost_credits = source.cost_credits
WHEN NOT MATCHED THEN INSERT BY NAME;

For daily_application_showback, use every grouped dimension in the MERGE condition. Use null-safe comparisons for nullable dimensions so that a retry cannot match multiple grouped source rows to one target row:

ON target.usage_date = source.usage_date
AND target.application_id = source.application_id
AND target.team_id IS NOT DISTINCT FROM source.team_id
AND target.cost_center IS NOT DISTINCT FROM source.cost_center
AND target.model_id = source.model_id
AND target.use_case IS NOT DISTINCT FROM source.use_case

The sample uses an unpartitioned tutorial table because support for update and delete operations can vary by DuckDB and Iceberg version. Validate table creation, the write pattern, partition design, partial-failure behavior, and reruns with the versions you deploy. An Iceberg snapshot commit is atomic for one table, but commits to the three output tables are not a single transaction.

Create the Amazon MWAA DAG

The duckdb_ai_inference_showback.py file in the sample bundle contains the complete Apache Airflow 3.3.1 DAG. It uses the public airflow.sdk import and a timezone-aware start date. Because schedule=None, you trigger it manually. You can add a schedule for recurring runs. The publication task runs before validation. Setting max_active_runs=1 prevents overlapping runs of this DAG. The detailed-table merge matches on event_id to avoid duplicate inserts during unchanged-input retries. Test partial failures, summary-table updates, and external concurrent writers separately.

# PUBLISH_SQL, VALIDATE_SQL, and the URI constants are defined in the sample bundle.
import pendulum

from airflow.sdk import DAG
from airflow.providers.duckdb.operators.duckdb import (
    DuckDBExecuteQueryOperator,
)

with DAG(
    dag_id="duckdb_ai_inference_showback",
    start_date=pendulum.datetime(2026, 9, 1, tz="UTC"),
    schedule=None,
    catchup=False,
    max_active_runs=1,
) as dag:
    # Transform the source records and publish the Iceberg output.
    transform_and_publish = DuckDBExecuteQueryOperator(
        task_id="transform_and_publish_showback",
        sql=PUBLISH_SQL,
        split_statements=True,
        params={
            "events_uri": EVENTS_URI,
            "rates_uri": RATES_URI,
            "ownership_uri": OWNERSHIP_URI,
            "table_bucket_arn": TABLE_BUCKET_ARN,
        },
        hook_params={
            "memory_limit": "2GB",
            "threads": 2,
            "temp_directory": "/tmp/duckdb-ai-showback-spill",
        },
        retries=1,
    )

    # Verify the detailed and summary outputs after publication.
    validate = DuckDBExecuteQueryOperator(
        task_id="validate_showback",
        sql=VALIDATE_SQL,
        split_statements=True,
        retries=1,
    )

    transform_and_publish >> validate

Because the operators omit conn_id, they use the built-in duckdb_default connection. The hook_params settings only limit DuckDB memory and threads and select a local spill directory.

In duckdb_ai_inference_showback.py, replace the source bucket and table bucket ARN placeholders, then upload the file to the Amazon S3 DAG folder configured for your Amazon MWAA environment.

Run and validate the pipeline

See validate_showback.sql in the sample bundle for the complete validation SQL. In the Airflow user interface, unpause duckdb_ai_inference_showback and trigger the DAG manually. The graph should show transform_and_publish_showback running before validate_showback.

The validation checks the row count and logical-key uniqueness of both summary tables in addition to the detailed-table manifest, event-ID uniqueness, and allocation-state checks. These assertions make an unchanged-input rerun fail if it creates duplicate summary rows.

Airflow graph showing the transform_and_publish_showback task followed by the validate_showback task.

Figure 2: The Airflow DAG runs transformation and publication before validation

We tested the walkthrough on a new mw1.small Amazon MWAA Provisioned environment in the Canada (Central) Region. The environment had no requirements or plugins file. In the representative run, the pipeline processed 303,543 source rows, removed 3,543 later-arriving duplicates, and published 300,000 unique events. The transform-and-publish task completed in about 11 seconds, and validation completed in about 6 seconds. These measurements describe this test run. Performance varies with worker capacity, data layout, and concurrent workloads.

Airflow grid showing the representative run and two successful unchanged-input reruns of the AI inference showback DAG.

Figure 3: The representative run and two unchanged-input reruns completed successfully

Review the transform-and-publish task log to confirm that DuckDB loaded the required extensions, read the inputs, and committed the Iceberg tables.

Successful transform_and_publish_showback task and its Airflow log.

Figure 4: The AWS aware hook creates the DuckDB S3 secret from temporary Amazon MWAA execution-role credentials before publication

The validation task confirmed unique event IDs, a detailed row count matching the generator manifest, all expected allocation states, and unique logical keys in both summary tables. The result contained 272,728 allocated events, 3,683 events missing a rate, 5,522 events missing ownership, and 18,067 non-success events. The summary tables contained 8,949 daily application rows and 9 team budget rows.

Airflow validation log showing three PASS results and 300,000 unique events.

Figure 5: The validation task reports PASS for uniqueness, row count, and allocation coverage

Trigger the DAG twice more without changing the source files. In our test, all three runs retained 300,000 unique events, 8,949 daily application rows, 9 team budget rows, the same allocation counts, and zero duplicate logical keys. This demonstrates repeatability for unchanged input, but does not establish recovery from partial failures or concurrent external writes.

The pipeline published a detailed costed_invocations table and two summary tables, daily_application_showback and team_budget_status, in the ai_showback namespace.

Amazon S3 Tables inventory showing the three tables in the ai_showback namespace.

Figure 6: Amazon S3 Tables contains the detailed output and two summary tables

If you configure Amazon S3 Tables integration with AWS analytics services, you can query the output from Amazon Athena. Because the output is stored as Iceberg tables, other Iceberg-compatible engines can read it.

Considerations

When you run DuckDB with Amazon MWAA, each task runs in an Amazon MWAA worker container whose CPU and memory depend on the environment class. Because Amazon MWAA uses the Celery executor, a single worker can run multiple Airflow tasks concurrently. Keep the following in mind:

  • Worker capacity: Set the DuckDB memory limit, thread count, and temporary spill space, then test with representative data. Joins, sorting, and data skew can affect peak resource use more than compressed input size alone.
  • Concurrency: Concurrent tasks on a worker share its CPU and memory, and each DuckDB task can also use multiple threads. Use Airflow pools and max_active_runs to limit how many DuckDB tasks run together and to prevent overlapping writes to the same tables.
  • Processing boundary: Use this pattern when SQL processing completes reliably within one worker. If a step needs horizontal scaling, high write concurrency, or more resources than one worker provides, run it with AWS Glue or Amazon EMR. Amazon MWAA can continue to orchestrate it.

For operational and security best practices, see the Amazon MWAA shared responsibility model.

Clean up

To avoid ongoing charges, pause the DAG and remove its file from the Amazon MWAA DAG folder. Delete the synthetic source data and drop the tutorial Iceberg tables. Remove the table bucket, source bucket, and Amazon MWAA environment only if you created them exclusively for this walkthrough. Remove any tutorial-specific permissions that are no longer needed.

Conclusion

In this post, you used Amazon MWAA to orchestrate an embedded DuckDB analytics pipeline. DuckDB ran inside the Airflow tasks to transform Parquet data from Amazon S3, publish the results as Apache Iceberg tables in Amazon S3 Tables, and validate the output. The DAG defined the task dependencies, while Amazon MWAA ran and monitored the tasks.

This pattern suits bounded, SQL-oriented transformations that fit within one worker. Start with representative data, set DuckDB memory and thread limits, and test retries, concurrency, and multi-table write behavior. If a task outgrows one worker, Amazon MWAA can continue orchestrating the workflow while a distributed service performs that step.

We thank Kamen Sharlandjiev (Principal Specialist Solutions Architect) and Aritra Ghosh (Senior Product Manager) for their contributions to this post.


About the authors

Vinod Jayendra

Vinod Jayendra

Vinod is a Senior Solutions Architect at Amazon Web Services, where he helps ISV customers address architectural, scalability, and operational challenges. He specializes in analytics and AI/ML and draws on his extensive application development experience to design practical, effective solutions. Outside of work, Vinod enjoys spending time with his family, cycling, and coaching youth sports teams.

Suba Palanisamy

Suba Palanisamy

Suba is an Enterprise Support Lead, helping customers achieve operational excellence on AWS. Suba is passionate about all things data and analytics. She enjoys traveling with her family and playing board games.

Niko Oliveira

Niko Oliveira

Niko is a Principal SDE at Amazon Web Services. He helped build and launch the Amazon MWAA services, and spends most of his time leading Amazon’s open source efforts on upstream Apache Airflow, where he is a committer and PMC member. Outside of work, Niko enjoys hiking, rock climbing and skiing in the mountains, watching sports, and he is currently diving deep into chess.

Varsha Gowda

Varsha Gowda

Varsha is a Senior Manager of Solution Architecture at AWS. She leads teams that help organizations modernize their data and analytics foundations, turning complex data into insights that drive real business outcomes. She focuses on helping customers evolve their data strategies so they can get the most out of analytics and AI. Outside of work, you’ll find her hiking, skiing, or traveling to experience new cultures.