Exploring and profiling data

On Tuesday, October 13, Mateo brings a question to the session. The store systems team has sent the first week of till exports to the landing bucket, gs://bl-data-landing/pos/, and he wants to load them. Ravi asks him to wait. "What is in the files?" he asks. Mateo knows the columns from the data contract, but he does not know how many rows are empty, whether any store sends negative quantities, or whether the timestamps really are local time. Finding out is called profiling, and it comes before cleaning and loading.

What profiling is for

Data profiling is the systematic inspection of a dataset to learn its shape and its problems. It answers practical questions: how many rows are there, which columns are often empty, how many distinct values does each column have, what are the minimum and maximum, which values are most common, and do the values follow the expected pattern? The answers tell you which cleaning rules you need, which assumptions in the data contract are wrong, and which quality checks to add later.

Profiling is also how you avoid surprises in front of an audience. Ravi remembers a sales dashboard at his previous job that showed a spectacular week, which turned out to be one store's till sending every transaction twice. A five-minute profile would have shown that one store's row count had doubled overnight.

Exploring files before loading them

The files are still in Cloud Storage, and there are three ways to look at them without loading them into a permanent table. The simplest is to download a sample and open it, which works for small files but does not scale. The second is to create an external table in BigQuery that points at the files: BigQuery reads the files where they sit each time it is queried, so Mateo can run SQL over them straight away. The third is to load the files into a scratch dataset with a table expiration, so the tables delete themselves after a few days.

bq mk --external_table_definition=@CSV=gs://bl-data-landing/pos/2026/10/*.csv \
  bl-data-prod:scratch.pos_explore

# Or, with an explicit schema file:
bq mk --external_table_definition=pos_sales_schema.json@CSV=gs://bl-data-landing/pos/2026/10/*.csv \
  bl-data-prod:scratch.pos_explore

External tables are excellent for exploration but slower and more limited than native tables for regular use: BigQuery cannot cache results from them in the same way, and some table features are not available. That is a theme of lesson 6. For now, the external table lets Mateo run profiling queries over seven days of files in seconds.

Profiling with SQL

A handful of SQL patterns cover most profiling. Ravi encourages Mateo to keep them in a saved query he can reuse for every new feed.

SELECT
  COUNT(*)                                   AS row_count,
  COUNT(DISTINCT store_id)                   AS stores,
  COUNTIF(tender IS NULL OR tender = '')     AS tender_missing,
  COUNTIF(qty < 0)                           AS negative_qty,
  COUNTIF(unit_price = 0)                    AS zero_price,
  MIN(unit_price) AS min_price, MAX(unit_price) AS max_price,
  APPROX_COUNT_DISTINCT(txn_id)              AS approx_txn_ids,
  APPROX_QUANTILES(qty * unit_price, 4)      AS line_value_quartiles
FROM `bl-data-prod.scratch.pos_explore`;

Two functions in that query are worth knowing for the exam. APPROX_COUNT_DISTINCT gives a close estimate of the number of distinct values much more cheaply than an exact COUNT(DISTINCT) on very large tables. APPROX_QUANTILES returns values that split the data into equal parts; asking for 4 gives the minimum, the three quartiles and the maximum, which is a quick way to see the spread and spot outliers. For row counts per store per day, a GROUP BY on store_id and the date does the job, and sorting by the count shows the stores that sent nothing or twice as much as usual.

Profiling in the console and in notebooks

BigQuery and its related tools can profile data without hand-written SQL. Mateo tries each one on the week of till data.

A data profile scan is especially useful because it can run on a schedule and keep a history, so the team can see when a column's null rate creeps up over weeks. Data profiling and data quality scans belong to Dataplex, which Google now presents as Dataplex Universal Catalog. Lesson 10 uses its sibling, the data quality scan, to turn profile findings into rules that fail when data goes wrong.

Profiling queries cost money like any other query, because BigQuery's on-demand pricing charges for the bytes a query reads. Before running a query on a large table, Mateo checks the estimate the query editor shows, or runs it as a dry run, which reports the bytes that would be processed without running it. On the warehouse history, a profile that reads every column of 1.2 TB would be far more expensive than one that reads only the five columns he is interested in. Because BigQuery stores data by column, selecting fewer columns is the main way to reduce cost. A LIMIT clause, by contrast, does not reduce the bytes read on a normal table.

bq query --use_legacy_sql=false --dry_run \
  'SELECT store_id, qty, unit_price FROM `bl-data-prod.raw.history_sales`'
# Query successfully validated. This query will process 61,442,108,416 bytes of data.

A quick profile in Python

Sometimes it is quicker to profile a sample in a notebook. Mateo takes forty rows from one store's file and writes a small profile to see what kinds of problems to look for in the full data.

import csv, io
from collections import Counter

sample = """store_id,txn_id,txn_ts,sku,qty,unit_price,discount,tender
012,T5501,2026-10-07 09:14:03,TNT-2P-GRN,1,289.00,,CARD
012,T5502,2026-10-07 09:20:41,stk-al-08,2,14.50,,CASH
012,T5503,2026-10-07 09:31:10,SLP-BAG-0C,1,159.99,15.00,CARD
012,T5503,2026-10-07 09:31:10,SLP-BAG-0C,1,159.99,15.00,CARD
012,T5504,2026-10-07 10:02:55,HDL-LMP-01,1,0.00,,
012,T5505,2026-10-07 10:15:20,TNT-2P-GRN,-1,289.00,,CARD
012,T5506,2026-10-07 10:40:09,BTL-1L-BLU,3,19.95,,card
"""
rows = list(csv.DictReader(io.StringIO(sample)))
print("rows:", len(rows))
for col in rows[0]:
    values = [r[col] for r in rows]
    empty = sum(1 for v in values if v.strip() == "")
    print(f"{col:11s} empty={empty} distinct={len(set(values))} top={Counter(values).most_common(1)[0]}")

dupes = [k for k, n in Counter(tuple(r.values()) for r in rows).items() if n > 1]
print("exact duplicate rows:", len(dupes))
print("negative qty rows:", sum(1 for r in rows if int(r["qty"]) < 0))
print("zero price rows:", sum(1 for r in rows if float(r["unit_price"]) == 0))
print("tender values:", sorted(set(r["tender"] for r in rows)))

rows: 7
store_id    empty=0 distinct=1 top=('012', 7)
txn_id      empty=0 distinct=6 top=('T5503', 2)
txn_ts      empty=0 distinct=6 top=('2026-10-07 09:31:10', 2)
sku         empty=0 distinct=5 top=('TNT-2P-GRN', 2)
qty         empty=0 distinct=4 top=('1', 4)
unit_price  empty=0 distinct=5 top=('289.00', 2)
discount    empty=5 distinct=2 top=('', 5)
tender      empty=1 distinct=4 top=('CARD', 4)
exact duplicate rows: 1
negative qty rows: 1
zero price rows: 1
tender values: ['', 'CARD', 'CASH', 'card']

Seven rows are enough to reveal four issues that turn out to be real in the full week: an exact duplicate line, a negative quantity, a zero price with no tender, and the tender value written both as CARD and card. One SKU is in lowercase. The sample also shows that txn_id is shared by the lines of the same basket, so it is not a unique row identifier on its own. None of these is surprising to someone who has worked with till data, but each one needs a decision, and lesson 4 makes them.

For very large tables, profiling every row is rarely necessary. BigQuery's TABLESAMPLE SYSTEM clause reads a random selection of storage blocks, for example about 10 in every 100, and bills only for the blocks it reads. A sample is good for spotting patterns, typical values and the shape of a distribution. It is poor for finding rare problems: a sample can easily miss the single store that sent no rows, so counts of missing files and duplicates should always run over the full day's data, which for Birchline's till files is small anyway.

Reading the findings with the business

A profile reports what the data looks like; it does not say what the data means. Mateo takes his findings to Nadia Brooks, who manages the store systems team, and they go through them together. Several turn out to be legitimate. Negative quantities are refunds, which the tills record as the original sale's SKU with a negative quantity. Zero prices with no tender are free promotional items, such as headlamps given away with a tent. The duplicate lines are a real bug: when a till loses its network connection, it resends the last batch of lines on reconnecting. And the lowercase SKUs come from one store that still enters some items by hand.

This step is the heart of good data preparation. Without the conversation with Nadia, Mateo might have deleted refunds as "bad data", which would have overstated sales by the value of every returned item. With it, each rule has a reason that can be written down and defended.

Profiling a semi-structured source

The returns export is JSON, and profiling it raises different questions: which fields are present in every record, which are sometimes missing, and how long the item lists are. BigQuery can load a sample into a scratch table with auto-detection, and a query over that table shows how often each field is present. Mateo finds that about one record in fifty has no reason_code, and that the items array sometimes holds the same SKU twice instead of a quantity of two. Both go on the list for the cleaning lesson.