DoorDash Data Engineer Interview Preparation Guide | Mid-Level

Direct answer
Give the listener a short roadmap up front (what you're about to walk through and in how many steps), narrate one idea at a time in order, and periodically restate where you are relative to that roadmap, rather than free-associating through your thought process.

Structured elaboration

1. State the roadmap before diving in: "There are two things going on here: first the root cause, then the fix I'd propose. Let me start with the root cause." This gives the listener a mental container to place what follows.
2. Narrate conclusions and reasons, not raw stream-of-consciousness. Say what you're checking and why, not just what you're doing: "I'm checking the logs because I suspect this is a timeout, not a crash," rather than silently scrolling and occasionally muttering.
3. Signal transitions explicitly: "okay, that rules out X, so now let's look at Y," so the listener can track your position in the reasoning instead of having to reconstruct it after the fact.
4. Pause at natural checkpoints to check the listener is still following, especially before switching to a new sub-problem, rather than only checking in at the very end.
5. Name your assumptions out loud as you make them, since an unstated assumption is invisible to the listener and, if wrong, can make the rest of your reasoning look wrong for a reason they can't see.

Worked example
Unstructured: "Okay so let me look at this... hmm... yeah so there's this function... wait, let me check something else... okay so actually I think the issue might be... let's see... yeah I think it's the caching."

Structured: "I'm going to check three possible causes in order of likelihood: caching, a race condition, or a bad config value. Starting with caching, since it's the most common cause of this symptom... [checks] ...that rules out caching, the values are fresh. Moving to the race condition..."

The second version gives the listener the plan up front, tells them which hypothesis is being tested and why, and explicitly states when a hypothesis is ruled out, so they can follow the reasoning instead of just watching an unexplained sequence of actions.

Trade-offs and pitfalls

• Over-narrating every micro-step can slow you down and annoy a listener who just wants the conclusion; calibrate the level of narration to whether the audience needs to follow the reasoning (an interview, a mentoring session) or just wants the answer (a peer who trusts you and is short on time).
• It's easy to silently switch approaches mid-thought without saying so; if you change direction, say so explicitly ("actually, let me back up") rather than leaving the listener to notice on their own.
• This is a skill that degrades under real pressure or unfamiliar problems; it's worth practicing the "state the roadmap first" habit specifically, since it's the cheapest part to do consistently even when the rest of your thinking is genuinely uncertain.

Direct answer

Schema-first (normalized) modeling optimizes for write correctness and long-term maintainability at the cost of query flexibility for unanticipated analytics needs; query-driven, denormalized modeling optimizes for the specific known queries at the cost of being harder to evolve if those queries change; for a microservice serving both its own transactional needs and ad hoc downstream analytics, the right balance is usually schema-first for the service's own operational data, with a separate, query-driven denormalized layer built specifically for the analytics consumers.

Structured elaboration

• Schema-first (normalized): model the domain's true entities and relationships first, driven by what the data actually IS, not by any one query's convenience; this keeps the service's own write path simple and correct (one place to update any given fact) and is resilient to future, not-yet-known query needs, at the cost of every non-trivial analytical query needing multiple joins.
• Query-driven (denormalized): model the schema around the SPECIFIC queries you already know you need to serve, often producing wide, pre-joined tables; this makes those specific queries fast and simple, at the cost of the schema being brittle to any query pattern not anticipated when it was designed, and at the cost of write-path complexity (keeping the denormalized shape consistent with the true source of facts).
• Balancing productivity, performance, and maintainability: developer productivity favors schema-first for the service's OWN code (a clean, normalized domain model is easier for the team to reason about and evolve); query performance for KNOWN, high-value analytics queries favors a denormalized layer; maintainability favors keeping exactly one normalized source of truth, with any denormalized layer treated as a derived, rebuildable artifact rather than a second place where facts can independently change.

Worked example

A concrete recommendation: the microservice's own database stays normalized (its own tables model its own domain correctly, optimized for its own transactional writes and reads); a separate, downstream analytics table or warehouse, fed by CDC or a scheduled ETL job from the normalized source, is denormalized specifically around the known analytics queries (a wide, pre-joined "orders with customer and product context" table, say). This keeps the service's own code simple and correct while still giving analytics teams fast, tailored access, without forcing the service's core domain model to compromise for a query pattern that belongs to a different consumer entirely.

Trade-offs and pitfalls

• The temptation to denormalize the service's OWN operational schema (rather than building a separate analytics layer) to make ad hoc queries fast is usually a mistake: it couples the service's write-path correctness to the needs of a downstream consumer, and every future change to the service's own domain model now has to consider its effect on someone else's analytics queries too.
• A separate denormalized analytics layer isn't free: it needs its own pipeline (CDC or ETL) to stay in sync with the normalized source, introduces staleness (the analytics layer lags the live service by however long that pipeline takes), and needs monitoring to catch drift, the same trade-offs as any other denormalization-for-reads decision.
• The right balance shifts with how STABLE the known analytics queries actually are: if the downstream query needs are still evolving rapidly, a query-driven denormalized layer built too early risks becoming outdated before it's even fully built out, and it may be better to serve early-stage analytics needs directly off the normalized source (accepting the join cost) until the query patterns stabilize enough to justify building and maintaining a dedicated denormalized layer.

Direct answer

Start at the KPI's own defining table or view and walk its lineage graph upstream one hop at a time, using whatever lineage source is available (a transformation tool's dependency graph, a data catalog, or the warehouse's own query-history metadata) to list its immediate producers. Then prioritize which of those to inspect first by what changed most recently and which carries the most complex logic, rather than checking every upstream table with equal weight, and confirm a hypothesis by comparing actual numbers against historical baselines before calling it the root cause.

Structured elaboration

1. Confirm the symptom precisely. Which number is wrong, since when, and by how much. The "since when" matters most, because it turns an open-ended search into "what changed upstream around that date."
2. Pull the first-pass dependency graph from tooling, not from memory. A lineage tool, whether it's a transformation framework's dependency graph, a data catalog, or the warehouse's own lineage or query-history view, gives the KPI's immediate upstream tables and transformations in seconds. This should always be the first move, before reading any transformation logic by hand.
3. Prioritize the candidates instead of sweeping all of them:
   • Recency of change is the strongest signal; a code or schema change close to when the KPI diverged is the top suspect.
   • Logic complexity matters next; joins, window functions, and aggregations hide subtle bugs far more often than a straight pass-through does.
   • Recent operational incidents on a source (a known late or failed load) are an obvious, cheap first check.
4. Validate quantitatively, not by inspection alone. Compare a suspect's current row counts, key distributions, or aggregate values against its own historical baseline for the same period. A real KPI bug shows up as a measurable divergence somewhere in the chain, and that comparison either confirms or rules out a candidate before more time is spent on it.
5. Fix at the actual point of defect, not by patching the KPI layer to compensate; patching the transformation or coordinating with the upstream data owner, then re-running affected models forward, is what actually resolves it rather than hiding it.
6. Add a targeted check to prevent recurrence on exactly the field or transformation that broke, so the same failure class is caught before it reaches the KPI again.

Worked example

Say the monthly revenue KPI comes in 8 percent below expectation for November, and the trace starts at the orders table feeding the revenue model. The typical daily order count in November is about 150,000. On November 14, the day the divergence first appears, the orders table shows only 122,000 rows:

Spread across a 30-day month, one day's 18.7 percent shortfall contributes roughly:

That's far smaller than the 8 percent monthly miss actually observed, which rules out the single-day volume dip as the primary cause and points the trace toward a sustained, multi-day issue instead. Following the lineage graph one more hop, to the pricing table the revenue model joins against, turns up a schema change (a new discount field) that landed around the same time and caused the join to double-count discounted rows for every day the field has existed, a defect whose scale (spread across many days, not one) is consistent with an 8 percent sustained miss. The arithmetic above is what rules the first hypothesis out and justifies moving one hop further upstream, rather than stopping at the first plausible-looking suspect.

flowchart LR
    A[KPI shows unexpected value] --> B[Pull lineage graph from KPI object]
    B --> C[List immediate upstream sources]
    C --> D[Prioritize by recency and logic complexity]
    D --> E[Compare suspect vs historical baseline]
    E -->|rules out| D
    E -->|confirms| F[Fix at the actual source]
    F --> G[Add targeted check to prevent recurrence]

Trade-offs & pitfalls

• Reading every model's transformation logic by hand before checking the automated lineage graph wastes time the tooling already answers in seconds; lineage-first is almost always the faster path.
• Checking every upstream table with equal priority, instead of ranking by recency and complexity, turns a targeted trace into an unfocused audit that takes far longer than it needs to.
• Patching the KPI view itself to compensate for a known-bad upstream input, instead of fixing the actual defective transformation, hides the bug until the next time that upstream table feeds something else.
• A common wrong turn is treating lineage as purely structural (what depends on what) without also checking when each dependency last changed; the timing correlation is usually what actually narrows the search from many candidates to one.

Direct answer

Do not decide this purely from a fixed fragmentation percentage. The widely repeated rule of thumb (reorganize between 5% and 30% average fragmentation, rebuild above 30%) is a reasonable starting filter, but Microsoft's own current guidance for SQL Server explicitly warns against relying on fixed thresholds alone, and points out that a lot of the performance improvement people attribute to a rebuild actually comes from the full statistics refresh a rebuild does as a side effect, not from the physical defragmentation itself. For a 500 GB online transaction processing (OLTP) table under continuous load, the practical default is REORGANIZE (always a fully online, low-impact operation) and REBUILD is reserved for when page density (a page is the fixed-size block of storage, 8 KB by default, that a table or index is physically built from; page density measures how full each page actually is, a different and often more consequential number than fragmentation) is materially low, when the table needs a genuine full-scan statistics refresh, or when fragmentation keeps returning quickly after a reorganize, which is itself a signal about fillfactor, not about the maintenance method.

Structured elaboration

Two different numbers get conflated as "fragmentation"

SQL Server's sys.dm_db_index_physical_stats reports both avg_fragmentation_in_percent (logical fragmentation: how out-of-order the leaf pages are relative to the index's key order, which mainly hurts range and full scans) and avg_page_space_used_in_percent (page density: how full each page actually is, which affects every kind of read because a less-full page means more I/O and less usable buffer-pool cache (the in-memory cache of recently used pages that lets the database skip a disk read on repeat access) per byte of real data). A table can have low logical fragmentation and still perform poorly because of low page density, or vice versa; treating "fragmentation" as one number hides this.

