DoorDash Business Intelligence Analyst Interview Preparation Guide (Junior Level)

Direct answer

Build a standing information diet from what the partner function already produces for itself, its goals or planning document, the metrics it is measured on, and its retro or release notes, and pair that with a recurring informal check-in with one counterpart in that function. You are not trying to get invited into their planning meeting; you are trying to read what they optimize for, and occasionally confirm your read against a real person.

Structured elaboration

The habit that ties these together: translate their metric into one sentence you could say back to them and have them agree it is accurate, then test that sentence the next time you talk. If you cannot state their current priority in a sentence they would sign off on, your information diet has a gap.

Worked example

Suppose you regularly partner with a support or customer-success function but are not in their planning. Their quarterly goals page (a document they publish for their own team) states the goal is "reduce median response time." Reading that before proposing a change that would meaningfully increase inbound volume lets you flag the likely trade-off to your counterpart ahead of launch, rather than finding out after the fact that you worked against their stated goal. The artifact told you what they were measured on; the counterpart conversation confirmed it was still current.

Trade-offs & pitfalls

• Relying only on artifacts risks reading a goal that is stale or aspirational and no longer reflects what the team is actually prioritizing day to day.
• Relying only on a single counterpart's opinion risks mistaking one person's take for the function's actual priority, especially if that person is not close to how the team's metrics are reviewed.
• A common miss: reading the dashboard but never validating the interpretation with anyone in that function, which produces confidently wrong assumptions that only surface when a decision already went the wrong way.
• The senior differentiator on an easy-sounding question like this is treating it as a standing habit built before you need it, rather than something you scramble to learn only after a conflict has already surfaced.

I prioritize meetings using three lenses: impact, uniqueness of my contribution, and opportunity cost.

Criteria to accept:

• High impact decisions relying on data (roadmaps, quarterly review, product prioritization).
• Meetings where I’m the data SME or asked to present numbers.
• Cross-functional syncs where BI alignment prevents duplicated work (data definitions, metrics council).
• If a stakeholder has escalated a blocker that only I can resolve.

Criteria to delegate:

• Status updates or tactical handoffs where my presence isn’t needed (accept if a direct report or data owner can speak).
• Deep technical implementation sessions unrelated to reporting/metrics (delegate to ETL/engineering partner).
• Recurring meetings with low ROI where I can provide a periodic written update instead.

Delegation rules:

• Pick a delegate with context, brief them on expected outcomes, and grant permission to decide.
• Share an agenda + specific questions they should answer.
• Request a 10–15s post-meeting summary.

Pre-read and preparation to maximize value:

• Review agenda and expected decisions; if missing, ask organizer for objectives.
• Pull key metrics/visuals in advance (1–2 slides/dashboards) and mark known caveats.
• Prepare 1–2 recommended options and the data-backed trade-offs.
• Flag open data/definition risks and proposed next steps.
• If unable to attend, send the visuals + short recommendations and attend the last 5 minutes to align on actions.

This approach keeps me at high-leverage conversations, empowers teammates, and ensures meetings produce actionable, data-driven outcomes.

Approach: precompute cohort membership and monthly activity using nested FIXED LODs so Tableau doesn’t recompute row‑level joins repeatedly. Build two core LODs: one to assign each customer a single cohort month (first purchase) and one to mark monthly activity per customer; then aggregate at cohort × month.

Calculations (Tableau LOD syntax):

1. Cohort month (assigns one cohort per customer)
   { FIXED [CustomerID] : MIN(DATETRUNC('month',[OrderDate])) }

2. Customer-month activity flag (did customer transact in that calendar month?)
   { FIXED [CustomerID],[YearMonth] : MAX(IF COUNT([OrderID])>0 THEN 1 ELSE 0 END) }
   (If using row-level orders replace COUNT with SUM([Quantity])>0)

3. Nested LOD to get cohort-month active customers:
   { FIXED [CohortMonth],[YearMonth] :
   SUM(
   { FIXED [CustomerID],[YearMonth] :
   MAX(IF DATETRUNC('month',[OrderDate])=[YearMonth] THEN 1
   ELSEIF MIN(DATETRUNC('month',[OrderDate])) = [CohortMonth] THEN
   // include repeat purchases in that month
   MAX(IF COUNT([OrderID])>0 THEN 1 ELSE 0 END)
   ELSE 0 END }
   )
   )
   }

