DuckDB in a Lambda - The Lightweight ETL to S3Tables

How I replaced Glue PySpark jobs with DuckDB as a lightweight ETL pattern

Jerome GUYON

Published Sep 11, 2026

Last modified Sep 11, 2026

Since a couple of months, I've been running DuckDB inside AWS Lambda as an ETL engine.

A tiny embedded engine on one side, a fully managed Iceberg store on the other, and almost nothing in between. That combination is what makes the whole approach worth writing about.

The job reads from SQL Server and writes straight into S3 Tables , one table per Lambda, all in SQL. A full run costs me a couple of cents.

A big reason this got practical for me is timing. The DuckDB-Iceberg extension went from read-only to genuinely useful in the space of a few releases:

1.4.0 LTS (September 2025) — Iceberg writes and the MERGE statement landed.
1.4.1 (October 2025) — Athena can read the Iceberg tables DuckDB writes.
1.5.3 (May 2026) — full MERGE INTO support, ALTER TABLE, partition transforms, and Iceberg v3.

A year ago I couldn't have built this and today it just works.

The problem with Glue

My requirement was boring: ingest 40-something tables from a SQL Server database into Iceberg on a daily schedule. Some tables have to get fully replaced, others need an incremental merge.

The default AWS answer is Glue, with PySpark jobs on a full Spark runtime and DPU billing. For tables ranging from a few thousand to a few million rows, I find it's totally overkill. Cold starts run into the minutes, and billing rounds up to a full DPU-minute. And I don't much enjoy developing the jobs.

There are other annoying points people rarely mention: a Glue job run isn't highly available, and it may require more network interfaces than you planned for in the VPC design. All of this can lead to extra engineering and "real glue" work.

When your job uses a connection, Glue provisions multiple elastic network interfaces in a single subnet, so the run lives in one Availability Zone. If that AZ has a bad day mid-run, your job fails and you retry. "Serverless" led me to assume more resilience than I actually got. It's worth knowing before you design around it.

For most transformation work I don't reach for Glue at all. My default is a set of small Lambdas that push the heavy lifting down to Athena, with Step Functions as an orchestrator/functional pipeline, tying them together. Athena is genuinely serverless, you pay per query per data scanned, and the compute is free.
For "read Parquet in S3, transform, write Parquet back" it's hard to beat.

Athena could do this. But I wanted one simple process that pulls from SQL Server and commits to Iceberg in one place.

I also looked at Lambda with PyIceberg and PyArrow. Unzipped, those dependencies blow past Lambda's 250 MB limit, so you end up shipping a Docker image. That works, but now you're babysitting container builds, an ECR repo, and slower cold starts.

So the question became: what if I replaced the whole process with the lightweight DuckDB engine, connected to SQL Server itself and writing straight to S3 Tables?

The architecture

Here's what runs:

1
2
3
4
5
6
EventBridge Scheduler → Step Functions → Lambda (parallel Map, one per table)
	│
	├── DuckDB + extensions (pre-bundled)
	├── ATTACH SQL Server (mssql extension)
	├── ATTACH S3 Tables (iceberg extension)
	└── CREATE TABLE AS / MERGE INTO

The Lambda runs Python on Graviton with a 15-minute timeout. DuckDB loads five pre-bundled extensions: aws, httpfs, avro, iceberg (official), and mssql (community). Step Functions fans out with a Map state, one invocation per table, and retries transient Lambda errors with backoff.

DuckDB works as a polyglot SQL engine here. It reads from SQL Server through the mssql extension's ATTACH and writes to S3 Tables through the iceberg extension using the Glue catalog. No intermediate Parquet files, no staging bucket. Source to destination in one SQL statement.

I'll come back to memory sizing and the other tuning knobs in a dedicated section further down. For the narrative, keep the mental model simple: attach, read, write.

Why S3 Tables is the part that matters

I could have written the output to raw Parquet in a plain bucket and called it a data lake and I've done that before. It works until it doesn't: small files pile up, queries slow down, and you end up writing your own compaction jobs and snapshot cleanup. That maintenance is where data lakes quietly rot.

And S3 Tables removes that chore. It's a managed Apache Iceberg table store, and the maintenance runs for you. S3 continuously compacts small objects into larger ones, manages snapshots, and removes unreferenced files.

The code

Here's the core of my Lambda.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import duckdb

EXTENSIONS_DIR = "/var/task/extensions"

