Google Data Engineer (Entry Level) Interview Preparation Guide

This is the load-time mechanics question, not the "what are SCD (Slowly Changing Dimension) types" theory question: given a staging table of the latest snapshot rows for a customer dimension, implement the MERGE (or MERGE-equivalent statement sequence) that correctly expires changed rows and inserts new current versions.

The implementation (as two statements, since a single MERGE can't both expire an old row and insert a new one for the same logical entity in one pass):

-- Step 1: expire current rows whose tracked attributes changed
UPDATE dim_customer
SET effective_to = '2026-01-05', is_current = false
WHERE is_current = true
  AND customer_id IN (
    SELECT d.customer_id
    FROM dim_customer d
    JOIN stg_customers s ON s.customer_id = d.customer_id
    WHERE d.is_current = true AND s.name != d.name
  );

-- Step 2: insert new current rows, for both changed customers and brand-new ones
INSERT INTO dim_customer (cust_key, customer_id, name, effective_from, effective_to, is_current)
SELECT nextval('cust_key_seq'), s.customer_id, s.name, '2026-01-05', '9999-12-31', true
FROM stg_customers s
LEFT JOIN dim_customer d ON d.customer_id = s.customer_id AND d.is_current = true
WHERE d.customer_id IS NULL;

Verified against a fixture with an unchanged customer, a customer whose tracked attribute changed, and a brand-new customer:

before: C1=Alice (current), C2=Bob (current)
staging: C1=Alice Smith (changed), C2=Bob (unchanged), C3=Carol (new)

after both steps:
  cust_key | customer_id | name        | effective_from | effective_to | is_current
  1        | C1          | Alice       | 2025-01-01     | 2026-01-05    | False
  3        | C1          | Alice Smith | 2026-01-05     | 9999-12-31    | True
  2        | C2          | Bob         | 2025-01-01     | 9999-12-31    | True   <- untouched
  4        | C3          | Carol       | 2026-01-05     | 9999-12-31    | True   <- new

invariant check (exactly one is_current=true row per customer_id): holds, 0 violations

re-running the same two statements again: identical result (confirmed idempotent, since step 1's
WHERE clause only matches rows that are STILL current and STILL differ, and step 2's WHERE only
matches customers with NO current row, both of which are already false after the first run)

What makes the merge atomic: the two statements above are not atomic against each other by default, a reader querying between step 1 committing and step 2 committing would briefly see zero current rows for a changed customer. Wrapping both statements in a single database transaction (BEGIN ... COMMIT) makes the pair atomic from any reader's perspective, so a concurrent query never observes the intermediate expired-but-not-yet-reinserted state, and a crash between the two statements rolls back to the pre-update state instead of leaving the dimension with a gap.

Why this can't be one MERGE statement: a MERGE clause matches one target row per source row and applies exactly one of INSERT/UPDATE/DELETE to it. SCD Type 2's core operation, "close out the old row AND open a new one for the same logical key," touches two rows (one existing, one new) for a single source row, which is structurally outside what one MERGE pass can express. This is why real SCD2 implementations are either the two-statement-in-a-transaction pattern shown here, or a changelog/append-only table with a view that computes "current" on read (avoiding the UPDATE entirely, at the cost of a more expensive read-time query).

Direct answer

An anomaly-detection approach for a business metric across many segments (stores, regions) should combine simple, explainable statistical rules (a seasonally-adjusted rolling z-score, or STL seasonal decomposition to separate trend/seasonal/residual components) as the default detector, reserving a lightweight ML model only for cases the simple rules demonstrably cannot handle well, and must explicitly account for day-of-week and holiday seasonality, since raw statistical rules applied to naturally cyclical data generate constant false alarms otherwise.

Structured elaboration

• Seasonality-aware baselines: compare a segment's current value against its own historical distribution for the same day-of-week (or, for holiday-sensitive businesses, the same day-relative-to-holiday), not a flat trailing average, since a Monday is not comparable to a Saturday for most consumer businesses.
• Reducing false positives on noisy, low-volume segments: a small store with naturally high day-to-day percentage variance (because its absolute volume is low) will trip a fixed percentage-based threshold constantly if every store uses the same threshold; scale the sensitivity per segment based on its own historical variance, or apply a minimum-absolute-change floor alongside the percentage threshold so a tiny store's noise does not generate the same alert volume as a large store's genuine anomaly.
• Prioritizing and routing alerts: rank alerts by a combination of statistical significance and business impact (an anomaly in a high-revenue segment matters more than the same statistical anomaly in a negligible one), and route by that priority so an on-call analyst sees the highest-impact anomalies first rather than a flat, unranked list.

Worked example

Daily revenue across 100,000 stores: a naive fixed threshold of "flag anything more than 2 standard deviations from the trailing 7-day average" fires constantly on Sundays for stores that are naturally quieter that day, training the on-call rotation to ignore Sunday alerts entirely. Replacing the baseline with a same-day-of-week rolling average (comparing this Sunday against the last several Sundays, not the last 7 days flatly) removes the bulk of that noise, and adding a per-store variance-scaled threshold (rather than one global threshold) prevents small, naturally-noisy stores from dominating the alert volume relative to large, stable ones where the same percentage move is actually far more unusual.

Trade-offs and pitfalls

The design decision most teams get wrong is applying one global threshold across segments with very different natural variance, which simultaneously over-alerts on noisy small segments and under-alerts on large, stable ones where even a modest percentage move is genuinely significant. A second common failure is detecting "the metric is broken" (a join bug, a missing partition) using the same anomaly detector built for organic business anomalies; the detector should explicitly also watch for signatures specific to pipeline failure (a sudden hard drop to exactly zero, a partition that never landed at all) rather than relying purely on statistical distance from a historical baseline to catch those cases.

Direct answer. Pre-aggregate the "many" side down to one row per key BEFORE joining it to the fact table, or deduplicate it down to one canonical row per key using an explicit tie-breaking rule; either one fixes the duplication at its source, whereas slapping DISTINCT on the final result only hides symptoms and can silently drop legitimately different rows.

Structured elaboration. The two real fixes and the one fake fix:

• Pre-aggregate before joining: if you need SUM(orders.total) per customer and orders is being joined through a one-to-many table like order_items, compute the per-order (or per-customer) aggregate in a subquery or CTE first, and join the already-aggregated result. The join can no longer multiply anything because the thing you're joining is already at the right grain.
• Deduplicate the many side with a canonical-row rule: when what you actually want is ONE representative row per key (the latest address, the primary email), pick a specific tie-breaking rule (most recent by timestamp, a boolean is_primary flag, or ROW_NUMBER() partitioned by the key) and join against only that one row per key.
• DISTINCT on the final result (the band-aid): this looks like it fixes the symptom because the visible duplicate ROWS disappear, but it operates on the wrong thing. If two DIFFERENT orders happen to produce an identical row on the columns you selected, DISTINCT silently merges them too, and you've now lost real data rather than fixed a join. It also does nothing to fix an aggregate computed from the pre-DISTINCT, already-inflated rows.

Worked example. customers(1,'Alice'). orders(100, customer_id=1, total=50), (101, customer_id=1, total=30). order_items has two rows for order 100 and one for order 101 (three item rows total).

-- naive (inflated): joining orders to order_items before aggregating double-counts order 100
SELECT c.customer_id, SUM(o.total) AS inflated_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id;
-- returns (1, 130.00): order 100's 50 got summed twice (once per item)

-- fixed: aggregate orders on their own; never join order_items for this metric at all
SELECT c.customer_id, SUM(o.total) AS correct_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;
-- returns (1, 80.00), the correct sum of order totals (50 + 30)

The fix here is really "don't join the many-side table at all if this metric doesn't need it," which is the pre-aggregation principle taken to its logical conclusion: join only what the specific metric actually requires.

Trade-offs and pitfalls. Pre-aggregation is the more scalable fix (the join now operates on a table that's already the right size) but requires you to correctly identify every place a metric is computed and make sure the aggregation grain matches; get the grain wrong and you've traded a visible bug for a subtle one. The canonical-row approach needs an explicit, deterministic tie-breaking rule, an ambiguous one (pick "any" row when several are equally recent) produces answers that vary between runs. Treat any DISTINCT you find sitting after a multi-table join as a signal to go find out WHY duplicates existed in the first place, not as the fix itself.

Direct answer

Build the recurring SLA (service-level agreement) report with one row per reporting period and pipeline, computing success rate, average latency, and volume directly from the run-level data, then use LAG to attach the prior period's success rate so the week-over-week change is a single window-function computation rather than a self-join. For a related timing metric, report the DISTRIBUTION (median, broken out by a dimension like hour of day), not just an average, since an average can hide a bimodal pattern an operator actually needs to see.

Structured elaboration

• The core report: aggregate run-level rows (status, latency_ms, records_processed) to one row per (pipeline_name, period), computing success_rate = AVG(CASE WHEN status='success' THEN 1.0 ELSE 0 END), avg_latency_ms = AVG(latency_ms), and total_records = SUM(records_processed).
• Week-over-week change: LAG(success_rate) OVER (PARTITION BY pipeline_name ORDER BY period) gives the prior period's rate in the same row, so the change is simply success_rate - LAG(success_rate) OVER (...), no self-join required.
• Why a distribution beats a single average for a timing metric: an average time-to-accept can look perfectly healthy while masking a real problem, for example a metric that's fast during business hours and slow overnight; reporting a median (robust to a handful of extreme outliers, unlike a mean) broken out by hour of day surfaces that pattern where a single company-wide average would not.
• Excluding cancelled/invalid records from a timing distribution (as opposed to the success-rate report, which legitimately needs to count failures) avoids polluting the "how long did this normally take" question with cases that never completed the lifecycle being measured.

Worked example

SELECT pipeline_name, week_start, success_rate,
  LAG(success_rate) OVER (PARTITION BY pipeline_name ORDER BY week_start) AS prev_success_rate,
  ROUND(success_rate - LAG(success_rate) OVER (PARTITION BY pipeline_name ORDER BY week_start), 3) AS wow_change_success
FROM pipeline_runs
ORDER BY week_start;

For pipeline p1 at 95% success one week and 80% the next, this returns prev_success_rate = 0.95, wow_change_success = -0.15 on the second row, a single number an on-call operator can alert on directly. For the companion timing-distribution report, computing the median via PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY minutes_to_accept) grouped by EXTRACT(hour FROM created_at) (excluding cancelled orders) would reveal, for example, a median of 3 minutes during the day and 22 minutes overnight, a gap a single blended average across all hours would completely hide.

Trade-offs & pitfalls

• The first week of a new pipeline has no prior period to compare against: LAG correctly returns NULL there, and the report should render that as "no comparison available" rather than a misleading 0% or blank change.
• A success-rate percentage alone doesn't convey volume: a rate dropping from 95% to 80% on 5 runs is a very different signal than the same drop on 5,000 runs; always show volume alongside the rate, not instead of it.
• Averages and medians answer different questions and neither replaces the other: a median is robust to outliers but hides how bad the worst cases are; pairing the median with a high percentile (p95 or p99) gives a fuller picture than either alone.

Direct answer

Never do the rename and split as a single in-place change. Add the new columns alongside the old ones, backfill history into them, let consumers migrate at their own pace against a stable interface that hides the transition, and only remove the old column once every consumer has verifiably stopped reading it.

Structured elaboration

The shape is expand, migrate, contract:

• Expand: add the new columns as nullable, and dual-write them going forward so old and new stay in sync on every new row.
• Migrate: backfill historical rows into the new columns; put a stable view or interface in front of the table that maps the old contract onto the new physical layout, so most consumers do not have to change anything on day one; move consumers to read the new columns directly, in waves, verifying each wave before starting the next.
• Contract: once no consumer reads the old column, confirmed rather than assumed, drop it.

Pace is set by the slowest consumer you do not control, not the fastest one you do. Internal teams you can coordinate with directly can migrate quickly; an external partner or an infrequently-run batch job sets the floor for how long the old column has to stay alive.

Verification at each wave means comparing values produced by the new columns against the old column, on both historical and freshly written rows, since a rename/split should reconcile exactly with the original, it is not a new computation.

Rollback is cheap under this design: because the old column keeps being written throughout the migration, rolling back is just repointing the view at the old column again, not restoring data.

Multi-producer, multi-consumer variant: the same rename/split done across a message topic with several independent producers and consumer groups, rather than one owned table, follows the same expand-migrate-contract shape, but the "stable view" becomes schema-registry-managed compatibility: register the new schema as backward and forward compatible, have producers add the new fields while still emitting the old one, and let each consumer group upgrade on its own deployment schedule, since nothing forces a consumer group to redeploy on your timeline the way a table's view can. The real difference is that there is no single "migration is done" date; with N producers and M independently-deploying consumer groups, you are tracking a compatibility matrix, not one cutover.

Worked example

A contact column, a freeform string like "John Doe, john@example.com", is being split into structured contact_name and contact_email columns, read by six downstream consumers. Suppose three of the six can migrate within the same sprint and three need a full quarter because of an external contract dependency. The old column's retention has to be planned against the slowest consumer, at least one quarter, not one sprint, otherwise it gets dropped while a consumer still depends on it. The backfill job that parses history into the two new columns only has to run once, so its cost is proportional to the table's total historical row count paid a single time; the dual-write cost, by contrast, is paid on every new row for as long as both columns coexist, meaning the total cost of the migration scales with how long the slowest consumer takes, not with the size of the backfill itself.

flowchart LR
  producer["producer (dual write old + new)"] --> table["table: old_col + new_cols"]
  backfill["one-time backfill job"] --> table
  table --> view["stable view (old contract)"]
  view --> consumerA["consumer A"]
  view --> consumerB["consumer B"]
  view --> consumerC["consumer C"]

Trade-offs & pitfalls

Skipping the transitional view forces every consumer onto the producer's own schedule instead of their own, which is exactly the coordination cost this pattern exists to avoid. Declaring a migration wave "done" by deploy date instead of by verifying zero reads against the old column risks dropping it while an infrequently-run job, a monthly batch report, say, still depends on it. In the multi-producer case, shipping a producer-side schema change without registry-enforced compatibility checks can break every consumer group at once, the opposite of the incremental, low-risk goal this whole pattern is meant to deliver.

Direct answer. Treat this as a correctness bug first and a performance question second: reproduce the discrepancy with a small, hand-checkable slice of data, isolate whether NULLs in the join or grouping column are the cause, and only then decide on a fix, since the fix for a correctness bug (get the right answer) is different from a performance fix (get the same answer faster).

Structured elaboration. NULL has three-valued logic in SQL: comparisons against NULL evaluate to UNKNOWN rather than true or false, which silently drops rows from equality-based joins and, notoriously, can make a whole NOT IN predicate evaluate to nothing at all if the subquery's result set contains even one NULL. To diagnose, reproduce the discrepancy on a small, deliberately-constructed sample where you can hand-count the correct answer, then narrow down which specific column and which specific operation (a join condition, a NOT IN, an aggregate that's supposed to include a NULL group) is where the count diverges from what you expect.

