The dbt, BigQuery, Looker Stack: A Reference Architecture

The reference stack on Google Cloud fits in one sentence: dbt turns raw data into tested fact and dimension tables in BigQuery, and LookML exposes those tables to users as governed Explores in Looker. Each layer has a precise job, and most performance, cost and trust problems come from logic placed on the wrong floor. This guide covers who does what, the conventions that prevent regrets, and a minimal architecture that holds for a two-person team as well as a group.

The three layers and their contract

Layer	Tool	Responsibility	What it should not do
Storage and compute	BigQuery	Store, run queries, partition, cluster	Carry business logic in ad hoc, unversioned views
Transformation	dbt	Clean, join, historise, test; produce tables at the right grain	Format for display, manage user permissions
Semantics and access	Looker (LookML)	Define measures, Explores, row-level security, caching	Transform data (complex joins, deduplication, history)

The most useful rule of thumb: if a piece of logic must be true for every tool, including outside Looker, it belongs in dbt. If it concerns how a human or an agent queries the data, it belongs in LookML. Customer deduplication goes in dbt. A per-region access_filter goes in LookML. A "net revenue" measure is declared once in LookML, on amount columns that dbt has made explicit.

The flow, from raw to screen

Ingestion. Sources land in BigQuery raw, one dataset per source (raw_shopify, raw_hubspot, raw_ga4), through BigQuery Data Transfer, Fivetran, Airbyte or native exports. Nobody queries these datasets directly.

Staging (dbt). One model per source table, prefixed stg_, that renames, casts and does nothing else. Materialised as a view. This is the only layer that knows the source column names.

Intermediate (dbt). int_ models for joins and reusable business rules (attaching an order to a canonical customer, deriving a status). Often ephemeral or views.

Marts (dbt). Fact tables (fct_orders, fct_sessions) and dimensions (dim_customers, dim_products), materialised as tables, partitioned by date and clustered on frequent filter keys. This is what Looker reads.

Semantics (LookML). One LookML view per mart table, Explores per domain, named and described measures, datagroups aligned with the end of the dbt run.

Consumption. Looker dashboards, Explores for analysts, Looker Studio through the Looker connector for broad distribution, Conversational Analytics agents, the API for integrations.

Naming conventions that prevent regrets

They look secondary until the day forty models and two hundred fields exist.

dbt. stg_<source>__<table>, int_<domain>_<action>, fct_<event>, dim_<entity>. Keys as <entity>_id, always the same type. Amounts suffixed _amount, counts _count, booleans prefixed is_ or has_. Dates as _date, timestamps as _at. One currency in the marts; conversion happens upstream.

LookML. The view carries the table name (fct_orders becomes the orders view via sql_table_name). Dimensions keep the column names untranslated; translation lives in label. Measures follow <quantity>_<aggregate> or an explicit business name: net_revenue, order_count, average_order_value. One group_label per family of fields.

BigQuery datasets. raw_* for sources, staging, intermediate, marts (or analytics) for dbt, and a dedicated looker_scratch dataset for PDTs with automatic table expiration.

Tests: where and which

In dbt, on every run. unique and not_null on every primary key, relationships between facts and dimensions, accepted_values on statuses, and one reconciliation test per key metric: the sum of fct_orders.invoiced_amount for the closed month must match the finance total within a tolerance. That last test is the one protecting the executive dashboard's credibility.

In Looker, on every PR. The LookML Validator (syntax and references), the Content Validator (dashboards broken by a rename), and an execution test of the critical Explores through the API or a tool such as Spectacles. A PR that breaks an executive dashboard must not be mergeable.

At the boundary. The primary_key declarations in LookML must match dbt's unique tests. When a mart changes grain, the dbt PR and the LookML PR ship together.

CI: the minimum that holds

One dbt repository and one LookML repository (or a monorepo with two folders), each with continuous integration.

dbt side. On every PR: dbt build --select state:modified+ against an ephemeral CI schema, with tests. On merge: deploy, scheduled run (Cloud Composer, Cloud Run Jobs, dbt Cloud or GitHub Actions depending on size). At the end of the run, a meta.load_log table receives a timestamp.

