Amazon Business Intelligence Analyst (Staff Level) - Comprehensive Interview Preparation Guide

Direct answer

When a small number of superusers dominate an average behavioral metric, the average stops representing a typical user, so robust reporting means leading with metrics less sensitive to extreme values, such as the median or a specific percentile, and reporting alternative cuts like top-decile share separately rather than folding everything into one mean.

Structured elaboration

A mean is pulled arbitrarily far by a small number of extreme values because it weights every unit of activity equally regardless of who generated it; a median is far more robust because it only cares about the middle of the distribution's ordering, not the magnitude of the extremes. For behavioral data specifically, a useful complementary pair is the median (what a typical user looks like) alongside a measure of concentration, such as what share of total activity comes from the top decile of users, since that number directly answers "how dependent is this metric on a small group" in a way neither the mean nor the median does alone. When stakeholders need to see the effect of a change on power users specifically, reporting a percentile (p90 or p99) alongside the median gives visibility into that population without letting it distort the headline number.

Worked example

import numpy as np
np.random.seed(5)

n = 1000
activity = np.random.poisson(5, n).astype(float)          # most users: modest activity
superuser_idx = np.random.choice(n, size=int(0.02 * n), replace=False)   # top 2%
activity[superuser_idx] += np.random.gamma(shape=2, scale=150, size=len(superuser_idx))

print('mean:', round(activity.mean(), 2), ' median:', round(np.median(activity), 2))
top_decile_share = activity[np.argsort(activity)[-100:]].sum() / activity.sum()
print('top-decile share of total activity:', f"{top_decile_share:.1%}")
print('superuser mean:', round(activity[superuser_idx].mean(), 1),
      ' non-superuser mean:', round(np.delete(activity, superuser_idx).mean(), 2))

Simulating 1,000 users' weekly event counts, where most users generate a modest, Poisson-distributed amount of activity but the top 2% (20 users) are superusers with much higher activity, gives a mean of 12.24 events per user but a median of only 5.00, a mean over twice the median. The top 100 users (the top decile) account for 65.5% of all activity in the simulation, and the 20 identified superusers alone average 361.8 events each, compared to 5.11 for everyone else. Reporting "average weekly engagement is 12.24 events" without qualification would meaningfully overstate what a typical user actually experiences, since more than 90% of users generated fewer events than that average.

Trade-offs and pitfalls

The main pitfall is reporting only the mean, or only the median, since each hides something the other reveals: the mean alone hides how unrepresentative it is of a typical user, and the median alone hides how much of the metric's total the tail contributes, which matters for questions like revenue or infrastructure cost that scale with total activity rather than with a typical user's activity. A second pitfall is treating superusers purely as a reporting nuisance to smooth away, when in practice they are often the most valuable and highest-retention segment of the user base, worth understanding and serving deliberately rather than only excluding from headline metrics.

Direct answer

I would decide each quarter with a written, repeatable process: measure the team's real supply, ask every line to submit the same one-page proposal, rank by expected value per person-week, reserve a slice for longer bets, and publish a decision with a reason and a way to appeal. The defence against push-back is that the rules and scores are visible before the result is known and are applied the same way to every line.

Supply first. Illustrative: 10 people x 12 weeks = 120 person-weeks. Hold 20% (24) for leave, on-call and ad hoc requests (the holdback), leaving 96 allocable.

Intake: each of the five product lines submits the decision it informs, the value range, effort in person-weeks, confidence and the date after which the work is worthless. Asks total 180 person-weeks for 96 of supply.

Scoring

