14 posts tagged with "Looker"

BigQuery BI Engine accelerates SQL queries using an in-memory execution service. When a query scans a pinned table, response times drop from seconds to milliseconds.

Reserving memory in BI Engine works like renting an assigned parking space in a garage. Management assigns you spot #42. However, every night the maintenance crew repaves the garage and paints new numbers on every stall. When you pull up in the morning, spot #42 is gone. Your permit still points to a space that no longer exists, and security redirects you to the slow overflow lot three blocks away. That is what happens when Looker rebuilds Persistent Derived Tables (PDTs) with new physical table names during each refresh.

In embedded dashboards, customer-facing applications demand sub-second query speeds. Pinning tables in BI Engine keeps critical data in memory, but declaring a table as preferred is only half the battle. To guarantee instant responsiveness, you must also eliminate the first query cold miss with cache pre-warming when your tables rebuild.

Configure table pinning when:

• Embedded analytics and customer-facing portals require consistent sub-second response times.
• Ad-hoc analyst queries repeatedly evict dashboard data from your BI Engine memory reservation.
• Your queries target Looker PDTs or pre-aggregated rollups that rebuild on scheduled intervals.

Conversational analytics is taking off, with Looker customers rapidly adopting Conversational Analytics in Looker to let users query business data through natural language.

In a multi-instance setup (such as Dev, Stage, and Prod), promoting agents between environments requires synchronizing runtime metadata, prompt configurations, and golden queries across instances. While our GitHub Actions release workflow demonstrates a three-tier pipeline, the underlying migration script uses looker-cli and supports generic source and target endpoints across any number of environments.

TL;DR​

You can run the migration script directly using looker-cli and curl:

# 1. Log in to both instances
looker-cli session login \
  --host "$LOOKER_SOURCE_BASE_URL" \
  --client-id "$LOOKER_SOURCE_CLIENT_ID" \
  --client-secret "$LOOKER_SOURCE_CLIENT_SECRET"

looker-cli session login \
  --host "$LOOKER_TARGET_BASE_URL" \
  --client-id "$LOOKER_TARGET_CLIENT_ID" \
  --client-secret "$LOOKER_TARGET_CLIENT_SECRET"

# 2. Run the migration script
curl -fsSL https://raw.githubusercontent.com/lkrdev/multi-instance-cicd-demo/main/scripts/migrate_agents_cli.sh | \
  LOOKER_SOURCE_BASE_URL="$LOOKER_SOURCE_BASE_URL" \
  LOOKER_TARGET_BASE_URL="$LOOKER_TARGET_BASE_URL" \
  bash -s -- config/content_agents_whitelist.yaml

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.

If you use Looker private embedding to render dashboards inside a custom web portal, you will eventually run into iframe authentication issues. In a private embed, the user's active browser session with the Looker instance determines whether the content loads.

Think of iframe authentication like trying to enter a Broadway theater. If the ticket scanner at the door doesn't recognize your ticket, they can't verify your identity through a glass barrier (the iframe) for security reasons. Instead, you have to step out of line, walk over to the physical box office window (a popup) to show your photo ID, get your ticket verified, and then return to the main entrance line to scan through.

Think of an access token like a corporate security badge. You wouldn't stand in line at the front desk to print a new visitor pass every time you walk through an interior door. Instead, you simply swipe your active badge until it expires.

Similarly, when your scripts or API services call Looker, they shouldn't authenticate from scratch on every request. Repeatedly logging into the API or logging in as a user (/login/:user endpoint) not only adds unnecessary latency but also creates token bloat that can degrade server performance at scale.

A simple token cache solves this. By storing access tokens locally, you restore active sessions instantly. Because Looker binds workspace state (Production vs. Developer Mode) directly to the session token, a user can maintain simultaneous dev and production tokens. Reusing a token already pinned to Developer Mode avoids the network round-trip of switching workspaces on every command.

If you are ready to implement this, jump directly to the code samples.

Semantic Layer Importance in Agentic AI​

There is a continued discussion over the need for a semantic layer to support enterprise scale deployments of conversational analytics and agentic data applications. Frequently referenced is a 2024 paper using GPT 4, which is one of the few quantitative assessments published in the public domain demonstrating the value of a semantic layer. The state of LLMs, including reasoning improvements and context adherence, continues to evolve, and existing benchmarks such as this quickly turn stale.

Recently, DBT put out a blog post with their own updated 2026 benchmark comparing Text-to-SQL against the DBT semantic layer, highlighting the benefits of the semantic layer. Unfortunately, this benchmark had limitations on the scope of the evaluation (e.g., only using 11 questions from the source benchmark, minimal context engineering).

This study set out to expand on the prior art by leveraging the same dataset, but with an expanded set of questions and a more comprehensive evaluation harness.

Today we're introducing the lkr code-mode MCP server to allow your LLM to orchestrate all of Looker API's in a simple interface. The Model Context Protocol (MCP) is a great way to connect AI agents to external tools. But as agents connect to bigger APIs, we run into a big problem: context bloat. Looker actually tried to fix this by creating a trimmed-down version of its MCP that exposes only a few select APIs. But that's pretty limiting if you want to build complex workflows and those workflows require many back and forth tool calls. Code Mode flips this on the head, the LLM writes code to orchestrate the entire workflow in one go; that's why developers are moving towards Code Mode for these use cases.

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.

In modern business intelligence, the ability to tailor data presentations to precise business needs is paramount. While Looker provides an extensive suite of standard charts and tables, organizations frequently encounter unique requirements such as specialized network graphs, custom geographic overlays, or highly interactive d3-based visualizations.

Looker addresses this need with its Custom Visualization Framework, which lets you run arbitrary third-party JavaScript code seamlessly within a governed BI environment. However, executing external JavaScript within an enterprise application introduces significant security challenges, primarily around Cross-Site Scripting (XSS), data exfiltration, and unauthorized DOM access.

In this deep dive, we will explore the architecture of the Custom Visualization API, the mechanics of its secure loading strategy, and best practices for safely hosting custom visualization assets.