BigQuery Denormalization: Using STRUCT and ARRAY to Replace Expensive JOINs

The relational data model with the third normal form (3NF) was created for transactional databases (OLTP), where the main goal is write speed and disk space efficiency. In analytical columnar storage (OLAP) like Google BigQuery, normalization becomes a bottleneck.

The JOIN operation in distributed systems causes data shuffle—moving large data volumes between cluster nodes. This leads to an exponential increase in slot consumption (compute resources), longer query execution times, and higher infrastructure costs. The Dremel architecture, which underlies BigQuery, was specifically designed to work with nested and repeated fields. Using STRUCT and ARRAY data types allows storing related data in a single row, eliminating the need for JOINs, speeding up scans, and significantly reducing FinOps metrics.

Below are 10 architectural use cases for migrating data from a classic relational model to a denormalized BigQuery structure, ranging from basic to advanced analytical patterns.

Case 1: Transactional Entities (Orders and Products)

Context: A classic e-commerce schema: an order headers table (orders) and an order items table (order_items).

Explanation: Calculating the order total, analyzing the cart, or filtering by purchased items requires a continuous JOIN between a multi-million row product table and the orders table. This is a standard example of resource waste.

Code:

SQL

-- Transforming classic tables into a denormalized data mart
CREATE OR REPLACE TABLE `project.dataset.orders_denorm` AS
SELECT 
  o.order_id,
  o.created_at,
  o.customer_id,
  -- Collecting order items into an array of structs
  ARRAY_AGG(
    STRUCT(
      i.product_id,
      i.quantity,
      i.price,
      i.discount
    )
  ) AS items
FROM `project.dataset.orders` o
LEFT JOIN `project.dataset.order_items` i USING(order_id)
GROUP BY 1, 2, 3;

-- Querying the denormalized table (without JOIN)
SELECT 
  order_id,
  (SELECT SUM(quantity * price) FROM UNNEST(items)) AS total_revenue
FROM `project.dataset.orders_denorm`
WHERE created_at >= '2026-09-01';

Solution Breakdown: Data is grouped during the ETL/ELT stage. The ARRAY_AGG(STRUCT(...)) function collects all products related to a single order_id into an array within the order row. When querying the table, UNNEST(items) is used inside a scalar subquery for calculations directly in the row context.

Result: The JOIN operation is eliminated for every read. The volume of scanned data decreases since the columnar architecture reads only the requested fields from the struct. Querying order history speeds up by 3 to 5 times.

Case 2: Multi-channel Contacts in CRM (User Profiles)

Context: Storing a user profile alongside multiple contacts (phones, emails, messengers).

Explanation: A relational database uses a users table and a user_contacts reference table. When segmenting the database for marketing campaigns, analysts must join these tables, complicating duplicate handling.

Code:

SQL

-- Query to select users with a priority email
SELECT 
  user_id,
  full_name,
  -- Extracting a specific contact from the array without JOIN
  (SELECT value FROM UNNEST(contacts) WHERE type = 'email' AND is_primary = TRUE LIMIT 1) AS primary_email
FROM `project.dataset.users_profiles`
WHERE 
  EXISTS (SELECT 1 FROM UNNEST(contacts) WHERE type = 'telegram' AND is_active = TRUE);

Solution Breakdown: The contacts field has the type ARRAY<STRUCT<type BOOL BOOL, STRING, is_active is_primary value>>. The EXISTS operator with UNNEST filters the main table by the presence of specific values in the nested array without duplicating rows.

Result: Guaranteed absence of duplicate user rows during filtering. The code is simpler, and the load on the query planner is significantly reduced.

Case 3: Web Analytics Event Collection (GA4 / sGTM Architecture)

Context: Streaming custom event parameters from server-side GTM.

Explanation: Each event contains a different set of parameters. Creating a separate column for each parameter results in a wide table with sparse data. The traditional EAV (Entity-Attribute-Value) approach with separate tables heavily degrades performance.

Code:

SQL

-- Data structure is typed: keys and values of different types
SELECT 
  event_date,
  event_name,
  user_pseudo_id,
  -- Extracting a string parameter
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  -- Extracting an integer parameter
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
FROM `project.dataset.events_*`
WHERE event_name = 'purchase';

Solution Breakdown: The GA4 data schema uses the event_params struct array. The parameter key is strictly defined, and the value is stored in a nested structure with typed fields (string_value, int_value, double_value).

Result: A flexible schema-on-read model allows adding new event parameters without altering the table DDL. The engine scans only the parameters explicitly accessed in UNNEST.

Case 4: Product Catalog with Variations (SKU)

