The BigQuery bill doubled, a Dataflow job is an hour behind Pub/Sub, a user asked to be deleted. Reported GCP scenario questions with the numbers filled in, and the choices defended: Dataflow or Dataproc, streaming or load jobs, on-demand or reserved slots.
Bills, full scans, big fact tables and deletes. Every one of these comes down to what a query is billed for and what the table layout lets BigQuery skip.
Streaming on Pub/Sub & Dataflow
6
Lag, duplicates, late events and exactly-once counts — the streaming questions where the transport, the processor and the sink each own part of the answer.
Batch, Spark & CDC
5
Choosing the engine for a stated job, making a slow Spark job fast, and getting an operational database into BigQuery without a nightly dump.
Failures, quality & serving
4
The morning after: a DAG that failed half-way, a bad file that loaded cleanly, a dashboard BigQuery cannot serve, and a bucket growing forever.
Evergreen · asked verbatim
5
The flat form, in the words interviewers actually use. Same ground as the scenarios above, asked as recall — because a candidate who can cut a bill in a scenario can still stall on "how do you reduce BigQuery costs".
Your project is on BigQuery on-demand pricing and the monthly query bill went from about $9,400 to $18,800 with no new dashboards announced. How do you find out why, and how do you bring it down?
Why they ask this
Cost is the theme GCP interviewers return to most, and Deloitte's published list pairs "reduce BigQuery costs" with INFORMATION_SCHEMA for exactly this reason. It tests whether you investigate with data before prescribing partitioning.
Say this
At $6.25 per TiB that is roughly 1,500 TiB a month becoming 3,000, so I would first find which users, service accounts and query shapes account for the extra 1,500 TiB using INFORMATION_SCHEMA.JOBS. Then I would fix those specific queries and add guardrails — required partition filters, a maximum bytes billed per query and custom daily quotas — so the next regression fails loudly instead of billing quietly.
The reasoning
**Size it.** On-demand is billed per TiB scanned (US list price $6.25, first 1 TiB a month free), so $9,400 is about 1,500 TiB and $18,800 about 3,000 TiB. A doubling in bytes almost never comes from organic growth in a month; it comes from a handful of jobs. That framing tells you to look for outliers, not to tune everything.
**Find it.** Query `INFORMATION_SCHEMA.JOBS` for the region, last 60 days, `job_type = 'QUERY'`, and group `SUM(total_bytes_billed)` by week and by `user_email`, then by `query_info.query_hashes.normalized_literals` to collapse the same query with different literals. The usual culprits: a scheduled query or dbt model whose partition filter was lost when someone rewrote a view; a BI tool refreshing a `SELECT *` extract every 15 minutes; a filter on a derived expression (`DATE(event_ts)`) instead of the partitioning column; a new join that fans out; or a service account someone pointed at the wrong table. Labels on jobs, if the team set them, make the attribution immediate.
**Fix the cause.** Restore the partition filter or rewrite it on the partitioning column; replace `SELECT *` with the columns used; point the BI tool at a small aggregate table or a materialized view; cluster the big table on the columns those queries filter by. Each fix is verified the same way it was found — the job's `total_bytes_billed` before and after.
**Stop the next one.** Set `require_partition_filter = true` on large partitioned tables so an unfiltered query errors instead of scanning; set `maximum_bytes_billed` on scheduled and tool-driven jobs; add custom quotas (query usage per day per user or per project); and alert on daily bytes billed from the same view. If after all that the workload is large and steady, compare the on-demand bill with the slot-hours it would need under an edition — that is a pricing decision, not a fix.
The formulations
Attribute bytes before changing anythingship
SELECT user_email,
SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 60 DAY)
AND job_type = 'QUERY'
GROUP BY user_email
ORDER BY tib_billed DESC
Tells you which principal and which query shape doubled the bill, so every later fix is measured against it.
Caps the money by making bad queries slow instead of expensive; the runaway job is still there, now competing for slots.
The answer most people give
"Partition and cluster all the big tables." That may be right eventually, but without attribution you cannot say which tables matter, and the doubling is usually one job that a single `WHERE` clause fixes.
They’ll ask next
The top job turns out to be a Looker explore that analysts need. How do you cut its cost without taking it away?
An events table of 6 TiB is partitioned daily on event_date, yet a query with WHERE DATE(event_ts) = '2026-09-01' bills the full 6 TiB. Why is partition pruning not happening, and how do you make sure it does?
Why they ask this
Partition pruning is what keeps BigQuery affordable, and Tiger Analytics' loop is reported to probe it. The question checks whether you know the precise rules for when BigQuery can skip a partition.
Say this
BigQuery prunes only when the filter is on the partitioning column itself, compared with a constant expression; `event_ts` is a different column, so every partition is read. Filter on `event_date` directly, keep the column alone on one side of the comparison, and set `require_partition_filter` so an unpruned query errors instead of billing.
The reasoning
Three years of daily partitions is about 1,100 partitions of roughly 5.5 GiB each, so a correctly pruned one-day query should bill a few GiB, not 6 TiB. The query filters on `DATE(event_ts)`. Even if `event_date` was derived from `event_ts` at load time, BigQuery does not know that; pruning is decided from the partitioning column alone. So it scans all partitions and filters rows afterwards.
The other ways the same thing happens, which interviewers push on next: the partitioning column wrapped in an expression that combines it with another field (`TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > …`) — BigQuery's docs say to isolate the partitioning column on one side of the comparison; a **dynamic** filter such as `WHERE event_date = (SELECT MAX(event_date) FROM events)`, which the docs say cannot limit partitions the way a constant expression can; filtering through a view or a join where the predicate never reaches the partitioned table; and for ingestion-time tables, filtering on a data column rather than `_PARTITIONTIME` or `_PARTITIONDATE`.
How to prove it before it costs anything: a dry run (the console estimate, or `bq query --dry_run`) shows bytes to be processed — a partition filter makes that estimate exact, so 6 TiB before the fix and single-digit GiB after is the proof. How to stop it recurring: `ALTER TABLE events SET OPTIONS (require_partition_filter = true)`. Every query must then include a filter that can prune, or it fails. For a derived-date pattern analysts insist on, compute the date parameter first in a script variable and pass it as a constant.
One subtlety: with clustering, a filter on the clustered column prunes blocks, but the dry-run estimate stays an upper bound because the block count is not known until execution. So "the estimate did not drop" is not proof that clustering is not working — the billed bytes after the run are.
The formulations
Filter the partitioning column with a constantship
SELECT user_id, event_name
FROM analytics.events
WHERE event_date = DATE '2026-09-01'
Prunes to one partition and the dry-run estimate is exact, so the saving is visible before the query runs.
Filter a derived expression of another columnavoid
SELECT user_id, event_name
FROM analytics.events
WHERE DATE(event_ts) = '2026-09-01'
Correct rows, full-table bill: event_ts is not the partitioning column, so all 6 TiB are scanned.
Belt and braces on both columnsworks
WHERE event_date = DATE '2026-09-01'
AND DATE(event_ts) = '2026-09-01'
Useful when event_date and event_ts can disagree near midnight; the first predicate prunes, the second keeps rows exact.
The answer most people give
"Add LIMIT 100 so it reads less." LIMIT is applied after the scan; on-demand billing is for bytes read, so the query still costs the whole table.
They’ll ask next
After you set require_partition_filter, a scheduled job fails every night. The owner says the filter is there. What is the query probably doing?
You have a 10 TB orders fact table covering three years, queried mostly by order date and filtered by customer_id and country. How do you partition and cluster it?
Why they ask this
Guides to Google's modelling round name this exact prompt — "for a fact table at 10TB, how do you organise the data?" It checks whether you can pick a partition key, respect the limits and justify the cluster order.
Say this
Partition by day on `order_date` — about 1,100 partitions, well under the 10,000 limit — and cluster on `customer_id` then `country`, the columns queries filter by, in that order. I would add `require_partition_filter` and a partition expiration only if there is a retention rule.
The reasoning
**Partition key.** Choose the column almost every query filters on and that has a natural time grain: `order_date`. Daily partitions over three years give about 1,100 partitions of roughly 9 GB each, which is healthy. Hourly would give over 26,000 partitions and break BigQuery's limit of 10,000 partitions per table; monthly would make each partition 280 GB and prune coarsely. Daily is also the default and matches how the table is loaded and corrected — a late correction rewrites one day, not a month.
**Clustering.** Up to four clustering columns, and order matters: BigQuery sorts blocks by the first column, then the second within it. Put the column filtered most often and most selectively first — here `customer_id`, which has high cardinality and is exactly the kind of column partitioning cannot handle — then `country`. A query filtering on `country` alone still benefits, but less than one filtering on `customer_id`. BigQuery reclusters automatically in the background at no charge, so appends do not degrade the layout for long.
**Guardrails.** `require_partition_filter = true` so nobody scans 10 TB by accident. Partition expiration only if a retention rule says data older than N days must go — it deletes whole partitions automatically. Long-term storage pricing applies per partition, so three-year-old partitions that are never modified already cost about half.
**What to check with the team.** If many queries filter by a different date (ship date, invoice date), one partition key cannot serve both; cluster on the second date or build a second table for that access path. If daily data is tiny, say under a gigabyte a day, monthly partitions with clustering on the date would serve better — tiny partitions add metadata overhead without saving meaningful bytes.
The formulations
Daily partitions, two clustering keysship
CREATE TABLE sales.fct_orders
PARTITION BY order_date
CLUSTER BY customer_id, country
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM staging.orders
About 1,100 partitions, block pruning on the two filter columns, and unfiltered scans refused.
Integer-range partitions on customer_idavoid
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 10000000, 1000))
Loses date pruning, which almost every query needs, and fixed ranges skew badly as new customers arrive.
The answer most people give
"Partition by customer_id so customer lookups are fast." You can only partition on a date, timestamp or integer range, a high-cardinality key would blow through the 10,000-partition limit, and you would lose date pruning. Customer_id is a clustering column.
They’ll ask next
Half the dashboards filter on ship_date instead of order_date. What do you change, and what does it cost?
Your team scans about 1,200 TiB a month on BigQuery on-demand. INFORMATION_SCHEMA shows the queries use about 200 slots on average during 10 working hours a day, 22 days a month, and almost nothing otherwise. Should you move to capacity pricing, and which edition?
Why they ask this
Slot allocation is named in guides to Google's loop, and this is where it turns into money. The interviewer wants the arithmetic and the caveats, not a preference.
Say this
On-demand costs about $7,500 a month here; the same work is roughly 44,000 slot-hours, which is about $2,600 at the Enterprise pay-as-you-go list price or $1,800 on Standard, before autoscaling overhead. So capacity pricing is likely cheaper, but I would pilot an autoscaling reservation with a zero baseline on a subset of projects and compare real bills before moving everything.
The reasoning
**On-demand side.** 1,200 TiB at the US list price of $6.25 per TiB is $7,500 a month (less the free first TiB).
**Capacity side.** 200 slots × 10 hours × 22 days = 44,000 slot-hours. At pay-as-you-go list prices that is about $1,760 on Standard ($0.04), $2,640 on Enterprise ($0.06) and $4,400 on Enterprise Plus ($0.10). The measurement matters: take `SUM(total_slot_ms) / 3,600,000` from `INFORMATION_SCHEMA.JOBS` for the same month, not a guess. And add overhead — the autoscaler adds slots in steps of 50, bills per second with a one-minute minimum, and scales down with a lag, so real consumption runs above the average. Even at 1.5× the estimate, Enterprise is around $4,000.
**Which edition.** Standard is cheapest but caps a reservation at 1,600 slots, supports autoscaling only (no baseline, no commitments) and lacks features some teams need; check the editions feature table against what you use — BigQuery ML and several governance and performance features are Enterprise-and-above. Enterprise suits this profile: a zero or small baseline with autoscaling to, say, 400 slots covers the 9-to-7 peak and costs almost nothing overnight. A one- or three-year commitment ($0.048 or $0.036 per slot-hour for Enterprise) only pays if you have a steady 24-hour baseline to commit, which this team does not.
**What changes for users.** Under capacity pricing a bad query costs time, not money: a 50 TiB scan now competes for the same 400 slots as everyone's dashboards. So keep the guardrails from on-demand (partition filters, bytes limits on scheduled jobs) and watch queueing. And you can mix: assign steady ELT projects to the reservation and leave a sporadic data-science project on on-demand.
The answer most people give
"Reservations are always cheaper at scale." They are cheaper when slots are busy. A reservation sized to the peak and left idle most of the day can cost more than on-demand — the answer depends on slot-hours used, which you have to measure.
They’ll ask next
After the switch, a data scientist's ad-hoc 80 TiB query slows every dashboard for 20 minutes. What do you change?
Reported · 2IAM & data securityBigQuery partitioning & clustering
A user exercises their GDPR right to erasure. Their events sit in a 4 TiB BigQuery table partitioned daily over three years, plus raw logs in Cloud Storage and a profile row in Bigtable, and you receive about 200 such requests a week. How do you purge them within 30 days?
Why they ask this
GDPR deletion is on compiled BigQuery lists and in Spotify-style question sets because it collides with everything that makes a warehouse cheap: append-only loads, partitioning and time travel.
Say this
Batch the week's requests into a table and run one `DELETE … WHERE user_id IN (SELECT user_id FROM deletion_requests)` per affected table, with the table clustered on `user_id` so the delete reads fewer blocks, and delete matching Cloud Storage objects and Bigtable rows from the same list. Then account for the copies you did not delete: BigQuery keeps deleted data for the time-travel window (seven days by default) plus seven days of fail-safe, so the 30-day promise has to include those 14 days.
The reasoning
**Batch, do not trickle.** 200 single-user `DELETE` statements a week each rewrite blocks across the table and queue behind one another, because mutating DML on a table is serialised. Land requests in a `deletion_requests` table and run one delete per target table per day or week. A `DELETE` on a partitioned table scans the columns referenced in every partition it touches (three years here), so cluster the table on `user_id` — the delete then reads and rewrites only blocks that can contain those users. Each job can touch up to 4,000 partitions; three years of daily partitions is about 1,100, so one statement covers it.
**Know where copies survive.** After a `DELETE`, the old data stays recoverable through time travel for the dataset's window — seven days by default, configurable down to two — and then for a further seven days of fail-safe, which cannot be shortened. Snapshots, table clones, exported extracts and derived tables built from the raw events also hold the user; the deletion list has to drive all of them. Setting the time-travel window to two days on datasets with personal data shortens the tail to nine days.
**The other stores.** In Cloud Storage, raw log files mixing many users cannot have one user removed in place — you rewrite the affected objects without that user, or you avoid the problem upstream by pseudonymising: store a per-user key and a surrogate id, and delete the key (crypto-shredding) so the surviving bytes are no longer personal data. Bigtable rows keyed by user are deleted by row key. Object versioning and retention policies on buckets must be compatible with deletion, or the rewrite silently keeps the old version.
**Prove it.** Record each request, the jobs that ran and their affected-row counts, and run a check query per store. Partition expiration handles retention rules ("keep 3 years"), not individual erasure — it is often confused with this.
The formulations
Batched delete driven by a request tableship
DELETE FROM analytics.events
WHERE user_id IN (SELECT user_id FROM privacy.deletion_requests
WHERE status = 'pending')
One mutation per table per batch, auditable, and cheap when the table is clustered on user_id.
One DELETE per request as it arrivesavoid
DELETE FROM analytics.events WHERE user_id = 'u_81523'
Hundreds of serialised mutations a week, each rescanning the table, for no gain in the 30-day deadline.
The answer most people give
"Set a partition expiration and the data ages out." Expiration removes whole partitions by age; it cannot remove one user from recent partitions, and waiting three years does not meet a 30-day erasure deadline.
They’ll ask next
Your finance team needs order totals to stay correct after the user is deleted. How do you delete the person but keep the revenue?
A Dataflow streaming job reads a Pub/Sub subscription receiving about 40,000 messages a second, and the oldest unacknowledged message is now 60 minutes old and rising. What do you check, and in what order do you fix it?
Why they ask this
Guides to Google's loop list "what happens when Dataflow lags? what happens when Pub/Sub backs up?" as failure modes a strong candidate raises unprompted. Here it is the whole question.
Say this
First decide whether the job is slow everywhere or stuck on something: Pub/Sub backlog and oldest-unacked age, then Dataflow data freshness and per-stage wall time, then worker CPU and the autoscaler's maximum. The usual causes are a hot key, a slow external call per element, a sink throttling writes, or a poison message being retried forever — each has a different fix, so scaling up is not the first move.
The reasoning
**Read the shape.** `oldest_unacked_message_age` rising while `num_undelivered_messages` is flat means one stuck message or key. Both rising means throughput is below 40,000 a second. In the Dataflow job, the data freshness and system latency graphs and the per-step wall time show where time goes. Check whether a second pipeline is reading the same subscription — Google's docs warn that sharing one causes duplicates, watermark lag and poor autoscaling.
**Common causes, and fixes.** Autoscaling pinned at `max_num_workers`: raise it, and make sure Streaming Engine is on so scaling is faster. A **hot key** (Dataflow logs "hot key detected"): one worker does most of the work in a `GroupByKey` — use a combiner (`Combine.perKey` pre-aggregates before the shuffle) or add a random suffix to the key and aggregate twice. A `DoFn` making an HTTP or database call per element: batch the calls, cache, or use a side input. A throttled sink (BigQuery write errors and retries in the logs): check quotas and batching. A **poison message**: in streaming, Dataflow retries a failing bundle indefinitely, so one bad record can stall a stage — catch the exception and route bad records to a dead-letter output.
**Catch up and prevent.** Once fixed, the job drains the backlog faster than real time only if it has headroom, so temporarily raise the worker ceiling. Alert on `oldest_unacked_message_age` and on data freshness, not on CPU. And remember the backlog has a deadline: Pub/Sub keeps unacknowledged messages for the subscription's retention (seven days by default), after which they are dropped.
The answer most people give
"Add more workers." If the lag is a hot key or a poison message, more workers sit idle while one is saturated or one bundle retries — the bill goes up and the lag does not move.
They’ll ask next
The lag clears, but the hourly aggregates for the lagged hour in BigQuery are lower than usual. Why, and what do you do about it?
A payments stream flows from Pub/Sub into BigQuery, and after a subscriber restart about 2% of the rows for that hour are duplicates. How do you make sure data is not duplicated and none is lost?
Why they ask this
A Walmart hiring manager asked exactly this of a data engineer candidate — how you make sure data is not duplicated and there is no data loss. Pub/Sub's at-least-once delivery makes it the default problem on GCP.
Say this
The duplicates are redeliveries: the subscriber wrote rows but restarted before acknowledging, so Pub/Sub sent them again. You cannot remove redelivery, so make the write idempotent on a business key — a `MERGE` on `payment_id`, or Dataflow's de-duplication plus an ID attribute — and acknowledge only after the write succeeds, which is what prevents loss.
The reasoning
**Where the 2% came from.** A subscriber pulled messages, inserted them into BigQuery, then restarted before its acks reached Pub/Sub. After the ack deadline, Pub/Sub redelivered them with the same message IDs, and the subscriber inserted them again. A second source is the publisher: if it retried after a lost publish response, the same payment exists as two messages with different IDs. The two need different fixes, so check whether the duplicate rows share a message ID.
**No loss.** Acknowledge only after the write is durable. Acking before writing turns a crash into lost data; acking after writing turns it into a duplicate, which is recoverable. So at-least-once plus an idempotent write is the target.
**No duplicates.** Options, strongest first: a Dataflow pipeline in exactly-once mode removes redeliveries by message ID, and with `id_label` / `withIdAttribute` set to `payment_id` it also removes publisher retries published within ten minutes of each other; write through the Storage Write API. Or land rows in a staging table and `MERGE` into the target on `payment_id`, so a repeat updates nothing. Or, for analytics only, keep raw rows and expose a view with `QUALIFY ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY publish_time) = 1`. Enabling Pub/Sub exactly-once delivery on a pull subscription helps with redeliveries, but not with publisher retries.
The formulations
Staging table plus MERGE on the business keyship
MERGE payments.fct_payment t
USING (SELECT * FROM payments.stg_payment
QUALIFY ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY publish_time) = 1) s
ON t.payment_id = s.payment_id
WHEN NOT MATCHED THEN INSERT ROW
A redelivered or re-published payment matches an existing row and inserts nothing, whatever its message ID.
Plain INSERT from the subscriberavoid
INSERT INTO payments.fct_payment SELECT * FROM payments.stg_payment
Every redelivery becomes a row; it is exactly how the 2% got there.
De-duplicating view over raw rowsworks
SELECT * FROM payments.raw_payment
QUALIFY ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY publish_time) = 1
Fine for analytics and keeps the raw evidence, but every reader pays for the de-duplication at query time.
The answer most people give
"Enable exactly-once delivery on the subscription." It only stops redelivery of one message ID to pull subscribers in one region. A publisher retry is a new message, and the write itself is still not idempotent.
They’ll ask next
Payments can be corrected later with a new amount under the same payment_id. Does your MERGE still work?
Ads click events flow from Pub/Sub through Dataflow into BigQuery. Upstream retries create duplicates and some events arrive up to 30 minutes late. How do you produce exactly-once daily unique click counts per campaign?
Why they ask this
It is reported as a Google data engineer question, and it combines every streaming concept at once: de-duplication, event time, windows, lateness and an idempotent sink.
Say this
De-duplicate on a `click_id` set by the client, window by event time into daily fixed windows with allowed lateness a little over 30 minutes, and emit early results each minute plus a final one when the window closes. Write each campaign-day as one row replaced on every firing, so repeated panes overwrite rather than add.
The reasoning
**Identity.** Upstream retries create new Pub/Sub messages, so message-ID de-duplication does not catch them. Require a `click_id` generated where the click happens. If the publisher puts it in a message attribute, the Pub/Sub connector can de-duplicate on it (within ten minutes of publication); for longer gaps, key by `click_id` and keep a "seen" flag in state until the day's window expires.
**Time.** Read event time from the click's timestamp (a message attribute), not publish time, so a late click lands in the right day. Use daily fixed windows — in UTC unless you offset them for a reporting time zone — with allowed lateness of, say, 45 minutes, early firings every minute of processing time for a live dashboard, and a late firing per late batch. Use accumulating panes so each firing carries the full count so far.
**Count and sink.** After de-duplication, "unique clicks per campaign" is a count per campaign per window. If "unique" means distinct users, use an exact distinct on user id per campaign-day, or an approximate sketch (HyperLogLog++) if the volume makes exact state too large, and say which. Because the same campaign-day is emitted many times, the sink must replace, not append: write panes to a staging table and `MERGE` on (`campaign_id`, `day`) keeping the latest pane, or append with a pane sequence number and read the latest through a view. Anything later than the allowed lateness is dropped by the pipeline, so reconcile the finished day against raw clicks in BigQuery with a nightly batch.
The answer most people give
"Dataflow is exactly-once, so just count." Dataflow de-duplicates Pub/Sub redeliveries, not upstream retries that arrive as new messages, and appending every early pane to BigQuery counts the same clicks many times.
They’ll ask next
Some clicks arrive six hours late from a partner who batches uploads. Do you stretch allowed lateness to six hours, or do something else?
Listening events feed per-user daily statistics. About 5% arrive more than an hour late and 0.5% more than 24 hours late, because phones play offline and upload later, and event volume spikes tenfold during a year-end campaign. How do you handle the late-arriving data?
Why they ask this
Spotify's loop is reported to ask how you handle late-arriving events — how you define lateness and update aggregates when they arrive. The offline-device version forces a real decision rather than a default setting.
Say this
Split it by lateness: the stream handles the first few hours with event-time windows and allowed lateness, and a batch job restates any day that receives late events after that, recomputed from raw events in a date-partitioned BigQuery table. Keeping 24 hours of window state for every user during a tenfold spike would be expensive and fragile.
The reasoning
**Define lateness in event time.** The device records when a track played; that is the event timestamp. Dataflow windows by it, so a late event lands in the day it happened. The question is how long to keep each day's window open. Allowed lateness of a few hours catches most of the 5%; stretching it to 48 hours means holding state for every active user and day for two days, which during a tenfold spike is exactly when you can least afford it.
**Two paths.** Stream: daily windows with early firings for freshness and late firings up to, say, four hours; results upserted into a per-user-day table. Raw path: every event, however late, appended to a raw table partitioned by event date (Storage Write API or load jobs from Cloud Storage). Batch correction: a scheduled job finds event dates that received rows in the last day (from ingestion time) and recomputes just those partitions with `MERGE` or partition overwrite. Stats become final after the correction window, and the product can say so.
**The spike.** Pub/Sub absorbs the burst; Dataflow autoscales with Streaming Engine; set the worker ceiling for ten times normal load in advance and test it. The batch correction does not care about the spike at all, which is another reason to move the long tail there.
The answer most people give
"Set allowed lateness to 48 hours." It works on a whiteboard; in practice it keeps two days of per-user state for hundreds of millions of users, and still drops the event that arrives on day three.
They’ll ask next
Users see their stats change the next morning and file bugs. How do you present provisional versus final numbers?
You run a daily batch pipeline that computes revenue from orders in BigQuery. How would you migrate it to a streaming-first design on Pub/Sub and Dataflow, and what changes, what stays the same, and what becomes harder?
Why they ask this
It is quoted as a Google data engineer question in guides to the loop. The interviewer wants the comparison — event time, exactly-once, late data, operations — not a list of services.
Say this
The business logic and the keys stay; the input becomes unbounded, so "the day" becomes an event-time window with a watermark, late orders and refunds need an update path, and the sink must accept repeated updates idempotently. What gets harder is correctness over time: corrections, reconciliation with finance, and running a job that never stops.
The reasoning
**Stays the same.** The revenue definition, currency and tax rules, the order and refund keys, and the target table's grain. Beam can run the same transforms in batch, so the logic can be shared and the batch path kept for backfills.
**Changes.** Orders are published to Pub/Sub; Dataflow windows them by order time into days (or hours) with a watermark; early firings make today's number visible within a minute. The output is no longer written once — each window emits several panes, so the sink becomes an upsert on (day, currency, region) rather than an overwrite of yesterday's partition. De-duplication moves from "select distinct in the batch" to a key-based de-duplication in the stream, because Pub/Sub is at-least-once.
**Harder.** Refunds and corrections that arrive days later — beyond any sensible allowed lateness — need a restatement path, usually the old batch job, now running over the raw event table. Reconciliation with finance, who close books on a batch cadence, needs a clear "final" moment. Operationally you now watch lag, watermark and backlog 24 hours a day, and the job costs money even when nobody looks at the dashboard. The honest answer often keeps both: stream for the live number, batch for the number finance signs off.
The answer most people give
"Replace the scheduled query with a Dataflow job that writes to the same table." Without event-time windows and an idempotent sink, repeated panes and late refunds make the streaming number disagree with finance within a week.
They’ll ask next
Finance asks for a single "revenue as of midnight" number that never changes afterwards. How do you give it to them from a stream?
Design a pipeline for real-time listening events from about 300,000 events a second (1 KB each, peaking at three times that), where dashboards must be under a minute behind and raw events must be kept for replay. Which GCP services do you use, and why does BigQuery fit?
Why they ask this
Spotify candidates report being asked to explain the high-level architecture of a data pipeline and justify why services like BigQuery fit the stack; Interview Query's Spotify guide lists the real-time listener pipeline as a typical prompt.
Say this
Clients publish to Pub/Sub; a Dataflow streaming job validates, de-duplicates on an event id and enriches, then writes raw events to a date-partitioned BigQuery table through the Storage Write API and minute aggregates for dashboards; bad records go to a dead-letter output, and the raw stream is also archived to Cloud Storage for replay. BigQuery fits because storage and compute scale separately, it ingests streams directly and it answers ad-hoc questions without a cluster.
The reasoning
**Sizing.** 300,000 × 1 KB is about 300 MB a second — roughly 24 TiB a day, three times that at peak. Pub/Sub absorbs the peak without pre-provisioned partitions; Dataflow with Streaming Engine and horizontal autoscaling sizes workers to the load, with the worker ceiling set for the peak. At the Storage Write API list price of $0.025 per GiB, streaming 24 TiB a day into BigQuery is about $600 a day, which is worth stating: the ingestion path is a real cost line.
**Layers.** Pub/Sub topic (no ordering keys — listening events do not need order, and keys would cap throughput per key). Dataflow: parse and validate, de-duplicate on the client's `event_id`, enrich with a side input of track metadata, window into one-minute aggregates. Sinks: raw events to BigQuery partitioned by event date and clustered by country and user, aggregates to a small table (or Bigtable if an app needs millisecond reads), and Avro or Parquet files to Cloud Storage for replay and reprocessing. A Composer DAG runs the daily batch jobs that restate late data.
**Why BigQuery.** Analysts get SQL over the raw events within seconds, without sizing a cluster; nested fields hold the event payload without a schema per event type; and partitioning plus clustering keep per-query cost proportional to the question asked. Where it does not fit: serving per-user numbers to millions of app screens, which belongs in a key-value store.
The answer most people give
"Pub/Sub into BigQuery, done." A BigQuery subscription is fine for raw landing, but it is at-least-once and does no validation, de-duplication or windowing, so the minute-level dashboard and the duplicates problem are left unsolved.
They’ll ask next
A bad client release sends malformed events for two hours. Walk through how those events are caught, stored and later replayed.
You are moving 180 nightly PySpark jobs (about 40 TB of input) off an on-premises Hadoop cluster, and you also need a new clickstream job at 20,000 events a second producing one-minute aggregates. The team knows Spark and not Beam. Which jobs go to Dataproc and which to Dataflow?
Why they ask this
A Capgemini candidate reports an interviewer who wanted Dataflow experience when theirs was Dataproc, and guides to Google's loop expect the reasoning "Dataflow over Dataproc because…, but Dataproc if we already had Spark". This makes the choice concrete.
Say this
The 180 Spark jobs go to Dataproc — serverless batches or ephemeral job clusters — because rewriting working Spark in Beam buys nothing and risks correctness. The new clickstream job goes to Dataflow, which gives managed streaming, autoscaling and exactly-once processing without running a cluster around the clock; Spark Structured Streaming on Dataproc is the fallback only if the team cannot take on Beam.
The reasoning
**The batch estate.** These jobs already work and are written in Spark. Dataproc runs them with little more than changing `hdfs://` paths to `gs://` and moving the Hive metastore. Nightly work fits serverless batches (per-second billing, nothing idle) or ephemeral clusters created per DAG run when jobs need custom images. Rewriting 180 jobs in Beam is months of work and a full reconciliation effort, for jobs that are not broken.
**The new stream.** Nothing exists yet, so choose on merit. Dataflow is built for streaming: event-time windows and watermarks for the one-minute aggregates, autoscaling to the load, exactly-once processing by default, and no cluster to patch. Running Spark Structured Streaming on Dataproc means a cluster up all day, micro-batch latency and your own checkpoint management — reasonable if the team must stay in one framework, but it is the weaker default.
**Also worth saying.** Some of the 180 jobs are probably SQL written in Spark; those can become BigQuery SQL (scheduled queries or dbt) with no cluster at all. Measure before and after: the reconciliation of old against new output per job is the migration plan, not a detail.
The answer most people give
"Dataflow is Google's recommended service, so move everything to Dataflow." Rewriting working Spark to Beam is cost and risk with no benefit; Dataflow's advantage is for new pipelines, especially streaming ones.
They’ll ask next
A year later half the batch jobs are PySpark with pandas UDFs that run slowly on serverless. What do you look at?
A nightly PySpark job on Dataproc used to take 25 minutes and now takes over two hours. In the slow stage, 199 of 200 tasks finish in about a minute and the last one runs for 90 minutes. How do you debug and fix it?
Why they ask this
It is on Deloitte's published managerial-round list. The numbers here point at skew, and the interviewer wants to see you read the Spark UI before touching cluster size.
Say this
One task taking 90 minutes while the rest take one is data skew: a single key holds a large share of the rows in that shuffle. Confirm it in the Spark UI (task duration and shuffle read size per task), then fix the skew — adaptive query execution's skew-join handling, broadcasting the small side, or salting the hot key — rather than adding workers, which would leave the one task exactly as slow.
The reasoning
**Find it.** Open the Spark UI through the Dataproc persistent history server (clusters are often ephemeral, so logs and history must be in Cloud Storage). In the slow stage, compare task duration and shuffle read bytes: if one task reads 40 GB and the others 200 MB, it is skew. Then find the key — a quick `groupBy(key).count()` on the join input usually shows a null, a default value like `0` or `unknown`, or one giant customer.
**Fix it.** With Spark 3, check `spark.sql.adaptive.enabled` and `spark.sql.adaptive.skewJoin.enabled` — AQE splits oversized partitions in sort-merge joins automatically. If the other side of the join is small, broadcast it and the shuffle disappears. If the hot key is null or junk, filter it out or handle it separately. Otherwise salt: add a random suffix to the hot key on the large side, replicate the matching rows on the small side, and join on the salted key.
**Rule out the Dataproc-specific causes too.** Secondary workers are preemptible or Spot by default; losing them mid-shuffle recomputes stages, which shows as failed and retried tasks rather than one slow task. Autoscaling that removes nodes during a shuffle does the same; enhanced flexibility mode or graceful decommissioning help. Many small input files in Cloud Storage slow listing and planning, which shows before the first stage starts.
The answer most people give
"Double the number of workers." The 199 fast tasks get faster and the one skewed task — which is the whole two hours — does not change.
They’ll ask next
The skewed key is a real customer that is 30% of all orders. Salting helped the join, but the aggregation after it is now the slow stage. Why?
Design an end-to-end pipeline that takes about 500 GB of CSV files landing in Cloud Storage each night, transforms them with PySpark on Dataproc and loads the result into BigQuery. How do you make reruns safe?
Why they ask this
It is on Deloitte's published managerial-round list word for word, next to "how do you read from GCS and write to BigQuery using PySpark". The rerun question is where the design is judged.
Say this
Files land under a date prefix; a Composer DAG waits for a completion marker, runs a serverless PySpark batch that reads with an explicit schema, validates, writes Parquet to a curated bucket and writes that date's partition in BigQuery, then checks row counts. Every step is keyed by the run date and overwrites its own output, so a rerun replaces the day instead of appending it twice.
The reasoning
**Land.** Sources write to `gs://raw/orders/dt=2026-09-14/` and finish with a `_SUCCESS` or manifest file listing expected files and row counts. The DAG's sensor waits for that marker, not for "some files", so a half-delivered day does not start the job.
**Transform.** A `DataprocCreateBatchOperator` (serverless) or an ephemeral cluster runs PySpark: read CSV with an explicit schema rather than `inferSchema` (500 GB is too much to scan twice, and inference silently changes types), reject rows that fail validation into a quarantine path, deduplicate on the business key, and write Parquet to `gs://curated/orders/dt=…/` with overwrite. Load to BigQuery either with the spark-bigquery connector (indirect mode stages files and runs a load job; direct mode uses the Storage Write API), or — often simpler — with a BigQuery load job from the curated Parquet, which uses the free shared slot pool.
**Make reruns safe.** Target a date-partitioned table and replace only that date: a load with `WRITE_TRUNCATE` to the partition decorator (`orders$20260914`), or the connector's option to write a specific partition. Never append. Finish with a check task comparing the manifest's row count with BigQuery's partition count, and fail the DAG if they differ. Because every step is idempotent per date, clearing the DAG run is the recovery procedure.
The answer most people give
"Spark reads the bucket and appends to the BigQuery table." It works on the first run. A retry after a mid-job failure appends the same day again, and a late-arriving file is either missed or loaded twice.
They’ll ask next
One source sends a corrected file for three days ago. What reruns, and what does not?
Write Apache Beam or PySpark code to load about 2 TB of CSV files a day from Cloud Storage into a BigQuery table. Would you use code at all?
Why they ask this
Tech Mahindra's GCP data engineer interviews are reported to ask for Beam or PySpark code that loads CSV into BigQuery. The strong answer writes the code and then asks whether a load job would do.
Say this
If the files only need loading, a BigQuery load job is the answer — no code, free ingestion on the shared slot pool, and a partition overwrite makes it rerunnable. If rows need parsing, cleaning or enrichment first, a Beam pipeline on Dataflow reading with `ReadFromText` and writing with `WriteToBigQuery` using file loads is the batch-friendly version.
The reasoning
**No-code first.** `bq load --source_format=CSV --skip_leading_rows=1 --replace 'sales.orders$20260914' 'gs://landing/orders/dt=2026-09-14/*.csv' ./schema.json` loads the day into its partition and replaces it on rerun. Batch loading uses the shared slot pool at no charge; a single load job can take up to 15 TB, so 2 TB is routine. One gotcha: gzip-compressed CSV files are limited to 4 GB each and cannot be read in parallel, so many uncompressed or moderately sized files load faster than one huge `.csv.gz`.
**When code is needed.** Malformed quoting, per-row validation, lookups against reference data or splitting one file into several tables justify a pipeline. In Beam, use the file-loads method for batch — it stages files and runs load jobs — rather than streaming inserts, which would add per-byte cost to a batch job for nothing. Route rows that fail parsing to a dead-letter file instead of failing the job.
**PySpark** does the same through the spark-bigquery connector, and makes sense when the transform is already Spark or needs Spark's joins at scale.
Pays per-byte streaming charges to load data that was already sitting in files.
The answer most people give
"Read the CSV with pandas and insert rows through the client library." Two terabytes will not fit in one machine's memory, and row-by-row inserts pay streaming prices for a job a free load handles.
They’ll ask next
One partner's CSV has embedded newlines inside quoted fields and the load fails. What do you change?
An orders database on Cloud SQL for MySQL is 3 TB and changes about 4,000 rows a second. Analysts want it in BigQuery no more than 15 minutes behind, including deletes. How do you set up change data capture with Datastream?
Why they ask this
Database ingestion with Datastream appears on compiled GCP lists, and CDC into the warehouse is a routine design task at GCP-heavy companies. It tests the setup details that decide whether it works in production.
Say this
Enable binary logging in row format with enough retention, create a Datastream stream with private connectivity from the Cloud SQL instance to a BigQuery destination in merge mode, backfill the 3 TB once, and set `max_staleness` to about 10 minutes so background merges keep the tables within the 15-minute target. Tables without a primary key fall back to append-only, so check keys first.
The reasoning
**Source.** Datastream reads MySQL's binary log, so binary logging must be on (on Cloud SQL, with point-in-time recovery enabled) in row format, and retained long enough to cover an outage of the stream — if the log rotates past Datastream's position, you backfill again. Create a replication user with the grants Datastream documents, and connect through private connectivity rather than a public IP.
**Stream and destination.** Include the tables analysts need, run a backfill of existing rows (3 TB takes hours; schedule it off-peak), then CDC continues from the log. In BigQuery, tables with a primary key use **merge** mode — updates and deletes are applied, so the table mirrors the source — and `max_staleness` tells BigQuery how stale the table may be before merging; 10 minutes leaves headroom under the 15-minute target. Lower staleness means more frequent merges and more slot usage, which Google recommends covering with a reservation for heavy CDC.
**What to check.** Tables without a primary key become append-only with a deletion flag, so add keys before starting — adding or removing a primary key on an already-replicated table is not supported by default. Schema changes on the source propagate for supported types, but test a column rename. And if analysts also want history, keep a second append-only stream or snapshot the merged table, because merge mode keeps only current state.
The answer most people give
"A scheduled query that pulls rows with updated_at in the last 15 minutes." Hard deletes never appear, rows updated without touching `updated_at` are missed, and 4,000 changes a second become repeated scans of the production database.
They’ll ask next
Datastream falls six hours behind after a bulk update of 200 million rows. What happened, and how do you catch up?
A Cloud Composer DAG that loads yesterday's events into BigQuery failed at 03:00, and the table has only about half of yesterday's rows. The events come from Pub/Sub through Dataflow. How do you handle the failure in production and recover the missing data?
Why they ask this
Deloitte's managerial round is reported to ask how you handle pipeline failures in production. With Pub/Sub and Dataflow upstream, the recovery path depends on retention windows you have to know.
Say this
First bound the damage: which hours are missing and whether the gap is in the DAG's load or upstream in Dataflow. Then replay from a durable source — the raw archive in Cloud Storage, or the Pub/Sub subscription if the messages are still retained — and rerun the DAG for that date, which is safe only if each task overwrites its own partition.
The reasoning
**Scope it.** Count rows per hour for yesterday against the same hours last week: a clean cut-off points at the DAG or a failed load; a thin spread points upstream. Check the Dataflow job for that window (lag, errors, a drain or update) and the Airflow task logs for the actual failure — often a quota error, an expired credential or a schema change. Tell consumers that yesterday is incomplete before they find out.
**Recover.** If the DAG failed but the data reached a staging table or Cloud Storage, fix the cause and clear the failed task — rerunning is safe if the load replaces yesterday's partition rather than appending. If the data never left Pub/Sub, it is still there only within retention: unacknowledged messages for seven days by default; acknowledged ones only if the subscription retains acked messages or the topic has retention, in which case you can **seek** a subscription to a timestamp and replay through a batch run of the same Beam code. The raw archive in Cloud Storage is what makes this routine rather than lucky.
**Prevent.** Retries with backoff on transient tasks; a data-completeness check (row counts or the Dataflow watermark per hour) before the load task runs, so the DAG waits for complete data rather than the clock; alerts on task failure and on freshness; and idempotent tasks keyed by run date so "clear and rerun" is always the answer.
The answer most people give
"Rerun the DAG." If the load appends, the rows that did arrive are loaded twice; if the data is gone from Pub/Sub, rerunning loads the same half again.
They’ll ask next
The subscription does not retain acknowledged messages and there is no archive. What can you still recover, and what do you change so this never depends on luck?
About 5 million artists check a dashboard of their daily stream counts, peaking at 20,000 page loads a second with a sub-second target, and counts update every few minutes. BigQuery is too slow and expensive for per-page queries. Which store do you serve from — Bigtable, Spanner or Cloud SQL — and why?
Why they ask this
Guides to Google's loop place Bigtable as the low-latency lookup store, and the Spotify-style prompt makes the choice concrete. It tests choosing a database by access pattern and scale, with numbers.
Say this
Bigtable: every page load is a lookup of one artist's recent days, which is a row-key range scan it serves in milliseconds at this throughput, and Dataflow can write updated counts every few minutes. Spanner would work but is priced for relational transactions this workload does not need; Cloud SQL would struggle with 20,000 reads a second over billions of rows; BigQuery stays the source for analysis.
The reasoning
**Why not BigQuery.** Each query has seconds of latency, concurrency limits, and on-demand billing with a 10 MB minimum per table referenced — 20,000 page loads a second is over a billion queries a day. BigQuery is where the counts are computed and audited, not served.
**Why Bigtable.** Design the row key as `artist_id#date` (or reversed date for newest-first), with columns for streams, listeners and countries. A dashboard reads one short key range. Bigtable handles this rate with low single-digit-millisecond reads when nodes are sized for it, and its write path absorbs the whole refresh every few minutes from Dataflow or a batch export. Avoid keys starting with the date: every write would hit one node.
**The alternatives.** Spanner is right if the page also needs relational queries or transactions — say, payment statements joined to contracts — and is more expensive per operation. Cloud SQL is fine for thousands of artists, but 5 million artists × 365 days is about 1.8 billion rows a year, and 20,000 reads a second needs read replicas and careful indexing. A cache such as Memorystore can sit in front of any of them for the hottest artists.
The answer most people give
"Use BigQuery with BI Engine." BI Engine accelerates dashboards for analysts; it is not designed as the backend for millions of external users each making many requests a second.
They’ll ask next
A handful of global stars get most of the traffic. Does that create a Bigtable hotspot, and what do you do?
About 40 partner files land in Cloud Storage each day, and last week a file with two swapped columns loaded cleanly and revenue was wrong for three days. How do you ensure data quality before loading into BigQuery?
Why they ask this
Deloitte's managerial round is reported to ask how you ensure data quality before loading into BigQuery. The swapped-column case is the kind that passes every schema check, which is why it is worth asking.
Say this
Load each file into a staging table, run checks there — schema and header, row count against the partner's manifest, value ranges and distributions against recent days, referential checks — and publish to the production table only if they pass; failures go to quarantine with an alert. Swapped columns of the same type are caught by distribution checks, not by schema.
The reasoning
**Why it slipped through.** Two columns of the same type — say `unit_price` and `quantity`, both numeric — swapped, so the schema matched, the load succeeded and nothing errored. Only the values were wrong. So schema validation is necessary and not enough.
**The gate.** Land the file, load it into `staging.partner_x` for that date, then run checks in SQL: header names match the contract (not just the count); row count matches the manifest; nulls, ranges and distributions — mean, min, max, distinct count per column — within bounds learned from the last 30 days (a price column whose mean jumps from 40 to 3 fails); keys exist in the dimensions. BigQuery scripting's `ASSERT` statement fails the job with a message when a condition is false, which a Composer task turns into a failed run. Dataplex data quality scans can run the same rules on a schedule.
**Publish or quarantine.** Only a passing file is merged into production (or the partition swapped in). A failing one moves to a quarantine prefix, the partner is told, and downstream dashboards keep yesterday's complete data rather than today's wrong data. Log every check result so you can show when a problem was caught.
The formulations
Assert on staging before publishingship
ASSERT (
SELECT AVG(unit_price) BETWEEN 20 AND 80
FROM staging.partner_x WHERE load_date = '2026-09-14'
) AS 'unit_price mean out of range: possible column swap'
Catches wrong values that pass the schema, and the failure message says what to look at.
Rely on the load job's schema checkavoid
-- load succeeds: both columns are NUMERIC
LOAD DATA INTO sales.orders FROM FILES (format = 'CSV', uris = ['gs://landing/x.csv'])
Schema validation passed on the file that broke revenue for three days.
The answer most people give
"We validate the schema, so bad data cannot get in." The swapped file had a valid schema. Quality checks have to look at values and compare them with what normal looks like.
They’ll ask next
A partner legitimately doubles prices after a currency change and your check blocks their file. How do you avoid false alarms without weakening the gate?
About 25 TB of raw logs land in Cloud Storage each month. They are read heavily for 30 days, occasionally for a year, never modified, and must be kept for seven years for audit. How do you implement lifecycle policies in Cloud Storage?
Why they ask this
Deloitte's published list asks how you implement lifecycle policies in GCS. With numbers, it becomes a question about minimum storage durations and early-deletion charges, which is where lifecycle rules go wrong.
Say this
Lifecycle rules on the bucket: Standard for the first 30 days, then Nearline, Coldline from about 90 days, Archive from 365, and delete at seven years, with a retention policy so nothing is deleted early. Each transition respects the target class's minimum duration, and I would check that files are large enough that per-object transition charges do not dominate.
The reasoning
**The rules.** `SetStorageClass` to Nearline at age 30 days, to Coldline at 90, to Archive at 365, and `Delete` at 2,555 days (seven years). Objects are never modified, so age is a clean signal. Minimum storage durations — 30 days for Nearline, 90 for Coldline, 365 for Archive — are counted from when the object was created, so transitions timed like this avoid early-deletion charges; an object deleted from Archive before a year is billed for the full year.
**Audit.** A **retention policy** of seven years on the bucket prevents deletion or overwrite before then, and **bucket lock** makes the policy itself irreversible when regulators require it. Lifecycle deletion then does the clean-up at the end. By year seven the bucket holds about 2.1 PB, almost all of it in Archive.
**Checks before shipping.** Class transitions are billed as operations per object, so millions of tiny log files cost more to tier than their bytes suggest — compact them into larger daily objects first. Reads from colder classes carry retrieval fees, so a yearly backfill over Coldline data has a real cost. If access is unpredictable, Autoclass can manage classes automatically instead of fixed ages.
Cheapest storage, but the heavy reads in the first 30 days pay Archive retrieval fees on 25 TB a month.
The answer most people give
"Move everything to Archive after a week to save money." The data is still read heavily for a month and occasionally for a year; retrieval fees and minimum durations can make that dearer than Standard.
They’ll ask next
Legal asks you to delete one customer's logs under GDPR, but the bucket is locked with a seven-year retention policy. What now?
What are the best practices to reduce BigQuery costs?
Why they ask this
It is on Deloitte's published list and nearly every compiled BigQuery list, and interviewers use it to hear whether you think about query cost, storage cost and pricing model separately.
Say this
Scan less: select only needed columns, filter on partition and cluster columns, and pre-aggregate what dashboards read repeatedly. Then guard and choose: require partition filters, cap bytes billed on scheduled jobs, set quotas, and pick on-demand or editions based on measured slot usage — and let storage age into long-term pricing.
The reasoning
**Query cost.** Avoid `SELECT *`; filter on partitioning columns with constants; cluster on common filters; use materialized views or small aggregate tables for dashboards; dry-run expensive queries; and find the top spenders in `INFORMATION_SCHEMA.JOBS` before optimising anything.
**Guardrails.** `require_partition_filter` on large tables, `maximum_bytes_billed` on scheduled and tool-driven jobs, custom daily quotas per user or project, and budget alerts.
**Pricing model and storage.** On-demand (US list $6.25 per TiB) suits spiky use; editions with autoscaling suit steady workloads measured in slot-hours. Tables or partitions untouched for 90 days drop to long-term storage at about half price automatically; partition expiration and shorter time-travel windows reduce storage further where retention rules allow.
The answer most people give
"Use LIMIT to reduce cost." LIMIT does not reduce the bytes scanned on an unclustered table, so it does not reduce the on-demand bill.
They’ll ask next
Which of these would you do first on a project you have never seen, and why?
How do you load data into BigQuery? Describe the different methods and when you would pick each.
Why they ask this
It appears on almost every compiled BigQuery list. It checks that you know the free path, the streaming paths and the managed transfer options, and the cost of each.
Say this
Batch load jobs from Cloud Storage (CSV, JSON, Avro, Parquet, ORC) are free on the shared slot pool and the default for files; the Storage Write API streams rows with seconds of latency; the Data Transfer Service and Datastream handle SaaS sources and database CDC; and `INSERT … SELECT` or external tables cover data already in reach.
The reasoning
**Load jobs**: `bq load`, the API, or `LOAD DATA` in SQL, from Cloud Storage or a local file. No ingestion charge on the shared pool, atomic per job, and a `WRITE_TRUNCATE` to a partition makes reruns safe. Prefer Avro or Parquet: they carry their schema and load in parallel.
**Streaming**: the Storage Write API ($0.025 per GiB list, first 2 TiB a month free) for rows that must be queryable within seconds, with at-least-once on the default stream and exactly-once with committed streams and offsets. The legacy `insertAll` API still works but costs more. Dataflow and Pub/Sub BigQuery subscriptions use the Storage Write API underneath.
**Managed and in-place**: BigQuery Data Transfer Service for scheduled imports (Google Ads, other clouds' storage, scheduled queries), Datastream for CDC from databases, federated queries to Cloud SQL, and external or BigLake tables to query files without loading.
The answer most people give
"Stream everything so data is always fresh." Streaming adds per-byte cost and a de-duplication problem; for data that arrives in files, a load job is free and simpler.
They’ll ask next
A daily 800 GB gzip CSV load takes hours. What do you change?
How do you handle task failures and retries in Airflow on Cloud Composer?
Why they ask this
It is on Deloitte's published Cloud Composer list. The interviewer wants the settings and, more importantly, whether your tasks are safe to retry.
Say this
Set `retries`, `retry_delay` and `retry_exponential_backoff` in the DAG's `default_args`, add `execution_timeout` so hung tasks fail, and alert through `on_failure_callback` or Cloud Monitoring. None of it helps unless each task is idempotent for its run date, so a retry repeats the work instead of duplicating it.
The reasoning
**Settings.** `retries=3`, `retry_delay=timedelta(minutes=5)` and `retry_exponential_backoff=True` for transient errors such as quota limits and network blips; `execution_timeout` so a stuck task fails instead of holding a worker; trigger rules (`all_done` for clean-up tasks) so tear-down runs after failures; and `sla` or deadline alerts for tasks that must finish by a time.
**Alerting.** `on_failure_callback` to post to chat or paging, plus Cloud Monitoring alerts on Composer's task-failure and DAG-run metrics, so a failure at 03:00 is seen before the business day.
**Idempotence.** Write outputs keyed by the run's logical date (overwrite a partition, `MERGE` on a key), give external jobs deterministic ids, and never append in a task that may retry. Then recovering from any failure is clearing the task and letting it run again.
The answer most people give
"Set retries to 5 and the problem goes away." Retries fix transient errors; a task that appends data or starts a second Dataproc job on each attempt turns one failure into duplicated data.
They’ll ask next
A task times out after the BigQuery job it started has actually finished. What happens on retry, and how do you make it safe?
EvergreenReported · 3Bigtable vs Spanner vs Cloud SQL
What is the difference between BigQuery and Bigtable, and when would you use each?
Why they ask this
The names are similar and the services are opposites, which is why the question is on so many compiled lists. It checks that you choose by workload.
Say this
BigQuery is a serverless analytical warehouse for SQL scans and aggregations over large data, with seconds of latency. Bigtable is a NoSQL wide-column database for very high-throughput reads and writes by row key with millisecond latency — the store an application serves from, not the one analysts query.
The reasoning
**BigQuery**: columnar, SQL, joins, billed per TiB scanned or per slot-hour, great for "revenue by country last quarter", poor for thousands of single-row lookups a second.
**Bigtable**: rows sorted by one key, column families, single-row transactions, provisioned nodes, sustaining very high write rates with low-latency reads — time series, IoT, user features for ML serving, per-entity counters. No SQL joins across tables in the relational sense; the row-key design is the whole performance story.
They often appear together: Dataflow writes events to Bigtable for serving and to BigQuery for analysis, and BigQuery can query Bigtable as an external source for occasional analysis.
The answer most people give
"Bigtable is the bigger version of BigQuery for larger data." They are different kinds of system; the choice is about access pattern, not size.
They’ll ask next
An ML team wants features computed daily in BigQuery served to a model at 10 ms latency. Where do the features live?
How do you secure sensitive information in a GCP data pipeline?
Why they ask this
It is on several compiled GCP lists and underpins every PII design question. The interviewer listens for layers: identity, data-level controls, encryption and perimeter.
Say this
Least-privilege IAM with a service account per pipeline; data-level controls in BigQuery — policy tags for column-level security and dynamic masking, row-level access policies, authorised views; encryption with customer-managed keys where required; Sensitive Data Protection (DLP) to find and de-identify PII; and VPC Service Controls around the projects.
The reasoning
**Identity.** Each pipeline runs as its own service account with only the roles it needs on the datasets and buckets it touches; humans get groups, not individual grants; no service-account keys where workload identity or impersonation will do.
**Data-level.** In BigQuery, tag PII columns with policy tags so only the fine-grained reader role sees them, apply dynamic data masking for everyone else, add row-level access policies where teams see only their region, and share curated data through authorised views or datasets. In Cloud Storage, uniform bucket-level access with IAM only.
**Encryption, discovery, perimeter.** Data is encrypted at rest by default; Cloud KMS customer-managed keys where policy requires control. Sensitive Data Protection (formerly Cloud DLP) scans for PII and can tokenise it in the pipeline. VPC Service Controls stop exfiltration by a valid identity, and data-access audit logs record who read what.
The answer most people give
"GCP encrypts everything by default, so it is secure." Encryption at rest protects the disks; it does nothing about a service account with project-wide BigQuery access reading every column.
They’ll ask next
Analysts need to join on email but must never see it. How do you give them that?
