DoorDash Business Intelligence Analyst (Senior Level) - Comprehensive Interview Preparation Guide

Missed dashboard service-level agreements (SLAs) from concurrent ad-hoc load is fundamentally a resource-contention problem, so the fix has to work at more than one layer: reduce how much compute each query needs, control how many queries compete for the same compute at once, and give critical dashboard queries priority over exploratory ones.

Warehouse sizing and workload isolation

Separate the warehouse or compute pool that serves dashboards from the one analysts run ad-hoc queries against, even if they read the same underlying tables. This is the single highest-leverage change: a runaway analyst query can no longer starve the dashboard's compute, because they are not sharing a resource pool. Size the dashboard-serving pool for its actual peak concurrent load, not its average.

Query queuing and prioritization

Within the ad-hoc pool, use the warehouse's workload-management or queueing feature to cap how many heavy queries run concurrently and to prioritize shorter, cheaper queries over long-running ones, so one large exploratory query does not block ten fast ones behind it.

Result caching

For the specific dashboard queries that repeat frequently with the same parameters, a result cache (warehouse-level or BI-tool-level) means the second and subsequent identical requests in a short window cost nothing, directly reducing concurrent load on the shared compute.

Sandboxes for exploratory work

Give analysts a genuinely separate, smaller compute environment for exploration (a personal or team-scoped virtual warehouse, or a sampled subset of the data) so their normal working pattern does not require touching the production dashboard pool at all.

Policy layer

The technical layers above only work if an explicit policy governs how they get used, and the policy half matters as much as the technical half:

• Prioritization rules: write down which query classes win contention by default (typically: scheduled dashboard-refresh queries outrank ad-hoc analyst queries), so the queueing/prioritization feature above is enforcing a real, agreed rule rather than an arbitrary default nobody chose.
• Sandbox access and quotas: define who gets a sandbox by default (all analysts, or by request), what compute and data it is scoped to, and a lightweight escalation path for a genuine one-off need to run something larger, so the sandbox is neither a rubber stamp nor a bottleneck.
• SLA ownership and monitoring: name an explicit owner for the dashboard SLA rather than leaving it to "the team," and define what breach threshold triggers action versus what is normal variance, so a slow week does not go unnoticed until users start complaining.
• Cost/chargeback visibility: make the isolated pools' cost visible to whoever owns the budget decision, so the isolation trade-off below (real infrastructure cost) stays a conscious, periodically revisited choice rather than a one-time provisioning decision nobody looks at again.

Trade-offs and pitfalls

Isolating pools adds real infrastructure and cost (you are provisioning compute you might otherwise have shared), and if the dashboard pool is sized too conservatively you have just moved the queueing problem from "analysts vs dashboards" to "dashboard queries queueing against each other" during a real traffic spike. Roll this out incrementally: separate pools first (the cheapest, highest-impact change), measure whether SLA breaches drop, and only add queueing/prioritization policy on top if contention remains within a single pool.

Direct answer
A recurring 30-minute 1:1 works best with a light, predictable structure (a quick check-in, blockers, a skill or growth item, and a career or forward-looking question), but the real skill is protecting the last two from being crowded out by whatever operational fire is loudest that week, and shifting the balance of the agenda as the relationship matures over the quarter.

Structured elaboration

A default structure for 30 minutes

Guarding against the common failure mode

A well-known failure pattern: the 1:1 happens reliably every week, on time, with all the segments technically present, but the career and growth segments become shallow ritual ("anything on your mind for growth?" "nope, all good") while blockers quietly eat the real time. The fix isn't just having a slot on the agenda, it's asking a specific, forward-looking question each cycle rather than an open-ended one, and being willing to occasionally protect that segment even when there's a real blocker competing for the time.

Diagnosing what's actually going on, not just tracking status

Part of the value of a recurring 1:1 is using it to figure out whether a struggle you're observing is a skill gap or a mindset or behavioral issue, because the two need different responses. Someone who's struggling because they don't yet know how needs teaching and practice; someone who's struggling because of avoidance, overconfidence, or a mismatch in how they're approaching the work needs a more direct conversation about the pattern itself, not more technical instruction. A 1:1 is a good place to probe for which one you're actually looking at before assuming.

An alternative structure for hands-on technical work