REORGANIZE versus REBUILD

The statistics angle, and why it changes the decision

Microsoft's current guidance is explicit that customers "often incorrectly attribute" a post-rebuild performance improvement to reduced fragmentation, when the real cause was the full-scan statistics update that happened alongside it. The query optimizer's plan choices depend on statistics currency and sampling quality, not on physical page order; a rebuild refreshes both at once, which is why it can look like a fragmentation fix when it was really a statistics fix achieved at far higher cost. The stated recommendation: if rebuilding an index seems to help, try replacing it with a plain UPDATE STATISTICS ... WITH FULLSCAN next time; the resource cost of updating statistics is minor and the operation typically finishes in minutes, versus hours for a rebuild on a large table.

Where fillfactor fits in

Fillfactor controls how full a page is left when it is built (a fillfactor of 90 leaves 10% of each page empty). Both REORGANIZE and REBUILD compact pages back to the index's configured fillfactor. A lower fillfactor leaves headroom for future row insertions without immediately triggering a page split (the event that causes fragmentation in the first place on a table with a non-sequential key), at the ongoing cost of more pages, more I/O, and less usable buffer-pool cache per byte of real data, every single day, not just during maintenance. Current Microsoft guidance is not to set fillfactor away from 100 by default; do it only where there is actual evidence of a high page-split rate, most commonly an index whose leading column is a non-sequential value like a randomly generated identifier. For this scenario, if fragmentation keeps climbing back to 30% shortly after a reorganize, that recurrence is the evidence to look for before lowering fillfactor, not something a maintenance schedule alone can fix.

