Junior Data Engineer Interview Preparation Guide - FAANG Standards

At real enterprise scale, the general lakehouse-versus-managed-warehouse trade-off sharpens around three things specifically: how concurrency actually behaves under load, what Atomicity, Consistency, Isolation, Durability (ACID) guarantees really mean for complex, high-volume writes, and how well each side handles mixed streaming-plus-batch ingestion without extra engineering effort.

What actually changes at scale

Concurrency. At moderate scale, both a lakehouse and a managed warehouse can serve many concurrent business intelligence (BI) users acceptably. At enterprise scale, a managed warehouse's concurrency handling (workload isolation, automatic scaling of independent compute clusters) tends to be more turnkey: the vendor has already solved the noisy-neighbor problem. A lakehouse can match this, but it usually requires deliberate compute-pool separation and tuning that the team has to design and maintain, rather than getting it largely for free from the platform.

Atomicity, consistency, isolation, and durability guarantees. Open table formats provide table-level ACID guarantees, which is a real and important upgrade over plain files. At enterprise scale, though, the volume and concurrency of writers (many pipelines committing to the same tables simultaneously) stresses that guarantee harder: metadata operations that are effortless with a handful of writers can become a real bottleneck with hundreds, and this is a genuine engineering problem a lakehouse team has to actively manage. A managed warehouse's transactional model is generally simpler to reason about (its query engine and storage are one integrated system), which is part of why it's easier to operate at scale with less specialized tuning, even though it offers less flexibility for complex multi-writer merge patterns.

Mixed streaming-plus-batch ingestion. A lakehouse's storage model is a natural fit for combining continuous streaming writes with periodic large batch loads into the same tables, because both are just writers committing to the same underlying table format. Managed warehouses have added streaming ingestion paths as well, but historically their strength was batch-oriented loading, so mixing a high-volume continuous stream with large batch jobs against the same warehouse tables is more likely to need careful workload management to avoid one interfering with the other.

Worked example

Consider an enterprise ingesting both a continuous stream of transaction events and nightly batch corrections from a legacy system into the same customer-activity table, served to hundreds of concurrent BI analysts. On a lakehouse, this is architecturally natural (both are writers against an ACID table), but the team needs mature compaction and metadata management to keep query performance from degrading as writer volume grows. On a managed warehouse, the batch and streaming paths might need to be more explicitly separated and reconciled, but the query-serving side to hundreds of concurrent analysts is more likely to just work at that scale without as much bespoke tuning.

Trade-offs and pitfalls

The pitfall at this scale is picking a side based on the same general trade-offs that applied at moderate scale, and being surprised when metadata scaling, writer concurrency, or workload isolation becomes the actual bottleneck rather than storage cost or basic query speed. Enterprise-scale lakehouse deployments succeed when the team genuinely invests in the operational discipline (compaction schedules, metadata monitoring, workload-isolated compute) the pattern requires; they struggle when it's adopted purely for the cost story without that investment.

Direct answer

Sort the intervals by their start value, then sweep through them once, keeping a running "last merged interval": if the next interval's start is at or before that interval's end (an overlap, or an exact touch), extend the running interval's end to cover it; otherwise, the next interval begins a new, separate merged interval. This is O(n log n) time, dominated entirely by the initial sort, and O(n) space for the output.

Structured elaboration

Why sort by start first. Without imposing an order, a single forward pass cannot be trusted to have already seen every interval that might overlap the one currently being examined; an interval appearing later in the input array could easily start earlier than one already processed. Sorting by start guarantees that once the sweep has moved past a given point, every remaining interval starts at or after it, which is exactly what makes a single linear sweep afterward sufficient.

The merge step itself. Initialize the output with the first (now sorted) interval. For each subsequent interval [s, e], compare s against the END of the last interval currently in the output. If s <= last.end, the two intervals overlap or exactly touch, so extend the last interval's end to max(last.end, e), using max rather than just assigning e directly, because a later interval can be fully NESTED inside the one already being extended (a smaller end than the current running interval), and assigning e unconditionally would incorrectly shrink coverage that the merged interval already legitimately spans. If s > last.end, there is a genuine gap, and [s, e] starts a new entry in the output.

