Microsoft Senior Data Engineer Interview Preparation Guide 2026

Direct answer

Straggler tasks (most finish quickly, a few take an order of magnitude longer) have four common root causes: data skew, garbage collection (GC) pauses, disk or network I/O contention, and resource starvation from co-located work; the Spark UI's per-task shuffle-read size, GC time, and executor-host columns, read together, usually distinguish between them without needing to guess.

Structured elaboration

Diagnostic order. Open the Stages tab for the slow stage, sort the task list by duration descending, and cross-reference each slow task's Shuffle Read Size, GC Time, and Host/Executor ID columns against the fast tasks in the same stage:

Data skew. Confirmed by shuffle-read-size variance (a small number of tasks reading far more shuffled bytes than the rest) plus, often, disk spill on exactly those tasks. Mitigations: salting the skewed key (add a random suffix to spread one hot key across multiple partitions, then aggregate the sub-results), switching to a broadcast join if the other side is small enough to avoid the shuffle entirely, and enabling Adaptive Query Execution's runtime skew handling (spark.sql.adaptive.skewJoin.enabled), which detects and splits oversized partitions automatically during a sort-merge join.

GC. Confirmed by the GC Time column being a large fraction of total task duration on the slow tasks specifically. Mitigations: reduce per-task memory pressure by increasing partition count (smaller partitions per task), reduce object churn by preferring the DataFrame/Dataset API and native Spark SQL functions over row-by-row Python or Scala UDFs where possible, and tune spark.executor.memory/spark.memory.fraction if execution memory is genuinely undersized for the workload.

Disk/IO. Confirmed by high "Fetch Wait Time" (time spent waiting for shuffle data to arrive from a remote executor) or unusually slow read/write throughput visible in executor logs, without corresponding shuffle-size or GC anomalies. Mitigations: check for a genuinely overloaded or failing disk on the specific host (correlate with node-level infrastructure metrics outside Spark, like disk queue depth or SMART errors), increase spark.shuffle.io.* retry/timeout tolerances if transient network blips are the cause, and consider whether the cluster's network topology is putting too much shuffle traffic through a constrained link (for example, cross-rack shuffle when rack-local data placement was expected).

