8 BigQuery Interview Questions with a Worked Event-Deduplication Query

TL;DR

• Start a BigQuery interview answer with the data grain, required result and workload. A faster query that counts the wrong orders is still wrong.
• Partitioning can exclude whole partitions; clustering can exclude storage blocks. Choose both from actual filters, and verify the query plan instead of promising a fixed speedup.
• Deduplicate order updates before filtering their final status. Use a documented tie-breaker, and keep business dates separate from ingestion times.
• Test the result on a small adversarial dataset, then validate it in BigQuery. Dry runs and billing limits help control execution cost; they do not prove business correctness.

The scenario behind these BigQuery interview questions

Suppose a retailer loads order updates into order_events. The finance team wants September revenue and completed-order counts by customer. Retries create duplicate records, cancellations arrive after completions, and some September orders are received in October.

Before writing SQL, establish the contract. In this exercise, event_id identifies one business order globally, and event_date is its immutable business date. Every update contains the complete current state. The source contract says a later received_at wins; a larger unique ingestion_id breaks equal-time ties. The fields are validated and non-null. Amounts are integer cents, and all orders use the same currency.

These are deliberately explicit assumptions. A real event stream may provide an authoritative source version that is safer than arrival time. If events can arrive out of order, simply choosing the last received record can select stale business data. If an order can move to another business date, filtering one month before deduplication also needs reconsideration.

The following eight questions build on this fictional dataset. They are practice questions, not a claimed list from a particular employer's interview.

1. How would you choose partitioning and clustering?

For a workload that repeatedly reports by business month, partitioning on event_date is a reasonable candidate. If analysts commonly restrict reports to particular customers, clustering by customer_id may be worth evaluating. If most queries aggregate every customer for a month, that clustering choice may provide less benefit.

Partition pruning depends on a qualifying filter on the partitioning column. Clustering organizes storage blocks so suitable filters can skip irrelevant blocks; the order of clustering columns affects that opportunity. Neither mechanism guarantees the order of returned rows. Use an explicit ORDER BY when the result needs one. See Google's partitioned-table query guidance and clustering documentation.

An interview follow-up might change the workload: operations now searches across months by order ID. Explain that this is a different access pattern. Measure representative queries and consider an appropriate serving design instead of assuming one table layout optimizes every question.

2. Does SELECT * with LIMIT 10 make an exploration query cheap?

No. For a normal table query, reducing the rows returned with LIMIT does not by itself reduce the bytes read by SELECT *. Select only the columns needed and apply appropriate partition filters. Google's compute optimization guide explains this distinction.

In our exercise, the revenue query needs the order identity, customer, amount, status and fields used for ordering updates. It does not need a large free-text support note or an attached document payload. Leaving those fields out is a concrete improvement that preserves the requested result.

Be precise about what you are measuring. Rows displayed, bytes processed, elapsed time and resource use answer different questions. A small result can require substantial work; a larger result can come from a well-pruned scan. Do not treat the number shown in the results grid as a cost estimate.

3. How would you deduplicate updates and calculate revenue?

Use a window function to choose the winning update for each order, then retain completed orders and aggregate them. Here is the complete relational query for the stated contract:

WITH ranked AS (
  SELECT event_id, customer_id, amount_cents, status,
    ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY received_at DESC, ingestion_id DESC
    ) AS rn
  FROM order_events
  WHERE event_date >= '2026-09-01'
    AND event_date < '2026-10-01'
)
SELECT customer_id,
  SUM(amount_cents) AS revenue_cents,
  COUNT(*) AS order_count
FROM ranked
WHERE rn = 1 AND status = 'completed'
GROUP BY customer_id
ORDER BY customer_id;

In the BigQuery table, event_date is a DATE, received_at is a TIMESTAMP, and ingestion_id and amount_cents are INT64. The remaining selected fields are strings. The date boundaries describe a half-open interval: include September 1 and exclude October 1.

The unique tie-breaker matters. Google's ROW_NUMBER reference states that ordering within a peer group is nondeterministic. An ordering rule that cannot distinguish two conflicting updates needs a business decision before it can support reproducible reporting. If an interviewer asks how RANK or DENSE_RANK would treat such tied updates, our guide to advanced SQL interview questions compares all three ranking functions.

Notice where the status filter sits. If a completed order is later cancelled, filtering to completed records inside the first query would discard the cancellation and resurrect the old completion. Deduplication must see both versions before deciding whether the winning state contributes revenue.

4. Which test records would expose mistakes?

A useful fixture includes failures that a clean happy-path dataset cannot reveal. Use the following fictional orders; customer Alice owns A through D, and Bob owns E through G.

The expected output is Alice with 18,500 cents and three orders, and Bob with 7,000 cents and one order. C and D demonstrate why SUM(DISTINCT amount_cents) is not a deduplication strategy: two real orders can have the same price.

Also test an empty month, an all-cancelled month, reversed insertion order and multiple shuffled insertion orders. If changing insertion order changes the answer, investigate the tie-breaking rule. Test the interval boundaries independently rather than relying on the full fixture alone.

For this guide, the displayed SQL is executed unchanged against a local SQLite fixture containing ISO date and timestamp strings. That checks the relational result and the stated counterexamples. It does not test BigQuery's execution engine, native type coercion, partition pruning or billing. A production implementation still needs a BigQuery integration run against its actual schema and representative data.

5. How would you handle late arrivals and corrections?

Order G belongs in September's business report even though the pipeline receives it in October. A pipeline that reads only records received during September would miss it. Decide whether the report is provisional, when it becomes final and how later corrections are represented.

For this exercise, a provisional monthly report can be recomputed from the affected business-date partitions after a correction. A larger system might maintain a current-state table and update affected aggregates. Either approach needs an idempotent update rule and an explicit policy for corrections outside the routine processing window.

Do not silently switch event_date to ingestion date to make a query easier to prune. That changes the question being answered. Likewise, the early month filter in our SQL relies on the immutable business-date assumption. If a correction moves an order from September to October, the system must remove its old contribution as well as insert its new one.

Explain how you would test this: produce a September report, inject a late correction, rerun the relevant processing, and verify that the revised total changes exactly once. Then replay the same correction and verify that it does not count twice.

6. What can go wrong after joining another table or using UNNEST?

Ask what one row represents before and after every operation. If one order joins to three support tickets, its revenue can appear three times. If an order contains an array of purchased items, expanding that array changes the grain from an order to an item.

For a fictional customer-status lookup, require one applicable status row per customer, or explicitly choose the version valid at the reporting time. Joining to an unrestricted history table is not equivalent to joining to a current customer table.

Build a tiny counterexample: one 10,000-cent order and two matching lookup rows. If the post-join sum becomes 20,000 cents, the problem is a relationship or grain error. Adding DISTINCT to the final query may conceal it while breaking other cases. State whether the requested metric is orders, line items, customers or update events before choosing the aggregation. Our SQL interview question bank includes a similar case, where joining orders to both line items and payments multiplies the row count.

7. How would you investigate a slow query?

First confirm that you are comparing the same result, parameters and relevant cache conditions. Review the execution plan for stages that expand data unexpectedly, expensive joins and work that can be reduced earlier. Google recommends using query-plan information and reducing data before joins where appropriate in its performance guidance.

For this scenario, compare the rows entering the order-to-customer join with the rows leaving it. An unexpectedly large expansion may point to the duplicate lookup problem above. Separately check whether the date filter qualifies for partition pruning. Supported functions can participate in pruning in some cases; the rule is not that every function around a date automatically prevents it.

Change one thing at a time and retain a correctness check. Record the query text, dataset range and observed metrics. Without those details, a claim that a rewrite was several times faster is difficult to evaluate or reproduce.

8. Which cost controls would you use before running a large query?

Use a dry run or query validator to inspect estimated processing before execution. For on-demand queries, a maximum-bytes-billed setting can reject a query whose estimate exceeds the configured limit. Clustering can make pre-execution estimates less precise because the eventual block pruning is determined during execution. These details are documented in Google's cost guidance and clustering guide.

A dry run does not prove that September revenue is correct. It also does not turn an on-demand cost estimate into a prediction for every capacity-based arrangement. Identify the project's billing model and the team's resource controls before describing the expected bill. The warehousing section of our data engineer interview guide has an example answer that covers both: reservations for core pipelines and on-demand for ad hoc queries, with slot quotas per team.

For an interview exercise, propose a sequence: validate the SQL, inspect the estimate, run a bounded fixture, verify the actual result, then evaluate a representative workload with agreed limits. That sequence gives the interviewer evidence for both correctness and operational judgment.

How to practise your explanation

Give yourself five minutes to state the data contract, sketch the deduplication query and predict the fixture output. Then ask a partner to change one assumption: order IDs are only unique within a store, cancellations arrive a week late, or the amount can be in different currencies.

Revise the design before editing the SQL. A store-scoped identity needs a composite key; multiple currencies need separate totals or an explicit conversion policy. These follow-ups test whether you understand the query rather than merely recognize its syntax.

Use PhantomCodeAI as part of your interview preparation, alongside direct practice explaining the assumptions, counterexamples and verification steps in your own words.