Skip to content

Forecasting with SQL

dbt Charts can visualize forecasts computed by SQL, a dbt model, or a warehouse ML feature. The board does not need a special forecasting chart type. Your query returns an ordered time column plus one or more measures, and a line or area chart plots the result.

This supports forecasting workflows, including actual-versus-forecast views. It is different from a native FP&A planning system: dbt Charts does not author a budget, scenario, or write-back workflow. The forecast logic and any future periods belong in SQL or in the model that SQL reads.

The forecast is a query result

A forecast query can return a shape like this:

SELECT
  month,
  actual_revenue,
  forecast_revenue
FROM monthly_revenue_forecast
ORDER BY month

Historical rows can contain both measures. Future rows normally contain a null actual_revenue and a value for forecast_revenue. The board treats both columns as ordinary series. This makes the same chart work with a forecast calculated by a dbt model, warehouse SQL, or a warehouse-native ML function.

Fit a SQL trend and plot it

The following runnable example fits a straight-line trend to the sample monthly metrics and plots the fitted series beside the observed revenue. The same shape can be extended to future periods by adding future rows in the SQL or dbt model.

source: db

queries:
  monthly_revenue_forecast:
    sql: |
      WITH indexed AS (
        SELECT
          month,
          revenue AS actual_revenue,
          ROW_NUMBER() OVER (ORDER BY month) AS month_index
        FROM dundersign_serving.monthly_metrics
      ),
      fit AS (
        SELECT
          REGR_INTERCEPT(actual_revenue, month_index) AS intercept,
          REGR_SLOPE(actual_revenue, month_index) AS slope
        FROM indexed
      )
      SELECT
        indexed.month,
        indexed.actual_revenue,
        fit.intercept + fit.slope * indexed.month_index AS forecast_revenue
      FROM indexed
      CROSS JOIN fit
      ORDER BY indexed.month

charts:
  revenue_forecast:
    query: monthly_revenue_forecast
    type: line
    title: Actual Revenue and Forecast
    x: month
    y: [actual_revenue, forecast_revenue]
    style:
      number_format: currency_whole

rows:
  - revenue_forecast
Sep2025OctNovDecJan2026FebMarAprMayJunJulAug$0$1,000$2,000$3,000$4,000Actual Revenue and Forecastactual revenueforecast revenue Data as of 14:29 UTC on 12 Oct 2026 made withdbt Charts
▶

REGR_SLOPE and REGR_INTERCEPT are convenient when the warehouse supports them. For warehouses without those aggregates, calculate the slope and intercept from AVG, SUM, or the warehouse's covariance functions. A rolling average, seasonal model, or warehouse ML prediction can use the same output shape. The important contract is that the SQL returns the time grain and the measures the chart should display.

For a more detailed regression example, including dialect notes and layering a fitted series over a base chart, see Trend Lines in Layered Charts.

Forecasting is not planning

Forecasting means estimating future values from historical or other input data. Planning and budgeting usually add scenario authoring, approvals, allocations, and write-back to an operational system. dbt Charts can display the results of those workflows when they are modeled in the warehouse, but it does not provide the planning workflow itself.

Waterfall and Gantt are also supported chart recipes for presenting planning and forecast context. See Waterfall and Gantt Recipes for their encodings.