Network. Overlaps with disk/IO diagnostically (both show up as fetch-wait-time) but is specifically about the transport between executors rather than local disk speed; distinguishing the two usually requires infrastructure-level network metrics (packet loss, bandwidth saturation) alongside the Spark UI, since Spark's own metrics show the SYMPTOM (waiting) without always distinguishing the specific cause. Mitigations: reduce the volume of data that has to cross the network in the first place (prefer reduceByKey/DataFrame aggregations that map-side combine over groupByKey, and filter or project columns before a shuffle rather than after); increase Spark's tolerance for transient network blips (spark.shuffle.io.maxRetries, spark.shuffle.io.retryWait, spark.network.timeout) so a brief congestion spike triggers a retry instead of a task or stage failure; and address the topology itself (favor rack- or AZ-local placement so shuffle traffic does not routinely cross a constrained link, and confirm inter-node network bandwidth is actually sized for the cluster's peak shuffle volume).

Resource starving. Confirmed when slow tasks cluster on specific hosts with no corresponding skew, GC, or fetch-wait anomaly in Spark's own metrics; this points outside Spark entirely, to node-level resource contention (a co-scheduled batch job, a runaway process, insufficient CPU/memory isolation between tenants on a shared cluster) that Spark's UI cannot see directly, requiring node-level monitoring (CPU steal time, memory pressure, disk I/O from OTHER processes) to confirm. Mitigations: isolate Spark's executors from noisy neighbors with dedicated node pools or a resource-manager queue that guarantees capacity (a YARN queue with minimum-guaranteed resources, or Kubernetes taints/tolerations and resource requests) rather than best-effort sharing; set explicit CPU/memory requests and limits so the scheduler cannot over-pack a host beyond what its real capacity supports; and, once a specific host is confirmed as a repeat offender (as in the worked example below), drain and inspect or replace that host rather than treating each occurrence as an isolated incident.

Worked example

A stage with 400 tasks: 395 complete in 8 to 15 seconds, 5 take 90 to 140 seconds. Checking the 5 slow tasks:

• Shuffle read size: within normal range of the other 395 tasks. Rules out skew.
• GC Time: consistent with the fast tasks, not elevated. Rules out GC.
• Host/Executor ID: all 5 slow tasks ran on 2 of the cluster's 40 hosts, and those same 2 hosts show elevated "Fetch Wait Time" for tasks running on them across MULTIPLE stages that day, not just this one.
• Conclusion: this pattern (host-correlated, not data-correlated, persisting across unrelated stages) points to resource starving or a network/disk problem specific to those 2 hosts, not a data-shape problem; the fix is infrastructure-level (drain and inspect those 2 hosts, check for a co-located noisy neighbor or failing hardware), not a Spark configuration change.

Trade-offs and pitfalls

• Small files as an additional root cause. A high volume of small input files can also produce straggler-like symptoms distinct from the four causes above: reading many tiny files means many small tasks with disproportionate per-task overhead relative to actual data processed, and the "slow" tasks in this case are often the ones stuck waiting on many sequential small object-store GET requests rather than any of skew/GC/IO/starving in the usual sense; the fix is compaction (merging small files) upstream, not a runtime mitigation on the reading job itself.
• Common mistake: applying a skew mitigation (salting, broadcast join) when the actual cause was GC or resource starving; this wastes engineering effort and, worse, can mask the real problem if the mitigation happens to also slightly help (for example, more partitions from salting incidentally also reduces per-task memory pressure, muddying the signal that GC was the actual cause).
• The four causes are not mutually exclusive. A skewed key can also trigger more GC (larger partitions mean more live objects during processing) as a downstream EFFECT of the skew, not a separate cause; read the shuffle-size signal as primary in that case, since fixing the skew often resolves the GC symptom as a side effect.
• Correlating slow tasks across MULTIPLE stages or MULTIPLE jobs on the same hosts (as in the worked example) is a much stronger signal for infrastructure-level causes than looking at a single stage in isolation; a genuinely bad node reveals itself through persistence, not a single occurrence.

Direct answer

Keep a stalled dependency peer-to-peer as long as direct conversation is still making progress. Escalate when you hit a concrete trigger: a scope change that neither side can unilaterally absorb, genuinely conflicting priorities that only someone with visibility into both roadmaps can arbitrate, or a hard deadline-driven blocker where peer-to-peer conversation has already stalled.

Framework

Default: work it peer-to-peer. Most stalls are under-communication or unclear ownership, and a direct conversation or a short written proposal usually unsticks them without anyone else getting involved.

Concrete triggers to escalate.

• Scope change: the fix now requires work neither team budgeted for, and only a manager can reprioritize that.
• Conflicting priorities: both sides are acting rationally from their own team's goals, and the trade-off needs someone with visibility into both roadmaps to arbitrate.
• Hard blocker with a deadline: a fixed external date is genuinely at risk, and peer-to-peer conversation has already stalled past a reasonable window, for example no movement after two direct attempts over several days.
• Repeated pattern: the same kind of stall keeps recurring with the same team, which means the real issue is the working relationship or process, not this one dependency.

What to bring when you escalate. A short brief: what's blocked, what you've already tried peer-to-peer, the realistic options and their trade-offs, and the specific decision you need.

Worked example (applying the criteria)

Situation: your team's deliverable needs a schema change from another team that they've deprioritized for two weeks despite two direct requests.
Applying the criteria: this isn't just a communication gap, direct conversation was already tried twice with no movement. It's a conflicting-priorities case, the other team's roadmap has no room for this without reprioritizing something else, combined with a hard blocker, a fixed external deadline in three weeks that this schema change sits on the critical path for (meaning if this dependency slips, the final deadline slips by the same amount, unlike a dependency with buffer to absorb delay).
Action: escalated to the shared manager with a one-page brief covering what's blocked, the two peer-to-peer attempts and their outcome, and two options: the other team reprioritizes one sprint of work, or your team ships a temporary workaround with known limitations, along with the deadline risk if neither happens within the week.
Result: the shared manager reprioritized one sprint item, unblocking the schema change with two weeks to spare before the deadline. Both teams also agreed to flag scope-affecting asks earlier next time, so the same dependency doesn't reach this point again.

Trade-offs and pitfalls

• Escalating too early over normal friction burns trust and reads as an inability to work horizontally.
• Escalating too late, repeatedly trying peer-to-peer past the point it's actually working, puts the deadline at real risk and looks like poor judgment in hindsight.
• A vague escalation with no options and no specific ask wastes the leader's time compared with a brief that names the decision needed.

Direct answer

SUM(amount) OVER (PARTITION BY customer_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) gives a running total that includes the current row; swap CURRENT ROW for 1 PRECEDING to get the total up to, but not including, the current row. The detail that trips people up is what happens with no explicit ROWS/RANGE clause at all: when an ORDER BY is present but the frame is left implicit, SQL's default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE groups by the value being ordered on, not by row position. If two rows share the same sale_date, RANGE treats them as tied peers and gives both of them the same running total (the sum through the end of that whole tied group), instead of a progressive per-row total. Add a deterministic tie-breaker column (such as sale_id) to the ORDER BY, or state ROWS explicitly, to get one running-total value per row.

Structured elaboration

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: counts by physical row position; each row's total is strictly the rows at or before it in the specified order.
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (the implicit default when only ORDER BY is given): counts by the ordered value; every row that ties on that value is included in every tied row's frame, so tied rows all see an identical total.
• Fix: either add a unique tie-breaker to ORDER BY (works with the implicit RANGE default too, since ties then become impossible) or state ROWS explicitly (works regardless of ties, because ROWS never groups by value in the first place).

Worked example

-- explicit ROWS with a tie-breaker
SELECT sale_id, customer_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY sale_date, sale_id
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM sales ORDER BY sale_id;

-- default frame, ORDER BY sale_date only (no tie-breaker) -- the trap
SELECT sale_id, customer_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY sale_date) AS running_default
FROM sales ORDER BY sale_id;

