DoorDash Staff-Level Business Intelligence Analyst Interview Preparation Guide

There's no universal winner here: a lakehouse consolidates a lake and a warehouse into one platform with transactional guarantees on top of cheap object storage, while keeping them separate lets each system specialize. The decision comes down to how much your organization actually needs unified governance and reproducible pipelines versus how much it benefits from best-of-breed, purpose-built tools on each side.

Where the trade-offs actually show up

Consistency and reliability. A lakehouse adds transactional guarantees (atomic commits, isolation between concurrent writers, versioned snapshots) directly on top of the lake's files, so the same storage that used to be 'append and hope' now behaves more like a database table. A separate lake plus warehouse gets this reliability only on the warehouse side; the lake itself is still just files, so anything reading raw lake data directly inherits the lake's weaker guarantees.

Operational complexity. Two systems means two things to operate, two places for metadata to drift apart, and an ETL (Extract, Transform, Load) or ELT (Extract, Load, Transform) layer whose only job is keeping them in sync. A lakehouse removes that sync step, but it shifts the operational burden onto managing one more sophisticated system well: table maintenance, metadata scaling, and query-engine tuning become your responsibility (or your platform vendor's) instead of being split across two mature, narrower products.

Cost. Object storage underneath a lakehouse is cheap, and compute is decoupled from it, so storage cost stays low even as history accumulates. A separate warehouse usually costs more per byte stored because it's optimized for query performance, not just cheap persistence. Whether that matters depends on how much of your data is 'hot' (queried constantly, where warehouse performance pays for itself) versus 'cold' (rarely touched, where cheap lake storage wins).

Tooling and team skills. A managed warehouse's ecosystem (business intelligence, or BI, connectors, workload management, query optimizer) is mature and requires less specialized tuning. A lakehouse built on open table formats gives you more control and avoids vendor lock-in, but your team needs to actually understand things like compaction and file layout to get warehouse-like performance out of it.

Worked example: how you'd actually decide

A useful decision framework, applied to a mid-size company weighing a lakehouse against replacing (or supplementing) an existing warehouse: start from where the trade-offs show up, not from a platform preference.

• If most of your workload is well-known BI reporting on structured data, and your team is small, a managed warehouse alone is probably the pragmatic choice: you get performance and low operational overhead for the workload you actually have.
• If you increasingly need both governed BI numbers and large-scale ML feature engineering off the same raw history, and you're willing to invest in the operational skill to run it well, a lakehouse removes the duplicate-copy, duplicate-pipeline problem that a two-system setup creates.
• A common middle ground, and the one most organizations actually land on, is a hybrid: raw and semi-structured data lives in the lake (or lakehouse bronze layer), and a smaller, highly curated warehouse (or gold layer) serves the numbers the business depends on daily. This keeps the blast radius of 'the analytics platform is down' small and contained to the curated layer.

Trade-offs and pitfalls

The biggest pitfall is picking the lakehouse for its architectural elegance without budgeting for the operational skill it actually requires: an under-tuned lakehouse (poor file sizing, no compaction discipline) can be slower and less reliable than a plain warehouse, which erases the cost advantage once you factor in the engineering time spent firefighting. The second pitfall is the opposite: keeping two systems 'because that's how we've always done it' long after the sync overhead between them has become the single largest source of data inconsistency complaints.

Direct answer
Comparing BI (business intelligence) platforms honestly means describing real production use, not marketing feature lists: what you actually used a given tool for, roughly what scale it handled, and at least one genuine limitation you had to design around, since every mainstream BI platform can technically build a dashboard, the interesting differences show up under real production constraints.

Structured elaboration
What to cover for a platform you've genuinely used: typical use cases (was it the tool for executive-facing polished dashboards, or the tool analysts used for fast ad-hoc exploration, since many organizations run more than one BI tool for different jobs rather than one tool for everything), data sources and volume it handled (did it connect live to a warehouse or rely on extracts, and at what data scale did it stay performant versus start to strain), scheduling and refresh capabilities actually relied on (not just what's technically supported, but what you actually configured and how reliable it was in practice), and one real limitation, something specific enough to show you actually hit it in production, not a generic complaint.

