Mastering the Hybrid Fact Pattern in Looker

brett-guenther · March 31, 2026, 3:12am

In retail and e-commerce, data usually falls into two buckets: Flow (Sales, Returns, Shipments) and Stock (Inventory on Hand, Warehouse Capacity).

The “Hybrid Fact” challenge arises because these two data types behave differently, but there is value in analyzing them together. Let’s say you want to analyze sales volume compared to inventory levels. If you sum your daily inventory for a month, you get a number 30x higher than reality. If you don’t sum your daily sales, you miss your sales volume. Furthermore, businesses often need to view this data through two lenses: the Gregorian calendar (Marketing/Ops) and a Fiscal calendar (Finance).

This article provides a production-ready LookML pattern to solve this using Semi-Additive Measures, Liquid-driven Snapshot Mapping, and Performance Tuning. The benefits of this approach are that the logic lives in one Explore, rather than requiring users to merge queries across two separate ones, thereby improving governance. The LookML code examples below use Bigquery Standard SQL syntax, but similar patterns can be applied to other dialects.

1. The Architectural Components

To build this, we use a Conformed Dimension strategy. All fact tables join to a single Date Dimension, while a “Snapshot Map” directs the Snapshot table on which specific days to ignore to prevent double-counting.

Looker View	Data Structure
`dim_date` (anchor base view)	Conformed Dimension
`universal_snapshot_map` (snapshot logic controller)	Mapping Table (hidden helper)
`fact_inventory_snapshot` (the stock)	Semi-Additive (Point-in-Time)
`fact_order_events` (the flow)	Additive (Period Total)

2. The Implementation

A. The Universal Snapshot Map (PDT)

To support multiple calendars, a Persistent Derived Table (PDT) maps the closing day of every possible time period. This identifies the “Source of Truth” date for snapshots and acts as a 1:1 extension of your date dimension.

view: universal_snapshot_map {
  derived_table: {
    datagroup_trigger: daily_refresh
    cluster_keys: ["calendar_date"]
    sql:
      SELECT
        calendar_date,
        -- Gregorian Closing Dates
        MAX(calendar_date) OVER(PARTITION BY EXTRACT(YEAR FROM calendar_date), EXTRACT(MONTH FROM calendar_date)) as last_date_cal_month,
        MAX(calendar_date) OVER(PARTITION BY EXTRACT(YEAR FROM calendar_date)) as last_date_cal_year,

        -- Fiscal Closing Dates
        MAX(calendar_date) OVER(PARTITION BY fiscal_year, fiscal_month) as last_date_fiscal_month,
        MAX(calendar_date) OVER(PARTITION BY fiscal_year) as last_date_fiscal_year,

        -- Absolute Latest Date (for KPI tiles)
        MAX(calendar_date) OVER() as last_date_total
      FROM ${dim_date.SQL_TABLE_NAME} ;;
  }
  fields_hidden_by_default: yes
  dimension: calendar_date { primary_key: yes type: date }
  dimension: last_date_cal_month { type: date }
  dimension: last_date_cal_year { type: date }
  dimension: last_date_fiscal_month { type: date }
  dimension: last_date_fiscal_year { type: date }
  dimension: last_date_total { type: date }
}

# example date dimension table with the fiscal calendar

view: dim_date {
  sql_table_name: `project.retail.dim_calendar` 

  dimension_group: calendar {
    type: time
    timeframes: [date,month,year]
    datatype: date
    primary_key: yes
    sql: ${TABLE}.calendar_date ;;
  }

  dimension: calendar_month_number {
    type: number
    hidden: yes
    sql: ${TABLE}.calendar_month ;;
  }

  dimension: fiscal_year_number { type: number sql: ${TABLE}.fiscal_year ;; group_label: "Fiscal Date" }
  dimension: fiscal_month_number { type: number sql: ${TABLE}.fiscal_month ;; group_label: "Fiscal Date" }
  dimension: fiscal_month_year { type: date datatype:date sql:DATE(${fiscal_year_number}, ${fiscal_month_number}, 1) ;; group_label: "Fiscal Date"}
  dimension: fiscal_year { type: date datatype:date sql: DATE(${fiscal_year_number}, 1, 1) ;; group_label: "Fiscal Date"}
  
}

