Run DuckDB analytics on your Amazon DynamoDB data with zero-ETL

The team behind DuckDB, the open source analytical database, is joining AWS. If you build on Amazon DynamoDB, the news is a good reason to look at how well the two work together. Analytical queries, like revenue by category or order volume by hour, run better against a separate copy of the data, which keeps your table focused on serving low-latency application traffic. DuckDB handles exactly that kind of query.

In this post, we show you how to run ad hoc SQL queries on your DynamoDB data with DuckDB. A zero-ETL integration replicates your table on a refresh interval into Apache Iceberg tables on Amazon S3 Tables, a capability of Amazon Simple Storage Service (Amazon S3). An AWS Lambda function running DuckDB then serves SQL queries over that data through an IAM-authorized HTTPS endpoint.

A quick look at Amazon DynamoDB

Amazon DynamoDB is a serverless, fully managed, distributed NoSQL database with single-digit millisecond performance at any scale. You design your table around your application’s access patterns, and DynamoDB serves those patterns with consistent low latency whether you’re handling 10 requests per second or 10 million.

Analytical queries are different. They aggregate across the whole table or ranges of the table, rather than reading individual items, and they change often. The standard approach is to keep operational traffic on DynamoDB and run analytics against a replicated copy, which historically meant building and maintaining an ETL pipeline. Zero-ETL removes that work. The integration reads from your table’s point-in-time recovery (PITR) backups, not from the table itself, so your table’s performance is unaffected. The data lands in an open table format on Amazon S3 automatically.

Introducing DuckDB

DuckDB is an in-process analytical database. There is no server to install or manage. The engine embeds directly inside a host process, whether that’s a Python script on your laptop, a notebook, or a Lambda function. Because the engine is a library, one Lambda function is a complete, self-contained analytics engine. It scales with concurrent invocations and costs nothing while idle.

DuckDB uses a columnar-vectorized query execution engine that processes large batches of values in a single operation. That is what makes it fast at the aggregations, joins, and scans that analytics demands. It installs with pip install duckdb and uses standard SQL.

The move to AWS changes nothing about the project itself. DuckDB remains free and open source under the MIT license, governed by the independent non-profit DuckDB Foundation. The founders continue to lead the project’s technical direction at AWS.

Introducing Amazon S3 Tables

Amazon S3 Tables provide storage that is purpose-built for Apache Iceberg, with compaction, snapshot management, and unreferenced file cleanup handled for you. Iceberg metadata carries per-file column statistics, so engines skip data files that can’t match a query’s predicates and read only what they need.

The data your DynamoDB table replicates into S3 Tables is immediately readable by Amazon Athena, Amazon Redshift, Amazon EMR, Apache Spark, and other Iceberg-compatible engines, DuckDB included. They all read one copy of the data.

Solution overview

The following diagram illustrates the solution architecture.

Figure 1: Solution architecture for querying Amazon DynamoDB data with DuckDB over a zero-ETL integration to Amazon S3 Tables

Here’s how the flow works:

1. Your application writes items to DynamoDB as usual.
2. An AWS Glue zero-ETL integration replicates new and changed items into an Iceberg table on S3 Tables, seeded by a full export from PITR, then kept current by change data capture (CDC) on a refresh interval, 15 minutes by default.
3. A caller sends a SigV4-signed HTTPS request containing SQL to a Lambda function URL with AWS Identity and Access Management (IAM) authorization.
4. The Lambda function runs the query with DuckDB, reading Iceberg data directly from S3 Tables, and returns JSON results.

Setting up a zero-ETL integration to S3 Tables involves several resources with specific permissions and ordering. The table bucket, an IAM role for AWS Glue, resource policies on both the table and the AWS Glue Data Catalog, and the integration itself all must be wired together in the right order. We packaged that wiring into a CDK construct, dynamodb-zero-etl-s3tables, so the integration is one construct instantiation. The complete solution is available in the aws-dynamodb-examples repository.

The Lambda query function is an example implementation, chosen to keep this post brief and well suited to ad hoc, bursty queries. The replication layer is plain Iceberg on S3 Tables, so nothing ties it to Lambda. Run DuckDB on Amazon Elastic Container Service (Amazon ECS) or Amazon Elastic Compute Cloud (Amazon EC2) instead, with more memory and no invocation time limit. The same pipeline drives much larger analytical processing without changing the DynamoDB or replication side at all.

Prerequisites

To deploy this solution, you must have the following:

• An AWS account with the AWS CDK v2 bootstrapped in an AWS Region that supports zero-ETL integrations from DynamoDB to S3 Tables.
• Node.js 20 or later and Docker, because the Lambda container image builds locally.
• Python 3.9 or later with Boto3 1.35.74 or later for the helper scripts.