What makes this a strong comparative answer versus a shallow one: naming the SPECIFIC limitation and what you did about it (worked around it, escalated to the vendor, chose a different tool for that specific need) rather than a vague "it has some limitations," and being honest that different tools are genuinely better suited to different jobs rather than claiming one tool is simply superior across the board, which reads as either inexperience or marketing-speak rather than real production judgment.

Worked example
A candidate describes two tools from real experience: one used primarily for governed, executive-facing dashboards, where its strength was a centralized semantic layer that kept metric definitions consistent across dozens of dashboards, but a real limitation was that building a genuinely custom, complex visualization outside its standard chart library required awkward workarounds, which pushed the team toward a second tool for that specific need. The second tool was used by analysts for fast ad-hoc exploration against a live warehouse connection, valued for its flexibility and quick iteration, but a real limitation surfaced at scale: with enough concurrent users running unbounded ad-hoc queries, warehouse contention became a genuine operational problem (connecting to the workload-isolation discipline) that required deliberate query-cost guardrails to manage, a limitation that wasn't obvious from initial evaluation and only showed up once usage grew past a certain point.

Trade-offs and pitfalls
A common weak answer here lists every feature a tool has without ever describing how it was actually used or what broke down in practice, which signals the candidate evaluated the tool's marketing material rather than operated it in production; the specific limitation, and especially what was actually done in response to it, is what demonstrates real hands-on judgment. It's also worth being careful not to overcorrect into pure tool-bashing: the goal is an honest, balanced account (what worked well AND what didn't), since a purely negative answer about every tool you've used raises its own question about whether the limitation was really the tool's fault or a mismatch in how it was being used.

Direct answer

I treat the old system's real behaviour as a set of hidden requirements and recover it from three directions: the code and jobs, the outputs, and the people. Then I classify each recovered behaviour as a genuine business rule to keep, a workaround for a defect to fix at the source with sign-off, or something obsolete to retire explicitly. Each kept behaviour becomes an automated acceptance test, and cutover (the moment users switch from the old system to the new one) only happens after a parallel run (running old and new side by side) proves the new outputs match.

How to surface what the system really does

1. Inventory the consumers. List every downstream report and who relies on it, and what decision each supports. Prioritise by impact if wrong.
2. Trace lineage (where each number came from and every step it passed through) from the report backwards. Read the queries, scheduled jobs, stored procedures (saved database programs that run queries) and spreadsheets that sit between source and report. Note every filter, join, hard-coded value and special case.
3. Compare outputs, not just code. Capture the legacy outputs for several past periods (this is sometimes called a golden-master or characterization test: you record what the system does today, right or wrong, as the baseline). Run the new pipeline on the same inputs and investigate every difference. Each difference points at hidden logic.
4. Interview the people who apply manual fixes. Ask: "Walk me through month-end. Show me the spreadsheet you use. What do you correct by hand, and why? What do you check before sending? Which numbers do you not trust?" Ask them to share a screen and do a real run (watching work happen exposes steps people forget to mention). Ask "tell me about the last time the report was wrong."
5. Record what you find in a manual-fix register.

How to protect the behaviours during replacement

• Write each kept behaviour as an acceptance test (a plain-language check of whether a behaviour is delivered, in Given/When/Then form): "Given the same customer appears in both source systems, when the monthly revenue is calculated, then it counts once."
• Run both systems in parallel for an agreed number of periods that includes a month-end and a quarter-end, and reconcile outputs (compare them and explain every difference) within an agreed tolerance. Example tolerance (illustrative): money totals match exactly, derived ratios within 0.1 percent, and any larger difference is investigated before cutover.
• Get a named owner to approve each classification, especially retirements. Nothing disappears silently.
• Cut over only on stated criteria (all kept behaviours covered by passing tests, differences explained, rollback plan ready).

Variation: refactoring an internal API with undocumented behaviour

The same approach applies when teams depend on an internal API's undocumented behaviour (sometimes described as Hyrum's law: with enough users, every observable behaviour gets relied on). Find the unstated dependencies by searching consumer code and call logs, ask each consuming team what they rely on beyond the documentation, then convert the findings into contract tests (automated checks that the API still behaves the way its consumers rely on) that run before any change ships.

