Interview weight: 5–10% (a "table stakes" topic — every developer loop probes it once, senior loops probe the edge cases: cascade delete, roll-up limits, junction objects, conversion rules) · Estimated time: 3–4 sessions (~90 min each) Target: By the end, you can whiteboard the Master-Detail vs Lookup decision matrix cold, explain exactly what breaks when a lookup converts to Master-Detail (and vice versa), diagnose a cascade-delete/junction-object incident, write parent-child and child-parent SOQL without hitting the subquery-depth wall, and reason correctly about polymorphic fields, Big Objects, and lead conversion — all in 2 minutes closed notes.

M0 — THE MAP (read this first, 5–10 min)

The one idea everything hangs on: A RELATIONSHIP TYPE IS A CONTRACT ABOUT WHO OWNS WHOM

Every concept in this module — cascade delete, roll-up summaries, OWD, junction objects, conversion rules, undelete semantics, SOQL depth — is a consequence of one realization:

Master-Detail is not "a stronger Lookup." It is a different contract: the detail record has no independent existence — its security, its lifecycle, and (optionally) its aggregation all inherit from the master. A Lookup is two independent records that happen to reference each other. Every incident in this module is someone treating one contract like the other — deleting a "detail" like it was independent, querying a "lookup" like it cascades, or converting between them without reading what the conversion silently rewrites underneath.

Think of it as a landlord-tenant system:

• Master-Detail = the tenant's lease is inside the landlord's building — if the building is demolished, the tenant has nowhere to exist (cascade delete); the tenant's rent report rolls up into the building's ledger (roll-up summary); the tenant can't set independent house rules (Controlled-by-Parent OWD); the building can only have 2 such embedded tenants per unit type (max 2 MD relationships per object).
• Lookup = the tenant owns a condo across town that merely references the landlord as an emergency contact — if the landlord's building burns down, the condo is untouched (no cascade); the condo has its own locks (independent sharing); you can swap emergency contacts freely (reparenting).
• Junction objects = a marriage certificate between two independent people, each a master to the certificate — if either person "dies" (parent deleted), the certificate is voided (deleted) too. Two Master-Detail relationships on one object = many-to-many.
• Roll-up summaries = only the landlord's ledger can aggregate embedded-tenant rents — a condo's landlord can't automatically total up condos it merely knows about (Lookup has no native roll-up; you fake it with Apex/Flow/DLRS).
• Conversion = re-drawing the contract after the fact — Salesforce enforces sanity checks (a lookup can't become Master-Detail if any record has it blank; a Master-Detail can't become Lookup if a roll-up depends on it) and it has a side effect nobody reads the fine print on (OWD silently changes).
• SOQL relationship traversal = walking the family tree — child→parent (dot notation) can walk arbitrarily far up the tree; parent→child (subquery) can only look one generation down before Salesforce says "get your own query."

Why this map matters (the bridge): "Master-Detail vs Lookup — when would you use each?" is a near-universal question, and the senior differentiator is knowing the silent side effects (cascade delete, OWD flips, roll-up dependency locks) — not just reciting "MD cascades, Lookup doesn't." Every incident below is a real org where someone picked the wrong contract, converted without reading the fine print, or queried the tree wrong — and the fix is always the same five disciplines:

1. Know the contract (MD vs Lookup) before you draw the ERD — ownership, lifecycle, sharing, aggregation.
2. Read the conversion rules before converting — they have irreversible side effects (OWD, roll-up dependency).
3. Respect delete/undelete semantics — cascade is not reversible past the recycle bin, and a detail-then-master delete orphans permanently.
4. Query relationships correctly — parent→child subqueries are 1 level; child→parent dot-notation is multi-level; polymorphic fields need TYPEOF or per-type queries.
5. Know the escape valves — DLRS/Apex/Flow for Lookup roll-ups, Big Objects for scale, External Objects for Salesforce Connect.

By the end of this module, "knowing it" looks like this: given any one of the 9 problems below, you can (a) name the mechanism that failed, (b) explain the fix on a whiteboard, (c) describe the corrected schema/query from memory, and (d) say which interview question it maps to.

The incidents (choose your own adventure — recommended order)

Protocol reminder (from file 00): attempt in writing FIRST (≥2 hypotheses + 2 solution attempts), hard 45-min cap, hint ladder, then reveal, then REDO, then retrieval drill. The sealed answer sheet lives in 08b_Topic08_Data_Model_Answer_Sheet.md. You are expected to fail. The failure is the task.

INCIDENT 1 — THE DELETE THAT TOOK THE BUILDING DOWN

STAKES

A sales ops admin, cleaning up a stale Opportunity, clicks Delete. Two seconds later: 340 OpportunityLineItem records, 12 OpportunityContactRole records, and — because a well-meaning consultant built a custom Commission_Split__c object as Master-Detail off OpportunityContactRole — another 12 downstream Commission_Split records vanish with it. Finance calls: "Where did Q3's commission calculations go?" The admin swears they only deleted "one dead opportunity." It's Friday, commission run is Monday.

THE INCIDENT

Opportunity (master)
  └── OpportunityLineItem (detail, Master-Detail, standard)
  └── OpportunityContactRole (detail, Master-Detail, standard)
        └── Commission_Split__c (detail, Master-Detail, custom — added by consultant)

Admin action: Delete Opportunity (single record, list-view button)
Result: 1 Opportunity + 340 OpportunityLineItem + 12 OpportunityContactRole
        + 12 Commission_Split__c = 365 records gone in one click, no warning shown
        beyond a generic "related records will also be deleted."

THE PROBLEM

Explain exactly why deleting ONE Opportunity deleted 365 records, why the platform's warning didn't stop anyone, what the two "safety valves" actually are (recycle bin + the 200-record junction cascade cap), and design the process fix so this can't happen by accident again.

Write: (1) the cascade mechanism through the multi-level Master-Detail chain, (2) the two safety valves and their real limits, (3) the process fix.

HINT LADDER

