Transform before load
Where a transform runs decides which machine pays for it. Here a transform before the load cuts the loaded data 25 times, from 60 MB/s to 2.4 MB/s. Load raw data first only when the load path and the warehouse have room for it.
See it on the board
The same traffic runs on two boards. The simulator measured both when this page was built.
| Measure | Goal | Load raw events, transform later | Transform first, load the result |
|---|---|---|---|
| p99 latency (Shop) | under 50 ms | 3.3 ms Pass | 3.3 ms Pass |
| p99 latency (Raw) | under 2 s | 10.1 s Fail | 14 ms Pass |
| Error rate (Raw) | under 0.1% | 5.9% Fail | 0% Pass |
| Monthly cost | 128 USD/mo | 160 USD/mo |
Key numbers
| Number | Value |
|---|---|
| Operations per second, one batch engine | 10,000 ops/s |
| Operations per second, one warehouse part | 10,000 ops/s |
| Default network link of a server | 1,000 Mbps |
2 workers · 1 TB moves in 1.1 h · 1,000 Mbps per card
How it works
A pipeline moves data from its source to a warehouse. Between them it must clean and reshape the data. You can run that work before the load (ETL) or after it (ELT). The choice is not a fashion. It decides which machine does the work, and how many bytes move.
In the mini board raw JSON events arrive at 60 MB/s. Dashboards need only the 4 percent that survives cleaning. The warehouse loads at most 50 MB/s, and the shop database, which also takes part, has 8 vCPU and must keep p99 below 50 ms.
With load first (ELT), all 60 MB/s go into the warehouse. That is more than its 50 MB/s limit, so the load falls behind and the run fails. With a transform first (ETL), a batch engine cleans the events and sends only the 4 percent to the warehouse. The loaded data falls 25 times, to 2.4 MB/s, with room to spare.
ELT is the better choice when the warehouse has room and you want to keep the raw data to reshape it later. ETL is better when the load path is the limit, or when you must remove data before it lands, for example private fields.
In either case, keep the raw data somewhere cheap, so you can run a fixed transform again.
In the levels you pick where the transform runs and check the rates on each link.