Deploy the solution

Clone the repository and deploy:

git clone https://github.com/aws-samples/aws-dynamodb-examples.git
cd aws-dynamodb-examples/infrastructure_as_code/cdk/ddb-duckdb-analytics
npm install
npx cdk deploy

The following AWS CDK code is the core of the stack, trimmed for brevity (see the repository for the complete stack). It creates the DynamoDB table with PITR enabled, the zero-ETL integration to S3 Tables, and the DuckDB query function behind an IAM-authorized function URL:

const table = new dynamodb.Table(this, 'OrdersTable', {
  tableName,
  partitionKey: { name: 'PK', type: dynamodb.AttributeType.STRING },
  sortKey: { name: 'SK', type: dynamodb.AttributeType.STRING },
  billingMode: dynamodb.BillingMode.PAY_PER_REQUEST,
  pointInTimeRecoverySpecification: { pointInTimeRecoveryEnabled: true },
  removalPolicy: cdk.RemovalPolicy.DESTROY,
});

const zeroEtl = new DynamoDbZeroEtlToS3Tables(this, 'ZeroEtl', {
  table,
  tableBucketName,
  integrationName,
});

const tableBucketArn = zeroEtl.tableBucket.attrTableBucketArn;

const queryFn = new lambda.DockerImageFunction(this, 'DuckDbQueryFn', {
  code: lambda.DockerImageCode.fromImageAsset(
    path.join(__dirname, '..', 'lambda'),
    { platform: ecrAssets.Platform.LINUX_AMD64 },
  ),
  memorySize: 3008,
  timeout: cdk.Duration.minutes(2),
  ephemeralStorageSize: cdk.Size.gibibytes(2),
  architecture: lambda.Architecture.X86_64,
  environment: { TABLE_BUCKET_ARN: tableBucketArn },
});

const fnUrl = queryFn.addFunctionUrl({
  authType: lambda.FunctionUrlAuthType.AWS_IAM,
});

The deployment completes in a few minutes and prints the function URL, the table name, and the table bucket ARN as stack outputs.

Seed sample data and watch zero-ETL work

The repository includes a script that writes sample orders to the table:

python3 scripts/seed.py 500

The integration seeds the Iceberg table with an initial export from PITR, which takes 15–30 minutes after deployment. Check progress with the status script:

python3 scripts/status.py

When the initial export lands, the output shows the integration state and the Iceberg table that now exists in your table bucket:

Output:

integration status: ACTIVE
namespace zetl_255e4c7c_3149_458a_a4db_fd9506922fe9: tables ['ddbduckdbanalytics_orders', 'zetl_integration_table_state']

From this point on, replication runs on the refresh interval. Write another batch of orders, wait one interval, and the new items appear in query results with no action on your part.

The integration also flattens each item’s top-level attributes into typed Iceberg columns. The following is a sample order item as DynamoDB stores it, with its attribute type descriptors:

{
  "PK": {"S": "CUST#0007"},
  "SK": {"S": "ORDER#2026-08-15T09:12:44+00:00#000042"},
  "order_id": {"S": "000042"},
  "category": {"S": "toys"},
  "status": {"S": "delivered"},
  "amount": {"N": "187.20"},
  "items": {"N": "3"}
}

In the Iceberg table, the same item is a plain row. amount arrives as a numeric column and category as a string column, and attribute names arrive lowercased, so PK becomes the pk column. You query them directly, without unwrapping DynamoDB attribute types.

Query your data with DuckDB

The repository’s invoke script signs requests with your current AWS credentials and sends SQL to the function URL:

python3 scripts/invoke.py "
  SELECT category, round(sum(amount), 2) AS revenue
  FROM analytics.<namespace>.<table>
  GROUP BY category
  ORDER BY revenue DESC"

Replace <namespace> and <table> with the values printed by the status script.

Output:

200
{
  "columns": ["category", "revenue"],
  "rows": [
    ["toys", 28039.85],
    ["sports", 26854.24],
    ["books", 25878.79]
  ],
  "row_count": 3,
  "truncated": false
}

In our testing in the US East (N. Virginia) Region (us-east-1) with the function configured at 3,008 MB, this aggregation returned in about 400 milliseconds on a warm invocation. A cold start took about 3 seconds. Your latencies depend on function memory, data size, and query shape, so treat these as starting points and measure your own workload.