• Hint 1 (the avenue): (1) Master-Detail cascade delete is transitive — deleting a master deletes every detail, and if that detail is itself a master to something else, the chain continues down. (2) The recycle bin is a 15-day soft safety net, not a warning system; there's also a 200-record cap on cascade-deleting junction-object children unless a setting is raised. (3) The fix is never "train the admin harder" — it's schema-level (should Commission_Split even be MD?) plus process-level (list-view delete restrictions, validation rule blocking delete on records with financial dependents).
• Hint 2 (the mechanism): (1) Every Master-Detail relationship enforces: delete the master → cascade-delete every detail, recursively. Opportunity → OpportunityLineItem (MD, standard) and Opportunity → OpportunityContactRole (MD, standard) both cascade in the same delete; OpportunityContactRole → Commission_Split__c (MD, custom) makes Commission_Split a third-generation detail — it disappears too, and nothing in the UI names it explicitly ("related records" is generic). (2) Recycle bin: all 365 land there for up to 15 days (or until 30x storage/other eviction rules), recoverable via undelete — IF nobody deletes the parent again first (Incident 6's lesson). Separately, Salesforce blocks cascade-deleting a master if it would delete more than 200 records of a junction object's children in one operation unless the "Enable cascading delete of up to 200 records" setting bumps the cap — a governor specifically aimed at this class of surprise. (3) Process fix: never let deletion of a financial/compliance object be a single unguarded click — add a validation rule or a "before delete" Flow/trigger that blocks delete when dependent Commission_Split records exist and aren't closed, and reconsider whether Commission_Split should be Master-Detail at all (a Lookup would decouple its lifecycle).
• Hint 3 (the skeleton): Cascade chain: Opportunity (delete) → OppLineItem + OppContactRole (delete, both MD) → Commission_Split__c (delete, MD off OppContactRole) = 3 generations, 365 records. Safety valves: (a) recycle bin, 15 days, only helps if you undelete the master, not just the child (see Incident 6); (b) the >200-junction-child cascade cap (raise via Setup only when justified). Fix: schema review (should this really be MD three levels deep?), a before-delete guard on financially sensitive details, and removing single-click delete access from list views for objects with this blast radius.

THE REVEAL — POSTMORTEM

What actually happened (real class of incidents — the multi-generation Master-Detail cascade; every org that nested MD three levels deep without mapping the blast radius):

The cascade mechanism: Master-Detail is a transitive ownership contract. Deleting Opportunity doesn't just delete its immediate details (OpportunityLineItem, OpportunityContactRole) — it deletes their details too, recursively, because Master-Detail cascade is not "one hop," it's "however deep the chain of MD relationships goes." The consultant who built Commission_Split__c as Master-Detail off OpportunityContactRole (rather than a Lookup) silently wired Commission_Split's entire lifecycle to Opportunity's — three generations removed, invisible on the Opportunity's own page layout.

The two safety valves (and their real limits):

1. Recycle bin: deleted records — master and every cascaded detail — sit in the recycle bin for up to 15 days (subject to storage eviction), recoverable via undelete. This is a soft net, not a warning system: nothing stops the delete from happening; it only gives you a window to reverse it, and only if you restore correctly (Incident 6).
2. The 200-record junction cascade cap: Salesforce blocks cascade-deleting a master if the operation would delete more than 200 detail records of a junction object in one shot, unless "Enable cascading delete of up to 200 records" (or the equivalent expanded setting) is turned on. This governor exists specifically because junction fan-out (many-to-many via two MD relationships) is where cascade deletes go from "a few records" to "tens of thousands."

The process fix:

1. Schema-level: ask, for every custom Master-Detail relationship, "does this child's lifecycle truly depend on the parent, or did we pick MD because we wanted a free roll-up summary?" Commission_Split__c wanted a roll-up (total commission per contact role) — that's a real reason to want MD, but it means the team accepted "deleting the Opportunity deletes the commission history" as a design decision, not an accident. If commission records must survive the Opportunity for audit/compliance, they should be Lookup with an Apex/Flow/DLRS roll-up instead.
2. Process-level: a before-delete Apex trigger or Flow on Opportunity that blocks deletion when dependent Commission_Split records exist in a non-void status ("cannot delete: 12 commission records exist — void them first"), and removing raw list-view delete access for objects with deep MD chains from profiles that don't need it.

Why the "obvious fixes" failed (the contrast):

• "Just recover from the recycle bin" → works only if the admin undeletes correctly and promptly; if anyone deletes the Opportunity a second time, or 15 days pass, the chain is gone for good (Incident 6 shows the sharper trap: deleting the detail-then-master version is unrecoverable even sooner).
• "Add a confirmation dialog" → the platform's generic "related records will also be deleted" already IS that dialog; it names no numbers and no downstream generations. A better confirmation isn't the fix — a business-rule gate is.
• "Make Commission_Split a Lookup and move on" → loses the native roll-up summary; the real fix requires either accepting the cascade as a documented business decision (with a delete-guard for financial data) or replacing the roll-up with DLRS/Flow if decoupling is required.

KNOWLEDGE EXTRACTION (interview-ready)

• "What happens when you delete a Master-Detail parent?" → Cascade delete, transitively through every generation of Master-Detail children — not just the immediate ones.
• "Is there a limit on cascade delete?" → Yes — cascade-deleting more than 200 records of a junction object's children in one operation is blocked by default; a setting can raise it, but it's a deliberate governor against silent fan-out.
• "How do you protect financially/compliance-sensitive detail records?" → Either don't model them as Master-Detail (use Lookup + an alternative roll-up mechanism), or add a before-delete guard (trigger/Flow) that blocks deletion when dependents exist.
• "Is the recycle bin a real safety net?" → Only partially — 15-day soft window, and only useful if you restore master-first correctly (see Incident 6); it is not a substitute for a delete guard.

THE REDO

From memory: the transitive cascade mechanism through 3 generations, the two safety valves with their exact limits, and the two-part process fix.

RETRIEVAL DRILL

1. Does Master-Detail cascade delete stop at one generation?
2. What's the exact governor limit on cascade-deleting junction-object children?
3. What setting raises that cap, and why is it off by default?
4. What are the two real limitations of the recycle bin as a safety net?
5. Name two ways to protect a financially sensitive Master-Detail child from accidental cascade.

INTERVIEW MAPPING

"What happens if you delete the parent of a Master-Detail relationship?" is a standard opener; the transitive multi-generation cascade + the 200-record junction cap is the senior-level follow-up that separates rote answers from real production experience.

INCIDENT 2 — THE ROLL-UP THAT WENT DARK

STAKES

For a year, Account.Total_Active_Contracts__c — a roll-up summary counting related Contract__c records — has powered the renewal dashboard executives check every Monday. Last sprint, someone "cleaned up the data model" by converting Contract__c's relationship to Account from Master-Detail to Lookup, because a business rule needed contracts reparented between accounts during a corporate restructuring. This week: the roll-up field is frozen at last quarter's number on every Account, dashboards are silently wrong, and nobody noticed for eleven days because the field still exists and still shows a number.

THE INCIDENT

Before:  Contract__c --[Master-Detail]--> Account
         Account.Total_Active_Contracts__c = ROLLUP(COUNT, Contract__c, Status = 'Active')

Change request: "Contracts need to move between Accounts during restructuring"
Consultant's fix: converted the Master-Detail to a Lookup relationship.

Result: conversion succeeded (no error shown at conversion time — or so the
        consultant claims); Total_Active_Contracts__c still displays on the
        page layout, still has a value, but it never changes again.

THE PROBLEM

Roll-up summary fields only work on the master side of a Master-Detail relationship — so how did the field keep displaying a number after the conversion to Lookup, and why did nobody get an error? State the actual conversion rule that should have fired, the three legitimate options once reparenting is required, and the corrected design.

Write: (1) what really happened at conversion time (the rule, and why it may not have blocked as expected), (2) the three legitimate paths forward, (3) the corrected design.

HINT LADDER

• Hint 1 (the avenue): (1) The real conversion rule: Master-Detail can only convert to Lookup if no roll-up summary field depends on that relationship — normally this blocks the conversion outright. Reconcile that with what happened: either the roll-up was deleted first ("cleaned up the data model" probably included deleting/deactivating the roll-up believing it was redundant), or the roll-up was on a different object than assumed. (2) Once a roll-up is gone, its field doesn't disappear — it freezes at its last calculated value forever, silently. (3) The three paths: keep Master-Detail and solve reparenting a different way, or move to Lookup and replace the roll-up with Apex/Flow/DLRS, or keep both objects but restructure the relationship being reparented.
• Hint 2 (the mechanism): (1) Salesforce will not let you convert Master-Detail → Lookup while a roll-up summary field references that relationship — the platform's Setup UI throws an explicit error identifying the roll-up field. For the conversion to have "succeeded," the roll-up field must have been deleted (or its source relationship changed) as part of the same cleanup — at which point the formula/rollup field silently becomes a static frozen value (it does not auto-delete; it just never recalculates again, and nothing in the UI flags it as stale). (2) The three legitimate paths: (a) keep Master-Detail, and solve reparenting via a documented "delete and recreate the Contract under the new Account" business process (loses history) — usually unacceptable; (b) convert to Lookup and rebuild the aggregate manually — Apex trigger (on Contract insert/update/delete, recalculate the Account's count) or a Flow (record-triggered, "update aggregate on change") or DLRS (Declarative Lookup Rollup Summaries) — the open-source AppExchange-listed tool purpose-built for exactly this gap, and citing it by name is a strong signal in interviews; (c) redesign: introduce a junction/join object so Account keeps a Master-Detail to something stable while Contract's reparenting happens on a different relationship. (3) Corrected: Lookup relationship (as required for reparenting) + DLRS (or an Apex trigger) recalculating Total_Active_Contracts__c on every relevant Contract DML, with a one-time backfill batch job to fix the eleven days of drift.
• Hint 3 (the skeleton): Conversion rule: MD→Lookup blocked while a roll-up summary depends on it; converting anyway requires removing the roll-up first, and the exposed field then freezes silently — no auto-delete, no "stale" flag. Fix: Lookup (for reparenting) + DLRS/Apex-trigger/Flow replacement aggregate + backfill batch to correct the frozen 11-day gap + a dashboard sanity check (compare against a live SOQL aggregate periodically).

THE REVEAL — POSTMORTEM

What actually happened (real class of incidents — the "we converted MD to Lookup and the roll-up just stopped" ticket; the Lookup-roll-up gap is one of the most-cited Master-Detail vs Lookup trick questions):

The conversion rule, precisely: Salesforce blocks converting a Master-Detail relationship to Lookup if any roll-up summary field depends on that relationship — the Setup wizard throws an explicit error naming the field. For "conversion succeeded, no error shown," the sequence was actually: someone deleted (or repointed) the roll-up summary field first, believing it was obsolete or redundant, then the MD→Lookup conversion went through cleanly because the blocking dependency no longer existed. The field Total_Active_Contracts__c that survives on the page layout after that is not a roll-up anymore in any functional sense — it is a plain number field frozen at its last computed value, and Salesforce gives zero UI signal that it is now dead. That's the eleven silent days.

The three legitimate paths once reparenting is genuinely required:

1. Don't convert — solve reparenting without Lookup. If the business truly needs "move this Contract to a different Account," and Master-Detail forbids reparenting by default, check whether the org has "Allow reparenting" enabled for this Master-Detail relationship (a per-relationship setting that exists precisely for this need) before reaching for a full relationship-type conversion.
2. Convert to Lookup and replace the roll-up with a live equivalent. Options in order of maintainability: DLRS (Declarative Lookup Rollup Summaries — a well-known, config-driven AppExchange tool that recalculates Lookup-side aggregates without custom code, and mentioning it by name is the "bonus points" answer), a record-triggered Flow (simpler logic, admin-maintainable), or an Apex trigger (best for complex aggregation logic or high volume). All three do the same job the native roll-up used to do — for free, on Master-Detail only.
3. Restructure the relationship instead. If contracts move between accounts rarely and in bulk (e.g., corporate restructuring events, not everyday CRUD), a scheduled/batch reassignment process that updates the Account lookup on Contract in bulk (while keeping the roll-up relationship elsewhere, or accepting a recompute batch after each restructuring event) can be cheaper than permanently giving up the native roll-up.

Why the "obvious fixes" failed (the contrast):

• "Just convert back to Master-Detail" → doesn't solve reparenting, which was the actual business requirement; also MD→Lookup was already a one-way trip in this case since data has since changed (parent-of-record assumptions may already be violated).
• "The field still shows a number, so it must still be working" → the single most dangerous assumption in this incident; a frozen static value looks identical to a live aggregate until someone checks the math.
• "Just re-add the roll-up field" → roll-up summary fields require Master-Detail; you cannot add one back on a Lookup relationship — this is exactly the asymmetry the incident is testing.

KNOWLEDGE EXTRACTION (interview-ready)

• "Can you have a roll-up summary on a Lookup relationship?" → No — roll-up summary fields only work on the master side of Master-Detail. On Lookup, you fake it with an Apex trigger, a record-triggered Flow, or DLRS (mention DLRS for the bonus point).
• "Can you convert Master-Detail to Lookup freely?" → No — blocked if any roll-up summary field depends on the relationship; you must remove the roll-up first, and it then becomes a frozen static value, not an error, not a deletion.
• "Can you convert Lookup to Master-Detail freely?" → No — only if the lookup field is populated on 100% of existing records (Master-Detail cannot be null).
• "What's DLRS and why would you mention it?" → Declarative Lookup Rollup Summaries — an open-source, config-driven managed package that computes Lookup-side aggregates without Apex; citing it signals real production experience beyond the textbook answer.

THE REDO