For roles where the most valuable use of the time is genuinely technical, a 1:1 doesn't have to follow the career-conversation template at all. Structuring it around live debugging together, walking through a real problem with explicit hypotheses ("I think it's X, here's how we'd check") and tracking which ones got ruled out, can be a more valuable use of 30 minutes than a generic status-and-goals agenda, especially early in a relationship when trust and technical credibility are still being built.

Evolving the structure over a quarter

• Early on, more of the time typically goes to blockers and establishing trust; the person needs to know the meeting is safe and useful before career conversations will be genuine rather than performative.
• As confidence builds, the balance should shift toward growth and forward-looking conversation, and the blockers segment should shrink because there's simply less friction to clear.
• If that shift isn't happening by mid-quarter, that's itself a signal worth naming directly rather than just continuing to run the same agenda.

Worked example

Situation

Early in a mentoring relationship, our 1:1s were almost entirely blockers: real, legitimate ones, but every week's slot filled up before we got near growth or career topics.

Action

I made an explicit change: reserved the last five minutes for a specific forward-looking question every time, stated as a fixed rule rather than something to get to if there was time, and moved lower-urgency blockers to async channels so they didn't have to consume the live time by default.

Result

By partway through the quarter, the ratio had genuinely shifted: blockers took less of the time because fewer new ones were coming up, and the growth and forward-looking segments started generating real, substantive conversation instead of the same shallow "all good" answer each week.

Trade-offs & pitfalls

• Mistaking a full agenda for a working one. Hitting every segment on the template doesn't mean the 1:1 is actually working if the career and growth segments are consistently shallow.
• Applying the same generic structure to a technical, debugging-heavy role. Forcing a career-conversation template onto a context where live technical problem-solving would be more valuable wastes the time on both sides.
• Not distinguishing skill gap from mindset issue. Responding to a mindset or behavioral pattern with more technical coaching, or the reverse, burns the time without addressing what's actually going on.
• Never revisiting the structure. A rigid agenda that never evolves as the mentee matures signals the relationship isn't actually progressing, even if the meeting keeps happening.

Direct answer

A statistical sampling plan for manually auditing a large dataset when validating every record is too costly needs an explicit sampling method (random versus stratified), a defined stratification scheme if the population is heterogeneous, a sample size computed for a target confidence level and margin of error, and a method for extrapolating sampled findings to estimate total error across the full population, with an honest accounting of the uncertainty in that extrapolation.

Structured elaboration

• Random versus stratified: simple random sampling is appropriate if the population is reasonably homogeneous with respect to the error being audited for; stratified sampling (sampling proportionally within known subgroups like transaction size or region) is more appropriate when the error rate plausibly varies across subgroups, since a random sample could, by chance, under-represent a subgroup with a meaningfully different error rate and bias the overall estimate.
• Choosing strata: pick strata based on dimensions you have reason to believe correlate with error likelihood, not arbitrary categories; for financial transactions, size (large transactions may warrant more scrutiny given their outsized impact) and region (differing local processes may introduce differing error patterns) are common, well-justified choices.
• Sample-size estimation: for a target confidence level and margin of error on an estimated proportion (like an error rate), the standard sample-size formula is $n = \frac{Z^2 \cdot p(1-p)}{E^2}$ where $Z$ is the z-score for the target confidence level (1.96 for 95%), $p$ is the assumed true proportion (use the conservative $p=0.5$, which maximizes $p(1-p)$ and therefore the required sample size, if no prior estimate exists), and $E$ is the target margin of error. For the 95% confidence, 2% margin-of-error target used in the worked example below: $n = \frac{1.96^2 \times 0.5 \times (1-0.5)}{0.02^2} = \frac{3.8416 \times 0.25}{0.0004} = \frac{0.9604}{0.0004} \approx 2{,}401$ records, meaning roughly 2,401 records need to be sampled (before any stratification split) to hit that target.
• Extrapolation and weighting: for stratified sampling, the sampled error rate within each stratum is weighted by that stratum's actual share of the total population when extrapolating to an overall error-rate estimate, not simply averaged across strata unweighted, since an unweighted average would implicitly and incorrectly treat every stratum as equally sized.

Worked example

