+1 (726) 227-2971
Vistelio

Looker on Apache Iceberg: Modeling Open Lakehouse Tables in LookML

Apache Iceberg has quietly become the table format underneath a lot of the data Looker reports on. BigQuery exposes it as BigLake managed Iceberg tables and read-only external Iceberg tables; Snowflake has native Iceberg tables backed by your own object storage; Databricks reads and writes Iceberg through Unity Catalog. From the LookML side the SQL still looks like SQL — which is exactly why teams get surprised when dashboards that worked fine on native tables get slow, or expensive, or quietly stale.

This tutorial walks through what actually changes for a Looker developer when the physical layer is Iceberg, and how to model for it.

1. How Iceberg reaches a Looker connection

Looker does not talk to Iceberg. It talks to an engine that reads Iceberg. The engine you pick determines almost everything about performance and cost.

  • BigQuery + BigLake managed Iceberg tables. Writable, DML-capable, metadata managed by BigQuery, and largely behaving like a native table to the query planner. This is the smoothest path: your existing BigQuery connection in Looker needs no change, and PDTs can be written normally into a standard dataset.
  • BigQuery external Iceberg tables (read-only). Defined over a metadata file or through a catalog such as AWS Glue or BigLake Metastore. These are read-only, so PDTs cannot be written back into them, and some accelerations (notably BI Engine) do not apply.
  • Snowflake Iceberg tables. Either Snowflake-managed (Snowflake is the catalog, full DML, near-native performance) or externally managed via a catalog integration (read-only, and the metadata refresh cadence is yours to manage).
  • Databricks / Unity Catalog. Managed tables readable as Iceberg, plus foreign catalogs for external Iceberg data.

Practical check before you model anything: in the Looker connection's admin page, run Test and then confirm the connection's temp/PDT schema is a writable, non-Iceberg-external location. A read-only Iceberg catalog cannot hold scratch schemas, and this is the single most common failure when a team first points Looker at a lakehouse.

-- Sanity check from Looker's SQL Runner, using the same credentials Looker uses
SELECT COUNT(*) FROM analytics_iceberg.orders WHERE order_date >= CURRENT_DATE - 7;

If that scan takes tens of seconds for a week of data, the table is almost certainly under-partitioned or suffering from small-file churn — fix that in the pipeline, not in LookML.

2. Model the partition, not just the timestamp

Iceberg uses hidden partitioning: the table declares a partition transform (days(order_ts), bucket(16, customer_id)) and the engine prunes using metadata rather than a physical partition column you must filter on. In theory you get pruning for free. In practice the engine only prunes when the predicate is a plain, non-transformed comparison on the source column.

That is a LookML modeling problem, because Looker generates predicates from your dimension SQL.

dimension_group: order {
  type: time
  timeframes: [raw, date, week, month, quarter, year]
  sql: ${TABLE}.order_ts ;;
  # good: predicates land on order_ts directly and prune Iceberg manifests
}

dimension: order_month_string {
  type: string
  sql: FORMAT_TIMESTAMP('%Y-%m', ${TABLE}.order_ts) ;;
  # bad as a filter target: wraps the partition column and defeats pruning
}

Rules that hold across all three engines:

  • Never wrap the partition source column in a function inside a dimension you expect people to filter on. Expose a clean dimension_group and let timeframes do the work.
  • If a bucket transform exists on a join key, keep the join predicate on the raw column so the engine can use the bucket layout.
  • Push a default date window into the Explore so nobody accidentally scans the whole history:
explore: orders {
  always_filter: {
    filters: [orders.order_date: "last 90 days"]
  }
  sql_always_where: ${orders.order_date} >= '2023-01-01' ;;
}

On a partitioned native BigQuery table you can also use require_partition_filter; with Iceberg, the LookML-side guardrail is usually your only enforcement, so write it explicitly.

3. Metadata columns are a gift — expose them

Iceberg carries per-row metadata that is genuinely useful for analysts and invaluable for debugging. Depending on the engine you can surface things like the file path, snapshot id, or last-modified time.

dimension: source_file {
  hidden: yes
  sql: ${TABLE}._FILE_NAME ;;
}

dimension: snapshot_id {
  type: number
  description: "Iceberg snapshot this row was read from. Use when reconciling a number against the pipeline run."
  sql: ${TABLE}.snapshot_id ;;
}