Once confirmed, the fix is a data-modeling and query-writing decision, not primarily a performance one: decide explicitly what SHOULD happen to NULLs in that join or filter (should an order with no assigned category be included or excluded from a report? should a NOT IN become a NOT EXISTS, which handles NULLs correctly?) and make the query say that explicitly rather than relying on default three-valued-logic behavior that happens to look right on data without NULLs and silently breaks the moment a NULL appears.

Worked example. A "customers without a completed order" report written as customer_id NOT IN (SELECT customer_id FROM orders WHERE status='completed') will silently return ZERO customers, not the correct list, the moment even one row in orders has a NULL customer_id (an orphaned or bad-data row), because SQL's three-valued logic makes the entire NOT IN evaluate to UNKNOWN once a NULL is anywhere in that subquery's result. Rewriting as NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status='completed') is unaffected by that same NULL, since EXISTS/NOT EXISTS never has to evaluate a NULL-vs-value comparison the same problematic way.

Trade-offs and pitfalls. Once you've found and fixed one instance of this pattern, treat it as evidence there may be siblings elsewhere in the same codebase, particularly any other use of NOT IN against a column that isn't guaranteed NOT NULL, since this exact defect class tends to recur wherever that same risky pattern was copied or independently reinvented.

Complexity

The fix itself doesn't change the query's complexity class; it changes correctness, which is the more urgent property to restore first.

