Vistelio | Slowly Changing Dimensions in LookML: Point-in-Time Reporting Without Lying to Your Business

Almost every Looker project we are called into has the same quiet bug in it. Orders join to customers, customers join to a sales rep, the sales rep joins to a region — and every one of those joins uses the current value of the attribute. Then someone in finance asks "what did the West region actually sell last quarter?", the answer moves every time a rep changes territory, and trust in the dashboard evaporates.

This is the slowly changing dimension (SCD) problem, and it is a modeling problem, not a Looker bug. Your warehouse teams are increasingly producing SCD Type 2 history (dbt snapshots, BigQuery change history, CDC pipelines into a versioned table) — but the semantic layer usually flattens it straight back to "current". This tutorial covers how to model that history properly in LookML.

The shape of the data

An SCD Type 2 dimension keeps one row per version of an entity, with a validity window:

customer_key	customer_id	segment	valid_from	valid_to	is_current
1001	42	SMB	2024-01-01 00:00:00	2025-03-14 00:00:00	false
1044	42	Mid-Market	2025-03-14 00:00:00	9999-12-31 00:00:00	true

Two conventions matter and you must confirm both with whoever builds the table:

Is valid_to exclusive (the next version's valid_from) or inclusive (one second/day earlier)? Exclusive with a half-open [valid_from, valid_to) comparison is the only version that never double-counts or drops rows at a boundary.
Is the open row's valid_to NULL or a sentinel like 9999-12-31? A sentinel is far friendlier in joins; if you get NULL, coalesce it in the view rather than in every join.

Step 1: a view that normalises the window

Do the coalescing once, in the view, so no Explore has to remember it.

view: customer_history {
  sql_table_name: analytics.customer_scd2 ;;

  dimension: customer_key {
    primary_key: yes
    type: number
    sql: ${TABLE}.customer_key ;;
    hidden: yes
  }

  dimension: customer_id {
    type: number
    sql: ${TABLE}.customer_id ;;
  }

  dimension: segment {
    type: string
    sql: ${TABLE}.segment ;;
  }

  dimension_group: valid_from {
    type: time
    timeframes: [raw, date, month]
    sql: ${TABLE}.valid_from ;;
  }

  # Normalise the open-ended row to a sentinel so joins stay simple.
  dimension_group: valid_to {
    type: time
    timeframes: [raw, date, month]
    sql: COALESCE(${TABLE}.valid_to, TIMESTAMP('9999-12-31')) ;;
  }

  dimension: is_current {
    type: yesno
    sql: ${valid_to_raw} = TIMESTAMP('9999-12-31') ;;
  }

  dimension: version_label {
    type: string
    sql: CONCAT(CAST(${customer_id} AS STRING), ' @ ', FORMAT_TIMESTAMP('%Y-%m-%d', ${valid_from_raw})) ;;
  }
}

Note the primary key: it is the surrogate version key, not customer_id. Getting this wrong is the single most common cause of wrong measures on SCD joins, because Looker's symmetric aggregates rely on a genuinely unique primary key per row.

Step 2: the "as-was" join

To answer what was true when the fact happened, join on the business key and the validity window:

explore: orders {
  label: "Orders (point-in-time)"

  join: customer_history {
    view_label: "Customer (as of order date)"
    relationship: many_to_one
    type: left_outer
    sql_on:
      ${orders.customer_id} = ${customer_history.customer_id}
      AND ${orders.created_raw} >= ${customer_history.valid_from_raw}
      AND ${orders.created_raw} <  ${customer_history.valid_to_raw} ;;
  }
}

The half-open comparison (>= … <) guarantees exactly one matching version per fact row. If you see order counts inflate after adding this join, your history table has overlapping windows — fix it upstream, and add a LookML data test so it never silently comes back:

test: customer_history_no_fanout {
  explore_source: orders {
    column: order_count {}
    column: id_count { field: orders.count_distinct_ids }
  }
  assert: order_count_matches_distinct_ids {
    expression: ${order_count} = ${id_count} ;;
  }
}

Step 3: offering "as-is" alongside "as-was"

Business users legitimately want both. "Revenue by the rep's current territory" is the right question for comp planning; "revenue by the territory at the time" is the right question for historical performance. Give them two clearly labelled joins rather than one ambiguous one. Refinements keep this tidy without copy-pasting the view:

view: customer_current {
  extends: [customer_history]
  sql_table_name: analytics.customer_scd2 ;;
}

explore: orders {
  join: customer_history {
    view_label: "Customer (as of order date)"
    relationship: many_to_one
    sql_on: ${orders.customer_id} = ${customer_history.customer_id}
      AND ${orders.created_raw} >= ${customer_history.valid_from_raw}
      AND ${orders.created_raw} <  ${customer_history.valid_to_raw} ;;
  }

  join: customer_current {
    view_label: "Customer (current)"
    relationship: many_to_one
    sql_on: ${orders.customer_id} = ${customer_current.customer_id}
      AND ${customer_current.is_current} ;;
  }
}

Label them in plain English in view_label and put the explanation in each field's description. This is the cheapest governance you will ever ship, and — usefully in 2026 — those descriptions are exactly what Looker's AI features and the Conversational Analytics API read when deciding which field answers a question. An unlabelled pair of customer joins is how an AI assistant confidently gives you the wrong number.

Step 4: a user-chosen "as of" date

Sometimes the question is not tied to a fact date at all: show me the customer base as it stood on 30 June. A templated filter handles this without a second Explore:

view: customer_as_of {
  extends: [customer_history]

  filter: as_of_date {
    type: date
    description: "Snapshot date for customer attributes. Defaults to today."
  }

  dimension: in_effect_as_of {
    type: yesno
    sql: {% if customer_as_of.as_of_date._is_filtered %}
           {% condition customer_as_of.as_of_date %} ${valid_from_raw} {% endcondition %}
           AND ${valid_to_raw} > {% date_start customer_as_of.as_of_date %}
         {% else %}
           ${is_current}
         {% endif %} ;;
  }
}

Then add in_effect_as_of as an always-on filter on the join (sql_where: or a required filter on the Explore). Test the unfiltered default carefully — an as of dimension that silently returns every version when nobody picks a date is worse than no feature at all.

Step 5: making it fast on BigQuery

Range joins are not free. Three things do most of the work:

Partition and cluster the history table on valid_from (partition) and the business key (cluster). Range predicates then prune partitions instead of scanning the full history.
Keep history narrow. Snapshot only the columns that genuinely change. A Type 2 table with 60 columns, of which 3 change, generates versions nobody asked for.
Pre-resolve the join in a PDT when the fact table is huge and the pattern is hot. Materialise order_id → customer_key once per datagroup, then join that skinny bridge to the dimension on an equality condition:

view: order_customer_bridge {
  derived_table: {
    datagroup_trigger: daily_etl
    partition_keys: ["order_date"]
    cluster_keys: ["order_id"]
    sql:
      SELECT o.id AS order_id,
             DATE(o.created_at) AS order_date,
             c.customer_key
      FROM ${orders.SQL_TABLE_NAME} o
      JOIN ${customer_history.SQL_TABLE_NAME} c
        ON o.customer_id = c.customer_id
       AND o.created_at >= c.valid_from
       AND o.created_at <  COALESCE(c.valid_to, TIMESTAMP('9999-12-31')) ;;
  }
}

The Explore then joins facts to the bridge on order_id and the bridge to the dimension on customer_key — two equality joins, which every query engine optimises far better than a BETWEEN.

Rollout advice

Do not swap an existing join in place. Add the point-in-time join beside the current one, ship both for a reporting cycle, and reconcile the two on a handful of known dashboards before you deprecate anything. When you do retire the old field, use hidden: yes plus a deprecation note first — renaming or deleting it outright will break saved Looks and scheduled deliveries.

Where this usually goes wrong

Primary key set to the business key instead of the version key, which quietly breaks symmetric aggregates.
valid_to treated as inclusive in one join and exclusive in another, so month boundaries double-count.
Timezone drift: facts stored in UTC, validity windows written in a local timezone. Compare raw timestamps in a single timezone and never join on _date fields across systems.
History with gaps, so a left_outer join silently produces NULL attributes. Add a data test that asserts the count of unmatched facts is zero.

Slowly changing dimensions are the difference between a BI platform the finance team argues with and one they close the books on. If your Looker model currently joins everything to "current" and your business has just started asking why last quarter's numbers moved, that is the symptom — and it is a contained, one-sprint fix with the right modeling patterns.

Need a second pair of eyes on a Looker model before the numbers get political? Vistelio's senior Looker and LookML developers do exactly this kind of remediation work — get in touch.