Restaurant Revenue Forecasting with dbt + BigQuery ML
Hourly revenue forecasts for 50+ restaurants, up to 30 days out, running entirely inside the data warehouse.
OVERVIEW
Hourly Demand Forecasts That Live in the Warehouse
Built a production forecasting system that predicts hourly revenue for 50+ restaurants up to 30 days in advance, using dbt and BigQuery ML.
Because the models run inside BigQuery, forecasts update continuously with no data movement and no separate serving stack. The system beat the operators' existing heuristic forecasts on accuracy and cut their forecast error roughly in half.
The result is staffing and inventory decisions grounded in a number the team can trust, refreshed automatically every day.
The Challenge
Restaurant operators were flying on intuition. Their forecasting process was:
Built on manual or heuristic estimates
Unable to predict demand at the hourly level
A poor basis for staffing, inventory, and prep decisions
That made the whole operation reactive: over-staffed on slow nights, caught short on busy ones, with inventory waste on both ends.
APPROACH
The Approach
I built a production ML forecasting system fully embedded in BigQuery:
Feature engineering in dbt: reusable models covering time horizons, sales history, weather, holidays, and local events
Ensemble models in BigQuery ML, including k-means clustering, gradient-boosted regressors, and random forests
A forecasting pipeline producing hourly, daily, and weekly predictions up to 30 days out, benchmarked against the client's existing forecasts
Prefect orchestration to schedule recurring training and prediction as reliable production jobs
A prediction-tracking system that logs every training and forecast event, scores accuracy, and watches for model drift
Core architecture:
dbt for the transformation and feature layer
BigQuery ML for in-warehouse model training and scoring
BigQuery as the single source of data and predictions
Prefect for scheduling and orchestration
A logging and evaluation layer for accuracy and drift monitoring
Looker Studio for operator-facing forecast dashboards
Keeping the model in SQL next to the data removed an entire serving tier and made the pipeline something a small team can actually run.
Technical Stack
dbt
BigQuery ML
BigQuery
Prefect
Python
SQL
Looker Studio
Google Cloud Platform
RESULTS
Key Outcomes
Under 5% weekly forecast error
Roughly 50% reduction versus the operations team's prior forecast error
Better staffing efficiency and inventory planning
Continuous, automated forecasts across 50+ restaurants
Forecast accuracy and drift tracked on every run
Why This Matters
For an operator, a forecast is only useful if someone trusts it enough to staff against it. This project mattered because it produced numbers accurate enough to act on, and put them where decisions get made:
Labor cost, the largest controllable expense, planned against real demand
Inventory and prep matched to expected covers
A repeatable system instead of a weekly guessing exercise
It also shows a pattern many SMBs miss: you often do not need a separate ML platform. If the data is in a warehouse, the model can live there too.
Looking Forward
Planned next steps:
Promotion and pricing elasticity modeling
Per-location model selection and tuning
Automated alerting when actuals diverge from forecast
Menu-item-level demand forecasting
Self-serve forecast scenarios for operators
Interested in building something similar?
We help organizations design scalable AI and analytics infrastructure for:
forecasting
growth analytics
operational automation
machine learning platforms
agentic AI workflows
cloud-native data systems
Let’s build systems that turn data into operational leverage.
