BigQuery Best Practices for Analysts

An analyst using BigQuery can fail in three ways. The number can be wrong. The query can be far more expensive than it needed to be. Or the result can be impossible for anyone, including the analyst, to reproduce next month. The first is the worst, because nothing announces it. BigQuery will return a confident, neatly formatted, incorrect total without complaint. The habits below are grouped by the failure they prevent.

Before writing: look at the table

Spend a minute on the table before querying it.

• Read the schema and the details. Note which column the table is partitioned by. That column belongs in almost every WHERE clause you will write.
• Preview, do not query. The preview tab shows rows at no charge. A SELECT * with a LIMIT still reads, and bills, the whole table unless the table is clustered (Google Cloud: estimate and control costs).
• Ask what one row represents. An order, an order line, an event? Every total you compute depends on the answer.

Correctness: four checks on real data

The script runs four checks on the New York yellow taxi trips for January 2024 and the zone lookup table published with them (NYC TLC: trip record data). The queries are GoogleSQL, parsed with the BigQuery grammar of the sqlglot library, translated to DuckDB and run locally. It is not a run of BigQuery.

# Four correctness checks an analyst should run, in GoogleSQL on real trip data. Each query is parsed
# with sqlglot's BigQuery grammar, translated to DuckDB and run locally. It is not a run of BigQuery.
import duckdb
import sqlglot

con = duckdb.connect()
con.execute("SET TimeZone = 'UTC'")
con.execute("CREATE VIEW trips AS SELECT * FROM read_parquet('../../dedata/yellow_tripdata_2024-01.parquet')")
con.execute("CREATE TABLE zones AS SELECT * FROM read_csv_auto('../../dedata/taxi_zone_lookup.csv')")


def bq(sql):
    return con.execute(sqlglot.transpile(sql, read="bigquery", write="duckdb")[0]).fetchall()


# 1. Profile a column before using it.
total, filled, empty = bq("""
SELECT COUNT(*), COUNT(passenger_count), COUNTIF(passenger_count IS NULL)
FROM trips""")[0]
print(f"1. rows {total:,}; passenger_count present {filled:,}; NULL {empty:,} ({empty / total:.1%})")

# 2. NULL is not a value: two opposite filters do not add up to the table.
one, not_one, average, per_row = bq("""
SELECT COUNTIF(passenger_count = 1),
       COUNTIF(passenger_count <> 1),
       ROUND(AVG(passenger_count), 3),
       ROUND(SUM(passenger_count) / COUNT(*), 3)
FROM trips""")[0]
print(f"2. = 1: {one:,}; <> 1: {not_one:,}; together {one + not_one:,}; missing from both {total - one - not_one:,}")
print(f"   AVG(passenger_count) {average}; SUM / COUNT(*) {per_row}")

# 3. A day depends on the time zone. Pickup times are read as UTC here, for illustration.
print("3. trips per day      as UTC   as Asia/Kolkata")
by_zone = {}
for label, expression in [("utc", "DATE(TIMESTAMP(tpep_pickup_datetime))"),
                          ("ist", "DATE(TIMESTAMP(tpep_pickup_datetime), 'Asia/Kolkata')")]:
    by_zone[label] = dict(bq(f"""
        SELECT {expression} AS day, COUNT(*)
        FROM trips
        WHERE {expression} BETWEEN '2024-01-14' AND '2024-01-16'
        GROUP BY day"""))
for day in sorted(by_zone["utc"]):
    print(f"   {day}    {by_zone['utc'][day]:10,} {by_zone['ist'][day]:17,}")

# 4. Check that a join key is unique before joining on it.
row_count, ids, names = bq("SELECT COUNT(*), COUNT(DISTINCT LocationID), COUNT(DISTINCT Zone) FROM zones")[0]
print(f"4. zones: {row_count} rows, {ids} distinct ids, {names} distinct names")
by_id = bq("SELECT COUNT(*) FROM trips AS t JOIN zones AS z ON z.LocationID = t.PULocationID")[0][0]
by_name = bq("""
SELECT COUNT(*)
FROM trips AS t
JOIN zones AS z ON z.LocationID = t.PULocationID
JOIN zones AS lookup ON lookup.Zone = z.Zone""")[0][0]
print(f"   rows after joining on the id {by_id:,}; after a further join on the zone name {by_name:,} (+{by_name - by_id:,})")

1. rows 2,964,624; passenger_count present 2,824,462; NULL 140,162 (4.7%)
2. = 1: 2,188,739; <> 1: 635,723; together 2,824,462; missing from both 140,162
   AVG(passenger_count) 1.339; SUM / COUNT(*) 1.276
3. trips per day      as UTC   as Asia/Kolkata
   2024-01-14        94,420           103,750
   2024-01-15        77,033            82,910
   2024-01-16        93,057            84,934
4. zones: 265 rows, 265 distinct ids, 262 distinct names
   rows after joining on the id 2,964,624; after a further join on the zone name 2,964,906 (+282)

Check 1: profile the column

COUNT(*) counts rows. COUNT(column) counts rows where the column is not NULL (Google Cloud: aggregate functions). The difference tells you how much of a column is missing. Here 140,162 trips, 4.7%, have no passenger count. Any statement about passengers silently describes only the other 95.3%.

Check 2: NULL is not a value

Two filters that look like opposites, "equals 1" and "does not equal 1", should cover the table. They do not. Together they account for 2,824,462 rows, and 140,162 are in neither.

The rule is in the documentation: an operator returns NULL when one of its operands is NULL (Google Cloud: operators). NULL <> 1 is not true, so the row is dropped. The same rule makes averages ambiguous. AVG uses only the non-NULL values and reports 1.339 passengers per trip; dividing the sum by all rows gives 1.276. Neither is wrong. They answer different questions, and you must know which you are reporting.

Say what you mean explicitly:

SELECT COUNTIF(passenger_count = 1)                              AS one,
       COUNTIF(passenger_count <> 1)                             AS other,
       COUNTIF(passenger_count IS NULL)                          AS unknown,
       COUNTIF(passenger_count <> 1 OR passenger_count IS NULL)  AS not_known_to_be_one
FROM analytics.trips;

Check 3: a day depends on the time zone

A TIMESTAMP in BigQuery is an absolute instant with no time zone of its own (Google Cloud: data types). Turning it into a date requires one, and if you do not give one, UTC is used (Google Cloud: date functions).

The third block cuts the same rows into days twice. Reading the pickup times as UTC, 15 January has 77,033 trips. Cut in Asia/Kolkata, five and a half hours ahead, it has 82,910. The rows are identical; the day boundary moved. A daily report that disagrees with the finance team's by a few percent is very often this.

State the time zone every time:

SELECT DATE(pickup_at, 'Asia/Kolkata') AS business_day, COUNT(*) AS trips
FROM analytics.trips
GROUP BY business_day;

Check 4: is the join key unique?

The zone lookup has 265 rows and 265 distinct ids, so joining trips to it on the id is safe: 2,964,624 rows in, 2,964,624 out. It has only 262 distinct names, because two zones are both called Corona and three share one long name. A further join on the name returns 2,964,906 rows. The join invented 282 trips.

Nothing fails when this happens. The counts and sums are simply too high. Before joining on any column, run the check, and after joining, compare row counts:

SELECT COUNT(*) AS row_count, COUNT(DISTINCT zone_name) AS distinct_keys
FROM reference.zones;

If the two numbers differ, the key is not unique, and the join will multiply rows. Google's guidance says the same from the performance side: avoid joins that produce more rows than they take in (Google Cloud: optimize query computation).

Cost: habits that take no effort

• Read the estimate before you run. The editor shows the bytes a query will process. Glance at it every time, and stop when it is larger than you expected.
• Name your columns and filter the partition. These two habits decide most of what a query costs. BigQuery cost optimization works through the numbers.
• Set a maximum bytes billed in your query settings. A query whose estimate exceeds it fails without charge.
• Let the cache work. Results are kept for about 24 hours, and an identical query is answered from the cache at no charge (Google Cloud: cached query results). Identical means identical text; a changed comment or space is a different query. A query that calls CURRENT_TIMESTAMP() or CURRENT_DATE() is never cached, so use a literal date while you iterate.
• Build on a small result. When exploring, save one filtered, aggregated table and work from that instead of going back to the raw table for every question.

Reproducibility and sharing

• Do not share from the cache. Cached results live in a hidden dataset that only you can read. To share a result, write it to a named table.
• Give scratch tables an expiration. A default table expiration on your working dataset removes old results automatically, so they do not accumulate as storage cost.
• Keep the query with the number. Save the query, with a comment stating what one row represents and which time zone applies. A figure without its query cannot be checked.
• Replace relative dates with fixed ones in anything you publish. "Last 30 days" is a different set of rows tomorrow.
• Put shared logic in a view. A definition of "active customer" written once is better than five slightly different versions in five reports.

CREATE OR REPLACE TABLE scratch.jan_trips_by_zone
OPTIONS (expiration_timestamp = TIMESTAMP '2026-11-01 00:00:00 UTC')
AS
SELECT pickup_zone, COUNT(*) AS trips
FROM analytics.trips
WHERE pickup_at >= '2024-01-01' AND pickup_at < '2024-02-01'
GROUP BY pickup_zone;

A checklist to keep by the editor

1. What does one row represent?
2. How many NULL values are in the columns I filter and average?
3. Which time zone defines a day in this report?
4. Is every join key unique on the side I expect?
5. Do the row counts before and after each join make sense?
6. Did I read the estimate?
7. Can someone else rerun this and get the same number?

For the SQL itself, see BigQuery SQL tips and window functions. For arrays, where row counts change by design, see nested and repeated fields.

Analytical SQL and warehouse practice are covered in the Data Engineering course.

Sources

• Estimate and control costs (Google Cloud Documentation) — Google Cloud
• Use cached query results (Google Cloud Documentation) — Google Cloud
• Operators (Google Cloud Documentation) — Google Cloud
• Aggregate functions (Google Cloud Documentation) — Google Cloud
• Data types (Google Cloud Documentation) — Google Cloud
• Date functions (Google Cloud Documentation) — Google Cloud
• Optimize query computation (Google Cloud Documentation) — Google Cloud
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission