In LookML, how do dimensions and measures differ in the SQL Looker generates?

## The distinction is a SQL distinction LookML fields are not two flavours of the same thing. A dimension and a measure land in different clauses of the SQL Looker writes, and that is the whole story. Take a small view: ``` view: orders { sql_table_name: public.orders ;; dimension: id { primary_key: yes type: number sql: ${TABLE}.id ;; } dimension: status { type: string sql: ${TABLE}.status ;; } measure: count { type: count } measure: total_amount { type: sum sql: ${TABLE}.amount ;; } } ``` Selecting `status`, `count` and `total_amount` produces roughly: ```sql SELECT orders.status AS status, COUNT(*) AS count, SUM(orders.amount) AS total_amount FROM public.orders AS orders GROUP BY 1 ``` The dimension appears twice — projected and grouped. The measures appear once each, wrapped in an aggregate. Add a second dimension and the grain gets finer: more groups, and every measure re-evaluates within each of them. ## Dimensions: row-level expressions A dimension's `sql:` must be valid at the level of a single row. `${TABLE}` is the placeholder Looker substitutes with the alias it gave this view's table in the current query, which is why you write `${TABLE}.status` rather than hard-coding the table name — the same view may be joined under different aliases. Dimensions carry a `type:` that tells Looker how to render and filter them: `string`, `number`, `yesno`, `date`, and the special `dimension_group` with `type: time`, which expands one timestamp column into a family of fields (raw, date, week, month, quarter, year) selected via `timeframes:`. A dimension can compute: `sql: ${TABLE}.first_name || ' ' || ${TABLE}.last_name ;;` is a perfectly ordinary dimension, and so is a `CASE` expression bucketing a numeric column. ## Measures: aggregates over the group A measure's `type:` selects the aggregate. `type: count` counts rows and takes no `sql:` parameter at all — Looker writes the count itself, which matters because it lets Looker substitute `COUNT(DISTINCT primary_key)` when a join has fanned the rows out. `type: sum`, `type: average`, `type: min`, `type: max` and `type: count_distinct` take a `sql:` parameter holding the *unaggregated* expression to aggregate: you write `sql: ${TABLE}.amount ;;`, not `sql: SUM(${TABLE}.amount) ;;`. `type: number` is the escape hatch for a computed measure, and it is where hand-written aggregation is allowed: ``` measure: average_order_value { type: number sql: 1.0 * ${total_amount} / NULLIF(${count}, 0) ;; value_format_name: usd } ``` Here `${total_amount}` and `${count}` reference other measures, so the expression is a ratio of two aggregates — the correct way to build a rate, because computing it row-by-row and averaging would give a different (usually wrong) number. Measures also accept a `filters:` parameter, which restricts the rows an individual measure aggregates without filtering the whole query: ``` measure: completed_revenue { type: sum sql: ${TABLE}.amount ;; filters: [status: "complete"] } ``` That compiles to a conditional aggregate, letting one row of results carry both filtered and unfiltered totals. ## The reference rules follow from the clauses - A measure may reference a dimension: the aggregate wraps a row-level expression. Legal and common. - A measure may reference another measure, as in the ratio above. - A dimension may **not** reference a measure. There is no row-level meaning for a value that only exists per group, and Looker rejects it. - `${other_view.field}` in either kind of field pulls another view into the query, so Looker adds that join. This is easy to do accidentally and turns a single-table query into a joined one, which changes both performance and — if the join fans out — the numbers. ## Why interviewers ask it Because the mistake it prevents is expensive. Candidates who think of a measure as "a number field" write `type: number` measures containing hand-rolled `SUM()`, which sidesteps the protections Looker applies to typed aggregates across joins. Others expect a filter on a measure to behave like a filter on a dimension, and are surprised it constrains groups rather than rows. Knowing which clause each field type reaches is what makes the rest of LookML predictable.