• 72 person-weeks (75% of 96) go by ranked value. Expected value is the estimated value times the confidence that it will materialize; dividing by effort gives expected value per person-week. Proposals are sorted from highest to lowest and funded down the list until the 72 are used; the cut line is the point where they run out.
• 24 person-weeks (25% of 96) go to longer bets chosen by a steering group (line heads' peers plus a finance partner), judged on a multi-year view, competitive positioning (what rivals can already do), and the team's own capacity to build the capability. Both financial value (revenue, cost) and non-financial value (risk reduction, learning) count, with a rule that no bet runs more than two quarters without a checkpoint.

The ranked list (illustrative). Example: A1 is worth about $700k if it works, with 80% confidence, so expected value is $560k; for 14 person-weeks that is $40k per person-week.

Adding each line's funded rows gives the "By value" column: A 14+10 = 24, B 10+8 = 18, C 12, D 12, E 6. The remaining 58 person-weeks of asks are long-bet requests (A 10, B 6, C 12, D 18, E 12). The steering group funds the first slice of the strongest multi-year cases: D 12 of 18 (best fit with the team's capacity and a two-year payoff, staged behind a checkpoint), C 6 of 12, E 6 of 12, and A and B none this quarter.

Keeping it defensible

• Roles: line heads propose, the steering group decides, the team lead recommends and owns capacity, and an executive sponsor breaks ties.
• Publish criteria and weights before scoring, then publish scores and one line of reasoning per decline, including what evidence would change it.
• One appeal per quarter, answered in writing; re-open at week six using the 24-person-week holdback if a result changes the value.
• Show the totals over four quarters so nobody wins by default.

Pitfalls: pure proportional shares reward whoever asks for most; always funding the loudest line trains everyone to escalate; skipping the holdback turns every urgent request into a re-negotiation.

Direct answer
A decision genuinely reflects a stated value, rather than merely being compatible with it, when the value actually changed what you chose to do, not just how you described it afterward. The strongest answers make that causal link explicit: what you would have done differently if the value hadn't been a factor.

Structured elaboration

• Situation and task: the decision point, described briefly.
• The counterfactual test: name what the default, easier choice would have been, and what specifically made you choose differently.
• Action: what you actually did, including who you had to convince or coordinate with.
• Result: the outcome, and ideally a signal that the choice was validated rather than merely feeling principled at the time.

Worked example
Faced with a choice between shipping a quick, directionally useful analysis in time for a decision meeting, or spending an additional two weeks on a more rigorous version, the default and professionally "safer" choice would have been to wait for rigor. Choosing to ship the quicker, clearly caveated version instead, because the business decision had a hard deadline and a rigorous-but-late analysis would have been useless, shows a genuine trade-off rather than a reflexive one. The decision was validated when the more rigorous follow-up analysis, completed afterward, confirmed the same direction, meaning the faster call hadn't cost the business a wrong decision.

Trade-offs and pitfalls
A story where the value and the easy choice happen to be the same thing doesn't actually demonstrate anything, since no real trade-off was made; choose a story with genuine tension in it. Naming the value first and building a story to fit it, rather than the reverse, tends to produce something that sounds rationalized rather than genuine; a genuinely reflective answer usually names the counterfactual without being asked. A result stated only as "and it felt right" is weaker than any concrete validation signal, even an imperfect one.

Goal and hypothesis

• Goal: Reduce 30-day churn among new users by redesigning onboarding.
• Hypothesis (H1): The redesigned onboarding will reduce 30-day churn rate vs. control (current onboarding).
• Null (H0): No difference in 30-day churn.

Primary and secondary metrics

• Primary metric: 30-day churn rate (binary: churned within 30 days).
• Secondary metrics: 7-day activation rate, time-to-first-key-action, NPS at day 14.
• Guardrail metrics (safety): 7-day retention (to detect short-term harm), conversion to paid (if relevant), customer support contacts per user, completion rate of onboarding flow.

Unit of randomization

• User-level randomization at account/new-signup ID (first session) to avoid cross-contamination. Randomize at first-touch to assign persistent bucket.

Sample-size estimation approach