A maintenance approach that minimizes user impact on 500 GB

Default to REORGANIZE on a recurring cadence driven by measured fragmentation and page density, not a fixed calendar. It is always online, so it does not need a maintenance window. Reserve REBUILD (with ONLINE = ON) for cases where reorganize is not enough, and if the table is partitioned, rebuild or reorganize one partition at a time rather than the whole 500 GB at once, to bound both the resource footprint and the disk headroom needed for any given operation. Before running any REBUILD, confirm there is enough free disk space for roughly a second copy of the index; on a table this size that is a real, checkable constraint, not a detail to skip.

Worked example

PostgreSQL does not expose an avg_fragmentation_in_percent DMV (Dynamic Management View, SQL Server's term for a system view exposing live internal engine statistics), but its pgstattuple and pgstatindex extensions measure the same underlying ideas (dead space in the table, and leaf-page density in a B-tree index). The following was executed against a real PostgreSQL 16 instance to show what "the index still looks full of garbage until you actually rebuild it" looks like in practice, not just claimed:

CREATE EXTENSION pgstattuple;
CREATE TABLE frag_demo (id bigint PRIMARY KEY, val text) WITH (fillfactor = 90);
INSERT INTO frag_demo SELECT g, repeat('a', 100) FROM generate_series(1, 300000) g;
DELETE FROM frag_demo WHERE id % 3 = 0;              -- delete a third of the rows
UPDATE frag_demo SET val = repeat('b', 100) WHERE id % 2 = 0;  -- update half

SELECT * FROM pgstattuple('frag_demo');
SELECT * FROM pgstatindex('frag_demo_pkey');

Output (executed), before any cleanup:

 dead_tuple_percent | free_percent
---------------------+--------------
               46.21 |          2.38

 avg_leaf_density | leaf_pages
-------------------+------------
             96.36 |        820

The index's avg_leaf_density looks high here (96.36%) but that number is misleading: those leaf pages are still full of index entries pointing at now-dead heap rows. Running an ordinary VACUUM reclaims the dead heap space for reuse, but it also removes the corresponding dead entries from the index leaf pages without compacting them, which actually makes the reported density worse, not better:

VACUUM (VERBOSE) frag_demo;
SELECT * FROM pgstattuple('frag_demo');
SELECT * FROM pgstatindex('frag_demo_pkey');

Output (executed):

 dead_tuple_percent | free_percent
---------------------+--------------
                0.00 |         49.63

 avg_leaf_density | leaf_pages
-------------------+------------
             60.13 |        820

Only an actual rebuild (REINDEX, PostgreSQL's equivalent of ALTER INDEX ... REBUILD) repacks the pages:

REINDEX INDEX CONCURRENTLY frag_demo_pkey;
SELECT * FROM pgstatindex('frag_demo_pkey');

Output (executed):

 avg_leaf_density | leaf_pages
-------------------+------------
             90.00 |        547

before, after = 6_758_400, 4_513_792  # index size in bytes, before and after REINDEX
print(f"index size dropped {(1 - after/before)*100:.1f}%, from {before/1024/1024:.2f} MiB to {after/1024/1024:.2f} MiB")
print(f"leaf pages: 820 -> 547 (density is back to the table's own fillfactor, 90%)")

Output (executed):

index size dropped 33.2%, from 6.45 MiB to 4.30 MiB
leaf pages: 820 -> 547 (density is back to the table's own fillfactor, 90%)

REINDEX CONCURRENTLY (available since PostgreSQL 12) is PostgreSQL's non-blocking equivalent of an online rebuild: it builds a new index alongside the old one and swaps them, so it needs roughly double the index's space temporarily, mirroring SQL Server's own space requirement during an online rebuild, and it cannot be run inside an explicit transaction block. For a full table rewrite rather than just an index, the pg_repack extension does the same "build a shadow copy, then swap" trick for the whole table without holding a long exclusive lock, which is PostgreSQL's rough analog to a SQL Server online table rebuild.

For the SQL Server side of this scenario specifically, here is reference syntax for the ONLINE rebuild and default-preferred reorganize (documented, not executed here, since this environment has no SQL Server instance to run it against):

-- default lever: always online, low impact
ALTER INDEX PK_Orders ON dbo.Orders REORGANIZE;

-- reserved for when reorganize isn't enough, or a full stats refresh is wanted anyway
ALTER INDEX PK_Orders ON dbo.Orders
REBUILD WITH (ONLINE = ON, FILLFACTOR = 90, SORT_IN_TEMPDB = ON);

-- the diagnostic query behind the decision. page_count isn't filtered to a specific
-- vendor-documented cutoff here; skip whatever indexes are small enough on your table
-- that mixed-extent allocation and sampling noise make their reported numbers unreliable.
SELECT i.name, ips.avg_fragmentation_in_percent, ips.avg_page_space_used_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'SAMPLED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;

Trade-offs & pitfalls

• Don't schedule blanket rebuilds on a fixed calendar. Correlate maintenance with measured query performance (SQL Server's Query Store A/B comparison, or a before/after pgstatindex reading as shown above) rather than assuming a rebuild helped just because it ran.
• REBUILD's roughly 2x space requirement is a real operational risk on a 500 GB table, not a footnote; confirm headroom before running one, and prefer partition-level rebuilds over whole-table rebuilds if the table is partitioned.
• A reorganize can fail on a nearly-full data file or filegroup, because it can only allocate its temporary work pages within the same file, not elsewhere in the filegroup, even when the filegroup as a whole has free space.
• Lowering fillfactor "just in case" is a permanent, ongoing cost, not a one-time maintenance decision; only do it where the evidence (recurring fragmentation shortly after a reorganize, a non-sequential leading key with a high page-split rate) actually supports it.
• Fragmentation numbers on very small indexes are frequently not meaningful, and maintaining them rarely reduces the reported fragmentation regardless of how it looks, so it is not worth the resource cost of including them in a maintenance pass.
• Plain VACUUM never fixes index bloat (extra, wasted space left inside an index by updates and deletes, making it physically larger than the live data needs and slower to scan) by itself in PostgreSQL. It reclaims heap space for reuse and removes dead index entries, but as the numbers above show, it can leave the index itself less dense, not more; a genuinely bloated index needs a REINDEX, not just a VACUUM.

Direct answer. For a large star-schema ETL (extract, transform, load) where a big fact table joins frequently against dimension tables, bucketing the fact table (and ideally the dimension tables too) by the common join key lets the join execute without a full shuffle, partitioning by a time or load-date dimension keeps historical maintenance and incremental loads efficient, and sort order within each bucket/partition further improves both compression and join locality within the bucket-to-bucket merge itself.

Structured elaboration.

• Bucketing for join locality. Hashing both the fact table and its most-frequently-joined dimension table into the SAME number of buckets on the shared join key (commonly a dimension's surrogate key) lets Spark or Presto/Trino execute the join as independent bucket-to-bucket merges, avoiding the network shuffle that a naive join would otherwise require, often the single largest cost in a star-schema ETL job at scale.
• Partitioning for maintenance and incremental loads. The fact table is additionally partitioned by a time dimension (load date, or the fact's own event date), which is what makes DAILY INCREMENTAL ETL runs efficient: a nightly job only needs to write (and validate) the current day's partition, not re-touch the entire historical fact table, and downstream queries scoped to a recent date range get partition pruning independent of, and in addition to, the join-locality benefit bucketing provides.
• Sort order within bucket/partition, reducing shuffle further. Within each bucket, sorting rows by the join key (Spark supports declaring a table as both bucketed AND sorted, CLUSTERED BY (dimension_key) SORTED BY (dimension_key) INTO N BUCKETS) turns the bucket-to-bucket merge into an efficient SORT-MERGE join within each bucket, rather than requiring an additional in-memory hash-build step per bucket, further reducing the join's CPU and memory cost on top of the shuffle elimination bucketing alone provides.

Worked example. A fact_sales table (bucketed and sorted by product_key, partitioned by sale_date) joining against a dim_product table (bucketed and sorted by the same product_key, same bucket count): the ETL job's join step for a given day's incremental load reads only that day's fact partition, and for each bucket within it, merges directly against the correspondingly-numbered, pre-sorted bucket of dim_product, no shuffle of either table, and a cheap sort-merge rather than a hash-build, for every bucket. This combination (partition for time-scoped incremental maintenance, bucket-plus-sort for join locality) is the standard, high-performance shape for exactly this kind of recurring star-schema ETL job.

Trade-offs & pitfalls. Bucketing and sorting both add real write-time cost (data must be shuffled once, at WRITE time, to land in the correct bucket, and sorted within each bucket), a cost that pays for itself specifically because the SAME join happens repeatedly (every ETL run, or every downstream query) rather than once; if the join pattern is genuinely one-off or rare, the write-time investment in bucketing and sorting may not be worth it, and a simpler unbucketed layout with a standard shuffle join at query time could be the more efficient overall choice for a rarely-repeated access pattern.

Direct answer

Make the mismatch visible the moment you see it, whether that is after the upstream team is already running behind or as soon as it surfaces during planning itself, and look first for a way to decouple your own delivery from their exact finish order, such as a stub, an adapter, or a feature flag, so you have room to negotiate re-sequencing or reduced scope instead of just waiting to see if the date slips.

Structured elaboration

Surface the mismatch immediately, not once it is a crisis

Whether you discover it because the other team is visibly behind, or because it becomes obvious during a shared planning session, name it out loud right away: here is what we committed to, here is what we now depend on, here is the gap.

Look for a decoupling option before assuming you have to slip

A mock interface, a stubbed API, or a feature flag lets your work continue against a placeholder while the real dependency finishes in parallel, with a defined swap-in point once it is ready.

Negotiate re-sequencing with a concrete ask, not just a complaint

Pointing out that another team is behind invites defensiveness. Proposing a specific way both teams can still hit their dates if two pieces are resequenced invites problem-solving instead.

Communicate consistently to everyone downstream of the decision

Use the same explanation each time: what changed, what the new plan is, and what happens if it changes again.

Set escalation triggers before you need them

Agree upfront on the specific checkpoint, a date or a milestone, at which, if the upstream work still is not ready, the issue escalates automatically to both leads, rather than waiting for the final deadline to find out.

Worked example

Base case: discovered after the upstream team is already behind. A team is building a feature on top of a platform capability, and the platform team is now behind schedule on it. Rather than waiting to see if the platform team catches up, the team builds a lightweight adapter against a mocked version of the interface, so its own work continues. They set an explicit go or no-go checkpoint a week before their real deadline: if the real dependency is not ready by then, they ship against the mock with a manual fallback, and swap in the real dependency once it lands.

Planning-time discovery variant. During a multi-team sprint-planning session, it becomes clear in the room that one team's planned start date for a shared integration depends on another team's work, which is not scheduled to finish until after the first team's own committed date, a mismatch nobody had caught before that meeting. The engineer facilitating the session, in this scenario a DevOps engineer coordinating the shared infrastructure both teams touch, flags the conflict on the spot and proposes re-sequencing right there: the first team starts against a stubbed interface while the second team's work continues in parallel, with the real dependency swapped in once ready. Right after the session, the facilitator sends a short written summary to both team leads and stakeholders using a repeatable communication template: what was found, what was agreed, and what happens if either date slips again. The summary also sets an explicit escalation trigger: if the second team's work is not ready by a named checkpoint date, it escalates automatically to both leads instead of surfacing again only at the final deadline.

Trade-offs and pitfalls

Building a decoupling layer, such as an adapter, a mock, or a flag, costs real engineering time that is wasted if the upstream team finishes on schedule after all. It is worth it when the downside of waiting and being wrong is worse than the cost of building it and not needing it, which is usually true for anything on a hard external deadline.

Escalating too early, before giving the upstream team a real chance to communicate a plan, burns trust and can look like an attempt to shift blame preemptively. Escalating too late removes any options besides slipping the date. Pre-agreed, specific escalation triggers tied to a date rather than a feeling are what keep this from being a judgment call made under pressure.

Direct answer

When a stakeholder acts on a misread model output, I treat it as three separate problems: what actually happened this time, why the interface or documentation allowed that misreading, and what changes so the same mistake cannot repeat quietly. I fix the immediate decision first, then the underlying cause, in that order, because correcting the person without fixing the surface just moves the same failure to the next person who reads the same number.

Structured elaboration

Diagnosing where the misunderstanding came from:

• I reconstruct exactly what they saw: the specific report, dashboard view, or export, including any rounding or formatting, not a description of it from memory.
• I check what the number actually represents (a probability, a percentile, a relative rank) against what was documented at the point where they saw it, since a mismatch there is often the whole story.
• I ask the stakeholder directly what they believed the number meant before I correct them, because their mental model tells me exactly which assumption to fix, rather than guessing.

Correcting the stakeholder without damaging trust:

• I explain using their own example rather than a generic one: the exact score they acted on, what it actually represents, and what a decision consistent with that meaning would have looked like.
• I use one visual, for instance grouping scores into a few plain risk bands with what each band typically means, rather than a wall of statistics.
• I propose a concrete way to recover from the specific bad decision (for example, a follow-up outreach to the group that was incorrectly deprioritized) so the correction comes with a fix, not just a lesson.

Changing the documentation and interface so it does not recur:

1. Add a short, plain-language description directly attached to every exported report: what the number is (a probability, not a guarantee), the range it falls in, and the last time the model was retrained.
2. Change the interface itself, not just the documentation: label the number in plain terms next to the raw value ("estimated risk, not a guarantee"), group scores into named bands instead of showing a bare decimal, and add a short explanation of the main factors behind an individual score, so the person does not have to go find documentation to interpret what they are looking at.

Worked example

A product manager used a churn-risk model's output to stop marketing spend on customers scored as "low risk," treating the score as a firm "do nothing" signal. Churn in that segment rose shortly after. Reconstructing what they saw showed the export displayed a bare decimal like 0.12 with no label, and the model card describing it as a calibrated probability, not a threshold-based flag, lived in a separate document nobody had opened in months.

I walked the PM through their own export: "a score of 0.12 here means our model estimates roughly a 1 in 8 chance this customer churns, not that they are safe. Some of the customers you deprioritized were sitting right at that level." We agreed on an immediate fix, a short reactivation outreach to the deprioritized group, and I changed the export itself to show a labeled risk band ("Low: roughly 1 in 10 to 1 in 20") next to the raw score, plus a one-line note on the model's current calibration date, so the next person reading that same export sees the meaning without needing a separate document.

Trade-offs and pitfalls

• Fixing only the interface without also correcting the specific stakeholder in front of you leaves the earlier bad decision unaddressed; both steps are needed, not one instead of the other.
• Adding too much explanatory text directly into the interface (a full methodology paragraph on every screen) recreates the original jargon problem in a new location; the interface fix should be one short plain-language label, with deeper detail one click away for anyone who wants it.
• Renaming or re-bucketing scores changes how people act on them going forward, so it needs a short heads-up to the whole team that uses the export, not just the person who misread it, or you risk a second, different misunderstanding.
• Blaming the interface entirely can let a real communication gap go unaddressed if the stakeholder also skipped documentation that was genuinely available; the goal is to make the correct reading the path of least resistance, not to absolve every prior explanation of responsibility.

Clarify first
I would ask whether the product manager wants a time-based window or a calendar window. A 168-hour window means the last 7 times 24 hours from each event timestamp. A calendar window means the current day plus the previous 6 calendar days in the store's business timezone. Those are not the same at midnight boundaries.

Implementation choices

• If they want 168 hours, use a timestamp window.
• If they want calendar days, aggregate by date first, then roll up daily counts.

-- Calendar-day version
WITH daily AS (
  SELECT
    store_id,
    CAST(order_ts AS date) AS order_date,
    COUNT(*) AS orders
  FROM orders
  GROUP BY store_id, CAST(order_ts AS date)
)
SELECT
  store_id,
  order_date,
  SUM(orders) OVER (
    PARTITION BY store_id
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7_day_orders
FROM daily;

Boundary example
An order at 2025-01-08 00:05 UTC is inside the calendar-day window for Jan 8 through Jan 2, but in a strict 168-hour window it depends on the exact timestamp cutoff. I would document that choice in the metric definition so analysts get the same answer.

Direct answer
Mentoring someone through their first postmortem means pairing them with a real, ideally low-stakes incident, giving specific feedback on structure and tone rather than just 'good job,' and modeling the blameless framing yourself before expecting them to reproduce it independently.

Structured elaboration

• Pick the right first incident. A moderate-severity, reasonably contained incident is a better first assignment than either a trivial one (nothing to learn from) or a highly political, multi-team, high-visibility one (too much pressure for a first attempt).
• Give feedback on structure. Check whether the timeline is objective and evidence-backed rather than reconstructed from memory, whether root cause is separated from contributing factors, and whether action items are specific and owned rather than vague aspirations.
• Give feedback on tone, with concrete examples. Point out any sentence that names a person rather than a system gap, and show, don't just tell, how to rewrite it: 'the engineer forgot to run the checklist' becomes 'the checklist has no automated enforcement, so a required step could be skipped.' Seeing the before-and-after side by side teaches the skill faster than an abstract rule.
• Have them facilitate a real meeting, with you as backup, not the lead. Reading about facilitation and doing it live under mild pressure are different skills; be present to redirect gently if the discussion drifts toward blame, but let them run it.
• Follow up on whether the action items actually happened. Closing the loop on whether their first postmortem's action items got implemented and verified teaches the full lifecycle, not just the writing exercise.

Worked example
A junior engineer is assigned to lead the postmortem for a minor, contained caching bug that caused stale data for about ten minutes. Before the meeting, the mentor reviews their draft timeline and flags one sentence ('the developer pushed an untested change') to rewrite as a system-focused observation about the deploy process lacking a required test gate for cache-invalidation logic specifically. During the meeting, the junior engineer facilitates; the mentor stays quiet unless the discussion drifts, at one point gently redirecting a comment that started to focus on who wrote the original caching code. Afterward, feedback covers three things: the timeline was strong and evidence-based, the action item ('add a test for cache-invalidation edge cases') was specific and well-owned, but the root cause and contributing factors weren't clearly separated in the writeup, which is worth practicing next time. Three weeks later, the mentor checks whether the test was actually added and merged, closing the loop rather than treating the writing exercise as the end of the mentorship.

Trade-offs and pitfalls
The most common mistake is giving only high-level praise or criticism ('good postmortem' or 'needs work') without specific, actionable examples the person can apply next time. A second is the mentor taking over facilitation when things get slightly awkward instead of letting the mentee work through it with light support, which prevents them from actually building the skill.

Direct answer

A cost spike is first an investigation problem, find out what actually changed and why, and only then a controls problem. Get a fast, structured root-cause read from the cloud billing breakdown itself, put in immediate guardrails that stop the bleeding without freezing legitimate work, then build governance, tagging, lifecycle rules, quotas, and alerts, so the next spike is caught in hours instead of discovered a month later on an invoice.

Structured elaboration

Investigate first. Start from the billing breakdown for the services involved, Amazon S3 storage and Amazon Athena or BigQuery query costs, split by usage type, project, or tag, and find what actually grew, storage volume, query volume, or data scanned per query, versus what stayed flat. Cross-reference when the spike started against anything that changed at that time: a new pipeline, a changed schedule, a query that lost a partition filter and started scanning a full table. The goal is a specific culprit, a named job that started doing a specific wrong thing on a specific date, not a vague sense that usage went up.

Apply short-term controls fast, without breaking legitimate work. Pause or throttle the specific identified offender, a runaway job, a query firing more often than intended, rather than a blanket freeze on everyone. Put a hard budget alert or quota on the specific project or account where the spike originated, so it cannot silently continue while the root cause is still being confirmed.

Build long-term governance so this does not just recur. Tagging: every resource tagged by team, project, or purpose, so cost is attributable to an owner instead of aggregated into one number nobody can trace. Lifecycle policies: data that ages out of expensive storage tiers automatically, moving cold data to cheaper storage classes or deleting genuinely unneeded data on a schedule, rather than accumulating forever by default. Quotas: a cap on spend or on scanned-data volume per team or project, so a single runaway job hits a ceiling instead of running unbounded. Cost alerts: thresholds that fire on a rate of change, spend rising sharply week over week, not only an absolute dollar figure, since a slow, steady creep past a fixed threshold is often the actual problem a simple threshold alert misses.

Tools and key metrics: the cloud provider's own cost or billing explorer as the primary source of truth, query-engine metrics like data scanned per query and query count, since these two usually explain most warehouse-side cost spikes far more than raw storage alone, and a per-team cost dashboard built from the tagging above, reviewed on a regular cadence rather than only when a bill looks wrong.

Worked example

Monthly Amazon Athena costs roughly tripled in a single month with no obviously changed usage pattern reported by any team.

The billing breakdown showed data scanned per query, not query count, had grown sharply, meaning individual queries were reading far more data rather than more queries running. Cross-referencing the date the spike started against recent deploys turned up a scheduled reporting job that had recently lost its date-partition filter during a refactor, causing it to scan entire tables instead of the prior day's partition on every run.

The short-term control was pausing that specific job within hours of being identified, and setting a budget alert on the project's Athena spend just above the pre-spike baseline, so a different job causing the same class of problem would be caught immediately rather than at the end of the month. The root cause was fixed by restoring the missing partition filter, and data scanned per run was verified back at its prior level before the schedule was re-enabled.

For governance going forward, a required cost-center tag was added to every new query or job so future spend was attributable by team, a lifecycle policy was added moving data older than 90 days into a cheaper storage tier, a per-team quota was added on data scanned per day, and the cost alert threshold was changed to fire on a week-over-week percentage increase rather than only an absolute dollar figure, since the original spike had crossed no fixed threshold until it had already been running for weeks.

Trade-offs and pitfalls

The most common failure is reacting to a cost spike with a blanket freeze on all queries or all storage growth, which stops legitimate work along with the actual problem and creates a second crisis. A second is fixing the immediate cause and skipping governance, so the same class of mistake, a missing partition filter, an untagged resource, recurs later with no faster way to catch it. A third is setting cost alerts only on absolute thresholds, which catches a spike that jumps a fixed line but misses the more common pattern of a slow, compounding creep that takes weeks to become obviously wrong.