BigQuery Mastery: Mastering JSON-to-Schema Generation

JSON to BigQuery Schema: RECORD Types, Partitioning, and Efficient UNNEST Queries

BigQuery stores data in a columnar format called Capacitor, which handles nested and repeated fields natively — there is no need to flatten your JSON into a relational normal form. A JSON object becomes a RECORD (STRUCT), a JSON array becomes REPEATED, and scalar values map to BigQuery's strictly typed primitives. The schema design decisions that matter most are choosing the right numeric type (FLOAT64 vs NUMERIC vs BIGNUMERIC), setting REQUIRED vs NULLABLE mode correctly, configuring partition and cluster columns for query cost reduction, and understanding how UNNEST() flattens repeated records in SQL queries.

Live Example: E-commerce Event Schema

// Input JSON
{
  "event_id": "evt_9f2a1b",
  "occurred_at": "2024-01-15T10:30:00Z",
  "user": {
    "id": "usr_001",
    "email": "alice@example.com",
    "country": "US"
  },
  "order": {
    "id": "ord_55321",
    "total": "149.99",
    "currency": "USD",
    "items": [
      { "sku": "WIDGET-L", "quantity": 2, "unit_price": "59.99" },
      { "sku": "CABLE-USB", "quantity": 1, "unit_price": "29.99" }
    ]
  },
  "session_id": "sess_abc",
  "platform": "web"
}

// Generated BigQuery Schema (JSON format for bq mk)
[
  { "name": "event_id",    "type": "STRING",    "mode": "REQUIRED", "description": "Unique event identifier" },
  { "name": "occurred_at", "type": "TIMESTAMP", "mode": "REQUIRED" },
  {
    "name": "user",
    "type": "RECORD",
    "mode": "NULLABLE",
    "fields": [
      { "name": "id",      "type": "STRING", "mode": "REQUIRED" },
      { "name": "email",   "type": "STRING", "mode": "NULLABLE" },
      { "name": "country", "type": "STRING", "mode": "NULLABLE" }
    ]
  },
  {
    "name": "order",
    "type": "RECORD",
    "mode": "NULLABLE",
    "fields": [
      { "name": "id",       "type": "STRING",  "mode": "REQUIRED" },
      { "name": "total",    "type": "NUMERIC", "mode": "REQUIRED" },
      { "name": "currency", "type": "STRING",  "mode": "REQUIRED" },
      {
        "name": "items",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
          { "name": "sku",        "type": "STRING",  "mode": "REQUIRED" },
          { "name": "quantity",   "type": "INTEGER", "mode": "REQUIRED" },
          { "name": "unit_price", "type": "NUMERIC", "mode": "REQUIRED" }
        ]
      }
    ]
  },
  { "name": "session_id", "type": "STRING",  "mode": "NULLABLE" },
  { "name": "platform",   "type": "STRING",  "mode": "NULLABLE" }
]

# Create the table
bq mk \
  --table \
  --time_partitioning_field=occurred_at \
  --time_partitioning_type=DAY \
  --clustering_fields=platform,user.country \
  myproject:events.ecommerce_events \
  schema.json

BigQuery Type Reference

// Numeric types — choose carefully:
// FLOAT64 (same as FLOAT):  64-bit IEEE 754 — imprecise for money
// NUMERIC:                  exact decimal, 38 digits, 9 decimal places — use for money
// BIGNUMERIC:               exact decimal, 76 digits, 38 decimal places — large financial
// INTEGER (INT64):          64-bit signed integer
// INT64, BIGINT:            aliases for INTEGER

// String types:
// STRING:           variable-length unicode
// BYTES:            raw binary (use for hashed values, binary data)

// Temporal types:
// DATE:             "2024-01-15" (date only, no time)
// TIME:             "10:30:00" (time only, no timezone)
// DATETIME:         "2024-01-15T10:30:00" (local, no timezone)
// TIMESTAMP:        "2024-01-15T10:30:00Z" (UTC-normalized, use this for events)

// Other types:
// BOOL:             true/false
// RECORD/STRUCT:    nested object
// ARRAY:            repeated values (same as mode: REPEATED in schema)
// JSON:             untyped JSON blob (BigQuery 2022+ — queryable but no type inference)
// GEOGRAPHY:        geospatial points, lines, polygons (WGS 84)

// Mode values:
// REQUIRED:    NOT NULL — ingestion fails if field is absent or null
// NULLABLE:    may be absent or null (default)
// REPEATED:    array of values — always NULLABLE at the element level

Partitioning and Clustering for Cost Reduction

BigQuery charges by bytes scanned. Partitioning and clustering reduce the data scanned per query:

-- Partitioned table: queries that filter on occurred_at scan only relevant day(s)
-- Without partition filter, BigQuery scans the ENTIRE table

-- Query with partition pruning (scans only 2024-01-15 partition)
SELECT event_id, user.country, order.total
FROM `myproject.events.ecommerce_events`
WHERE DATE(occurred_at) = '2024-01-15'
  AND platform = 'web';

-- Clustered columns further reduce scan within each partition
-- Cluster order matters — first column has most impact
-- Best for: high-cardinality columns used in WHERE/GROUP BY