From memory: the exact MD→Lookup conversion rule and its failure mode, the three legitimate paths, and the DLRS/Apex/Flow trio as the Lookup roll-up workaround.

RETRIEVAL DRILL

1. What blocks a Master-Detail → Lookup conversion?
2. What happens to a roll-up summary field's displayed value after its source relationship converts away and the field itself is deleted vs frozen?
3. Name the three Lookup-side roll-up workarounds.
4. What's the per-relationship setting that can solve reparenting without a full conversion?
5. What blocks a Lookup → Master-Detail conversion?

INTERVIEW MAPPING

"Roll-up summary on Lookup — possible?" is a classic trick question; this incident is its production consequence — the silent freeze, not just the missing feature — which is what separates a memorized fact from an understood mechanism.

INCIDENT 3 — THE QUERY THAT COULDN'T SEE ITS GRANDCHILDREN

STAKES

A dashboard component needs, for each Account, its Opportunities, and for each Opportunity, its OpportunityLineItems, in one query — "so the UI can render a nested table without N+1 calls." A developer writes a triple-nested subquery. It compiles fine in the Developer Console's autocomplete... until they run it: MALFORMED_QUERY: Semi join sub-select's inner query has too many levels. Demo to the VP is in three hours; the dev has burned one already trying every variation of nested parentheses.

THE INCIDENT

SELECT Id, Name,
  (SELECT Id, Name,
     (SELECT Id, Product2.Name, Quantity FROM OpportunityLineItems)
   FROM Opportunities)
FROM Account

THE PROBLEM

Name the exact rule this violates, explain why it exists (what would the platform have to do differently to support it), and design two working alternatives — one using a restructured query shape, one using a different access pattern entirely (client-side composition).

Write: (1) the rule + why, (2) alternative 1 (query restructuring), (3) alternative 2 (composition pattern) — and when you'd pick each.

HINT LADDER

• Hint 1 (the avenue): (1) Parent→child relationship subqueries in SOQL are limited to one level of nesting — you cannot subquery a subquery's children. (2) Child→parent traversal (dot notation) has no such 1-level limit — it can walk multiple generations up. (3) Alternatives: query from the middle object instead of the top, or issue two separate queries and stitch client-side (or in Apex) by parent Id.
• Hint 2 (the mechanism): (1) The rule is literally enforced by the query parser: one SELECT ... FROM ChildRelationship subquery per parent level is allowed; a subquery inside that subquery throws MALFORMED_QUERY. This is a query-planning limit — the platform builds a single semi-join structure for the outer+one-inner-level pair; supporting arbitrary depth would mean unbounded join complexity in one query plan, which the multi-tenant query optimizer doesn't allow. Compare: child→parent dot notation (OpportunityLineItem.Opportunity.Account.Name) has no equivalent depth cap up to 5 levels of standard relationships (or up to the object's relationship depth for custom, with its own field-path limits) — because it's a straight join chain the platform resolves top-down, not a nested semi-join tree. (2) Alternative 1: query from OpportunityLineItem instead of Account — SELECT Id, Quantity, Product2.Name, Opportunity.Name, Opportunity.Account.Name FROM OpportunityLineItem WHERE Opportunity.AccountId IN :accountIds — flattens the "grandchild" data using child→parent dot-notation instead of nesting subqueries, giving you every field you need in one flat result set the UI can group in Apex/JS. Alternative 2: two queries + client-side stitch — query Accounts with one level of Opportunities subquery, then a second query for all OpportunityLineItems WHERE OpportunityId IN :oppIds, and assemble the nested structure in Apex (a Map<Id, List<OpportunityLineItem>>) before serializing to the LWC. (3) Pick alternative 1 when the leaf-level data is what you ultimately render (flat table, easy grouping); pick alternative 2 when you genuinely need the three-level nested shape server-side and the record counts are large enough that a flattened join would be wastefully wide.
• Hint 3 (the skeleton): Rule: parent→child subquery nesting is capped at 1 level; child→parent dot notation is not. Fix 1: query the lowest-level object with dot-notation up (FROM OpportunityLineItem ... Opportunity.Account.Name). Fix 2: two flat queries (Account+Opportunities, then OpportunityLineItems by parent Id set) composed in Apex into nested wrapper objects for the UI.

THE REVEAL — POSTMORTEM

What actually happened (real class of incidents — the "MALFORMED_QUERY: too many levels" error that hits every dev who tries a 3-level nested relationship query for the first time):

The rule and why it exists: SOQL parent-to-child relationship queries (subqueries in the SELECT clause using a child relationship name) are capped at one level of nesting — you can query an Account's Opportunities, or an Opportunity's OpportunityLineItems, but not an Account's Opportunities' OpportunityLineItems in a single nested subquery. This isn't an arbitrary number; each subquery compiles to a semi-join the query planner has to fold into the outer query's execution plan, and Salesforce's multi-tenant architecture bounds the planner to one level of that folding to keep query cost predictable across every tenant sharing the infrastructure. Child-to-parent traversal is architecturally different — Opportunity.Account.Name is a straight foreign-key walk, resolved as ordinary joins, and Salesforce allows walking that chain multiple levels (5 for standard relationships is the commonly cited practical ceiling, more for custom relationship chains within field limits) because it doesn't multiply the result-set shape the way nested subqueries do.

Alternative 1 — restructure by querying from the lowest object:

SELECT Id, Quantity, UnitPrice, Product2.Name,
       Opportunity.Name, Opportunity.StageName,
       Opportunity.Account.Name, Opportunity.Account.Id
FROM OpportunityLineItem
WHERE Opportunity.AccountId IN :accountIds

This returns one flat row per line item with every ancestor field attached via dot notation — no subquery depth issue at all, because there's no subquery. The Apex/LWC layer groups the flat rows into the nested UI shape it needs.

Alternative 2 — two queries, composed in Apex:

List<Account> accts = [SELECT Id, Name, (SELECT Id, Name FROM Opportunities) FROM Account WHERE Id IN :accountIds];
Set<Id> oppIds = new Set<Id>();
for (Account a : accts) for (Opportunity o : a.Opportunities) oppIds.add(o.Id);
Map<Id, List<OpportunityLineItem>> lineItemsByOpp = new Map<Id, List<OpportunityLineItem>>();
for (OpportunityLineItem li : [SELECT Id, OpportunityId, Quantity, Product2.Name FROM OpportunityLineItem WHERE OpportunityId IN :oppIds]) {
    lineItemsByOpp.putIfAbsent(li.OpportunityId, new List<OpportunityLineItem>());
    lineItemsByOpp.get(li.OpportunityId).add(li);
}
// stitch lineItemsByOpp into each Account's Opportunities before returning to LWC

Two queries, well within limits (100 SOQL synchronous), composed into the nested wrapper the front end wants.

Why the "obvious fixes" failed (the contrast):

• "Add more parentheses / reorder the nesting" → the parser error isn't a syntax mistake, it's a hard architectural cap; no amount of bracket-juggling gets past it.
• "Use SOSL instead" → SOSL searches across objects but doesn't solve relationship-shaped aggregation any better; it's the wrong tool for a structured parent-child fetch.
• "Just make three separate round trips from the LWC" → works but is the N+1 pattern the dev was trying to avoid in the first place; composing in Apex (2 queries) gets the same shape in one server round trip.

KNOWLEDGE EXTRACTION (interview-ready)

• "How deep can parent-to-child SOQL subqueries go?" → One level. You cannot nest a subquery inside a subquery.
• "How deep can child-to-parent dot notation go?" → Multiple levels (commonly cited: up to 5 for standard relationship chains) — it's a join walk, not a semi-join nest, so the platform allows it.
• "How do you get 'grandchild' data in one query?" → Query from the lowest-level object and dot-notation upward, or issue two queries and compose in Apex.
• "Semi-join / anti-join?" → WHERE Id IN (SELECT ... ) (semi-join) and WHERE Id NOT IN (SELECT ...) (anti-join) are separate SOQL patterns from parent-child subqueries — they filter by existence in a related object, not fetch nested data, and don't share the 1-level cap.

THE REDO

From memory: the exact subquery depth rule, why it exists (semi-join planning cost), and both working alternatives with when to pick each.

RETRIEVAL DRILL

1. How many levels can a parent→child SOQL subquery nest?
2. Why does child→parent dot notation not have the same cap?
3. Give the flattened-query alternative for Account→Opportunity→OpportunityLineItem.
4. Give the two-query Apex-composition alternative.
5. What's the difference between a semi-join and this subquery-nesting limit?

INTERVIEW MAPPING

A live-coding or whiteboard SOQL exercise almost always includes one relationship query; asking you to fetch three levels of nested data is a deliberate trap for this exact limit — reciting the fix cold (flatten + dot-notation, or two queries) is the pass signal.

INCIDENT 4 — THE TASK THAT POINTED AT THE WRONG TYPE

STAKES

A "recent activity" component queries Task records and displays WhatId as a clickable link to "the related record." For Accounts and Opportunities it works. For Cases, it's been silently rendering broken links for two months — support agents assumed it was "just a UI bug" and stopped trusting the activity timeline entirely, missing follow-ups. The root cause: WhatId is polymorphic, and the component's Apex controller queried it like an ordinary lookup.

THE INCIDENT

// Controller method — "get related record name for display"
List<Task> tasks = [SELECT Id, Subject, WhatId, What.Name FROM Task WHERE OwnerId = :userId];
// What.Name resolves fine for Account/Opportunity/Contact...
// but Case has no "Name" field — What.Name silently returns null for Case-related tasks,
// and the link-building logic (WhatId + a hardcoded '/lightning/r/Account/' + WhatId)
// sends users to a broken Account URL for a Case Id.

THE PROBLEM

Explain why What.Name returns null for some records and not others, what a polymorphic relationship actually is in Salesforce's schema (WhatId, WhoId, OwnerId), the correct way to query and disambiguate it (TYPEOF, or the split-query alternative), and the corrected link-building logic.

