Google Data Engineer Interview Preparation Guide - Junior Level (1-2 Years)

Direct answer

When a WHERE clause mixes AND and OR conditions, correctness depends on how the boolean expression is grouped, not on the order you physically write the conditions in. SQL evaluates the whole boolean expression by its precedence rules (AND binds tighter than OR), and a NULL-aware condition (IS NULL / IS NOT NULL) has to be used deliberately, since NULL never satisfies = or <>. Get the grouping or the NULL handling wrong and the query silently returns the wrong rows with no error.

Structured elaboration

Three pieces make a mixed WHERE clause correct:

1. Equality flag: is_paid = TRUE is a plain boolean check. It works as expected on TRUE and FALSE rows (a NULL is_paid row is excluded too, since NULL = TRUE evaluates to unknown, not true).
2. Date range: prefer a half-open interval, order_date >= start AND order_date < end, over BETWEEN. BETWEEN is inclusive on both ends, which can double-count a row that lands exactly on the boundary between two adjacent ranges.
3. NULL-aware condition: any column that can be NULL (a cancellation timestamp, a discount code) needs IS NULL / IS NOT NULL, not = NULL or <> value. column <> value evaluates to NULL (not TRUE) for a NULL row, so that row is silently dropped even when the intent was to include it.

Where correctness actually gets lost is precedence: AND binds tighter than OR. WHERE a AND b AND c OR d parses as (a AND b AND c) OR d, not a AND b AND (c OR d). Whenever AND and OR sit in the same WHERE clause, wrap the OR branch in explicit parentheses. That grouping is the "structure" that matters. Physical execution order does not: the optimizer is free to reorder predicates as long as the logical result is preserved.

Worked example

Sample data (orders), tested in DuckDB 1.5:

Goal: paid orders placed in Q1 2024 that are either not cancelled, or cancelled but refunded.

Correct (OR branch parenthesized):

SELECT order_id
FROM orders
WHERE is_paid = TRUE
  AND order_date >= DATE '2024-01-01'
  AND order_date <  DATE '2024-04-01'
  AND (cancelled_at IS NULL OR refunded = TRUE)
ORDER BY order_id;

Result: order_id = 1, 2 (2 rows). Order 3 is correctly excluded (cancelled and not refunded), order 4 is excluded (too early), order 5 is excluded (unpaid), order 6 is excluded (too late).

Buggy (same conditions, parentheses removed around the OR):

SELECT order_id
FROM orders
WHERE is_paid = TRUE
  AND order_date >= DATE '2024-01-01'
  AND order_date <  DATE '2024-04-01'
  AND cancelled_at IS NULL OR refunded = TRUE
ORDER BY order_id;

Result: order_id = 1, 2, 5 (3 rows). Order 5 leaks in even though it is unpaid and from March, because the clause is now (is_paid AND date_range AND cancelled_at IS NULL) OR refunded = TRUE, and refunded = TRUE alone satisfies the second half of the OR.

Trade-offs & pitfalls

• Always parenthesize an OR branch that sits inside a longer AND chain, even when the grouping "looks" unambiguous. The next person editing the query may not preserve your intended precedence.
• Prefer half-open date ranges (>= start AND < end) over BETWEEN for timestamp columns, to avoid double-counting or off-by-one-day gaps caused by boundary values.
• column <> value silently excludes NULL rows. If NULLs should be included, add OR column IS NULL explicitly, do not assume <> covers it.
• Some engines will not warn on a missing parenthesis. The query just runs and returns a wrong but plausible-looking row count, which is why this bug class tends to survive casual review.

Direct answer

Move to a dedicated warehouse when ad-hoc analytical queries start measurably hurting the production database's transactional performance, when the same numbers are being computed slightly differently in different spreadsheets, or when reporting needs data joined across sources the production database does not have (a payments processor, a support tool, a marketing platform). Start with the simplest useful version: a small set of tables that are periodically copied out of production into a separate database or a managed cloud warehouse, denormalized just enough to answer the handful of reports people actually run today, not a fully modeled Kimball bus architecture with conformed dimensions across every future business process.

Structured elaboration

