Athena or DuckDB?

The news

AWS bought DuckLabs, the company behind DuckDB. It was announced on 26 August 2026 and closed on 31 August. DuckDB stays MIT-licensed, the non-profit DuckDB Foundation keeps the IP, and MotherDuck (managed DuckDB) is still independent.

So far one integration has shipped: since 30 September 2026, Aurora PostgreSQL can query Parquet and Iceberg in S3 with DuckDB inside (CREATE EXTENSION aurora_analytics). AWS says Athena, Glue, EMR and the rest will get DuckDB "over time", with no dates.

Same lake, three engines

Firehose / Lambda ──▶ S3: Parquet (or Iceberg), catalogued in Glue
                         │
       ┌─────────────────┼──────────────────────┐
       ▼                 ▼                      ▼
    Athena            DuckDB                Aurora + aurora_analytics
 big scans, ad hoc   app queries, ETL,      live tables JOIN lake history,
 reports, BI         local dev and tests    in one SQL query (read-only)

Nobody should switch for cost alone: the difference is at most about 2x. The case for DuckDB is speed for small queries and developer experience.

What each looks like in code

Athena: start, poll, fetch.

const { QueryExecutionId } = await athena.send(new StartQueryExecutionCommand({
	QueryString: `SELECT dt, count(*) AS orders FROM lake.orders WHERE customer_id = '42' GROUP BY dt`,
	WorkGroup: 'primary',
}))
// poll GetQueryExecution until SUCCEEDED, then page through GetQueryResults

DuckDB in a Lambda: query and return.

import { DuckDBInstance } from '@duckdb/node-api'

const db = await DuckDBInstance.create(':memory:') // module scope: reused while warm
const conn = await db.connect()
await conn.run(`SET home_directory = '/tmp'`)                // Lambda has no HOME
await conn.run(`SET extension_directory = '/opt/duckdb_ext'`) // bundled in the layer
await conn.run(`CREATE SECRET (TYPE s3, PROVIDER credential_chain, REGION 'ap-southeast-2')`)

export const handler = async ({ customerId }) => {
	const reader = await conn.runAndReadAll(`
		SELECT dt, count(*) AS orders, sum(total) AS revenue
		FROM read_parquet('s3://lake/orders/*/*.parquet', hive_partitioning = true)
		WHERE customer_id = $1 AND dt >= '2026-01-01'
		GROUP BY dt ORDER BY dt`, [customerId])
	return reader.getRowObjectsJson()
}

The same query runs on a laptop with an SSO profile, and in CI against a few sample Parquet files with no AWS at all.

Writing Parquet with DuckDB, sorted so that both keys prune:

COPY (
	SELECT * FROM read_json('s3://lake/raw/orders/2026/10/19/*/*.gz')
	ORDER BY customer_id
) TO 's3://lake/orders' (FORMAT parquet, PARTITION_BY (dt), COMPRESSION zstd);

The second-index problem

Data partitioned by date, queried by customer, scans everything. Without copying the data:

1. Sort each file by the second key (the COPY above). Parquet keeps min/max values per chunk; Athena and DuckDB skip chunks that can't match, and Athena bills only the bytes it reads. The limit: every file's footer is still opened, fine for thousands of files, slow for millions.
2. Iceberg tables in S3 Tables: partition by day(ts) and bucket(16, customer_id). A query by date reads one day; a query by customer reads one-sixteenth of the files. Per-file statistics on every column, and AWS compacts and sorts (or z-orders) the files for about $0.01/GB in Sydney. Athena and DuckDB both read them.

Partition projection and Glue partition indexes only speed up planning. They aren't indexes.

Iceberg, briefly

Iceberg is a table format on top of Parquet: a metadata layer that adds partitions you can change later, schema changes, updates and deletes, time travel and per-file statistics. S3 Tables is AWS's managed version. It's where this lake should go next; I'll dig into it in a later guide.

Aurora with DuckDB inside

If the app already runs on Aurora PostgreSQL (17.11+ or 18.6+), aurora_analytics exposes lake tables as foreign tables, so one SQL query can join this month's live orders with five years of history in S3. It's read-only, costs nothing beyond Aurora compute and S3 requests, and is the first sign of where AWS takes DuckDB. On Neon, use DuckDB in a Lambda instead.

MotherDuck

Managed DuckDB, with a Sydney region, from $250 a month plus usage. Worth it for a shared warehouse that analysts or agents query all day. Its AI functions run in the US.