Write: (1) why What.Name breaks specifically for Case, (2) the polymorphic-field query mechanics, (3) the corrected design.

HINT LADDER

• Hint 1 (the avenue): (1) WhatId on Task/Event is a polymorphic lookup — it can point to many different sObject types (Account, Opportunity, Case, Campaign, custom objects...), and each type exposes different fields; Name isn't universal (Case has CaseNumber, not Name). (2) SOQL offers TYPEOF specifically to branch field selection by the actual referenced type inside one query. (3) The corrected logic must check WhatId's object type (via Id.getSObjectType() in Apex, or TYPEOF in SOQL) before deciding both which field to display and which URL prefix to build.
• Hint 2 (the mechanism): (1) Polymorphic fields in Salesforce: WhatId (Task/Event — "what is this related to," typically non-human objects: Account, Opportunity, Case, Campaign, custom), WhoId (Task/Event — "who," Lead or Contact), and standard OwnerId/CreatedById (User or Queue for some objects) are all polymorphic — one field, many possible target types, and the schema does not guarantee a common field set across those types (Account has Name; Case does not — it has CaseNumber and Subject). Querying What.Name blindly works only for the subset of types that happen to have a Name field; for Case it silently resolves to null rather than erroring, which is exactly why it went unnoticed for two months (no exception, just wrong data). (2) The disambiguated query uses TYPEOF: SELECT Id, Subject, WhatId, TYPEOF What WHEN Account THEN Name WHEN Case THEN CaseNumber WHEN Opportunity THEN Name ELSE Id END FROM Task. Alternatively, in Apex, check the runtime type: String sObjType = task.WhatId.getSObjectType().getDescribe().getName(); and branch display/link logic on that string — the split-query alternative is to query Tasks first, collect WhatIds grouped by prefix/type, then run one targeted query per object type to fetch display-appropriate fields. (3) URL building must use the actual object API name, not a hardcoded /Account/ prefix — '/lightning/r/' + sObjType + '/' + task.WhatId + '/view'.
• Hint 3 (the skeleton): Fix: SELECT Id, Subject, WhatId, TYPEOF What WHEN Case THEN CaseNumber, Status ELSE Name END FROM Task, plus Apex-side getSObjectType() to build the correct Lightning URL prefix dynamically instead of hardcoding /Account/. Root lesson: a polymorphic field is not "a lookup with extra steps" — it has no guaranteed common schema across its possible targets, so any code touching it must branch on type.

THE REVEAL — POSTMORTEM

What actually happened (real class of incidents — the "WhatId displays wrong/broken for Case" bug that hits every activity-timeline custom component eventually):

Why What.Name breaks specifically for Case: WhatId on Task and Event is a genuinely polymorphic relationship — it can reference Account, Opportunity, Campaign, Case, or a custom object, among others. Salesforce does not require these target types to share a common field surface. Account and Opportunity both have Name; Case does not (it identifies itself via CaseNumber). When you write What.Name in SOQL against a Task whose WhatId points to a Case, Salesforce resolves it as best it can — for a field that doesn't exist on the actual referenced type, it returns null rather than throwing a query error, because the query itself is syntactically valid (it just happens to be semantically wrong for that record's actual type). That silent-null behavior — not an exception — is exactly why this shipped and stayed broken for two months undetected: nothing failed loudly.

The correct query mechanics:

1. TYPEOF — the SOQL construct built for exactly this: SELECT Id, Subject, WhatId, TYPEOF What WHEN Account THEN Name WHEN Case THEN CaseNumber, Status WHEN Opportunity THEN Name, StageName ELSE Id END FROM Task. This lets one query return the right fields per actual referenced type, with an ELSE catch-all for types you didn't enumerate.
2. Apex runtime type check — task.WhatId.getSObjectType().getDescribe().getName() gives the real object API name at runtime; branch both the display-field logic and the URL-building logic on it. This is necessary anyway for building a correct Lightning record URL, since the URL's object segment must match the actual type, not an assumed one.
3. Split-query alternative — when the downstream logic per type is heavy enough that a single TYPEOF query gets unwieldy, query Tasks first, bucket the WhatIds by resolved type, then issue one clean per-type query (SELECT ... FROM Case WHERE Id IN :caseWhatIds) to get full, type-appropriate field sets.

The corrected link-building logic: never hardcode the object segment of a Lightning URL. Always derive it from the polymorphic field's actual runtime type: '/lightning/r/' + actualObjectApiName + '/' + task.WhatId + '/view'.

Why the "obvious fixes" failed (the contrast):

• "Just add Case.Name to the org" → Case has no Name field by design (it's not a nameable record in the standard sense); you cannot bolt one on without a formula field hack that still requires knowing the type to apply.
• "Catch the null and show 'Unknown'" → hides the bug instead of fixing the display; the link would still be broken for Case even if the label read correctly, because the hardcoded /Account/ prefix is the deeper bug.
• "Query WhatId as if it's always Account" → the actual bug in production; the fix isn't a special case for Case, it's recognizing that every consumer of WhatId/WhoId must branch on type, not just the one type someone happened to test.

KNOWLEDGE EXTRACTION (interview-ready)

• "What is a polymorphic relationship field? Give examples." → A lookup that can reference more than one sObject type. Standard examples: WhatId (Task/Event → Account/Opportunity/Case/Campaign/custom), WhoId (Task/Event → Lead/Contact), OwnerId (User or Queue, depending on object).
• "How do you query a polymorphic field type-safely?" → SOQL TYPEOF ... WHEN ... THEN ... ELSE ... END, or Apex Id.getSObjectType() and branch logic per type, or split into per-type queries.
• "Why did What.Name return null instead of erroring for Case?" → The query is syntactically valid; the referenced type simply lacks that field, so it resolves to null per-record rather than failing the whole query.
• "Any polymorphic-field gotchas in reports/list views?" → Standard reports generally can't filter/group cleanly across a polymorphic field's differing target types without type-specific handling; the same TYPEOF-style branching discipline applies wherever the field is consumed.

THE REDO

From memory: why What.Name silently nulls for Case, the TYPEOF syntax, the Apex runtime-type-check alternative, and the corrected URL-building rule.

RETRIEVAL DRILL

1. Name three polymorphic fields on standard objects.
2. Why does What.Name not throw an error for a Case-related Task?
3. Write the TYPEOF skeleton for Task.What across Account/Case/Opportunity.
4. What Apex method gives you a record's actual runtime sObject type from an Id?
5. What's the split-query alternative, and when would you prefer it over TYPEOF?

INTERVIEW MAPPING

Polymorphic fields are a favorite "do you actually understand the schema, or just the UI" probe — asking you to explain WhatId/WhoId and demonstrate TYPEOF is common in SOQL-focused rounds.

INCIDENT 5 — THE LEAD THAT CLONED ITSELF

STAKES

Marketing runs a campaign; 4,000 Leads come in over two weeks. Sales converts them steadily. By week three, data ops flags 1,100 duplicate Accounts — same company name, same domain, created minutes apart. The VP of Sales wants to know why "the exact same customer" now has two Account records, two sets of Contacts, and orphaned Opportunities split across both. The lead conversion process — supposedly Salesforce's built-in, "safe" mechanism — is the prime suspect.

THE INCIDENT

Lead conversion (standard "Convert" button/API):
  Lead.Company = "Acme Corp"  →  creates a NEW Account "Acme Corp"
    (no existing-Account match found/attempted by the rep)
  Lead.FirstName/LastName/Email  →  creates a NEW Contact under that Account
  Lead (with Opportunity checkbox checked)  →  creates a NEW Opportunity

Two different reps, two different days, convert two different Leads that both
say "Acme Corp" — because nobody matched against the EXISTING "Acme Corp"
Account before converting, and standard conversion does not fuzzy-match by default.

THE PROBLEM

Explain exactly what standard Lead conversion creates and under what conditions it reuses an existing Account vs creates a new one, why "duplicate rules" alone didn't stop this, and design the corrected conversion process (matching + mapping + guardrails).

Write: (1) what conversion creates and its default matching behavior, (2) why duplicate rules didn't prevent it, (3) the corrected process.

HINT LADDER

• Hint 1 (the avenue): (1) Standard Lead conversion, by default, creates a new Account/Contact unless the converting user explicitly picks an existing Account/Contact in the conversion screen — it does not automatically fuzzy-match "Acme Corp" against an existing Account named "Acme Corp" unless duplicate rules with "Alert" + a manual match step are configured and the rep actually acts on the alert. (2) Duplicate rules can warn, but a rule set to "Allow" (with alert) doesn't block anything, and a rule scoped only to Account (not surfaced during Lead conversion's Account-creation step) may not even fire there. (3) The fix is process + configuration: enforce duplicate-blocking rules specifically on the conversion path, and/or require explicit Account search before every conversion.
• Hint 2 (the mechanism): (1) The Lead Convert action offers three choices at conversion time: create a new Account/Contact, or attach to an existing Account/Contact selected via a lookup/search in the conversion UI. If the rep skips the search (or the org's conversion UI doesn't prominently surface it) and just clicks "Convert," a brand-new Account is created every time, regardless of whether one with the same name already exists — Salesforce's conversion mechanism has no built-in fuzzy matching of its own. (2) Duplicate rules (Setup → Duplicate Rules, paired with Matching Rules) CAN run during Lead conversion IF configured to check the Account object and set to Block (or Alert-and-require-acknowledgment) — but many orgs configure duplicate rules as "Alert only" (non-blocking) for Leads, and may not have equivalent Account-side matching rules active for the conversion-created Account, especially if matching rules only key off exact-match fields (a "Acme Corp" vs "Acme Corporation" won't fuzzy match without a similarity-based matching rule). (3) Fix: configure a blocking Duplicate Rule on Account (fuzzy matching rule on Name + Website/Domain), require reps to search-and-select an existing Account during conversion when a match exists, and consider Apex-based conversion (Database.LeadConvert in a custom conversion flow) that runs an explicit pre-conversion Account search and blocks/redirects automatically instead of relying on rep diligence.
• Hint 3 (the skeleton): Default conversion = new Account/Contact/Opportunity unless the rep explicitly attaches to existing records via the conversion screen's search. Duplicate rules only help if scoped to Account, set to Block (not just Alert), with a fuzzy matching rule (not exact-name-only). Corrected process: mandatory Account search step in the conversion UI (or a custom LWC conversion wizard), a blocking duplicate rule with domain/name fuzzy matching, and a scheduled batch job to detect and merge the 1,100 existing duplicates (Database.merge or a dedupe tool).