Given a consumer calls GET /orders with no sort parameter
When the response arrives
Then orders are listed oldest first

This ordering is in no documentation, but a billing job relies on it. If a refactor changes it, the test fails before the change ships.

Interview techniques and artifacts for undocumented workflows

• Contextual walkthrough (watch the person do the real task on their own screen).
• "Last time it went wrong" questions, which surface exceptions.
• Artifacts: a workflow map with the manual steps marked, the manual-fix register above, and a list of acceptance tests traced to each behaviour.

Worked example (illustrative numbers)

The legacy revenue report for last quarter totals 1,000,000. The new pipeline, run on the same data, gives 1,012,000. Investigating the 12,000 difference shows duplicate orders, caused by customers that exist in both source systems, that analysts delete by hand each month. That rule goes into the register, becomes a test, and is encoded in the pipeline. The rerun now matches. Only then is the next difference examined. The 12,000 gap is 1.2 percent of 1,000,000. Because the stated tolerance for money totals is exact, any gap on this total must be explained; the 0.1 percent band (1,000 on this total) applies only to derived ratios, and even if a team chose to allow it on this total, 12,000 would be twelve times that band. Likewise, if a derived ratio such as a churn rate differed between the two systems by 0.5 percent (relative), that is five times the 0.1 percent tolerance and would block cutover until explained. The figure 5,000 would apply only to a 0.5 percent gap on the 1,000,000 total, which the exact-match rule for money totals already blocks. In this example the duplicates are the same kind of record as the register's duplicate customers: the analysts delete duplicate customer records, which removes their duplicated orders from the total.

Trade-offs and pitfalls

• Copying every legacy behaviour also copies its bugs; classify before encoding.
• Parallel runs cost time and compute. Limit them to the reports with real impact.
• Interview notes alone are not protection: only tests that fail when the behaviour breaks protect it.

Direct answer

Telling the difference between genuine alignment and stakeholders who are simply not objecting out loud comes down to watching what they DO after the meeting rather than what they said IN it, and actively creating low-risk opportunities for disagreement to surface, rather than assuming a quiet room means agreement.

Structured elaboration

1. Silence is not evidence of agreement. In many group settings, disagreement is socially costly to voice, especially for less senior or more soft-spoken participants; treating a quiet room as confirmation is one of the most common and costly misreads in stakeholder management.
2. Watch post-meeting behavior. Genuine alignment shows up as people acting consistently with what was agreed; quiet disagreement shows up as slow follow-through, requests to "revisit" a decision soon after it was made, or work quietly diverging from what was agreed.
3. Create explicit, low-risk space for dissent. Directly asking "what's the one thing about this that concerns you" to specific individuals, rather than a general "any objections," and following up privately with quieter participants after a group session, surfaces concerns a public forum suppresses.
4. Watch for a specific tell: enthusiastic agreement with vague follow-up questions. Genuine alignment tends to produce specific, detail-oriented questions about implementation; quiet non-agreement often produces polite approval with unusually few follow-up questions, since there's little incentive to engage deeply with a decision you don't actually support.

Worked example

After a kickoff where everyone nodded along to a proposed plan, following up individually with the quietest participant, who asked no questions during the meeting, surfaces a real, unstated concern about a dependency they didn't feel comfortable raising in front of the group. Their public silence had been read as agreement; a private, low-pressure follow-up revealed it wasn't.

Trade-offs and pitfalls

Over-interpreting every silence as hidden disagreement can create unnecessary anxiety and extra process for genuinely aligned situations; use this as a targeted check for high-stakes decisions and quieter participants specifically, not a blanket suspicion applied to every meeting.

Streaming dedup at scale comes down to a trade-off between memory, latency, and how much false-negative/false-positive risk you can tolerate, and the right answer usually blends more than one technique.

The options

