The columnar files the pipeline already produces are retained permanently and laid out so that engines can skip data by path; a single query builder then drives both the transactional and the columnar engine.
01
Byproduct
already being written
02
Retained
zone
kept, not expired
03
One query
builder
written once
04
Transactional
engine
the live database
05
Columnar
engine
reads files directly
One definition, two places to run it
Byproduct 01 Byproduct The pipeline was already writing the whole dataset in a compact, analysis-friendly format on its way into the database. It was being treated as packaging — used once and deleted on a short cycle. Parquet / S3 Retained zone 02 Retained zone Keeping it costs almost nothing, so it is now kept. The files are arranged so that which airport and which day a file covers is readable from its location, which means an engine can skip everything irrelevant without opening a single file. The unusual decision was exporting one physical piece of storage at a time rather than one business day at a time; letting the layout dictate the unit of work rather than the calendar made the export roughly fifty times faster. Hive-style partition keys / no-expiry lifecycle rule One query builder 03 One query builder Both engines are driven by the same piece of code that composes the query; only the thing being read changes. Two separately written versions would slowly drift apart, and then a difference in the answers would be indistinguishable from a difference in the code. Python / one shared SQL builder Transactional engine 04 Transactional engine The live database still answers everything that has to be up to the second. Taking the heavy analytical scans off it is the whole point: it goes back to doing the work only it can do. PostgreSQL Columnar engine 05 Columnar engine An analytical engine runs inside the existing service and reads the retained files directly. There is no extra system to operate, no separate bill, and no second copy of the data to keep in step. DuckDB / in-process, reads Parquet
The columnar files the pipeline already produces are retained permanently and laid out so that engines can skip data by path; a single query builder then drives both the transactional and the columnar engine.
01
Byproduct
already being written
02
Retained zone
kept, not expired
03
One query
builder
written once
04
Transactional
engine
the live database
05
Columnar
engine
reads files directly
Byproduct 01 Byproduct The pipeline was already writing the whole dataset in a compact, analysis-friendly format on its way into the database. It was being treated as packaging — used once and deleted on a short cycle. Parquet / S3 Retained zone 02 Retained zone Keeping it costs almost nothing, so it is now kept. The files are arranged so that which airport and which day a file covers is readable from its location, which means an engine can skip everything irrelevant without opening a single file. The unusual decision was exporting one physical piece of storage at a time rather than one business day at a time; letting the layout dictate the unit of work rather than the calendar made the export roughly fifty times faster. Hive-style partition keys / no-expiry lifecycle rule One query builder 03 One query builder Both engines are driven by the same piece of code that composes the query; only the thing being read changes. Two separately written versions would slowly drift apart, and then a difference in the answers would be indistinguishable from a difference in the code. Python / one shared SQL builder Transactional engine 04 Transactional engine The live database still answers everything that has to be up to the second. Taking the heavy analytical scans off it is the whole point: it goes back to doing the work only it can do. PostgreSQL Columnar engine 05 Columnar engine An analytical engine runs inside the existing service and reads the retained files directly. There is no extra system to operate, no separate bill, and no second copy of the data to keep in step. DuckDB / in-process, reads Parquet