3 posts tagged with "LookML"

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 opened a Looker dashboard with ten filters, watched the tiles spin for longer your expected, and wondered why it’s taking so long when the underlying database is usually fast?

The culprit is often a silent connection storm with the tiles AND the dashboard filters. By default, Looker issues separate, concurrent database queries for every type: string filter on a dashboard the moment it loads, just to populate the suggestion dropdowns. It is the database equivalent of a restaurant kitchen prepping every single menu item the second a customer sits down, before they have even opened the menu.

Before trying to optimize these queries, ask yourself: do your users actually need dynamic suggestions for all of these filters? If a field has high cardinality (like a user_uuid) or if the values are static and change only once a year, you should not query the database for suggestions.

Let's look at why this happens and how to manage suggestions in LookML to protect your database connection pool.

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.