How we built a local SQL engine for DynamoDB (a database with no JOINs)

DynamoDB can export a 100 GB table but it cannot aggregate one. There is no JOIN, no GROUP BY, no COUNT(*) — not slow versions of them, none — and even PartiQL, AWS's own SQL-ish surface, doesn't add them. So every team ends up writing the same throwaway script: paginate a Scan, fold rows into a map, print the answer, delete the script.

DynoTable's Workbench is our answer to that script: a tab where you write one real SELECT — multi-table JOIN … ON, WHERE, GROUP BY, HAVING, DISTINCT, CASE, aggregates — against tables that support none of it. This post is how it works under the hood, the parser that silently lied to us, and why we later tore out the engine's memory model.

Don't build a query engine

The core decision was refusing to write a relational executor. DynamoDB pages stream into an embedded SQLite database, and SQLite — one of the most tested query engines on earth — does the relational work:

Each DynamoDB item lands in SQLite as its raw typed envelope ({platform: {S: "ios"}}) in a single item column. A small user-defined function unwraps attributes at query time, so your SELECT platform compiles to a attr(item, 'platform', …) call. One detail in that function is load-bearing: it wraps its JSON parsing in a catch, because the SQLite binding turns one malformed row's parse error into an abort of the entire prepared statement. Without the catch, one corrupt row would kill the query.

The honest constraint we kept: DynamoDB's access-pattern physics still apply. A JOIN's to-side must be a primary key or a GSI partition key — the engine resolves joins by key lookup against the stream, never by cross-product scan. If you need arbitrary joins on non-key attributes, that's a modelling conversation, not a query-engine feature.

The parser that lied to us

The first version used a popular off-the-shelf SQL parser. Over a few weeks, four independent correctness bugs traced back to it:

• SELECT DISTINCT was silently dropped. The parser accepted it and the emitter ignored it — every duplicate row came back. A user's bug report read: "shows a table of all the values, not counters."
• Anything unhandled became the literal SQL NULL. CASE, CAST, scalar subqueries — the emitter's fallback for an unknown syntax node was return 'NULL'. Queries ran, returned confident wrong answers, and raised no error.
• Identifier case didn't behave like SQL. SELECT PLATFORM FROM t returned nulls when the attribute was platform — the opposite of what every SQL database trains you to expect.
• Autocomplete disagreed with the resolver, suggesting names the compiler would then fail to resolve.

The replacement, sql-parser-cst, won on one primitive the survey found nowhere else: its tree preserves both the raw text and the normalised name of every identifier — so the compiler can tell platform from "PLATFORM". That single distinction lets one grammar carry proper ANSI semantics: unquoted identifiers resolve case-insensitively, quoted ones are the case-sensitive escape hatch, exactly like Postgres or SQLite. It also carries a source range on every node, so a diagnostic underlines the offending construct instead of the whole statement.

And we replaced the return 'NULL' fallback with a rule we now treat as policy: no silent NULL, ever. The emitter's default arm is a precise-ranged error. Window functions get a message that says why, not just no:

Window functions are not supported in Workbench.
They have undefined semantics on the joined-row materialization.

The trade we accepted: a pre-1.0 dependency, pinned to the exact version, upgraded only by hand with the test suite as the gate. We evaluated patching the old parser (it needed a parallel pre-tokeniser — parsing every input twice), using the editor's syntax tree (token-level, no clause structure), and hand-rolling (most of a SQLite parser, unbounded maintenance). A four-way correctness failure beats all of those concerns.

Knowing what not to block

One diagnostic deliberately does not stop you. When a filter references an attribute the schema can't confirm the casing of, we can't know the canonical case — DynamoDB's server-side filters are case-sensitive and its schema only declares key attributes. That emits a warning, and warnings never disable Run. We learned this class of lesson the hard way in our validator work: a gate that rejects the most common legitimate query shape is worse than no gate.

The cap was the wrong shape

The first aggregation engine buffered scanned rows into an in-memory SQLite with a size cap — 64 MB here, 250 MB there, and one path with no cap that could OOM the app on a big table. Hit the cap and you got a partial answer with an overflow flag.

The redesign started with a reframing: the competitor is the throwaway script a developer would write instead. And a competent script doesn't buffer-then-cap — it streams, folding each page into a running total with bounded memory. The in-memory cap wasn't too small; it was the wrong shape. Worse, a partial aggregate isn't "incomplete" — it's wrong. The moment that made this concrete: our own AI demo confidently narrated a partial "top spenders" ranking as fact.

The rebuilt Compute engine works the way the script does, plus everything the script never bothers with:

• A planner classifies every query into one of three lanes: stream-fold (plain algebraic aggregates — COUNT/SUM/AVG/ MIN/MAX — folded page-by-page off the main process; size never gates a foldable query, it just takes longer), materialise (sorts, key-bounded joins — spilled to disk-backed SQLite, no memory cap), or an honest refusal that recommends the right heavyweight tool (DynamoDB's S3 export plus Athena) for the tail we can't serve.
• The fold classification is deliberately narrow. A HAVING, an ORDER BY, a bare column next to the aggregate — any of them forces the materialise lane. A fold that ignored a clause and still reported "exact" would be precisely the wrong-but-confident bug this planner exists to prevent.
• Exactness is part of the result. Aggregate columns show a partial badge while pages are still streaming, and drop it only when the scan has drained. Even arithmetic honesty is tracked: integer sums stay exact past 2⁵³ (stored as 64-bit integers), while a fractional sum that exceeds the float mantissa flags itself partial: precision rather than round quietly.
• Fuses replace popups for machines. The interactive app asks before a five-million-item scan; the AI assistant and MCP callers get scan budgets and page-boundary aborts instead. And the result envelope the model sees is deliberately trimmed — cost class and size estimates are withheld, because surfacing a stale guess invites the model to narrate it as fact.

One cost note that surprised people internally: for a one-off aggregate, a live scan is roughly 6× cheaper than generating a DynamoDB→S3 export — which is why the script-shaped lane is the default and the warehouse is the recommendation for repeated heavy analytics, not the reflex. (The pricing calculator will price a full pass of your own table.)

Is it safe to point SQL at production?

The Workbench is structurally read-only. The final SQL that reaches the local engine passes a defence-in-depth gate that tokenises and rejects anything that isn't a single SELECT — as a contract violation, not a user error, because the compiler should never have produced it. The SQLite substrate runs with dangerous built-ins disabled, and writes to DynamoDB have exactly one path in the entire app: the reviewable staging area, which SQL cannot reach.

What transfers if you're building one

• Don't write a query engine; write a faithful compiler onto one that already exists. Your bugs will live in the seams (identifier case, type envelopes, corrupt rows), not the join algorithm.
• Demand quote-provenance from your parser. If it can't tell name from "NAME", you cannot implement SQL's case semantics, and your users will find out before you do.
• Make "unhandled syntax" a loud error, never a default value. Silent NULL is how confident wrong answers ship.
• Benchmark your design against the throwaway script your user would write. If the script streams and you buffer, you built the wrong shape.
• Treat partial aggregates as wrong answers that need labelling, not partial credit.

The Workbench docs cover the day-to-day feature surface, and SQL for DynamoDB maps what works across the whole ecosystem. Or just download DynoTable, open a Workbench tab with ⌘⌥Q, and run a JOIN against the database that doesn't have one.