(Practical implementation: use CohortMonth calc (1) as a dimension; compute a Customer-Month flag via LOD (2); then at cohort-month level SUM the flag per distinct CustomerID. Avoid double-counting by ensuring the atomic unit is CustomerID×YearMonth and flags are 0/1.)

Performance improvements:

• Push heavy aggregations to the database via a pre-aggregated table or view (SQL) that produces CustomerID, CohortMonth, YearMonth, ActiveFlag — then connect Tableau to that table (extract).
• Use Tableau extracts with incremental refresh, partition by YearMonth.
• Reduce Viz complexity: use a single aggregated measure (ActiveCustomers) and compute retention % = ActiveCustomers / CohortSize.
• Index DB on CustomerID and OrderDate; precompute YearMonth as integer (YYYYMM) to speed grouping.
• Limit scope of default query (top N cohorts) and provide filters to load more.

Heatmap for executives:

• Rows: CohortMonth (most recent at top), Columns: Month offset (0,1,2,... up to 36)
• Cell value: Retention % (ActiveCustomers / CohortSize), color palette from white→brand color; annotations on 0,1,3,12 month benchmarks.
• Show cohort size as a bar or number left of rows; on hover show absolute counts and YoY comparison.
• Include KPI strip above: average 1‑month retention, 3‑month, 12‑month, trend sparkline.
• Use discrete month offsets (0 = acquisition month) to normalize cohorts of different calendar months.
• For readability, cap decimal places, use conditional formatting to call out significant drops (>5% MoM) and tooltips with drivers (channel, product).

Edge cases & validation:

• Exclude test/internal accounts via flag.
• Handle customers with multiple first dates by MIN date LOD.
• Validate by comparing DB pre-aggregate totals vs Tableau aggregates for sample months.

This design ensures single-count customer-month atoms via nested LODs (or, better, pre-agg table), reduces compute in Viz, and presents an executive-friendly heatmap with clear benchmarks and drill-to-detail.

Direct answer: Model each metric once in dbt (or an equivalent metrics layer) as a versioned, tested SQL artifact built on top of incrementally-materialized staging models, with an explicit strategy for late-arriving data (a rolling reprocessing window) and backfills (parameterized full-refresh runs scoped to specific date ranges), so the SAME definition serves near-real-time and audited historical reporting from one source.

Structured elaboration:

• Model design: a layered structure: raw/staging models (lightly cleaned, deduplicated raw events) feed intermediate models (sessionized, identity-resolved) which feed final metric models (the actual named business metrics); each layer is independently testable, and a metric model never reads directly from raw events, keeping the "expensive cleaning" and "business logic" concerns separated.
• Incremental strategy: metric models use an INCREMENTAL materialization that only reprocesses a trailing window (e.g., the last 3 days, wide enough to cover typical late-arriving data) on each run, rather than a full rebuild, while periodically running a full-refresh reconciliation to catch anything the trailing window missed.
• Late-arriving data: the incremental window's width should be set based on measured, observed data on how late data typically arrives (not a guess), and any data arriving LATER than that window is caught by the periodic full-refresh reconciliation, with a documented, monitored lag metric so the team can see if the window's width assumption starts to look wrong.
• Backfills: a parameterized run (e.g., dbt run --vars '{"start_date": "...", "end_date": "..."}') targeting a specific historical range, using the SAME model logic as the regular incremental run, so a backfill is guaranteed to produce results consistent with what the incremental pipeline would have produced, rather than a separate, divergent backfill-specific code path.
• Testing: schema tests (not-null, uniqueness) on staging models, business-logic tests (the golden-fixture pattern from S18) on final metric models, and a freshness test asserting the incremental window is actually being refreshed on schedule.
• Deployment: metric model changes go through the same code-review + CI-test gate as any other code change (S58's registry-enforcement pattern), with semantic_version bumped and documented on any logic change.
• Auditability/lineage: dbt's own lineage graph (which models feed which) doubles as the lineage documentation this topic's registry sub-area calls for, generated automatically from the model dependency graph rather than maintained by hand.

Worked example: migrating a company's ad hoc Tableau-embedded SQL metrics into this dbt-based layer starts by picking the highest-value, most-disputed metric first (often "active users," per the recurring reconciliation theme throughout this topic), building it as a tested, versioned dbt model, and redirecting Tableau to read from that model's output table instead of its own embedded SQL, one metric at a time rather than attempting a single big-bang migration of all metrics simultaneously.

Trade-offs & pitfalls: An incremental window set too narrow silently misses genuinely late data (undercounting until the next full-refresh catches up, which can be days); set too wide, it defeats the purpose of incremental processing by reprocessing nearly as much as a full rebuild would. Tune the window from OBSERVED data-arrival-lag statistics, and monitor it as its own metric so the assumption doesn't silently go stale as upstream systems change.

Direct answer. Walk through your triage process for narrowing down what changed: what evidence you would gather first, the handful of underlying causes that pattern is usually explained by, and how you would confirm which one actually happened rather than guessing.

Structured elaboration. Start by gathering evidence that distinguishes "the plan changed" from "the plan is the same but the work it does grew": compare the current plan to a known-good baseline if one is available (many engines let you capture and diff plans, or at minimum you can re-run EXPLAIN and eyeball the operator shapes), and check whether the DATA changed (row count growth, a new skewed value, a bulk load) independent of any plan change. From there, the most common root causes for this "regressed with no application change" pattern are, in roughly descending order of how often they turn out to be the actual cause: statistics that went stale after a data change (a bulk load or a big delete that wasn't followed by a refresh); a cached plan compiled for an atypical parameter value now being reused for a much more typical, much less selective one (a parameter-sniffing regression); the DATA itself simply growing or shifting distribution past a threshold that used to favor one strategy and now favors another; and resource contention from something unrelated to this specific query (concurrent load, a maintenance job, disk pressure) that's slowing everything down and only looks query-specific because that's the query someone happened to notice first.

To confirm which one actually happened rather than just picking the most common cause and hoping: check statistics freshness and the estimated-vs-actual row counts at the plan's key nodes first, since that single check distinguishes the stale-statistics and parameter-sniffing causes (both would show a mismatch) from the pure-resource-contention cause (which typically wouldn't); if estimates and actuals both look reasonable and current, pivot to checking system-level resource metrics (CPU, I/O, lock waits) for the affected time window instead.

Worked example. A nightly report that quietly regressed after last week's data load added a disproportionate number of rows in one previously-small category is a textbook case: the statistics captured before the load still describe the smaller, more balanced distribution, so the optimizer's row-count estimate for that category is now badly wrong, and a plan built around that bad estimate (likely favoring an index-heavy strategy suited to the old, smaller volume) is now handling a much larger actual volume than it was ever appropriate for.

Trade-offs and pitfalls. It's tempting to jump straight to "just refresh statistics and see if that fixes it" without confirming that's actually the cause; that's a reasonable first cheap experiment to try, but treat a fix that "seems to help" with some skepticism until you've actually looked at the estimated-vs-actual gap directly, since a coincidental unrelated change (contention easing off on its own) can make an unrelated fix look like it worked.

Direct answer

Summarize a high-cardinality categorical column with a frequency table of the top-N values plus an explicit "other" bucket for the long tail, and visualize it as a bar chart of just those top values rather than trying to show all of them at once. Before deciding how to handle it downstream, check whether the column is even predictive by comparing the outcome rate across its most frequent values (a target-rate-versus-frequency view), rather than assuming a high-cardinality column is automatically useful or automatically noise.

Why this approach works

Showing thousands of categories to a stakeholder is not just unreadable, it's actively unhelpful, since almost all of the information a human can absorb is concentrated in the handful of most frequent values anyway. Collapsing the long tail into "other" (with a clear note of how many distinct values and what share of rows that bucket represents) keeps the summary honest about how much is being hidden. For the predictiveness question, plotting each of the top categories' rate against how frequently it occurs surfaces two different concerns at once: whether specific categories look meaningfully different from the baseline rate, and whether the ones that do are common enough to matter or just noisy because they have only a handful of observations.

Worked example

A product_id column has 1.2 million distinct values against a binary purchase target. Grouping by product and looking at the top 20 by frequency, most cluster around a similar purchase rate near the dataset average, but three of them show a purchase rate roughly double the baseline, and all three also have thousands of observations, ruling out small-sample noise as the explanation. That's a genuinely interesting finding worth flagging. A fourth product shows a purchase rate of 100%, but it only has 4 observations total, which is a case of small-sample noise, not a real signal, and shouldn't be reported the same way as the first three.

Trade-offs and pitfalls

The specific downstream handling (hashing, target encoding, grouping rare categories into "other" as a permanent feature-engineering decision) is out of scope for the EDA step itself; the goal here is deciding whether the column deserves that investment at all, not designing the encoding.

Direct answer

A market's underlying attractiveness comes down to how much leverage sits with suppliers and buyers, how many substitutes and new entrants can realistically go after the same demand, and how intense rivalry already is among the players fighting for it. High switching costs, scale advantages, network effects (a product getting more valuable as more people use it), control over a scarce input, or regulatory protection all raise the effective barrier a challenger has to clear before it can take share. Where those forces are weak, expect thin, competed-away margins no matter how good day-to-day execution is; where they are strong, even mediocre execution can sustain healthy profits for a long time.

Structured elaboration

Five forces are worth reasoning through separately, then combining:

• Buyer power: how easily can customers push back on price or switch away? Concentrated, price-sensitive buyers with low switching costs squeeze margins.
• Supplier power: does the company depend on a small number of suppliers (talent, components, data, distribution) who can capture value for themselves?
• Threat of new entrants / barriers to entry: how expensive, slow, or legally restricted is it to start competing from scratch? Barriers include capital intensity, regulatory licensing, brand trust built over years, and access to distribution.
• Threat of substitutes: not direct competitors, but different ways to solve the same underlying need. A product can dominate its narrow category and still lose to a substitute nobody classified as a competitor.
• Rivalry intensity: how many players are fighting for the same customers today, and on what basis (price versus differentiation)? Price-based rivalry among similar products erodes margin fastest.

Layered on top of these forces are moat mechanisms: network effects, switching costs, economies of scale, brand, and proprietary data or technology. A moat is really just a durable answer to "why can't a new entrant clear the barrier-to-entry force easily." The forces tell you the market's shape; the moat tells you whether a specific company's position within that shape is defensible.

The key discipline is combining the forces rather than judging any one in isolation. A market can look attractive on rivalry (few players) but still be unattractive overall if buyer power is enormous (a handful of buyers who can dictate price) or if substitutes make the whole category replaceable.

Worked example

Take budget airlines as a concrete case. Rivalry is intense (many carriers competing hard on price on overlapping routes). Buyer power is high (price-comparison sites make switching trivial, and the product is close to a commodity). Supplier power is also high in a specific way: there are effectively two aircraft manufacturers, so suppliers can dictate terms. Barriers to entry are moderate: gates, aircraft financing, and regulatory certification are real hurdles, but not impossible for a well-capitalized entrant to clear, which is why new low-cost carriers keep appearing. Substitutes exist (rail, driving, video conferencing for some business travel) and cap how much prices can rise even when seats are full.

Combine those: high rivalry plus high buyer power plus high supplier power plus moderate entry barriers is a textbook unattractive market structure, which is consistent with the airline industry's historically thin, cyclical margins even though individual airlines can still out-execute peers on cost discipline or route selection for a period.

Trade-offs & pitfalls

The most common mistake is treating one force as the whole answer, usually rivalry, because it is the most visible ("look how many competitors there are"). A market with few visible competitors can still be unattractive if buyer power or substitute risk is severe. The second mistake is conflating market size with market attractiveness: a large market with weak barriers to entry just means a large number of companies will compete away the profit pool, not that any one of them captures much of it. The third is assuming a moat is permanent once established; the forces (and the strength of a specific moat within them) have to be re-assessed whenever the underlying technology, regulation, or customer behavior shifts, not judged once and filed away.

Direct answer

Reframe the disagreement as a question you can investigate rather than a preference to negotiate: which metric better predicts the retention and revenue outcomes you actually care about, and which one is harder for users (or the tracking itself) to inflate without a real behavior change behind it. Bring both stakeholders into defining the evaluation criteria before running any analysis, then document the winning metric's exact definition, owner, and calculation so the decision does not quietly drift or get re-litigated every quarter.

Structured elaboration

Step 1: align on evaluation criteria before looking at data

Get agreement from both sides on what would make one metric better than the other: predictive power for downstream outcomes (retention, conversion), sensitivity to product changes you actually want it to detect, resistance to being inflated by something other than real engagement, and operational feasibility (can it be computed reliably, does it need special instrumentation).

Step 2: run the comparison

• Correlate each candidate against downstream retention and revenue outcomes, segmented by user cohort, so the comparison is not dominated by one large but atypical segment.
• Check each metric's susceptibility to being inflated by something other than genuine engagement (an idle browser tab left open vs. a person actually returning to the product).
• If feasible, look for a natural experiment or a product change that should move one metric more than the other, and see which movement actually tracked the business outcome.

Step 3: document and govern

Write a short metric specification: exact definition, event sources and filters, calculation logic, owner, and review cadence, and put it in a shared glossary. Assign an owner responsible for revisiting the definition if the product changes in a way that could invalidate it.

Worked example

One concrete piece of the "which metric is easier to inflate without real engagement" comparison: a user opens the product, is actively engaged for 5 minutes, then leaves the browser tab open in the background for another 55 minutes before closing it. Session tooling logs the full open-to-close duration as time on site:

That single idle tab inflates the time-on-site number by a factor of 12 relative to actual engagement, with zero additional product value delivered. Sessions per week does not have this specific failure mode (an idle tab does not start a new session), though it has its own: a user who returns five times in one day to check one thing each time can rack up session count without deeper engagement, which is why the evaluation in Step 1 has to check susceptibility in both directions before picking a winner.

Trade-offs & pitfalls

A metric that wins on predictive power in a backward-looking correlation is not guaranteed to keep predicting well once the org starts optimizing for it directly (Goodhart-style drift: once people can move the number, moving the number and moving the underlying outcome can decouple). Documenting the decision prevents ambiguity but can ossify a definition past its useful life if nobody revisits it after a major product change (a redesign that changes what a "session" even means). The single biggest process risk is skipping Step 1 and going straight to running analyses that each side interprets as vindicating their prior view; agreeing on the evaluation criteria first is what makes the eventual answer something both sides can accept rather than something the loser argues with.

Direct answer

For a non-engineer audience, a small dashboard of 5-8 data-quality KPIs, null rate, schema-mismatch count, duplicate rate, freshness, and service-level agreement (SLA) pass rate as the core set, each shown at the cadence appropriate to how quickly it can meaningfully change, with any composite "quality score" broken down transparently by dimension rather than collapsed into a single opaque number.

Structured elaboration

• Metric selection: pick metrics that map directly to a business consequence a non-engineer would recognize (freshness maps to "is this data current enough to trust for today's decision," schema-mismatch count maps to "did something break upstream"), rather than internal engineering metrics that require pipeline knowledge to interpret.
• Cadence: freshness and schema-mismatch checks change fast and matter in near-real-time, so update them frequently; a duplicate rate or a broader quality trend changes more slowly and is better shown as a daily or weekly aggregate, since updating it every few minutes would just add noise without adding useful signal.
• A composite score, done honestly: if you build a single "quality score," break it down by the underlying dimension driving a low score visibly, right there on the dashboard, rather than presenting one number that could be low for any of several very different underlying reasons; hiding the composition defeats the purpose of a stakeholder-facing dashboard, which is to build trust through transparency, not to manufacture a reassuring single number.

Worked example

A stakeholder dashboard shows six tiles: freshness (updated every 15 minutes, "data as of: 8 minutes ago"), schema-mismatch count (updated in near-real-time, "0 unexpected schema changes today"), null rate (updated hourly, "0.4% of required fields, within normal range"), duplicate rate (updated daily, "0.3% today, within normal range"), SLA-pass rate (updated daily, "97% of checks passed yesterday", meaning 97% of the agreed data-delivery commitments were met), and a composite quality score shown as a simple bar broken into its five contributing dimensions rather than a single unexplained number, so a stakeholder glancing at a lowered score can immediately see it was driven by, say, a freshness dip rather than having to ask an engineer what happened.

Trade-offs and pitfalls

The temptation to collapse everything into one clean composite score is strong because it is simpler to present, but a single opaque number that occasionally drops with no visible explanation actively damages trust in the dashboard over time, since stakeholders learn to distrust a number they cannot interpret or act on. A dashboard's job for a non-engineer audience is not to hide complexity, it is to present the right, limited amount of it clearly, and a composite score that is not decomposable on demand fails that job even if it looks cleaner at first glance.

Direct answer: When two rows tie on every column in your ORDER BY, including the timestamp, ROW_NUMBER() = 1 no longer has a single correct answer: which of the tied rows gets rank 1 depends on the engine's internal row order, which can differ across runs, across a re-execution after a vacuum/reorganize, or across a query plan change, none of which are things you control. The fix is to keep adding ORDER BY keys until no two rows in any partition can tie on all of them simultaneously, ending with a column that is guaranteed unique (a primary key or surrogate id) as the final, unconditional tiebreaker.

Structured elaboration

Why it breaks determinism. ROW_NUMBER() assigns 1, 2, 3, ... to rows within a partition based on the ORDER BY clause. SQL only guarantees a well-defined order among rows that the ORDER BY clause can actually distinguish; rows that tie on every listed key are, per the standard, in an implementation-defined order relative to each other. Two runs of the identical query, or the same query before and after a table reorganization, can legally return a different row as rank 1 even though nothing about the data changed. For a dedup pipeline, that means the 'surviving' row for a given key can flap between runs, which breaks any downstream process relying on a stable choice (a slowly changing dimension load, an incremental pipeline keyed on the surviving row's id, or just a report that should be reproducible).

A concrete multi-criteria tie-break. A common real fix, not just 'add the id': when deduplicating events that arrived from multiple devices for the same user at the identical timestamp, rank by business priority first, THEN by recency, THEN by id: prefer a desktop-sourced event over mobile, and mobile over tablet, before falling back to timestamp, and only fall back to the row id if literally everything else ties.

WITH ranked AS (
  SELECT id, user_id, score, event_ts, device,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY score DESC, event_ts DESC,
        CASE device WHEN 'desktop' THEN 1 WHEN 'mobile' THEN 2 WHEN 'tablet' THEN 3 ELSE 4 END,
        id
    ) AS rn
  FROM events2
)
SELECT * FROM ranked WHERE rn = 1;

Worked example (executed in DuckDB). Two rows for user 1 tie exactly on score (90) and event_ts (09:00:00): one from mobile (id 2... shown as id in output), one from desktop. A third row for the same user has a lower score.

-- ORDER BY score DESC, event_ts DESC only (no further tiebreak):
id | score | event_ts | device  | rn
1  | 90    | 09:00:00 | mobile  | 1   <- arbitrary: this engine happened to rank id=1 first
2  | 90    | 09:00:00 | desktop | 2
3  | 80    | 08:00:00 | tablet  | 3

-- with device-priority + id added as final tiebreak:
id | score | event_ts | device  | rn
2  | 90    | 09:00:00 | desktop | 1   <- deterministic: desktop always wins this tie now

Adding the device-priority CASE and the id column changes which row wins the tie from 'whatever the engine happens to return first' to a fixed, explainable, always-reproducible answer: desktop over mobile over tablet, with the surrogate id as the final fallback if even device somehow ties.

DISTINCT ON as an alternative (Postgres and DuckDB). DISTINCT ON expresses the identical intent more compactly on engines that support it: pick one row per key, ordered by the same tiebreak chain, without a separate ROW_NUMBER/filter step.

SELECT DISTINCT ON (user_id) id, user_id, score, event_ts, device
FROM events2
ORDER BY user_id, score DESC, event_ts DESC,
  CASE device WHEN 'desktop' THEN 1 WHEN 'mobile' THEN 2 WHEN 'tablet' THEN 3 ELSE 4 END, id;

Run against the same data, this returns the identical winning row (desktop, id matching the ROW_NUMBER = 1 result), confirming the two formulations agree once the ORDER BY chain is fully deterministic. DISTINCT ON is not portable (Postgres and DuckDB support it; SQL Server, Snowflake, and BigQuery do not), so ROW_NUMBER() = 1 remains the more broadly applicable pattern.

A related but distinct angle: time-window duplicates instead of exact-key duplicates. Not every 'duplicate' shares an identical key. If two events for the same user within, say, 5 seconds of each other should be treated as one duplicate click even though their timestamps differ slightly, exact-key PARTITION BY doesn't apply at all; that's the gaps-and-islands technique instead (bucket by a rounded/truncated timestamp, or flag a new group whenever the gap from the previous event exceeds the tolerance), not a tie-break question. Worth naming explicitly: 'duplicate' meaning exact-key collision and 'duplicate' meaning near-in-time collision are two different problems that happen to both get called deduplication.

Trade-offs & pitfalls

• A hash of the full row's content is another valid final tiebreaker when there's no natural priority order, but it must be computed over stable, deterministic functions only; never include random(), now(), or any non-deterministic expression in a tiebreak chain, or you reintroduce the exact nondeterminism you were trying to remove.
• Document the tiebreak precedence somewhere visible (a comment in the query or a data-quality doc); a reviewer six months later has no way to infer 'why does desktop always win' from the CASE expression alone.
• Adding more ORDER BY keys costs a marginally more expensive sort, not more scans; it's essentially free compared to the cost of nondeterministic output in a pipeline anyone depends on.