Executed against three rows for one customer: sale_id 1 (2024-01-05, amount 100), sale_id 2 (2024-01-05, amount 50, same date as row 1), sale_id 3 (2024-01-08, amount 30). With ROWS and the sale_id tie-breaker, the running total is 100, 150, 180, one distinct value per row. With the default frame (ORDER BY sale_date only, no tie-breaker), the running total is 150, 150, 180: both same-date rows get 150, because RANGE puts them in the same tied group and sums through the whole group.

Complexity

Computing the running total is a single sorted pass per partition: an index on (customer_id, sale_date, sale_id) lets the engine read each partition already in the needed order and maintain the sum incrementally, O(n) after that; without such an index the engine has to sort each partition first, O(n log n) overall. ROWS versus the default RANGE frame do not differ in this cost, both are a prefix sum over an already-ordered partition; the tie-breaker column changes correctness, not cost. A single customer with a very large number of sales rows can force that partition's sort to spill to disk if it exceeds the engine's working-memory budget.

Trade-offs & pitfalls

• ORDER BY 1 PRECEDING versions return NULL for the first row per partition (there is no earlier row); decide whether to leave that NULL or COALESCE it to 0 depending on what downstream reporting expects.
• Any ORDER BY column that can plausibly repeat (a date truncated from a timestamp, or several writes landing in the same second) is a candidate for this exact trap; the safest habit is to always add a tie-breaker to a window ORDER BY, whether or not you also state the frame explicitly.
• This default-frame behavior (RANGE when only ORDER BY is given, no explicit clause) is part of the ANSI SQL standard and holds consistently across PostgreSQL, SQL Server, Snowflake, and BigQuery; it is not an engine quirk you can assume away.

Direct answer

These two needs sit at opposite ends of the latency spectrum and should not be forced onto one pipeline: daily revenue reports are a batch problem, real-time fraud alerts are a streaming problem, and trying to serve both from the same infrastructure choice usually means over-paying for one or under-serving the other.

Structured elaboration

For (A), daily aggregated revenue reports: analysts consume this once a day, the numbers need to be complete and auditable (finance will reconcile them against source systems), and a few hours of processing latency is invisible to the consumer. A nightly batch job that waits for the full day's data, recomputes cleanly, and can be rerun if something goes wrong is the right shape: simple failure recovery, lower compute cost per unit of data processed, and no need to reason about partial/late data during the run because by the time it runs, the day is over.

