BigQuery Interview Questions and Answers

BigQuery interview questions are half SQL and half money. The SQL part tests GoogleSQL features such as QUALIFY, arrays and approximate aggregation; the money part tests whether you know what a query will be billed and how table design changes that. Here each answer combines Google's documentation with GoogleSQL run on real New York taxi data (NYC TLC: trip record data). One honest caveat: the queries ran locally, translated to DuckDB, not on BigQuery, so the SQL behaviour carries over but the byte counts and approximate results are DuckDB's and Parquet's, not BigQuery's.

import duckdb
import pyarrow.parquet as pq
import sqlglot

TRIPS = "yellow_tripdata_2024-01.parquet"                       # NYC TLC yellow taxi trips, January 2024
db = duckdb.connect()
db.execute(f"CREATE TABLE trips AS SELECT * FROM '{TRIPS}' WHERE tpep_pickup_datetime >= '2024-01-01' AND tpep_pickup_datetime < '2024-02-01'")
db.execute("CREATE TABLE zones AS SELECT * FROM 'taxi_zone_lookup.csv'")


def bigquery(sql):                                              # GoogleSQL in, run on DuckDB (not on BigQuery)
    return db.execute(sqlglot.transpile(sql, read="bigquery", write="duckdb")[0]).fetchall()


print("B1 bytes per column: why SELECT * costs more in a columnar store")
meta = pq.ParquetFile(TRIPS).metadata
size = {}
for g in range(meta.num_row_groups):
    for c in range(meta.num_columns):
        col = meta.row_group(g).column(c)
        size[col.path_in_schema] = size.get(col.path_in_schema, 0) + col.total_uncompressed_size
needed = ["tpep_pickup_datetime", "PULocationID", "total_amount"]
print(f"   {len(size)} columns; 3 needed columns are {sum(size[c] for c in needed) / sum(size.values()):.0%} of the uncompressed bytes")

print("\nB2 APPROX_COUNT_DISTINCT vs COUNT(DISTINCT): pickup-dropoff routes")
exact, approx = bigquery("""SELECT COUNT(DISTINCT CONCAT(CAST(PULocationID AS STRING), '-', CAST(DOLocationID AS STRING))),
                                   APPROX_COUNT_DISTINCT(CONCAT(CAST(PULocationID AS STRING), '-', CAST(DOLocationID AS STRING)))
                            FROM trips""")[0]
print(f"   exact {exact:,} | approximate {approx:,} | error {abs(approx - exact) / exact:.2%}")

print("\nB3 QUALIFY: the busiest drop-off zone for each pickup borough")
for row in bigquery("""
    SELECT z.Borough, d.Zone AS dropoff_zone, COUNT(*) AS trips
    FROM trips t JOIN zones z ON z.LocationID = t.PULocationID JOIN zones d ON d.LocationID = t.DOLocationID
    WHERE z.Borough IN ('Manhattan', 'Queens', 'Brooklyn')
    GROUP BY z.Borough, d.Zone
    QUALIFY ROW_NUMBER() OVER (PARTITION BY z.Borough ORDER BY COUNT(*) DESC) = 1
    ORDER BY z.Borough"""):
    print("  ", *row)

print("\nB4 ARRAY_AGG and UNNEST: nested data and back")
borough, zone_list = bigquery("""SELECT Borough, ARRAY_AGG(Zone ORDER BY Zone) FROM zones
                                  WHERE Borough = 'Staten Island' GROUP BY Borough""")[0]
print(f"   one row for {borough}: an array of {len(zone_list)} zones, starting {zone_list[:3]}")
flat = bigquery("""SELECT COUNT(*) FROM (SELECT ARRAY_AGG(Zone) AS z FROM zones GROUP BY Borough) AS t, UNNEST(t.z) AS zone""")[0][0]
print(f"   UNNEST back to one row per zone: {flat} rows")

print("\nB5 on-demand cost of scanning the 3 needed columns for a year at 6.25 USD per TiB, after the free TiB")
year_tib = sum(size[c] for c in needed) * 12 / 2**40
print(f"   about {year_tib:.4f} TiB a year -> {max(0, year_tib - 1) * 6.25:.2f} USD if it were the only query that month")

B1 bytes per column: why SELECT * costs more in a columnar store
   19 columns; 3 needed columns are 40% of the uncompressed bytes

B2 APPROX_COUNT_DISTINCT vs COUNT(DISTINCT): pickup-dropoff routes
   exact 26,335 | approximate 24,933 | error 5.32%

B3 QUALIFY: the busiest drop-off zone for each pickup borough
   Brooklyn Crown Heights North 683
   Manhattan Upper East Side North 137411
   Queens Times Sq/Theatre District 12402