• Baseline churn (example): p1 = 40% (0.40). Target MDE: absolute 4 percentage points (p2 = 36%) → 10% relative reduction.
• Power 80%, alpha 0.05, two-sided. Using two-proportion formula:
  Zα/2=1.96, Zβ=0.84.
  n ≈ [(1.96*√(2p(1-p)) + 0.84*√(p1(1-p1)+p2(1-p2)))^2] / (p1−p2)^2
  With p≈0.40, delta=0.04 → n ≈ 2,336 per arm.
• Adjust for:
  • Expected attrition/invalids: +20% → ~2,800 per arm.
  • Multiple segments/subgroup analysis: inflate further or pre-specify stratified tests.
• If baseline churn is unknown, run short baseline measurement (1–2 weeks) to estimate p and re-calc.

Randomization and stratification

• Stratify randomization on important covariates (e.g., platform iOS/Android/web, geo, paid vs free cohort) to balance and reduce variance.
• Persist assignment in user profile to enforce consistent experience across devices/sessions.

Monitoring and stopping rules

• Pre-specify fixed-horizon test duration based on reaching required sample size; do NOT peek continuously.
• If interim looks necessary, pre-specify alpha spending (e.g., O'Brien–Fleming) with 1–2 interim analyses to control Type I error.
• Safety stopping rules (continuous monitoring allowed for harms):
  • Stop early for harm if a guardrail metric (e.g., 7-day retention or conversion) shows statistically significant degradation beyond a pre-specified threshold (e.g., absolute drop >3 pp with p < 0.01).
  • Stop for futility if conditional power < 20% at interim.
• Use ITT as primary analysis; run per-protocol and complier analyses as secondary.

Analysis plan and metrics calculation

• Primary analysis: compare 30-day churn proportion (two-sided z-test / logistic regression) adjusting for stratification covariates.
• Report absolute and relative lift, 95% CIs, and p-values.
• Pre-register subgroups and multiplicity correction (e.g., Holm-Bonferroni) for multiple secondary metrics.

Treatment of ineligible users and cross-exposure

• Ineligible users: define eligibility before randomization (e.g., internal staff, bots). Exclude them from randomization pipeline. If discovered post-randomization, keep them in ITT analysis but flag and run sensitivity excluding them.
• Users exposed to other experiments:
  • Prefer to prevent overlap by excluding users in conflicting experiments or use orthogonal/factorial designs when possible.
  • If overlap occurs, log exposures and include experiment-exposure indicators in regression to adjust for contamination; run interaction tests.
  • If a high proportion of overlap exists, mark as contamination and consider pausing or redesigning assignment.

Operational considerations (BI role)

• Implement an experiment tracking table: user_id, assignment, timestamp, exposures to other experiments, eligibility flags, and key metric events.
• Build dashboards to monitor metric time series, daily sample counts, balance diagnostics, and guardrail alerts.
• Automate alerts for sample-size achieved, imbalance, or guardrail breaches.
• Document pre-analysis plan and share with stakeholders before launch.

Expected outputs and decision criteria

• If primary metric shows statistically significant reduction in 30-day churn with no meaningful negative impact on guardrails, rollout the redesign.
• If no significant effect or harms observed, iterate on design or revert to control and run follow-up tests.

Direct answer. Implement masking as a policy-driven layer between the warehouse and the BI (business intelligence) tool, evaluated at query time based on the requesting user's role, using format-preserving or reversible transformations rather than blank-out so aggregations still compute correctly and a small privileged group can request unmasking through an audited, separate step. Push enforcement into the database or warehouse layer wherever the platform supports it, rather than the BI-tool layer, because a database-layer control also protects anyone who queries the warehouse outside the BI tool, which a BI-tool-only control cannot.

Design constraints from the requirements

• Role-varying: the same column shows a different value depending on who's asking.
• Query-time: no separate masked copy of the table to keep in sync; masking is computed as part of query execution.
• Aggregation-safe: SUM, COUNT, AVG, and GROUP BY on masked columns must still produce correct results, which rules out simple redaction on numeric columns feeding aggregates. Redacting a salary column to NULL breaks AVG(salary); the transform has to hide the display value without disturbing the value the aggregate function actually reads.
• Reversible for a small privileged group: that group needs the real value back on request, which means format-preserving encryption or deterministic tokenization under a controlled key for that class of column, not a one-way hash.

Database layer versus BI-tool layer

Default to the database layer as the source of truth for enforcement, and treat any BI-tool-level control as a convenience on top, not a substitute, because auditability and coverage, the two properties that matter most for a PII (personally identifiable information) control, only come from the database layer regardless of which client connects.

Architecture

flowchart LR
    A[Warehouse: raw sensitive columns] --> B[Masking policy engine]
    B -->|role check at query time| C[Query result: masked or real value]
    C --> D[BI tool: dashboards, ad hoc queries]
    C --> E[Direct SQL clients]
    F[Privileged unmask request] --> G[Key/token service, audited]
    G --> B
    B --> H[Audit log: who saw what, when]

Privileged users don't hold a standing "see everything" role; they issue a scoped unmask request gated by a separate authorization check and a key or token service, and every request is logged with who, which column, which row, and when, so an auditor can answer "who saw the real value and why" without relying on the requester's own account.

Worked example. A customers table has a phone_number column. The masking policy shows analysts the last four digits with the rest replaced by X (XXX-XXX-1234); a COUNT(DISTINCT customer_id) tile still works because masking operates on the display value, not on the join key, which stays untouched. A fraud analyst with the privileged role calls the unmask function on one flagged row, which decrypts the format-preserving-encrypted value with a key scoped to that role, and the access is written to the audit log with the analyst's identity, the row's primary key, and a timestamp.

Trade-offs and pitfalls

• Column-level masking alone doesn't stop inference: filtering on unmasked quasi-identifiers together (WHERE city = 'X' AND age = 34) can narrow a masked-name row to one person; policy review has to look beyond single-column rules.
• Reversible tokenization makes key management as security-critical as the data itself; losing control of the key is equivalent to losing the masking.
• BI-tool-only masking is faster to ship but creates a false sense of security once anyone with warehouse credentials, a new hire, a data engineer debugging a pipeline, can query around it; that's the most common wrong turn in this design.

Direct answer. Cross-system backpressure spanning brokers, stream processors, and multiple downstream sinks needs the signal to propagate END-TO-END (not stop at the first hop), with each hop able to shed gracefully for non-critical traffic rather than blocking everything uniformly, and explicit fairness rules so one slow tenant or one struggling sink cannot silently starve everyone else.

Structured elaboration.

1. Detection metrics at each hop. Consumer lag (broker to processor), processing-queue depth (within the processor), and sink write latency/error rate (processor to sink) -- each hop needs its OWN local signal, because a slowdown detected only at the final sink gives no early warning at the earlier hops.
2. Propagation mechanism. A slow sink should cause the processor to slow its consumption from the broker, which in turn causes broker-side consumer lag to grow, which (if the broker itself surfaces this signal to producers, e.g., via a token-bucket credit protocol (the broker grants producers a bounded number of send credits, replenished over time) or an explicit throttle-response feedback loop (the broker reports its current pressure and producers adjust their rate accordingly)) should eventually slow the original producer. Each hop's local backpressure signal becomes the trigger for the NEXT hop upstream to apply its own backpressure, rather than each hop independently guessing when to throttle.
3. Graceful degradation for non-critical traffic. Not all traffic deserves equal treatment under pressure: tag traffic by criticality (e.g., a 'critical events' class vs 'best-effort analytics' class) so that under sustained pressure, the system sheds or delays the best-effort class first, preserving throughput for critical traffic. This requires the propagation mechanism above to be QUEUE-AWARE, applying backpressure selectively rather than uniformly.
4. Avoiding unfair throttling across tenants. A single misbehaving or overloaded tenant/producer should not cause the backpressure signal to throttle EVERY producer equally; per-tenant rate awareness (or per-tenant queues) at the point where backpressure is applied prevents one tenant's slowdown from becoming everyone's problem.
5. Operator playbook for incidents. A clear escalation path: which dashboards show the propagation chain end-to-end (so an operator can see at a glance which hop is the original source of pressure, not just where the symptom is loudest), what manual interventions are safe (scaling a specific hop, temporarily shedding a specific traffic class), and what to communicate to stakeholders if degradation is expected to be visible (e.g., delayed non-critical analytics).
6. Guaranteeing durability during the slowdown. Backpressure only avoids data loss if it SLOWS acknowledgment rather than DROPS messages, and that has to be true at every hop: on the Kafka side, producer acks=all (or an equivalent min.insync.replicas setting) means a message is not considered sent until it is replicated, so a producer that is being throttled simply sends less often, never loses an already-acknowledged message; the broker's own retention window then becomes the outer durability bound, since an unread message only survives as long as retention allows, so retention must be sized longer than the worst-case backpressure episode (the same sizing exercise you would run for buffer capacity) or a sufficiently long slowdown can let a consumer's offset fall out of the retention window and lose unread data even though nothing was ever technically 'dropped' by the pipeline itself. On the Spark Structured Streaming side, checkpointing (the offset log plus processed-state) must only advance PAST a micro-batch after that batch's output is durably written, so a restart during a slowdown resumes from the last successfully processed offset rather than either replaying already-written data or skipping unprocessed data. Combined with step 3's priority-lane design, 'graceful degradation' for best-effort traffic means DELAYED, not dropped: it sits in its own durable queue/topic accumulating (subject to the same retention math above), which is what actually keeps the whole design consistent with the 'without data loss' requirement rather than quietly redefining low-priority loss as acceptable.

Worked example. A downstream OLAP sink starts rejecting writes under its own load spike. Detection: sink write-error rate crosses threshold. Propagation: the stream processor, seeing rising write failures/latency to that sink, stops pulling new messages from the broker at its previous rate, which shows up as growing consumer lag on that processor's consumer group. If the broker surfaces consumer-lag-driven throttling to producers (or if producers themselves monitor broker-side lag), the original producers slow their send rate. Meanwhile, the critical-events traffic class continues flowing at full rate throughout (its own dedicated queue/priority lane is unaffected), while best-effort analytics traffic is the one actually delayed, matching the intended degradation priority.

Trade-offs & pitfalls. Building a genuinely end-to-end propagation chain is significantly more engineering effort than local, per-hop backpressure, and the added complexity (priority classes, per-tenant fairness, cross-hop signal propagation) is only worth it once a real incident has shown that local-only backpressure fails to protect what matters -- for a lower-stakes pipeline, local backpressure at each hop independently (without full end-to-end propagation) is a legitimate, simpler starting point. Flink-specific implementations add operator-level knobs (network buffer sizes, operator parallelism, async I/O for external calls) that tune HOW backpressure manifests within one hop, but do not by themselves solve the cross-system propagation and fairness problems described above -- both layers are needed for a genuinely robust system.

Direct answer

Store the raw event timestamp in UTC on the fact table (never convert at write time), and store the local business date/time as a SEPARATE derived attribute computed using the relevant time zone at query or extract, transform, load (ETL) time, keyed off the dimension that determines which time zone applies (the user's or store's location). Never overwrite or discard the original UTC timestamp.

Structured elaboration

• Why keep UTC as the source of truth: local time is a function of UTC time plus a time zone, and time zones (and their daylight-saving offsets) change; if you only store a converted local time and discard the UTC original, you lose the ability to correctly re-derive local time later if the zone or DST rules change, and you can't reliably compare events across different local time zones at all.
• Deriving local business time: join the fact (with its UTC timestamp) to a time-zone lookup (either on the relevant dimension, like store_dim.timezone, or a separate country_timezone table) and compute local time at query or ETL time: event_time_utc AT TIME ZONE store_dim.timezone. This correctly accounts for daylight-saving transitions if the underlying time zone conversion function is DST-aware (most modern SQL engines and time zone libraries are).
• Avoiding double-counting across date boundaries: aggregating "daily" metrics must group by LOCAL date (derived from local time), not the UTC date, or a single business day's activity gets split across two UTC dates near midnight local time, undercounting or overcounting depending on the time zone offset direction. Precompute a local_date_key attribute per event during ETL if this grouping happens frequently, to avoid repeating the conversion in every query.
• Materializing versus computing on demand: for high query volume against this pattern, materialize the local date/time as its own column at load time (computed once) rather than repeating the timezone conversion in every downstream query, which is both a performance optimization and a consistency guarantee (every query using the same precomputed local date, rather than each one potentially implementing the conversion slightly differently).

Worked example

An event occurs at 2026-03-08 06:30:00 UTC. For a user in New York (UTC-5 in early March, before the typical spring-forward DST transition), the local time is 2026-03-08 01:30:00, still March 8th locally. For a user in Tokyo (UTC+9), the same UTC timestamp is 2026-03-08 15:30:00, also March 8th locally, but a different clock time. If instead the event happened at 2026-03-08 03:00:00 UTC, the New York local time (UTC-5) is 2026-03-07 22:00:00, a DIFFERENT calendar date locally than the UTC date, exactly the kind of boundary case that breaks naive UTC-date-based daily aggregation for that user.

Trade-offs and pitfalls

The classic bug is grouping "daily active users" or similar metrics by the UTC date column directly, which systematically misattributes events near midnight to the wrong LOCAL day for every time zone except UTC itself, and does so silently (the query runs fine, the numbers just don't match what a local observer would report). Precomputing and testing the local-date derivation against known DST transition dates for major time zones is worth the ETL investment for any dashboard reporting daily metrics to a geographically distributed audience.

Situation: Our analytics org lacked diversity of thought; hires were technically strong but tended to come from the same backgrounds, which limited creative approaches to stakeholder problems.

Task: As the hiring manager for BI, I needed to redesign our hiring process to reduce bias and increase diverse perspectives while still ensuring technical competence with tools like Tableau/Power BI and SQL.

Action:

• Job descriptions: I rewrote JD’s to focus on outcomes and core competencies (problem framing, stakeholder communication, SQL proficiency) and removed degree requirements and biased phrasing. Added a “what success looks like” section and encouraged transferable experience (e.g., domain expertise, self-taught projects).
• Interview panels: I mandated diverse panels (function, level, gender, background) and trained interviewers on structured interviews and unconscious-bias checkpoints. Panelists had role-based rubrics to score consistently.
• Assessment design: Replaced open-ended take-home projects with a time-boxed, anonymized skills exercise: a 90-minute dataset + ask to build 2 visuals and write 3 insights. Focused on interpretation and communication, not tool-specific formatting. Code/SQL tasks were auto-graded where possible.
• Sourcing: Expanded sourcing to industry meetups, community colleges, bootcamps, and employee referral bonuses targeted at underrepresented groups. Partnered with internal analytics consumers to spot potential nontraditional candidates.
• Metrics: Tracked funnel metrics by demographic (apply → interview → offer → accept), diversity of educational and industry background, time-to-hire, interview score variance, and new-hire retention + impact (stakeholder satisfaction, number of unique domain contributions).

Result: Within 9 months our candidate slate diversity increased 40%, offer acceptance from underrepresented groups rose 25%, and stakeholders reported more creative solutions in quarterly surveys. The structured rubric reduced score variance between interviewers by 30%.

This taught me that measurable, role-aligned changes across JD language, assessments, panels, and sourcing create fairer processes and better business outcomes.

Direct answer: An event-level metric describes individual occurrences (one row per click, one row per page view); a user-level metric aggregates those occurrences up to one row per user (per some time window), answering "how many distinct users did X" rather than "how many times did X happen."

Structured elaboration: Compute at event-level when the question is inherently about volume or rate of occurrences (total page views, click-through rate on a specific banner). Compute at user-level when the question is about reach or behavior of people (DAU, percent of users who converted, average sessions per user). The SQL pattern to go from one to the other is a two-step aggregation: first collapse events to a per-user-per-day flag or count, then aggregate that intermediate table.

Worked example (executed): given clicks(user_id, click_time) with user 1 clicking 3 times on June 1 and once on June 2, and user 2 clicking once on June 1:

WITH daily_activity AS (
  SELECT user_id, date(click_time) AS day, COUNT(*) AS clicks_that_day
  FROM clicks
  GROUP BY user_id, date(click_time)
)
SELECT day, COUNT(DISTINCT user_id) AS daily_active_clickers
FROM daily_activity
GROUP BY day;

Run against a small SQLite table with exactly these rows, this returns (June 1, 2) and (June 2, 1): an event-level COUNT(*) on the same raw data would instead give (June 1, 4) and (June 2, 1), a materially different number for June 1 because it's answering a different question (how many clicks happened, not how many people clicked).

Trade-offs & pitfalls: The most common bug is silently mixing the two grains within one report: e.g., reporting "average clicks per user" as total_clicks / distinct_active_users_this_week when total_clicks was accumulated over a different window than the user count, producing a number that isn't a genuine per-user average for any single period. Always name the grain (event-level count, user-level count, or a ratio of the two over the SAME window) in the metric's label, not just in internal documentation, because dashboard consumers otherwise assume "clicks" and "active clickers" are interchangeable.

Direct answer

Default to deterministic (exact-key) deduplication whenever a stable, trustworthy join key exists, because it is fast, fully explainable, and has zero false-positive risk. Reach for probabilistic (similarity/fuzzy) matching only when no reliable key exists across the records you need to merge, for example customer records arriving from systems with no shared identifier, because it trades perfect precision for the ability to catch real duplicates a key-based approach would simply miss.

Structured elaboration

• Deterministic dedup: match on an exact key (a primary key, a hashed and canonicalized email). Runtime cost is a GROUP BY/DISTINCT/window-function pass, it is trivially explainable to an auditor ("these two rows have the same customer_id"), and it never merges two genuinely different entities. Its failure mode is under-merging: two records for the same real person with typos in the key are left as separate rows.
• Probabilistic dedup: score candidate pairs on similarity across several fields (name, email, phone, address) using a blocking step to avoid comparing every row to every other row, then merge pairs above a tuned threshold. It catches the entity-resolution cases deterministic matching misses, at the cost of needing a labeled sample to tune the threshold, ongoing maintenance as data patterns shift, and a nonzero, tunable false-positive rate (two different people wrongly merged).
• Decision rule: use deterministic dedup as the default first pass on every table. Only add a probabilistic layer where a real business cost is attached to the residual duplicates a key-based pass leaves behind (e.g., double-counted customers inflating revenue-per-customer, or duplicate mail sent to the same household).

Worked example

A retailer discovers that dashboard "unique customers" is inflated because the same person signed up twice with j.smith@gmail.com and johnsmith@gmail.com. Deterministic dedup on customer_id or even canonicalized email finds nothing (the emails are genuinely different strings). A probabilistic pass scoring name similarity ("John Smith" vs "John Smith") at 100, combined with matching phone and shipping address, would surface this pair as a high-confidence merge candidate even though no single field ties them together exactly.

Trade-offs and pitfalls

The single biggest mistake is presenting probabilistic dedup results to stakeholders as if they were as certain as deterministic ones. A responsible answer states the expected false-positive and false-negative rate up front (measured against a labeled sample), keeps a human-in-the-loop review queue for borderline-confidence pairs rather than auto-merging everything, and never silently discards the pre-merge records: an auditable trail of what was merged into what, and why, is what lets a bad merge be reversed later.