For (B), fraud alerts within 10 seconds: by definition, this decision has to be made before a batch job would even have started. This is not a latency optimization on top of batch, it is a different processing model: a streaming (or at minimum sub-minute micro-batch) pipeline that scores each transaction as it arrives, with a state store holding whatever recent history the model needs (velocity checks, recent device/IP behavior) and an alerting path that can act within the window.

Worked example

Concretely: revenue reporting reads from the same append-only event log as the fraud pipeline, but on a completely separate cadence and infrastructure. The fraud path needs a stateful stream processor (state per card/account, sub-second decisions) and pays 24/7 compute for that state and the always-on stream. The reporting path needs a scheduled job (Airflow-triggered Spark or a warehouse-native transform) that reads yesterday's partition once, and pays only for the run itself, typically a fraction of the fraud path's monthly compute for a similar data volume, because it isn't holding anything resident between runs.

Trade-offs and pitfalls

The trap is assuming one architecture has to serve both because they draw from the same source events. That's a data-modeling question (do both consume the same log, yes), not a processing-model question (do both need the same latency, no). Building the fraud pipeline on top of the revenue pipeline's batch cadence would make fraud detection useless (alerts arrive after the damage is done); building revenue reporting on top of the fraud pipeline's streaming infrastructure would mean paying always-on compute and operational overhead for a report nobody looks at outside business hours, with no benefit, since "more real-time" doesn't make a once-a-day report more correct or more useful.

Direct answer: For complex JSON documents with nested objects, arrays, and domain-specific merge rules, the design attaches per-field (or per-path) merge metadata rather than one blanket strategy for the whole document, applies structural rules by field TYPE (scalars use LWW or a domain rule, arrays use set/sequence semantics depending on intent, nested objects recurse the same merge logic), embeds explicit domain rules where the natural type-based default is wrong (ratings take max, tags union), and falls back to surfacing an unresolvable conflict rather than guessing when no rule cleanly applies.

Structured elaboration

Per-path metadata. Rather than one merge function for the whole document, the design tracks, for each JSON path (e.g. $.ratings.overall, $.tags), what KIND of merge applies there: last-write-wins, numeric-max, set-union, or a custom domain function. This is necessary because a single document genuinely mixes fields with very different correct merge semantics, treating them uniformly (e.g. LWW for everything) would be simple but wrong for many fields.

Default rules by structural type. Scalars (numbers, strings, booleans) default to LWW unless a domain rule overrides it. Arrays default to set-union semantics if order doesn't matter for that field (deduplicating by value or by an element ID), or to a sequence-CRDT-style position-preserving merge if order genuinely matters (e.g. an ordered list of steps). Nested objects recurse: the SAME per-path merge logic applies to each nested field independently, so a deeply nested document is handled compositionally rather than needing a special case for depth.

Domain-specific rule examples. "Ratings take max": for a field like $.qualityScore where two concurrent updates might reflect two different reviewers' concurrent submissions, the domain decides the highest score should win rather than an arbitrary last-write, a business rule, not a generic data-structure default. "Tags union": for $.tags, the domain decides concurrent additions from different sources should ALL be kept (set-union) rather than one replacing the other, since tags are additive by nature.

Fallback strategies for impossible merges. Some conflicts have no automatic resolution: two concurrent edits to a deeply structural field (e.g. one edit reshapes $.address from a string to a nested object, while a concurrent edit modifies the OLD string-shaped value), these are genuinely irreconcilable by any generic rule and need to be surfaced explicitly (both candidate document versions, with metadata about the conflicting paths) for human or application-level resolution, rather than the merge engine guessing and potentially producing an internally inconsistent document.