Looker side. The project is connected to Git with mandatory PRs. A datagroup reads the log table:

datagroup: dbt_daily {
  sql_trigger: SELECT MAX(finished_at) FROM `dwh.meta.load_log` WHERE status = 'success' ;;
  max_cache_age: "24 hours"
}

Looker's cache clears exactly when dbt has delivered new data, neither before (wasted queries) nor after (stale data).

Cost control

BigQuery bills on bytes processed or on capacity. On this stack, four settings account for most of the bill.

Partition and cluster the marts in dbt (partition_by on the event date, cluster_by on filter keys). An Explore filtered on date only reads the relevant partitions.

Force the partition filter in LookML with conditionally_filter or always_filter on Explores over large tables, and declare partition_keys on PDTs.

Materialise aggregates once, in dbt (a fct_daily_sales table by day and product) rather than letting thirty tiles recompute the same sum. Looker's aggregate_table complements this for dashboard-specific rollups.

Measure. INFORMATION_SCHEMA.JOBS filtered on the Looker service account and the dbt account, once a month. The twenty most expensive queries show where to act. Without this measurement nobody knows whether the optimisation worked.

An often-forgotten setting: a looker_scratch dataset with seven-day table expiration, so abandoned PDTs do not pile up.

A minimal architecture

For a two-person team and five sources, the following is enough and takes four to six weeks to stand up.

• One Google Cloud project, BigQuery on-demand pricing to start, moving to reserved slots beyond roughly a hundred TiB processed per month.
• BigQuery Data Transfer for Google Ads and GA4, a managed connector for the CRM and the e-commerce platform.
• dbt Core scheduled by a daily Cloud Run Job, or dbt Cloud if nobody wants to own scheduling. Around fifteen models to begin with.
• Looker (Google Cloud core) Standard edition, one LookML project, three Explores (sales, customers, acquisition), one datagroup.
• One Git repository per tool, GitHub Actions for dbt CI, mandatory PRs on the Looker side.
• Looker Studio, through the Looker connector, for fixed reports with wide distribution.

What gets added as the organisation grows: separate dbt environments (dev, CI, prod), a data catalogue, dbt source freshness tests, row-level security in LookML, and Conversational Analytics agents on the cleaned Explores.

What we see in production: in a large industrial group, Looker and BigQuery have served the executive dashboards for several years on top of a governed semantic layer. The most expensive incidents came from logic on the wrong floor: history tracking done in a Looker PDT instead of dbt, impossible to test, rebuilt nightly over three years of data. Moving it fixed both the cost and the trust.

FAQ

Do we need dbt if we already have Looker derived tables?

PDTs are convenient for a dashboard-specific aggregate. Once they carry complex joins, history or logic other tools need, they become hard to test and expensive to rebuild. dbt exists for that, with tests and CI. The transition happens one mart at a time.

Where should metrics be defined, dbt or LookML?

Only one of the two layers should be exposed to users. On a Looker stack, LookML carries the measures and Explores, and dbt guarantees explicit, tested amount columns. If tools other than Looker must consume the same metrics, dbt's metrics layer (MetricFlow) becomes an option, provided definitions are not duplicated.

Can this stack run without a data engineer?

One person comfortable with SQL and Git can set it up and maintain it at this scale, at two to three days per month once in production. Managed scheduling (dbt Cloud, Cloud Run Jobs) avoids having to operate an Airflow.

What is the first sign that logic is on the wrong floor?

A Looker PDT that takes more than ten minutes to rebuild, or a dbt model containing columns formatted for display. In the first case the logic moves up into dbt; in the second it moves down into LookML.

Next step

If your stack already has the three layers but the numbers contradict each other or the BigQuery bill keeps rising, the problem is almost always logic on the wrong floor or a duplicated definition. Five weeks is enough to put the marts and the measures back where they belong.

See the BigQuery, dbt, Looker semantic layer service, and read next: Why your numbers disagree and The LookML audit checklist.