Cleaning and transforming data

Thursday's session, on October 15, turns the profile findings into rules. Mateo arrives with the list he agreed with Nadia Brooks from the store systems team: remove the duplicate lines a till resends after a network drop, keep refunds but flag them, mark free promotional items, standardize the SKU and tender values, and convert local till times into proper timestamps. Ravi wants each rule written once, in a place everyone can read, and applied the same way every night.

Cleaning versus transforming

The two words are often used together, but they describe different work. Cleaning fixes problems in the data so that it says what it is supposed to say: removing duplicates, correcting formats, handling missing values, rejecting impossible values. Transforming changes the shape or meaning of correct data for a purpose: joining sales to the product catalog, converting currencies, aggregating to daily totals, or pivoting rows into columns. Cleaning usually happens first, in staging. Transformation into business-ready tables happens in the curated layer.

The common cleaning operations

Most cleaning falls into a small number of operations. Knowing them by name helps when reading exam scenarios, which often describe a symptom and ask for the operation that fixes it.

Writing the staging transformation

Because Birchline follows ELT, the raw till files are loaded unchanged into raw.pos_sales, with the till time in a DATETIME column, and staging is built from raw with SQL. Ravi and Mateo write the staging query together. Each block of the query does one of the agreed rules, and a comment says why.

CREATE OR REPLACE TABLE `bl-data-prod.staging.pos_sales` AS
WITH typed AS (
  SELECT
    r.store_id,
    r.txn_id,
    TIMESTAMP(r.txn_local, s.time_zone)             AS txn_ts,      -- local till time to a real moment
    UPPER(TRIM(r.sku))                              AS sku,         -- manual entry at one store
    r.qty,
    r.unit_price,
    COALESCE(r.discount, 0)                         AS discount,
    CASE WHEN r.unit_price = 0 THEN 'PROMO'
         ELSE UPPER(TRIM(r.tender)) END             AS tender,      -- free items have no tender
    r.qty < 0                                       AS is_refund,
    r.unit_price = 0                                AS is_promo,
    r.source_file
  FROM `bl-data-prod.raw.pos_sales` AS r
  JOIN `bl-data-prod.reference.stores` AS s USING (store_id)        -- unknown stores drop out here
)
SELECT * FROM typed
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY store_id, txn_id, txn_ts, sku, qty, unit_price
  ORDER BY source_file) = 1;                                        -- till resends after a network drop

Three details in the query are typical of good staging SQL. The QUALIFY clause filters on the result of a window function, so ROW_NUMBER() can number the copies of each line and keep only the first; this removes exact duplicates while keeping legitimate repeated purchases, which differ in their timestamp. The join to the store reference table both supplies the time zone and quietly drops any row from a store that does not exist; Ravi notes that silent dropping is dangerous, and lesson 10 adds a check that counts and reports such rows instead. And the boolean flags is_refund and is_promo keep the information analysts need without changing the original values.

Mateo asks why the query uses TIMESTAMP(txn_local, time_zone) instead of just storing local time. The answer is that analysts will ask questions such as "how many sales happened in the first hour after opening across all stores" and also "what happened nationally between 21:00 and 22:00 Eastern on Black Friday". The first needs local time, which can always be recovered from the timestamp and the store's zone; the second needs a single, comparable moment. Storing the true moment and the zone keeps both questions answerable.

The query rebuilds the whole staging table each time it runs. For a few million rows that is quick and cheap, and it has a valuable property: running it twice gives the same result as running it once. A job with that property is called idempotent, and it means a failed night can simply be re-run without creating extra rows. As the table grows, the team will switch to rebuilding only recent days, for example with a MERGE statement or by replacing one date partition at a time, but the same principle holds: re-running a day must never double it.

Failing safely: SAFE functions and error tables

An ordinary CAST stops the whole query if one value cannot be converted, which can be the right behavior for a nightly job that should never see bad values. More often, the better choice is to convert what can be converted and set the rest aside for review. BigQuery's SAFE_CAST and the SAFE. prefix on functions return NULL instead of an error.

