Google data interview pack

The Google loop

1. BigQuery

   Why DATE(ts) = ... scans the table. Clustering vs partitioning. maximum_bytes_billed.

2. SQL

   Windows and anti-joins. They still open a pad even on a cloud team.

3. Design

   Slots vs on-demand, labels for FinOps, and an authorized view that does not copy PII.

SQL (4)

1. hard · SQL

   NULL = NULL is unknown. How does that bite a MERGE or an anti-join?

   NOT IN with a NULL in the list returns empty. Anti-joins should be NOT EXISTS or a LEFT JOIN … WHERE right.key IS NULL. MERGE match conditions that compare nullable keys silently miss rows. Say this out loud; it is a common take-home trap.

2. medium · SQL

   QUALIFY vs a CTE for window filters — when do you use each?

   QUALIFY is cleaner in Snowflake/BigQuery when the dialect has it. A CTE is portable and easier to debug. Either is fine; filtering rn in WHERE on the same SELECT is not.

3. medium · SQL

   A query is correct but scans last year. How do you prove partition pruning?

   Read the plan / bytes scanned / partitions assigned. Add the partition column to the filter (or clustering). EXPLAIN or the warehouse profile — not a guess.

4. hard · SQL

   EXPLAIN says Hash Join. When do you want Nested Loop instead?

   When one side is tiny and indexed (point lookups), nested loop + index can beat building a hash. Warehouse SQL usually hashes; OLTP Postgres often nested-loops. Say “probe side size,” not “hash is always faster.”

Coding (2)

1. medium · Spark

   Broadcast join vs shuffle join. What is the failure mode of a hint?

   Broadcast sends the small side to every executor. If it is not actually small, you OOM workers. AQE can convert automatically. A forced broadcast hint on a growing dimension is a time bomb. Measure the small side after filters.

2. easy · Spark

   UDF vs Spark SQL expr for a string normalize. Which do you merge?

   expr / built-in functions. Python UDFs kill Catalyst and Photon. If you must UDF, pandas/arrow vectorized, and measure. “It was faster on my laptop” is not a plan.

Orchestration (2)

1. easy · Airflow

   TaskFlow vs operators. When do you still write a classic Operator?

   TaskFlow is fine for Python that stays in the worker. Use a dedicated Operator (SnowflakeOperator, DatabricksSubmit) when the work should run in the warehouse, not on the Airflow box. The tell: if the function downloads a table into pandas, you already lost.

2. hard · Airflow

   setup/teardown vs a hope that the cluster terminates.

   Airflow 2.7+ teardown runs even when the work fails. Use it for ephemeral clusters and temp schemas. Hope is not a task.

Streaming (1)

1. medium · Apache Flink

   Flink or Spark Structured Streaming?

   Flink for low-latency keyed state and true event-time. Spark for micro-batch ETL that happens to read Kafka. If the SLA is warehouse minutes, you may not need either — Dynamic Tables / DLT might be enough.

Warehouse (17)

1. medium · BigQuery

   A query scanned 4 TB. Name three fixes before you buy more slots.

   Partition on the filter column (usually date). Cluster on high-frequency filters / join keys. Select only needed columns (columnar). Avoid SELECT *. A clustered table still scans the partition; it just reads fewer blocks inside it.

2. medium · BigQuery

   On-demand vs editions / slots. How do you explain the bill to a PM?

   On-demand: you pay per TiB scanned — great until a JOIN without a partition hits a 20 TB table. Slots / editions: you buy capacity; waste is idle slots, not a surprise scan. Dashboards that refresh every 5 minutes belong on capacity or a materialized table, not on-demand ad-hoc SQL.

3. easy · BigQuery

   Streaming inserts vs load jobs. When is streaming the expensive habit?

   Streaming is for true low-latency rows and costs more. Nightly dumps should be load jobs (or Storage Write API in batch). Querying the streaming buffer has caveats. Do not stream a daily Salesforce extract.

4. hard · BigQuery

   Authorized views vs copying a table into another project. What is the governance answer?

   Authorized views let project B query a restricted slice without owning the data. Copying creates a second source of truth and a stale-ACL problem. Row-level security / policy tags are the next layer when a view is not enough.

5. medium · BigQuery

   Why can a clustered table still charge you for a full partition?

   Clustering is not a partition. If you do not filter the partition column, BigQuery still opens every partition and then uses clustering inside those files. Date filter + cluster on customer_id is the usual pair. Clustering alone is not a magic index.

6. easy · BigQuery

   Slots vs a materialized table for a 5-minute dashboard.

   Materialize the aggregate (or an MV) and/or put repeat BI on capacity. Do not re-scan 4 TB on-demand every refresh.

7. medium · BigQuery

   Authorized view vs copying into another project.

   Authorized view (or row ACLs) keeps one source of truth. Copies go stale and pick up the wrong ACL.