Worked example. A product-review document: $.title (LWW), $.rating (max, per the domain rule that a higher rating from a concurrent update should win, reflecting the reviewer's most positive assessment), $.tags (set-union, so tags added from two different concurrent edit sessions all survive), and $.photos (an OR-Set of photo IDs, so concurrently-uploaded photos from two devices both appear rather than one overwriting the other). Two concurrent edits: edit A sets rating: 4, tags: ["great_service"]; edit B (concurrent, unaware of A) sets rating: 5, tags: ["fast_delivery"]. Merge result: rating: 5 (max rule), tags: ["great_service", "fast_delivery"] (union rule), no conflict surfaced, since every field had a clean, applicable rule.

Trade-offs and pitfalls. The biggest practical risk in this design is an UNDEFINED merge rule for a path silently defaulting to something wrong (e.g. LWW applied to a field that actually needed union semantics) rather than failing loudly during development; a strong version of this design validates, at schema-definition time, that every field in the document has an EXPLICIT merge rule assigned, rather than relying on an implicit fallback that might quietly produce a business-incorrect result in production.

Approach: model both storage and IOPS separately using historical telemetry, expected 5x growth over 12 months, and seasonal/feature-driven spikes. Model growth: fit exponential or monthly-compound linear model to current bytes/day and IOPS/day; produce monthly projections and 95th-percentile spike scenarios (p99 IOPS). Safety margins: target headroom = max(20% + forecast error, SLA-driven margin). For storage include retention, backups, compression ratios, and schema growth. Cost projections: multiply projected capacity by provider tier pricing + IO request tiers; include network egress. Automation/alerts: set thresholds (70/85/95% capacity) with automated actions: scale up storage (cloud autoscale), add IOPS provision, or throttle low-priority jobs. Implement continuous forecasting job (daily) that writes predictions to monitoring (Prometheus/Grafana) and triggers runbooks. Prevent outages: autoscaling with pre-warm, reservation of burst IOPS credits, scheduled capacity increase workflow requiring manual approval at defined thresholds, and synthetic load tests to validate headroom. Metrics to monitor: usable storage, consumed IOPS (read/write separately), queue depth, latency percentiles, checkpoint lag. This balances cost and safety while enabling predictable ops.

Direct answer

At the scale of roughly a thousand pipelines across a data platform, the observability platform itself needs three properties beyond what a single-team monitoring stack needs: tiered ingestion and storage that scales cost sub-linearly with pipeline count, multi-tenant isolation so one noisy team's telemetry cannot degrade another team's queries, and a way to link an SLO (service-level objective) breach back to a candidate root cause automatically, since manual correlation does not scale past a handful of teams.

Structured elaboration

• Ingestion and storage tiers: a hot tier (recent metrics/logs/traces, full fidelity, fast queries, retained days to a few weeks) backed by an aggregation/rollup tier for older data (downsampled metrics, sampled traces, retained months to a year) and a cold archive tier (compliance-driven, cheap object storage, retained years, rarely queried). Metrics and logs from a thousand pipelines at reasonable per-pipeline cardinality still land in the billions-of-data-points range daily, so the hot tier's cost is the dominant design constraint.
• Multi-tenant isolation: per-team resource quotas on ingestion rate and query concurrency, so a team that starts emitting a runaway high-cardinality metric (a bug, not malice) cannot starve query capacity for every other team. Logical isolation via consistent tagging (team, pipeline, environment) is cheaper than physical isolation (separate clusters per team) but requires enforcing quota limits at the ingestion gateway, not just at query time.
• Query latency at scale: pre-aggregating common dashboard queries (rollups by pipeline and hour) rather than scanning raw data at read time; this is what keeps a "show me pipeline health" dashboard fast even as the underlying dataset grows into the billions of rows.
• Linking an SLO breach to a candidate root cause: correlate the breaching metric's timestamp against recent deploy events, config changes, and upstream lineage; a lightweight heuristic (which deploys happened in the 30 minutes before the breach, which upstream pipelines the failing pipeline depends on) gets an on-call engineer to a short list of candidates in seconds instead of them manually cross-referencing a deploy log by hand.

Worked example

Concretely: pipeline X's freshness SLO breaches at 03:14. The platform's correlation logic queries the deploy-event log for any deploy touching pipeline X or its direct lineage dependencies in the preceding hour, finds a config change to an upstream shared library at 02:50, and surfaces that as the top candidate root cause on the incident dashboard automatically, before a human has even opened a terminal. This is the payoff of investing in the correlation layer: at ten pipelines, a human can eyeball recent changes; at a thousand, that manual process simply does not scale, and the platform has to do the first pass of correlation itself.

Trade-offs and pitfalls

Per-tenant cost attribution and quota enforcement is worth the up-front design effort at this scale, because without it, the platform team ends up firefighting one noisy tenant's runaway cardinality every few weeks instead of having a self-service quota system that fails gracefully (rejecting or sampling the offending team's excess telemetry) rather than degrading the whole platform. The main pitfall in the correlation layer is false confidence: a heuristic correlation ("a deploy happened nearby in time") is a STARTING hypothesis for a human to verify, not a verdict, and treating it as authoritative risks chasing the wrong root cause while the real one goes unaddressed.

