Database Administration and Data Engineering Case Studies
Technical5 Case StudiesUpdated Sep 2026
Database administration and data engineering can look like back office work until a booking table stops updating on a Monday morning or a dashboard disagrees with the finance ledger. The five cases below come from teams that keep storage engines, pipelines, and access rules working for internal products. Each one starts with a concrete failure or a tempting proposal, then moves through evidence, decisions, and the follow-up that made the change stick. Read them for the reasoning more than the tooling. The tools differ from shop to shop, but questions about requirements, performance evidence, access, and maintainability come up almost everywhere.
Case Study 1
Label printing slowdown after a Friday schema migration
Larkspur Freight Systems, a mid-sized logistics software company whose internal platform handles shipment booking for about 340 regional dispatch offices.
The situation
The shipment booking database at Larkspur Freight Systems had run without trouble for many months before a release changed the rules. On a Friday afternoon, the platform team shipped a migration that added a non-null column with a computed default to the bookings table, which holds roughly 48,000,000 rows. The migration passed staging, but staging held about one twentieth of production volume. By early evening, dispatch offices reported that label printing had gone from under two seconds to more than forty. Nobody had paged the on-call database administrator, because the migration itself had completed without error. Lucy, who leads the platform team, opened the incident channel within the hour.
The challenge
The team had two options and neither was clean. Rolling back meant rewriting a 48,000,000 row table under live write load, which could take longer than the outage itself. Leaving the change in place meant dispatch kept waiting. The logs also showed that the migration ran under a service account with owner rights across the whole schema, a leftover from an earlier project, so the question of who could alter production tables was open too.
Analysis
The first useful move was to separate what the release changed from what the workload was doing. Lucy pulled query plans for label generation before and after the migration and found that the computed default widened each row enough that the planner stopped using the index on the hot lookup path. The index still existed, and its statistics looked healthy, which made the cause harder to see. The plans were the performance evidence the team needed, and they also showed why staging had missed the change: the smaller data set never pushed the planner toward the other path. The requirement that had gone unwritten was simple. Label lookups must return within two seconds at peak dispatch volume, and nobody had turned that into a test.
The access problem needed the same discipline. The migration account could alter any table in the schema, and the same account ran two reporting jobs. Revoking its rights in the middle of the incident would have broken label generation further, so the team first created a narrow role for the migration tool, tested it in staging, and moved the reporting jobs to a read-only role. Maintainability improved as a side effect. Migration headers now declare the expected lock level and rewrite time, and the review checklist asks whether a change touches any table above an agreed size. Those notes turn a risky change into something a reviewer can judge before it runs.
- Friday afternoonMigration with computed default completes without error
- Friday eveningDispatch reports label printing past forty seconds
- First hour of incidentQuery hint restores index plan, response under two seconds
- Following morningBackfill scheduled; migration role and access narrowed
- Following weekBackfill completes with no further slowdown observed
Actions taken
- Pinned the label lookup to its original index plan with a query hint, which brought response times back under two seconds within the first hour while the schema change stayed in place.
- Moved the default expression into a backfill job that ran in small batches overnight, then removed the hint after two clean days of production plans that matched the expected shape.
- Created a narrow migration role limited to the named schema objects, then moved the two reporting jobs onto a read-only role and removed owner rights from the shared service account.
- Added a staging step that restores a sanitized copy of production row counts before any migration is approved, and required a lock-level note in each migration header.
Results
Label printing returned to its normal range before the end of the incident, and the backfill finished during the following week without another slowdown. The team recorded the incident with a timeline and a list of changes, and the postmortem named the missing requirement rather than the person who ran the migration. Two reporting jobs needed small query changes after losing owner rights, which cost the analytics group a half day. Dispatch offices lost no bookings because the write path stayed available throughout. The next migration review caught a similar default on a smaller table and sent it back for batching before it reached production.
Questions for discussion
- Which measurements from staging would have been enough to predict the label query slowdown, and which would have been misleading?
- When a shared service account has broad rights, what is the least disruptive order for narrowing them during an active incident?
- How should a migration review decide when a schema change needs batching or a separate maintenance window?
Case Study 2
Conflicting revenue figures across finance and sales dashboards
Brightwater Retail Analytics, the internal data group of a regional grocery chain that serves about 1,200 store managers and a central finance office.
The situation
Brightwater Retail's point-of-sale data reaches the warehouse from about 1,200 stores every night, and two marts built on that feed serve different audiences. The sales dashboard counts transactions as they close at the register. The finance mart waits for a settlement file from the payment processor, nets out returns, and applies currency rules for two stores that trade in a second currency. In the third week of a quarter, the totals for the same region differed by about four percent. Store managers had begun quoting whichever figure suited their targets, and a regional director asked for one answer before the quarterly review. Mateo, the data engineering lead, was asked to find out which number was wrong.
The challenge
The problem was not that one number was wrong. Both were defensible under their own rules, and neither team had written those rules down where the other could read them. Some settlement files arrived late or missed a day, so finance totals moved after the sales report had already gone out. Mateo also had no record of which transforms ran against which source version, which made it hard to say whether a change in the gap came from the data or from the code.
Analysis
Teams grounded in Database Administration and Data Engineering usually begin by writing down what each number is supposed to mean before they touch a query. Mateo built a reconciliation table that joined transaction-level sales to settlement lines by store, business date, and payment reference, then measured unmatched rows each night. The unmatched share ran between one and six percent depending on whether a settlement file had landed. That evidence did two things. It showed the gap sat mostly in late files rather than in transform logic, and it gave the team a metric to watch every morning. Lineage tracing from source tables through each mart also turned up a column rename upstream that had quietly flipped how returns were signed in one branch, which explained a smaller slice of the gap.
Access and maintainability came up together. Three groups could edit the shared transform repository, and the finance mart was rebuilt by a job that sales staff had no permission to inspect. The team separated the rights so that each mart owner could read upstream lineage without being able to change another domain's logic. Mateo also added a validation gate before publication: a row count check against the settlement control total, and a rule that held publication when a source file was more than one business day late. The gate lives in the repository next to the transform it protects, with the reason for each threshold written beside it, so the next engineer can read why the rule exists.
Actions taken
- Agreed on a written definition of recognized revenue with finance and sales, including how returns, currency conversion, and late settlement lines should be treated.
- Added a nightly reconciliation table that matches transactions to settlement lines, with an alert when unmatched rows exceed three percent of the day's sales value.
- Gated publication of the finance mart on settlement completeness, so a late file holds the previous day's figures and labels them as provisional instead of replacing them.
- Split repository permissions so each mart owner can read upstream lineage and edit only their own transforms, and required a reviewer from the affected domain on every change.
Results
Within a few weeks, the nightly unmatched share settled below one percent on most days, and the remaining gap was almost entirely explained by two stores whose settlement feed used a different reference format. The regional director received one revenue figure, with a provisional flag on days when a file was late, which ended the practice of quoting competing totals. Two transform changes that had been made without review surfaced during lineage checks and were reverted after their owners confirmed intent. The quarterly review ran on reconciled figures, and the finance and sales leads now meet briefly on the first business day to agree on any provisional numbers before they circulate.
Questions for discussion
- Which parts of a disputed metric should be defined in writing before anyone compares queries, and who should own those definitions?
- When a validation gate holds back a report, what should the people who depend on it see in the meantime?
- How would you decide whether a shared transform repository needs stricter write permissions or better review habits?
Case Study 3
Unexpected bulk exports from a member records database
Harrow Point Credit Union, a member-owned lender with a small data platform team of six engineers and one shared security operations group.
The situation
Harrow Point Credit Union keeps member records, loan balances, and contact details in one relational database that serves branch systems and a nightly reporting integration. Just after midnight, the audit feed flagged the integration's service account running about eight times its usual export volume. The queries pulled wide tables, including contact fields the reporting job does not need. The source address resolved to a cloud network range that was not on the integration's allowlist. Priya, the overnight analyst in the security operations group, escalated to Tomas, the database engineer on call, and asked whether the integration had changed. The change log showed nothing.
The challenge
The team had to act before knowing whether this was a stolen credential, a misconfigured job, or an unannounced test. Blocking the account would stop branch reporting in the morning, and the records group needed to know which member rows had left the system. Preserving evidence mattered as much as stopping the traffic, because the database audit tables rolled over on a short schedule and older entries would disappear without anyone deciding to remove them.
Analysis
Scope came first, and the evidence available determined how wide that scope could be drawn. Tomas pulled connection records and found the suspicious sessions had authenticated with the integration's credential from one foreign address, in three bursts over about forty minutes. Statement-level auditing had been switched off months earlier because it slowed the primary during month-end batch loads, so the team could not see exactly which columns were read. Network flow records and replica statistics filled part of the gap. The sessions had pulled roughly 212,000 rows from the member table, and replica read load had climbed to match. Those numbers gave an upper bound on exposure. They were enough to justify containment and not enough to name the affected members.
Access controls were the second question. The integration account held a role that could read every member table, a far broader grant than the reporting job needed, and its credential sat in a configuration file several teams could open. Containment had to remove that reach without breaking branch systems that shared the same role. The team split the role so branch applications kept their existing grants, revoked the integration's wide read, and rotated the secret through the vault that branch systems already used. Maintainability came from the decision record. Each grant now has a named owner and a review date, and statement auditing on the member schema returned as a sampled feed so the batch window stayed clear.
- Scope access Trace sessions, roles, and client addresses before changing anything
- Preserve evidence Hash and copy logs to write-once storage before cleanup starts
- Revoke and rotate Remove wide grants and rotate the credential, leaving branch roles intact
- Review and document Update the runbook, grants, and audit settings with named owners
Actions taken
- Revoked the integration account's read rights on member tables and rotated its credential, keeping branch application roles untouched so morning teller operations continued without interruption.
- Took a snapshot of the audit tables, connection logs, flow records, and replica statistics, hashed each file, and stored copies in write-once storage before any cleanup began.
- Enabled targeted statement auditing on the member schema for the suspect role and for all administrative sessions, with sampling elsewhere so the month-end batch window was unaffected.
- Wrote the response steps into a runbook approved by the security lead and the records manager, listing who may revoke access, who may notify members, and when legal review is required.
Results
Containment held. No further sessions from the foreign range appeared after rotation, and replica read load returned to its normal band by the next morning. The preserved evidence bounded exposure to the rows those sessions could have returned, and the records manager, with legal review, decided which members needed notice under the credit union's agreed process. The investigation did not identify a clear cause in the first week, and the report says so rather than guessing. The reporting integration was later rebuilt against a narrower view, and targeted statement auditing became a standing part of the member schema.
Questions for discussion
- What evidence would you need to move from an upper bound on exposure to a list of affected members, and how far back would you look?
- When containment would interrupt morning operations, how should the team weigh speed against a fuller understanding of the threat?
- How should a shared service account be designed so that a single leaked credential cannot read everything?
Case Study 4
Choosing a read architecture for a growing catalog service
Pinecrest Commerce Labs, a marketplace software team that runs a catalog service for about 2,300,000 active listings from independent sellers.
The situation
Pinecrest Commerce Labs runs a catalog service that answers product detail requests for a marketplace with about 2,300,000 active listings. Most traffic is read-only and clusters in the evening hours of each seller's region. Over several months, p95 latency on product pages drifted from comfortable to beyond the service target during promotions. The primary database handled both seller writes and public reads, and its replica count had already been raised twice with diminishing returns. Three options were on the table: a larger primary, a cache in front of the catalog, or a separate read store fed by change events. Dimitri, the principal engineer, was asked to recommend one before the planning meeting for the next promotional season.
The challenge
Each option traded latency, cost, and operating load differently, and the security rules complicated the picture. Public reads had to avoid exposing seller drafts that were not yet published, and any copy of catalog data would inherit the visibility rules of the primary. The on-call rotation was small, so any option that added a component had to justify the pages it would generate.
Analysis
The recommendation started from the workload rather than the technology. Dimitri split a month of request logs by read pattern: lookups by identifier made up most of the volume, search was a smaller share, and seller writes arrived in bursts. Profiling showed that many slow reads were not expensive queries at all. They waited on contention for a few hot rows that promotional listings shared. A cache would absorb the detail lookups but would not help search, and its hit rate would depend on how long promotional rows stayed hot. A separate read store offered the most headroom, but replication lag would need tight limits for price and stock changes, which put a real monitoring burden on the design.
Access and maintainability weighed against the separate store more heavily than the numbers alone suggested. A copy of catalog data would need the same visibility rules as the primary, so the event pipeline had to filter unpublished drafts before anything left the write path, and that filter needed testing as carefully as the database grants. The cache was simpler to operate but could serve a listing after a takedown unless invalidation was reliable. Dimitri proposed a hybrid: a cache for detail pages with short expiry and explicit invalidation on takedown, plus a replica dedicated to search indexing. Visibility logic would stay in one place, and the on-call team would own one new component rather than three.
Actions taken
- Recommended the hybrid to the planning group with the measured hot row contention attached, so the trade was visible to the people who would carry the on-call load.
- Built the event filter that removes unpublished drafts before any change leaves the write path, and gave it its own test suite that runs on every schema change.
- Set explicit cache expiry for product detail pages and wired takedowns to publish an invalidation event, with a dashboard showing stale reads by category.
- Added a lag alert for the search replica with thresholds agreed with the seller operations team, and assigned the alert to the same rotation that owns the cache.
Results
The hybrid went into a staged rollout over the following two months. The cache covered product detail pages and cut primary reads by roughly three fifths, while the search replica took indexing load away from the write path. Promotional pages held their latency target through the next sale, and the on-call team reported fewer pages about primary contention. Two problems surfaced. Cache invalidation missed one takedown path in the first week, which led to a rule that every takedown publishes an explicit invalidation event. The search replica also needed a lag alert the team had not planned for. The added cost was modest next to the larger primary that had been rejected.
Questions for discussion
- Which workload measurements would change your recommendation, and what would you need to see before trusting them?
- When a cache and a published-visibility rule meet, where should responsibility for invalidation live?
- How should a team weigh an added component against the on-call load it creates?
Case Study 5
Automated document classification for a public records archive
Greenhollow Public Records Office, a state-funded archive that digitizes municipal documents for about 140 local agencies.
The situation
Greenhollow Public Records Office digitizes municipal documents for about 140 local agencies and publishes indexed metadata to a public search portal. Manual indexing occupies most of a small indexing team's working hours, and the backlog holds several hundred thousand pages. A vendor offered an automated classifier that reads scanned pages, assigns a document type, extracts dates and case numbers, and flags personal identifiers for redaction. The vendor's pilot report showed strong accuracy, but on documents the vendor had selected. Renata, the records manager, asked the data engineering team to test the tool on a sample drawn from the office's own holdings before any contract was signed, with a deadline tied to the budget cycle.
The challenge
The risk was not only wrong labels. A missed identifier would publish personal information on the public portal, and a wrong date could move a record out of the range where people search for it. Indexers would also be tempted to accept suggestions unchecked if the tool was fast enough. The team had to define what good looked like for this office, not for the vendor's sample, and decide who checks what before any document reaches the portal.
Analysis
The evaluation began with requirements taken from the portal's obligations rather than from the vendor's accuracy claim. Redaction errors ranked highest, then wrong dates, then wrong document types. Ayo, the data engineer on the project, built a held-out sample of 2,000 pages from three agencies with different scan quality, and had two experienced indexers label it independently so disagreement could be measured. The tool's identifier detection missed a meaningful share of handwritten notes and faint carbon copies, and its date extraction struggled where a date sat in a form letter's header. Overall accuracy looked respectable. Broken down by document type and scan condition, the weak spots fell on exactly the records that carried the most privacy risk.
Access and maintainability shaped the rollout as much as accuracy did. The vendor would receive page images containing personal data, so the team had to confirm where processing happened, how long logs were kept, and which staff could read them. The output also needed a version record, because a silent model update would make past tags impossible to reproduce. Ayo proposed storing the model version, confidence score, and reviewer identity with every suggestion, and keeping suggestions in a staging table the portal could not read. That made the evaluation repeatable and kept automation from writing straight into the public index.
Actions taken
- Set redaction recall as a hard gate: no automated suggestion reaches the portal unless a trained indexer has confirmed every flagged identifier on the page.
- Routed low confidence pages and mixed packets to full manual indexing, and sampled a fixed share of high confidence pages each week to check whether the threshold still holds.
- Kept vendor processing inside the agreed region, limited log access to two named staff, and wrote a contract clause requiring notice before any model change.
- Piloted the tool in three stages, starting with typed records, and set the pilot to stop automatically if weekly redaction misses exceeded the agreed count.
Results
The office adopted the tool for typed records, and mixed packets stayed fully manual. Indexing throughput on the typed backlog rose sharply, and the team cleared a large share of the queue in the first month of rollout. Two incidents came up during the pilot. A redaction miss was caught by the sampling step before publication, and a model update the vendor had not announced was noticed because the logged version changed. Both led to contract and process changes. Renata described the decision as slower than the vendor wanted and faster than the old process allowed, and the results went to the budget committee as a measured trade rather than a promise.
Questions for discussion
- What minimum sample would you accept for judging a classifier on a records archive, and how would you draw it so the hard cases are represented?
- When a vendor update changes outputs silently, what should the office be able to reproduce, and for how long?
- Where should the line sit between automated suggestion and human approval when the costs of an error fall on people outside the office?
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.