1. Exact dedup with a durable keyed state store: for every event id you've seen, keep a record in state (RocksDB-backed state in Flink/Spark Structured Streaming, or an external store like Redis). Guaranteed correct (zero false positives or negatives) but state grows unbounded unless you bound the window you're willing to remember.
2. Hash-based approaches: hash the event's content (not just its id) to dedup near-identical payloads even without a shared id field. Useful when upstream systems don't guarantee a stable id, but adds hashing compute and doesn't help if two legitimately different events happen to hash-collide (rare, but non-zero for weak hashes).
3. Bloom filters for approximate dedup: a probabilistic structure that can say "definitely not seen before" or "possibly seen before" using far less memory than exact state. The trade-off is explicit: false positives are possible (an event gets wrongly treated as a duplicate and dropped), false negatives are not (a genuine duplicate is never wrongly let through).
4. Time-windowed dedup: bound the durable state to a rolling window (say, the last 24 hours of ids), on the assumption that duplicates arriving further apart than that are rare enough to accept. This bounds memory growth for options 1 to 3 rather than being a separate technique on its own.
5. Dedup across restarts: whichever mechanism you pick, its state has to survive a job restart, either via a state backend with checkpointing (Flink/Spark's own fault-tolerant state) or by re-deriving state from a durable external store on startup, otherwise a restart silently resets your dedup window and lets through duplicates you'd already filtered.

When approximate dedup (Bloom filters) is actually acceptable
When the cost of a rare false-positive drop is genuinely low relative to the memory savings, most commonly for high-volume telemetry or engagement events feeding an aggregate metric, where losing an occasional event has negligible impact on the aggregate, versus a payments or billing pipeline, where a false-positive drop means a real transaction silently vanishes and Bloom filters are the wrong tool regardless of memory pressure.

Memory vs. latency, concretely: exact state stores cost more memory per unique key but impose essentially no extra latency per event (a state lookup). Bloom filters cost far less memory but need periodic maintenance (rotating or resizing as the filter fills up and its false-positive rate climbs), which is an operational latency/complexity cost rather than a per-event one. The realistic production pattern for high-volume, loss-tolerant streams is Bloom filter as a cheap first-pass filter, with an exact check as a fallback for anything the Bloom filter flags as "possibly seen," which gets you most of the memory savings with none of the false-positive risk.

A warehouse-level result cache, BigQuery's automatic result cache is the canonical example, stores the exact output of a previously executed query, keyed by the query's text (and, in most implementations, the state of the underlying tables at execution time), and serves that stored result instead of re-running the query the next time an identical query is issued, as long as none of the tables it read have changed since the cached result was produced.

How it interacts with table updates

The cache is invalidated per-query the moment any table the original query touched changes, and it separately carries its own upper-bound time-to-live independent of table changes: BigQuery's automatic result cache, for example, holds a cached result for roughly 24 hours and also invalidates it immediately if any referenced table changes before that window closes, whichever happens first. A new insert, update, or delete to any dependency invalidates every cached result that depended on that table, even a result that was otherwise perfectly fine, and even a result over tables that never change still eventually falls out of cache once the TTL elapses. This means result-cache freshness is exact (you will never see a result that is definitely stale relative to the table's current state) but its hit rate on a table that updates frequently, even in ways unrelated to what a specific dashboard displays, can be much lower than expected.

Designing to benefit from it

• Keep dashboard queries byte-for-byte identical across users and page loads where possible (same column order, same literal formatting, same filter representation), since most result-cache implementations key on the query text, not its semantic meaning, so two queries that return the same rows but are phrased differently will not share a cache entry.
• Separate frequently-updated tables from the tables a dashboard's expensive aggregation actually depends on, where the data model allows it, so that an unrelated update elsewhere in the warehouse does not needlessly invalidate a cached dashboard result.
• Combine with a materialized view or pre-aggregated table for the truly high-traffic dashboards, so the result cache is caching a cheap read of an already-small pre-aggregated table rather than a full scan-and-aggregate, which keeps even a cache miss relatively cheap.

Trade-offs and pitfalls

Relying on the result cache alone for freshness guarantees is fragile: it silently serves a fresh answer when nothing changed and silently falls back to a full, potentially expensive recomputation the instant anything changes, with no visible degradation warning to whoever is watching dashboard latency. Do not treat the cache hit rate as something you can directly control; design the query patterns and table update cadence around it instead, and treat it as a free accelerant on top of a materialized-view strategy, not a substitute for one.

Direct answer
Implement a drill-down (country to region to city to customer, or a similar hierarchy) using a consistent BI-tool pattern: nest the hierarchy in the data model, preserve the parent level's filter context at each step (breadcrumbs, not a reset), and pre-aggregate the common rollup levels so each transition stays fast even as the hierarchy gets deeper.

