Spotify Data Engineer (Senior Level) - Comprehensive Interview Preparation Guide

Direct answer

Partition the trip stream by a derived HOUR bucket (from event_time) so tasks working on the same hour are co-located, group by (hour, origin_zone, dest_zone) as the join/aggregation key, and use Structured Streaming's event-time windowing (window(event_time, "1 hour")) with an explicit watermark to correctly fold in LATE-arriving events without holding unbounded state forever. Memory safety comes from bounding how long a window stays open (the watermark threshold) and from pre-aggregating (partial counts per micro-batch) rather than ever collecting raw trip rows for 1000×1000 zone pairs into one place.

Structured elaboration

Partitioning strategy. Partition the underlying storage (if writing hourly output to a lake table) by hour (and optionally origin_zone for downstream query pruning, picking a partition column by query pattern); for the STREAMING computation itself, Spark's own groupBy(window(...), origin_zone, dest_zone) handles the in-flight grouping, and the underlying shuffle partition count (spark.sql.shuffle.partitions) should be sized against the actual per-micro-batch row volume (hundreds of millions of rows per DAY means tens of millions per hour, the same reasoning here).

Join keys. The natural grouping key is (hour_window, origin_zone, dest_zone); with 1000 zones, the theoretical key space is 1,000,000 OD pairs per hour, though real trip data is typically far sparser (most zone pairs never see a trip in a given hour), so the actual cardinality driving shuffle/memory is usually much smaller than the theoretical maximum, worth confirming against real data rather than assumed.

Windowing. F.window("event_time", "1 hour") buckets each row into its HOUR based on the row's OWN event_time value, not on when the row arrives at the job; this is what makes correct late-event handling possible at all (a row that arrives late but has an event_time from an hour ago still lands in that hour's bucket).