Auditing a population of financial transactions stratified by size (small, medium, large) and region (three regions), sampling proportionally within each of the nine resulting strata to hit an overall 95% confidence, 2% margin-of-error target. If the large-transaction stratum, despite being only 5% of total transaction count, shows a meaningfully higher sampled error rate than the small and medium strata, the size-weighted overall estimate correctly reflects that this small-but-higher-error stratum contributes disproportionately to total dollar-value risk, an insight a simple unstratified random sample would likely have missed or underweighted entirely, since it might easily undersample the numerically-small large-transaction stratum by chance.

Trade-offs and pitfalls

The most common mistake in practice is computing a sample size formula correctly but then reporting the resulting error-rate estimate without also reporting its margin of error and confidence level, presenting a point estimate ("we found a 1.2% error rate") as if it were an exact measurement rather than an estimate with real, quantifiable uncertainty around it. The honest version of this finding always carries its confidence interval alongside the point estimate, and any decision made from it should account for the width of that interval, not just the point estimate itself.

0–2m — Clarify objective and constraints

• Ask: target market (country/region), product definition, timeline, success metric (revenue, users, reimbursement), budget/constraints, data availability. Confirm interviewer priorities.

2–8m — Structure the problem (6m)

• Use a 3-part framework: Market Attractiveness, Go-to-Market Feasibility, Financial Viability.
• Subdivide each: TAM & growth, customer segments & channels, competition & regulations, cost structure & pricing, data/analytics needs.
• State key hypotheses and required data points.

8–16m — Analyze / quick calculations (8m)

• Rough TAM: population × segment share × willingness-to-pay (use public proxies).
• Unit economics: price − variable cost = contribution margin; multiply by expected penetration.
• Build 2-3 scenario sketches (conservative / base / aggressive).
• Translate to dashboard needs: required KPIs (CAC, LTV, churn, conversion funnel).

16–20m — Synthesize and next steps (4m)

• Present recommendation tied to metrics, top 3 risks, and 90-day data plan (which dashboards, A/B tests, data sources).
• Ask for feedback / missing inputs.

Frameworks to mention: TAM/SAM/SOM, 4Ps (product/price/place/promotion) adapted for channels, Unit Economics & Cohort Funnel, RICE for prioritization.

Sanity checks under time pressure:

• Population arithmetic check (round numbers, check units).
• Compare implied revenue per customer to industry benchmarks.
• Breakeven months = CAC / monthly contribution — ensure realistic.
• Cross-validate penetration rate against similar product rollouts (e.g., 0.5–5% first-year typical for non-viral B2C).
• Check data consistency across sources (e.g., web traffic vs. claimed market size).

This approach showcases BI strength: rapid hypothesis-driven quant work, KPI-focused recommendations, and clear dashboard/testing plan to de-risk the entry.

Direct answer

