bq load and the autodetect trap

bq load is the canonical way to ship CSVs into BigQuery. Two modes: autodetect (BigQuery infers types) and explicit schema (you provide a schema file). Autodetect saves time on day one and creates bugs forever after.

scripts/bq_load_autodetect.sh
bash
bq load --autodetect --replace \
  --source_format=CSV \
  "${GCP_PROJECT_ID}:${BQ_RAW_DATASET}.olist_orders_dataset" \
  "gs://${GCS_RAW_BUCKET}/raw/olist_orders_dataset.csv"

Autodetect is fine for the first ten minutes of exploring a dataset. Never for production. Notice no schema specified.

Autodetect samples the first 100 rows. A timestamp that looks like an integer in those rows becomes INT64 in the schema. Three days later, a row with a real timestamp arrives and the load fails. The fix is a static schema file.

scripts/bq_load_with_schema.sh
bash
bq load --replace \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --schema=schemas/olist_orders_dataset.json \
  "${GCP_PROJECT_ID}:${BQ_RAW_DATASET}.olist_orders_dataset" \
  "gs://${GCS_RAW_BUCKET}/raw/olist_orders_dataset.csv"

Explicit schema with a JSON file. Reproducible. Future-proof. Production-grade.

One-time effort. Write it by hand for each table the first time. After that, version-control it next to the dbt project. The course ships schemas for all 8 Olist tables. dbt builds on top of those raw datasets, treating them as the source of truth.

Quiz: Quiz

Loading practice…