B4 ARRAY_AGG and UNNEST: nested data and back
   one row for Staten Island: an array of 20 zones, starting ['Arden Heights', 'Arrochar/Fort Wadsworth', 'Bloomfield/Emerson Hill']
   UNNEST back to one row per zone: 265 rows

B5 on-demand cost of scanning the 3 needed columns for a year at 6.25 USD per TiB, after the free TiB
   about 0.0003 TiB a year -> 0.00 USD if it were the only query that month

Q1. Why does SELECT * cost more in BigQuery?

On-demand queries are billed by bytes processed, 6.25 USD per TiB after the first free TiB each month (BigQuery: pricing), and BigQuery stores data by column, so it reads only the columns a query references. In the taxi file, the three columns a typical revenue query needs held 40% of the uncompressed bytes; SELECT * would read all 19 columns. Name the columns, and the bill falls with them. SELECT * EXCEPT(...) helps when you need almost every column.

Q2. Does LIMIT reduce the cost?

Not on a non-clustered table. Google's cost guidance says a LIMIT clause does not change the amount of data read there: you pay for scanning the columns in the whole table even if one row comes back. On a clustered table, LIMIT can reduce the bytes scanned, because scanning stops once enough blocks are read (BigQuery: control costs). To preview data cheaply, use the table preview instead. To cap spending, use a dry run, whose estimate is an upper bound, and the maximum bytes billed setting.

Q3. When would you use APPROX_COUNT_DISTINCT?

When an approximate answer is good enough and the exact one is expensive: distinct users or sessions over billions of rows. Approximate functions use far less memory and time than COUNT(DISTINCT), at the price of statistical error (BigQuery: approximate aggregates). On the taxi data, there were 26,335 distinct pickup-to-drop-off routes; DuckDB's approximate count returned 24,933, 5.3% off. BigQuery's implementation has its own precision, so its error will differ, but the principle holds. Say when an exact figure is required, for billing or finance, and never use the approximate one there.

Q4. What does QUALIFY do?

It filters on the result of a window function, the way HAVING filters on aggregates, without a subquery. Ranking drop-off zones within each pickup borough and keeping the top one gave: Brooklyn to Crown Heights North, Manhattan to Upper East Side North, and Queens to Times Square, the last reflecting airport trips into Manhattan. The same query in standard SQL needs a subquery around the window function.

Q5. How do arrays and UNNEST work?

ARRAY_AGG packs values into an array, giving nested data such as one row per Staten Island with its 20 zones. UNNEST turns an array back into rows: unnesting every borough's array gave all 265 zones. BigQuery favours nested, repeated fields because they avoid joins at query time; interviewers ask how you would query them, and the answer is almost always UNNEST in the FROM clause.

Q6. How would you design a large table for cost?

Partition by date, cluster by the columns most often filtered, and make queries filter on the partition column:

CREATE TABLE taxi.trips
PARTITION BY TIMESTAMP_TRUNC(tpep_pickup_datetime, MONTH)
CLUSTER BY PULocationID
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM taxi.raw_trips;

TIMESTAMP_TRUNC sets the partition granularity, and require_partition_filter rejects queries that would scan every partition (BigQuery: partitioned tables). The choice of monthly rather than daily partitions follows from the data's size: the sizing exercise in data engineering interview questions and answers found daily partitions of only a couple of megabytes for this dataset.

Q7. What else lowers storage and query cost?

Tables or partitions left unmodified for 90 days move to long-term storage at about half the price (BigQuery: pricing). Partitioning helps here too: old partitions age into the cheaper tier while recent ones stay active. Materialising intermediate results also helps when the same expensive transformation feeds many queries.

Q8. On-demand or capacity pricing?

BigQuery offers two compute models. On-demand charges for the bytes each query processes. Capacity pricing charges for slots, BigQuery's units of compute, by the slot-hour, whatever the bytes scanned (BigQuery: pricing). On-demand suits irregular or modest workloads; capacity suits steady, heavy ones where predictable spend matters. A good answer says which one the company uses before optimising anything, because bytes scanned matter directly under on-demand and slot time matters under capacity.

How to answer BigQuery questions

• Translate every design choice into bytes scanned.
• Know which features cut cost and which do not: column selection, partitions and clusters do; LIMIT on unclustered tables does not.
• Use GoogleSQL features fluently: QUALIFY, arrays, structs, approximate functions.

The service itself is explained in BigQuery explained, query tuning in BigQuery SQL tips and optimisation, and analytic SQL in BigQuery window functions. For the wider platform, see GCP interview questions for data engineers.

BigQuery is part of the Data Engineering course.

Sources

• BigQuery pricing (Google Cloud) — Google Cloud
• Estimate and control costs (BigQuery documentation) — Google Cloud
• Approximate aggregate functions (BigQuery documentation) — Google Cloud
• Create partitioned tables (BigQuery documentation) — Google Cloud
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission