Designing burst shaped ingestion pipelines for a show that peaks in four days

It is 13:40 on the second day. The operations lead wants to know how many scans have gone through the north entrance since doors opened, because he is deciding whether to move two staff. The answer exists in the access control vendor's system and lands in your warehouse at 02:00 tomorrow, which is a nightly batch built by somebody who was thinking about a business with a Monday morning reporting cycle.

Burst shaped ingestion pipelines exist because an exhibition's data does not arrive at a steady rate. Almost the entire year's operational volume lands inside four days, and the design that follows from that is less demanding than most teams assume and demanding in a different place than they expect.

The shape of the arrival curve

Start with the numbers rather than the adjective.

One edition produces 38,900 badge scans. Those arrive across four show days, which is 96 hours, giving a flat average of 405 scans an hour. The flat average is misleading, because scans only happen while the halls are open. Four days at eight open hours is 32 hours, which puts the operating average at 1,216 an hour. The busiest hour, typically the first hour of day one when everybody badges in at once, runs closer to 2,400, or about 6.2 per cent of the entire edition inside sixty minutes.

Now set that against the rest of the year. 38,900 scans spread over 8,760 hours is 4.4 an hour. The peak hour is roughly 540 times the annual mean rate, and for 8,664 hours of the year the true rate is zero.

The registration side has a different shape and is worth putting next to it. 12,400 registrations accrue across roughly nine months, which is about 46 a day, or under 2 an hour. Registration is a trickle with a late surge. Scanning is a wall. A single pipeline design that treats both the same way will be over engineered for one and wrong for the other.

What should you size the loader for?

The peak hour, and then you can stop worrying about throughput.

2,400 rows an hour is 40 rows a minute, which is 0.67 rows a second. Any database written in the last thirty years ingests that without noticing. Even if a hall opening produced ten times the modelled peak, 24,000 rows an hour is 400 a minute, and a single COPY statement moves that in well under a second.

The PostgreSQL 18 documentation, current in 2026, makes the mechanism explicit: "Use COPY to load all the rows in one command, instead of using a series of INSERT commands. The COPY command is optimized for loading large numbers of rows; it is less flexible than INSERT, but incurs significantly less overhead for large data loads." The same page notes that loading a large number of rows with COPY "is almost always faster than using INSERT, even if PREPARE is used and multiple insertions are batched into a single transaction".

So throughput is a solved problem at exhibition scale, and teams that spend show week tuning it are solving the wrong thing. What people actually complain about on the floor is latency: the gap between a badge being scanned and that scan appearing in the number on the screen. That gap is set by the batch interval and by how long the vendor holds data before making it available, and neither is affected by how fast your loader runs.

Working the batch window

Pick the interval from the decision it supports, then check the batch size falls out sensibly.

The operations lead moving staff between entrances needs a number that is minutes old. A five minute interval satisfies that. At the peak rate of 2,400 an hour, a five minute batch carries 200 rows. Two hundred rows through COPY, plus an idempotent write and a refresh of one aggregate, is a job measured in hundreds of milliseconds, and there are 288 of those runs in a day.

Across four show days that is 1,152 runs carrying 38,900 rows in total, an average of 34 rows a run. The overwhelming majority of those runs will find nothing at all, because they fire overnight and before doors. A loader that handles the empty case cleanly and exits is doing most of its work.

Two consequences follow. The scheduler needs to fire frequently for four days and almost never for the rest of the year, which is an awkward shape for anything billed by uptime and is a cost question of its own. And every one of those 1,152 runs has to be safe to repeat, because a run that fails halfway through at 11:15 on day two will be rerun by somebody who has thirty seconds to think about it, which is why rerunning a load has to be harmless.

What actually breaks during show week

Volume is the thing teams prepare for and rarely the thing that fails. The Kimball Group's technique summary on ETL architecture, drawn from The Data Warehouse Toolkit third edition (Wiley, 2013), opens by observing that "the extract, transformation, and load (ETL) system consumes a disproportionate share of the time and effort required to build" a warehouse, and the disproportion during show week sits almost entirely in the extract stage.

The recurring failures have a pattern. Vendor exports resent with overlapping ranges, so the same scan arrives in two files. Scanner clocks that drifted while a handheld unit was offline during setup, putting an hour of activity in the wrong hour. Rate limits on the access control platform's API, hit for the first time because the polling interval was shortened to five minutes that morning. A disk filled by debug logging somebody switched on to diagnose the first problem. Credentials that quietly expired during the eleven quiet months.

None of those is a throughput problem and none is fixed by a faster loader. They are handled by treating every export as potentially duplicated, recording the source file and the vendor's own identifier on every row, and backing off politely when the API says no.

Ralph Kimball's The Data Warehouse ETL Toolkit (Wiley, 2004) is still the reference that treats these as first class design concerns rather than as operational accidents, and the Kimball Group describes it as providing "detailed guidance regarding the design and development of comprehensive extract, transformation, and load systems". The framing that helps most is that the extract stage owes you a contract about what it delivers, and that contract is enforced by your code because the vendor is not going to enforce it.

Is a streaming pipeline worth it for four days?

I would not build one, and the reasoning is about the other 361 days.

A streaming ingestion path buys latency measured in seconds instead of minutes. On a show floor, the decisions that data supports run on a slower clock than that: moving staff, opening an overflow hall, deciding whether to keep a bar open. Five minutes is comfortably inside the useful window for all of them, and the operations lead is walking to the entrance anyway.

Against that, a streaming path adds a broker, a consumer, offset management, dead letter handling and a monitoring surface, all of which must survive eleven months of nobody touching them. The failure mode of a batch job that has not run since last September is a clear error in a log. The failure mode of a stalled consumer with a stale offset is a dashboard that looks fine and is silently eleven months behind.

The one case that changes the answer is a genuinely real time product feature: a matchmaking prompt that fires when a buyer walks past a stand, or a live capacity gate on a session room. That is a product requirement, and it should be built as its own path rather than by converting the reporting pipeline into something the reporting pipeline does not need to be.

Where this stops

The arithmetic above assumes the data arrives during the burst, and a meaningful share of it does not.

Lead retrieval exports from exhibitors often land days or weeks after the show closes, because the exhibitor has to log into a vendor portal and download them. Registration corrections trickle in for a month. A hall that lost network for two hours on day two will deliver those scans in a lump when the units are docked. So the pipeline that peaks in four days also has to accept a long tail, and a loader written on the assumption that the edition is closed when the halls close will either reject those rows or double count them.

The second limit is that peak hour figure. 2,400 is a modelled number from one show's shape, and a consumer show with a 09:00 opening ceremony, or a hosted buyer programme that badges everybody in a single wave, produces a spikier curve than a B2B trade show where arrivals spread across the morning. Model your own from last edition's scan timestamps before sizing anything, and do it per entrance rather than for the show as a whole, because the entrance level peak is what determines whether a single scanner queue backs up.

Pull last edition's scan export, bucket the timestamps by hour, and sort descending. The top row is your real peak and the exercise takes ten minutes. If that number divided by sixty is a rate your loader handles comfortably, you can stop thinking about throughput and spend the effort on keeping the thing alive through the quiet months, which is where an annual cadence pipeline usually fails, and record the figure in the data platform conventions so next year's team does not remodel it from scratch.