Complexity. The sort is O(n log n) and dominates the total cost; the sweep afterward visits each interval exactly once, O(n). The output requires O(n) space in the worst case (no intervals overlap at all, so every input interval becomes its own output entry).

Touching versus overlapping, a design decision worth naming. Whether two intervals that exactly touch (one's end equals the next one's start, such as [1,4] and [4,5]) should merge is a real semantic choice, not an automatic consequence of the algorithm. Using <= in the overlap check treats a touch as mergeable (matching how meeting-room-style problems usually intend adjacency: back-to-back meetings occupy no coverage gap), while a strict < would keep them as separate, back-to-back intervals; a senior answer should state explicitly which convention is being used rather than leave it implicit.

Worked example

def merge_intervals(intervals):
    if not intervals:
        return []
    ordered = sorted(intervals, key=lambda pair: pair[0])
    merged = [list(ordered[0])]
    for start, end in ordered[1:]:
        last = merged[-1]
        if start <= last[1]:
            last[1] = max(last[1], end)
        else:
            merged.append([start, end])
    return merged

print(merge_intervals([[1, 3], [2, 6], [8, 10], [15, 18]]))

# Independent cross-check: a genuinely different method (an event-counting sweep
# line) rather than a second copy of the same merge logic.
def sweep_line_reference(intervals):
    if not intervals:
        return []
    events = []
    for s, e in intervals:
        events.append((s, 0, 1))   # start: delta +1, processed before ends at same coord
        events.append((e, 1, -1))  # end:   delta -1
    events.sort()
    result = []
    active = 0
    run_start = None
    for pos, _, delta in events:
        if active == 0 and delta == 1:
            run_start = pos
        active += delta
        if active == 0:
            result.append([run_start, pos])
    return result

import random
random.seed(3)
hand_picked = [
    [[1, 4], [4, 5]],             # touching intervals
    [[5, 8], [1, 3]],             # unsorted input
    [[2, 5], [1, 10]],            # fully nested interval
    [[1, 2]],                     # single interval
    [],                           # empty input
    [[5, 6], [1, 2]],             # unsorted, non-overlapping
    [[1, 3], [2, 6], [8, 10], [15, 18]],
]
mismatches = 0
for tc in hand_picked:
    if merge_intervals(tc) != sweep_line_reference(tc):
        mismatches += 1
for _ in range(3000):
    n = random.randint(1, 6)
    intervals = [[a, a + random.randint(0, 5)] for a in (random.randint(0, 15) for _ in range(n))]
    if merge_intervals(intervals) != sweep_line_reference(intervals):
        mismatches += 1
print(f"7 hand-picked cases + 3000-trial random sweep (seed 3): mismatches = {mismatches}")

Output:

[[1, 6], [8, 10], [15, 18]]
7 hand-picked cases + 3000-trial random sweep (seed 3): mismatches = 0

matching the question's own example exactly: [1,3] and [2,6] overlap (2 <= 3) and merge into [1,6]; [8,10] and [15,18] each start after the previous merged interval's end, so they remain separate. This was additionally cross-checked against the independently-implemented sweep-line reference above (treating each interval as a +1/-1 event at its start/end and rebuilding runs where the event count returns to zero), which is a genuinely different method rather than a second copy of the same merge logic, on 7 hand-picked cases (touching intervals, unsorted input, a fully nested interval, a single interval, empty input, and unsorted non-overlapping intervals) plus a 3,000-trial random sweep (seed 3); the printed line confirms zero mismatches across all of it, giving real cross-validation rather than testing the code against itself.

Trade-offs and pitfalls

The most consequential bug is assigning last[1] = end directly instead of last[1] = max(last[1], end) when merging: this passes every test case where intervals only partially overlap or extend each other, and only fails on a fully nested interval, which is easy to omit from a quick manual test set and then breaks silently in production by shrinking coverage that should have been preserved. A second common bug is skipping the initial sort, either because the candidate assumes input is already given in start order (sample inputs in problem statements are often, misleadingly, already sorted) or forgets that an unsorted input makes a single forward sweep unreliable; always sort explicitly rather than relying on an assumption about input order. Finally, treat the touching-versus-overlapping boundary as a decision to state out loud (<= merges touching intervals, < does not) rather than an arbitrary implementation detail, since which one is correct depends on what the intervals represent.