B. The Snapshot Fact (Inventory)

We use Liquid logic to detect which calendar grain the user has selected. This ensures that “Ending Inventory” is always the correct point-in-time snapshot, whether the user is looking at a Fiscal Period or a Calendar Month. All the dimensions in the dim_date view defined previously are mapped to different snapshot logic via liquid using the “is_selected” syntax.

view: fact_inventory_snapshot {
  sql_table_name: `project.retail.fact_inventory_daily` ;;
  autogenerate_primary_keys: yes

  dimension: snapshot_date { type: date sql: ${TABLE}.snapshot_date ;; }

  dimension: is_snapshot_day {
    hidden: yes
    type: yesno
    sql: 
      -- 1. Fine Grain: If Date is selected, show everything
      {% if dim_date.calendar_date._is_selected %} 1=1
      -- 2. Gregorian Grains
      {% elsif dim_date.calendar_month._is_selected or dim_date.calendar_month_number._is_selected %}
        ${calendar_date::date} = ${universal_snapshot_map.last_date_cal_month::date}
      {% elsif dim_date.calendar_year._is_selected %}
        ${calendar_date::date} = ${universal_snapshot_map.last_date_cal_year::date}

      -- 3. Fiscal Grains
      {% elsif dim_date.fiscal_month._is_selected or dim_date.fiscal_month_year._is_selected %}
        ${calendar_date::date} = ${universal_snapshot_map.last_date_fiscal_month::date}
      {% elsif dim_date.fiscal_year._is_selected or dim_date.fiscal_year_number._is_selected %}
        ${calendar_date::date} = ${universal_snapshot_map.last_date_fiscal_year::date}
      -- 4. Default: Show the most recent data point (KPIs)
      {% else %}
        ${calendar_date::date} = ${universal_snapshot_map.last_date_total::date}
      {% endif %}
      ;;
  }

  measure: ending_inventory_on_hand {
    type: sum
    sql: ${TABLE}.quantity ;;
    filters: [is_snapshot_day: "yes"]
    description: "Units available at the closing of the selected period (Gregorian or Fiscal)."
  }
}

C. The Event Fact (Sales)

These are standard additive measures; no special filtering is required because every row within the selected date range must be summed.

view: fact_order_events {
  sql_table_name: `your-project.retail.fact_order_items` ;;
  
  measure: units_sold {
    type: sum
    sql: ${TABLE}.quantity ;;
    description: "Total units sold during the period."
  }
  
  measure: order_count {
    type: count_distinct
    sql: ${TABLE}.order_id ;;
  }
}

3. The Unified Explore

The logic is enabled in the Explore by joining the snapshot map to dim_date, allowing Liquid to identify period-end markers.

explore: retail_performance {
  label: "Inventory & Sales Performance"
  
  # Base View: The Date Dimension
  from: dim_date
  view_name: dim_date

  # Join 1: The Snapshot Map (The Logic Provider)
  join: universal_snapshot_map { 
      type: left_outer 
      relationship: one_to_one 
      sql_on: ${dim_date.calendar_date::date} = ${universal_snapshot_map.calendar_date} ;; }

  # Join 2: Inventory Snapshot (Semi-Additive)
  join: fact_inventory_snapshot {
    type: left_outer
    relationship: one_to_many
    sql_on: ${dim_date.calendar_date::date} = ${fact_inventory_snapshot.snapshot_date} ;;
  }

  # Join 3: Order Events (Additive)
  join: fact_order_events {
    type: left_outer
    relationship: one_to_many
    sql_on: ${dim_date.calendar_date::date} = ${fact_order_events.order_date} ;;
  }
}

4. Why This Pattern Wins

Accuracy at Every Grain: Looker automatically filters the inventory table to the last day of the selected period (e.g., December 31st for a Gregorian Calendar Year selection).
Avoids “Zombie” Inventory: This pattern preserves the true warehouse state. If a product goes out of stock mid-month, it correctly shows as zero for “Ending Inventory,” unlike simple max approaches.
Performance: It utilizes a small PDT map and standard aggregations instead of expensive window functions in the main fact table, keeping dashboards fast.