Signal one: production impact. A heavy analytical query (a full table scan for a monthly report, say) run directly against the database serving live user traffic can degrade transactional latency for real users; if analysts are being asked to "only run reports at night" or engineers are seeing production incidents traced to a report someone ran, that is a concrete, observable signal, not a vague sense that things feel slow.

Signal two: inconsistent numbers. Once more than one person is computing the same metric independently (one analyst's spreadsheet formula, another's ad-hoc query), small differences in filtering or date handling silently produce different answers to "what was our revenue last month," and nobody notices until two answers are compared in the same meeting. This is the earliest, cheapest form of the exact conformance problem later covered by dimension conflicts across marts; catching it before it compounds is far cheaper than the reconciliation project.

Signal three: joining across sources. Once a report needs to combine production order data with a separate support tool's ticket data and a third-party payment processor's transaction data, there is no single production database to query against anymore; some place has to receive copies of all three and let them be joined together, which is the core job a warehouse exists to do.

The simplest version to build first. Do not start with a full dimensional model. Start with a small, straightforward extract-and-load process (even a scheduled job that copies a handful of production tables into a separate database or a managed cloud warehouse on a nightly cadence) and let analysts query those copies directly, denormalized or lightly modeled, for exactly the reports people already run. Introduce actual dimensional modeling (declared grain, a real date dimension, slowly-changing-dimension handling) only once a second or third report reveals that ungoverned ad-hoc structure is producing inconsistent answers or is too slow to maintain by hand, which is the point at which the methodology and system-design questions the rest of this topic covers actually become relevant.

Worked example

A single unindexed analytical query scanning a 10-million-row production orders table for a monthly report can hold a lock or consume enough I/O bandwidth to add hundreds of milliseconds to unrelated transactional queries hitting the same table concurrently; at a company processing customer-facing checkout requests against that same table, a delay large enough for customers to notice during checkout is the concrete, business-visible cost of skipping a warehouse, not an abstract inefficiency. That single observation, "a report degraded checkout latency," is usually the moment a 20-person startup's engineering leadership actually approves the investment, well before any of the modeling-methodology questions in this topic become the operative concern.

Trade-offs and pitfalls

The most common mistake at this stage is over-building: reaching for a full Kimball-style bus architecture, multiple conformed dimensions, and Type 2 slowly changing dimension (SCD) history tracking before there is more than one or two reports that need any of it wastes engineering effort the startup does not have to spare, and most of that early investment will be redesigned anyway once real reporting needs are better understood. The opposite mistake, waiting until the production database is visibly struggling before doing anything, is also common and more expensive to unwind, since by then inconsistent numbers have usually already reached several audiences and eroded trust in whichever spreadsheet or dashboard people were relying on.

Try to safely cast the cleaned string and flag whatever doesn't survive the cast, rather than trying to enumerate every possible malformed pattern up front.

Approach

SELECT id, amount_text,
  CASE
    WHEN REGEXP_MATCHES(amount_text, '^\s*\(.*\)\s*$')
      THEN -1 * TRY_CAST(REGEXP_REPLACE(amount_text, '[^0-9.]', '', 'g') AS DECIMAL(12,2))
    ELSE TRY_CAST(REGEXP_REPLACE(amount_text, '[^0-9.\-]', '', 'g') AS DECIMAL(12,2))
  END AS amount_clean,
  (CASE
    WHEN REGEXP_MATCHES(amount_text, '^\s*\(.*\)\s*$')
      THEN -1 * TRY_CAST(REGEXP_REPLACE(amount_text, '[^0-9.]', '', 'g') AS DECIMAL(12,2))
    ELSE TRY_CAST(REGEXP_REPLACE(amount_text, '[^0-9.\-]', '', 'g') AS DECIMAL(12,2))
  END) IS NULL AS amount_error
FROM sales_raw;

Parenthesized amounts like '(500.00)' are detected up front by matching the whole string against a leading '(' and trailing ')', and are negated after stripping the parens themselves; anything else falls through to the original strip-and-cast path. Stripping everything except digits, the decimal point, and a minus sign handles common currency-symbol and thousands-separator noise ('$1,234.56' becomes '1234.56'); TRY_CAST (or SAFE_CAST, depending on dialect) returns NULL instead of erroring on anything that still isn't a valid number after cleaning, which is exactly the signal you want to flag as amount_error.