-- Check partition info
SELECT table_name, partition_id, total_rows, total_logical_bytes
FROM `myproject.events.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'ecommerce_events'
ORDER BY partition_id DESC
LIMIT 10;

-- Expire old partitions automatically (after 90 days)
ALTER TABLE `myproject.events.ecommerce_events`
SET OPTIONS (partition_expiration_days = 90);

Querying Nested and Repeated Fields with UNNEST

-- Access nested RECORD fields with dot notation
SELECT
  event_id,
  user.country,
  order.total,
  occurred_at
FROM `myproject.events.ecommerce_events`
WHERE user.country = 'US'
  AND order.total > 100;

-- UNNEST: flatten REPEATED items into rows for aggregation
-- Each order item becomes its own row, joined to the parent event
SELECT
  e.event_id,
  e.occurred_at,
  item.sku,
  item.quantity,
  item.unit_price,
  item.quantity * item.unit_price AS line_total
FROM `myproject.events.ecommerce_events` AS e,
UNNEST(e.order.items) AS item
WHERE DATE(e.occurred_at) = '2024-01-15';

-- Aggregate over REPEATED without UNNEST using built-in functions
SELECT
  event_id,
  ARRAY_LENGTH(order.items)                                AS item_count,
  (SELECT SUM(i.quantity) FROM UNNEST(order.items) AS i)  AS total_quantity,
  (SELECT MAX(i.unit_price) FROM UNNEST(order.items) AS i) AS highest_unit_price
FROM `myproject.events.ecommerce_events`
WHERE order.id IS NOT NULL;

-- GROUP BY nested field
SELECT
  user.country,
  COUNT(*) AS event_count,
  SUM(order.total) AS total_revenue
FROM `myproject.events.ecommerce_events`
WHERE DATE(occurred_at) BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY user.country
ORDER BY total_revenue DESC;

Ingesting JSON Data

# Load from Google Cloud Storage (newline-delimited JSON)
bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  --schema=schema.json \
  myproject:events.ecommerce_events \
  gs://my-bucket/events/*.json

# Auto-detect schema (good for exploration, not production)
bq load \
  --autodetect \
  --source_format=NEWLINE_DELIMITED_JSON \
  myproject:events.ecommerce_events \
  gs://my-bucket/events/sample.json

# Stream inserts via API (real-time)
from google.cloud import bigquery

client = bigquery.Client()
table_id = "myproject.events.ecommerce_events"

errors = client.insert_rows_json(table_id, [
  {
    "event_id":    "evt_12345",
    "occurred_at": "2024-01-15 10:30:00 UTC",
    "user":        { "id": "usr_001", "country": "US" },
    "order":       { "id": "ord_001", "total": "149.99", "currency": "USD", "items": [] },
    "platform":    "web"
  }
])
if errors:
    raise Exception(f"BigQuery insert errors: {errors}")

Schema Evolution

-- BigQuery supports relaxing REQUIRED to NULLABLE and adding new NULLABLE columns
-- It does NOT support: changing types, removing columns, tightening NULLABLE to REQUIRED

-- Add a new nullable column (safe)
ALTER TABLE `myproject.events.ecommerce_events`
ADD COLUMN IF NOT EXISTS utm_source STRING;

-- Relax REQUIRED to NULLABLE for a field (requires full schema update via API)
# bq update --schema updated_schema.json myproject:events.ecommerce_events

-- For type changes (e.g., STRING → NUMERIC), you must:
-- 1. Add new column: order_total_numeric NUMERIC
-- 2. Backfill: UPDATE table SET order_total_numeric = CAST(order_total AS NUMERIC)
-- 3. Remove old column (can't drop, but can stop using it)
-- BigQuery doesn't support DROP COLUMN on active tables (workaround: recreate from query)

Best Practices for Production

• Use NUMERIC for all monetary values: FLOAT64 cannot exactly represent values like 0.10 — small rounding errors accumulate in financial aggregations. NUMERIC (38 significant digits, 9 decimal places) handles all real-world currency amounts exactly.
• Use TIMESTAMP for event times, DATE for partition column: TIMESTAMP stores UTC — correct for analytics. Use DATE(event_timestamp) as the partition column or declare a DATE partition field separately. Avoid DATETIME — it stores local time with no timezone and creates ambiguity.
• Limit REPEATED nesting depth: BigQuery supports up to 15 levels of nesting. Deep nesting is valid but query complexity grows. Beyond 2-3 levels of RECORD nesting, consider flattening to separate tables and using JOIN.
• Always specify partition filter in production queries: A query without a partition filter on a partitioned table will scan all partitions. Use require_partition_filter = true table option to enforce this at the table level and prevent accidental full-table scans.

FAQ

Q: When should I use BigQuery's JSON type vs RECORD/REPEATED?
A: Use RECORD for structured data with a known, stable schema — it's stored in columnar format and queries run faster. Use the JSON type for truly schema-less data (arbitrary event properties, webhook payloads with unknown keys) — it stores raw JSON as a string but supports JSON_VALUE(col, '$.key') extraction. RECORD is always faster and cheaper to query than JSON.

Q: How does BigQuery pricing work with nested data?
A: BigQuery charges by bytes scanned per query. Selecting only top-level columns from a table with nested RECORD fields does NOT scan the nested field data — columnar storage means you only pay for the columns you reference in SELECT, WHERE, and GROUP BY.

Q: How do I handle schema mismatches during ingestion?
A: Set --max_bad_records=0 for strict ingestion (any mismatch fails the load). For exploratory ingestion, use --max_bad_records=N to allow N failures before aborting. For streaming inserts, errors are returned per row in the API response — handle them in your application.