Loading data into BigQuery

Thursday, October 22, is the first time Mateo loads real data into Birchline's production project. The ingestion plan from lesson 5 sends most sources toward BigQuery, but there are several different ways to get data into a BigQuery table, and they differ in cost, speed and what can go wrong. Ravi wants Mateo to know all of them, and to choose the simplest one that meets each need.

The ways into a BigQuery table

Batch load jobs

A load job is the workhorse for files. It reads one or more files, applies a schema, and writes the rows into a table in a single operation that either succeeds completely or fails without writing anything. That all-or-nothing behavior is valuable: a half-loaded night is impossible. Load jobs that use BigQuery's shared slot pool are free of charge, which makes them the cheapest way to bring large volumes in; the trade-off is that the shared pool gives no guarantee about how quickly a job starts when the system is busy.

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --replace \
  --max_bad_records=0 \
  bl-data-prod:raw.pos_sales_20261021 \
  "gs://bl-data-landing/pos/2026/10/21/pos_*_20261021.csv" \
  ./pos_sales_schema.json

Each option in the command reflects a decision from earlier lessons. The explicit schema file avoids auto-detection. Skipping the header row matches the data contract. Setting the number of bad records allowed to zero means one malformed line fails the whole load, which is the right choice for financial data: Ravi would rather know about the bad line than lose it silently. For less critical feeds, a small allowance lets the load succeed and the bad lines are reported in the job's errors.

The write disposition says what happens if the table already has data. WRITE_APPEND adds the new rows, WRITE_TRUNCATE replaces the table's contents (the --replace flag above), and WRITE_EMPTY loads only if the table is empty. A related setting, the create disposition, decides whether the job may create the table if it does not exist. Choosing the disposition is how a load is made idempotent: replacing one night's table or partition can be repeated safely, while appending the same files twice doubles them.

Loading into partitions

Rather than one table per night, Birchline's raw.pos_sales is a single table partitioned by business date. Each night's load replaces only its own partition, so a re-run of October 21 cannot touch October 20. A load job can target one partition with a partition decorator, the table name followed by a dollar sign and the date, or a query can replace a partition. Partitioning also makes queries cheaper when they filter on the date, which GCP-221 covers in depth.

bq load --source_format=CSV --skip_leading_rows=1 --replace \
  --time_partitioning_field=business_date \
  'bl-data-prod:raw.pos_sales$20261021' \
  "gs://bl-data-landing/pos/2026/10/21/*.csv" ./pos_sales_schema.json

Checking that a load is complete

A successful load job proves only that the files it found were loaded. It says nothing about files that did not arrive. Mateo adds a check after each night's load: compare the files and rows loaded against what was expected. The stores' upload script writes a small manifest file with the row count of each store's file, which gives the check something to compare against.

# Compare last night's load with the stores' manifest.
manifest = {"001": 812, "002": 640, "003": 915, "012": 701, "019": 588, "027": 0, "031": 920}
loaded   = {"001": 812, "002": 640, "003": 915, "012": 701, "031": 1840}
closed_today = {"027"}               # known closures from the reference table

problems = []
for store, expected in sorted(manifest.items()):
    got = loaded.get(store, 0)
    if store in closed_today and got == 0:
        continue
    if got == 0:
        problems.append(f"store {store}: file missing ({expected} rows expected)")
    elif got != expected:
        problems.append(f"store {store}: loaded {got}, manifest says {expected}")

print(f"Stores expected: {len(manifest)}, loaded: {len(loaded)}")
print("\n".join(problems) if problems else "All stores complete")

Stores expected: 7, loaded: 5
store 019: file missing (588 rows expected)
store 031: loaded 1840, manifest says 920

In this sample night, store 019's file never arrived and store 031 appears twice, exactly the double upload that staging deduplication will catch. The check does not fix either problem, but it turns both into a message at 03:30 instead of a surprise at 09:00. The same check will later move into an automated quality scan, in lesson 10.

Loading semi-structured data

Newline-delimited JSON loads much like CSV, with --source_format=NEWLINE_DELIMITED_JSON. Nested objects become STRUCT columns and arrays become REPEATED columns, as long as the schema describes them. The returns portal's export is a single JSON array with a phone number in every record, so it needs the light transformation planned in lesson 1 before it can load. A small Cloud Run job reads the export, drops the phone number, writes one record per line to the landing bucket, and then starts the load. The unmasked file is deleted from the portal's export folder once the job confirms the load.

For data whose fields change often, such as the raw web event payloads, loading the whole record into a column of the JSON type avoids a schema change every time the shop's developers add a field. The trade-off, as lesson 2 explained, is that the fields inside have no declared types until a query extracts them.

External tables and BigLake tables

Sometimes data should not be loaded at all. An external table leaves files in Cloud Storage and reads them at query time. Mateo used one in lesson 3 for exploration. External tables are convenient, but every query reads and parses the files again, performance is lower than native storage, and some features, such as table preview and certain caching, are not available.

A BigLake table is an improved kind of external table. It uses a connection with its own service account, so users can be given access to the table without being given access to the files in the bucket, and it supports fine-grained controls such as column-level security on data that stays in open formats like Parquet or Apache Iceberg. BigLake is a good fit when the same files must be used by BigQuery and by other engines such as Spark on Dataproc. Birchline's supplier invoices and photo metadata may end up there; for now, the warehouse data is loaded as native tables because it is queried constantly.

Streaming into BigQuery

For records that arrive one by one, BigQuery's Storage Write API is the recommended way to stream. It supports exactly-once delivery when used with streams that track offsets, and it is what Dataflow and Pub/Sub BigQuery subscriptions use behind the scenes. The older streaming insert method, called tabledata.insertAll, still works but is now the legacy option. Lesson 7 shows Birchline's web events arriving this way.

Transfers and change data capture into BigQuery

Two managed services fill tables without any load code. For Google Ads, Mateo sets up a BigQuery Data Transfer Service transfer: he chooses the Google Ads connector, the customer account, the destination dataset raw in us-east5, and a daily schedule. The service creates the reporting tables and refreshes them each day, and it can backfill earlier dates on request. Its transfer runs appear in the console with their status, which the team watches like any other job.

For the shop's MySQL database, Datastream will write changes into BigQuery tables that mirror the source tables. Datastream can apply the changes so that the BigQuery table always shows the latest state of each row, with a configurable staleness limit that trades freshness for cost. The plan is to point Datastream at the orders, order lines and product tables only, not the whole database: the customer table holds personal data that the analytics team does not need in raw form.

Which method for which feed