What this checkpoint covers
Kavrenoth Dairy is an invented regional dairy selling doorstep milk deliveries by subscription and farm shop orders for collection. Its analyst is moving reporting into a warehouse; the questions apply the guide to designing a marketing data model, the guide to loading ad platform data into a warehouse and the tutorial on data tests for marketing tables.
Kavrenoth Dairy: the warehouse so far
orders_allholds one row per farm shop order, one per delivery made and one per month of refunds.- Spend joins to sessions on campaign name; one team calls paid social "Facebook", the other "Paid social".
- The shop's social account was opened in euros by a former agency; everything else is in pounds. The euro account also reports in its own time zone, an hour ahead of head office.
- Each night's load appends the previous day. Sign-ups credited to one Wednesday, by load: next day 63, a week later 81, two weeks later 86, three weeks later 86.
- Last month: warehouse spend £18,400; platform interfaces £18,400; invoices £22,600, including £3,680 of VAT and a £520 agency fee.
Task
- Write a decision and a reason for every question before reading on; a right decision with the wrong reason is a miss.
- Reread the guide section linked from each miss, confident misses first.
Questions
- The analyst wants to keep
orders_allbecause "everything is in one place". What is wrong, and what replaces it? - The board wants cost per new subscription by channel, comparable across deliveries and the shop. What must exist first?
- Marketing renames "Spring Doorstep" to "Doorstep Trial" and moves leaflets from Offline into a new Direct mail group. Finance wants last year's channel totals to stay as reported. How are keys and history handled?
- Every Monday the latest week's cost per sign-up looks worse, and the agency calls social "in decline". A mid-month change to social's attribution setting lands in the same report. What do you change?
- The finance director wants spend "corrected" to match the invoices, and asks why the euro account is a different size in every report and why its days never quite line up with head office's. What do you say?
- Which tests should the deliveries and spend tables carry, and which should stop the reporting tables refreshing?
- On board-pack morning the deliveries freshness test fails. The shop revenue reconciliation test has failed daily for a month, because the warehouse removes cancelled orders and the source does not. The analyst proposes switching both off. What happens instead?
Answers explained
- It mixes grains, so refund rows get summed with orders. Each process gets its own fact at its natural grainWhat one row of a table represents, such as one order, one order line, one session or one customer per day; queries and metric definitions must match it.: per order, order line, delivery and subscription, with refund status on the order. See facts at their natural grain.
- Shared dimensions and a campaign mapping table: one channel table fed by a mapping, a date table on the reporting calendar, and every platform ID, link value and system campaign field mapped to one key, added at launch, with an unmapped report. See the campaign mapping table.
- Join on IDs; date the regrouping. The rename touches only a name; the platform ID maps to the dairy's own key. Validity dates on the channel mapping rows keep last year's facts on the grouping valid then, recorded in the change log. See keys, identity and history.
- A refresh window, with settings stored beside the numbers. Conversion LagThe time between an ad interaction and the conversion it leads to, which leaves the most recent reporting days incomplete. keeps adding sign-ups to recent days, so an append-once load freezes them. Reload a trailing window measured from the snapshot, label it provisional, keep each figure's attribution setting in a dated history and mark the change as a series break. See late conversions and refresh windows.
- The warehouse holds what was spent. VAT, fees, credits and timing are explained in the spend-tracking process, not patched into loads. Convert the euro account once in the cleaned layer at a documented rate, original kept. Its days are either aligned to the reporting zone at account set-up or documented as account days, because a platform's daily figures cannot be split into head office's days afterwards. See reconciling with invoices and interfaces.
- Every kind of rule, each with a severity. A unique key at each grain catches a doubled load before DeduplicationRemoving double counts when one conversion is reported by more than one source. is needed; then required fields, accepted values, mapping, freshnessWhether a table, feed or report holds data as recent as it should; a freshness check fails when the latest date is older than the source's expected arrival. against each source's real arrival, same-weekday volume and totals against the source. Duplicate keys usually block and mapping gaps usually warn; Kavrenoth sets the severity of freshness, volume and reconciliation per table with each owner, recorded in the rules list. See write the rules each must meet.
- The owner is told; nothing is switched off. The owner gets the failing rows; its dashboards show as stale with the last pass time, so the pack waits or goes out labelled. The reconciliation test needs like compared with like, or a known failure recorded with an owner and an expiry. See living with failing tests.
Questions four and five worked through
Sign-ups for the Wednesday still rise after a week and are settled by the two-week load, so a 14-day delete-and-reload window covers them. The next-day load held 63 of an eventual 86, about 27% short; repeated in each Monday report, that is the "decline".
Warehouse and interfaces agree, so the load is complete: £18,400 + £3,680 VAT + £520 fee = £22,600, all explained in the spend tracker.
Where to go next
Misses on the first three send you back to the cleaning franchise data model exercise; on the next two, to the loading guide; on the last two, to the tests tutorial. Then move on to the mattress brand mix model review, with the advanced marketing analytics level to hand.