Direct answer

Identify a system's access patterns by explicitly listing, for each major user-facing feature, exactly what the database is asked to do (which reads, writes, filters, sorts, aggregations, and bulk operations it must serve), and then let each concrete pattern justify a specific modeling decision, rather than designing the schema first and hoping it happens to serve whatever queries show up later.

Structured elaboration

The process: for a product catalog, walk through the product's real user journeys and note what each one requires of the data layer, not in the abstract but as a specific query shape: browsing a category page (filter by category, sort by popularity or price, paginate); a search-with-facets page (filter by several optional attributes simultaneously, plus return facet counts for the remaining options); an admin bulk-import (insert or upsert tens of thousands of rows at once); a "recently viewed" widget (fetch a small, ordered set for one user).

Three concrete access patterns and the modeling decisions they drive:

1. Category browsing with sort-by-price, paginated: drives a composite index on (category_id, price) (or (category_id, popularity_rank)), and drives the decision to keep price as a queryable column rather than buried in a JSON blob, since it's a first-class filter/sort key.
2. Faceted search across several optional attributes: drives either a search-index (Elasticsearch) if facet counting across many combinations needs to stay fast, or a hybrid relational-plus-JSONB design with a GIN index if the facet set is small and stable enough for relational containment queries to keep up.
3. Bulk admin import of tens of thousands of rows: drives a decision to support efficient bulk upserts (a staging table plus a single MERGE/INSERT ... ON CONFLICT, rather than one row at a time), and drives which constraints can safely be deferred until the end of the bulk operation versus checked per-row.

Worked example

Concretely, documenting these patterns BEFORE finalizing the schema changes what "reasonable" design looks like: if faceted search turns out to be the dominant, most latency-sensitive pattern, that alone might justify introducing a search index as a second store (the polyglot-persistence trade-off), a decision that would look premature if you'd only looked at the schema in isolation without first establishing how central that access pattern actually is to the product.

Trade-offs and pitfalls

• The common failure mode this process is meant to prevent is designing a "textbook normalized" schema first and only discovering, once real traffic arrives, that the dominant queries need three expensive joins every time; documenting access patterns upfront turns "let's see how it performs" into a deliberate design input.
• Access patterns change as a product evolves, so this isn't a one-time exercise; a schema optimized for today's known patterns can become a poor fit for a materially different dominant use case a year later, which argues for periodically revisiting the documented patterns against actual query logs, not just at initial design time.
• The risk of over-fitting to CURRENT access patterns is real too: a schema aggressively optimized (heavily denormalized, narrowly indexed) for exactly today's three known queries can make an unanticipated but reasonable new query type expensive; balancing "serve the known patterns well" against "don't foreclose reasonable future patterns" is itself a judgment call, not a mechanical one.

Direct answer

The first move is to stop treating the surface argument as the thing to resolve, and instead separate each person's stated position (what they're demanding) from their underlying interest (what they actually need or fear), because two people can hold incompatible positions while having compatible, even identical, underlying interests.

Structured elaboration

This is the positions-versus-interests distinction from negotiation practice: a position is what someone is asking for, an interest is why they're asking for it.

1. Talk to each side separately first, not together, while the surface disagreement is still hot. Ask not "what do you want" but "what happens to you if you don't get it" or "what are you actually worried about here," which surfaces the interest instead of relitigating the position.
2. Look for the mismatch. Often the two stated positions look mutually exclusive ("ship now" versus "don't ship now") while the interests underneath are compatible or unrelated ("I'm worried about a customer commitment" versus "I'm worried about a specific failure mode"), which means there's a solution neither position alone would have suggested.
3. Check your own read before acting on it: name the interest you think you're hearing back to the person ("it sounds like the real concern is X, is that right") rather than assuming you've diagnosed it correctly from the outside.
4. Only once you can state both interests accurately do you bring the two sides back together, now around the actual problem instead of the positions they opened with.