Direct answer

Once you know a partner function is measured on something different from you (speed versus quality or risk, for example), you scope your asks to be small and cheap under their metric, and you change what "status" means when you talk to them: short, action-oriented signals instead of the detailed risk narrative you'd give your own stakeholders. You're not changing what you need, you're changing how you package it so it doesn't read as a tax on the thing they're rewarded for.

Structured elaboration

1. Diagnose the incentive, don't assume it. Confirm what the partner function is actually measured on (deploy velocity, ticket close time, uptime, cost) rather than inferring it from how they push back. Different sub-teams within the "same" function can be measured differently.
2. Scope the ask to the smallest unit that gets you what you need. If they're speed-measured, don't ask for a broad, standing review of everything; ask for a narrow, well-bounded check on the specific surface that carries the risk you actually care about, and let everything else pass without friction.
3. Translate the ask into their currency. Instead of framing a request around your risk language, frame it around what it costs (or saves) them in their terms: incident response hours avoided, rework avoided, a compliance gate they'd otherwise hit later and more expensively.
4. Change the shape of status, not just the ask. For a speed-measured partner, give a compact signal (blocked/not blocked, a count, a single risk flag) they can act on in seconds. Save the fuller narrative for your own stakeholders who need the detail. Sharing the same long-form update with both audiences under-serves the partner who needs to move fast.
5. Keep a floor. Adapting your ask to their incentive has a limit: there's a minimum you can't compromise below without failing your own mandate. Know that floor before the conversation so "scoping down" doesn't quietly become "giving up the requirement."
6. Revisit as trust builds. Early asks are necessarily narrow and low-trust. As the partner sees your asks are well-scoped and your status updates are reliable, you can often widen the ask (a slightly broader review surface, more lead time) because they've learned you're not going to slow them down for nothing.

Worked example

A platform team is measured on release velocity; a security-minded partner function is measured on defect and incident rates. Rather than asking the platform team to route every change through manual security review (a direct tax on their velocity metric), the ask is scoped to only changes that touch a named risk surface, such as authentication or payment code. Everything else ships without added friction. Status to the platform team is a single weekly line: "2 changes in the review queue, 0 blocking, both cleared by Thursday." The fuller write-up, with rationale and residual risk, goes to the security function's own leadership, not to the platform team, because that's not the audience that needs it to act.

Trade-offs & pitfalls

• Pitfall: scoping the ask down so far it stops actually managing the risk it exists to manage. Know your floor before you negotiate.
• Pitfall: assuming the incentive instead of confirming it. Guessing wrong (e.g., treating a team as purely speed-driven when they're also on the hook for a compliance metric) leads to asks that miss what would actually land.
• Pitfall: sending the same status update to every audience. It either over-informs the speed-measured partner (who tunes it out) or under-informs your own stakeholders (who need the detail to make decisions).
• Senior differentiator: treating the ask size and the status format as things you design deliberately around the incentive gap, and revisiting that design as trust changes, rather than a fixed communication style you use with everyone.

Direct answer

A shuffle occurs whenever an operation needs rows sharing a key, or requiring a global order, to be physically co-located on the same partition: groupByKey, reduceByKey/aggregateByKey, join (unless one side is broadcast), distinct, sortByKey/orderBy, and explicit repartition/coalesce(shuffle=True). Shuffles are expensive because they combine three genuinely costly operations at once: network transfer (data physically moves between executors), serialization/deserialization (every record is serialized to write shuffle files and deserialized to read them back), and disk I/O (shuffle data is written to and read from disk, not kept purely in memory, plus a sort step on the read side for most shuffle implementations).

Structured elaboration

Operations that trigger a shuffle, organized by why. Aggregation-by-key operations (groupByKey, reduceByKey, aggregateByKey, combineByKey) need every value for the SAME key co-located to combine them. Join operations (join, cogroup) need matching keys from BOTH sides co-located, unless one side is small enough to broadcast (that alternative in depth). Set/uniqueness operations (distinct) need to compare every occurrence of a value against every other occurrence, wherever they originated. Ordering operations (sortByKey, orderBy) need a GLOBAL order across the whole dataset, which requires knowing the relative rank of every row against every other row, impossible to determine from a single partition's local view alone. Explicit repartitioning (repartition, coalesce with shuffle=True) directly and deliberately redistributes data across a new partition count.

Why each cost component is expensive, concretely.

• Network. Shuffle data crosses executor (and often physical node) boundaries; network bandwidth, even on fast cluster interconnects, is typically the slowest hop in the entire read-transform-write pipeline compared to local memory or even local disk access.
• Serialization. Every record written to a shuffle file is serialized (converted to bytes) on the map side and deserialized on the reduce side; this is real, non-trivial CPU cost per record, which is exactly why serializer choice (Kryo versus Java) directly affects shuffle-heavy job performance.
• Disk spill. Shuffle write output is written to LOCAL disk (not kept purely in executor memory) specifically so it can survive the writing executor's later removal, and the reduce side often needs an additional SORT step (for shuffle implementations requiring sorted output, relevant to the HashAggregate-vs-SortAggregate distinction) which itself may spill to disk if the data does not fit in the allocated sort buffer.

Spark UI / metric fields confirming a shuffle-heavy stage. In the Stages tab, per-stage summary metrics directly expose: Shuffle Read Size / Records and Shuffle Write Size / Records (non-trivial, non-zero values here directly confirm the stage involves a shuffle at all, and their magnitude relative to the stage's INPUT size indicates how much of the stage's total cost is shuffle-related versus pure computation); Shuffle Spill (Memory) and Shuffle Spill (Disk) (non-zero values here specifically indicate the shuffle's working set exceeded available execution memory and had to spill, a stronger signal of a shuffle-cost problem, not just a shuffle's PRESENCE); the query plan's Exchange node (visible via explain(), the direct correspondence between Exchange nodes and wide dependencies/stage boundaries) confirms exactly where in the LOGICAL pipeline a shuffle was inserted, complementing the Stages tab's RUNTIME view of shuffle cost.

Worked example

df.groupBy("customer_id").agg(F.sum("amount")).explain()

The physical plan shows an Exchange hashpartitioning(customer_id, 200) node between the scan and the final HashAggregate, confirming a shuffle occurs at exactly the groupBy step (the wide dependency), partitioned into 200 partitions (Spark's shuffle-partitions default, unless overridden). Running the equivalent job and inspecting the Stages tab afterward would show a Shuffle Write metric on the map-side (partial-aggregate) stage matching roughly the post-partial-aggregation data volume, and a Shuffle Read metric on the reduce-side (final-aggregate) stage matching that same volume, the two numbers that should roughly agree with each other (what one stage writes as shuffle output, the next stage reads as shuffle input) and, together, are the direct evidence "this stage is shuffle-heavy" rather than an assumption.

Trade-offs and pitfalls

• The 3 concrete costs of a shuffle, stated together as a checklist worth memorizing: (1) network transfer of the shuffled bytes between executors, (2) serialization/deserialization CPU cost on both the write and read side, (3) disk I/O for the shuffle files themselves plus any spill during the reduce-side sort/combine step; all three compound, which is why a shuffle is categorically more expensive than a narrow transformation touching the identical VOLUME of data, not merely "somewhat slower."
• Common mistake: treating "shuffle occurred" (a binary fact, confirmable via the plan's Exchange node alone) and "shuffle is a performance problem for THIS job" (a magnitude question, requiring the Stages tab's actual size/spill metrics) as the same finding; many correct, necessary shuffles are NOT the bottleneck for a given job, and chasing shuffle elimination for its own sake, on a shuffle that is not actually costly relative to the rest of the job, is wasted effort.
• Common mistake: checking only Shuffle Read/Write SIZE and missing the Spill metrics specifically; a stage can have a large but well-provisioned shuffle (fits comfortably in execution memory, no spill) that performs fine, while a SMALLER shuffle that spills heavily (undersized memory relative to that specific stage's needs) can be the actual bottleneck; size alone does not tell the whole story, spill does.
• Confirming a shuffle via explain()'s plan-level Exchange node and confirming its COST via the Stages tab's runtime metrics are complementary, not redundant: the plan tells you WHERE a shuffle exists in the logical pipeline, the runtime metrics tell you HOW MUCH it actually cost for a specific run against specific data.

Direct answer

Both strategies handle two keys hashing to the same bucket, but they store the overflow differently. Separate chaining keeps a small list (or similar container) at each bucket, so a collision just means appending to that bucket's list; insert, lookup, and delete all cost $O(1)$ on average. Open addressing instead keeps every entry directly in the single backing array, and on a collision probes a deterministic sequence of other slots (linear probing tries the next slot each time; quadratic probing tries slots at increasing squared offsets) until it finds an empty one. This keeps memory compact and cache-friendly, but makes delete trickier: removing an entry by simply clearing its slot can break the probe chain for entries that were placed after it.

Structured elaboration

Insert, lookup, delete, side by side

Load factor and resizing

Load factor $\alpha = \dfrac{n}{m}$ (n entries, m slots) governs both strategies' health. Chaining degrades gracefully as $\alpha$ rises past 1, since the expected cost per lookup is $O(1 + \alpha)$: the average list length just grows linearly with $\alpha$. Open addressing degrades sharply as $\alpha$ approaches 1, since probe sequences get long and clusters grow, so implementations typically resize (allocate a bigger table, commonly doubling it, and rehash every entry) once $\alpha$ crosses a fixed threshold, often around 0.7 for open addressing, versus a looser threshold for chaining since it tolerates a higher load factor before performance visibly suffers.

Why quadratic probing exists

Linear probing (always try the next slot) causes primary clustering: once a run of occupied slots forms, it tends to grow, since anything hashing into that run has to probe past all of it. Quadratic probing spreads probe offsets out (offsets $0, 1, 4, 9, \ldots$ from the original hash) to reduce, though not eliminate, that clustering; the trade-off is that not every slot in the table is guaranteed reachable unless the table size and probing constants are chosen carefully.

Worked example

A table of size $m = 8$ with a resize threshold of $\alpha = 0.75$ triggers a resize once $n$ would exceed $0.75 \times 8 = 6$ entries, i.e., on the 7th insert. After doubling, the new table has $m = 16$ slots and the same threshold now allows up to $0.75 \times 16 = 12$ entries before the next resize. This is the same amortized (averaged over a sequence of operations) argument as a doubling dynamic array: the expensive full-table rehash happens rarely enough, relative to the cheap inserts between resizes, that insert stays $O(1)$ amortized even though a single insert that triggers a resize costs $O(n)$.

Trade-offs & pitfalls

• Chaining costs extra memory per entry for list-node overhead, but tolerates a high load factor and makes deletion simple; a poorly-distributed hash function can degrade one bucket to $O(k)$ for that bucket's $k$ entries (worst case $O(n)$ if everything collides), which some standard library implementations guard against by converting a sufficiently long chain into a balanced tree.
• Open addressing has excellent cache locality (everything contiguous in one array) and no per-entry pointer overhead, but needs a lower load factor to avoid probe-length blowup, and its deletions need tombstones, which themselves need periodic cleanup: enough accumulated tombstones can make a lookup scan nearly the whole table before it reaches a truly empty slot.
• The most common wrong turn on open addressing: deleting an entry by clearing its slot outright. That "empty" slot is exactly the signal that stops a probe search, so a lookup for a different key that was displaced past the deleted slot will wrongly conclude that key isn't present, even though it's still sitting further along the probe chain.

Direct answer

I shipped a change to a signup flow that looked correct in every test environment but broke for users on a specific combination of browser and network condition we hadn't covered, and it was a customer, not our monitoring, who found it first, mid-demo, which made the failure both technical and painfully visible. Working out why it failed meant separating the actual technical root cause from the process gap that let it ship at all, and the fix that stuck was the one that closed the process gap, not just the code.

What happened and how I investigated

The change passed our automated tests and looked fine in manual quality testing, but broke for a subset of users because of an interaction between a caching layer and a redirect that only showed up under a specific, uncommon network condition. It surfaced when a prospective customer hit it during a live demo, which told me something important on its own: our alerting wasn't watching for this failure mode at all, so if the customer hadn't hit it live, it could have persisted undetected. Rather than just fixing the immediate bug, I traced two separate things: the technical root cause, the caching and redirect interaction, and the process gap, which was that our test matrix didn't cover that network condition and our monitoring had no signal that would have caught it in production either.

What I said and to whom, while it was still broken

As soon as I confirmed the cause, I told my manager and the account team handling that customer directly, with the specific technical explanation and an honest estimate of the fix timeline, rather than a vague "we're looking into it." That let the account team manage the customer conversation with real information instead of a placeholder.

What changed as a result

The immediate fix addressed the caching and redirect bug. The change that outlived the incident was adding the specific network condition to our test matrix and adding a monitoring alert for that class of redirect failure, so the next similar bug would be caught by our own systems instead of by a customer mid-demo. I also flagged that our sign-off process treated "tests pass" as equivalent to "ready to ship" with no explicit check for untested conditions, which is a narrower and more honest description of what our tests actually covered.

Trade-offs and pitfalls

The pitfall is stopping at the technical fix and treating the incident as resolved, when the more durable failure was the process gap that let something with an untested condition ship in the first place. A failure caught by monitoring and one caught by a customer can share the identical root cause, but they are different signals about how much your detection is actually covering.

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.

Direct answer. Join the raw table to a subquery that computes, per key, the maximum timestamp, matching back on BOTH the key and that maximum timestamp, so only the single latest row per key survives the join.

Structured elaboration. The join-based approach computes the "what's the latest timestamp per key" question as its own aggregate subquery first (a GROUP BY key with MAX(timestamp)), then joins the raw table back to that result on both the key AND the timestamp matching the computed maximum, which naturally keeps only the winning row(s) per key. This produces the identical logical answer to a ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC) approach filtered to rn = 1, just expressed through aggregation and a join instead of a window function.

Worked example. raw_customers(customer_id, email, created_at): (1, 'a@x.com', t1), (2, 'a@x.com', t2 later), (3, 'b@x.com', t3).

-- join-based: aggregate the max timestamp per key, then join back to it
SELECT rc.customer_id, rc.email, rc.created_at
FROM raw_customers rc
JOIN (
  SELECT email, MAX(created_at) AS max_created
  FROM raw_customers
  GROUP BY email
) latest ON rc.email = latest.email AND rc.created_at = latest.max_created;

-- ROW_NUMBER() alternative, for comparison
SELECT customer_id, email, created_at FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
  FROM raw_customers
) t WHERE rn = 1;

Both return the identical result: (2, 'a@x.com', t2), (3, 'b@x.com', t3), correctly keeping the later of the two rows sharing 'a@x.com'.

Trade-offs and pitfalls. The join-based approach has one correctness wrinkle the window-function version doesn't: if two rows for the SAME key happen to share the exact same maximum timestamp (a genuine tie), the join-based version returns BOTH of them, since both match the aggregate's max_created equally, whereas ROW_NUMBER() with a stable ORDER BY tie-break returns exactly one, deterministically. If ties are possible in your data, you'd need to add an explicit secondary tie-breaker (an id column) to the join's matching condition to get single-row-per-key behavior back. Performance-wise, the two approaches often compile to comparably efficient plans on a well-indexed key/timestamp pair, so the choice mostly comes down to whether your engine and team are more comfortable with the aggregate-and-join idiom or the window-function idiom, and whether tie-handling needs to be airtight.

Direct answer. Reducing data-processing compute cost by a large percentage without breaking SLAs works best as a prioritized set of levers applied roughly in order of expected-savings-per-effort, validated incrementally rather than applied all at once, so a cost regression can be attributed to a specific change and rolled back cleanly if it risks the SLA.

Structured elaboration.

1. Candidate levers, roughly ordered by typical impact-to-effort ratio. (a) Right-sizing: many clusters/instances are provisioned generously and rarely revisited -- auditing actual utilization against provisioned capacity often finds low-risk, immediate savings. (b) Partitioning: repartitioning or adding partition pruning so jobs scan only the data they actually need (by date, by key) cuts both compute time and data volume moved -- often a large win when the current layout forces full-table or full-partition scans. (c) Caching: caching a reused expensive intermediate result (a join output, an aggregation) across jobs that currently recompute it repeatedly removes duplicated compute entirely, at the cost of a cache-invalidation policy to keep it correct. (d) Storage lifecycle and format: moving cold data to cheaper tiers and adopting better compression/columnar formats reduces ongoing storage cost with minimal processing-logic risk. (e) Spot instances for tolerant workloads (instances reclaimable with short notice at a steep discount, safe for checkpointed batch work). (f) Job consolidation: multiple small, similar jobs run separately often share fixed per-job overhead that consolidation eliminates. (g) Scheduling: shifting non-time-critical batch work to off-peak windows where spot/reserved pricing is more favorable. (h) Data-transfer cost: at large scale, cross-region or cross-service data movement can be a surprisingly large cost line; auditing and reducing unnecessary transfer often yields savings nobody had been tracking.
2. Quick wins vs. longer-term investment. Right-sizing and storage-tier lifecycle policies are typically quick, low-risk wins (config/policy changes, not code rewrites); job consolidation and format migration are medium-effort; deeper architectural changes (moving compute location, redesigning a pipeline's core processing logic) are longer-term investments reserved for when the quick/medium wins are exhausted and cost pressure remains.
3. Measuring improvements and attribution. Roll out changes incrementally (one lever, or a small batch of independent levers, at a time) and measure BOTH cost and SLA-relevant metrics (latency, completion time, error rate) before and after each change, so a regression can be attributed to the specific change that caused it rather than lost in a bundle of simultaneous changes.
4. Cost attribution to teams/pipelines. Tag resources and cost line items by owning team/pipeline BEFORE starting the optimization effort, so savings (and any regressions) can be measured and communicated at the granularity stakeholders actually care about, and so future cost growth can be caught early by the team closest to the cause.
5. Validating savings safely. For any change carrying correctness or SLA risk (job consolidation, format changes, moving compute location), validate against a comparable data volume in a non-production environment first, and roll out to production with a defined rollback plan and monitoring window before declaring the change permanent.
6. Rollout order. Quick wins first (fastest validated savings, builds momentum and buys margin for riskier changes later), then medium-effort changes, with the highest-risk architectural changes last and only if cost targets are not yet met by the earlier tiers.

Worked example. A team facing pressure to cut cost by 40% without breaking SLAs finds via a resource audit that (a) several clusters are provisioned at roughly 2x their peak observed utilization (right-sizing alone plausibly recovers a meaningful fraction of the target), (b) 60% of data older than 30 days sits on hot storage despite being queried less than once a month (moving it to a cheaper cold storage tier recovers further savings at near-zero risk), and (c) three separate small nightly jobs process overlapping data with duplicated per-job overhead (consolidating them removes that duplication), and (d) two of those same jobs re-run an identical expensive join against a slowly-changing dimension on every invocation, a strong caching candidate once the jobs are consolidated. Applying right-sizing, partition pruning, and storage tiering first (lowest risk, fast to validate) captures a large share of the 40% target within the first rollout wave; job consolidation, validated carefully against output correctness given its higher implementation risk, closes most of the remaining gap; if a residual gap remains after these, spot-instance adoption for the most preemption-tolerant remaining workloads is the next lever, reserving any deeper architectural change as a longer-term follow-up rather than a first-wave requirement.

Trade-offs & pitfalls. The temptation under cost pressure is to apply the most aggressive lever (often spot instances or an architectural rewrite) first because it sounds impactful, when right-sizing and storage-lifecycle changes are frequently both cheaper to implement AND lower-risk -- sequence by risk-adjusted impact, not by how dramatic a change sounds. For an ML-specific variant of this problem (cutting cost for a training pipeline), REPRODUCIBILITY is an added constraint the general levers above do not automatically respect: sampling or aggressive caching applied to training data must be validated not to silently change model outputs run-to-run, which is a correctness risk beyond the usual SLA/latency risk this framework otherwise covers.

Direct answer

All three service models sit on one spectrum: how much of the technology stack the provider manages for you, versus how much you still manage yourself. Infrastructure as a Service (IaaS) hands you raw compute, storage and networking and you own everything from the OS up. Platform as a Service (PaaS) additionally takes over the OS, runtime and middleware (the software layer that sits between the OS and your application, such as message queues, application servers, or authentication/session handling), so you just deploy code. Software as a Service (SaaS) is a finished application: you own only your data and how you configure and use it.

The three models, one at a time

IaaS. The provider manages the physical data center, the network hardware, and the virtualization layer (the hypervisor that carves one physical server into many virtual machines). You manage the OS, runtime, middleware, application code, and data. Example: Amazon Web Services (AWS) Elastic Compute Cloud (EC2), which rents you a virtual machine and stops there. IaaS is the right default when a workload needs a specific kernel version, custom drivers, or full control of the network stack, for example lifting an existing application that depends on a particular OS build.

PaaS. The provider additionally owns the OS, runtime and middleware. You push application code (or a container image) and the platform patches, scales and load-balances it for you. Example: Google App Engine on Google Cloud Platform (GCP). PaaS fits a small team that wants to ship a web application without hiring dedicated operations staff.

SaaS. The provider owns everything, including the application itself. You own only your data and its configuration: which users exist, what they can see, how records are structured. Example: Salesforce. SaaS is the right choice for a standard business capability, such as a customer relationship management (CRM) system, where building your own would create no advantage.

Data never fully moves to the provider's column. You always decide who is allowed to see it and whether it is used correctly, even when the provider secures every layer beneath it.

Worked example: classifying five real services

• AWS EC2 = IaaS. It hands you a virtual machine. You choose and patch the OS, install a runtime, and own everything above the hypervisor.
• GCP App Engine = PaaS. You deploy application code; the platform owns the OS, the language runtime, and automatic scaling. You never log into a server.
• Azure SQL Database = PaaS, not IaaS, even though "it's a database" makes it sound like infrastructure. Microsoft manages the OS and the database engine's patching and high availability; you manage schema, queries and data. Contrast this with running SQL Server on an Azure virtual machine, which would be IaaS, because you would own the OS and the engine installation yourself.
• Salesforce = SaaS. A finished CRM application. You configure objects, fields and workflows and own your customer records, but Salesforce owns the application code, runtime and infrastructure underneath.
• Google Workspace = SaaS. Gmail, Docs and Drive are ready-to-use applications. Your real responsibility is your data and access configuration: who is in your organization and what they can share externally.

The same ladder shows up on an ML (machine learning) team's bill. A GPU-backed virtual machine running a custom training loop is IaaS: you install the framework, the drivers, and manage the OS yourself. A managed training job that takes your training script and returns a model artifact sits in the PaaS band: the provider owns the cluster orchestration and worker scaling (coordinating many machines to run the job together, and adding or removing machines as the workload changes), you own the training code, data and the resulting model. A managed model-serving endpoint is the same band: you supply the model, the platform owns the runtime and autoscaling of the inference containers (the running, ready-to-answer copies of your packaged model that handle prediction requests).

Trade-offs and pitfalls

The most common misclassification is calling a managed database IaaS "because it holds data like a database server would." The test is not what the service does, it's which layers you still touch: if you never patch an OS or install the database engine, it is PaaS regardless of the product name. A second pitfall is assuming SaaS means zero responsibility; you are always responsible for who has access and how it is configured, and a breach caused by an over-permissioned admin account is still your incident even though the vendor wrote no bad code. Finally, don't treat the three models as a single company-wide choice: most real organizations run all three at once (EC2 for a legacy system, App Engine for a new service, Workspace for email), and the interesting decision is made per workload, not once.