Looker uses LookML — a modelling language that sits between your data warehouse and end-user queries — to define dimensions, measures, and joins in version-controlled YAML-like files. This guide covers LookML Explores, views, joins, dimension and measure types, derived tables, and how the modelled semantic layer enables governed self-service analytics.
What LookML Is
LookML (Looker Modeling Language) is a declarative language for defining data models in Looker. Instead of embedding business logic in individual reports or BI tool calculated fields, LookML centralises logic in version-controlled model files. Every dimension, measure, join, and filter is defined once and reused across all reports.
The result: business users can explore data and build their own charts without the risk of inconsistent metric calculations — the metric definitions are locked in the LookML model and enforced uniformly. This is the fundamental difference between Looker and most other BI tools: the semantic layer is not optional, it is the architecture.
LookML Concepts
**Project**: The top-level container for LookML files. One project per Looker instance is typical; some organisations use multiple projects for separation of concerns. Projects connect to a database connection defined in Looker admin.
**Model**: A model file (.model.lkml) defines which explores are available and which database connection they use. One model per subject area — sales_analytics.model.lkml, product_analytics.model.lkml.
**View**: A view file (.view.lkml) defines the columns available from one table (or derived table). Views contain dimension and measure definitions — not data, just metadata about how to query data.
**Explore**: An explore is an entry point for querying — it defines which view is the primary table and which other views can be joined to it. Users see explores in the Looker interface; an explore called "Orders" gives users access to order-level dimensions and measures.
**Dimension**: A column or computed attribute that can be used to filter or group. Corresponds to GROUP BY or WHERE in SQL.
**Measure**: An aggregation. COUNT, SUM, AVG, MAX, MIN, or a custom calculation. Corresponds to aggregate functions in SQL.
View File Structure
A view file declares dimensions and measures for one table:
view: orders {
sql_table_name: analytics.fact_orders ;;
dimension: order_id {
type: string
primary_key: yes
sql: order_id ;;
}
dimension: order_date {
type: date
sql: order_date ;;
}
dimension: status {
type: string
sql: status ;;
}
dimension: order_amount {
type: number
sql: order_amount ;;
value_format_name: usd
}
measure: count {
type: count
drill_fields: [order_id, order_date, status]
}
measure: total_revenue {
type: sum
sql: order_amount ;;
value_format_name: usd
}
measure: average_order_value {
type: average
sql: order_amount ;;
value_format_name: usd
}
}
The sql: field contains the SQL expression for each dimension — the raw column name, a CASE WHEN expression, a date truncation, or any other valid SQL. LookML generates the full SELECT statement from these definitions at query time.
Dimension Types
Dimension type controls how Looker handles the field:
- type: string — text field, used for filtering and grouping
- type: number — numeric field available for calculations
- type: date — date handling with automatic date group and timeframe support
- type: datetime — timestamp with time components
- type: yesno — boolean, filters as Yes/No
- type: tier — bucket a number into ranges: tiers: [0, 100, 500, 1000]
- type: duration — compute duration between two timestamp dimensions
**Date dimension groups**: Declare a dimension group to automatically generate date_date, date_week, date_month, date_quarter, date_year sub-dimensions from a single timestamp column:
dimension_group: created {
type: time
timeframes: [date, week, month, quarter, year, raw]
sql: created_at ;;
}
Users get created_date, created_week, created_month, etc. — automatic date truncation without writing each separately.
Measure Types
- type: count — COUNT(*) by default; add filters for conditional counts
- type: count_distinct — COUNT(DISTINCT column)
- type: sum — SUM(column)
- type: average — AVG(column)
- type: max / min — MAX or MIN
- type: number — custom calculation referencing other measures
Custom calculated measure:
measure: conversion_rate {
type: number
sql: orders_with_payment_count / NULLIF(total_count, 0) ;;
value_format_name: percent_2
}
Explores and Joins
An Explore joins views together:
explore: orders {
label: "Order Analysis"
join: customers {
type: left_outer
sql_on: orders.customer_id = customers.id ;;
relationship: many_to_one
}
join: products {
type: left_outer
sql_on: order_line_items.product_id = products.id ;;
relationship: many_to_one
}
}
relationship specifies the cardinality: many_to_one, one_to_many, one_to_one, many_to_many. Looker uses relationship to determine whether COUNT measures need fanout correction — critical for accurate aggregation when joining one-to-many relationships.
Derived Tables
When you need to query against a pre-aggregated or transformed result rather than a raw table, use a derived table. LookML supports two types:
**SQL-based derived table**: Write SQL directly in the view file.
view: customer_order_summary {
derived_table: {
sql:
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(order_amount) AS lifetime_value,
MIN(order_date) AS first_order_date
FROM analytics.fact_orders
GROUP BY customer_id
;;
}
dimension: customer_id { ... }
measure: average_lifetime_value {
type: average
sql: lifetime_value ;;
}
}
**Persistent derived tables (PDTs)**: Looker materialises the derived table query into the warehouse as a physical table, refreshing on a defined schedule. Useful for expensive aggregations that would otherwise be re-computed on every user query.
LookML Parameters for Governance
**Access filters**: Enforce row-level security via user attributes. Define a user attribute (e.g., region) in Looker admin, assign per user, then reference in the view:
access_filter: {
field: orders.region
user_attribute: user_region
}
Every query against this explore automatically receives a WHERE orders.region = '...' filter based on the viewing user's attribute — no RLS configuration needed in the warehouse.
**Required access grants**: Restrict explore or field access to specific user groups:
required_access_grants: [finance_team]
Fields with this setting are hidden from users who are not members of the finance_team access grant.
LookML Development Workflow
LookML is stored in a Git repository connected to Looker. Developers work in Looker's development mode — changes are isolated to the developer's branch and visible only to them. When ready, they create a pull request, get peer review, and merge to production. The production LookML is deployed to end users.
This Git-native workflow means:
- All changes are version controlled with author and timestamp
- Rollback to any prior state is possible
- Code review enforces quality before deployment
- Multiple developers can work on separate branches simultaneously
Our BI strategy practice designs and builds LookML models for enterprise Looker deployments — contact us to discuss your Looker architecture requirements.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →