Beyond OLAP: Cutting the Cost of Batch ETL with SynnoDB

What is SynnoDB? SynnoDB synthesizes a bespoke database engine for your workload instead of making your workload fit a general-purpose one. A general-purpose engine has to be ready for any query anyone might write, and you pay for that readiness on every run, which is where 10 - 100x of avoidable cost comes from. Workloads never stop changing either, and the answer has always been to re-invent the engine by hand. SynnoDB generates it instead, and regenerates it when the workload moves, which will matter more as agents start writing most of the queries.

SynnoDB for ETL! So far SynnoDB has synthesized OLAP engines, and now it does the same for ETL. Run a pipeline every night and you have two choices today: a general-purpose engine that is slower and more expensive than it needs to be, or something custom that you maintain forever. Neither is a good deal, so SynnoDB gives you a third: a bespoke ETL engine synthesized from your dbt project, built for the machine you are willing to pay for.

On dbt's own demo project, the bespoke engine runs 6 to 7x faster than DuckDB when neither engine has a memory limit, and up to 12x faster when both are held to 16 GiB. On that small machine it also finishes a job that DuckDB does not. We gave Jaffle Shop to SynnoDB's ETL synthesis and ran both engines on the same data:

All of this is a cost story. The machine is what you pay for, and you pay for it every night. So the result that matters most is the one where DuckDB stops: on a box small enough that nobody argues about it in a budget review, SynnoDB finished the 82 GiB run that DuckDB could not.

You do not have to change anything. Keep building your pipeline in dbt, and once it settles, synthesize an execution kernel for it.

The workload: Jaffle Shop

Jaffle Shop is a fair test and a hard one. The Jaffle Shop is the dbt example everyone knows, a shop that sells toasted sandwiches and drinks. Fair, because it is not our benchmark and cannot have been tuned to suit us. Hard, because it is full of strings and UUID keys and joins, which is the work general engines are tuned for.

Almost all of the work happens along one chain of marts. Six raw tables arrive as Parquet: customers, orders and line items, plus three small lookups for products, stores and supplies. Six staging models clean them up and six marts do the real work. Three of those marts are small dimensions. The other three are large and sit in a chain: line items roll up into orders, and orders roll up into customers.

Here is one order as it arrives in the raw tables, so you can see what the pipeline works with:

customer:  27fb9a3f-…  "Jennifer Garcia"
order:     642bb3b5-…  subtotal 7200, tax 432, total 7632   (integer cents)
                      ordered_at 2018-09-01 14:05
items:     867941e2-… → BEV-003      86ca7ccf-… → BEV-005
          03f179f1-… → BEV-002      5c347743-… → JAF-001
products:  BEV-003 "vanilla ice" beverage $6.00
          JAF-001 "nutellaphone who dis?" jaffle $11.00

And here is the whole job in one piece:

with order_items as (              -- one row per line item, fully denormalised
   select
       i.id as order_item_id, i.order_id, i.sku as product_id,
       date_trunc('day', o.ordered_at)      as ordered_at,
       p.name                               as product_name,
       (p.price / 100)::numeric(16,2)       as product_price,
       p.type = 'jaffle'                    as is_food_item,
       p.type = 'beverage'                  as is_drink_item,
       s.supply_cost
   from raw_items i
   left join raw_orders   o on i.order_id = o.id
   left join raw_products p on i.sku      = p.sku
   left join (select sku, sum(cost) as supply_cost
              from raw_supplies group by 1) s on i.sku = s.sku
),


orders as (                        -- one row per order, with the items rolled up
   select
       o.id as order_id, o.customer as customer_id,
       (o.subtotal / 100)::numeric(16,2)  as subtotal,
       (o.tax_paid / 100)::numeric(16,2)  as tax_paid,
       date_trunc('day', o.ordered_at)    as ordered_at,
       sum(oi.product_price)              as order_items_subtotal,
       sum(oi.supply_cost)                as order_cost,
       count(oi.order_item_id)            as count_order_items,
       row_number() over (partition by o.customer
                          order by date_trunc('day', o.ordered_at))
                                          as customer_order_number
   from raw_orders o
   left join order_items oi on oi.order_id = o.id
   group by 1, 2, 3, 4, 5
)


select                             -- one row per customer, lifetime totals
   customer_id,
   count(distinct order_id) as count_lifetime_orders,
   min(ordered_at)          as first_ordered_at,
   max(ordered_at)          as last_ordered_at,
   sum(subtotal)            as lifetime_spend_pretax,
   sum(tax_paid)            as lifetime_tax_paid
from orders
group by 1

From dbt project to a bespoke ETL engine

A dbt project already says everything an engine needs to know. SynnoDB takes it exactly as it is: the models say what to compute, the schema says what the data looks like, and unlike in OLAP a dbt job also ships tests that define what a correct result is.

What comes out is a C++ engine that can only run your pipeline. The generated engine only knows how to turn these six raw tables into these six marts, and that narrowness is why it is so much quicker at it.

Your own tests are what keep the result honest. The generated engine has to keep the dbt tests green, and on top of that match the reference output exactly, row for row. You never have to take our word for it, because the tests you already wrote are what decide.

You pick the machine and the engine is built to fit it. SynnoDB is told up front how many cores it will get and how much memory it may use, so it plans around those limits rather than discovering them halfway through a run. That turns the machine into the knob you control cost with: you decide what you are willing to rent, and you get an engine for that budget. For the small-machine results below, SynnoDB was told it had 8 threads and 16 GiB.

First experiment: unlimited memory

The first experiment lifts the memory limit entirely. We compared the SynnoDB engine against DuckDB 1.5.3, which is already very fast. Both ran with 8 threads on the same data, on a machine carrying far more RAM than either could use.

SynnoDB is 6 to 7x faster and needs only half the memory at the same time. The left panel shows the gap holding at every size we tested, so this is not a small-data effect that washes out once the job gets serious. On the right, SynnoDB peaks at 213 GiB of RAM on 82.4 GiB of input while DuckDB peaks at 450 GiB.

Memory is what you actually pay for. RAM is more expensive than it has been in years, and you rent it in one fixed size and pay for that size by the hour, whether the run needs it for ten minutes or ten hours. So an engine that reaches for 450 GiB to chew through 82 GiB of Parquet is one whose speed you only get by renting a big machine every night. That is why the second experiment matters more than the first.

Second experiment: a small, cheap machine

For the second experiment the machine got much smaller: 8 cores and a kernel-enforced 16 GiB ceiling, with output going to network storage and every run starting on a cold page cache. The pipeline, the data and the thread count stayed the same, and both engines met the same machine and the same limits.

Once a machine is smaller than what an engine wants, the excess goes to disk, and DuckDB pays for it. It spills at every size in the table above, and the further it has to spill the further behind it falls, from 5.9x at 8.2 GiB to 12.2x at 41.5 GiB. The job never got harder. DuckDB is just spending more and more of its time moving data around instead of transforming it.

At 82.4 GiB, DuckDB no longer finishes on this machine. It runs for 69 minutes, spilling as it goes, then hits the 16 GiB ceiling with nothing to show for it, while SynnoDB does the same job in a little over 8 minutes. That is where the comparison stops being about speed: on the machine you want to pay for, one engine delivers the pipeline and the other does not.

The engine was built for these limits from the start, so the same 16 GiB machine takes the data all the way to a terabyte:

SynnoDB keeps going long after DuckDB has stopped. 990 GiB of Parquet, or 19.5 billion rows, runs in 123 minutes on the same 16 GiB machine that DuckDB could not get through 82 GiB on. The input grows by a factor of 120 along the way and the machine never changes, so you never re-provision and never re-plan capacity. The only thing that grows is the number of hours.

Machine size stops being a requirement of the pipeline and becomes a cost decision. The question stops being "how big a machine do I have to rent for this pipeline" and turns into "how long am I willing to wait on the machine I already have".

Let's cut your ETL bill

ETL is where you pay most for generality you never use. The logic barely changes, it runs on a schedule, and it runs over the same kind of data every time. So you pay a general engine to be ready for anything on every run, even though the job is always the same one. And if a pipeline needs a big warehouse for one heavy hour, you rent that warehouse every night.

The pipelines that gain the most are the ones that hurt the most. If someone has started asking about the warehouse bill, or a nightly run has slowly turned into a four-hour run, it is worth talking to us. Stable logic, scheduled runs and data that only grows are the profile SynnoDB is built for.

You keep dbt, your models and your tests. SynnoDB takes the project as it is and gives you back an engine that does the same job on a much smaller machine, and the tests you already wrote are what prove it did it right.

If you have a pipeline whose bill is big enough to be annoying, we would like to see it.