Pro-Tip: Use _is_filtered checks in Liquid logic to ensure “Ending Inventory” remains accurate even when users select custom date ranges like “Last 14 Days”.

  dimension: is_snapshot_day {
    hidden: yes
    type: yesno
    sql:
      {% if dim_date.calendar_date._is_selected %} 1=1
      {% elsif dim_date.calendar_month._is_selected %} ${snapshot_date::date} = ${universal_snapshot_map.last_date_cal_month}
      {% elsif dim_date.calendar_year._is_selected %} ${snapshot_date::date} = ${universal_snapshot_map.last_date_cal_year}
      {% elsif dim_date.fiscal_month_number._is_selected or dim_date.fiscal_month_year._is_selected %} ${snapshot_date} = ${universal_snapshot_map.last_date_fiscal_month}
      {% elsif dim_date.fiscal_year._is_selected or dim_date.fiscal_year_number._is_selected %} ${snapshot_date} = ${universal_snapshot_map.last_date_fiscal_year}
      -- 3. THE OPTIMIZED RANGE: Only triggers for Total KPIs/Summary tiles
    -- We use COALESCE to fallback to the 'Total Latest' if the filter is open-ended
      {% elsif dim_date.calendar_date._is_filtered %}
        ${snapshot_date} = COALESCE(
          DATE_SUB(CAST({% date_end dim_date.calendar_date %} AS DATE), INTERVAL 1 DAY),
          ${universal_snapshot_map.last_date_total}
        )
      -- 4. DEFAULT: Absolute latest data point (for no filters/no selection)
      {% else %}
      ${snapshot_date} = ${universal_snapshot_map.last_date_total}
      {% endif %} ;;
  }

5. Powerful Optimizations

SQL Preamble: Multi Pass Partition Pruning

To avoid costly full table scans when user filters don’t align with table partitioning, use sql_preamble and templated filters. This forces BigQuery to perform static partition pruning across multiple tables simultaneously. This logic can be enhanced to capture other fiscal time periods used in filters.

explore: retail_performance {
  sql_preamble:
  {% if dim_date.fiscal_month_year._is_filtered %}
    DECLARE min_date DEFAULT (SELECT MIN(calendar_date) FROM ${dim_date.SQL_TABLE_NAME} WHERE {% condition dim_date.fiscal_month_year %} DATE(fiscal_year, fiscal_month, 1) {% endcondition %} LIMIT 1);
    DECLARE max_date DEFAULT (SELECT MAX(calendar_date) FROM ${dim_date.SQL_TABLE_NAME} WHERE {% condition dim_date.fiscal_month_year %} DATE(fiscal_year, fiscal_month, 1)  {% endcondition %} LIMIT 1); 
  {% endif %}
  ;;

  join: fact_order_events {
    type: left_outer
    relationship: one_to_many
    sql_on: ${dim_date.calendar_date} = ${fact_order_events.order_date} ;;
    sql_where: ${fact_order_events.order_date} BETWEEN min_date AND max_date ;;
  }
}

Aggregate Awareness

Event data (Sales) is perfectly suited for Aggregate Awareness. Pre-calculating monthly totals can significantly speed up high-level dashboards while maintaining model flexibility.

explore: consumer_lifecycle {
   aggregate_table: sales_monthly_summary {
    query: {
      dimensions: [dim_date.calendar_month, dim_date.fiscal_month_number]
      measures: [fact_order_events.units_sold]
    }
    materialization: {
      datagroup_trigger: daily_refresh
    }
  }
}

Summary: The Hybrid Performance Stack

By combining these techniques, you solve the three biggest hurdles in modern BI when dealing with these hybrid fact patterns:

1. Metric Integrity: Semi-additive inventory logic that respects Dual Calendars.
2. Scalability: Aggregate Awareness ensures your dashboard loads are fast, even when summarizing over large historical periods.
3. Cost Control: Advanced control of SQL generation using SQL Preamble and Templated Filters allowing for Partition Pruning to ensure BigQuery only scans the minimal amount of data required for every query.

Topic		Replies	Views
Only measuring the "latest" values Looker lookml	14	123	May 1, 2025
Start and End Period Selection with Looker Technical Tips & Tricks filter , reports-and-visualizations , lookml	0	497	June 17, 2025
「最新の」値だけをメジャーにする方法日本人コミュニティ (Japanese) lookml	0	42	October 28, 2019