Articles
Monzo Analytics Engineer Take-Home: Data Modelling, Trade-Offs, and Case-Study Reasoning
Prepare for a Monzo Analytics Engineer take-home with clear data grain, duplicate and refund handling, reconciliation tests, and defensible trade-offs.
For a Monzo Analytics Engineer take-home, prepare a data model that another person can understand, reproduce and challenge. Explain what each row means, how source events become reliable reporting data, and which trade-offs you accepted. A complicated pipeline cannot compensate for an undefined metric.
Official: Monzo's May 25, 2026 hiring article describes a one-week take-home stage and a one-hour case study. It emphasizes pragmatic modelling, structured work and communicating decisions. The week is a listed submission window, not a request to spend seven full working days on the task. Confirm your invitation's deadline and expected effort. Monzo Analytics Engineering interview article
The banking-event exercise below is original practice material, not Monzo's dataset, scoring rubric or current take-home prompt.
Separate the official brief from candidate evidence
Monzo's official article places AE work across reliable data models and downstream analytical use, mentioning dbt, BigQuery and Looker. It describes the case study as a collaborative discussion of assumptions, approach, risks and impact. That supports preparing explanations alongside SQL; it does not establish a required repository layout or a universal exercise. Official AE interview article
Candidate-reported: a July 1, 2026 Lead Analytics Engineer account describes a BigQuery modelling task and inability to access its sample data. This is one candidate account. Reports from other occupations in the discussion do not independently confirm the AE task, deadline or hiring outcome. Candidate access-blocker report
We did not find two independent completed AE reports from the same recruiting cycle. This article therefore focuses on the official modelling brief and our preparation recommendations, without reconstructing a universal hiring loop.
Before doing substantial work, confirm that you can query a small sample, see the schema, and save outputs in the permitted location. If access fails, send the recruiter a concise description of the affected resource and error through your normal contact route. Preserve the blocker and avoid inventing replacement data as though it were supplied.
Define the consumer, metric and grain before writing SQL
Grain is the entity or event represented by one row. An event table can contain several rows for one purchase; a purchase table should not silently inherit that multiplicity. Declare the grain before choosing keys, joins or aggregates.
Our original brief asks for daily merchant net settled spend in a fictional banking dataset. The consumer is an analyst comparing merchant activity over time. The measure includes settled purchases and subtracts settled refunds; pending authorizations and cancellations contribute no settled spend. This is an analytical measure, not a bank balance or accounting ledger.
State currency and units: amounts are integer minor units, and reporting remains separated by currency. A value of 10,000 means £100 only for the GBP records in this fixture. Summing currencies without an explicit conversion policy produces a number with no coherent unit.
Keep event time and arrival time separate. occurred_at describes when the source event happened. ingested_at describes when your pipeline received a delivery. This exercise reports activity by occurrence date in UTC; a real brief might require a different business timezone or settlement date definition.
Work a small payment lifecycle by hand
Use this invented GBP fixture before testing the large transformation. All purchases belong to merchant M1; the refund references purchase T1. Refund R1 occurred on October 2 but arrived on October 4.
| Delivered event | Reporting meaning |
|---|---|
| E1: T1 authorized, £100, October 1 | Pending lifecycle event; contributes £0. |
| E2: T1 settled, £100, October 1 | Settled purchase; contributes £100. |
| E2 delivered again with the same event ID | Duplicate delivery; contributes no additional spend. |
| E3: T2 authorized, £60, October 1 | Pending lifecycle event; contributes £0. |
| E4: T2 cancelled, £60, October 1 | Cancellation; contributes £0. |
| E5: T3 settled, £40, October 1 | Settled purchase; contributes £40. |
| R1: separate settled refund for T1, £20 | October 2 refund; contributes −£20 once received. |
The seven deliveries contain six unique events; R1 has its own event identity and references the original purchase. Adding every positive source amount produces £480. That mixes authorization, settlement, cancellation, duplicate and refund amounts rather than measuring settled spend. The intended cumulative net settled spend after all events arrive is £140 − £20 = £120.
October 1's activity is £140. October 2's restated activity is −£20. Keeping the refund as a separate record preserves both the original purchase and the later adjustment. Replacing the purchase row with its latest refund event would erase information and can misstate daily activity.
This miniature reconciliation is useful because a reviewer can verify it without trusting your implementation. If your model cannot reproduce it, an attractive dashboard does not validate the result.
Build layers that preserve identity and meaning
Our recommended layers are raw deliveries, deduplicated events, signed settled movements, and a merchant-day aggregate. Each layer answers a different question. Raw data preserves delivery evidence; deduplication resolves repeated delivery; movements express the measure; aggregation serves the consumer.
For identical duplicate payloads, choose a deterministic representative using ingestion order and a stable delivery tie-breaker. If the same event ID carries conflicting amount or currency values, treat that as a source-quality exception. Do not silently call the later payload correct merely because it arrived later.
The following original BigQuery outline assumes those conflicting-ID checks have already passed:
WITH deduplicated AS (
SELECT *
FROM sandbox.events
QUALIFY ROW_NUMBER() OVER (
PARTITION BY event_id
ORDER BY ingested_at DESC, delivery_id DESC
) = 1
), movements AS (
SELECT event_id, merchant_id, currency,
DATE(occurred_at, 'UTC') AS activity_date,
CASE event_type
WHEN 'settled_purchase' THEN amount_minor
WHEN 'settled_refund' THEN -amount_minor
END AS net_amount_minor
FROM deduplicated
WHERE event_type IN ('settled_purchase', 'settled_refund')
)
SELECT merchant_id, currency, activity_date,
SUM(net_amount_minor) AS net_settled_spend_minor
FROM movements
GROUP BY merchant_id, currency, activity_date;
Official syntax reference: BigQuery's QUALIFY clause filters window-function results. The clause supports the deduplication shape here; it does not establish that the chosen event identity or source ordering is correct. BigQuery query syntax
Document source preconditions beside the query: positive magnitudes for purchases and refunds, stable event IDs, one currency per movement, and a valid merchant reference. The outline is a modelling example, not a query executed against Monzo or a deployed BigQuery job.
Test the business meaning as well as the schema
A unique key test can pass while the amount is wrong. Pair structural checks with independent examples. In this fixture, replaying E2 must leave cumulative spend unchanged; adding R1 must decrease it by £20; authorization and cancellation events must not change settled spend.
Official tooling reference: dbt data tests express assertions about models, including generic checks such as uniqueness, non-null values, accepted values and relationships, plus custom SQL assertions. Choose tests for the actual contract rather than importing a long checklist unchanged. dbt data tests
For the merchant-day output, test uniqueness across merchant, currency and date. Test non-null keys and permitted currencies at the relevant input layer. Check that every refund references a known purchase when that relationship is part of the supplied schema; otherwise document how unmatched refunds are handled.
Add a conservation check: summing the final daily model across the fixture should equal the deduplicated signed settled movements. This catches aggregation defects. The independent £120 calculation can also expose a wrong business definition shared by both layers.
A dimension join deserves its own check. If merchant M1 has two history rows and you join only on merchant ID, a £100 movement can become £200. Either choose the dimension version valid at event time or explicitly use a current snapshot with a unique key. Explain the reporting meaning of that choice.
Negative daily net spend is not automatically invalid: a day with refunds but no purchases can legitimately be negative under this definition. An indiscriminate “amount must be positive” assertion would reject the correct October 2 output.
Explain late arrivals and incremental trade-offs
At an October 3 data cutoff, the delayed refund is unknown and the observed cumulative net is £140. When it arrives on October 4, a model based on occurrence date restates October 2 by −£20. A model based on arrival date would instead show the adjustment on October 4. State which question each answers.
For a small take-home, a complete rebuild may be the clearest correct solution. An incremental model adds responsibility for detecting late arrivals, identifying affected reporting dates and replacing or merging the correct outputs. Extra machinery is useful only if the brief or scale warrants it.
A fixed three-day lookback is an assumption about lateness, not a guarantee. A refund arriving outside that window can be lost unless the system has a backfill or affected-key mechanism. Explain the maximum supported lateness, the reconciliation check, and how you would recover an older correction.
Similarly, pruning source partitions solely by occurrence date can miss a newly arrived historical event. An ingestion-based discovery step can identify affected occurrence dates for recomputation. Keep event-time reporting semantics distinct from the mechanics used to discover changed data.
Explain which failure mode the extra processing cost prevents: “I rebuild this small fixture for clarity; at larger scale I would discover changed dates by ingestion time and reconcile affected partitions” is reviewable. “I used incremental processing because it is best practice” leaves the risk unexplained.
Make the submission and case-study reasoning easy to review
Our recommended submission includes an entry-point README, model definitions, a small fixture, test commands and a short decision log. Follow the supplied output requirements first; these are preparation suggestions, not Monzo-mandated files.
A useful decision log records the choice, rejected alternative, consequence and revisit condition. For this case: refund records remain separate because daily chronology matters; grouping includes currency because no conversion policy exists; full rebuild is acceptable because the fixture is small; late data restates occurrence-date reporting.
Describe execution honestly. A local SQLite fixture can verify portable relational logic and expected amounts. It does not prove BigQuery permissions, dbt materialization behavior, warehouse cost or production scheduling. Separate documentation-reviewed dialect syntax from checks you actually ran. Include a small expected-output file so the reviewer can compare the reported £120 with the model result. Record the input cutoff too: before the delayed refund arrives, £140 is the correct as-of total under this fixture, rather than evidence that the model failed.
In the case-study discussion, lead with the consumer's decision and the model's contract. Demonstrate one row through the layers and one deliberately troublesome row, such as the delayed refund. Then explain what new requirement would make you change the design.
If asked to add historical merchant categories, discuss effective dating and attribution before adding a history table. If asked for a customer balance, explain why the settled-spend model lacks the complete ledger needed. A different consumer can require a different model. Revisit the grain, measure and time policy before extending the SQL.
Sources and Further Reading
- Monzo: Interviewing for Analytics Engineering, published May 25, 2026
- Candidate-reported Lead AE task and dataset-access issue, July 1, 2026
- dbt: Data Tests
- Google Cloud: BigQuery Query Syntax
Research checked October 4, 2026. Follow the current invitation's scope, access rules and deadline. All event IDs, dates, amounts, SQL and model decisions in the exercise are original examples.