A small orders_freshness view over the table's snapshot history, joined to nothing and surfaced on a dashboard tile, answers the "is this dashboard current?" question that otherwise becomes a support ticket for your team.

4. Time travel, and why you should keep it out of the main Explore

Iceberg's snapshot model makes point-in-time queries trivial at the SQL level (FOR SYSTEM_TIME AS OF, AT(TIMESTAMP => ...), depending on the engine). It is tempting to expose that as a Looker filter. Resist putting it on the core Explore — a time-travel predicate changes which snapshot every join reads and will quietly break symmetric aggregates and caching assumptions.

Instead, isolate it in a dedicated Explore built from a parameterized derived table:

view: orders_as_of {
  derived_table: {
    sql:
      SELECT * FROM analytics_iceberg.orders
      FOR SYSTEM_TIME AS OF TIMESTAMP('{% parameter as_of_ts %}')
    ;;
  }

  parameter: as_of_ts {
    type: unquoted
    default_value: "2026-01-01 00:00:00"
  }
}

Label that Explore clearly ("Orders — Audit / As Of") and keep it out of the folders business users live in. Auditors love it; dashboard authors should never stumble into it.

5. PDTs, incremental builds, and small files

LookML PDTs on a lakehouse behave the same as anywhere else, with two caveats:

  1. Write PDTs to a native or managed schema, not into an externally managed Iceberg catalog. Keep the scratch schema on the engine's own storage.
  2. Incremental PDTs create small files. An hourly increment_key on an Iceberg-backed output produces many tiny data files and a long manifest list, and read performance degrades steadily. Either widen the increment interval or make sure table maintenance (compaction / OPTIMIZE) runs on a schedule.
derived_table: {
  sql: SELECT ... FROM analytics_iceberg.orders WHERE {% incrementcondition %} order_ts {% endincrementcondition %} ;;
  increment_key: "order_ts"
  increment_offset: 1
  datagroup_trigger: iceberg_orders_snapshot
}

6. Trigger caching on the snapshot, not on the clock

The best thing about Iceberg for a Looker developer is that freshness is knowable. Instead of guessing with sql_trigger_value: SELECT CURRENT_DATE, trigger on the table's newest snapshot so caches invalidate exactly when the data actually changes:

datagroup: iceberg_orders_snapshot {
  sql_trigger: SELECT MAX(committed_at) FROM `project.analytics_iceberg.INFORMATION_SCHEMA.SNAPSHOTS` WHERE table_name = 'orders' ;;
  max_cache_age: "6 hours"
}

(The exact metadata source differs per engine — Snowflake and Databricks expose equivalent snapshot/history views. The pattern is what matters.) This one change typically does more for lakehouse dashboard cost than any amount of SQL tuning: you stop rebuilding on a timer and start rebuilding on a commit.

7. Cost traps specific to open lakehouses

  • Metadata scans are not free. Thousands of manifest files turn a "cheap" SELECT MAX(date) into a slow planning step. Watch planning time in Looker's System Activity history.query_runtime alongside execution time.
  • Federated / external legs bypass accelerators. BI Engine will not accelerate an external Iceberg read; materialize a PDT or an aggregate table for the tiles that need sub-second response.
  • Aggregate awareness still works. An aggregate_table built on native storage over an Iceberg fact table is one of the highest-leverage optimizations available, because it moves the hot path off the object store entirely.
  • Storage billing is yours now. With customer-managed Iceberg the object storage bill sits outside the warehouse invoice, so warehouse spend can look flat while total cost rises. Report on both.

Checklist before you ship

  • Connection's PDT scratch schema is writable and not external.
  • Every Explore over an Iceberg fact table has a date guardrail.
  • Filterable date dimensions reference the partition source column unwrapped.
  • Datagroups trigger on snapshot metadata, not on a clock.
  • At least one aggregate table or PDT serves the highest-traffic dashboard.
  • Compaction/maintenance is scheduled and owned by someone named.

Iceberg does not change LookML syntax. It changes which LookML choices are expensive. Model the partition honestly, cache off the snapshot, and keep the scratch layer on native storage — that covers the large majority of lakehouse performance problems we see in Looker projects.

If your Looker instance is moving onto an Iceberg-backed lakehouse and dashboards have started to drag, get in touch — a short technical audit usually finds the two or three models doing the damage.