# --- Runs once per cold start, reused across warm invocations ---
db = duckdb.connect()
db.execute("SET home_directory = '/tmp'")
db.execute(f"SET extension_directory = '{EXTENSIONS_DIR}'")

# Load pre-bundled extensions from the deployment package (no network)
for ext in ("aws", "httpfs", "avro", "iceberg", "mssql"):
    db.execute(f"LOAD '{EXTENSIONS_DIR}/{ext}.duckdb_extension'")

# Attach SQL Server as a source
mssql_url = f"mssql://{user}:{password}@{host}:{port}?database={database}&encrypt=true"
db.execute(f"ATTACH '{mssql_url}' AS src (TYPE mssql)")

# Attach S3 Tables (Iceberg) as destination
db.execute(f"CREATE SECRET (TYPE s3, PROVIDER credential_chain, REGION '{region}')")
db.execute(f"ATTACH '{account_id}:s3tablescatalog/{table_bucket}' AS catalog (TYPE iceberg, ENDPOINT_TYPE glue)")

# --- Runs on every invocation, one table per event ---
def handler(event, context):
    # per table work

The connection, the extension loading, and both ATTACH calls sit globally so they run once per execution environment on cold start. Only the per-table work lives inside handler, which runs on every invocation.

The ingestion itself has two possible modes:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
def handler(event, context):
    
    # PSEUDO code

	# Full replace mode: drop and recreate
	db.execute(f"DROP TABLE IF EXISTS catalog.{namespace}.{table_name}")
	db.execute(f"CREATE TABLE catalog.{namespace}.{table_name} AS SELECT * FROM src.{source_table}")

	# Incremental mode: MERGE INTO
	db.execute(f"""
		MERGE INTO catalog.{namespace}.{table_name} AS t
		USING (SELECT * FROM src.{source_table}) AS s ON {on_clause}
		WHEN MATCHED THEN UPDATE SET *
		WHEN NOT MATCHED THEN INSERT *
	""")

That's the entire ETL logic. One ATTACH for the source, one ATTACH for the destination, one SQL statement to move the data. DuckDB handles type mapping, batched reads, Iceberg metadata, the Parquet writes, and catalog registration. I write SQL and get out of the way.

Pre-bundling extensions at build time

By default DuckDB downloads extensions at runtime from extensions.duckdb.org.
This is very handy for experimentation usage, but in a real production environment we want more control on versions or in a private subnet with no NAT gateway, that download fails and your queries break in confusing ways.

The fix is to fetch the extensions with your favorite build tool. With CDK's bundling phase, at cdk deploy time, the build container downloads the gzipped extensions for the target architecture and drops them into the package, so nothing needs the network at runtime:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// In CDK 
new pythonLambda.PythonFunction(this, 'DuckDbEtlFn', {
  runtime: lambda.Runtime.PYTHON_3_10,
  architecture: lambda.Architecture.ARM_64,
  timeout: cdk.Duration.minutes(15),
  memorySize: 512,
  // Define a larger size for ephemeral storage 
  ephemeralStorageSize: cdk.Size.mebibytes(5120),
  bundling: {
    commandHooks: {
      afterBundling: (_inputDir: string, outputDir: string) => [
        // Official extensions from extensions.duckdb.org
        `bash -c 'mkdir -p ${outputDir}/extensions && for ext in aws httpfs avro iceberg; do curl -sSfL --retry 3 https://extensions.duckdb.org/v1.5.4/linux_arm64/\${ext}.duckdb_extension.gz | gunzip > ${outputDir}/extensions/\${ext}.duckdb_extension; done'`,
        // Community extension from a separate registry
        `bash -c 'curl -sSfL --retry 3 https://community-extensions.duckdb.org/v1.5.4/linux_arm64/mssql.duckdb_extension.gz | gunzip > ${outputDir}/extensions/mssql.duckdb_extension'`,
      ],
    },
  },
});

Extensions are architecture-specific, so linux_arm64 has to match the Graviton runtime.
Official and community extensions live on separate registries.
The version in the URL (v1.5.4) must match the installed duckdb package exactly.

At runtime, LOAD reads from the local filesystem and makes zero network calls. The Lambda runs happily in a fully isolated subnet.

