A practice prompt we wrote. No company or candidate report names it, so it carries no company tag.
How to answer
Landing the feeds in one place is the easy half. The real question is underneath: which records are the same customer, and the same product, and who says so.
- Ask who reads the view and what a wrong merge costs them. A customer service rep or the customer’s own account page needs high precision, because a false merge shows someone else’s orders. A marketing audience tolerates misses. Set match thresholds per use, not once.
- Split it into two problems out loud. Products usually have a shared identifier somewhere; customers usually don’t. They resolve differently, so design them separately.
- Inventory the feeds before drawing. For each one, name the key it carries, how fresh it is, who owns it and how dirty it is. Point of sale has a transaction, a store, a card token and maybe a scanned loyalty number. E-commerce has an account, an email and addresses. Loyalty has a member number, email and phone. Suppliers have their own SKUs and, sometimes, a GTIN.
- Sketch the layers in one breath. Raw and append-only per source, then conformed and normalized, then resolved, then served.
- Spend most of your time on entity resolution. Profile keys for hub values, link exactly, block to get candidate pairs, then score, with a review band in between and precision and recall measured on labeled pairs.
- Make freshness explicit per source and per link. The view states what it knows as of when, rather than promising “real time”.
- Name owners, survivorship and a correction path. Who owns each record, which source wins each attribute, how a steward fixes a bad merge, and how that fix survives the next run.
- Close on privacy. Card numbers stay out; consent and deletion requests reach every linked record, raw layer included.
Say the first four steps in under two minutes, then ask which half they want in depth. The interviewer, not your plan, should decide where the rest of the time goes.
The trap is the confident box labeled “data lake” with arrows in and a dashboard out. Stopping there skips the customer who exists four times. Deduplicating records with misspelled names and addresses drills the scoring half on its own. Entity resolution, change data capture and owning the golden record in a customer’s systems are taught in Enterprise system design for FDEs, in Pro. That module’s first lesson, enterprise design is different, is free.
Follow-ups
What the interviewer may ask next, once your first answer is on the table.
- What is the key for a customer across the four feeds?
- Loyalty data arrives weekly, point of sale in real time. What does the view promise?
- Who can correct a wrong merge, and how?
Where answers go wrong
- Proposes a data lake and stops before entity resolution.
Answer this in two minutes
Write the answer you would say out loud. The clock starts with your first word.
Compare with the model answer
Model answer
“First, who reads this? If store staff or the app show it to the customer, a false merge is a privacy incident, so I’d tune for precision and accept duplicates; a marketing audience can take a looser threshold. Then I’d treat this as two resolution problems on one pipeline, products first, because they’re easier and merchandising feels it immediately.
Rough scale, so we agree on it: at 20M loyalty members and 20M web accounts, comparing every loyalty member with every web account is 4 × 10^14 comparisons, before point of sale adds anyone. That’s why the customer half needs blocking. I’ll sketch the layers, then check with you: do you want depth on customers or on products? I’ll assume customers, with products briefly.
Layers. Each feed lands raw and append-only with its load time: point of sale as a stream from the store systems, e-commerce by change data capture with a tool such as Debezium, loyalty as the weekly file, suppliers as files or EDI. A conformed layer normalizes: lowercased, trimmed emails; phones in E.164; standardized addresses; converted supplier units. Nothing is resolved yet, so a bad normalization can be replayed. On Databricks, these layers map onto its medallion architecture: raw is bronze, conformed and resolved are silver, and served is gold.
Products. The key is the GTIN where one exists. Around it I keep a crosswalk from each supplier’s SKU and our internal SKU to one product ID, and where a GTIN is missing a merchandiser maps it in a queue. Pack sizes are a trap: a single can and a case of cans have different GTINs, and the crosswalk records that relation rather than merging them.
Customers. Suppliers carry no customer, so it’s three feeds, and none shares a key. The answer to ‘what’s the key’ is: we mint one. Before trusting any exact key, I profile it. A loyalty number scanned at many stores in one day, or an email shared by many accounts, is a hub key: a house card the cashier scans for customers without one, or a placeholder email typed at checkout. Hub keys go on a deny list and never link records, and a shared family card links purchases to a household, not a person.
The first profiling query I’d run finds loyalty numbers scanned at several stores yesterday, busiest first:
-- loyalty numbers scanned at many stores
-- in one day: house cards, not people
select loyalty_no,
count(distinct store_id) as stores,
count(*) as scans
from pos_sale
where sold_at >= current_date - 1
and sold_at < current_date
and loyalty_no is not null
group by loyalty_no
having count(distinct store_id) > 3
order by scans desc
limit 20;Then deterministic links: a scanned loyalty number ties that sale to the member, and the same normalized email ties e-commerce to loyalty. As the arithmetic showed, scoring every remaining pair is out of reach, so I block first: records are compared only when they share a normalized phone, an email local part, or postcode plus surname prefix. I check the blocking step’s recall on labeled pairs, because a pair that is never compared can never match. Candidates are scored on name, address and phone, in the spirit of Fellegi and Sunter’s record linkage model. Above an upper threshold we link, below a lower one we don’t, and the band between goes to review. Anonymous card-only sales stay unlinked unless the payments and privacy teams approve using a processor token. Never the card number; that pulls the platform into PCI DSS scope.
Two tables carry it. One maps every source key to a golden ID and records which rule linked it. The other holds the stewards’ verdicts, which the matcher must obey.
create table customer_link (
-- 'pos', 'ecom' or 'loyalty'
source text not null,
source_key text not null,
-- stable golden id
customer_id uuid not null,
-- 'loyalty_scan', 'email_exact',
-- 'scored' or 'steward'
rule text not null,
score numeric,
primary key (source, source_key)
);
-- steward decisions, read by the matcher
create table match_assertion (
left_source text not null,
left_key text not null,
right_source text not null,
right_key text not null,
verdict text not null
check (verdict in ('same', 'different')),
decided_by text not null,
reason text not null,
decided_at timestamptz not null
default now(),
-- each pair stored one way round
check ((left_source, left_key)
< (right_source, right_key)),
-- a reversed verdict is a new row
primary key (left_source, left_key,
right_source, right_key, decided_at)
);I’d have two CRM analysts label the same pairs independently, drawn from the review band and from both sides of it, settle their disagreements into written rules, and then report precision and recall per rule. The first demo shows those numbers, not a merged profile.
Stable IDs. When two clusters merge, the older ID survives. When one splits, the larger half keeps it. Both changes go out as events, so marketing’s audiences and the order history follow them.
Freshness. Each source has its own as-of column: purchases as of minutes ago, points balance as of the last loyalty file. Deterministic links apply as events arrive; scored matching runs nightly. So the view promises that a purchase appears within minutes, but that a new customer may appear as two people until the next morning, and it says which.
Survivorship. Each attribute has a rule, owned by a steward: address and email from the most recently confirmed source, so a checkout beats an old loyalty sign-up; consent from the strictest source; product dimensions from the supplier; product names from merchandising. The served view records which source won each field, so ‘why is this my address?’ has an answer.
Owners and corrections. The CRM team owns the customer record and merchandising owns the product record; each names a steward. A steward corrects a merge by writing a different assertion with a reason. A reversed verdict is a new row, and the matcher reads the latest one for each pair, so the history stays. The matcher reads assertions as constraints while it builds clusters: ‘same’ is a forced link, and ‘different’ is a cannot-link that splits any cluster that would join the two, even through a third record. Merges and splits are logged so we can answer ‘why is this one person?’
Privacy. Consent flags travel with the customer ID. A deletion request fans out through customer_link to every source key, raw layer included: personal fields in raw are encrypted with a per-customer key, and deletion destroys the key, so replays keep working and the person is gone. Derived layers are rebuilt from there.
First release: the product crosswalk plus loyalty-to-e-commerce email links with hub keys filtered, the review queue, and precision and recall on a labeled sample. Blocking and scored matching come second.”