BigQuery ML Multi-Time-Series Forecasting for Demand Planning

PMLE By islever Editorial Last updated

Developing ML models using BigQuery ML or AutoML on Gemini Enterprise Agent Platform. Building models given the task considering cost, complexity, latency, and scalability.

Short answer

A Train a multivariate BigQuery ML ARIMA_PLUS_XREG model on all SKUs by using the TIME_SERIES_ID_COL option to enable parallel, single-query multi-time-series forecasting.

Question

You are developing a demand forecasting model for a large online retailer. The company has 20,000 SKUs that are sold through a single distribution center. You have three years of historical data. You need to provide a daily forecast at the SKU level for the next two weeks. You need to develop the first version quickly and minimize the development effort while delivering high forecasting accuracy. What should you do?
  1. Train a multivariate BigQuery ML ARIMA_PLUS_XREG model on all SKUs by using the TIME_SERIES_ID_COL option. Correct Answer
  2. Write a Python script in Agent Platform Workbench that trains a BigQuery ML ARIMA model for each SKU, and use a corresponding model for each SKU forecast.
  3. For each SKU, send the last month’s history of sales and the SKU’s description to Gemini Pro to generate a prediction for the next two weeks. Instruct the LLM to act as a category specialist in retail and to focus on accuracy.
  4. Fine-tune the Gemini Pro model on the historical data to predict the daily sales for each SKU. Use the fine-tuned model without prompt engineering.

Key Takeaways

The question tests knowledge of BigQuery ML's ability to handle multiple time series in a single query via the TIME_SERIES_ID_COL parameter, avoiding manual iteration.

Learn how to efficiently forecast 20,000 SKUs using BigQuery ML's ARIMA_PLUS_XREG with TIME_SERIES_ID_COL to minimize coding effort while maintaining high accuracy.

Common mistake: Candidates often choose Option B, believing they must write custom Python scripts to handle large volumes of data. However, this ignores BigQuery's native support for multi-time-series models, which drastically reduces development time and effort.

Expert Analysis

The scenario requires forecasting for 20,000 SKUs with minimal development effort and high accuracy. Option A leverages BigQuery ML's native multi-time-series capability. By specifying TIME_SERIES_ID_COL, a single SQL statement trains separate ARIMA_PLUS_XREG models for each SKU in parallel within the BigQuery infrastructure. This approach satisfies the 'quickly' and 'minimize development effort' constraints because it eliminates the need for iterative Python scripting or API calls that would be required if training individual models.

Option B suggests writing a Python script to train a model for each SKU. While feasible, this introduces significant orchestration overhead, latency, and code complexity, violating the requirement to minimize development effort. Managing 20,000 separate training jobs programmatically is inefficient compared to BigQuery's declarative SQL approach.

Options C and D involve Large Language Models (LLMs). LLMs are not designed for precise numerical time-series forecasting tasks like daily sales predictions. Using an LLM for this purpose (C) would yield hallucinated or imprecise results, failing the 'high forecasting accuracy' requirement. Fine-tuning an LLM (D) for such granular numerical prediction is computationally expensive, slow to develop, and generally less accurate than specialized statistical models like ARIMA for this specific use case.

Therefore, the key insight is recognizing that BigQuery ML supports hierarchical/multi-time-series modeling natively. The trap is assuming that managing 20,000 items requires complex code (B) or AI gimmicks (C/D), whereas the correct path is using the platform's built-in optimization features.

Exam Strategy

When faced with requirements for 'minimal effort' and 'scale', prioritize managed services with native bulk-processing capabilities over custom scripting. Look for keywords like 'parallel', 'single query', or 'native support' in options involving large datasets. For time-series forecasting at scale, always consider BigQuery ML's multi-time-series features before resorting to custom algorithms or LLMs.

Frequently Asked Questions

Why is ARIMA_PLUS_XREG better than standard ARIMA?

ARIMA_PLUS handles changepoints and holidays automatically. XREG allows adding external regressors (like promotions) to improve accuracy beyond just historical trends.

Can I use Gemini Pro for precise sales forecasting?

No. LLMs are probabilistic text generators, not numerical predictors. They lack the precision required for daily sales forecasting and may hallucinate numbers.

More PMLE FAQ →

Community Votes

A
100%

100% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 2 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 2

  1. taylz876 👍 1 Answered A
    Why A is right • 20,000 SKUs, quickly and with minimal effort: setting the time series ID column to the SKU means one CREATE MODEL statement trains 20,000 separate forecasts in parallel, with no scripting for each product. • Daily forecast for the next two weeks, high accuracy: ARIMA_PLUS handles trend and seasonality, and the XREG version can add extra variables, which helps accuracy. • Three years of history is enough for it to learn the weekly and yearly patterns. Why the others are wrong • B: A Python script that trains one model per SKU means 20,000 models to create and manage by hand, which is a lot of effort. • C: Sending one month of history per SKU to Gemini Pro isn't a reliable forecasting method, and it doesn't scale to 20,000 SKUs. • D: Fine-tuning an LLM on the sales data takes much more effort and isn't designed for numeric forecasting. Definitions: SKU: a "stock keeping unit", the code for one specific product in a store's inventory (for example, a particular shirt in size medium, blue). 20,000 SKUs means 20,000 different products to forecast. • Time series: data recorded over time in order, like daily sales for one product over three years. • ARIMA_PLUS: BigQuery ML's forecasting model for a time series. It learns the pattern in the past values (the overall trend, the weekly and yearly ups and downs, and holiday effects) and projects it forward. Plain ARIMA_PLUS uses only the series' own history. • ARIMA_PLUS_XREG: the same, but it can also use extra variables (for example promotions or price) to improve the forecast. The "XREG" stands for external regressors. • TIME_SERIES_ID_COL: the column that says which time series each row belongs to. Here you'd set it to the SKU. BigQuery then treats each SKU as its own separate series and trains a separate forecast for each, all in one statement.
  2. RajuMVV 👍 1 Answered A
    Minimal Development Effort & Speed: BigQuery ML's ARIMA_PLUS / ARIMA_PLUS_XREG natively supports multiple time-series forecasting in a single SQL query. By setting TIME_SERIES_ID_COL = 'sku_id', BigQuery automatically trains, tunes, and forecasts across all 20,000 SKUs simultaneously without manual loop orchestration

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Monitoring ML Model Performance with Precision and Recall Sampling

S-grade

Optimizing ML Model Recall for Compliance Risk

S-grade

How to Minimize Development Time for Weekly TensorFlow Retraining in Vertex AI?

S-grade

Building CLV Models with BigQuery ML in Vertex AI Workbench

S-grade

Practice All PMLE Questions

Access 65 questions with complete answers and detailed explanations.

View Full PMLE Practice Test →

← Back to PMLE Study Guide