LookML Guide: Writing and Organising Looker Data Models

LookML is Looker's proprietary modeling language — a layer that translates raw database tables into the business-friendly dimensions and measures that analysts query through Looker's Explore interface. Instead of every analyst writing their own SQL, LookML centralises the business logic: revenue is defined once in LookML and every Explore query uses that definition. This guide covers the core LookML objects, project structure, and the best practices that keep models maintainable.

The LookML abstraction

When an analyst uses Looker's Explore, they select dimensions and measures from a panel without writing SQL. Looker generates SQL from those selections and sends it to the data warehouse. LookML is what teaches Looker how to generate that SQL.

The LookML layer defines:

- Which tables exist (Views)

- Which columns are dimensions (group-by fields) vs measures (aggregate fields)

- How tables relate to each other (Explores and joins)

- What SQL each dimension and measure generates

- What filters are always applied

- Access controls for specific fields

Without LookML, Looker cannot generate queries. The quality of the LookML model determines the quality of the self-service analytics experience.

Core LookML objects

**Views**: A view maps to a database table (or a derived table). Views contain the dimension and measure definitions for that table. A view file defines: the name, the SQL TABLE NAME (or derived table SQL), and the fields.

Dimensions are the attributes that analysts group by — customer_name, order_date, product_category. Each dimension has a type (string, date, number), an SQL expression that generates the column reference, and a label shown in the UI.

Measures are the aggregations — total_revenue, order_count, average_order_value. Each measure has a type (sum, count, average, max, min) and an SQL expression. For SUM and AVG, the SQL specifies the column to aggregate.

**Explores**: An Explore is the entry point for an analysis — it is what appears in the Explore menu. An Explore is defined in a model file and specifies: the base view, any joined views, join conditions, join types, and relationship types. Most Explores join one fact view to multiple dimension views.

**Models**: A model file groups Explores and specifies which connection to query. A LookML project typically has one or a few model files, each targeting a different database connection or a different business domain.

Derived tables

A derived table (DT) is a view backed by a SQL query rather than a database table. The SQL is specified in the derived_table block and is executed as a subquery when the view is queried. Two types:

**Persistent derived tables (PDT)**: The DT SQL is executed and the result is materialised as a physical table in the database. Refreshed on a schedule. Used for expensive aggregations that would be slow to compute on every Explore query.

**Ephemeral derived tables**: The DT SQL is inlined as a subquery at query time. No materialisation. Appropriate for simple transformations that are fast to execute.

PDTs require a scratch schema in the database where Looker writes the materialised tables. The PDT is invalidated and rebuilt when the trigger condition (a sql_trigger_value or a cron schedule) fires.

Liquid templating in LookML

LookML supports Liquid templating — a templating language that allows dynamic SQL generation based on user attributes, parameters, or context. Liquid is used in:

**User attribute-based row-level security**: The SQL expression for a dimension or filter can reference user attributes (set in Looker Admin for each user or group). A filter applied to every Explore can restrict rows based on the authenticated user's region attribute — implementing row-level security without database-level enforcement.

**SQL substitutions**: Liquid parameters (user-defined inputs to a query) can be injected into SQL expressions. A date parameter that changes the time window of a calculation. A currency parameter that changes which exchange rate column is used.

LookML project structure best practices

**One view per source table**: Each database table or derived table gets its own view file. Do not combine multiple tables in one view.

**Separate model files by business domain**: A model file defines which Explores are available to users connected to that model. Create separate models for Finance, Marketing, and Operations if those domains have different users, different explores, and should be governed separately.

**Consistent naming conventions**: Field names in LookML become the labels analysts see. Use business-friendly names (Total Revenue, not sum_revenue). Use underscores, not spaces, in technical names. Be consistent across views — customer_id in one view should not be cust_id in another.

**Descriptive field documentation**: Add description fields to every dimension and measure. The description appears in Looker's field picker as a tooltip. This is the primary in-context documentation for analysts.

**Always filter aggregate measures by view primary key**: Looker generates COUNT DISTINCT and SUM operations. Without proper type specifications, symmetric aggregations can produce incorrect results for fanout joins. Use count and sum_distinct types correctly, and test aggregations against known values.

LookML vs dbt: complementary roles

LookML and dbt serve different purposes in the analytics stack. dbt transforms raw data into clean, structured models in the warehouse — the silver and gold layers. LookML sits on top of those dbt models and defines the business-friendly semantic layer for Looker's self-service interface.

The typical pattern: dbt builds fact and dimension tables from raw data. LookML views point at those dbt models. Looker Explores join the LookML views and expose dimensions and measures to analysts.

dbt is the transformation layer; LookML is the semantic layer. They are not alternatives — they are complements.

For the semantic layer context, see semantic layer tools and looker vs tableau. Our data architecture consulting practice designs and implements Looker deployments including LookML model architecture — book a scoping call to discuss your Looker requirements.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.