THE REVEAL — POSTMORTEM

What actually happened (real class of incidents — the "lead conversion creates duplicate accounts" ticket, one of the most common data-quality incidents in any Sales Cloud org running a lead-gen funnel):

What conversion actually creates, and the default matching behavior: Standard Lead conversion (via the Convert button, the Lightning conversion flow, or Database.LeadConvert in Apex) creates, from one Lead: an Account (from Company and mapped address/industry fields), a Contact (from name/email/phone, attached to that Account), and optionally an Opportunity (if the "Create Opportunity" option is checked). Critically, the conversion screen lets the user search for and select an existing Account/Contact to attach to instead of creating new ones — but if that step is skipped (which nothing forces), a brand-new Account is created every single time, with zero automatic similarity check against existing company names. "Acme Corp" created twice by two different reps on two different days is not a bug in the platform — it's the platform doing exactly what it was told (or not told) to do.

Why duplicate rules didn't stop this: Duplicate Rules pair with Matching Rules to detect potential duplicates on create/edit. Two gaps commonly allow this incident: (1) the matching rule is configured for exact matching on Name (or wasn't extended to check Website/domain), so "Acme Corp" vs "Acme Corporation" (or a slightly different legal entity string from the Lead's Company field vs the existing Account's Name) doesn't match; (2) the duplicate rule's action is set to Alert (shows a warning, doesn't block) rather than Block — and during a Lead-conversion-generated Account creation, an alert that nobody is actively watching for (it's a background system action from the rep's point of view, "just clicking Convert") gets silently dismissed or never surfaces prominently in the conversion UI flow.

The corrected process:

1. Blocking duplicate rule on Account, backed by a fuzzy matching rule (name similarity + Website/domain match, not just exact string equality) — this is the single highest-leverage fix.
2. Mandatory existing-Account search as a required step before conversion — either via training/process discipline, or better, a custom conversion flow (LWC + Database.LeadConvert in Apex) that runs the search automatically and forces the rep to confirm "no match" before creating new, removing reliance on rep diligence entirely.
3. Cleanup: a scheduled batch job (or a run of Salesforce's built-in "Merge Duplicates" / a dedupe managed package) to detect and merge the 1,100 existing duplicate Accounts, reparenting their Contacts/Opportunities via Database.merge(), which Salesforce handles natively for Account/Contact/Lead merges (up to 3 records per merge operation).

Why the "obvious fixes" failed (the contrast):

• "Just tell reps to search before converting" → training doesn't scale against 4,000 leads and rep urgency; the fix has to be structural (blocking rule or forced-search UI), not behavioral.
• "Turn on duplicate rules" → already on, set to Alert — the fix is changing the action (Block) and the matching rule (fuzzy, not exact), not just "turning them on."
• "Delete the duplicate Accounts" → deletes Contacts/Opportunities attached to them too (Master-Detail/lookup cascade concerns re-enter here) — the correct operation is merge, which preserves and reparents related records, not delete.

KNOWLEDGE EXTRACTION (interview-ready)

• "What does Lead conversion create?" → An Account, a Contact, and optionally an Opportunity (if the checkbox is set) — with the option to attach to existing Account/Contact instead of creating new ones.
• "Does Lead conversion prevent duplicate Accounts automatically?" → No — zero built-in fuzzy matching; duplicate prevention depends entirely on configured Duplicate/Matching Rules (and their Block vs Alert setting) or a custom conversion process.
• "How do you fix existing duplicate Accounts safely?" → Database.merge() (or the Merge Duplicates UI) — reparents related Contacts/Opportunities/Cases automatically, unlike delete.
• "Field mapping in Lead conversion — how does it work?" → Standard field mapping (Lead→Account/Contact/Opportunity) is configurable in Setup ("Map Lead Fields"); custom Lead fields need explicit mapping or they're lost on conversion.

THE REDO

From memory: what standard conversion creates, why duplicate rules as configured didn't block it, and the three-part corrected process (blocking fuzzy rule + forced search + merge cleanup).

RETRIEVAL DRILL

1. What three records can standard Lead conversion create?
2. Does conversion auto-match to an existing Account by default?
3. Why did "Alert" duplicate rules fail to stop this?
4. What's the correct operation to fix existing duplicate Accounts, and why not delete?
5. How many records can Database.merge() combine at once?

INTERVIEW MAPPING

"Walk me through what happens when you convert a Lead" is a standard Sales Cloud data-model question; the duplicate-Account failure mode is the senior follow-up testing whether you know conversion has no built-in dedupe intelligence.

INCIDENT 6 — THE RECYCLE BIN THAT COULDN'T GIVE IT BACK

STAKES

An admin accidentally deletes a custom Invoice_Line__c (a Master-Detail detail record under Invoice__c). Realizing the mistake, they go to the recycle bin, find the Invoice_Line, and — before restoring it — decide to "clean up" by also deleting the now-empty-looking parent Invoice__c (which still had other unrelated line items, but the admin didn't check). When they try to restore the original Invoice_Line, undelete fails silently or restores it "orphaned" with no way to properly reattach — finance's month-end close is now missing a real invoice line with no clean recovery path.

THE INCIDENT

Step 1: Delete Invoice_Line__c (detail, MD to Invoice__c) → lands in recycle bin, master untouched.
Step 2: Admin, mistakenly believing the Invoice is now orphaned/empty, deletes Invoice__c (master)
        → Invoice__c and ALL its OTHER remaining detail records cascade into the recycle bin too.
Step 3: Admin tries to undelete the original Invoice_Line__c from Step 1.
Result: the restore either fails (parent doesn't exist in an undeleted state) or succeeds
        into a broken/orphaned state, because its master was deleted AFTER it —
        the detail record deleted first is not automatically re-linked to a
        later-deleted-then-restored master.

THE PROBLEM

Explain the exact ordering rule that makes this unrecoverable (why "delete detail, then delete master" breaks undelete for the detail), what the correct recovery sequence would have been, and the preventive design (a check before any parent delete).

Write: (1) the ordering rule and why it breaks, (2) the correct recovery sequence if caught in time, (3) the preventive check.

HINT LADDER

• Hint 1 (the avenue): (1) Master-Detail detail records depend on their master even in the recycle bin conceptually — a detail deleted before its master can only be safely restored while the master is still live; once the master is also deleted, restoring the master doesn't automatically "reconnect" to a detail that was already independently in the bin. (2) The correct sequence, if both are in the bin, is to undelete the master first, then the detail — order matters, and even then, a detail deleted before the master may not be guaranteed to relink cleanly, depending on how the specific deletion sequence happened. (3) Prevention is a before-delete check on the master: are there any related detail records — active OR in the recycle bin — before allowing/warning on parent deletion.
• Hint 2 (the mechanism): (1) Master-Detail's ownership model means a detail record's continued valid existence is contingent on its master. When Invoice_Line__c is deleted while Invoice__c is still active, the recycle-bin copy of the line item "remembers" its parent Id, and undelete works fine in isolation. But once Invoice__c itself is subsequently deleted (cascading its remaining live details into the bin too), the platform's undelete semantics for Master-Detail generally require restoring the master before its details, and a detail that was deleted independently before the master's own cascade event sits outside that specific cascade group — restoring the master does not retroactively pull back details that were deleted in an earlier, separate operation; that detail's restore either errors on a missing valid parent state during the restore window or leaves it in a fragile state. The safe, documented practice for recovering a Master-Detail branch is: restore the master first, then restore its details, and never assume an independently-deleted detail from an earlier operation will cleanly reattach after a master's own later delete-then-restore cycle. (2) Correct recovery sequence, if caught within the 15-day window and before further mistakes: undelete Invoice__c (the master) first, confirm it and its cascaded-with-it details are intact, and only then attempt the separately-deleted Invoice_Line__c undelete — accepting that this order minimizes but does not eliminate risk. (3) Prevention: before allowing deletion of any Master-Detail parent, run a check (validation rule, or a "confirm" screen backed by an Apex/Flow lookup) that counts related active AND recycle-bin detail records and surfaces them explicitly — "this Invoice still has 3 other line items, are you sure?" — rather than trusting the admin's visual assumption that a record "looks empty."
• Hint 3 (the skeleton): Rule: detail records deleted independently before their master's own later delete are not guaranteed a clean reattachment when both are undeleted — order of restore matters (master before detail) and even that isn't a full guarantee across separate deletion events. Recovery: undelete master first, then attempt detail. Prevention: a pre-delete check (validation rule/Flow) that surfaces related detail-record counts (including recycle-bin) before any Master-Detail parent delete is confirmed.

THE REVEAL — POSTMORTEM

What actually happened (real class of incidents — the "deleted the child, then deleted the parent, now undelete is broken" support ticket; a well-documented Master-Detail undelete trap):

The ordering rule and why it breaks: Master-Detail relationships tie a detail record's valid existence to its master. When you delete a detail record while its master is still active, the detail sits in the recycle bin holding a reference to a live parent — restoring it is straightforward. The trap springs when the master is subsequently deleted: that master-delete cascades its currently-existing detail records into the recycle bin as one linked group, but the Invoice_Line that was deleted earlier, independently, is not part of that cascade group — it was already gone by the time the cascade happened. Salesforce's documented guidance is that a detail record deleted before its master must have its master restored first if you want any chance of clean reattachment, and even then, undelete of a Master-Detail child whose parent has gone through its own separate delete-and-possibly-restore cycle is fragile — it can restore into an orphaned or error state rather than seamlessly reattaching, because the platform did not track the two deletions as one atomic operation to reverse together.

The correct recovery sequence, if caught in time:

1. Undelete the master (Invoice__c) first. Confirm it restores along with the detail records that were cascaded with it in that same delete operation.
2. Then attempt to undelete the independently-deleted Invoice_Line__c from step 1, understanding this is the higher-risk restore — verify the resulting record's relationship field (Invoice__c lookup on the line item) actually points to a valid, restored master, and manually re-link (update the master-detail parent reference on the recovered record, if the platform allows editing it, which for standard Master-Detail is typically not editable post-creation — meaning in the worst case, the line item may need to be recreated from a backup/export, not truly undeleted, if reattachment fails).
3. Reconcile against a backup. Given the fragility, any org with financially significant Master-Detail data should treat "detail deleted before master" as a case that may require restoring from a data backup/export (scheduled export, a backup tool, or a sandbox refresh with the missing record) rather than relying on recycle-bin undelete alone.

The preventive design: before permitting deletion of any Master-Detail parent, run a check — a validation rule, a Flow with a "before delete" trigger action, or a simple Apex trigger — that queries for related detail records and explicitly informs the user of the count, so "this looks empty" is never assumed visually. For high-value objects (financial, compliance), consider blocking parent deletion outright while any detail records (active or recently deleted) exist, requiring an explicit administrative override.

Why the "obvious fixes" failed (the contrast):

• "Just undelete both from the recycle bin" → order matters, and even correct order isn't a full guarantee; the admin's instinct to "undelete the detail I remember deleting" first is exactly backwards.
• "Recycle bin holds everything for 15 days, so nothing is really lost" → true for straightforward same-operation cascades; false for this specific "detail-then-master, deleted in separate operations" sequence — this is precisely the gap the recycle bin doesn't cover cleanly.
• "Just re-create the line item manually" → workable, but only if you still have the original data (a report export, a backup) — the incident's real lesson is that recovery depended on evidence that existed outside Salesforce, which is why backups matter even with recycle bin "safety."

KNOWLEDGE EXTRACTION (interview-ready)

• "If you delete a Master-Detail child, then delete its master, can you get the child back?" → Not reliably. Undelete the master first; the independently-deleted child's restore is fragile and may fail to reattach cleanly — treat it as a possible unrecoverable case requiring backup restoration.
• "What's the safe order for restoring related deleted records?" → Master before detail, always — but this reduces risk, it doesn't guarantee success across separate deletion events.
• "How do you prevent this class of incident?" → A pre-delete check on Master-Detail parents that surfaces related detail-record counts (including recycle bin) before confirming deletion.
• "Recycle bin retention?" → Up to 15 days (subject to storage-based early eviction) — a soft safety net, not a guaranteed one for cross-operation cascades.

THE REDO

From memory: why detail-then-master deletion breaks clean undelete, the master-first recovery sequence, and the pre-delete count-check prevention.

RETRIEVAL DRILL

1. What's the safe order to undelete a Master-Detail parent and child?
2. Why doesn't restoring the master automatically reattach a detail deleted in an earlier, separate operation?
3. What's the fallback if undelete fails to cleanly reattach?
4. What prevention mechanism stops "the parent looked empty" mistakes?
5. Recycle bin retention window?

INTERVIEW MAPPING

Delete/undelete semantics on Master-Detail is a favorite senior-level trick question precisely because it's counterintuitive — most candidates assume the recycle bin is a full safety net, and this incident is the scenario that tests whether you know its real limits.

INCIDENT 7 — THE OWD THAT FLIPPED ITSELF

STAKES

A security audit flags that Custom_Financial_Note__c — an object that should be strictly Private (visible only via explicit sharing) — is now Public Read/Write org-wide, and everyone can see and edit every executive's confidential note. Nobody remembers changing the OWD directly. Git history shows no OWD change in metadata... except a data model change three months ago: Custom_Financial_Note__c was converted from a Master-Detail child of Executive_Profile__c to a Lookup, "to allow notes to be reassigned between profiles."

THE INCIDENT

Before conversion:
  Custom_Financial_Note__c --[Master-Detail]--> Executive_Profile__c
  Custom_Financial_Note__c OWD: Controlled by Parent (inherits Executive_Profile__c's Private OWD)

Conversion performed: Master-Detail → Lookup (to allow reassignment)

After conversion (silent side effect, not flagged in the change request):
  Custom_Financial_Note__c OWD: automatically set to Public Read/Write
  (Master-Detail's "Controlled by Parent" has no meaning once it's a Lookup —
   Salesforce must assign SOME real OWD, and it defaults to the most permissive.)

THE PROBLEM

Explain precisely why converting Master-Detail to Lookup forces an OWD change (why "Controlled by Parent" can't just carry over), why the platform defaults to Public Read/Write rather than Private, and design the process that would have caught this before it shipped.

Write: (1) why the OWD must change and why it defaults to the permissive option, (2) the security review that should exist, (3) the corrected settings + process.

HINT LADDER

• Hint 1 (the avenue): (1) "Controlled by Parent" is not a real independent OWD value — it's a delegation ("use whatever the master's sharing says"), and it's only a valid setting for objects that are the detail side of a Master-Detail relationship. Once the object is no longer a Master-Detail child, "Controlled by Parent" is meaningless and Salesforce must assign a concrete OWD. (2) It defaults to the most permissive (Public Read/Write) rather than guessing at intent, because Salesforce cannot know what the "right" restrictive value should be — a fail-open default that a human must consciously restrict. (3) The catch should be a mandatory security review step on every relationship-type conversion, specifically checking OWD after the fact.
• Hint 2 (the mechanism): (1) "Controlled by Parent" is a specific OWD setting available ONLY when the object is the detail of a Master-Detail relationship — it explicitly means "this object has no OWD of its own; sharing/visibility flows entirely from the master." The moment the relationship converts to Lookup, that delegation relationship no longer exists (Lookup has independent sharing by design — that's the whole point of Lookup vs Master-Detail), so the platform cannot leave the object in a now-meaningless state; it must assign a concrete, standalone OWD value. (2) Salesforce's documented behavior for this specific conversion is that the OWD is set to Public Read/Write — the most permissive option — rather than Private, because the platform cannot infer the intended restrictive posture from a formerly-delegated setting, and defaulting restrictive could silently break existing workflows that assumed inherited visibility; the safer-for-continuity-but-dangerous-for-security choice is permissive-by-default, and it is on the admin to immediately tighten it. This is a documented gotcha, not a bug, but it is very rarely mentioned in change-request reviews. (3) Process: any relationship-type conversion touching a Master-Detail relationship must include an explicit post-conversion OWD check as a mandatory review step — before/after screenshots of Sharing Settings for the converted object, reviewed by a second person, before the change is considered complete.
• Hint 3 (the skeleton): "Controlled by Parent" = delegation, only valid under Master-Detail; conversion to Lookup removes the delegation target, forcing a real OWD; Salesforce defaults that real OWD to Public Read/Write (documented behavior, fail-open). Fix: immediately set the correct OWD post-conversion (Private + explicit sharing rules recreating the old access pattern), and add a mandatory "check OWD after any MD→Lookup conversion" step to the change process, ideally automated via a deployment validation script that diffs Sharing Settings.

THE REVEAL — POSTMORTEM

What actually happened (real class of incidents — the "OWD silently flipped after a relationship conversion" security gap; one of the most underappreciated Master-Detail/Lookup conversion side effects, and a frequent audit finding):

Why the OWD must change: "Controlled by Parent" is a special OWD value that exists only to express "this detail object inherits its access model from its Master-Detail parent — it has no sharing rules of its own." It is not a real, independent security posture; it's a pointer to someone else's posture. The instant Custom_Financial_Note__c converts from Master-Detail to Lookup, there is no longer a parent whose sharing it can be "controlled by" in that sense — Lookup relationships are explicitly designed to have independent sharing between parent and child. The platform cannot leave the field pointing at a relationship that no longer confers that meaning, so it must assign a genuine standalone OWD.

Why it defaults to Public Read/Write, not Private: this is documented Salesforce behavior for this specific conversion, and the logic is continuity-over-caution: the platform has no way to know what restrictive posture you actually intend, and a sudden switch to Private could silently break every existing process, report, and integration that assumed the previously-inherited (potentially broad) visibility continued working. Rather than guess, it fails open — maximally permissive — on the theory that "nothing breaks functionally" even though this is precisely backwards from a security standpoint for sensitive data. This is the single most consequential "silent side effect" in Master-Detail/Lookup conversions, and it is why every conversion of this type demands an explicit post-conversion security review.

The corrected settings + process:

1. Immediately fix the OWD: set Custom_Financial_Note__c to Private (or Controlled by Parent is no longer available — so Private, plus explicit sharing rules or manual sharing recreating whatever access pattern the old "Controlled by Parent" inheritance used to provide).
2. Mandatory post-conversion review: any change request that converts a Master-Detail relationship to Lookup (or vice versa) must include an explicit line item: "OWD before / OWD after — reviewed by [second person]." This is a checklist gate, not a suggestion.
3. Automated guardrail: a deployment validation script (or a scheduled Apex job comparing OWD settings against an expected baseline) that flags any object whose OWD changed unexpectedly between deployments — catching this class of drift even when the human review step is skipped.

Why the "obvious fixes" failed (the contrast):

• "Just set it back to Controlled by Parent" → not possible; that setting requires an active Master-Detail relationship, which the business requirement (reassignable notes) explicitly ruled out.
• "Blame the conversion tooling / find who ran it" → the behavior is documented and expected platform behavior, not a bug or misconfiguration by the person who ran the conversion; the real gap is the missing review step, not the tool.
• "Audit git history for the OWD change" → OWD is an org-wide sharing setting, and depending on how change tracking is set up, a side-effect change triggered by a different metadata change (the relationship conversion) may not surface distinctly in a diff the way a direct settings edit would — reinforcing why a dedicated automated check is needed rather than relying on change-log archaeology.

KNOWLEDGE EXTRACTION (interview-ready)

• "What happens to OWD when you convert Master-Detail to Lookup?" → "Controlled by Parent" is no longer valid (it requires an active Master-Detail relationship); Salesforce assigns the object a real OWD, and the documented default is Public Read/Write — the most permissive option, not Private.
• "Why does it default permissive instead of restrictive?" → Fail-open for continuity of existing processes; the platform can't infer intended restrictiveness, so it avoids silently breaking things functionally — at the cost of a silent security widening that must be caught by review.
• "What's the mandatory step after this conversion?" → An explicit OWD review — set the correct restrictive value and recreate access via sharing rules/manual sharing if needed.
• "Is 'Controlled by Parent' available for Lookup relationships?" → No — it is exclusive to the detail side of an active Master-Detail relationship.

THE REDO

From memory: why the delegation breaks on conversion, the documented default (Public Read/Write) and why, and the three-part corrected process.

RETRIEVAL DRILL

1. What does "Controlled by Parent" actually mean as an OWD setting?
2. Why can't it survive a Master-Detail → Lookup conversion?
3. What does the OWD default to after that conversion, and why that value specifically?
4. What's the mandatory review step this incident should have had?
5. How do you recreate the old inherited access pattern after fixing the OWD to Private?

INTERVIEW MAPPING

This is the sharpest "do you know the silent side effects, not just the textbook rule" question in the whole module — most candidates know MD converts to Lookup with some restrictions, almost none unprompted mention the OWD flip. Naming it unprompted is a strong senior signal.

INCIDENT 8 — THE REPORT THAT FORGOT HOW TO COUNT

STAKES

IoT device telemetry — 200 million rows/month — was migrated from a custom object to a Big Object (Device_Event__b) to solve a storage-limit crisis. Storage problem solved. Three weeks later, the operations dashboard that used to show "events per Account, rolled up live" is now permanently blank, the nightly Apex batch job that used to aggregate event counts onto the Account record throws no errors but never runs the aggregation, and a scheduled report that used to alert on anomalous event spikes has been silently producing zero rows for weeks.

THE INCIDENT

Before: Device_Event__c (standard custom object, Master-Detail to Account)
        - Roll-up summary on Account: Total_Events__c = COUNT(Device_Event__c)
        - Apex trigger on Device_Event__c (after insert): update anomaly-detection fields
        - Standard report + dashboard on Device_Event__c

After migration to Big Object (Device_Event__b):
        - Total_Events__c roll-up: frozen (Big Objects can't be the detail side of
          Master-Detail; the relationship + roll-up no longer function)
        - The after-insert trigger: doesn't fire (Big Objects don't support standard
          triggers in the same way — inserts go through Database.insertImmediate/
          insertAsync, not standard DML that fires apex triggers the same way)
        - Standard reports: Big Objects have limited/no native report-type support in
          the classic sense; querying requires SOQL (indexed fields only) or async
          bulk query patterns, not ad hoc report builder drag-and-drop

THE PROBLEM

Explain, mechanism by mechanism, why each of the three broken features (roll-up, trigger-based aggregation, reporting) failed after the Big Object migration, what Big Objects are actually designed for and NOT designed for, and design the replacement architecture for each broken piece.

Write: (1) why each of the three things broke, (2) what Big Objects are/aren't for, (3) the three replacement designs.

HINT LADDER

• Hint 1 (the avenue): (1) Big Objects are built for massive-scale, mostly-immutable historical data (billions of records) with index-based async querying — they deliberately give up standard-object conveniences (triggers, roll-ups, ad hoc reporting) in exchange for scale. (2) Roll-up summaries require an active Master-Detail relationship on a standard object; Big Objects cannot participate as the detail side of Master-Detail at all. (3) Aggregation and alerting need to be rebuilt as: scheduled Apex jobs querying the Big Object asynchronously and writing summarized results to a standard object, rather than expecting live triggers/roll-ups/reports to keep working unchanged.
• Hint 2 (the mechanism): (1) Roll-up broke because roll-up summary fields are a Master-Detail-only feature, and Big Objects cannot be the detail side of a Master-Detail relationship (they don't support standard relationship semantics the same way) — the roll-up simply has nothing valid to compute from anymore, and like Incident 2, it freezes rather than erroring. (2) The trigger-based aggregation broke because Big Objects use a different insert path — Database.insertImmediate() (synchronous, small batches) or Database.insertAsync() (asynchronous, via a queueable-style mechanism) — and standard Apex triggers on Big Objects have significant limitations (historically, insert-only trigger support at best, and many orgs find their existing "on insert, cascade an update to a related standard object" pattern simply does not carry over the way it did for a standard custom object); the team's after-insert trigger logic effectively stopped running as designed. (3) Reporting broke because Big Objects are queried via indexed SOQL fields only (you must query by an indexed field, not arbitrary WHERE clauses) and are not natively drag-and-drop reportable the way standard/custom objects are in the classic Report Builder — dashboards and ad hoc reports built against Device_Event__c don't just "point at" Device_Event__b and keep working. (2) Big Objects are for: petabyte/billion-row archival and historical data, audit trails, IoT/event data at massive scale, with async/index-based access patterns — they are explicitly NOT for: data needing live roll-ups, live triggers, ad hoc reporting, or transactional/real-time business logic. (3) Replacements: (a) roll-up → a scheduled Apex batch job that queries Device_Event__b via its indexed fields, aggregates counts per Account, and writes the result to Account.Total_Events__c (a plain field now, updated periodically, not live); (b) trigger-based real-time anomaly detection → rearchitect as event-driven: publish a Platform Event at ingestion time (before/alongside the Big Object write) so real-time anomaly logic subscribes to the event, not to a Big Object trigger; (c) reporting → build a summary/aggregate standard object populated by the scheduled batch job specifically for reporting and dashboards, since that object supports normal Report Builder/dashboard functionality, while the Big Object remains the system of record for raw historical detail.
• Hint 3 (the skeleton): Big Objects trade standard-object features (triggers, MD roll-ups, ad hoc reports) for massive scale + async/indexed querying. Fixes: scheduled batch aggregation job (Big Object → summary field/object), Platform Events for real-time detection at ingestion time (not Big-Object triggers), and a dedicated reporting/aggregate object populated by the batch job for dashboards — the Big Object itself stays as the archival system of record, never the live reporting surface.

THE REVEAL — POSTMORTEM

What actually happened (real class of incidents — the "we solved storage by moving to Big Objects and broke three unrelated things" migration; a very common gap when teams treat Big Objects as "a custom object with more rows"):

Why the roll-up broke: roll-up summary fields are exclusively a Master-Detail feature on standard/custom objects. Big Objects fundamentally do not support being the detail side of a standard Master-Detail relationship the way a custom object does — the relationship and its dependent roll-up simply stop functioning, freezing (as in Incident 2) rather than throwing a visible error.

Why trigger-based aggregation broke: Big Objects use distinct write paths — Database.insertImmediate() for small synchronous batches and Database.insertAsync() for asynchronous bulk loads — rather than ordinary DML, and Apex trigger support on Big Objects is limited relative to standard objects (historically constrained largely to insert-context scenarios, with significant differences from the full before/after insert/update/delete trigger lifecycle developers rely on for standard objects). The team's "on insert, cascade an update elsewhere" pattern was written assuming full standard-object trigger semantics and did not carry over cleanly.

Why reporting broke: Big Objects are queried via SOQL against indexed fields specifically defined on the Big Object's schema — you cannot run arbitrary ad hoc WHERE-clause queries the way you can against a standard object, and classic Report Builder / dashboards do not natively support Big Objects the way they do standard and custom objects. A report built against Device_Event__c has no direct equivalent against Device_Event__b without custom tooling.

What Big Objects are actually for: massive-scale, largely historical/append-heavy data — audit trails, IoT telemetry, archival records — measured in the hundreds of millions to billions of rows, where the tradeoff of giving up live triggers, roll-ups, and ad hoc reporting in exchange for storage scale and indexed async querying is worth it. They are not a drop-in replacement for a standard custom object that happens to have a lot of rows — every piece of standard-object tooling built around the old object (triggers, roll-ups, reports, dashboards) has to be deliberately rearchitected, not assumed to "just work" at scale.

The three replacement designs:

1. Roll-up replacement: a scheduled Apex batch job querying Device_Event__b on its indexed fields, aggregating per Account, writing the result into a plain Total_Events__c field on Account — periodic, not live, and that tradeoff must be communicated to stakeholders.
2. Real-time anomaly detection replacement: move detection to event time, not storage time — publish a Platform Event when telemetry arrives (in parallel with, or just before, the Big Object write), and have anomaly-detection logic subscribe to that event stream instead of depending on a Big Object trigger that doesn't behave like a standard one.
3. Reporting/dashboard replacement: a dedicated summary/aggregate standard object, populated by the same scheduled batch job, purpose-built to be reportable/dashboardable — the Big Object remains the system of record for raw historical detail, while the summary object is the system of record for "what the business looks at."

Why the "obvious fixes" failed (the contrast):

• "Just point the existing report at the Big Object" → classic reporting tooling doesn't support Big Objects the way it supports standard objects; there's no simple re-pointing.
• "Rewrite the trigger to work on Big Objects" → the trigger model itself is constrained on Big Objects; the fix is architectural (move logic to ingestion-time events), not a trigger rewrite.
• "Add the roll-up back with a workaround" → roll-ups require Master-Detail, which Big Objects can't participate in; there's no workaround that preserves "live roll-up" semantics — the honest fix is accepting periodic (batch) aggregation instead.

KNOWLEDGE EXTRACTION (interview-ready)

• "What are Big Objects for?" → Massive-scale (hundreds of millions to billions of records) archival/historical data — audit trails, IoT/event data — with async, indexed-field-based querying; explicitly a different tool from standard/custom objects.
• "Do Big Objects support roll-up summaries?" → No — they cannot be the detail side of a Master-Detail relationship at all.
• "Do Big Objects support standard Apex triggers?" → Limited support relative to standard objects; write paths use Database.insertImmediate()/insertAsync(), and trigger lifecycle behavior differs meaningfully from full standard-object trigger semantics.
• "How do you report on Big Object data?" → Not via classic ad hoc Report Builder; query via indexed SOQL fields and typically feed a summary/aggregate standard object (via scheduled batch) that IS reportable.

THE REDO

From memory: why each of the three features broke (roll-up, trigger, reporting), what Big Objects are/aren't designed for, and the three replacement architectures.

RETRIEVAL DRILL

1. Can a Big Object be the detail side of a Master-Detail relationship?
2. What are the two DML methods used to write to Big Objects?
3. How must you query a Big Object (what kind of fields)?
4. Name the ingestion-time replacement for a Big-Object trigger that needed real-time reaction.
5. What object should carry your dashboard/report logic when the underlying data lives in a Big Object?

INTERVIEW MAPPING

Big Objects appear as a "have you worked at real scale" differentiator question; this incident tests whether you know the specific standard-object features that silently stop working, not just "Big Objects are for lots of data."

🏆 CAPSTONE — THE ERD NOBODY DREW

STAKES

You're the newly hired senior developer at a fast-growing org. In your first week, you're handed a support inbox with six unresolved data-model tickets that have been sitting for months because "nobody understood the schema well enough to fix them safely." There is no ERD documentation. The VP of Engineering wants, by Friday: a diagnosis of each ticket, a prioritized fix plan, and — because leadership has finally agreed to fund it — a proposal for the missing ERD/documentation discipline that would have prevented all six.

THE INCIDENT — six tickets, real evidence, no explanations attached

Ticket A (Finance): "We deleted a stale test Opportunity last month and somehow lost 40 real Commission_Split records tied to a DIFFERENT, still-active Opportunity that shared a Contact Role."

Opportunity --[MD]--> OpportunityContactRole --[MD]--> Commission_Split__c
Deleted: Opportunity "Test-DELETE-ME" (had 1 OpportunityContactRole, which
  turned out to be erroneously also linked as detail to Commission_Split
  records that finance believed belonged to Opportunity "Acme Renewal 2026" —
  investigation needed: was Commission_Split really MD off THIS
  OpportunityContactRole, or off the Opportunity directly?)

Ticket B (Sales Ops): "Our 'Total Pipeline Value' roll-up on Account has read zero for six weeks. Nobody touched the field."

Account.Total_Pipeline__c = ROLLUP(SUM(Opportunity.Amount), Opportunity WHERE StageName != 'Closed Lost')
Six weeks ago: Opportunity relationship to Account converted Lookup → nothing
  (still Master-Detail!) but a NEW custom field "Account_Snapshot__c" (Lookup,
  unrelated) was added and everyone assumed IT was now the "real" account link,
  quietly migrating record-detail-page logic to reference it instead — the
  roll-up still works off the ORIGINAL AccountId Master-Detail field, which
  nobody's been populating correctly since the migration to Account_Snapshot__c.

Ticket C (Support): "A dashboard listing 'recent Task activity per Case' shows garbage company names for a third of rows."

SELECT Id, Subject, WhatId, What.Name FROM Task WHERE WhatId != null

Ticket D (Data Quality): "212 duplicate Accounts created this quarter, all traced to Lead conversion, all missing Opportunities that should have existed."

Lead conversion checkbox "Create Opportunity" left unchecked by default in
the org's Lead conversion settings; reps not trained to check it; separately,
duplicate rule for Account is Alert-only.

Ticket E (IT Ops): "Nightly aggregation batch job against our archived call-log Big Object throws no errors but Account.Total_Calls__c hasn't updated in 2 months."

Call_Log__b (Big Object) — batch job queries WHERE Account_Id__c = :acctId
  (Account_Id__c is NOT one of the object's declared indexed fields)

Ticket F (Security/Audit): "An external audit found Vendor_Contract__c is Public Read/Write; we don't know why, and finance is panicking."

Git blame shows a metadata deploy 4 months ago: "Convert Vendor_Contract__c
relationship to Vendor__c from Master-Detail to Lookup, per Procurement
request for cross-vendor contract reassignment."

THE PROBLEM

For EACH ticket, identify: (1) which incident/mechanism from this module it maps to, (2) the root cause, (3) the fix. Then produce: a prioritized punch list (tonight / this week / this quarter), the ERD/documentation proposal (what it must capture to prevent recurrence), and a 2-minute verbal answer you'd give the VP Friday morning.

HINT LADDER

• Hint 1 (the avenue): Map each ticket to a module incident by its villain mechanism: A→junction-object cascade delete (Incident 1) with an added twist (verify the actual relationship depth before assuming); B→a relationship that was NEVER actually converted, but everyone's assumptions drifted (a "phantom conversion" — the real bug is a parallel/shadow field taking over reporting logic while the roll-up's true source relationship silently stopped being maintained); C→polymorphic WhatId (Incident 4); D→Lead conversion gaps (Incident 5), but the specific defect here is a missed Opportunity checkbox, not just duplicate Accounts; E→Big Object indexed-query requirement (Incident 8's cousin — querying on a non-indexed field); F→OWD-flip-on-conversion (Incident 7).
• Hint 2 (the mechanism): (A) Confirm via Schema Builder/describe calls whether Commission_Split__c is MD to OpportunityContactRole or directly to Opportunity — the ticket's confusion itself is evidence nobody has an ERD; the fix is documentation + a delete-guard trigger, same as Incident 1. (B) The roll-up's SOURCE relationship (the real Master-Detail AccountId on Opportunity) was never actually converted — it's still intact and still computing correctly off real data; the bug is that a parallel, unrelated field (Account_Snapshot__c) was introduced and business logic silently started trusting it instead, while data entry/process stopped keeping AccountId populated the way it used to (e.g., new Opportunities get created attached only via Account_Snapshot__c workflows, leaving the real MD AccountId null or attached to a placeholder) — the fix is a data audit reconciling AccountId vs Account_Snapshot__c and a decision about which is canonical, NOT a relationship-conversion fix. (C) TYPEOF or type-branching, per Incident 4. (D) Two separate defects: default-unchecked "Create Opportunity" (a Setup configuration fix — flip the default, or better, make Opportunity creation an explicit, prominent decision in a custom conversion flow) plus the Alert-only duplicate rule (per Incident 5, change to Block + fuzzy matching). (E) Big Objects can only be efficiently queried on their declared indexed fields — a WHERE clause on a non-indexed field either errors or performs a full/inefficient scan depending on configuration and volume; the fix is adding/using a proper indexed field (Big Object index fields are defined at object-creation time and are NOT alterable after the fact the way standard object indexes can sometimes be adjusted — meaning if Account_Id__c isn't indexed, the real fix may require a schema redesign/re-migration of the Big Object with an index that includes Account_Id__c). (F) Per Incident 7 — fix the OWD to Private + sharing rules, and add the missing review-step process going forward.
• Hint 3 (the skeleton): Full mapping table + fixes as in the reveal below; prioritize by business risk (F = compliance/security = tonight; A, E = broken finance reporting = this week; B, D = data-quality drift = this week/this quarter; C = cosmetic but erodes trust = this week). ERD proposal: every custom object's relationship type, cardinality, OWD, and roll-up dependencies documented in Schema Builder + a maintained data dictionary, with a mandatory "relationship-change impact checklist" (OWD check, roll-up dependency check, delete-cascade blast-radius check) gating every future MD/Lookup conversion.

THE REVEAL — POSTMORTEM

See the sealed Answer Sheet (08b_Topic08_Data_Model_Answer_Sheet.md) for the full model report, per-ticket diagnosis, prioritized punch list, ERD/documentation proposal, and the 2-minute verbal script.

KNOWLEDGE EXTRACTION (interview-ready)

• A real data-model incident is rarely "one clean mechanism" — Ticket B here is a process/assumption drift incident, not a clean relationship-conversion bug; recognizing "nobody actually converted anything, but everyone stopped trusting the real field" is itself a skill.
• Every relationship-type conversion (MD↔Lookup) needs a standing checklist: roll-up dependency check, OWD check, cascade-delete blast-radius check, reparenting-requirement check — this capstone is the reason the checklist exists.
• Big Object index fields are fixed at schema design time — plan your query patterns BEFORE migrating, not after.
• An ERD is not decoration — it is the artifact that turns "nobody remembers why this is Master-Detail" into an answerable question in thirty seconds.

THE REDO

Reproduce, from memory, the full six-ticket mapping table (ticket → mechanism → root cause → fix), the prioritized punch list, and the 2-minute VP script.

RETRIEVAL DRILL (write a 2-minute script + a verification checklist)

1. For each of the six tickets, state the module incident it maps to.
2. State the one thing an ERD/data-dictionary would have made instantly answerable in each case.
3. Write the standing "relationship-conversion checklist" (4 items) that prevents recurrence.
4. Write your 2-minute verbal answer to the VP.
5. Write a verification checklist (5 items) you'd run before declaring any of the six tickets "closed."

INTERVIEW MAPPING

This is the "give me a schema war story" capstone — the take-home-style prompt senior loops use to see whether you can triage multiple simultaneous data-model failures, prioritize by business risk, and propose the governance fix, not just patch each symptom.

End of Module 8 workbook. Proceed to 08b_Topic08_Data_Model_Answer_Sheet.md only after writing your attempts.