+1 (726) 227-2971
Vistelio

Looker on Snowflake and Databricks: Connection Setup, PDT Schemas, and Cost Controls

Almost every Looker performance article — including several of ours — quietly assumes BigQuery underneath. Plenty of Looker estates do not run on BigQuery. They run on Snowflake, or on Databricks SQL warehouses, or on both after an acquisition. The LookML looks the same, but the levers that actually control speed and spend are completely different: virtual warehouse sizing instead of slot reservations, result cache semantics instead of BigQuery's 24-hour cached results, TRANSIENT tables instead of partition expiration.

This tutorial covers what a Looker developer needs to change when the warehouse is not Google's.

1. Get the connection right before you tune anything

In Admin → Connections, the fields that matter most are the ones people leave at their defaults.

Snowflake. The connection needs a role, a warehouse, a database, and a schema. Create a dedicated LOOKER_ROLE and a dedicated warehouse (for example LOOKER_WH) rather than reusing an ETL warehouse — otherwise a dbt run and a dashboard load fight over the same compute, and you can no longer attribute cost. Prefer key-pair authentication over a password. If your users have Snowflake accounts of their own, OAuth plus user attributes lets you push row-level security down into Snowflake roles instead of duplicating it in LookML.

Databricks. Point Looker at a SQL Warehouse (serverless or pro), never at an all-purpose compute cluster; all-purpose clusters have startup latency and concurrency behaviour that make dashboards feel broken. Use Unity Catalog three-level naming (catalog.schema.table) consistently in your views, and give the service principal USE CATALOG, USE SCHEMA, and SELECT at minimum.

Three settings on the connection page repay attention on any dialect:

  • Max connections. This is a ceiling on concurrent queries from Looker. Too high and you queue inside the warehouse where Looker cannot see it; too low and users queue inside Looker. Start conservative (20–25) and adjust using System Activity queue times.
  • Per-user query timeout / max query cost. Set it. Without a timeout, one accidental cross join keeps a warehouse hot for an hour.
  • SQL Runner and PDT toggles. Only the connections that need to build PDTs should have a temp schema configured.

2. Temp schemas: where PDTs actually live

BigQuery users tend to think of PDTs as free. On Snowflake and Databricks, every PDT build is metered compute writing metered storage, so the temp schema deserves a deliberate setup.

Snowflake. Create the scratch area explicitly and grant it to the Looker role:

CREATE SCHEMA ANALYTICS.LOOKER_SCRATCH;
GRANT ALL PRIVILEGES ON SCHEMA ANALYTICS.LOOKER_SCRATCH TO ROLE LOOKER_ROLE;
GRANT USAGE ON WAREHOUSE LOOKER_WH TO ROLE LOOKER_ROLE;

Then set Temp Database to ANALYTICS.LOOKER_SCRATCH on the connection. Snowflake PDTs are created as transient tables, which means no Time Travel storage and no Fail-safe — that is what you want for derived data, but it also means you cannot recover a dropped PDT. Never point a temp schema at a database that also holds production tables.

Databricks. Create a schema in Unity Catalog (main.looker_scratch) with an explicit managed location, and grant CREATE TABLE, MODIFY, and SELECT on it. Delta tables underneath a PDT will accumulate versions; schedule VACUUM on the scratch schema, or a month of hourly rebuilds becomes a genuine storage line item.

On both dialects, tie every PDT to a datagroup rather than a sql_trigger_value that polls. A polling trigger query runs on a schedule and wakes a warehouse that would otherwise be suspended:

datagroup: nightly_etl {
  sql_trigger: SELECT MAX(loaded_at) FROM analytics.etl_audit ;;
  max_cache_age: "24 hours"
}

view: order_facts {
  derived_table: {
    datagroup_trigger: nightly_etl
    distribution_style: all   # Redshift only — ignored elsewhere
    indexes: ["order_id"]     # ignored on Snowflake/Databricks
  }
}

Note the last two lines: indexes and distribution_style are Redshift-era parameters and do nothing on Snowflake or Databricks. Copying them from an old project is harmless but misleading. What does help on Snowflake is clustering the underlying source tables on the columns your access_filters and date filters use; on Databricks, liquid clustering or Z-ordering on the same columns.

3. Warehouse sizing is a Looker decision now

On BigQuery you tune queries. On Snowflake and Databricks you tune concurrency, and the shape of your Looker traffic drives it:

  • Many small dashboard tiles → a small warehouse with multi-cluster auto-scaling (Snowflake) or a serverless warehouse with a low autostop (Databricks). Scaling out beats scaling up.
  • Heavy nightly PDT builds → a separate, larger warehouse on a second Looker connection used only by the PDT-building model. Two connections against the same database is a completely legitimate pattern and the cheapest fix for "dashboards are slow at 2am".
  • Auto-suspend at 60 seconds on the dashboard warehouse. Snowflake's result cache survives suspension, so a suspended warehouse does not cost you cache hits — only warm-up.

Use Looker's System Activity history explore to get the numbers: median and p95 runtime by dashboard, query count by hour, and the share of queries served from Looker's own cache. Join that against Snowflake's SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY (or Databricks' system.query.history) on the query comment tag Looker injects, and you can attribute warehouse credits to individual dashboards.

4. Two layers of cache, and knowing which one answered

A Looker query on Snowflake can be answered by Looker's cache, by Snowflake's result cache, or by a real scan. Get your max_cache_age deliberately shorter than your data latency expectation and longer than your click rate — 15 minutes to 1 hour is a common landing spot for operational dashboards. Then remember that Snowflake's result cache only hits when the SQL text matches exactly and the role has the same privileges; a Liquid-templated WHERE clause that embeds {{ _user_attributes['tenant_id'] }} produces a different SQL string per tenant, so every tenant pays for their own first scan. If that hurts, materialise a per-tenant PDT instead.

5. Dialect-aware LookML

The usual sources of migration pain:

  • Date functions. DATEDIFF argument order differs between Snowflake and BigQuery, and timestamp_start_of_week_day behaviour depends on the dialect's week definition. Prefer LookML dimension_group: time timeframes over hand-written SQL wherever you can.
  • Identifier casing. Snowflake upper-cases unquoted identifiers. If your ETL created quoted lower-case tables, every sql_table_name needs quoting — pick one convention and enforce it in review.
  • sql_distinct_key and symmetric aggregates. Supported on both dialects, but if your primary keys are strings, hashing costs real CPU. Fix the fan-out with a better join grain rather than paying for symmetric aggregates on every tile.
  • Struct and array columns. Databricks and Snowflake both expose semi-structured data; flatten it in a view or in dbt rather than in a sql: block, so Looker never re-parses JSON at query time.

Where to start

If you inherited a Looker instance on Snowflake or Databricks, do these five things in order: give Looker its own role and warehouse; move the PDT temp schema out of production; set a query timeout; convert sql_trigger_value polling to datagroups; and split PDT builds onto a second connection. That sequence usually cuts warehouse spend noticeably before you touch a single view file.

If you would like a second pair of eyes on a Looker instance running on Snowflake or Databricks, get in touch — a technical audit is usually a two-week engagement and pays for itself in warehouse credits.