Worked example

Two stakeholders keep arguing about which vendor to select, and the conversation keeps circling back to feature comparisons that don't seem to be moving anyone. Talking to each separately, one turns out to be worried about a renewal timeline with the incumbent vendor that nobody else in the room knows about, and the other is worried about a specific integration risk they haven't clearly articulated because the conversation kept staying at the feature-comparison level. Once both interests are on the table, the feature debate turns out to be a proxy fight, and the actual decision hinges on two much narrower, more answerable questions.

When the conflict is specifically between a client's product owner and your own engineering lead, the same diagnosis holds but the three concrete steps look like this: first, a short separate conversation with each side to surface what's actually driving their position, the product owner's real driver is often a commitment already made to their own stakeholders, the engineering lead's is often a technical risk they haven't been able to quantify yet. Second, restate each interest back to its owner to confirm you've got it right before doing anything with it. Third, bring both interests, not both positions, into a joint conversation, framed as a shared problem to solve rather than a decision to make.

Trade-offs and pitfalls

• Jumping straight to a compromise on the stated positions, splitting the difference, usually satisfies neither underlying interest and just produces a worse version of the original disagreement later.
• Assuming you've correctly guessed the interest without checking it back can send you further from resolution, since you're now negotiating a made-up problem instead of the real one.
• This diagnosis takes time you may not have if the conflict is actively blocking something urgent. Sometimes the honest move is a short-term decision to unblock, with the interest-finding conversation happening in parallel, not gating everything.
• If one side's real interest is something they're not willing to say out loud, office politics, distrust of a specific person, you may not get a clean answer from asking directly, and you'll need to read between the lines of what they avoid saying, not just what they say.

Direct answer. Confirm duplication is the cause (rather than a logic bug) by comparing the row count immediately after the join to the row count you'd expect from the smaller side alone, and by checking, on a small sample, whether specific keys legitimately have multiple matches on both sides; fix it either by aggregating one side down to uniqueness before the join, or by deduplicating the joined result afterward in a way that doesn't silently discard rows you actually need.

Structured elaboration. An unanticipated many-to-many relationship means a join key you expected to be unique on at least one side actually has multiple matching rows on BOTH sides for some values, which multiplies rather than merely combines: two matching rows on each side for the same key produce four joined rows, not two, which then inflates any downstream SUM or COUNT computed from that joined result. To confirm this is the cause, pick a specific key value, count its rows on each side of the join independently, and multiply those counts together; if that product matches the number of joined rows you're seeing for that key, you've confirmed genuine multiplicative duplication rather than, say, a join condition that's simply too loose.

Two realistic fixes, and they aren't interchangeable: pre-aggregate one side down to one row per join key BEFORE the join (appropriate when you only actually need a single value per key from that side, like a "most recent" or "total" per key); or deduplicate the JOINED result afterward using a window function or an explicit grouping, which is appropriate when you genuinely need attributes from multiple matching rows on the many-side and the "duplication" is actually correct given the relationship, just not what a naive downstream SUM assumed.

Worked example. A product joined to promotions where a product can have multiple active promotions and a promotion can apply to multiple products is a genuine many-to-many; a report computing "total sales per product" that naively joins in promotions and then sums a sales column will multiply each product's real sales figure by however many active promotions it happens to have, a bug that's invisible on products with exactly one promotion and only becomes obvious (and often only gets NOTICED) on products with several.

Trade-offs and pitfalls. The riskiest version of this bug is exactly the one described in the worked example: it's silently correct for the common case (one match per key) and silently wrong only for the less common case (multiple matches), which means it can ship, look fine in testing, and only surface as a real discrepancy once someone happens to look at a key with genuine multiplicity, often much later and much harder to trace back to its root cause.

Complexity

Detecting this costs a handful of targeted counting queries against a specific suspect key value, not a full reprocessing of the dataset, so confirmation is cheap even though the underlying bug can be expensive to have shipped.

Edge cases

A key with exactly one match on both sides produces exactly one joined row and looks completely correct, which is precisely why this bug tends to survive testing against a small, low-multiplicity sample and only appears once the real data includes genuine multi-match keys.