# Athena or DuckDB?

> How should I query my data lake, and what changed when AWS bought DuckDB's company? Use both. Athena for big scans and ad-hoc questions with zero code. DuckDB for app queries, writing Parquet and local development, on the same files. Sorting files, or Iceberg, fixes the second-index problem without copying data.

- A decision guide from The Stack by Nicolas Dao (Cloudless Labs, Sydney)
- Web page: https://cloudlesslabs.com/stack/guides/data-lake/. The whole stack in one file: https://cloudlesslabs.com/stack/brief.md
- Tools: [S3 + Glue + Athena](https://cloudlesslabs.com/stack/aws-athena-data-lake/index.md) (Use when it fits), [DuckDB](https://cloudlesslabs.com/stack/duckdb/index.md) (Use when it fits), [Aurora PostgreSQL](https://cloudlesslabs.com/stack/aws-aurora/index.md) (Use when it fits), [Amazon Data Firehose](https://cloudlesslabs.com/stack/aws-firehose/index.md) (My default), [Amazon S3](https://cloudlesslabs.com/stack/aws-s3/index.md) (My default)

## 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)
```

| | Athena | DuckDB in Lambda |
|---|---|---|
| Best at | Scans of tens of GB to TBs, many users, zero code | Many small, selective queries; writing Parquet; local dev |
| Cost to read 1 GB | $0.005 (10 MB minimum per query) | about $0.002 |
| Cost to read 1 TB | $5, in minutes | about $2.20, but only if you build the fan-out across many functions |
| Latency | Seconds, start then poll for results | Sub-second when warm |
| Local dev | None: every test query hits AWS | Same SQL on a laptop against the same S3 data |
| Writing Parquet | CTAS, at most 100 partitions per query | `COPY`: you choose sort order, file size, partitions |
| Concurrency | 100 active queries in Sydney (adjustable), queued | Each Lambda is its own engine |

**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.

```js
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.

```js
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:

```sql
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.

## Related

- [A data lake](https://cloudlesslabs.com/stack/use-cases/data-lake.md): the use case this guide works through
- [S3 + Glue + Athena](https://cloudlesslabs.com/stack/aws-athena-data-lake/index.md): the full entry (verdict, pricing, gotchas)
- [DuckDB](https://cloudlesslabs.com/stack/duckdb/index.md): the full entry (verdict, pricing, gotchas)
- [Aurora PostgreSQL](https://cloudlesslabs.com/stack/aws-aurora/index.md): the full entry (verdict, pricing, gotchas)
- [Amazon Data Firehose](https://cloudlesslabs.com/stack/aws-firehose/index.md): the full entry (verdict, pricing, gotchas)
- [Amazon S3](https://cloudlesslabs.com/stack/aws-s3/index.md): the full entry (verdict, pricing, gotchas)
- [Every decision guide](https://cloudlesslabs.com/stack/brief.md): listed at the end of the brief
- [Map of every Markdown file on cloudlesslabs.com, with what each one answers](https://ask.cloudlesslabs.com)