Find the shared goal underneath the surface disagreement, both sides usually want the launch to succeed, they disagree on what risk is acceptable to get there. Then convert the abstract tension into a concrete, time-boxed trade-off (what ships now versus what's deferred), with clear ownership of whatever risk gets accepted.

Framework

Reframe before negotiating. Name the actual shared objective (a successful launch) instead of letting the conversation stay framed as one function's priority against another's.

Make the trade-off concrete. Lay out a short options list showing what changes at each risk-versus-speed level, and the cost of each option. Where possible, propose a phased release, ship a reduced-risk version now, defer the rest, rather than forcing an all-or-nothing choice.

Assign ownership of the accepted risk. Whoever accepts a shortcut, for example skipping a test cycle or deferring hardening, should be named explicitly, so the decision isn't 'the team decided' with no accountability attached.

Other shapes this same tension takes. It doesn't always surface as engineering-stability-versus-speed. The identical negotiation shows up as design, performance, accessibility, and time-to-market trade-offs, for example a fully accessible, polished interaction versus a simpler version that ships on the marketing date, and as security, network, and product integration-deadline trade-offs, for example a security or network team wanting a longer hardening pass before a product integration ships, against a fixed launch date on the product side. The mechanism doesn't change across these framings: name the shared goal, make the trade-off explicit and time-boxed, and assign ownership of the risk that's accepted.

Worked example

Situation: engineering wanted an additional hardening and testing pass before a release; the business side had a customer commitment tied to a fixed date, eight weeks out.
Action: convened both sides and reframed the disagreement as 'how do we hit the date without an unacceptable stability risk', not engineering against the business. Broke the release into a smaller core scope that could pass full testing within the eight weeks, with the higher-risk pieces deferred to a fast-follow. Named engineering as the owner of the go/no-go call on stability for the core scope, and named the business side as the owner of communicating the phased scope to the customer.
Result: the reduced-risk core shipped on the committed date, and the deferred piece landed two weeks later with no incident. Because the trade-off was explicit and time-boxed rather than a vague 'we'll be a bit more careful', both sides could tell their own stakeholders exactly what was decided and why.

Trade-offs and pitfalls

• Treating this as a one-time negotiation, rather than designing a recurring mechanism such as a standing risk-versus-release framework, means the same fight repeats at every deadline.
• Splitting the difference without being explicit about what's actually being risked satisfies no one and hides the real trade-off from both sides.
• The senior version of this answer describes redesigning the choice so it isn't zero-sum, the phased release, not describing how you convinced the other side to give in.

Direct answer

At this scale the schema decision and the platform decision are not separable steps, they constrain each other directly: choosing a slowly-changing-dimension (SCD) strategy sets how many rows you write per day, which sets your storage and compute footprint; choosing a partitioning scheme sets how much of that footprint any given query has to scan, which sets your latency and cost per query. Design them together: a star schema (fact table at a declared grain, surrounded by conformed dimensions, customer dimension as SCD Type 2) fed by change-data-capture (CDC) for the tables that must be near-real-time and nightly batch for everything else, landing in a tiered storage layer (hot/recent partitions on fast storage, cold/historical partitions on cheaper storage), with compute kept separate from storage so query concurrency scales independently of data volume, and a pre-aggregation layer in front of the raw fact table so the thousands of concurrent business intelligence (BI) dashboards never hit it directly.

Structured elaboration

Dimensional modeling approach. Declare the fact table's grain first (for example, one row per order line item), because every other decision downstream, join fan-out, storage volume, which dashboards the table can support, follows from it. Use SCD Type 2 for dimensions where history matters for reporting (customer, product); this is the single biggest lever on write volume at this scale, which the worked example below quantifies. Use conformed dimensions across every fact table so cross-process reporting is safe.

Ingestion pattern. Not every table needs the same freshness: use log-based change-data-capture for the tables the business genuinely needs updated within minutes (order status, inventory), and nightly batch loads for slower-moving reference data. Mixing these is normal and expected, forcing everything onto one pattern either over-engineers the slow-moving tables or under-serves the time-critical ones.

Storage tiering and compute/storage separation. Recent, frequently-queried partitions live on the warehouse's primary (fast, more expensive) storage; older partitions move to cheaper object storage, addressed transparently through the same table so a query spanning both tiers does not need special-casing. Keeping compute (the query engine) separate from storage (where the data lives) is what lets you scale the two independently: a spike in concurrent BI users needs more compute, not more storage, and a spike in ingested data volume needs more storage, not necessarily more compute.

Pre-aggregation strategy. With a few thousand concurrent BI users refreshing dashboards every minute, the raw partitioned fact table is not what most of those queries should hit: pre-aggregate the handful of metrics that drive most dashboard traffic into materialized summary tables refreshed on a cadence the business can tolerate (for example, every five minutes), and reserve direct fact-table access for the smaller volume of genuinely ad-hoc analyst queries.

Where the schema and the platform choice constrain each other. The SCD Type 2 choice increases row count, which increases the amount of data any given partition or pre-aggregation job has to process; the partitioning scheme then determines whether that extra row count actually costs you at query time (well-pruned partitions mean SCD2's extra history rows are usually excluded by a date filter) or not (a poorly chosen partition key means every query pays for the full history every time). You cannot pick the dimensional model and the physical layout independently and expect the platform to absorb whatever the modeling choice produced for free.

Worked example

Take a 10-million-row customer dimension with a 2% daily attribute-change rate. Compare a full daily snapshot against SCD Type 2:

customers = 10_000_000
daily_change_rate = 0.02
days = 365

full_snapshot_rows_per_year = customers * days                       # 3,650,000,000
scd2_new_rows_per_year = customers * daily_change_rate * days        # 73,000,000
scd2_total_rows_after_year = customers + scd2_new_rows_per_year      # 83,000,000

Executed: full daily snapshotting writes 3,650,000,000 rows in a year; SCD Type 2 writes 73,000,000 new rows in the same year, leaving the dimension at 83,000,000 total rows.

$1 - \frac{83{,}000{,}000}{3{,}650{,}000{,}000} \approx 97.7\%$

That is a 97.7% reduction in rows written, which at roughly 250 bytes/row is the difference between about 913 GB written per year and about 21 GB stored after year one, a direct, quantified consequence of the SCD strategy on storage cost and compute spent maintaining the dimension. Now layer on the partitioning decision: if the fact table is partitioned by event date and a typical dashboard query looks at the last 7 days, that query scans

$\frac{7}{365} \approx 1.9\%$

of the year's partitions regardless of how large the SCD Type 2 dimension it joins to has grown, which is exactly why the schema choice (SCD2 keeps the dimension small relative to a full snapshot) and the platform choice (date partitioning keeps the fact-side scan small regardless of total history) have to be reasoned about together: either one alone leaves cost on the table that the other was supposed to remove.

flowchart LR
  SRC[Source systems] -->|CDC streams| RAW[(Raw bronze storage tier)]
  SRC -->|nightly batch| RAW
  RAW --> XFORM[Transform: conform and apply SCD Type 2]
  XFORM --> STAR[(Curated star schema: partitioned fact and dims)]
  STAR --> COMPUTE{Elastic compute pool}
  COMPUTE --> AGG[(Pre-aggregated materialized marts)]
  AGG --> BI[BI dashboards, thousands of concurrent users]
  STAR --> ADHOC[Ad-hoc analyst queries]

Trade-offs and pitfalls

The most common mistake at this scale is treating pre-aggregation as an afterthought bolted on once dashboards are already slow, rather than a first-class part of the design: retrofitting it later usually means a painful migration of every dashboard's queries. A second common mistake is choosing SCD Type 2 everywhere by default; dimensions where the business genuinely does not care about history (a lookup table of country codes, say) should stay Type 1, because every dimension carrying unnecessary history multiplies join cost across every fact table that references it. Finally, storage tiering only pays off if the partition key used for tiering is the same one the query engine can prune on: tiering by ingestion date while most queries filter by event date defeats both the cost savings and the pruning benefit at once.

Direct answer: For "does a matching row exist in B" checks between two large tables, reach for EXISTS / NOT EXISTS (or the equivalent LEFT JOIN ... IS NULL anti-join) over a plain JOIN or IN/NOT IN. EXISTS returns a boolean per row in A and stops scanning B at the first match, so it never duplicates A's rows even when B has duplicate keys, and it is not vulnerable to the NULL-in-subquery bug that makes NOT IN silently return zero rows. IN with a subquery is usually fine because modern optimizers rewrite it into the same semi-join plan as EXISTS; the real memory risk is a literal IN list built in application code and pasted into the query, not a subquery. Whichever pattern you pick, an index on B's join key is what actually determines whether the query is fast; without one, everything degrades to a scan of B per row of A.

Structured elaboration

Duplicates in B. This is the concrete failure mode that separates EXISTS from a plain JOIN. If B (say, a fraud_events table) has two rows flagging the same user for two different reasons, an INNER JOIN between users and fraud_events returns that user twice, and any downstream COUNT(*) or report built on top silently double-counts them. EXISTS only ever asks "does at least one row match," so the user appears once no matter how many fraud events they have.

The anti-join at scale (removing fraud users). Say you need every user who is NOT flagged for fraud, out of hundreds of millions of users and a smaller but still large fraud_events table where a user can appear more than once. NOT EXISTS and LEFT JOIN ... IS NULL are the two safe shapes; NOT IN is not, because of the NULL trap below. With an index on fraud_events(user_id), both NOT EXISTS and LEFT JOIN ... IS NULL push down to an anti-join plan (hash anti-join or merge anti-join) that touches each fraud_events row once, rather than re-scanning it per user.

The WHERE id IN (subquery) performance failure mode is not what most people think. IN with a correlated or uncorrelated subquery is generally rewritten by the optimizer into the same semi-join plan EXISTS would produce, so on its own it usually is not a memory risk. The failure mode that actually causes memory/parse blowups is different: application code that assembles a literal WHERE id IN (1, 2, 3, ..., 2000000) list and sends it as one giant SQL statement. That list has to be parsed, planned, and often materialized as a hash set client- and server-side, and on some engines a very long literal IN list stops the planner from using an index efficiently at all. The fix is structural, not syntactic: load the id list into a temp table or a values-table join instead of inlining it as literals.

Worked example (executed in DuckDB)

-- A: 3 users. B: fraud flags, with a duplicate on user 2 and one row with a NULL key.
CREATE TABLE A (id INT, name VARCHAR);
INSERT INTO A VALUES (1,'alice'), (2,'bob'), (3,'carol');

CREATE TABLE B (id INT, a_id INT, reason VARCHAR);
INSERT INTO B VALUES (10,2,'chargeback'), (11,2,'velocity'), (12,3,'chargeback');

-- INNER JOIN duplicates bob (two fraud_events rows for a_id=2)
SELECT a.* FROM A a JOIN B b ON b.a_id = a.id ORDER BY a.id;
-- -> (2,'bob'), (2,'bob'), (3,'carol')   -- bob appears twice

-- EXISTS: bob appears once, regardless of how many B rows match
SELECT a.* FROM A a WHERE EXISTS (SELECT 1 FROM B b WHERE b.a_id = a.id) ORDER BY a.id;
-- -> (2,'bob'), (3,'carol')

-- NOT EXISTS / LEFT JOIN ... IS NULL: fraud-free users (both agree)
SELECT a.* FROM A a WHERE NOT EXISTS (SELECT 1 FROM B b WHERE b.a_id = a.id) ORDER BY a.id;
SELECT a.* FROM A a LEFT JOIN B b ON b.a_id = a.id WHERE b.a_id IS NULL ORDER BY a.id;
-- both -> (1,'alice')

Now the NULL trap, verified by adding one unlinked fraud row with a NULL a_id (for example an orphaned or system-generated flag row):

INSERT INTO B VALUES (13, NULL, 'unlinked');

-- NOT IN silently returns ZERO rows for every user once B's result set contains a NULL
SELECT a.* FROM A a WHERE a.id NOT IN (SELECT a_id FROM B) ORDER BY a.id;
-- -> (empty result, even though alice is genuinely fraud-free)

-- NOT EXISTS is unaffected by the NULL row
SELECT a.* FROM A a WHERE NOT EXISTS (SELECT 1 FROM B b WHERE b.a_id = a.id) ORDER BY a.id;
-- -> (1,'alice')   -- correct, matches the pre-NULL result

This is the exact mechanism of the trap: a.id NOT IN (1, 2, NULL, 3, 2) evaluates as a.id <> 1 AND a.id <> 2 AND a.id <> NULL AND a.id <> 3 AND a.id <> 2. a.id <> NULL evaluates to UNKNOWN for every row, and ANDing UNKNOWN into the chain makes the whole WHERE clause never evaluate to TRUE, so the query returns nothing at all, for any user, not just the one connected to the NULL row.

Trade-offs & pitfalls

• Never write NOT IN (subquery) unless you can prove the subquery's column is NOT NULL (a NOT NULL constraint, or you add WHERE a_id IS NOT NULL inside the subquery yourself). Default to NOT EXISTS.
• IN (subquery) itself is not dangerous; a giant literal IN list assembled by application code is. If you find yourself building a list of a million ids in code, push it into a temp table and join instead.
• EXISTS needs an index on B's join column to be fast; without one it is a nested-loop scan of B per row of A, which is the same cost profile a naive JOIN without an index would have.
• If B has duplicates and you actually need columns from B (not just existence), you cannot avoid the JOIN, but you can avoid duplication by picking one B row per A row first (a ROW_NUMBER() or DISTINCT ON) before joining.

Direct answer
A live connection queries the warehouse directly every time someone opens or interacts with a dashboard; an extracted snapshot copies a subset of the data into the BI (business intelligence) tool's own storage on a schedule, and the dashboard queries that local copy instead. Live connections are always current but put ongoing load on the warehouse and scale with concurrent usage; extracts are fast and cheap to query but are only as fresh as their last refresh.

Structured elaboration
Freshness: a live connection shows data as of the moment someone loads the dashboard; an extract shows data as of whenever it was last refreshed, which could be minutes or a day stale depending on the refresh schedule.

Concurrency: a live connection sends a real query to the warehouse for every dashboard view, so 500 people opening the dashboard simultaneously means 500 concurrent queries hitting the warehouse; an extract is queried from the BI tool's own storage, which typically handles concurrent reads far more cheaply and doesn't compete with other warehouse workloads.

Performance: extracts are usually faster to query, both because they're often pre-aggregated or filtered down to just what the dashboard needs, and because they're not subject to the warehouse's current query queue and load from other jobs.

Security: a live connection can enforce row-level security dynamically at query time (a user's permissions are checked fresh on every query); an extract, if it contains data the requesting user shouldn't see, needs its own access controls applied to the extract itself, which is an easier thing to get wrong (an extract built once with the wrong scope stays wrong until someone notices and rebuilds it).

Cost: a live connection's cost scales with usage (more viewers, more warehouse queries, more compute cost); an extract's cost is mostly fixed (the periodic refresh job), largely independent of how many people view the resulting dashboard.

Scalability: extracts scale better to a large number of viewers precisely because they've decoupled dashboard-viewing load from warehouse-query load.

Worked example
Scenario one favoring an extract: a company-wide dashboard viewed by 2,000 employees a few times a week, where the underlying data only changes once a day. A live connection here would mean up to 2,000 concurrent warehouse queries at peak, an unnecessary cost and load for data that's only actually updating once daily; a nightly-refreshed extract serves all 2,000 viewers from the BI tool's own storage at a fraction of the cost, and nobody loses anything meaningful in freshness since the underlying data wasn't changing intraday anyway. Scenario two favoring a live connection: a small operations team of 5 people actively monitoring a metric that changes by the minute during an incident, where staleness of even 15 minutes could mean acting on outdated information; here the low concurrency (5 viewers) makes the warehouse load trivial, and the freshness requirement makes a periodically-refreshed extract actively wrong for the use case.

Trade-offs and pitfalls
A common mistake is defaulting to live connections everywhere because 'always current' sounds strictly better, without accounting for what that costs at real concurrency: a live-connected dashboard that was fine with 10 viewers during testing can quietly become a warehouse-load problem once it's rolled out company-wide and viewed by thousands. The reverse mistake, defaulting to extracts everywhere for the performance and cost benefits, risks stakeholders making decisions on stale data without realizing it, especially if the extract's refresh schedule isn't clearly surfaced on the dashboard itself; a dashboard should always make its own data freshness visible (a 'last updated' timestamp), regardless of which approach is used, so nobody mistakes an extract for live data.

Definitions:

• 1NF: atomic values, single-valued cells.
• 2NF: 1NF + no partial dependency on a composite PK (each non-key attribute depends on whole PK).
• 3NF: 2NF + no transitive dependencies (non-key attributes depend only on PK).
  Example violating 2NF:
  Table: order_product(order_id, product_id, product_name, product_price, quantity)
  Sample row: (1001, 200, "Widget", 9.99, 2)
  Here product_name/price depend only on product_id (part of composite PK), causing partial dependency.
  Transform to 2NF:
• order_items(order_id, product_id, quantity) -- PK (order_id, product_id)
• products(product_id, product_name, product_price) -- PK product_id
  Now product attributes live in products and order_items stores only quantity. This removes partial dependencies and redundancy.

Situation: At my previous company I was building a weekly executive dashboard showing "monthly active users (MAU)" and a new stakeholder in Growth insisted we should count any user who loaded the app in the last 30 days — including bots and API-only service accounts — arguing it would show our top-of-funnel reach.

Task: I needed to resolve whether to adopt their definition (which inflated MAU by ~12%) or keep a stricter product-definition that excludes bots and service accounts so leadership could track real user engagement.

Action:

• I ran reproducible SQL queries from our event warehouse to show the gap: total unique user_ids in last 30 days vs unique user_ids after filtering out known bot signatures, service-account flags, and sessions shorter than 3 seconds.
• I produced a data lineage diagram showing where user_type flags originate and the confidence level for each source.
• I simulated downstream impacts: churn rate, retention cohorts, ARPU calculations, and an example executive slide showing how the inflated MAU would mask a 6-point drop in 30-day retention.
• I met with the stakeholder, presented the evidence, and proposed a compromise: present the product MAU as the primary KPI (clean definition) and include a secondary "raw reach" metric that transparently includes bots with a note on limitations.

Result: Leadership adopted the compromise. The clean MAU became the official KPI for performance reviews, avoiding misinformed decisions. The dual metrics improved transparency; Growth used the raw reach for marketing experimentation while Product used clean MAU for retention work. This reduced misaligned initiatives and prevented two costly campaigns that would have targeted perceived growth based on inflated numbers. I learned to pair clear data proofs with pragmatic reporting options to align stakeholders.