BigQuery · Data engineering

Loading messy CSV exports into BigQuery: what three years of distributor data taught us

Nicoleta Ungur · 2026-11-09

For three years we've been building the data platform behind a US sales performance product for beverage distributors. It's used by more than 1,500 sales reps. Its data arrives the way it does in most industries: CSV exports from each distributor's systems, dropped into Cloud Storage and loaded into BigQuery. The main transactions table now holds hundreds of millions of rows. These are the problems that cost us the most time.

Map columns by name, never by position

Our first loader assumed column 7 was always the invoice date. Then a distributor's system added a column. Map by header name, normalise headers (case, spaces, punctuation), and fail loudly on a required column that's missing instead of shifting everything one place to the right.

Expect the schema to change

New columns show up without warning. The loader should add them to the table (BigQuery allows adding nullable columns) and log that it did, not reject the file.

Parse dates defensively

We've seen June 31, Excel serial numbers like 45567 in date columns, and two-digit years. Load raw strings into a staging table, then convert with SAFE.PARSE_DATE, which returns NULL instead of failing the whole query, and send the rows that come back NULL to a review table.

SELECT
  COALESCE(
    SAFE.PARSE_DATE('%m/%d/%Y', raw_date),
    DATE_ADD(DATE '1899-12-30', INTERVAL SAFE_CAST(raw_date AS INT64) DAY)
  ) AS invoice_date
FROM staging.transactions

Strip invisible characters

Carriage returns inside text fields make two products look different when they're the same. Trim and remove control characters on the way in, not in every report.

Partition by the date you filter on

A table partitioned by ingestion time looks fine until every report filters by transaction date and BigQuery scans the whole table. Partition by the business date and cluster by the columns you filter on most, such as distributor and product.

Materialized views have rules

Some changes need the view dropped and recreated, and joins or non-incremental logic need allow_non_incremental_definition = true with a max_staleness. Script it, don't click it.

Fix data in bulk, with a mapping table

When codes change across many distributors (sales rep or route codes, for example), one UPDATE ... FROM UNNEST([STRUCT(...)]) per distributor beats hundreds of single-row updates, and it's easy to review before it runs.

Turn on soft delete

Cloud Storage soft delete has let us recover a whole folder of source files that had been overwritten. It costs little and buys you time to notice.

If your data arrives as files from systems you don't control, this is the work we do. See data integration and BigQuery.