Predictive Fields in LookML: Surfacing BigQuery ML Scores and Forecasts in Your Semantic Layer

Every Looker team eventually gets the same request: "can the dashboard tell us what is going to happen, not just what happened?" Forecasted revenue, churn risk scores, propensity-to-buy, anomaly flags. The instinct is to send the data out to a notebook, score it somewhere else, and load the results back. That works, but it leaves the predictions outside your governed semantic layer — ungoverned, unjoined, and quietly diverging from the metrics everyone trusts.

BigQuery ML (BQML) removes most of that friction. You train models with SQL inside the same warehouse Looker already queries, and you surface predictions in LookML like any other field. This tutorial walks through the pattern our developers use on client projects: train in BigQuery, materialize predictions in a PDT, model them in LookML, and expose them safely in an Explore.

1. Train the model in BigQuery, not in Looker

Keep training out of LookML. Model training is a scheduled, versioned, reviewed operation; Looker's job is to serve results. Train with a plain SQL statement, either in a scheduled query, dbt, or a Dataform workflow:

CREATE OR REPLACE MODEL `analytics.churn_model`
OPTIONS (
  model_type = 'LOGISTIC_REG',
  input_label_cols = ['churned'],
  data_split_method = 'AUTO_SPLIT'
) AS
SELECT
  customer_id,
  tenure_months,
  plan_tier,
  support_tickets_90d,
  avg_monthly_spend,
  churned
FROM `analytics.customer_features`
WHERE snapshot_date < CURRENT_DATE() - 30;

For time series, ARIMA_PLUS is usually the fastest path to a credible forecast:

CREATE OR REPLACE MODEL `analytics.revenue_forecast`
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'order_date',
  time_series_data_col = 'revenue',
  time_series_id_col = 'region',
  horizon = 90
) AS
SELECT order_date, region, revenue
FROM `analytics.daily_revenue`;

2. Materialize predictions in a PDT

Do not call ML.PREDICT in a live Explore query. Scoring on every dashboard render is slow, expensive, and non-reproducible — two users looking at the same "churn risk" on the same day can see different numbers if the model is retrained in between. Materialize scores on a schedule instead, with a datagroup tied to the same signal that drives your model refresh:

view: customer_churn_scores {
  derived_table: {
    sql:
      SELECT
        customer_id,
        predicted_churned_probs[OFFSET(0)].prob AS churn_probability,
        CURRENT_TIMESTAMP() AS scored_at
      FROM ML.PREDICT(
        MODEL `analytics.churn_model`,
        (SELECT * FROM `analytics.customer_features`
          WHERE snapshot_date = CURRENT_DATE())
      ) ;;
    datagroup_trigger: ml_scoring_datagroup
    partition_keys: ["scored_at"]
  }

  dimension: customer_id {
    primary_key: yes
    type: string
    sql: ${TABLE}.customer_id ;;
  }

  dimension: churn_probability {
    type: number
    value_format_name: percent_1
    description: "Model-estimated probability of churn in the next 90 days. Scored nightly; not a live calculation."
    sql: ${TABLE}.churn_probability ;;
  }

  dimension: churn_risk_band {
    type: string
    sql: CASE
           WHEN ${churn_probability} >= 0.7 THEN "High"
           WHEN ${churn_probability} >= 0.4 THEN "Medium"
           ELSE "Low"
         END ;;
  }

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

The scored_at dimension matters more than it looks. It is the only thing that lets a business user answer "how old is this prediction?" without opening a ticket.

3. Join scores to the governed entity, one row per key

Prediction tables are a classic fanout hazard. If customer_features has one row per customer per snapshot and you forget the date filter, your churn view joins many-to-one against customers and every revenue measure doubles. Enforce one row per key in the derived table, declare the primary key, and join with relationship: one_to_one:

explore: customers {
  join: customer_churn_scores {
    type: left_outer
    relationship: one_to_one
    sql_on: ${customers.id} = ${customer_churn_scores.customer_id} ;;
  }
}

4. Forecasts need their own view, not a column on the fact table

ML.FORECAST output is a different grain from your orders table: future dates, with confidence intervals and no actuals. Model it as its own view and let Looker's merged results or a union view stitch actual and forecast together for the chart:

view: revenue_forecast {
  derived_table: {
    sql:
      SELECT
        region,
        DATE(forecast_timestamp) AS forecast_date,
        forecast_value,
        prediction_interval_lower_bound AS lower_bound,
        prediction_interval_upper_bound AS upper_bound
      FROM ML.FORECAST(
        MODEL `analytics.revenue_forecast`,
        STRUCT(90 AS horizon, 0.8 AS confidence_level)
      ) ;;
    datagroup_trigger: ml_scoring_datagroup
  }
}

Always expose the bounds. A forecast line with no interval reads as a promise; a forecast with a visible band reads as a forecast.

5. Govern the model, not just the dashboard

Three guardrails we put on every BQML-in-Looker build:

• Label everything. Descriptions on every predicted field stating the model name, training window, and refresh cadence. Gemini and the Conversational Analytics API read those descriptions too, so good metadata improves both human and AI interpretation of model output.
• Restrict who can see scores. Risk scores about customers — or about employees — are frequently more sensitive than the underlying facts. Gate them with required_access_grants and a user attribute rather than relying on folder permissions.
• Monitor drift in LookML. Materialize ML.EVALUATE output into a small view and build an admin dashboard on it. If AUC or MAPE moves, you want an alert, not an escalation from a sales director who stopped believing the number.

SELECT CURRENT_DATE() AS eval_date, *
FROM ML.EVALUATE(MODEL `analytics.churn_model`);

6. Know when not to do this

BQML in LookML is the right call when the prediction is a stable, schedulable attribute of an entity you already model — churn risk, lifetime value, demand forecast. It is the wrong call when you need real-time scoring at request time, heavy custom feature engineering, or model architectures BigQuery does not support. In those cases, let the ML platform own scoring and have Looker read the resulting table; the LookML pattern above is identical, only the producer changes.

Wrapping up

Predictive fields do not earn trust because the model is clever. They earn it because they sit in the same semantic layer, with the same joins, the same access controls, and the same freshness guarantees as every other metric on the dashboard. Train in BigQuery, materialize on a datagroup, model one row per key, label the output honestly, and watch for drift.

If you want a second pair of eyes on a BQML or forecasting model you are bringing into LookML — or help building one — get in touch. Vistelio's senior Looker developers do this work every week.