The deployment package is the DuckDB Python wheel (~50 MB unzipped) plus the five extensions (~130 MB unzipped) plus my handler. That lands around 180 MB unzipped, under the 250 MB ceiling, so no Docker image needed. It's not roomy though: iceberg alone is 43 MB, and one or two more heavy extensions would push you over.

Glue vs DuckDB Lambda

Metric	Glue (PySpark)	DuckDB Lambda
Cold start	45-90 seconds	~2 seconds
Runtime (500K rows)	2-3 minutes	8-12 seconds
Cost per run (40 tables)	~$0.45	~$0.03
Availability model	Single AZ per run	Lambda regional
Min billing unit	1 DPU-minute	1 ms

These are order-of-magnitude figures from my own runs, not a formal benchmark, so treat them as "roughly this big," not exact

For billions of rows and heavy distributed transforms, Glue still earns its keep. But for mid-size tables on a schedule, this is a 15x cost difference and is way faster.

Tuning and optimization

The handler I showed earlier is the base version of the pattern. For larger tables with a MERGE, it may die with an out-of-memory error partway through the hash join. So here are some changes you might want to make (thanks to my collegue Zineb Errahmouni for the deep dive on these):

1. Give DuckDB a hard memory limit and let it spill to disk.

DuckDB defaults to using roughly 80% of available RAM, and a MERGE builds a hash table that wants RAM it can't spill. On Lambda, memory is shared with the extensions and the OS, so it may produce Out Of Memory crashes. You have to pin a hard limit below the Lambda's memory and to point the spill directory at the ephemeral disk.

1
2
3
4
5
6
# Cap the engine well below the Lambda's memory ceiling
db.execute("SET memory_limit = '1024MB'")

# Let large joins spill to the ephemeral disk instead of dying
db.execute("SET temp_directory = '/tmp'")
db.execute("SET max_temp_directory_size = '4GB'")

I run the Lambda at 4 GB but cap DuckDB at 1 GB. It looks wasteful but the spill size may be large. Measure.

2. Pin the thread count.

DuckDB sizes its thread pool from the detected core count, and each thread grabs its own set of buffers. On a Lambda that reports more vCPUs than you sized memory for, that parallelism eats RAM you wanted for the join.

1
db.execute("SET threads = 2")

Lambda gives you vCPU depending on memory amount. Setting threads = 2 stops DuckDB from possibly over-allocating. Set this for Lambdas at 4 GB or more.

3. Drop insertion-order preservation.

By default DuckDB preserves the order rows arrive in, which costs memory and time on a bulk load. For an ETL that dumps rows into Iceberg, order doesn't matter. We can turn it off.

1
db.execute("SET preserve_insertion_order = false")

On my wider tables this shaved a noticeable slice off both runtime and peak memory. Free win for a bulk pipeline.

4. Stage the source rows in a temp table before the MERGE.

Reading straight from src.table inside a MERGE means the SQL Server connection stays open for the whole merge, and DuckDB re-plans against a remote source. Pulling the rows into a local temp table first keeps the SQL Server session short and hands the MERGE a local, well-typed input. It also gives you a clean place to add columns or to make changes.

1
2
3
4
5
6
7
8
9
10
# Stage locally, close the remote read quickly
db.execute(f"CREATE TEMP TABLE _staging AS SELECT {columns}, '{instance_id}' AS instance_id FROM src.{source_table}")

# Then MERGE from the local staging table
db.execute(f"""
    MERGE INTO catalog.{namespace}.{table_name} AS t
    USING _staging AS s ON {on_clause}
    WHEN MATCHED THEN UPDATE SET *
    WHEN NOT MATCHED THEN INSERT *
""")

For a full-replace of a small table, reading directly is simpler and I skip the staging step.

Things to know

The 15-minute wall: That's the Lambda's hard timeout. A single table that runs longer needs a different home, probably Fargate. Maybe Lambda MicroVMs is an option now.
S3 Tables maintenance: compaction and cleanup run automatically.

My take

The DuckDB team recently joined AWS, which I read as a decent signal for the extension ecosystem's future. But I'd have built this the same way without that news.

If you're still running PySpark to load a few million rows into Iceberg every night, try this ;)

Credits

Big thanks to Zineb Errahmouni for her deep dive on the DuckDB memory and tuning behavior that shaped the tuning section.

— Jerome

# data # aws-lambda # etl # amazon-s3 # serverless

Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.

Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article

Builder Center

DuckDB in a Lambda - The Lightweight ETL to S3Tables

Jerome GUYON