Data Engineering Solutions Case Studies
Technical5 Case StudiesUpdated Sep 2026
Five short cases follow, each built around a problem that teams working with pipelines, warehouses, and the services that depend on them meet regularly. They cover a production slowdown after a release, a dispute over inventory totals, a suspected credential compromise, a read-path decision under latency and cost pressure, and the evaluation of an automated routing tool before it touches real work. The organizations are fictional, but the constraints are ordinary. Each case is read through the same four lenses: system requirements, performance evidence, access controls, and maintainability. The diagrams summarize the reasoning, so compare them with your own choices before accepting the conclusions.
Case Study 1
Restoring a dashboard after a release slowed queries
Harrow Freight Exchange, a regional marketplace that matches carriers and shippers and runs a nightly pricing pipeline.
The situation
Harrow Freight Exchange runs a marketplace that matches carriers with shippers, and its nightly pricing pipeline feeds the rate cards sales staff quote each morning. Two days after a release that renamed a date column and merged two staging tables, the pricing dashboard began showing rates six hours stale, and the query service started timing out during the morning peak. The on-call engineer, Priya, found dashboard queries scanning full history instead of recent partitions. Support tickets arrived within the hour, and two regional sales leads asked whether they should stop quoting from the dashboard altogether.
The challenge
The team had to restore a trustworthy dashboard without discarding the release, which carried a fix the warehouse vendor had requested. Rolling back everything would also reverse the schema change, and the backfill job that rebuilt partitions ran under a service account with write access to production tables that nobody on the current rotation could explain. Priya needed to decide what to restore first, what to pause, and what sales could safely use while the evidence was still incomplete.
Analysis
The team began with the requirement, which turned out to be unwritten. The dashboard had an implied freshness target of one hour and a latency expectation nobody had recorded, so the release had been reviewed against nothing measurable. Priya pulled query plans from before and after the deployment and compared bytes scanned per query. The plans showed partition pruning had stopped working, because a filter compared a timestamp to a date string once the column was renamed. That evidence pointed at one change rather than the whole release. The access problem was separate. The backfill service account could write to every production table, including rate history, so a partial run could have overwritten good data with incomplete results.
Maintainability was the neglected part. The rename had no contract test, and the dashboard query lived in a notebook that only one analyst could edit. After the immediate fix, the query moved into version-controlled SQL with a test that checked scan size for the three most common filters. The team also wrote a short runbook naming the owner of each table and the order in which partitions should be rebuilt. None of this is glamorous. It would have been unnecessary if the release had been checked against stated requirements, and the cheapest prevention was a test that fails when the query plan changes.
Actions taken
- Roll back only the rename and the staging consolidation, keeping the vendor fix, and confirm that scanned bytes per query returned to the pre-release range before reopening the dashboard.
- Revoke the broad write grant on the backfill service account, replace it with a role limited to the two partitioned tables, and log every change in the access review ticket.
- Post a short notice to sales leads stating which rate cards were current as of the last clean run and which were not, and hold quoting from stale ones until the rebuild finishes.
- Add a release gate that fails when scanned bytes for the three busiest dashboard filters rise more than 20 percent, and name an owner for every table in the runbook.
Results
Service returned to normal within the working day. Dashboard queries scanned roughly the same data as before the release, and the stale rate cards were replaced after the rebuild finished in the afternoon. The backfill account could no longer touch the history tables, and a later audit found two other service accounts with similar grants, which were narrowed the following week. The release gate caught a regression in the next sprint, when a new filter bypassed pruning, and the change was fixed before it reached production. Sales leads resumed quoting once the morning rates were confirmed, though one region reported lost bookings from the outage.
Questions for discussion
- Which evidence would have justified rolling back the whole release instead of only the renamed column?
- How should a team label outputs as unsafe while the cause of a fault is still unconfirmed?
- What minimum access model would let a backfill run without write access to history tables?
Case Study 2
Reconciling inventory totals that disagreed across reports
Juniper Feed & Supply, a farm supply retailer with 11 distribution sites and a central finance team.
The situation
Juniper Feed & Supply is a farm supply retailer with 11 distribution sites and a finance team in a separate office. Each month the warehouse system and the general ledger each produce an inventory valuation report, and for two consecutive closes the totals differed by an amount finance could not explain. Regional managers had stopped trusting both reports, and purchasing had begun placing orders from spreadsheets built by hand. The data team had inherited the pipeline from a contractor whose documentation stopped partway through the transformation layer.
The challenge
The gap was small enough to dismiss and large enough to affect purchasing. The team had to find which of four upstream sources carried the discrepancy, decide which transformation rules were authoritative, and do all of this without freezing the monthly close. Two feeds used different unit codes for the same bag size, and one site retried failed uploads, producing duplicate receipts. Nobody had a record of which version of the store master table had been used for each report.
Analysis
The first question was what the two reports were meant to agree on. Finance valued stock at standard cost, while the warehouse report used last receipt price, so part of the difference was definitional rather than a data error. The team wrote the expected reconciling items down, each with an owner. Then they traced 40 mismatched SKUs through every stage and recorded row counts and totals at each handoff. The duplicate receipts appeared after ingestion but before the store join, which narrowed the fault to one step. Access was part of the picture as well. Three analysts could edit the unit mapping table directly, and its change history did not say who had last altered the unit conversions.
Teams grounded in Data Engineering Solutions tend to treat lineage as something to maintain, not a diagram drawn once. Here the fix was to make each transformation testable. The unit conversion became a versioned lookup with a check that every SKU mapped to exactly one unit. The deduplication rule became a query that counted receipts by document number and site before and after the step. A nightly monitor then compared row counts and control totals between stages and posted any movement to the close channel. Monitoring does not make data correct, but it turns a silent change into a visible one within a day instead of at month end.
- 1Warehouse feedLast receipt price in two unit codes, and one site retried uploads.
- 2Deduplication stepRemoved repeated receipts after ingestion but before the store join.
- 3Store master joinUsed whichever master version was current at run time, not a fixed one.
- 4Valuation reportPublished totals without carrying the reconciling items forward.
Actions taken
- Freeze the unit conversion table and assign one owner to changes, recording the reason and approver for each edit in a version history that finance can read.
- Add a deduplication step keyed on document number and site, and keep removed rows in a quarantine table so any disputed receipt can be restored with one query.
- Restrict direct edits to the mapping table to the data team, and give finance read-only access to the lineage views so they can check sources without requesting extracts.
- Schedule stage-by-stage control total checks that alert the close channel when row counts or totals move beyond an agreed tolerance, starting at 0.5 percent.
Results
The gap traced to duplicate receipts from one site and a unit mismatch in two feeds, which together explained most of the difference. The next close reconciled within the agreed tolerance, and the remaining amount was a timing item finance had already expected. Purchasing returned to system reports within two cycles. The quarantine table has since restored receipts four times, which led to a narrower deduplication rule. The monitor caught a changed feed format during a routine load, a problem that would previously have appeared only at month end. Regional managers still asked for a summary of reconciling items, which the team now publishes with each close.
Questions for discussion
- Which reconciling items should finance be able to resolve without involving the data team?
- How would you decide whether a deduplication rule is removing valid records or correcting true duplicates?
- What would a useful tolerance look like for a valuation that moves with seasonal stock levels?
Case Study 3
Containing a token that read policy documents at odd hours
Pell & Osei Insurance Brokers, a mid-sized brokerage with a small platform team and an external claims portal.
The situation
Pell & Osei Insurance Brokers is a mid-sized brokerage with a small platform team and an external claims portal. Early in the week, monitoring flagged a service token reading policyholder documents from an address range the platform had never seen. The requests arrived in bursts at unusual hours, and they retrieved files in alphabetical order, which is not how portal users browse. The on-call analyst, Marcus, could not yet tell whether the token had leaked through a vendor integration or whether an internal tool had been misconfigured.
The challenge
The team had to act quickly without destroying evidence. Revoking the token would stop the activity but also break a nightly sync with a claims adjuster firm, and the platform team had a standing habit of clearing old logs to free storage. Legal needed to hear about any exposure before a notice went out, and the incident commander was away. Marcus had authority to contain but not to decide on notification, so every step had to be recorded in a form someone else could review.
Analysis
Scope came first. The token could reach a document store holding several hundred thousand policy files, so the team needed to know which files were actually retrieved, not which were reachable. Storage-layer access logs were the strongest evidence because they recorded object reads independently of the application. Marcus copied them into a read-only analysis environment, counted distinct objects per hour, and compared the pattern with normal portal browsing. The burst rate also exceeded what the rate limiter should have allowed, which suggested the limiter did not apply to this token or that requests took a path around it. Both possibilities pointed at access design, not a single bad credential.
Maintainability was weak. Token scopes had been granted for convenience and never reviewed, and the vendor integration had no owner in the service catalog. The team recorded every step in the incident ticket, including who approved each containment action and what evidence was copied beforehand. Log retention was extended on the affected accounts, and the routine cleanup job was suspended. Anyone reading the record later should be able to see why a token was revoked at a given point, not only that it was revoked, and the written sequence is what made the review possible.
- First hourAlert raised, token confirmed as source, and on-call analyst took ownership.
- Within two hoursStorage and gateway logs copied to write-once storage with a hash manifest.
- End of dayToken rotated and scoped replacement checked against the nightly sync.
- Following weekLegal reviewed the object list and approved the policyholder notice wording.
Actions taken
- Preserve storage access logs, token issuance records, and gateway logs into write-once storage with a hash manifest before any revocation or cleanup job runs.
- Rotate the affected token, issue a replacement limited to the document endpoints the vendor sync needs, and confirm the nightly sync completes under the new scope.
- Notify the legal lead and privacy officer with the scoped object list, and hold any external notice until they have reviewed the evidence and approved the wording.
- Enforce per-token rate limits at the gateway for all service tokens, and alert when a token reads from an address range not seen in the prior thirty days.
Results
The scoped review found that about 2,300 documents had been retrieved over a little under two days, all through the one token. Rotation ended the activity within the hour. The vendor had been storing the credential in a shared configuration file, which was corrected and removed from the repository. Legal reviewed the object list and approved notices to affected policyholders, a process that took several weeks. Gateway rate limits now cover all service tokens, and the access review assigns an owner to every integration. A reviewer later rebuilt the decision sequence from the incident record without interviewing anyone.
Questions for discussion
- What should be recorded at the moment a containment action is taken, and who should be able to read it later?
- When is it reasonable to revoke a credential that supports a business process with no spare path?
- How long should access logs be kept while an review is still open, and who decides?
Case Study 4
Choosing a read path for live seat availability
Marlow Ticketing, a ticketing platform serving about 380 mid-sized venues with a seat availability service.
The situation
Marlow Ticketing runs a ticketing platform for about 380 mid-sized venues, and its seat availability service must hold up when a popular event opens sales. Availability is currently read from the primary transactional database. During the last two on-sales, seat map latency climbed past the product team's threshold, and a failover during a sale would stop checkout entirely. To cope with read traffic that had little to do with purchases, the team had scaled the primary instance up, which raised cost without addressing the load pattern.
The challenge
The question was not whether the platform could be made faster, since it plainly could, but which option fit the workload without adding an unmanageable burden. Three options were on the table: a replica with a cache in front, a streaming materialized view for availability, and a separate read service fed by change events. Each had a different cost curve, a different failure mode, and a different group of people who would operate it at two in the morning during a major on-sale.
Analysis
Requirements came first. Live seat availability had to be fresh within seconds during an on-sale, while historical seating charts could tolerate minutes of lag. Measurements from the last three on-sales showed reads far outnumbered writes, but the reads concentrated on a few event keys. That skew mattered more than the average. A cache keyed by event would absorb most of the hot traffic, while a replica alone would copy the hot partition and still saturate it. Access controls shaped the choice too. The seat map was public and purchase data was restricted, so a separate read service could hold a narrower credential with a network path that never reached payment tables.
Operating cost decided most of the argument. The streaming view was the most responsive option, but it added a second system with its own failure modes, and the people carrying the pager had limited experience with stream processing. A dedicated read service was cleaner over time, yet it required the event team to change how seat updates were published. The team chose a staged path: a short-expiry cache keyed by event, a replica reserved for reporting, and a versioned change event contract that could later feed a dedicated read service without touching the publishing code. Cheap on paper is not the same as cheap to recover.
Actions taken
- Write latency targets for each read path, separating live seat availability from historical charts, and measure current p95 values against them before choosing a design.
- Introduce a cache keyed by event identifier with short expiry for live availability, and confirm through a replayed load test that the two largest events stay within target.
- Give the public seat read service its own credential and network route with no access to payment tables, and review that scope at each quarterly access check.
- Publish seat change events on a versioned contract so a dedicated read service can be added later without changing the event team's publishing code.
Results
The cache carried most of the live seat map traffic in the next on-sale, and the primary instance returned to its earlier size. Latency stayed under target for the two largest events, although a replay test showed a third hot event would still need the dedicated service. The restricted credential held even when a misconfiguration was introduced during testing, since the seat service could not reach payment data. Operating cost fell, but the stale-data window grew slightly, and the product team accepted that with a visible timestamp on the seat map. The change event contract is in use, and the dedicated service will be built only if the replay threshold is exceeded.
Questions for discussion
- What evidence would show that a cache has stopped being the right answer for a read path?
- Who should carry the pager for a streaming component, and what should they be trained on first?
- How would you weigh a stale-data window against the cost of building a dedicated read service?
Case Study 5
Piloting an automated repair router without losing oversight
Tessaly Housing Cooperative, a nonprofit landlord with about 4,200 units and a nine-person repairs desk.
The situation
Tessaly Housing Cooperative is a nonprofit landlord with roughly 4,200 units across three neighborhoods and a repairs desk of nine dispatchers. A vendor offered a language model tool that reads incoming repair requests, assigns a category and urgency, and routes them to contractors with no human review. The pitch promised a large cut in triage time, and the board wanted a pilot before the next budget cycle. Dispatchers were skeptical. Requests arrive as free text, often in a second language, and some describe a gas or water problem in only a few words.
The challenge
The vendor's accuracy figure came from a generic benchmark of service requests, which told the cooperative little about its own tenants. The real questions were whether the tool would miss urgent gas and electrical issues, whether its errors would fall unevenly across neighborhoods or languages, and how much checking dispatchers would still need to do. The pilot also had to be built so that a bad routing decision could not leave an emergency waiting in a queue nobody was watching.
Analysis
Begin with requirements the operation can state plainly. The cooperative listed urgent categories that could never be routed automatically, and it set a recall requirement for those categories well above the overall accuracy number, since overall accuracy hides a rare and dangerous failure. The evaluation used three months of past requests labeled by two senior dispatchers, with a held-out set drawn from each language and neighborhood. Access was designed in from the start. The vendor received request text and unit identifiers only, with tenant names, addresses, and phone numbers withheld, and no vendor staff had standing access to the live queue.
Maintainability for a classifier means knowing when it has drifted. Each prediction would be logged with the model version, the confidence score, and the dispatcher's final decision, so disagreement rates could be tracked weekly by category and neighborhood. A rising override rate is usually the first sign that the request mix has changed or that a model update has gone wrong. Pilots tend to end once the numbers look good, but the more useful question for the operations lead was who would notice when the tool stopped being right, and what they would do about it.
- Measure recall Track urgent-category recall and subgroup error rates each week.
- Review disagreements Dispatchers examine overrides by language, neighborhood, and category.
- Adjust thresholds Raise confidence floors or retrain when a subgroup falls below target.
- Expand carefully Widen routing share only after two clean review cycles and a tested switch.
Actions taken
- Set a recall floor for urgent gas, electrical, and flooding categories, and block automatic routing for any request the tool labels below a stated confidence score.
- Run the tool in shadow mode for a full month, with dispatchers working as usual and predictions stored but not acted on, then review disagreements by language and neighborhood.
- Limit the pilot to request text and unit identifiers, keep contact details out of the vendor pipeline, and require written approval from the operations lead for every model change.
- Keep a manual routing path behind a single switch that returns all intake to dispatchers, and test that switch with the on-call team before any share of traffic moves.
Results
The shadow month showed the tool was quick and reliable on maintenance categories such as appliance repairs, where recall was high and most disagreements concerned wording. Recall for urgent gas and electrical requests fell below the floor in the first two weeks, mainly on very short messages, so those categories stayed manual. Disagreement rates were higher for one neighborhood and one language, and the vendor was asked to explain the pattern before any rollout. The cooperative then routed about a fifth of routine requests automatically, with the kill switch tested twice. Dispatchers still review every urgent label.
Questions for discussion
- Which error would be most costly for tenants in this setting, and how should the recall floor reflect that?
- What should a dispatcher be allowed to override, and what should be recorded when they do?
- How would you judge whether an automated tool has earned a larger share of routing?
Learning Resources
Technical CASE STUDIES
China, India, United States, Indonesia, Pakistan, Nigeria, Brazil, Bangladesh, Russia, Japan, Mexico, Philippines, Vietnam, Egypt, Turkey, Germany, United Kingdom, France, Italy, Canada, South Korea, Australia, Spain.