8. medium · BigQuery

   Streaming a nightly dump. Why is that the expensive habit?

   Streaming is for low-latency rows. Nightly files are load jobs. You pay more and gain nothing.

9. medium · BigQuery

   INFORMATION_SCHEMA for FinOps. What do you look up?

   Jobs by user, bytes billed, slot-ms, the query text that scanned the monster. Then partition that table.

10. medium · BigQuery

    You clustered on customer_id and filtered WHERE country = 'US'. Why did bytes scanned barely drop?

    Clustering helps when the filter matches the cluster columns (and the table is large enough). Country is not customer_id. Partition by date, cluster by the selective columns you actually filter.

11. hard · BigQuery

    A scheduled query overwrites a table; two dashboards query it at 08:01. What can they see?

    A truncate-and-load can show empty or half-written depending on job type. Load with a temp table and swap, or use time-travel/clone. BI should not hit a table you overwrite in place without a transaction story.

12. medium · BigQuery

    Slots are reserved; a student query still billed on-demand. How?

    The job ran outside the reservation assignment (wrong project, admin project, or on-demand fallback). Draw the assignment: project → reservation → folder. “We bought slots” is not a policy.

13. easy · BigQuery

    INFORMATION_SCHEMA.JOBS for FinOps. Which three fields do you group first?

    user_email or principal, query (or labels), and total_bytes_billed / slot_ms. Then look at cache_hit. Without labels, you have a list of people, not teams.

14. easy · BigQuery

    Streaming inserts for a nightly dump of 400 GB. Why is that the expensive habit?

    Streaming is for low-latency rows; you pay a streaming premium and get tiny buffers. Load jobs (or Storage Write API in batches) for dumps. Nightly is not streaming.

15. medium · BigQuery

    Authorized view in project analytics_prod, source in project raw. What must the viewer role not get?

    Direct SELECT on raw. The view’s owner needs source access; the analyst gets the view only. Copying the table into their project is how PII spreads.

16. medium · PostgreSQL

    A dashboard is slow. First tool?

    EXPLAIN (ANALYZE, BUFFERS). Name seq scan vs index, then decide. Adding an index without a plan is cargo cult.

17. hard · PostgreSQL

    NULL in a unique column. What happens?

    Postgres unique allows multiple NULLs (until NULLS NOT DISTINCT). Warehouse dialects differ. CDC + MERGE on a nullable key silently drops or dupes. Name the constraint.

System design (1)

1. medium · System design

   Cost doubled and nobody knows which team. What was missing in the design?

   Tags / chargeback on compute and storage, query tags from dbt/jobs, and an owner on every gold table. Architecture without FinOps is a slide.

Data quality (1)

1. medium · Data quality

   A check is red every day and nobody cares. What did you do wrong?

   The threshold is wrong or the owner is missing. Fix or delete. A permanently red test is worse than no test — it trains the team to ship on red.

AI / ML (2)

1. medium · AI / RAG / MLOps

   Why do you store the embedding model version next to every vector?

   Embeddings from model A and model B are not comparable. A silent model upgrade without a backfill mixes spaces and tanks retrieval. Version the model, the chunker, and the source document hash. This is data versioning, not ML trivia.

2. hard · AI / RAG / MLOps

   When is “just use the warehouse LLM function” the wrong answer?

   When you need a specific model, tight latency, or tool-use the warehouse cannot host. Cortex/BigQuery ML are ELT steps, not every product.

Behavioral (3)

1. medium · Behavioral

   Describe a design you were wrong about. What did you change?

   Pick a real miss: Type-1 when you needed Type-2, Streams+Tasks when you needed Airflow, pandas on a 40 GB join. Say the incident that proved it, the rollback, and the rule you wrote down. Humility plus a written policy beats a perfect résumé of wins.

2. medium · Behavioral

   A project you led was cancelled after two months. What was still valuable?

   The decision record, the prototype that showed cost, and the people you unblocked. Do not pretend it shipped. Interviewers want learning and shutdown hygiene, not a fairy tale.

3. easy · Behavioral

   Tell me about a time you reduced warehouse cost without a hero rewrite.

   Concrete: auto-suspend, a clustering key on a real filter, killing a 5-minute dashboard tick, or a warehouse per team. Percent and dollars. “We migrated everything to X” is not the only story — small levers count.

SQL coding labs

Problem statements only. Run them against tests on DataLane; solutions live in the pad.

1. medium · about 6 min · tables: employees

   Who reports to Ben

   The employees table is an org tree: manager_id points back at employee_id in the same table. Return Ben’s direct reports — one level down, not the whole subtree.

   https://www.datalane-data.blog/coding/problems/reports-to-ben/

2. hard · about 14 min · tables: employees

   Salaries above the department average

   Return employees earning more than their own department’s average, and show that average beside them. The trap: compute the average with a window and filter it in WHERE, and the average is recomputed over only the surviving rows.

   https://www.datalane-data.blog/coding/problems/salary-above-dept-average/