Skip to main content

Pin LookML PDTs to BigQuery BI Engine

· 17 min read

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.

Looker's dynamic PDT lifecycle​

Looker manages Persistent Derived Tables with high reliability and zero downtime.

When Looker rebuilds a PDT, it does not overwrite the existing table in place. Instead, Looker creates a new table with a unique hash in your scratch dataset, such as looker_scratch.LR$M123_orders_pdt. Users continue querying the previous table while the new build finishes.

Once the new table materializes, Looker atomically swaps queries to the fresh data and drops the old table. If a build fails midway, Looker keeps the old version intact. This approach prevents downtime, avoids partial reads, and protects dashboard users from build locks.

While this lifecycle is ideal for query reliability, it creates a challenge for static memory pinning.

BigQuery BI Engine uses the preferred tables configuration option to prevent cache eviction. For standard data warehouse tables like analytics.dim_users, you set this option once in Terraform or the Google Cloud console.

However, static configuration cannot keep up with Looker's dynamic table names. Each rebuild produces a new table name with a different hash. If you pin a table once, that pin becomes obsolete as soon as Looker completes its next datagroup cycle. The old table disappears, the new table remains unpinned, and query speed reverts to disk scans.

Why publish_as_db_view does not solve the issue:

LookML provides a publish_as_db_view parameter for persistent derived tables. This parameter creates a static view in BigQuery that points to the latest physical PDT.

However, BigQuery BI Engine does not support standard logical views in the preferred tables configuration. BI Engine requires physical tables or materialized views to allocate memory blocks. If you add a standard logical view to preferred tables, BigQuery rejects the operation or ignores the view. To accelerate a Looker PDT with BI Engine, you must pin the physical scratch table directly.

Provision regional capacity first​

Before you pin tables in LookML, you must create your BI Engine reservation.

BigQuery allows only one BI Engine reservation per project, per region. Because of this restriction, BigQuery assigns the fixed name default to the regional reservation. You cannot create multiple named reservations for separate teams or environments.

BigQuery does not provide a CREATE BI_CAPACITY statement. Instead, you create and scale the initial reservation using ALTER BI_CAPACITY with the size_gb parameter:

ALTER BI_CAPACITY `my-project.region-us.default`
SET OPTIONS (
  size_gb = 10
);

You can also provision capacity in the Google Cloud console under BigQuery BI Engine. Alternatively, use the google_bigquery_bi_reservation Terraform resource.

If you do not create this reservation first, downstream ALTER BI_CAPACITY statements fail. Once created, LookML create_process updates the preferred_tables list dynamically on each PDT build without altering size_gb.

Pinning tables across the PDT DAG​

Production Looker models organize data into a Directed Acyclic Graph (DAG) of derived tables. For example, you aggregate transactions into an order_facts rollup table, and compute customer lifetime metrics in a user_order_facts table.

Looker executes PDT builds according to these graph dependencies. When a downstream view references ${order_facts.SQL_TABLE_NAME}, Looker detects the dependency and builds the upstream table first.

LookML provides two primary derived table formats:

  • Native Derived Tables (NDTs), which use LookML explore_source syntax.
  • SQL Derived Tables (SQL DTs), which use direct SQL queries.

Because LookML rules prohibit the create_process parameter inside an explore_source NDT, you cannot place pinning statements inside an NDT view. Instead, the recommended pattern separates your business logic from infrastructure pinning by running a terminal runner view at the end of the PDT DAG.

The end-of-DAG runner pattern​

In this pattern, order_facts is a Native Derived Table (NDT) and user_order_facts is a SQL Derived Table (SQL DT). Neither view contains create_process blocks or infrastructure DDL.

First, define the order_facts NDT:

view: order_facts {
  derived_table: {
    datagroup_trigger: ecommerce_etl_modified
    explore_source: order_items {
      column: order_id { field: order_items.order_id }
      column: items_in_order { field: order_items.count }
      column: order_amount { field: order_items.total_sale_price }
      column: user_id { field: order_items.user_id }
      column: created_at { field: order_items.created_raw }
      derived_column: order_sequence_number {
        sql: RANK() OVER (PARTITION BY user_id ORDER BY created_at) ;;
      }
    }
    cluster_keys: ["user_id"]
  }

  dimension: order_id {
    primary_key: yes
    hidden: yes
    type: number
    sql: ${TABLE}.order_id ;;
  }

  dimension: items_in_order {
    type: number
    sql: ${TABLE}.items_in_order ;;
  }

  dimension: order_amount {
    type: number
    value_format_name: usd
    sql: ${TABLE}.order_amount ;;
  }

  dimension: order_sequence_number {
    type: number
    sql: ${TABLE}.order_sequence_number ;;
  }

  dimension: is_first_purchase {
    type: yesno
    sql: ${order_sequence_number} = 1 ;;
  }
}

Next, define the user_order_facts SQL DT:

view: user_order_facts {
  derived_table: {
    datagroup_trigger: ecommerce_etl_modified
    sql:
      SELECT
        user_id,
        COUNT(DISTINCT order_id) AS lifetime_orders,
        SUM(sale_price) AS lifetime_revenue,
        CAST(MIN(created_at) AS TIMESTAMP) AS first_order,
        CAST(MAX(created_at) AS TIMESTAMP) AS latest_order
      FROM `looker-private-demo.thelook.order_items`
      GROUP BY user_id ;;
    cluster_keys: ["user_id"]
  }

  dimension: user_id {
    primary_key: yes
    hidden: yes
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension_group: first_order {
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.first_order ;;
  }

  dimension_group: latest_order {
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.latest_order ;;
  }

  dimension: lifetime_orders {
    type: number
    sql: ${TABLE}.lifetime_orders ;;
  }

  dimension: repeat_customer {
    type: yesno
    sql: ${lifetime_orders} > 1 ;;
  }

  dimension: lifetime_revenue {
    type: number
    value_format_name: usd
    sql: ${TABLE}.lifetime_revenue ;;
  }

  measure: average_lifetime_revenue {
    type: average
    value_format_name: usd
    sql: ${lifetime_revenue} ;;
  }
}

Finally, create a terminal runner view called order_facts_bi_engine. This view uses create_process and references both ${order_facts.SQL_TABLE_NAME} and ${user_order_facts.SQL_TABLE_NAME}.

This runner view introduces three key optimizations:

  • A hidden explore (explore: order_facts_bi_engine { hidden: yes }) enables developers to test, rebuild, and verify the runner in Development Mode.
  • A zero-byte dummy table (CREATE OR REPLACE TABLE ${SQL_TABLE_NAME} AS SELECT NULL AS n FROM UNNEST([]) WHERE 1=2) fulfills Looker's table tracking requirement instantly without consuming disk storage.
  • A single query with UNION ALL scans and pre-warms both tables into BI Engine RAM simultaneously.

Because this runner view references both upstream tables, Looker builds order_facts and user_order_facts first. Looker executes the runner view only after both upstream tables finish building:

explore: order_facts_bi_engine { hidden: yes } # Used to test in dev mode, remove before pushing to production

view: order_facts_bi_engine {
  derived_table: {
    datagroup_trigger: ecommerce_etl_modified
    create_process: {
      sql_step:
        -- 1. Create a lightweight marker table for Looker to track
        CREATE OR REPLACE TABLE ${SQL_TABLE_NAME} AS
        SELECT NULL AS n FROM UNNEST([]) WHERE 1=2 ;;

      sql_step:
        -- 2. Strip Looker dialect backticks and pin both upstream tables
        BEGIN
          DECLARE orders_table STRING;
          DECLARE user_facts_table STRING;

          SET orders_table = TRIM('${order_facts.SQL_TABLE_NAME}', '`');
          SET user_facts_table = TRIM('${user_order_facts.SQL_TABLE_NAME}', '`');

          EXECUTE IMMEDIATE FORMAT("""
            ALTER BI_CAPACITY `my-project.region-us.default`
            SET OPTIONS (
              preferred_tables = ['%s', '%s']
            )
          """, orders_table, user_facts_table);
        END ;;

      sql_step:
        -- 3. Pre-warm both tables in BI Engine RAM
        SELECT COUNT(*) FROM ${order_facts.SQL_TABLE_NAME}
        UNION ALL
        SELECT COUNT(*) FROM ${user_order_facts.SQL_TABLE_NAME} ;;
    }
  }

  dimension: n {
    hidden: yes
  }
}

Aligning datagroup triggers across the DAG​

In a multi-table DAG, make sure the runner view's datagroup_trigger matches the most frequent datagroup in the pipeline.

If order_facts rebuilds hourly while order_facts_bi_engine triggers daily, Looker skips the runner after the hourly build. Consequently, the newly generated order_facts table remains unpinned until the daily trigger fires.

When all views share datagroup_trigger: ecommerce_etl_modified, Looker rebuilds the entire chain in dependency order:

  • Looker materializes order_facts first.
  • Looker materializes user_order_facts second.
  • Looker triggers order_facts_bi_engine last.

This sequence guarantees that every fresh table in the DAG gets pinned and pre-warmed immediately.

What happens if ALTER BI_CAPACITY changes nothing?​

You might wonder whether re-running ALTER BI_CAPACITY with unchanged table lists causes unintended side effects.

The short answer is no. ALTER BI_CAPACITY is completely idempotent.

If you execute ALTER BI_CAPACITY with identical capacity and table options:

  • BigQuery completes the DDL statement as an instantaneous metadata no-op.
  • BigQuery never invalidates or drops existing tables.
  • BigQuery preserves active in-memory cache blocks for tables that remain in the preferred list.

Changing BI Engine capacity never causes Looker to rebuild tables. Looker evaluates rebuild schedules exclusively using your LookML datagroup trigger queries. Looker does not monitor BigQuery BI Engine capacity changes, and BigQuery never instructs Looker to re-run PDT builds.

Standalone SQL derived tables​

If your data pipeline contains only a single SQL derived table without upstream NDTs, you can place create_process directly inside that view.

view: user_order_facts {
  derived_table: {
    datagroup_trigger: ecommerce_etl_modified
    create_process: {
      sql_step:
        CREATE OR REPLACE TABLE ${SQL_TABLE_NAME}
        CLUSTER BY user_id
        AS
        SELECT
          user_id,
          COUNT(DISTINCT order_id) AS lifetime_orders,
          SUM(sale_price) AS lifetime_revenue
        FROM `looker-private-demo.thelook.order_items`
        GROUP BY 1 ;;

      sql_step:
        BEGIN
          DECLARE current_table STRING;
          SET current_table = TRIM('${SQL_TABLE_NAME}', '`');

          EXECUTE IMMEDIATE FORMAT("""
            ALTER BI_CAPACITY `my-project.region-us.default`
            SET OPTIONS (
              preferred_tables = ['%s']
            )
          """, current_table);
        END ;;

      sql_step:
        SELECT COUNT(*) FROM ${SQL_TABLE_NAME} ;;
    }
  }

  dimension: user_id {
    primary_key: yes
    hidden: yes
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension: lifetime_orders {
    type: number
    sql: ${TABLE}.lifetime_orders ;;
  }

  dimension: lifetime_revenue {
    type: number
    value_format_name: usd
    sql: ${TABLE}.lifetime_revenue ;;
  }
}

However, separating data transformation from infrastructure pinning into a dedicated runner view remains the cleaner pattern. A dedicated runner scales smoothly when you add more derived tables.

Preserving pinned tables with procedural SQL​

Use this pattern when you maintain table pins outside of Looker. Because BigQuery permits only one BI Engine reservation per region, all models, teams, and tools in your project share that single default capacity. If your PDT build executes a naive ALTER BI_CAPACITY statement, that statement replaces the entire array and clears external table pins from memory.

To preserve existing tables, retrieve the active list before running ALTER BI_CAPACITY. BigQuery supports multi-statement procedural SQL using BEGIN and END blocks.

You can query INFORMATION_SCHEMA.BI_CAPACITIES to read the current array. Then filter out stale Looker scratch tables, append your active PDT names with backticks stripped, and execute dynamic DDL with EXECUTE IMMEDIATE.

      sql_step:
        BEGIN
          DECLARE updated_tables STRING;

          SET updated_tables = (
            SELECT STRING_AGG(FORMAT("'%s'", table_name), ', ')
            FROM (
              -- 1. Retain existing non-scratch tables from active configuration
              SELECT table_name
              FROM `region-us.INFORMATION_SCHEMA.BI_CAPACITIES`,
              UNNEST(preferred_tables) AS table_name
              WHERE table_name NOT LIKE '%looker_scratch.LR$%'

              -- 2. Include core static warehouse tables
              UNION DISTINCT
              SELECT 'my-project.analytics.dim_customers'

              -- 3. Append the active PDTs at the end of the DAG run
              UNION DISTINCT
              SELECT TRIM('${order_facts.SQL_TABLE_NAME}', '`')
              UNION DISTINCT
              SELECT TRIM('${user_order_facts.SQL_TABLE_NAME}', '`')
            )
          );

          EXECUTE IMMEDIATE FORMAT("""
            ALTER BI_CAPACITY `my-project.region-us.default`
            SET OPTIONS (preferred_tables = [%s])
          """, updated_tables);
        END ;;

The script reads active tables from INFORMATION_SCHEMA.BI_CAPACITIES. It removes expired Looker scratch tables matching looker_scratch.LR$%. It retains permanent tables like dim_customers. It appends both ${order_facts.SQL_TABLE_NAME} and ${user_order_facts.SQL_TABLE_NAME}. Finally, EXECUTE IMMEDIATE runs the ALTER BI_CAPACITY statement with the consolidated array.

Pre-warming the cache​

Declaring a table in preferred_tables reserves priority for that table in memory. However, BigQuery BI Engine uses lazy loading. BI Engine does not load data into memory until a user or application queries the table.

If a PDT rebuilds at night, the first morning dashboard query experiences a cold miss. The user waits while BI Engine reads columns from storage into memory.

Because create_process executes statements in sequence, you can add a warm-up step. Place a lightweight query in a final sql_step after the ALTER BI_CAPACITY step:

      sql_step:
        SELECT COUNT(*) FROM ${order_facts.SQL_TABLE_NAME}
        UNION ALL
        SELECT COUNT(*) FROM ${user_order_facts.SQL_TABLE_NAME} ;;

Using UNION ALL scans and pre-warms multiple tables across your pipeline in a single query. When Looker completes the build, it runs this query. The query forces BI Engine to evaluate the table and load target columns directly into memory. When users open the dashboard later, queries hit a warm memory cache immediately.

Sizing your reservation​

Determining the required BI Engine reservation size prevents memory waste and cache eviction.

When sizing BI Engine, target physical GiB rather than logical GiB as your baseline.

Logical GiB represents raw, uncompressed data. Sizing reservations against logical GiB causes expensive over-provisioning. Physical GiB represents compressed columnar data stored on disk.

BI Engine loads only the specific columns and partitions requested by queries. In addition, BI Engine applies dictionary encoding and compression in RAM. Allocating 1.2 to 1.5 times the physical GiB of your queried columns provides sufficient RAM without over-provisioning.

Follow these two steps to size your reservation:

Check table storage bounds​

Query INFORMATION_SCHEMA.TABLE_STORAGE to inspect the storage footprint of your active PDTs:

SELECT
  project_id,
  table_schema,
  table_name,
  ROUND(total_logical_bytes / POW(1024, 3), 2) AS logical_gib,
  ROUND(total_physical_bytes / POW(1024, 3), 2) AS physical_gib
FROM
  `my-project.region-us.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE
  table_name LIKE 'LR_%'
ORDER BY
  total_logical_bytes DESC;

To map these generated LR_ table names back to your LookML views, open Admin > Persistent Derived Tables in Looker. The admin panel displays the active database table name beside each LookML view name, model, and datagroup status.

Monitor memory usage​

Track live memory consumption using Google Cloud Monitoring metrics under the bigquerybiengine.googleapis.com service:

  • bigquerybiengine.googleapis.com/capacity/used_bytes tracks the memory currently used by cached data.
  • bigquerybiengine.googleapis.com/capacity/total_bytes tracks your total reservation limit.

If used_bytes approaches 100% of your total capacity, or if queries report PARTIAL_INPUT, allocate additional memory in 2 GiB increments.

Can create_process calculate capacity dynamically?​

You might wonder if create_process can query TABLE_STORAGE and compute size_gb automatically during each PDT build.

While BigQuery procedural SQL allows updating size_gb via ALTER BI_CAPACITY, avoid dynamic auto-scaling inside LookML PDT builds for two reasons:

  • BigQuery metadata latency delays INFORMATION_SCHEMA.TABLE_STORAGE updates by several minutes. A newly created PDT often returns zero or null bytes if queried immediately during the build sequence.
  • Altering size_gb inside a single PDT build affects every dashboard in your Google Cloud project. Because BigQuery permits only one BI Engine reservation per region, a calculation mistake can evict cache blocks across other production models.

Keep size_gb as a stable, pre-allocated baseline managed in Terraform or the Google Cloud console. Use LookML create_process exclusively to rotate preferred_tables.

Required IAM permissions​

Running ALTER BI_CAPACITY inside a PDT build requires specific Google Cloud IAM permissions. The service account configured in your Looker database connection must have permission to update reservations.

Grant the following IAM role to the Looker service account on the BigQuery reservation project:

  • roles/bigquery.resourceAdmin

If security policy restricts administrative roles, create a custom IAM role with these permissions:

  • bigquery.bireservations.get
  • bigquery.bireservations.update

This workflow assumes that your connection already supports Persistent Derived Tables. If you need to enable PDTs or configure a scratch schema, see the Looker PDT documentation.

Full working LookML implementation​

Here is the complete, simplified LookML implementation combining the order_facts NDT, the user_order_facts SQL DT, and the order_facts_bi_engine runner explore.

The hidden explore enables manual testing and rebuilding in Development Mode. The dummy table creates an instant zero-byte tracker. Procedural SQL strips backticks before running ALTER BI_CAPACITY, and UNION ALL pre-warms both tables in memory:

# 1. Native Derived Table (Order Facts)
view: order_facts {
  derived_table: {
    datagroup_trigger: ecommerce_etl_modified
    explore_source: order_items {
      column: order_id { field: order_items.order_id }
      column: items_in_order { field: order_items.count }
      column: order_amount { field: order_items.total_sale_price }
      column: user_id { field: order_items.user_id }
      column: created_at { field: order_items.created_raw }
      derived_column: order_sequence_number {
        sql: RANK() OVER (PARTITION BY user_id ORDER BY created_at) ;;
      }
    }
    cluster_keys: ["user_id"]
  }

  dimension: order_id {
    primary_key: yes
    hidden: yes
    type: number
    sql: ${TABLE}.order_id ;;
  }

  dimension: items_in_order {
    type: number
    sql: ${TABLE}.items_in_order ;;
  }

  dimension: order_amount {
    type: number
    value_format_name: usd
    sql: ${TABLE}.order_amount ;;
  }

  dimension: order_sequence_number {
    type: number
    sql: ${TABLE}.order_sequence_number ;;
  }

  dimension: is_first_purchase {
    type: yesno
    sql: ${order_sequence_number} = 1 ;;
  }
}

# 2. SQL Derived Table (User Order Facts)
view: user_order_facts {
  derived_table: {
    datagroup_trigger: ecommerce_etl_modified
    sql:
      SELECT
        user_id,
        COUNT(DISTINCT order_id) AS lifetime_orders,
        SUM(sale_price) AS lifetime_revenue,
        CAST(MIN(created_at) AS TIMESTAMP) AS first_order,
        CAST(MAX(created_at) AS TIMESTAMP) AS latest_order
      FROM `looker-private-demo.thelook.order_items`
      GROUP BY user_id ;;
    cluster_keys: ["user_id"]
  }

  dimension: user_id {
    primary_key: yes
    hidden: yes
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension_group: first_order {
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.first_order ;;
  }

  dimension_group: latest_order {
    type: time
    timeframes: [date, month, year]
    sql: ${TABLE}.latest_order ;;
  }

  dimension: lifetime_orders {
    type: number
    sql: ${TABLE}.lifetime_orders ;;
  }

  dimension: repeat_customer {
    type: yesno
    sql: ${lifetime_orders} > 1 ;;
  }

  dimension: lifetime_revenue {
    type: number
    value_format_name: usd
    sql: ${TABLE}.lifetime_revenue ;;
  }

  measure: average_lifetime_revenue {
    type: average
    value_format_name: usd
    sql: ${lifetime_revenue} ;;
  }
}

# 3. Hidden Explore and Terminal BI Engine Runner
explore: order_facts_bi_engine { hidden: yes } # Used to test in dev mode, remove before pushing to production

view: order_facts_bi_engine {
  derived_table: {
    datagroup_trigger: ecommerce_etl_modified
    create_process: {
      sql_step:
        -- Create a lightweight marker table for Looker to track
        CREATE OR REPLACE TABLE ${SQL_TABLE_NAME} AS
        SELECT NULL AS n FROM UNNEST([]) WHERE 1=2 ;;

      sql_step:
        -- Strip Looker dialect backticks and pin both upstream tables
        BEGIN
          DECLARE orders_table STRING;
          DECLARE user_facts_table STRING;

          SET orders_table = TRIM('${order_facts.SQL_TABLE_NAME}', '`');
          SET user_facts_table = TRIM('${user_order_facts.SQL_TABLE_NAME}', '`');

          EXECUTE IMMEDIATE FORMAT("""
            ALTER BI_CAPACITY `my-project.region-us.default`
            SET OPTIONS (
              preferred_tables = ['%s', '%s']
            )
          """, orders_table, user_facts_table);
        END ;;

      sql_step:
        -- Pre-warm both tables in BI Engine RAM
        SELECT COUNT(*) FROM ${order_facts.SQL_TABLE_NAME}
        UNION ALL
        SELECT COUNT(*) FROM ${user_order_facts.SQL_TABLE_NAME} ;;
    }
  }

  dimension: n {
    hidden: yes
  }
}