Worked example

"$1,234.56" cleans to "1234.56" and casts successfully; "(500.00)", the parenthesized-negative accounting convention, is detected by the leading/trailing-paren check and casts to -500.00 rather than silently losing its sign (verified: without this check the same string casts to a wrong, positive 500.00 with no error flag raised); "1.234,56" (a European locale convention) cleans to "1.23456" under this simple stripping rule and casts to the WRONG number rather than failing outright, which is the real danger of a purely mechanical strip-and-cast approach: it can silently produce a plausible-looking but wrong value instead of a clean failure.

Trade-offs and pitfalls

Because of that European-locale trap, a single global stripping rule is only safe once you've confirmed all your data uses ONE locale convention; if multiple locale conventions are genuinely mixed in the same column, you need to detect which convention a row is using (for example, by checking whether a comma or a period appears last) before choosing how to strip it, rather than applying one rule blindly to everything. The parenthesized-negative case is the same class of risk: any format convention the strip-and-cast approach doesn't explicitly detect will silently produce a plausible but wrong number rather than a clean failure, so before shipping this kind of check, enumerate every convention actually observed in a sample of the real data (currency symbols, thousands separators, parens, locale decimal/thousands swaps) and add an explicit branch for each one rather than assuming the stripping regex alone covers them. The same casting-and-flagging pattern reused here for currency strings applies unchanged to a column that changed storage type over time (VARCHAR with noise in old partitions, clean NUMERIC in new ones): identify the bad old-partition rows with the same TRY_CAST-is-NULL check before planning the backfill.

Hybrid architecture:

• Ingest: use a streaming layer (Kafka) with lightweight producers.
• Low-latency store: materialize near-real-time aggregates in a fast OLTP/NoSQL store (Redis, RocksDB-backed service, or ClickHouse with TTL) for second-level metrics and drilldowns.
• Analytical store: periodically batch/stream into a columnar warehouse (Snowflake, BigQuery, ClickHouse) for complex ad-hoc queries.
  Schema choices:
• For real-time layer: schema-normalized key-value with pre-aggregated counters and event windows; support fine-grained keys for drilldowns.
• For warehouse: denormalized wide tables or fact+dimension model optimized for columnar scans.
  Syncing: use CDC/streaming transforms (Kafka Streams or Flink) to update real-time store and write compressed events to warehouse. Maintain materialized views and rollups in both stores.
  Query routing: route interactive dashboard queries to real-time store for recent windows (seconds) and to warehouse for deep historical analysis with fallbacks.
  Trade-offs: real-time store optimized for latency and limited ad-hoc flexibility; warehouse for flexible analytics. Use consistent aggregation semantics and eventual convergence strategies.

Direct answer

Use event-driven capture where the source can emit change events, and fall back to polling only where it cannot, since event-driven capture keeps the catalog current with far less wasted work; for a warehouse, subscribe to schema-change and table-creation events where the platform supports them; for a streaming platform, consume its own schema-registry change stream; for flat files in object storage, subscribe to the storage layer's object-created notifications rather than repeatedly listing the bucket. When a source is temporarily unavailable, mark its metadata as stale rather than deleting or blanking it, so the catalog's view degrades gracefully instead of corrupting.

Structured elaboration

Poll versus event-driven, per source type:

• Warehouse: most modern warehouses expose either native change events or queryable system tables for schema and object changes; event-driven capture (subscribing to those events) avoids the catalog re-scanning the entire warehouse's metadata on a fixed interval just to notice one new table.
• Streaming platform: schema changes are typically registered through a schema registry that itself can emit change notifications; consuming that stream keeps the catalog current within moments of a schema evolving, rather than discovering it on the next scheduled poll.
• Flat files in object storage: object storage systems commonly support event notifications on object creation, deletion, or modification; subscribing to those events avoids the alternative of periodically listing an entire bucket (expensive and slow at scale) just to detect new files.

Polling is still the right fallback where a source genuinely offers no event mechanism, or as a periodic reconciliation pass even alongside event-driven capture, to catch any event that was silently dropped (event delivery is rarely perfectly guaranteed, so an infrequent reconciliation poll is the backstop, not the primary mechanism).

Handling a source that's temporarily unavailable without corrupting the catalog's view:

• Never let a failed harvest overwrite existing metadata with an empty or partial result; a harvester that cannot reach its source should skip the update entirely and leave the last-known-good metadata in place.
• Explicitly mark the affected source's catalog entries as stale as of <timestamp> when the last successful harvest is older than the source's expected freshness window, so a consumer sees a clear staleness signal rather than data quietly going out of date with no indication.
• On recovery, reconcile rather than blindly overwrite, compare the newly-harvested state against the last-known state and apply the diff, which also surfaces exactly what changed while the source was unreachable (useful context, not just a return to normal operation).

Worked example

The object-storage source for a raw_events dataset goes unreachable for six hours due to a permissions misconfiguration on the storage bucket. The event-driven harvester, unable to connect, does not clear or overwrite the dataset's existing catalog entry; instead, after the source's expected freshness window (say, new files are expected hourly) passes without a successful harvest, the catalog entry is automatically flagged stale, last confirmed 3 hours ago. An analyst searching the catalog during this window sees the staleness flag alongside the (still-present, still-usable) prior metadata, rather than either a missing entry or metadata that silently looks current when it is not. Once the permissions issue is fixed, the harvester reconnects, event notifications for the files that arrived during the outage are replayed (most object-storage event systems retain a window of undelivered notifications), and the catalog reconciles to the current state, clearing the staleness flag.

Trade-offs & pitfalls

Event-driven capture is not free of gaps, event delivery systems can drop or delay a notification, so relying on it exclusively, with no periodic reconciliation poll at all, risks a catalog that silently drifts without ever showing a staleness flag, because the harvester technically "succeeded" on a smaller set of events than actually occurred. A reconciliation poll set too infrequently defeats its own purpose as a backstop; set too frequently, it reintroduces the cost that event-driven capture was meant to avoid. Marking sources stale rather than removing them protects against corruption but shifts the burden onto consumers to notice and respect the staleness flag, if the search and filtering interface does not surface staleness prominently, an analyst can still act on out-of-date metadata despite the flag technically being present.

Direct answer

Compressing a three-month ramp into three weeks means deliberately not becoming broadly competent and instead becoming narrowly reliable on exactly what the project needs, while being explicit about what I'm skipping and how the resulting gap gets covered, whether that's a reviewer, a narrower scope, or stated uncertainty on anything I can't fully back. I would never let three weeks of learning quietly pass as equivalent to three months; the compression only works if everyone downstream knows what they're actually getting.

What compression actually means

Triage by what the project needs, not by the team's usual onboarding order. A normal three-month ramp typically builds broad familiarity before depth. With three weeks, I invert that: identify the two or three things this specific project actually requires me to be right about, and go deep only there, accepting shallow or absent knowledge everywhere else. If the timeline compressed further, to a single day, the triage gets sharper still: I would ask what one piece of context, if I got it wrong, would sink the project, and spend almost all the time there, explicitly skipping everything else rather than spreading thin.

Name the quality bars I refuse to drop even under compression. Compression is about learning less, not about shipping unverified work. I would still hold the same review and testing standards for anything I produce, even if the compressed ramp buys speed on learning but never on care.

Lean on other people's time, and be honest about the cost. The fastest lever available is borrowing a domain expert's attention instead of self-teaching everything from scratch, but that time is not free. I would be specific with the team about how much of someone's time I'm asking for and for how long, rather than letting it show up later as their own work quietly slipping.

Cover the gap with structure, not bravado. Where I know I'm still shallow, I build in a mandatory review step, narrow the scope of what I own until I catch up, or explicitly flag deliverables as carrying more uncertainty than the team's usual standard, rather than letting a compressed ramp quietly lower the bar without anyone deciding that on purpose.

Worked example

Joining a project three weeks before a launch, with the team's usual ramp closer to three months, I asked the lead directly what single area, if I got it wrong, would actually hurt the launch. The answer was one integration point with a partner system, so I deliberately left everything else about the surrounding codebase thin. I spent roughly half of the three weeks almost entirely on that integration, pairing daily with the engineer who owned it, which meant asking for about six hours a week of her time, made explicit up front rather than assumed. For the parts I stayed shallow on, I did not pretend otherwise: I flagged two areas in my own handoff notes as reviewed by me but not independently verified, and asked for an extra reviewer on anything touching them until I had more time. The launch shipped on schedule; the cost was that a change I made in one of the flagged areas weeks later took noticeably longer because I was still building real familiarity with it, a cost I had knowingly deferred rather than avoided.