Handling late-arriving events. A WATERMARK (withWatermark("event_time", "30 minutes"), for instance) tells Structured Streaming how long past an hour boundary to keep accepting rows for that hour's window before considering it CLOSED and releasing its state; events arriving within the watermark threshold are correctly folded into their hour's aggregate, while events arriving LATER than the watermark are dropped (a deliberate, bounded trade-off: without SOME cutoff, the job would need to hold every hour's state open FOREVER, an unbounded memory growth as its own failure mode). The watermark threshold should be sized against the REAL observed lateness distribution of the trip stream (mobile/GPS-sourced event streams commonly see tens of minutes of lateness from connectivity gaps), not picked arbitrarily.

Memory optimizations to avoid OOMs. (1) Pre-aggregate WITHIN each micro-batch before any cross-partition shuffle, so partial counts move across the network instead of raw rows (this is exactly what groupBy(...).count() already does via Spark's own map-side combine, no extra code needed, but worth understanding why it matters at this row volume). (2) Bound state size via the watermark (above), so old, closed windows' state is released rather than accumulating forever. (3) Avoid collect()/toPandas() on the full hourly result if the OD-pair cardinality is genuinely large; write the aggregated result directly to a sink (a lake table, a serving store) rather than materializing it in the driver.

Worked example

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.master("local[2]").appName("od_matrix").getOrCreate()
spark.sparkContext.setLogLevel("ERROR")

# Batch stand-in validating the AGGREGATION logic (the streaming source
# itself cannot be demonstrated in a batch sandbox, but groupBy+window
# behaves identically whether fed by a streaming or batch DataFrame).
rows = [
    ("t1", "Z001", "Z045", "2026-07-30 09:05:00"),
    ("t2", "Z001", "Z045", "2026-07-30 09:40:00"),
    ("t3", "Z002", "Z045", "2026-07-30 09:10:00"),
    ("t4", "Z001", "Z045", "2026-07-30 10:02:00"),
    # A LATE event: event_time is in the 09:00 hour, but this row is
    # appended to the DataFrame after the 10:02 (t4) row, simulating
    # out-of-order arrival.
    ("t5", "Z001", "Z045", "2026-07-30 09:15:00"),
]
df = (spark.createDataFrame(rows, ["trip_id", "origin_zone", "dest_zone", "event_time"])
      .withColumn("event_time", F.to_timestamp("event_time")))

od_hourly = (df
    .groupBy(F.window("event_time", "1 hour").alias("hour_window"), "origin_zone", "dest_zone")
    .agg(F.count("*").alias("trip_count")))

result = (od_hourly
    .select(F.col("hour_window.start").alias("hour_start"), "origin_zone", "dest_zone", "trip_count")
    .orderBy("hour_start", "origin_zone", "dest_zone"))
result.show(truncate=False)

Output (actually executed with python3.12 + pyspark 3.5.1, Java 17, local[2]):

+-------------------+-----------+---------+----------+
|hour_start         |origin_zone|dest_zone|trip_count|
+-------------------+-----------+---------+----------+
|2026-07-30 09:00:00|Z001       |Z045     |3         |
|2026-07-30 09:00:00|Z002       |Z045     |1         |
|2026-07-30 10:00:00|Z001       |Z045     |1         |
+-------------------+-----------+---------+----------+

The late row t5 (event_time in the 09:00 hour, appended to the DataFrame AFTER t4, a 10:00-hour row) correctly landed in the 09:00 bucket with count 3 (t1, t2, t5), confirmed directly by filtering the result: event-TIME windowing groups by the row's own timestamp, not arrival order, exactly the mechanic a Structured Streaming watermark relies on to decide how much lateness is still acceptable before closing a window.

Complexity

• Time: dominated by the shuffle for the groupBy, $O(n)$ in row count for the map-side partial aggregation, plus the shuffle of the (far smaller) partially-aggregated intermediate results.
• Space: state held per OPEN window is bounded by the watermark threshold (only windows within the lateness threshold of the current event-time watermark stay open); real OD-pair cardinality within any single open hour, not the theoretical 1,000×1,000 maximum, drives actual memory.

Edge cases

• A trip whose event_time is from several DAYS ago (far beyond any reasonable watermark): correctly dropped, not held open indefinitely; if genuinely-old backfill data needs processing, that is a separate BATCH job, not something the live streaming watermark should be widened to accommodate (widening it for a rare backfill case would hold every window's state open far longer than needed for the normal case).
• An hour with zero trips for most zone pairs: correctly absent from the output entirely (no row emitted for the ~999,000+ zone pairs with zero trips that hour), not a padded zero-row per pair, keeping actual state/output size proportional to REAL activity, not the theoretical zone-pair count.
• origin_zone equals dest_zone (an intra-zone trip): valid data, aggregated like any other pair; no special-casing needed unless the specific business definition of "OD demand" explicitly excludes intra-zone trips, worth confirming rather than assuming.

Trade-offs and pitfalls

• Common mistake: setting the watermark threshold arbitrarily short to minimize memory footprint, without checking the trip stream's actual observed lateness distribution; a threshold shorter than real-world lateness silently DROPS legitimate late trips rather than including them, a correctness cost traded for a memory savings that may not even be necessary.
• Common mistake: grouping by the raw event_time timestamp instead of a windowed bucket; without F.window(...), every distinct timestamp becomes its own group, defeating the entire point of HOURLY aggregation and producing an enormous number of tiny groups instead of the intended ~hour-granularity output.
• 1000 zones means a theoretical 1,000,000-pair key space, but real state is usually far smaller; sizing memory/config for the theoretical maximum when real trip data is much sparser wastes resources, while assuming sparsity without checking real cardinality risks an OOM if actual demand is more uniformly spread than expected. Validate against real data before committing to either assumption.

A feature built on a third-party managed AI service like Azure OpenAI needs SLOs that explicitly separate what YOU control (your own integration code, your own error handling) from what the VENDOR controls (their service's own availability, latency, and rate limits), since an incident caused by vendor throttling needs a genuinely different response than one caused by your own bug.

Structured elaboration

Measurable SLOs with illustrative targets: latency p95 end-to-end (including your own integration overhead, not just the vendor's raw response time) targeted at, e.g., under 3 seconds; availability percentage targeted at 99.5% monthly for the feature overall (deliberately set below Azure OpenAI's own published SLA, since your feature's ceiling is bounded by the vendor's real, observed reliability plus your own integration's added risk, not just the vendor's number alone); error rate targeted at under 2% of requests, tracked separately from vendor-attributed 429/rate-limit responses (which are logged and trended but not counted against your own code's error-rate SLO, since they are not a bug in your integration); and cost per 1,000 requests targeted at a defined budget ceiling (e.g. $X/1000 requests), a genuinely important SLI for an AI-service-backed feature specifically, since cost can vary meaningfully with usage patterns and retry behavior in a way that's less prominent for typical infrastructure SLOs.

Worked example

Monitoring and alert thresholds: instrument both your own integration layer's health AND, separately, the vendor's observed latency/error/rate-limit behavior as experienced by your actual traffic, so you can distinguish "our code broke" from "the vendor throttled or degraded us" when an incident occurs. Page on-call if p95 latency exceeds 5 seconds sustained for 10 minutes, OR if your own-code error rate (explicitly excluding vendor 429 responses) exceeds 5% for 5 minutes. Track vendor-attributed 429/rate-limit rate as its own dashboard metric with a lower-severity, non-paging alert (a ticket, not a page) if it exceeds 10% of requests for 15 minutes, since that condition requires a different response path (backoff, vendor engagement) than your own bug would. Alert if cost per 1,000 requests drifts more than 20% above its trailing 7-day baseline, since a spike often indicates aggressive retries against a struggling vendor endpoint rather than genuine traffic growth.

Handling an incident caused by external rate-limit throttling from the vendor: first, confirm via your instrumentation that the failure signature (a specific rate-limit error code/response, not a generic timeout or 500) matches vendor throttling rather than your own bug, since misdiagnosing a self-inflicted problem as "the vendor's fault" wastes response time and delays the real fix. Once confirmed as genuine vendor throttling, the response is largely OUTSIDE your control to fix directly (you can't force the vendor to raise your rate limit instantly), so the mitigation is architectural: implement request queuing/backoff with jitter to smooth bursts against the vendor's actual limit, consider whether a fallback (a simpler, non-AI-backed response, or a cached/previous result) can degrade gracefully rather than failing outright during the throttled period, and separately, engage the vendor relationship (requesting a rate-limit increase, or reviewing your usage pattern against their published limits) as a parallel, slower-moving track.

Trade-offs and pitfalls

Setting your own SLO tighter than what the vendor's OWN published SLA can support is a common and avoidable mistake specifically with managed AI services, since your feature's ceiling is fundamentally bounded by the vendor's own commitments (and often, in practice, well below their nominal SLA during periods of high demand across all their customers, not just yours); the SLO-setting process here needs to explicitly account for the vendor's real, observed (not just advertised) reliability as an input, not just aspirational internal targets. It's also worth tracking cost per 1,000 requests as a genuine SLI, not just a finance metric, since certain failure-handling strategies (aggressive retries against a struggling vendor endpoint) can quietly and significantly increase cost while doing little to actually improve the user-facing outcome, a trade-off worth making visible rather than discovering only on the next bill.

Requirements & goals: support up to 10x seasonal peak with SLOs (p99 latency < X ms, availability 99.95%). Budget constraint target and acceptable degradation window during ramp.

Metrics to track (real-time + historical):

• Traffic: requests/sec, input bytes/sec, distinct keys
• Utilization: CPU, memory, disk I/O, network
• Service-level: p50/p95/p99 latency, error rate, timeouts
• Queueing: queue lengths, backlog growth rate, processing rate
• Platform: instance boot time, container startup time, cold-cache miss rate
• Business: revenue-impacting requests, SLA breaches

Autoscaling strategies:

• Reactive horizontal autoscaling (target CPU, request latency, or custom metric like in-flight requests per host) with conservative cooldowns to prevent thrash.
• Predictive scaling using historical seasonality (time-series forecasting: ARIMA/Prophet/ML) to start scale-out before expected spike.
• Hybrid: predictive scale-up + reactive fine-tuning. Use rate-based and queue-length based policies to capture bursts.
• Use graceful draining and connection-aware load balancers; ensure state is externalized.

Pre-warming / reserved capacity:

• Maintain a warm pool of standby instances or containers (fraction of expected peak) for fast handoff.
• Use reserved/committed instances for base baseline and autoscaling for incremental capacity. Consider savings plans for steady baseline.
• For spot/interruptible instances, mix with reserved for cost: keep critical portion on reserved, opportunistic capacity for bulk.

Testing for scale:

• Rehearsals: regular load tests that simulate 1x–10x traffic including realistic request patterns and cache-warm vs cold scenarios.
• Chaos and failure injection (instance termination, AZ outage) during scaled conditions.
• Canary and gradual rollout during real spikes.
• Measure boot times, cache warm-up curves, and end-to-end SLOs.

Trade-offs (cost vs availability):

• 100% reserved capacity avoids cold starts but is costly. Heavily predictive scaling reduces cold time but risks overprovisioning if forecasts miss.
• Spot instances reduce cost but increase risk; use for noncritical batch or as pooled capacity with fast replacement.
• SLA targets dictate min reserved capacity: higher availability -> higher reserved %.
• Optimize by caching, batching, request shaping, and graceful degradation for low-value requests.

Operational pieces:

• Clear runbooks for scale events, automated alerts on forecast deviations.
• Dashboards for metrics and cost.
• Post-mortems after spikes to refine forecasts and policies.

Example: baseline 1000 rps, peak 10k rps. Reserve capacity for 2x baseline (handles growth + safety), predictive autoscale to 8x starting 2 hours before expected peak, warm pool to cover remaining 0–20% sudden bursts, reactive autoscale with queue-length target to reach full 10x. Adjust based on rehearsals and cost SLO.

Direct answer

Without formal authority, the lever is technical credibility built through artifacts other people can independently check: a written proposal grounded in real data, a working prototype, and a track record of small delivered wins, not persuasion technique. Leading through influence differs from direct management in exactly this: you cannot assign the work, so every step has to make it easier for someone else to say yes than to say no.

Structured elaboration

• Diagnose before proposing. Collect the evidence (incident data, latency trends, where teams keep colliding) before writing anything. An undiagnosed proposal reads as an opinion; an evidence-backed one reads as a finding.
• Write it down concretely. A short design document with a specific problem statement, two or three named milestones, and a measurable success criterion for each (a target latency or error-rate range, not a vague goal) lets someone evaluate the idea without trusting your judgment on faith.
• Build the smallest thing that proves the idea, not the whole thing. A scoped prototype against a single team's workload is cheap to say yes to and gives you a concrete result to point at instead of a projection.
• Pull in the people who would implement or be affected, deliberately. A proposal with co-authors from outside your own team is harder to dismiss as one person's pet project. This is also the mechanism that keeps direction from becoming siloed inside your own team's worldview: without deliberately involving adjacent teams, "technical direction" quietly becomes "what my team already wanted to build."
• Keep it visible. Regular short updates and a shared tracker mean momentum does not depend on you personally chasing people down.

Worked example

A platform initiative is expected to eventually support on the order of a million users, and teams currently ship changes ad hoc with no shared plan. As an individual contributor, you spend several weeks pulling incident and latency data into a few named failure themes, then write a short design proposal with milestones for an observability baseline, a prototype for the highest-risk theme, and a backward-compatible rollout, each with an explicit success measure. You pilot the riskiest piece with one team first, because a single team's result is concrete evidence rather than a projection, then bring that data back to the wider group before asking anyone else to adopt it. The honest result of this kind of effort is usually partial: some teams adopt the pattern quickly because the pilot removed their specific pain, others wait for a second team to prove it first, and the plan itself gets revised once a stakeholder objects to a milestone you had not stress-tested. That is expected, not a failure of the approach; the goal was to make the direction adoptable, not to force it.

Trade-offs and pitfalls

The dependency on artifacts cuts both ways: a proposal or prototype that turns out to be wrong is now visible and attributable to you in a way a vague opinion never was, which is uncomfortable but is also what makes the influence real. The bigger failure mode is over-investing in the write-up and under-investing in the pilot: a well-argued document with no working proof is easy to admire and easy to ignore. Influence exercised entirely within your own team's technical culture is the other common trap: it produces direction that only makes sense to your team, which is exactly the siloing this approach is meant to avoid.

Direct answer
"Unclear requirements" is a real cause sometimes and a convenient explanation other times, so the first job is figuring out which, using evidence rather than taking the explanation at face value. Look at the pattern across several instances, not just the latest miss, separate estimation problems from execution problems from actual requirement gaps, then fix the specific mechanism, not the person's attitude.

Diagnose using the pattern, not the excuse

• Pull several recent examples, not just the most recent miss. Was the requirement genuinely ambiguous every time, or does "unclear requirements" get invoked even when the ticket had clear acceptance criteria? The former is a process problem; the latter is a signal something else is going on (confidence, avoidance, poor estimation).
• Look for where in the workflow it breaks down: did they ask clarifying questions before starting and get bad answers, or did they not ask and guess? Did the requirement change mid-task without being re-scoped? Did they commit to something they didn't actually understand, to avoid looking behind?

Separate the possible root causes

• Genuine ambiguity: the requirement really was underspecified and nobody caught it before work started.
• Estimation or planning gap: the requirement was clear but the person didn't break it down enough to notice the ambiguous parts until they hit them.
• Avoidance: asking clarifying questions feels risky (looks like not knowing), so they guess and then have a ready explanation when it goes wrong.
• Skill gap under a different name: they may not yet have the judgment to know what "clear enough to start" looks like.

Fix the mechanism that matches the cause

• Genuine ambiguity: introduce a lightweight definition-of-ready check before work starts, owned jointly, not something you police alone.
• Estimation or planning: practice breaking a ticket into sub-tasks together and flag the ambiguous piece explicitly before committing to a date.
• Avoidance: make asking clarifying questions cheap and normal, model it yourself, and separate "I don't know yet" from an evaluation of competence.
• Skill gap: pair on a couple of tickets so they see what "clear enough" actually looks like in practice, rather than being told about it abstractly.

Worked example
A mentee on a team I supported kept missing sprint commitments, and the stated reason was always some version of unclear requirements. Looking at the last four tickets together, not just the most recent one, a pattern showed up: on three of the four, the acceptance criteria were actually written clearly, but the mentee hadn't asked any clarifying questions before starting, then hit an edge case mid-task and treated the whole ticket as ambiguous from the start. On the fourth, the ticket genuinely was underspecified.

The fix wasn't "communicate more clearly" in the abstract. It was two things: a short pre-work check where we'd both look at a ticket before it was picked up and flag anything genuinely unclear (catching the real ambiguity case), and a habit of the mentee sending one clarifying question per ticket before starting, even a small one, to break the avoidance pattern. The signal it was working wasn't a single metric; it was that "unclear requirements" stopped being the explanation for misses, because the real ambiguity was being caught earlier and the avoidance pattern had a lower-stakes outlet.

Trade-offs and pitfalls

• Taking "unclear requirements" at face value every time lets a deeper issue (avoidance, skill gap) hide behind a plausible-sounding excuse indefinitely.
• Assuming it's never true is just as wrong; requirements genuinely are underspecified sometimes, and treating every instance as a character problem erodes trust.
• The fix has to match the actual cause. A definition-of-ready checklist won't help someone avoiding asking questions, and coaching someone to "just ask more" won't help if the requirements really were bad.

Direct answer

A synthetic canary for a data pipeline is a small, known, artificially-injected record that you push through the pipeline end to end and then verify arrives correctly and on time, giving you a continuous, proactive signal of pipeline health that doesn't depend on waiting for real production data to reveal a problem.

Structured elaboration

• What synthetic records to insert: a canary record with a distinctive, easily-queryable marker (a reserved customer id or a specific field value that would never occur in real data) and a known expected transformation outcome, so you can verify not just "did it arrive" but "did it arrive with the CORRECT computed values."
• Frequency: inject on a cadence proportional to how quickly you need to detect a problem relative to the pipeline's own processing latency, for a pipeline expected to process within minutes, injecting every 5-10 minutes gives a reasonable balance between detection speed and injection overhead; for a nightly batch job, once per run is sufficient.
• Success criteria: the canary record must (a) arrive at the expected output location within an expected latency bound, and (b) have the exact expected transformed values, both checked automatically, since a canary that only checks arrival (not correctness) would miss a transformation bug that silently mangles the value.
• Alarms and automated rollback: if the canary fails to arrive within its latency bound, alert on LATENESS; if it arrives with wrong values, alert on CORRECTNESS, a different failure signature that likely points to a transformation-logic bug rather than an infrastructure slowdown; for a canary tied to a recent deployment, a repeated correctness failure can trigger an automated rollback of that deployment before it affects real data at scale.

Worked example

Concretely: every 10 minutes, inject a record with customer_id = "CANARY-0001", amount = 100.00, into the pipeline's input topic. The pipeline's known transformation logic should apply a fixed 8% tax calculation, so the expected output is amount_with_tax = 108.00. A canary-checker job queries the output table every 10 minutes for the most recent CANARY-0001 record: if none appears within 3 minutes of injection (well above the pipeline's typical 90-second processing latency), it alerts on LATENESS; if one appears but amount_with_tax is not 108.00, it alerts on CORRECTNESS with the actual observed value attached. This continuous check catches a transformation-logic regression (say, a recent deploy that broke the tax calculation) within 10 minutes, long before it would otherwise be noticed via a downstream business complaint about wrong totals.

Trade-offs and pitfalls

Checking correctness, not just arrival, is what makes a canary meaningfully more valuable than a basic heartbeat/liveness check, a pipeline can be "alive" (still processing records, still writing output) while a specific transformation is silently wrong, which only a value-correctness check on a known input would catch. The pitfall is a canary record that's distinguishable enough to filter out of real metrics and dashboards (you don't want canary traffic polluting real business metrics) but realistic enough that it actually exercises the same code path as production data, an overly artificial canary that takes a shortcut through simplified logic wouldn't actually validate the real transformation path, defeating the purpose.

Beyond the pipeline canary itself, it's worth also testing the MONITORING and ALERTING logic before it goes live: inject a synthetic failure (a deliberately wrong canary value) in a staging environment and confirm the expected alert actually fires and maps to the right runbook, so you're not discovering a broken alert rule for the first time during a real incident.

Direct answer

An experiment-tracking system for ML needs to record run metadata (hyperparameters, code version, environment), metrics over time (training/validation loss and other tracked quantities), and artifacts (model checkpoints, logs, plots), because each of these three categories supports a different but essential need: reproducing a run exactly, comparing runs to each other, and recovering a specific trained model.

Structured elaboration

• Hyperparameters and configuration: every tunable setting used for the run (learning rate, batch size, model architecture choices, random seed); this is what lets you answer "what configuration produced this result" and compare configurations across runs to understand what mattered.
• Code version: the exact git commit hash (or equivalent) of the training code used, since the same hyperparameters with different code can produce very different results, and without this, a run's result can't be trusted to be reproducible even with everything else recorded.
• Environment: the software environment (container image tag, or a dependency lockfile snapshot), since, as discussed for reproducibility more broadly, different library versions can change numerical behavior even with identical code and hyperparameters.
• Metrics over time: not just the final metric value but the full time series (loss/accuracy per step or epoch), which supports diagnosing training dynamics (did it diverge briefly and recover, was convergence unusually slow), not achievable from a final-value-only record.
• Artifacts: model checkpoints (so the trained model itself is recoverable, not just knowledge that a run happened), and any generated plots/logs useful for later review without needing to re-run the experiment.
• How this supports reproducibility, collaboration, and governance: reproducibility is directly served by the hyperparameters/code/environment triad (everything needed to exactly redo the run); collaboration is served by making all of this discoverable and comparable across a team (rather than trapped in one person's local notes or terminal history); governance (audit, compliance, model lineage in a production environment) is served by the combination of all of the above providing a complete, traceable record of exactly how any deployed model was produced.

Worked example

A logged run record: {run_id: "r-2847", commit: "a3f92e1", hyperparameters: {lr: 3e-4, batch_size: 256, seed: 17}, environment: "training:v2.3.1-cuda12.1", metrics: [{step: 100, loss: 2.31}, {step: 200, loss: 1.87}, ...], artifacts: {checkpoint: "s3://bucket/r-2847/model.ckpt", plots: ["s3://bucket/r-2847/loss_curve.png"]}}; from this single record, another team member could exactly reproduce the run's environment and configuration, understand its training dynamics from the metric time series, and directly load the resulting trained model from the artifact reference, without needing to ask the original author anything.

Trade-offs & pitfalls

Tracking systems that only log the final metric value (not the full time series) or only the hyperparameters (not the code commit and environment) provide a false sense of completeness; a run record missing even one of the three categories (config, metrics-over-time, artifacts) leaves a real gap in either reproducibility, diagnosability, or recoverability that surfaces painfully later, usually when someone actually needs the missing piece.

How I would investigate
I would compare the query profile, not just the SQL text. A CTE, or common table expression, is a named subquery. On some warehouses it is inlined, and on others it may be materialized or repeatedly scanned. I would look for repeated reads of the same large table, lost predicate pushdown, and big intermediate results.

First checks

• Run EXPLAIN or the warehouse query profile.
• See whether the same base table is scanned more than once.
• Check whether filters are applied after large joins instead of before them.
• Compare row counts at each step to find the explosion point.

What I would change first
If a suspicious CTE is referenced once, I would inline it or fold it into the parent query. If it is reused multiple times, I would stage the filtered result in a temp table or a materialized table so the expensive work happens once.

Rule of thumb
Readability is good, but if separate CTEs each reread a billion-row fact table, the query gets slower. The fix is usually to push filters earlier, keep only needed columns, and avoid recomputing the same join or aggregation in multiple branches.

Direct answer

In the moment, don't defend the conclusion, invite the specifics: ask which number or chart looks selective, and offer to walk through the underlying data live if you can. That converts a vague credibility attack into a concrete, checkable claim, which is the only kind you can actually resolve. Over the following weeks, the real fix is making your process visibly checkable by default, not just re-litigating this one dataset.

Structured elaboration

1. In the room, acknowledge the seriousness of the accusation without agreeing with it ("that's fair to want to be sure of" is different from "you're right, we might have"), then ask for the specific number or chart in question. "Cherry-picking" is an accusation about a specific choice, not a vague vibe, and it should be answerable as one.
2. If you can show the underlying query or filter live, do it. Transparency in the moment is more convincing than any verbal defense.
3. If you can't resolve it live, the data isn't in front of you, or it's more involved than a quick look, commit to a specific follow-up with a date, not an open-ended "we'll look into it."
4. Protect anyone else in the room whose work is being questioned, not just your own position. If the report being challenged is a teammate's, say you'll review it together and that you stand behind the process, without personally vouching for a conclusion you haven't independently checked yet.
5. Afterward, the fix isn't a one-time rebuttal, it's making the methodology reviewable by default (documented definitions, visible filters, reproducible queries) so the next accusation, fair or not, gets resolved by pointing at the artifact instead of relitigating credibility from scratch.

Worked example

In a cross-functional review, a stakeholder says your team "cherry-picked the numbers to make this initiative look better than it is." You ask: "which chart looks off to you, is it the retention numbers or the revenue attribution?" They point to the retention chart. You pull up the filter live: the date range was chosen to match the initiative's actual launch date, not to flatter the result, and you show that in real time. Over the following weeks, you publish the filter logic and date-range rationale alongside the dashboard by default, so the next reviewer doesn't have to ask.

A variant of this same moment is worse in a specific way: a senior executive looks at a specific analyst's report and says, in front of the group, "this is just wrong," with no detail about what's wrong. The analyst is in the room and visibly rattled. You step in before the analyst has to defend themselves alone: "can you point to the specific number that looks off, we'll walk through the methodology together right now," which does two things at once, it forces the vague accusation to become a specific, checkable one, and it signals to the room that the analyst isn't standing alone under an unspecified attack. After the meeting, you follow up with the analyst privately too, since being publicly called "wrong" with no detail is its own hit to confidence, separate from whatever the actual data issue turns out to be, and that needs acknowledging even once the technical question is resolved.

Trade-offs and pitfalls

• Getting defensive or citing your team's track record instead of the specific number in question makes it sound like you're avoiding the check, even when your work is solid.
• Promising instant certainty before you've actually looked can back you into a worse spot if the live check turns something up you didn't expect. It's fine to say "let me pull that up" and take a minute.
• Fixing only the disputed metric, and not the underlying reviewability gap, means the same accusation, fair or not, recurs on the next dashboard.
• Rushing to defend a teammate can tip into speaking over them or implying they can't defend their own work. The goal is to stop them from having to defend it alone in an unfair moment, not to take over entirely.

Direct answer

Design the sink so writes are naturally idempotent, typically an upsert keyed by a deterministic identifier derived from the source event (its partition and offset, or a stable business key), so a replayed write after a restart produces the identical row rather than a second one.

Structured elaboration

Without distributed-transaction support at the sink, you can't get the streaming job's checkpoint and the sink's write to commit as a single atomic unit; the workaround is to make the write itself safe to repeat. Concretely: derive a deterministic key for every write (source offset, or a natural key already unique to the business event), and express the write as "set this row's value" (an upsert) rather than "append a new row." On a restart-and-replay after a crash, the same source records get reprocessed and re-written with the identical key and value, so the external database simply overwrites the same row with the same content, producing no visible duplicate.

Worked example

A sink writing aggregated hourly revenue per product to a relational table keyed by (product_id, hour): an upsert (INSERT ... ON CONFLICT (product_id, hour) DO UPDATE SET revenue = excluded.revenue) means that if the job crashes after writing hour 14's revenue but before its checkpoint commits, and recovery reprocesses and rewrites hour 14's revenue again, the second write simply overwrites the row with (in this case) the same recomputed value, leaving no duplicate row and no incorrect double-counted total.

Trade-offs and pitfalls

This only works cleanly when the write is naturally expressible as an idempotent upsert; an operation with an external side effect (sending a notification, charging a payment) can't be made idempotent this way without an additional mechanism, typically an idempotency key checked and recorded at the point of the side effect itself, not just at the database write. A common mistake is deriving the upsert key from something that isn't actually stable across a replay (a randomly generated ID rather than the source offset or a genuine business key), which silently reintroduces duplicates despite the upsert pattern looking correct.