The Lambda function is a container image with DuckDB and its httpfs, aws, avro, and iceberg extensions installed at build time. Installing them at build time is necessary because the Lambda filesystem outside /tmp is read-only, so extensions can’t download at runtime. It also keeps cold starts short. The DuckDB connection and the ATTACH to the S3 Tables catalog happen at module load, outside the handler, so warm invocations skip setup entirely. Credentials come from the function’s execution role through the DuckDB credential_chain provider, and the role carries read-only access scoped to the one table bucket.

The endpoint accepts SQL by design, so the function locks the engine down after setup. The following code from the handler makes sure a query can read the Iceberg tables and nothing else:

con.execute(f"ATTACH '{TABLE_BUCKET_ARN}' AS analytics "
            "(TYPE iceberg, ENDPOINT_TYPE s3_tables, READ_ONLY)")
con.execute("SET disabled_filesystems = 'LocalFileSystem'")
con.execute("SET autoinstall_known_extensions = false")
con.execute("SET autoload_known_extensions = false")
con.execute("SET allow_community_extensions = false")
con.execute("SET lock_configuration = true")

With this configuration, DuckDB refuses local file reads, configuration changes, and writes to the catalog, whereas ordinary analytical queries work unchanged. Responses are capped at 10,000 rows by default, which helps keep them inside the 6 MB function URL response limit. Tune the cap through the function’s MAX_ROWS environment variable to suit your result sizes.

Because the data is standard Iceberg in S3 Tables, the same catalog is queryable from anywhere DuckDB runs. The following code attaches it from a laptop:

INSTALL aws; INSTALL httpfs; INSTALL iceberg;
CREATE SECRET (TYPE s3, PROVIDER credential_chain);
ATTACH 'arn:aws:s3tables:us-east-1:111122223333:bucket/your-bucket' AS analytics
  (TYPE iceberg, ENDPOINT_TYPE s3_tables);
SELECT count(*) FROM analytics.your_namespace.your_table;

That’s the whole local setup. DuckDB attaches to the catalog with your existing AWS credentials and queries the data in place. The same tables also remain available to Amazon Athena, Amazon Redshift, and Amazon EMR.

Considerations

Keep in mind the following:

• Freshness: The integration applies changes on a refresh interval, 15 minutes by default and configurable, with on-demand ingestion available. This pattern is built for analytics, where data that is minutes old is acceptable. For search or sub-second freshness needs, DynamoDB offers a zero-ETL integration with Amazon OpenSearch Service.
• Cost: PITR is billed on table size ($0.20 per GB-month in us-east-1). The initial seed is billed as a full export ($0.10 per GB in us-east-1). Ongoing replication is billed in CDC units, one unit per write up to 1 KB ($0.10 per million CDC units in us-east-1). S3 Tables bill for storage, requests, and maintenance. There’s no per-GB scan charge on queries because query compute is the Lambda invocation itself. See Amazon DynamoDB pricing for current rates in your Region.
• Access control: The function URL uses IAM authorization. Callers need both lambda:InvokeFunctionUrl and lambda:InvokeFunction on the function, and requests must be SigV4-signed. Don’t switch the URL to NONE auth, because the endpoint executes SQL.
• Arbitrary SQL: The DuckDB lockdown limits queries to reads of the attached catalog. If you expose this pattern to callers who shouldn’t write their own SQL, put an allowlist of named queries in front of it.
• Query engine placement: Lambda fits ad hoc queries with short runtimes and results that fit in memory. For sustained workloads, long-running queries, or datasets that need more than 10 GB of function memory, run DuckDB on ECS or EC2 against the same S3 Tables catalog.
• Response size: Function URLs cap non-streaming responses at 6 MB. For large result sets, consider Lambda response streaming or write results to Amazon S3 and return a link.
• Region availability: Zero-ETL from DynamoDB to S3 Tables is available in a subset of Regions. Check the AWS Glue documentation for availability.

Clean up

To avoid incurring ongoing charges, delete the resources you created:

npx cdk destroy

The integration creates namespaces and Iceberg tables inside the table bucket outside of AWS CloudFormation, which would normally block the bucket’s deletion. The stack includes a custom resource that empties the bucket during deletion, so one cdk destroy completes cleanly. S3 Tables holds a deleted bucket’s name in a transitional state for a period after deletion. If you destroy and immediately redeploy under the same name, bucket creation can fail with a conflict. Wait and retry, or deploy under a different stack name.

Conclusion

In this post, we showed you how to run DuckDB analytics on your DynamoDB data. Zero-ETL replication lands your table in Iceberg tables on S3 Tables, and DuckDB queries them from inside a Lambda function behind an IAM-authorized function URL. The operational table keeps serving traffic at single-digit millisecond latency while the integration keeps the analytical copy current without pipeline code. Queries run in Lambda, so you pay per invocation. Explore the complete solution on GitHub to deploy it in your own account.

About the author