Mark Styx

Delivered

Partner reporting API, three engines

A side-by-side test of three ways to answer the partner reporting API's slow query: the existing S3 Select approach, Athena, and DuckDB on Fargate. No new engine was adopted, but rebuilding the existing path exposed a bug, and fixing it took responses from 70+ seconds to under 5.

Context
Advertising technology
Period
2025
My role
Data Platform Lead (designer and builder)
Stack
  • Rust
  • DuckDB
  • AWS Lambda
  • ECS Fargate
  • Athena
  • EFS

Live architecture

Three ways to answer the same partner query

Pick a component to trace what it sends and receives.

Every variant answered the same request: daily impressions and revenue for one supply partner over a date range. One rebuilt today's S3 Select approach, one ran the query on Athena and polled for the result, and one served it from a DuckDB file on EFS that a nightly ECS task loaded from the same Parquet data.

Text description of this diagram

Every variant answered the same request: daily impressions and revenue for one supply partner over a date range. One rebuilt today's S3 Select approach, one ran the query on Athena and polled for the result, and one served it from a DuckDB file on EFS that a nightly ECS task loaded from the same Parquet data.

  • Partner request (reporting API): The partner-facing exchange reporting API, where some requests took 70+ seconds. The test exposed a bug in the existing path, and the fix brought them under 5. Sends to S3 Select Lambda, Athena Lambda, DuckDB Lambda.
  • S3 Select Lambda (today's approach): Production answered with S3 Select across hourly objects. The test rebuilt that in Rust: 8 concurrent selects, aggregated in memory. Sends to Hourly Parquet (select).
  • Athena Lambda (start, poll 500 ms): Starts the query on Athena and checks its status every 500 ms, up to 60 times. Sends to Hourly Parquet (scan).
  • DuckDB Lambda (forwards via ALB): A thin Rust Lambda that forwards the request to the DuckDB service, so all three variants share the same entry shape. Sends to DuckDB API (ALB).
  • DuckDB API (axum on Fargate): Rust and axum on a 2 vCPU, 4 GB ARM Fargate task, opening a read-only connection per request. Sends to DuckDB file (read-only).
  • Hourly Parquet (exchange_hour on S3): The hourly exchange aggregates every variant reads from. Sends to Nightly loader (copy).
  • Nightly loader (ECS task at 02:00): Appends new days from Parquet, keeps the last 100, then runs ANALYZE and VACUUM. Sends to DuckDB file (append).
  • DuckDB file (EFS, last 100 days): One DuckDB file on EFS, shared by the loader and the API.

Static diagram (SVG)

The problem

The partner-facing reporting API had requests taking more than 70 seconds, which had been called excessive, and it was. Production answered each request by running S3 Select across hourly objects and aggregating the results. The fixes on the table were an OLAP database or better queries, and my own guess going in was that Athena would be the simplest and probably faster for long date ranges.

A guess isn’t a decision, so I built the options side by side.

How it works

Every variant answered the same request: daily impressions and revenue for one supply partner over a date range, from the hourly exchange aggregates stored as Parquet. The diagram above shows all three paths.

  • S3 Select. A Rust Lambda rebuilt the production approach: list each hour’s objects, run up to 8 selects at once, and aggregate in memory.
  • Athena. A Rust Lambda starts the query and checks its status every 500 ms, up to 60 times.
  • DuckDB. A thin Rust Lambda forwards the request through an internal load balancer to an axum API on a 2 vCPU, 4 GB ARM Fargate task. The API reads a DuckDB file on EFS. A scheduled ECS task runs at 02:00, appends new days from the Parquet data, keeps the last 100 days, then runs ANALYZE and VACUUM.

Decisions

Rebuild the baseline instead of trusting it. Comparing new Rust services against the existing Go code would have measured the language and the code as much as the engine. Rebuilding S3 Select in the same stack kept the comparison about the engine.

Same entry point for every variant. Each backend sits behind a Lambda with the same request shape, so a client, or a benchmark, can’t tell them apart except by speed.

One writer, many readers. The nightly loader is the only process that writes the DuckDB file, and the API opens a new read-only connection per request. The file on EFS is the only state the two share.

Same build for every variant. Every service is a static ARM Rust binary built the same way, so packaging doesn’t differ between the backends being compared.

Where it landed

DuckDB wasn’t adopted for the API, and production stayed on S3 Select. The test still found the real problem: rebuilding the S3 Select path next to the alternatives exposed a bug in the existing solution. Once I fixed it, requests that had taken 70+ seconds came back in under 5. Athena stayed the analytics engine. DuckDB did find a smaller job: it became the local development engine and state store in the SQLMesh-based modeling platform, and the shared state store later moved onto EFS, the same storage pattern this test used.

Tradeoffs and lessons

Test the option you already have. The existing approach is the cheapest one to keep, so it deserves a fair rebuild before anything replaces it. Here, the rebuild is what found the bug.

An embedded engine moves the work to loading. DuckDB makes the query side simple, but someone has to own the nightly copy, retention, and maintenance. That operational cost belongs in the comparison next to latency.