Context: PIM (Product Information Management) systems generate catalogs where one base product has dozens of variations (color, size, price).

Explanation: A normalized structure requires linking products, product_variants, and inventory. Outputting the full product tree requires a cascade of joins.

Code:

SQL

CREATE OR REPLACE TABLE `project.dataset.catalog_denorm` AS
SELECT 
  p.product_group_id,
  p.title,
  p.category,
  ARRAY_AGG(
    STRUCT(
      v.sku_id,
      v.color,
      v.size,
      v.price,
      v.stock_quantity
    )
  ) AS variants
FROM `products` p
JOIN `variants` v ON p.product_group_id = v.product_group_id
GROUP BY 1, 2, 3;

Solution Breakdown: A single row per product group is formed at the data mart level. Logistics, prices, and sizes are packed into the variants array.

Result: An optimized structure for exporting to NoSQL databases or serving via API directly from BigQuery. Checking for at least one variation in stock relies on a simple EXISTS (SELECT 1 FROM UNNEST(variants) WHERE stock_quantity > 0).

Case 5: Entity Change History (SCD Type 2 / CDC)

Context: Storing the history of order status or client data changes (Change Data Capture).

Explanation: Slowly Changing Dimensions Type 2 implies creating a new row for each change. Retrieving a snapshot for a specific date requires complex JOINs on valid_from and valid_to columns.

Code:

SQL

SELECT 
  customer_id,
  current_status,
  -- Retrieving the previous status from the history array
  history[SAFE_OFFSET(ARRAY_LENGTH(history) - 2)].status AS previous_status
FROM (
  SELECT 
    customer_id,
    ARRAY_AGG(
      STRUCT(status, updated_at) 
      ORDER BY updated_at ASC
    ) AS history,
    ARRAY_AGG(status ORDER BY updated_at DESC LIMIT 1)[OFFSET(0)] AS current_status
  FROM `project.dataset.customer_cdc_log`
  GROUP BY customer_id
)

Solution Breakdown: All changes are aggregated into a chronologically sorted array. Accessing the first (OFFSET(0)), last, or any N-th element of the history is handled by index.

Result: Elimination of window functions (LEAD, LAG) and self-joins when querying historical data. The entire change history is attached to a single ID in one row.

Case 6: A/B Testing and User-Experiment Mapping

Context: Split-test analytics where a user is assigned to multiple experiments.

Explanation: User transactions must be matched with the test group assignment table. Under high traffic volume, joining the session table with A/B test logs is inefficient.

Code:

SQL

SELECT 
  user_id,
  transaction_id,
  revenue,
  -- Checking for a specific test in the array of active experiments
  (SELECT variant_name FROM UNNEST(active_experiments) WHERE experiment_id = 'checkout_redesign_v2') AS checkout_variant
FROM `project.dataset.transactions_with_experiments`

Solution Breakdown: During daily sessionization, an ARRAY<STRUCT<experiment_id STRING STRING, variant_name>> is written into the session or transaction row, capturing the exact context of experiments at the time of the action.

Result: Analysts do not need to query the raw logs of the A/B testing platform. Evaluating test results becomes a basic aggregation query, lowering processing costs.

Case 7: Multi-touch Attribution (MTA)

Context: Building user touchpoint chains prior to conversion.

Explanation: For attribution models (First Click, Last Click, U-Shape), analysts must collect all user sessions before conversion, sort them by time, and calculate weights. In a relational model, this requires window functions and joins between conversion and traffic tables.

Code:

SQL

CREATE OR REPLACE TABLE `project.dataset.conversions_mta` AS
SELECT 
  c.conversion_id,
  c.user_id,
  c.revenue,
  ARRAY_AGG(
    STRUCT(
      s.session_id,
      s.source,
      s.medium,
      s.session_start
    ) ORDER BY s.session_start ASC
  ) AS touchpoints
FROM `conversions` c
JOIN `sessions` s 
  ON c.user_id = s.user_id 
  AND s.session_start <= c.conversion_time
GROUP BY 1, 2, 3;

Solution Breakdown: The conversion serves as the parent entity. All preceding sessions are collected in the touchpoints array. The interaction chain is available within a single row.

Result: Developing attribution algorithms via UDFs (User Defined Functions) in JavaScript or SQL occurs inside the row, without executing data shuffle across the dataset.

Case 8: Hierarchical Structures (Category Tree)

Context: Processing trees (e.g., product category graphs) with unlimited nesting levels.

Explanation: Relational databases use an Adjacency List (parent_id), requiring recursive CTEs. BigQuery handles recursion poorly, resulting in slow queries.

Code:

SQL

