Business Cases

It's Monday morning at Meridiaan Bank. Your manager wants a portfolio overview for the standup — from the investments data mart: a fact_position table joined to dim_customer, dim_instrument and dim_date. It's real-world messy: stale snapshots, closed positions, and three currencies. Join the star, clean it, get the number right.

Q4 planning at Atlas. The sales ledger mixes completed orders with cancelled and returned ones — quote revenue off the raw table and you'll overstate the book by hundreds of thousands.

Procurement renews carrier contracts next month. The shipment log is full of in-transit and cancelled rows — some still carrying a stale on-time flag — and a careless on-time rate will hand the wrong carrier a renewal.

The CFO has a board meeting Thursday and a number that doesn't tie out. The spend mart — a fact_transaction table around dim_cost_center and dim_date, with budgets in fact_budget — is full of pending entries, a reversal, and a prior-year row that leaked in. And one budget was re-baselined mid-year, so 'the budget' isn't a single row. Find the real overruns before he walks in.

CliniqHub's medical director suspects uneven workload across doctors in the same specialty. The clinical mart is a star: fact_appointment joined to dim_doctor, dim_patient and a conformed dim_date, with a second fact_doctor_capacity sharing the same doctor and date dimensions. The log mixes completed visits with cancellations and no-shows — count the wrong ones and the story falls apart.

The CFO is pitching investors next week and needs a clean MRR story. The revenue mart is a star: fact_subscription joined to dim_customer, dim_plan and a conformed dim_date, with a second fact_mrr_monthly snapshot sharing the same plan and date dimensions. Stale snapshots and free trials lurk in the data — count the wrong rows and the whole deck is wrong.

The growth team agrees conversion is down but not where the funnel leaks. The clickstream mart is a star — fact_session and fact_funnel_event around conformed dim_channel, dim_date and a dim_funnel_step lookup — but it's modelled straight from a raw event feed: duplicate fires, a stray non-funnel event, an unattributed session and split orders. A careless query points at the wrong step.

Marketing and the CFO are fighting over who gets credit for €600 of new-account revenue. You'll settle it with last-touch, first-touch and linear attribution — but it's a two-fact marketing mart, the touch feed is dirty, and a careless query crowns the wrong channel.

Lumen Care is done sending every patient the same recall reminder. They want RFM segments — recency, frequency, monetary — built from the patient-engagement data mart: a fact_encounter event table joined to dim_patient and dim_date, with a monthly fact_rfm_snapshot sharing those same dimensions. But the ledger carries reversed encounters (some dated after the real last visit), and the snapshot history has a stale reload — so a careless model rewards patients for visits that never settled.

The CHRO presents to the board on Thursday and the attrition figure looks wrong against headcount. Rebuild it from the HR data mart — a monthly headcount snapshot and a terminations fact around shared employee, department and date dimensions. It's real-world messy: a stale snapshot, blank-string statuses the export never nulled, and tenure to band.

Three products ran dry over a holiday weekend last quarter and it cost real money. The warehouse manager wants a daily stockout-risk report — built from the inventory data mart: two fact tables (a daily stock snapshot and the order-line feed) sharing the same product and date dimensions. It's real-world messy: stale snapshots, a late out-of-order correction, and noise in the order feed.

The nightly load into the warehouse doesn't match the source system, and finance noticed before you did. Reconcile two facts over the same conformed order dimension: find what the load dropped, what it re-loaded — and don't mistake a correctly-cancelled order or a late-arriving one for a bug.

Reports off the investments mart have started double-counting AUM, and the model is the suspect. The customer dimension at the centre of the star has integrity problems — duplicated surrogate keys, orphan facts, broken history — find them all before the next regulatory run.

Friday at 18:00 the CFO signed the daily payments total for 12 June into the weekly liquidity pack. Monday at 08:30 the same query for the same date returned a different number. The warehouse loads from the mainframe in incremental batches, and a Saturday-night backfill recovered a feed interruption — delivering genuinely late transactions and, it turns out, re-delivering rows that were already loaded. Reconstruct exactly what the CFO saw on Friday, prove row by row what changed and why, and hand him an audit-grade bridge from the signed figure to the corrected one.

A small coffee roaster wants its first proper sales report — revenue by category and best-selling products. The data is tidy; the only catch is a single cancelled order that shouldn't count as a sale. A gentle first JOIN-and-GROUP-BY.

Your very first morning at the coffee roaster. Before anyone asks for a report, learn to read the data: pull up the order log, keep the real sales, list what the shop sells, and find the biggest order. SELECT, WHERE, DISTINCT, and ORDER BY — one at a time, no joins yet.

Lumen renegotiated its insurer contracts: each clinic gets a fixed number of funded new-patient intake slots per year. Tess suspects clinics kept accepting registrations long after the slots ran out, because nobody could see the counter ticking. Build the counter: monthly intake per clinic, a running cumulative against the commissioned capacity, and for each clinic the month it fell off the cliff.

Lumen's board wants what has never existed: the clinics and the never-integrated ApotheCare pharmacy arm on one monthly page. The pharmacy stores cents, voids and refunds where the clinics store euros and cancellations, and the hand-stitched spreadsheet has produced a different total every quarter — once a hundred-fold one. Align the two ledgers into one shape, prove nothing was lost, and answer the question only a stitched set can: how many patients does the network truly serve?

Two days before the reserving committee, Isolde's specialty casualty book reads a 94% loss ratio — and her gut says it's worse. The claims hub reports case-incurred (paid + reserve); finance books only what's settled. The two feeds have drifted apart, don't share a clean key, and span three currencies. Rafael blames double-booked reopened claims; Isolde suspects paid losses with no reserve ever raised. Decompose the gap into duplicate-row inflation versus genuine unreserved leakage — in clean USD.

The property treaty book reads a flat ~96% combined ratio year over year, and the underwriting leads use that to argue every renewal cohort is as good as the last. Rafael doesn't buy it: a few mature legacy cohorts are propping up the average while the 2021–2023 soft-market years quietly deteriorate as their losses develop. Comparing a 2023 cohort at 6 months against a 2018 cohort at 60 months is meaningless. Strip out the maturity illusion — group treaties by the underwriting year they were bound and compare every cohort at the SAME 12-month development age — to prove whether the newer cohorts are genuinely worse. Premium and losses live in two awkward feeds with different grains, duplicated rows, mixed currencies, and two encodings of development age.

A category-4 windstorm is tracking toward Northern Europe and the regulator has given Hana 48 hours to certify the group's aggregate exposure to this single event — the maximum the group could pay if the worst-case footprint materialises — net of its own outward retrocession. The cat model's headline is double-counting: the same underlying risk shows up in two overlapping feeds (the per-treaty register and the facultative/account feed), and large accounts that sit inside blanket treaties get summed twice. On top of that the model treats a NULL Aegis share as zero — silently dropping the treaties where Aegis takes the whole line — and the two feeds spell the accumulation-zone codes three different ways so they won't join. Normalise the zones, fix the NULL-share convention, de-duplicate the treaty/account overlap, convert mixed currencies to USD, apply the per-zone retro cap, and hand the regulator a certified net number with the cat model's error fully decomposed.

App release 4.18 shipped a bug in the analytics SDK: for a window in March every event arrived without the collector's session id, and the growth dashboard — which counts sessions straight off those ids — flatlined at zero. The raw events are all there. Rebuild the sessions from the event log under the 30-minute inactivity standard, prove the method against an intact day the collector still has, and hand Rens the numbers the dashboard would have shown. The raw feed is raw: retry duplicates, keep-alive pings, and logged-out devices all change the answer.