3 posts tagged with "BigQuery"

Working with nested STRUCT and repeated ARRAY data types in modern cloud data warehouses like BigQuery, Snowflake, and Databricks is standard practice for performance and storage efficiency. However, BI tools often struggle with semi-structured data, forcing engineers to build complex ETL pipelines that flatten everything into massive, slow tables.

Think of nested data like a modular bento box: instead of scattering your main course, side dish, and sauce across separate plates in different rooms, everything for a single order is packaged neatly in one self-contained box.

When a standard SQL developer sees a bento box, their first instinct is to dump all the boxes onto a conveyer belt and unroll every item flat. Before you spin up a dbt model or ETL pipeline to flatten your nested data, realize that Looker handles STRUCT and ARRAY fields natively inside LookML—preserving columnar storage efficiency while generating clean SQL on demand.

Looker out of the box​

When you generate LookML from a BigQuery schema containing nested and repeated data, Looker handles the initial modeling automatically across different nesting depths:

• Single 1:1 STRUCT fields (like geo.country) become dimensions that reference the leaf field directly using dot notation (sql: ${TABLE}.geo.country ;;).
• Repeated 1:N ARRAY<STRUCT> fields (records in arrays) trigger Looker to hide the raw array column in the parent view (hidden: yes), generate a dedicated child view for the array elements, and build a LEFT JOIN UNNEST(${parent.array}) Explore join with relationship: one_to_many.
• Structs inside arrays (such as items.item_dimensions.width) are parsed as dot-notation dimensions directly inside the unnested array view (sql: ${TABLE}.item_dimensions.width ;;).
• Arrays inside arrays (such as items.item_params) generate chained child views and cascading unnest joins in the Explore, unnesting the deeper array relative to the first-level view (LEFT JOIN UNNEST(${items.item_params})).

Here is how nested fields work in Looker, how symmetric aggregates protect you from fanout bugs, and how to model deeply nested structures cleanly.

Have you ever needed to query a data structure where you didn't know how deep the relationships went? Think of an organizational chart, a complex product assembly (Bill of Materials), or a nested comment thread like Hacker News.

Standard SQL joins fail here because they require you to know the number of levels upfront. Enter the Recursive Common Table Expression (CTE)—a powerful tool that allows a query to reference itself, iterating through levels until a termination condition is met.

In this guide, we’ll explore why recursive CTEs are a game-changer, how to implement them in BigQuery, and the specific architectural patterns required to make them work seamlessly in Looker.

This implementation guide explains how to embed Looker dashboards backed by Google BigQuery with OAuth into your custom application smoothly, eliminating the "double authentication" phase in the iframe. There is a reference example repository, looker_oauth, made by Sam Pitcher. The code samples here are in Python, but can be done in any server-side framework.