Trade-offs and pitfalls

The core trade-off is depth for speed: three weeks buys narrow reliability, not the broad judgment three months would have given, and pretending otherwise is the real risk, not the compression itself. The most common pitfall is letting the compressed timeline quietly lower quality bars along with breadth, when only breadth should be sacrificed. A second pitfall is treating borrowed expert time as free; if it isn't planned and bounded, the person you leaned on absorbs the cost you didn't.

Direct answer. Choosing between on-demand, reserved, and spot instances for a continuously-running ETL cluster with predictable daily peaks is a risk-adjusted cost model: reserved capacity covers the predictable BASELINE load at a discount for committing, on-demand covers genuine uncertainty at full price, and spot covers the discretionary/interruptible portion of load at the steepest discount but with real preemption risk that must be actively mitigated, not ignored.

Structured elaboration.

1. Inputs to model. Price curves for each instance type/commitment tier (reserved discount depends on commitment length and payment structure; spot pricing varies by instance type, region, and time, and is genuinely variable, not fixed); preemption probability for the specific instance types under consideration (varies significantly by type and region, and should be checked against recent historical data, not assumed uniform); recovery cost (what does it cost in time and reprocessing if a spot instance is preempted mid-job -- this is workload-specific and depends heavily on checkpointing granularity); SLA penalties (what is the actual cost, financial or reputational, of missing the job's deadline due to preemption-driven delay).
2. How to simulate risk. Model the job's completion-time distribution under a range of assumed preemption rates (not just the expected/average rate, but a pessimistic tail scenario), incorporating recovery cost per preemption event, to see how often a spot-heavy strategy would plausibly miss the SLA under realistic variation, not just under the average case.
3. Mitigation strategies for spot risk. Checkpointing (frequent enough that a preempted instance loses only a bounded amount of work, directly bounding recovery cost); redundancy (running critical path work with some over-provisioning or diversified instance types/AZs, so a preemption in one pool does not stall the whole job); capacity buffer (keeping enough non-spot capacity available to absorb a preemption event without missing the SLA, essentially a small reserved/on-demand cushion sized against the simulated worst case).
4. Allocation across the three tiers. A common pattern: size the RESERVED tier to the predictable daily baseline (the load level the cluster needs essentially every day, captured at a discount for committing to it), fill discretionary/batch-tolerant capacity above that baseline with SPOT (since ETL workloads with good checkpointing tolerate preemption well and this capacity is the most price-sensitive), and reserve ON-DEMAND as the fallback that absorbs whatever spot capacity does not cover during a preemption event or an unexpected demand spike, rather than being a primary tier.

Worked example. A cluster with a predictable daily peak needing roughly 20 baseline nodes continuously plus up to 15 additional nodes during a 4-hour daily peak window: reserve roughly 20 nodes (the always-needed baseline) at a reserved-instance discount; run the 15 peak-window nodes on spot, since the ETL job's checkpointing (say, every 10 minutes) bounds worst-case rework from a preemption to a small fraction of the 4-hour window; keep a small on-demand buffer (e.g., 3-5 nodes) available to absorb a spot-capacity shortfall during the peak window without missing the SLA. The risk simulation, run against the region's recent historical spot-preemption rate for the chosen instance type, confirms this allocation meets the SLA in the large majority of simulated days even accounting for occasional preemption clusters.

Trade-offs & pitfalls. Assuming a SINGLE, static preemption probability rather than modeling its variance (preemption rates can spike sharply during broader regional demand surges, not stay at their historical average) is the most common way a spot-heavy strategy that looked safe on paper fails in production exactly when everyone else is also competing for the same discounted capacity. The reserved-capacity commitment is itself a real financial risk in the other direction: over-committing reserved capacity against a baseline that later shrinks (e.g., after a later optimization effort reduces the cluster's actual resource needs) locks in cost that can no longer be avoided for the commitment period.

Direct answer

Write-audit-publish means landing new data in a staging location consumers can't see yet, running data-quality checks (the audit) against it while it is still invisible, and only then making it visible with a single atomic operation (a partition swap or a pointer flip), rather than publishing first and validating afterward. The audit step exists to solve the problem of consumers reading bad or partial data during a load: without it, "load then check" means anyone querying during or right after a bad load already saw the wrong numbers before any check had a chance to catch it.

Structured elaboration

The problem this solves. A pipeline that writes directly into the table or partition consumers actually query, then validates afterward if at all, leaves a window between when writes start and when a check would have failed, during which consumers can read incomplete or wrong data with no visible signal anything is wrong.

The three stages:

1. Write. Land the new batch in an isolated location, a staging table, a new partition or version, a new file set, that existing queries against the published location cannot see at all.
2. Audit. Run validation against that staged data while it remains invisible to consumers: row counts against expectation, null rates, schema conformance, referential checks, business-rule checks.
3. Publish. Only if the audit passes, flip visibility with a single atomic operation, an atomic partition swap, a metadata pointer update, or a table rename, so consumers see either the previous, already-audited state or the new, now-audited state, never a partial mix of the two.

Why the publish step has to be atomic. If publishing itself were multiple steps (remove old rows, then insert new ones), a reader querying in the middle could see an inconsistent partial state even after the audit already passed. The pattern only fully solves the visibility problem if the very last step is a single, indivisible flip.

What happens when the audit fails. The staged batch is simply never published. Production keeps serving the last good, already-audited state, and the failed batch goes to remediation instead of ever becoming visible to anyone.

Where it fits and where it doesn't. This suits batch or micro-batch loads into a location with many downstream readers who can't each validate before consuming, like a shared warehouse table. Strict low-latency streaming, where staging and swapping an entire batch isn't practical, achieves the same underlying goal (don't let one bad unit block or corrupt everyone) through per-record dead-letter handling instead.

Worked example

A daily table normally holds 2,000,000 rows. A join bug in that day's load causes fanout, and the staged batch lands with 2,600,000 rows:

If the audit's row-count check allows a tolerance band of plus-or-minus 5 percent around the historical baseline, a 30 percent deviation fails it clearly, so the atomic swap never happens. Consumers keep reading yesterday's correct 2,000,000-row table while the bad batch sits quarantined and the join bug gets fixed, instead of every downstream report briefly, or permanently until someone happens to notice, reflecting a 30 percent inflated count.

flowchart LR
    A[New batch written to staging] --> B[Audit checks run on staged data]
    B -->|pass| C[Atomic publish: swap or pointer flip]
    B -->|fail| D[Staged data quarantined]
    C --> E[Consumers read new data]
    D --> F[Remediation and retry]
    F --> A

Trade-offs & pitfalls

• Auditing the already-published table instead of the staged one only catches the problem after consumers may already have read bad data; the ordering, audit before publish rather than after, is the entire point of the pattern.
• Making publish itself a multi-statement operation reintroduces the exact partial-visibility problem the pattern exists to prevent.
• The pattern trades some latency (data isn't visible until the audit finishes) and some storage (staged and published copies briefly coexist) for that safety; that's a clear win for a widely-consumed shared table, and a less obvious win for a low-stakes, single-consumer dataset where the extra latency may not be worth it.
• A common wrong turn is treating "we run some validation somewhere in the pipeline" as equivalent to write-audit-publish; the pattern specifically depends on the audited data being invisible to consumers until it passes, not just checked at some point along the way.

Approach: compute yearly and cumulative cash flows for each option (no discounting), compare cumulative totals to find when Buy is cheaper or when they equalize.

Numbers:

• Buy: License = $200k/yr, Implementation & training = $100k (Year 1 only), Maintenance = 15% * 200k = $30k/yr.

  • Year 1 cost = 200k + 100k + 30k = $330k → Cumulative Y1 = $330k
  • Year 2 cost = 200k + 30k = $230k → Cumulative Y2 = $330k + $230k = $560k
  • Year 3 cost = 230k → Cumulative Y3 = $560k + $230k = $790k

• Build: Initial development = $400k (Year 1), Annual maintenance = $80k/yr, Hardware = $50k/yr.

  • Year 1 cost = 400k + 80k + 50k = $530k → Cumulative Y1 = $530k
  • Year 2 cost = 80k + 50k = $130k → Cumulative Y2 = $530k + $130k = $660k
  • Year 3 cost = 130k → Cumulative Y3 = $660k + $130k = $790k

Result: Buy is cheaper in Year 1 and Year 2. At the end of Year 3 cumulative costs are equal ($790k) — break-even occurs at year 3.

Non-financial factors to influence decision:

• Time to value / time-to-market: Buy usually faster to deploy; important if you need quick ingestion/analytics.
• Customization & fit: Build allows tailored pipelines, connectors, schema needs; buy may require workarounds.
• Operational burden: Build shifts maintenance, bug fixes, on-call to your team; Buy offloads support to vendor.
• Scalability & performance: Consider vendor’s proven scaling vs your capacity to design for scale (Spark/cluster ops).
• Talent & opportunity cost: Do you have engineers to build/maintain, or are they better used on core data products?
• Vendor lock-in, roadmap alignment, SLAs, compliance/security, observability, and upgrade paths.
  Recommendation (data-engineer lens): If you need fast integration and limited custom logic, Buy is attractive short-term and through Year 2. If long-term control, deep custom processing, or lower variable costs matter and you have bandwidth, Build becomes equally justified by Year 3 — choose Build only if non-financial benefits outweigh the immediate operational cost and risk.

Direct answer

Deduplicate on the natural event key (not on the full row) using a deterministic ranking window function, row_number() over partitionBy(event_key).orderBy(ingest_time, tie_breaker), keeping rank 1, rather than dropDuplicates(), because dropDuplicates gives no control over WHICH copy of a duplicate is kept when duplicates disagree in any other column, and late-arriving duplicates specifically need a clear, explicit "first write wins" (or "last write wins", depending on the business rule) policy rather than whatever Spark's internal execution happens to keep.

Structured elaboration

Why not just dropDuplicates(["event_id"])? It works for correctness (exactly one row per event_id survives) but which of several duplicate rows survives is unspecified when they differ in other columns (for example, differing ingest_time due to late arrival, or a corrected payload in a later duplicate); relying on unspecified behavior for a business-meaningful choice (keep the FIRST-seen copy, or keep the copy with the LATEST correction) is fragile and the correct policy varies by domain, so it should be explicit in the query, not implicit in the engine's execution order.

Design for late-arriving duplicates specifically. "Late-arriving" means the SAME logical event can appear again in a LATER micro-batch or a later partition of a batch job, potentially long after the original. A pure in-memory Set-based dedup (as would work for a single bounded batch entirely in memory) does not scale to multi-terabyte data and does not naturally handle duplicates arriving in separate batches days apart; a window-function approach that reads the FULL relevant history (or, for a streaming job, uses dropDuplicatesWithinWatermark bounded by a watermark) is what actually scales.

Shuffle volume. The dedup key's partitioning determines shuffle cost directly: partitionBy(event_id) shuffles the full dataset once (unavoidable, since duplicates of the same key must land in the same partition to be compared), proportional to total data volume, not to the number of DISTINCT keys. For a truly multi-terabyte dataset, this shuffle is the dominant cost of the whole job; there is no way to avoid it while guaranteeing correctness for arbitrarily-late duplicates, since any two duplicate rows could in principle be in different physical files that both need to be compared.

Memory. The window function's per-partition working set is bounded by the number of duplicate ROWS per key (usually small, a handful of copies at most), not by total partition size, so memory pressure here is driven mainly by partition-count/skew choices (the standard shuffle-sizing considerations), not by anything dedup-specific, UNLESS a small number of keys have pathologically many duplicate copies (a genuine data-quality bug upstream, worth alerting on separately).

Correctness under failures. If the dedup job itself fails partway and is retried, re-running the identical deterministic window-function query over the same (or a superset, if incrementally reprocessing) input produces the identical result, since row_number() with a fully deterministic order (including the tie-breaker) is a pure function of its input; this is a meaningfully stronger property than, for example, a stateful streaming dedup relying on an accumulating in-memory or state-store set that must itself be checkpointed correctly to survive a restart without either losing dedup state (letting a duplicate back in) or growing unboundedly.

Worked example

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

spark = SparkSession.builder.master("local[2]").appName("dedup").getOrCreate()

data = [
    ("e1", "u1", "2026-01-01 09:00:00", "2026-01-01 09:00:05"),
    ("e1", "u1", "2026-01-01 09:00:00", "2026-01-01 09:00:07"),  # duplicate of e1
    ("e2", "u1", "2026-01-01 09:01:00", "2026-01-01 09:01:02"),
    ("e3", "u2", "2026-01-01 08:50:00", "2026-01-01 09:05:00"),  # late-arriving original
    ("e2", "u1", "2026-01-01 09:01:00", "2026-01-01 09:06:00"),  # very late duplicate of e2
]
df = spark.createDataFrame(data, ["event_id", "user_id", "event_time", "ingest_time"]) \
    .withColumn("event_time", F.to_timestamp("event_time")) \
    .withColumn("ingest_time", F.to_timestamp("ingest_time"))

# First-ingest-wins policy, deterministic tie-break on user_id in case two
# copies share an identical ingest_time.
w = Window.partitionBy("event_id").orderBy("ingest_time", "user_id")
deduped = (df.withColumn("rn", F.row_number().over(w))
             .filter(F.col("rn") == 1)
             .drop("rn")
             .orderBy("event_id"))

deduped.select("event_id", "user_id", "event_time", "ingest_time").show(truncate=False)
print("distinct event_ids:", deduped.select("event_id").distinct().count())
print("row count after dedup:", deduped.count())

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

+--------+-------+-------------------+-------------------+
|event_id|user_id|event_time         |ingest_time        |
+--------+-------+-------------------+-------------------+
|e1      |u1     |2026-01-01 09:00:00|2026-01-01 09:00:05|
|e2      |u1     |2026-01-01 09:01:00|2026-01-01 09:01:02|
|e3      |u2     |2026-01-01 08:50:00|2026-01-01 09:05:00|
+--------+-------+-------------------+-------------------+
distinct event_ids: 3
row count after dedup: 3

The duplicate e1 copy (ingested at 09:00:07) and the very-late duplicate e2 copy (ingested at 09:06:00, four minutes after the original) are both correctly dropped, keeping the FIRST-ingested copy of each; the late-arriving genuinely-new event e3 (whose event_time is earlier than everything else, but which only arrived at ingest_time 09:05:00) is correctly kept as its own row since it is not a duplicate of anything, demonstrating that "late-arriving" and "duplicate" are independent properties this design handles separately and correctly.

Trade-offs and pitfalls

• Shuffle volume is proportional to total data scanned, not to how sparse the duplicates are. Even a dataset with a 0.01% duplicate rate pays the full shuffle cost of partitioning by event_id, because the dedup logic cannot know in advance which rows are duplicates without comparing them; this is the fundamental cost floor of exact, correctness-guaranteed dedup at scale, and it is not avoidable by a cleverer algorithm, only bounded by scoping the comparison window (see below).
• Bounding the comparison scope for genuinely unbounded lateness. For streaming or very-large incremental batch jobs, comparing every new row against the ENTIRE historical dataset is not sustainable; dropDuplicatesWithinWatermark (Structured Streaming) or an explicit business-defined lateness bound (e.g., "duplicates can arrive up to 7 days late, beyond that treat as a new event") trades perfect correctness for arbitrarily-late duplicates against bounded state size, which is usually the right trade-off in practice since unbounded state growth is itself a production risk (that trade-off in more depth).
• Correctness under failures depends on the dedup key and tie-breaker being FULLY deterministic. If the tie-breaker itself is non-deterministic (for example, relying on row physical order rather than an explicit column), a retry after a partial failure can pick a DIFFERENT duplicate copy to keep than the original attempt did, which is a subtle correctness bug that only manifests on retries, not on a clean single run, making it easy to miss in testing.
• Common mistake: deduplicating on a composite of MANY columns ("the whole row minus timestamp") instead of a true business key, which silently fails to catch duplicates that differ in any column at all (for example, a duplicate event re-sent with a corrected but different payload value is not a "duplicate" by a whole-row comparison, even though it represents the same underlying event and should be resolved by the same explicit keep-policy, not accidentally kept as two separate rows).