Verifying acceleration​

After your PDT builds, verify that BI Engine accelerates incoming queries. Open BigQuery Studio in the Google Cloud console and run this query against INFORMATION_SCHEMA.BI_CAPACITIES:

SELECT
  project_id,
  location,
  size,
  preferred_tables
FROM
  `region-us.INFORMATION_SCHEMA.BI_CAPACITIES`;

Verify that the preferred_tables array includes your active ${SQL_TABLE_NAME}.

Next, run a query from your Looker dashboard or Explore. Inspect the query execution details in INFORMATION_SCHEMA.JOBS:

SELECT
  creation_time,
  job_id,
  query,
  total_bytes_billed,
  bi_engine_statistics.bi_engine_mode,
  bi_engine_statistics.bi_engine_reasons
FROM
  `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
  project_id = 'my-project'
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND statement_type = 'SELECT'
ORDER BY
  creation_time DESC
LIMIT 10;

If BI Engine accelerates the query, bi_engine_mode displays FULL. If unsupported SQL features or memory constraints limit acceleration, bi_engine_mode displays PARTIAL or DISABLED. Inspect the bi_engine_reasons field to diagnose any reported fallback reasons.

Operational tradeoffs​

Pinning tables to BI Engine requires careful capacity management. Consider these operational tradeoffs before deploying this configuration across multiple models:

  • If the combined size of your pinned tables exceeds your reserved memory capacity, BI Engine evicts partitions or falls back to standard slot execution.
  • Because all PDTs in a region share a single default reservation, concurrent ALTER BI_CAPACITY statements can overwrite each other's preferred tables.
  • Offset datagroup rebuild schedules by several minutes, or combine table lists into a single end-of-DAG runner.
  • When you change LookML, Looker automatically drops orphaned scratch tables from BigQuery storage.
  • The procedural pruning filter removes those dropped scratch tables from the reservation array on the next build.