Looker semantic models

On Wednesday, November 4, Ravi introduces Mateo to Birchline's Looker instance, which Finance and the executive team use for monthly reporting. The store manager dashboard in Data Studio is quick to change, but Lena has noticed that "net sales" in Finance's Looker dashboards differs slightly from the figure in the analytics team's reports. The cause turns out to be a single difference in how gift card sales are treated. The fix, and the reason Looker exists, is a semantic model: one place where every important metric is defined, reviewed and reused.

What a semantic model is

A semantic model, sometimes called a semantic layer, sits between the warehouse tables and the people who ask questions. It translates database columns into business terms, defines how each measure is calculated, says how tables join, and controls who sees what. Users then explore by choosing fields such as "Store region" and "Net sales", and the tool writes the SQL. Because every query is generated from the same definitions, two people asking the same question get the same answer.

In Looker, the semantic model is written in LookML, a text language stored in files and managed with Git. Looker does not copy the data: it generates SQL from the model and runs it in BigQuery, so freshness and cost follow the warehouse.

Views, dimensions and measures

A LookML view usually corresponds to one table or derived table. It names the table with sql_table_name and defines fields. A dimension is a field to group or filter by; a dimension group of type time creates a set of date fields at once, such as date, week and month. A measure is an aggregate such as sum, count, count_distinct or average.

view: sales {
  sql_table_name: `bl-data-prod.curated.sales` ;;

  dimension: sale_line_id {
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.sale_id ;;
  }
  dimension: channel {
    type: string
    sql: ${TABLE}.channel ;;
  }
  dimension_group: sale {
    type: time
    timeframes: [date, week, month, year]
    datatype: date
    sql: ${TABLE}.sale_date ;;
  }
  measure: net_sales {
    type: sum
    sql: ${TABLE}.net_sales ;;
    value_format_name: usd_0
    label: "Net sales"
    description: "Sales after discounts, with refunds as negative values. Excludes gift card sales, which are not revenue until redeemed."
  }
  measure: transactions {
    type: count_distinct
    sql: ${TABLE}.txn_id ;;
  }
}

The gift card difference is settled in the measure's description and its SQL. Finance treats gift card sales as a liability until the card is used, so the governed definition excludes them; the analytics team's view had included them. Once the LookML measure is changed to filter out the gift card SKU group and reviewed, every Looker dashboard and every explore uses the corrected figure the next time it runs. Ravi then changes v_store_daily in BigQuery to match, so Data Studio agrees too.

Explores and joins

An explore is the starting point users choose when they ask a question, such as "Sales". It is defined in a model file, which also names the database connection. The explore lists the views that can be joined to the base view, the join conditions and the relationship between them.

connection: "bigquery_prod"
include: "/views/*.view.lkml"

explore: sales {
  label: "Sales"
  join: stores {
    type: left_outer
    relationship: many_to_one
    sql_on: ${sales.store_id} = ${stores.store_id} ;;
  }
  join: products {
    type: left_outer
    relationship: many_to_one
    sql_on: ${sales.sku} = ${products.sku} ;;
  }
}

The relationship parameter is not decoration. Many sales rows belong to one store, so the relationship is many_to_one. Looker uses relationships and primary keys to apply symmetric aggregates: when a join could fan out, as described in lesson 3, Looker writes SQL that sums each row of a table only once, using its primary key. This is one of Looker's most valuable features, because it makes fan-out errors much harder to produce by accident. It only works if every view has a correct primary key.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE stores (store_id INT PRIMARY KEY, region TEXT, target REAL);
INSERT INTO stores VALUES (12, 'East', 50000), (15, 'East', 70000);
CREATE TABLE sales (sale_id INT PRIMARY KEY, store_id INT, net_sales REAL);
INSERT INTO sales VALUES (1, 12, 200), (2, 12, 300), (3, 15, 400), (4, 15, 100), (5, 15, 250);
""")
naive = """SELECT SUM(st.target) FROM sales s JOIN stores st USING (store_id)"""
# Symmetric aggregate idea: add each store's target once, identified by its primary key
symmetric = """SELECT SUM(target) FROM (
                 SELECT DISTINCT st.store_id, st.target
                 FROM sales s JOIN stores st USING (store_id))"""
print("true total target:", db.execute("SELECT SUM(target) FROM stores").fetchone()[0])
print("naive sum after join:", db.execute(naive).fetchone()[0])
print("summed once per store:", db.execute(symmetric).fetchone()[0])

true total target: 120000.0
naive sum after join: 310000.0
summed once per store: 120000.0

The naive sum gives 310,000 dollars because store 12's target is counted twice and store 15's three times. The true total is 120,000. A user who asks Looker for "total target by region" from the Sales explore gets 120,000, because the stores view declares store_id as its primary key and the join as many_to_one.

Adjusting simple LookML parameters

The exam expects an associate practitioner to make simple changes to an existing model, not to design one from scratch. The parameters below cover most such changes.

Mateo's first change is small. Store managers find the measure called "transactions" confusing, so he adds label: "Baskets" and a description, and creates a filtered measure for store-channel sales using the filters parameter. He makes the change in Development Mode, where his edits affect only his own view of the model. Looker's LookML validator checks the syntax and references; he tests the new fields in an explore; then he commits the change to Git and opens a pull request. Ravi reviews it, and only after it is merged and deployed to production do other users see the new label.

Derived tables and caching

Sometimes the right view is not a table but a query. A derived table is a view defined by SQL in LookML. A persistent derived table (PDT) is written to the database as a table and rebuilt on a schedule or when a trigger changes, which suits expensive summaries. Many teams now prefer to build such tables in BigQuery with scheduled queries or Dataform, as in lesson 5, and point a plain view at them, so the logic is available outside Looker too. Looker also caches query results according to datagroups, which can be tied to the warehouse's load schedule so that cached results expire when new data arrives.

Looker as a semantic layer for other tools

Looker's definitions are not only for Looker dashboards. Data Studio can connect to Looker explores through a connector, so a Data Studio report can use governed measures while keeping Data Studio's flexible design. Connected Sheets can also use Looker models, and Looker's SQL interface lets other BI tools query the model. Birchline plans to use this for the store manager dashboard: the layout stays in Data Studio Pro, while net sales and the return rate come from the governed Looker model, so managers and Finance see the same numbers.

Working with an existing model

Most associate-level LookML work starts in someone else's model, so Ravi shows Mateo how to find his way around. The Looker IDE lists files by type: model files ending in .model.lkml, view files ending in .view.lkml, and sometimes dashboards defined in LookML ending in .dashboard.lookml. From any field in an explore, the menu option to go to LookML jumps straight to its definition, which is the fastest way to answer "how is this calculated?" The SQL tab in an explore shows the exact SQL Looker generated, which helps when a number looks wrong and you want to see the joins and filters it used.

Changes that look small can have wide effects. Renaming a field breaks any saved Look or dashboard tile that uses it, so Looker offers the alias parameter to keep old names working; the Content Validator finds saved content broken by a change before users do. Hiding a field is safer than deleting it. And because the model is in Git, every change has an author, a date and a review, which is exactly what Finance needs when it asks who changed the definition of net sales and why.