Edge cases

Any aggregate (COUNT, SUM, AVG) silently ignores NULL values within the aggregated column by default, which is usually correct but worth double-checking explicitly whenever a report's totals look suspiciously low; that's a related but distinct NULL pitfall from the join/membership issue above.

Direct answer. A serverless query service (Athena, BigQuery on-demand) charges per byte scanned with no infrastructure to manage, which fits unpredictable, bursty workloads well; a provisioned warehouse (Redshift, a dedicated Synapse pool) reserves compute you pay for continuously, which fits steady, high-volume workloads better. For a startup with an unpredictable query pattern and a limited budget, the serverless model is usually the safer starting point.

Structured elaboration.

• Cost predictability. Serverless bills scale with usage, so a quiet month costs almost nothing, but an unexpectedly large or inefficient query can produce a cost spike with little warning. Provisioned capacity costs the same every month regardless of usage, which is predictable but wasteful if usage is low or spiky.
• Performance on large joins. A provisioned warehouse can be tuned (partitioning, sort/distribution keys, dedicated compute) to make large joins consistently fast. A serverless engine reading raw files typically re-scans the full dataset for every large join unless the data is well-partitioned, so performance is more variable and depends heavily on how the underlying files are laid out.
• Concurrency. Serverless engines generally scale to many simultaneous queries without you doing anything, since there is no shared cluster to contend for. A provisioned warehouse has a fixed pool of compute, so concurrent heavy queries can queue behind each other unless you have configured workload management.
• Operational burden. Serverless requires no cluster sizing, patching, or pause/resume decisions. A provisioned warehouse requires someone to right-size the cluster, monitor utilization, and decide when to scale up or down.