SELECT 
  category_id,
  name,
  -- Extracting the root category
  category_path[OFFSET(0)].name AS root_category,
  -- Generating the full breadcrumbs path
  ARRAY_TO_STRING(
    (SELECT ARRAY_AGG(p.name ORDER BY p.level) FROM UNNEST(category_path) p), 
    ' > '
  ) AS breadcrumbs
FROM `project.dataset.categories_denorm`;

Solution Breakdown: When the category directory is updated, the full path from the root to the current node is calculated once and saved as ARRAY<STRUCT<level INT64, STRING STRING, category_id name>> in the category_path column.

Result: Recursive CTEs (WITH RECURSIVE) are entirely removed from analytical queries. Retrieving hierarchy levels executes with O(1) time complexity.

Case 9: Error Log Grouping (Error Tracking)

Context: Storing and analyzing server infrastructure logs (Cloud Run, cloud functions, Dataform pipelines).

Explanation: Analyzing millions of separate log rows is inefficient. Engineering teams need aggregated incidents containing a list of related errors.

Code:

SQL

SELECT 
  incident_id,
  service_name,
  COUNT(1) AS total_errors,
  ARRAY_AGG(
    STRUCT(timestamp, error_message, stack_trace) 
    ORDER BY timestamp DESC LIMIT 50
  ) AS latest_error_samples
FROM `project.dataset.raw_logs`
WHERE severity IN ('ERROR', 'CRITICAL')
GROUP BY incident_id, service_name;

Solution Breakdown: Errors are grouped by incident ID. Instead of storing extensive detail logs in a separate table, the latest_error_samples array stores the last 50 error samples as structs for rapid debugging.

Result: The incident table remains compact. Engineers review problem metadata and can unnest the array for detailed analysis without joining the raw logs.

Case 10: GCP Billing Cost Data Details (FinOps)

Context: Cost analysis using the Google Cloud Billing Export.

Explanation: GCP billing data is exported to BigQuery in a denormalized format. Project labels, tags, and credits are passed as struct arrays. Attempting to normalize these arrays degrades cost analytics performance.

Code:

SQL

SELECT 
  invoice.month,
  service.description AS service,
  cost,
  -- Extracting a specific key from the project tags struct array
  (SELECT value FROM UNNEST(project.labels) WHERE key = 'environment') AS env_label,
  -- Aggregating the nested credits array to calculate the final cost
  cost + IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0) AS net_cost
FROM `billing_export.gcp_billing_export_v1_*`
WHERE project.id = 'tech-macro-prod'

Solution Breakdown: The native GCP billing schema provides a reference architecture for ARRAY and STRUCT usage. Project tags and discounts are stored inside the row. UNNEST is applied locally to filter by environment and calculate the net cost.

Result: Terabytes of billing logs are processed in seconds. TCO (Total Cost of Ownership) can be calculated by custom labels without relying on external mapping tables.

Best Practices for Using STRUCT and ARRAY

  1. Monitor Row Size Limits: BigQuery enforces a maximum row size limit of 100 MB. Avoid placing a large client’s entire historical data into a single array. Limit array sizes, and use LIMIT when aggregating log samples.
  2. Partitioning and Clustering: Clustering by fields inside a STRUCT (e.g., STRUCT.key) is supported and effective. However, clustering tables by ARRAY fields is not supported. Apply clustering to the parent row IDs.
  3. Data Updates (UPDATE / MERGE): Updating a specific array element in BigQuery is resource-intensive, requiring a complete array rebuild for the given row. Denormalized tables with arrays are best suited for append-only data, data marts, or periodically fully recalculated snapshots.
  4. Data Typing vs. JSON: Using STRUCT and ARRAY is significantly more efficient than storing data in a JSON column or as a string. Typed structures utilize the columnar format, allowing the engine to scan only required fields. Parsing JSON forces the engine to read the entire text block.
  5. Implement SAFE_OFFSET: When accessing array elements by index, use SAFE_OFFSET(n) instead of OFFSET(n). If an index is out of bounds, SAFE_OFFSET returns NULL rather than failing the entire query.

Transitioning from the third normal form (3NF) to denormalized data marts using STRUCT and ARRAY is a structural requirement of the Dremel architecture powering BigQuery.

For data engineering and FinOps, eliminating macro JOIN operations in favor of local array expansion (UNNEST) ensures mathematically predictable query costs and reduces the volume of scanned data. This approach requires adjusting ELT pipeline design: the intensive processing required to aggregate data into arrays should occur once during the write phase. This preparation allows analytics teams, BI systems, and ML models to execute thousands of lightweight, fast, and cost-effective read queries.

Similar Posts