Structured elaboration

1. Implementation in a BI tool: define the hierarchy explicitly in the semantic/data model (e.g. country > region > city > customer) so the tool can auto-generate drill actions, rather than building four separate unrelated charts a user has to manually cross-reference.
2. Preserving filter context: when a user drills from country to region, the region-level view should automatically inherit the country filter (and any other active filters like date range), shown via a breadcrumb trail ("USA > West > San Francisco") so the user always knows their current context and can navigate back up without losing state.
3. Performance: pre-aggregate the higher levels of the hierarchy (country, region) as materialized views or extracts, since those are queried far more often than the deepest level; the lowest level (customer, or a sales-stage funnel's individual opportunities) can query live since it's accessed less frequently and by fewer users at once.
4. Preserving links/bookmarks: support a direct, shareable link or bookmark to a specific drilled-in view (e.g. a specific region's dashboard state) so a user can return to or share that exact context without re-navigating from the top every time.
5. Sales-stage drilldown variant: the same pattern applies to a funnel-stage drilldown (overall funnel to a specific stage's drop-off detail), where breadcrumbs and back-links preserve the funnel-stage and time-range context as the user moves from a high-level view to opportunity-level detail.

Worked example
A sales dashboard where clicking "USA" on a country-level map filters and navigates to a region-level view showing a breadcrumb "USA >", clicking "West" further filters to a city-level view ("USA > West >"), and clicking a city opens customer-level transaction detail, with each step's URL bookmarkable and directly shareable.

Trade-offs and pitfalls
A drilldown that resets filters at each level (losing the country or date-range context when moving to region) forces the user to re-apply filters repeatedly and is one of the most common usability complaints about poorly-implemented drill interactions.

Direct answer

returns_fact at one-row-per-returned-line-item grain, referencing product_dim, customer_dim, date_dim (return date), original_order_date_dim (a role-playing use of the date dimension for the original purchase date), and return_reason_dim, chosen at line-item grain because return reasons and refund amounts are typically tracked per item, not per whole order.

Structured elaboration

• Grain choice reasoning: returns are usually partial (a customer returns 1 of 3 items from an order), so order-level grain would lose the ability to attribute a specific return reason or refunded amount to the specific item involved; line-item grain preserves that detail and lets order-level return rates be derived by aggregating up, not lost by aggregating down.
• Five dimensions: product_dim (what was returned), customer_dim (who returned it), date_dim used twice via role-playing (the return date and the original purchase date, letting queries measure time-to-return), and return_reason_dim (defective, wrong size, changed mind, etc.), each a natural, independently-useful dimension for a returns analytics workload.
• Measures: refund_amount, quantity_returned, both additive across product, customer, and time, supporting straightforward SUM-based dashboards.

Worked example

returns_fact(return_line_id, product_key, customer_key, return_date_key, original_order_date_key, reason_key, quantity_returned, refund_amount). A "average days to return, by reason" report computes AVG(return_date - original_order_date) GROUP BY reason_key, directly answerable at this grain without needing to reconstruct partial-order detail from a coarser table.

Trade-offs and pitfalls

The main design risk is choosing order-level grain because it seems simpler, then discovering the business's real questions (which specific products get returned most, for which reasons) can't be answered without line-item detail, forcing a rebuild. Line-item grain costs a bit more storage and slightly more complex order-level rollup logic, but is the safer default for a returns-specific fact table given how naturally partial most real-world returns are.

Direct answer

Build sales orders first, because it touches the most shared dimensions and forces you to get the conformed ones right while the stakes are lowest; conform customer and date starting with that very first mart, since both are used by all three processes and retrofitting them later means re-touching every mart already built. Marketing campaigns and support tickets can follow in either order once those two dimensions exist.

Structured elaboration

A bus matrix lists business processes as rows and the dimensions each one uses as columns, with a mark where a process uses a dimension. It exists to answer exactly this question: given limited capacity to build marts one at a time, which dimensions have to be conformed from day one so that the second and third marts can simply reuse them, instead of each mart inventing its own version that has to be reconciled later.

Reading the matrix: customer and date are marked for all three processes, so they are the dimensions that MUST be conformed starting with the first mart built, whichever one that is. product and campaign and support agent are each used by only one process, so they can be built process-specific for now without creating a future conflict.

Which mart first. Sales orders is the natural first mart here: it uses three dimensions (customer, date, product), the most of the three processes, so building it first forces the team to design customer and date properly (with the attributes and grain that will actually generalize) while there is only one mart depending on them. Building a narrower mart first (support tickets, using only two dimensions) risks designing customer too narrowly for what marketing or sales will later need from it.

Order for the rest. Once customer and date are conformed from the sales-orders build, marketing campaigns and support tickets can be built in either order: each one only needs to reuse the existing customer and date dimensions and add its own process-specific dimension (campaign, support agent). Neither remaining mart's build order affects the other's correctness, because they do not share a dimension with each other that they do not already share with sales orders.

Worked example

Suppose support tickets were built first instead, and its team modeled customer narrowly, keyed only by the email address on the ticket, with no attributes for billing account or acquisition channel. When sales orders is built next and needs customer to support revenue-by-acquisition-channel reporting, the acquisition-channel attribute does not exist on the dimension support tickets already published, so sales either has to extend the existing dimension (risking breaking the support mart's existing reports if the extension is done carelessly) or build a second, incompatible customer dimension (recreating the exact fragmentation the bus matrix exists to prevent). Building the widest-touching mart first avoids this by making the team design the shared dimension for its eventual full set of consumers, not just its first one.

Trade-offs and pitfalls

The most common mistake is treating the bus matrix as a one-time planning document rather than a living contract: once customer is conformed, every future mart's team has to be held to using it as-is (or proposing a reviewed extension), not quietly forking their own version because the shared one is missing one attribute they want. A second mistake is over-conforming too early: campaign and support agent do not need to be designed for hypothetical future reuse before any second process actually needs them, since guessing wrong about a dimension nobody ends up sharing wastes the same design effort the matrix is meant to save.

Direct answer. When a metric move turns out to be driven by illegitimate traffic (a suspected bot-driven spike, or a jump in active users unmatched by revenue), the investigation looks for behavioral and technical fingerprints that distinguish automated or fraudulent activity from genuine demand, since a bot-driven anomaly usually looks statistically unusual in ways real user growth doesn't.

Structured elaboration. Behavioral signals: session-duration distributions (bots often show unnaturally uniform or unnaturally short session lengths compared to the normal, more variable human distribution), events-per-session outliers (an account generating far more actions per session than is behaviorally plausible), and timing patterns (activity spread with suspiciously even, non-human regularity across a day, or concentrated in a narrow burst). Technical signals: IP concentration (a disproportionate share of 'new' activity originating from a small number of IP addresses or address ranges), and user-agent entropy (a suspiciously low diversity of device or browser signatures relative to a normal, organically diverse user base). Validation before recommending mitigation: cross-check that the suspected fraudulent segment does NOT show the downstream behavior real users show (if 'active users' spiked but revenue, or any harder-to-fake engagement signal, didn't move proportionally, that mismatch itself is corroborating evidence, not just the initial trigger for suspicion).

Worked example. Active users jump 20% in a week while revenue stays flat. Checking session-duration distribution for the incremental users shows an unusually large cluster of sessions lasting almost exactly the same short duration, far more uniform than the organic user base's naturally varied session lengths. IP-concentration analysis shows a meaningful share of these sessions originate from a small number of IP ranges associated with data-center hosting rather than typical residential or mobile ISPs. Both signals together, plus the fact that revenue didn't move with the user count, are strong enough evidence to flag the incremental users as very likely automated traffic rather than real growth, and to recommend filtering or challenging that segment before it further distorts downstream metrics and any decisions based on them.

Trade-offs and pitfalls. Any single signal alone (a slightly unusual session-duration cluster, a modest IP concentration) is weak evidence on its own and could have an innocent explanation (a legitimate but unusual traffic source, a corporate NAT concentrating many real users behind few IPs); the case for a fraud/bot conclusion should rest on MULTIPLE converging signals plus the downstream-metric mismatch, not any one signal in isolation, and mitigation should be proportionate to that confidence level.