SELECT
  return_id,
  SAFE_CAST(refund_amount AS NUMERIC)                    AS refund_amount,
  SAFE.PARSE_DATETIME('%Y-%m-%d %H:%M', returned_at_text) AS returned_at
FROM `bl-data-prod.raw.returns`;

-- Rows that could not be converted go to an error table for review
INSERT INTO `bl-data-prod.staging.returns_errors`
SELECT *, CURRENT_TIMESTAMP() AS caught_at
FROM `bl-data-prod.raw.returns`
WHERE SAFE_CAST(refund_amount AS NUMERIC) IS NULL
   OR SAFE.PARSE_DATETIME('%Y-%m-%d %H:%M', returned_at_text) IS NULL;

The error table is as important as the safe conversion. A NULL that nobody looks at is just a quieter way to lose data. Ravi's rule is that every staging job either produces zero error rows or produces a count that someone sees the next morning.

A cleaning function you can test

Some rules are easier to reason about outside SQL. Mateo writes the SKU and tender standardization as a small Python function so that he can test it against examples Nadia gave him, before translating it into the SQL above. Testing a rule on known cases first is a habit worth building.

import re

def clean_sku(raw):
    """Upper-case, trim, and turn spaces or underscores into hyphens."""
    s = raw.strip().upper()
    s = re.sub(r"[ _]+", "-", s)
    return s if re.fullmatch(r"[A-Z0-9]+(-[A-Z0-9]+)+", s) else None

def clean_tender(raw, unit_price):
    if unit_price == 0:
        return "PROMO"
    t = (raw or "").strip().upper()
    return t if t in {"CARD", "CASH", "GIFT"} else "UNKNOWN"

tests = [(" stk-al-08", "STK-AL-08"), ("TNT_2P_GRN", "TNT-2P-GRN"), ("btl 1l blu", "BTL-1L-BLU"), ("12345", None)]
for raw, expected in tests:
    got = clean_sku(raw)
    print(f"{raw!r:14} -> {got!r:14} {'ok' if got == expected else 'FAILED'}")

print(clean_tender("card", 19.95), clean_tender("", 0.0), clean_tender("voucher", 40.0))

' stk-al-08'   -> 'STK-AL-08'    ok
'TNT_2P_GRN'   -> 'TNT-2P-GRN'   ok
'btl 1l blu'   -> 'BTL-1L-BLU'   ok
'12345'        -> None           ok
CARD PROMO UNKNOWN

The last tender test is a good example of why rules need owners. One store recorded a paper voucher, which is not in the approved list. The function marks it UNKNOWN rather than guessing, and Mateo adds a question for Nadia: should VOUCHER become a valid tender, or should the store stop accepting them? Data cleaning keeps turning up business questions like this, and the practitioner's job is to surface them, not to answer them alone.

Tools for cleaning and transforming

SQL in BigQuery is the default for Birchline, but Google Cloud offers several ways to clean and transform data, and the exam expects you to know when each fits.

BigQuery data preparation deserves a closer look because it is designed for people in Mateo's position. It opens on a table, samples it, and uses Gemini in BigQuery to suggest steps as cards: standardize this date format, trim these strings, deduplicate on these columns, join to that table. Each card shows the SQL it would apply, and the user can accept, edit or reject it. Validation steps can route failing rows to an error table, and the finished preparation writes to a destination table with full refresh, append, incremental or upsert behavior. Google's documentation notes some limits: the suggestions are based on a sample, the sources and destinations must be in the same location, and the preparation is scheduled through Dataform rather than run on its own.

Older material and some practice questions mention Dataprep, a visual cleaning tool from Trifacta, now part of Alteryx, that was sold through Google Cloud. It is a partner product with its own licensing rather than a core Google service, and for new work inside BigQuery, Google now points users to BigQuery data preparation. If an exam question offers Dataprep for visual cleaning, read the other options carefully: a newer native option may be the intended answer.

Recording what was done

Every rule the team agreed has a reason and an owner, and Ravi keeps them in a short rules register next to the SQL in the team's Git repository. When Finance asks next year why the October sales differ from the till's own report by 1,214 dollars, the register shows that the difference is exactly the duplicate lines removed on two nights when store 031's till lost its connection.