Data formats, schemas and data types

In the second session, on Thursday, October 8, Ravi opens a folder of sample files that arrived overnight: a store sales export, a returns export, a product catalog extract and a supplier price list. "Before we load anything," he says, "we need to know what we are holding." Choosing the right load settings, the right storage and the right column types all depends on understanding the format and the schema. Mistakes here are cheap to fix on day one and expensive to fix after a year of loads.

Structured, semi-structured and unstructured data

Lesson 1 introduced the three kinds of data. The distinction matters because each kind tends to have its own home on Google Cloud. Structured data, with fixed columns and types, fits tables in BigQuery or a relational database such as Cloud SQL. Semi-structured data, such as JSON with nested or optional fields, can be loaded into BigQuery using nested and repeated columns or the JSON data type, or kept in a document database such as Firestore. Unstructured data, such as images, PDFs and audio, lives as objects in Cloud Storage, and can be referred to from BigQuery through object tables when it needs to be analyzed.

Common file formats

Most data reaches Google Cloud as files, and BigQuery can load several file formats directly. They differ in whether they carry their own schema, whether they are compressed and splittable, and whether they are stored by row or by column.

BigQuery's documentation notes that JSON must be newline-delimited for loading: each line holds one complete record. A file that contains a single large JSON array, as many applications export, has to be converted first. The returns portal does exactly that, so it is one of the reasons the returns data needs a light transformation before loading.

For loading speed, Google's documentation recommends Avro as the preferred format for loading into BigQuery because its data blocks can be read in parallel even when compressed, and Parquet and ORC also load efficiently. Compressed CSV and JSON files load more slowly because a gzip file cannot be split, so a single worker has to read it from start to finish. For Birchline's small store files that hardly matters. For the ten-year history export from the old warehouse, it matters a great deal, so Ravi plans to export that history to Parquet or Avro rather than to gzipped CSV.

What a schema is

A schema describes the shape of the data: the names of the fields, their data types, whether they may be empty, and for nested data, how the fields fit inside each other. In BigQuery, every table has a schema, and each column has a name, a type and a mode. The mode is NULLABLE (the value may be missing), REQUIRED (it must be present) or REPEATED (the column holds an array).

A schema can come from three places. Self-describing formats such as Avro, Parquet and ORC carry it inside the file. For CSV and JSON, BigQuery can auto-detect a schema by sampling the file, or you can supply one explicitly, as a JSON schema file or in the load command. Auto-detection is handy for exploring a file, but Ravi insists on explicit schemas for anything that will be loaded every night. A sample that happens to contain only whole numbers will make a price column an INTEGER, and the first night a price of 24.99 arrives, the load fails.

[
  {"name": "store_id",   "type": "STRING",    "mode": "REQUIRED"},
  {"name": "txn_id",     "type": "STRING",    "mode": "REQUIRED"},
  {"name": "txn_ts",     "type": "TIMESTAMP", "mode": "REQUIRED"},
  {"name": "sku",        "type": "STRING",    "mode": "REQUIRED"},
  {"name": "qty",        "type": "INT64",     "mode": "REQUIRED"},
  {"name": "unit_price", "type": "NUMERIC",   "mode": "REQUIRED"},
  {"name": "discount",   "type": "NUMERIC",   "mode": "NULLABLE"},
  {"name": "tender",     "type": "STRING",    "mode": "NULLABLE"}
]

BigQuery data types that matter for loading

Choosing the right type for each column prevents a whole category of errors. Mateo works through the store sales columns one by one with Ravi, and the reasoning is more useful than the final list.

Two choices deserve a closer look. First, money should be NUMERIC, not FLOAT64. A floating-point number cannot represent most decimal fractions exactly, so adding thousands of prices can produce totals that are off by a fraction of a cent. That is harmless for a temperature but not for a ledger that Finance reconciles to the cent.

# Why money should not be stored as a floating-point number.
from decimal import Decimal

prices = [0.10] * 30_000          # thirty thousand items priced at ten cents
float_total = sum(prices)
exact_total = sum(Decimal("0.10") for _ in range(30_000))

print(f"Float total:   {float_total!r}")
print(f"Exact total:   {exact_total}")
print(f"Difference:    {Decimal(repr(float_total)) - exact_total}")

Float total:   3000.0
Exact total:   3000.00
Difference:    0.00

Second, timestamps need a known time zone. The store tills record local time without a zone. Birchline's stores span more than one time zone, so 9:00 at a store in Denver is not the same moment as 9:00 in Columbus. Loading the till time straight into a TIMESTAMP column would treat it as UTC and shift every sale by several hours. The team decides to load the till time as DATETIME in a raw column, add each store's time zone from the store reference table, and compute a proper TIMESTAMP in staging.

Nested and repeated data

BigQuery can store nested data directly. An online order with three order lines can be one row whose lines column is an ARRAY of STRUCTs, each holding a sku, a quantity and a price. Storing data this way avoids repeating the order header three times and often makes queries cheaper, because related data is read together. JSON files with nested objects load naturally into this shape, and Avro and Parquet carry nested schemas too.

{"order_id": "W-1048812", "placed_at": "2026-10-07T14:22:05Z", "customer_ref": "C-88213",
 "lines": [{"sku": "TNT-2P-GRN", "qty": 1, "price": "289.00"},
           {"sku": "STK-AL-08",  "qty": 2, "price": "14.50"}]}

The alternative is the JSON data type, which stores a whole JSON value in one column and lets queries reach into it with JSON functions. It suits data whose fields change often or are not known in advance, such as the raw payload of a web event that the shop's developers keep extending. Where the fields are known and stable, typed nested columns are better, because each field gets a proper type and BigQuery can read just the fields a query needs.

Schema evolution

Sources change. The shop team adds a gift_message field; a supplier renames a column; the till software update adds a loyalty_id. A load that expects the old schema can fail, or worse, silently put values in the wrong columns. BigQuery allows some changes safely when you append data: adding new NULLABLE columns and relaxing a REQUIRED column to NULLABLE. These are controlled by load options that allow field addition and field relaxation. Other changes, such as renaming a column or changing its type, need a deliberate migration.

For CSV files, column order matters, because a CSV has no field names apart from an optional header. If a supplier inserts a new column in the middle, every column after it shifts one place. JSON and the binary formats match fields by name, so a new field does not move the others. This is one more reason Ravi asks partners to send newline-delimited JSON or Parquet when they can.

A data contract

To reduce surprises, the team writes a short data contract for each regular feed: a document agreed with the people who produce the data, stating the format, the schema, the delivery time, the file naming pattern and who to contact when something changes. It is not a Google Cloud feature, just good practice, but it turns schema changes from midnight surprises into planned work. The store systems team agrees the first contract for the till exports: CSV with a header, UTF-8 encoding, comma delimiter, one file per store per night named pos_STORE_YYYYMMDD.csv, delivered by 02:00 local time.