Worked example. A startup with three analysts running a handful of exploratory queries a day against a dataset that grows unpredictably should start serverless: at low query volume, the pay-per-byte-scanned cost is a fraction of what even the smallest provisioned cluster would cost sitting idle most of the day, and there is no capacity-planning burden for a two-person data team to carry. If that same startup grows to have dozens of analysts running the same set of dashboard queries hundreds of times a day against a stable, well-understood dataset, the calculus flips: a provisioned warehouse, with its data laid out and indexed specifically for those repeated queries, becomes cheaper per query and gives more predictable dashboard latency than continuing to pay per byte scanned on every refresh.

Trade-offs and pitfalls. The most common mistake is staying on the serverless model well past the point where usage has become steady and repetitive, since at high, predictable volume, provisioned capacity is almost always cheaper. The opposite mistake is over-provisioning a warehouse for a startup's earliest, lightest workload, which locks in cost the team does not yet need. Revisit the decision as usage grows rather than treating the initial choice as permanent; many teams end up running both, serverless for exploration and new datasets, provisioned for the small set of queries that run on a predictable, heavy schedule.

Direct answer

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

Structured elaboration

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

Worked example

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

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

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

Complexity

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

Trade-offs & pitfalls

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

Situation: You need to join a 5 TB dimension table and a 200 GB fact table on a sharded cluster but network egress is tightly constrained. Below I evaluate each strategy and give a recommended plan.

1. Broadcast the smaller table

• Pros: No shuffle for the larger table; very fast joins on each shard.
• Cons: Broadcasting 200 GB to every shard is expensive and likely infeasible under tight network budgets; memory pressure on workers.

2. Repartition both sides on the join key (hash-shuffle)

• Pros: Correct, scales, no replication.
• Cons: Requires full cluster shuffle of both tables (moves ~5.2 TB across network), expensive; heavy disk/io.

3. Pre-join / denormalize

• Pros: Eliminates runtime joins; best long-term for repeated queries.
• Cons: ETL cost upfront and storage duplication; needs maintenance for updates.

4. Use external distributed engines (Spark)

• Pros: Flexible: can control join strategy (sort-merge, broadcast) and use tunables.
• Cons: Still subject to network shuffle costs; doesn't eliminate cross-shard traffic unless combined with other techniques.

5. Bloom filters (semi-join)

• Pros: Very network-efficient: build a Bloom filter from the 200 GB fact table (small — a few MBs depending on false-positive rate) and send it to dimension shards to filter rows before shuffle. Reduces data moved dramatically if fact keys are selective.
• Cons: False positives cause some extra rows; not helpful if most dimension rows match the fact table.

Recommendation (tight network budget)

• First, if workload is repetitive, denormalize important fields into the fact table (or a pre-joined materialized table).
• Otherwise, use a two-step Bloom-filter/semi-join approach in Spark or your DB:
  1. On facts (200 GB), compute distinct join keys and build a tuned Bloom filter (e.g., 1–2% false-positive).
  2. Distribute that small filter to dimension shards to pre-filter the 5 TB; now only matching dimension rows are shuffled.
  3. Repartition the remaining rows on the join key and complete the join.
• This minimizes cross-shard traffic while keeping correctness; fallback to full repartition only if Bloom filtering yields insufficient reduction.

Example numbers: if Bloom filter reduces the 5 TB dimension to 200 GB of matching rows, you avoid moving ~4.8 TB. Use monitoring to measure selectivity and adapt (increase filter size or denormalize if selectivity low).

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

Structured elaboration

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

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

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