BigQuery ML ARIMA_PLUS for 50,000-SKU Daily Demand Forecasting

PMLE By islever Editorial Last updated

Short answer

A Use BigQuery ML CREATE MODEL with model_type='ARIMA_PLUS' and SKU as the time-series ID to forecast all 50,000 SKUs in-warehouse, automatically handling holidays and weekly seasonality.

Question

You are an ML engineer at a large retail company. You have a BigQuery table containing five years of daily sales data for 50,000 distinct product SKUs. You need to generate a daily demand forecast for the next 30 days for each individual SKU. The solution must automatically account for holidays and weekly seasonality. You want to minimize data egress costs and operational complexity by keeping the data and model within the data warehouse. What should you do?
  1. Use the CREATE MODEL... OPTIONS(model_type='ARIMA_PLUS') statement in BigQuery to generate a forecast based on historical SKU sales. Correct Answer
  2. Use the gcloud ai custom-jobs create command to run a PyTorch LSTM container on Agent Platform that pulls data from a CSV into Cloud Storage.
  3. Use the CREATE MODEL... OPTIONS(model_type='LINEAR_REG') statement in BigQuery to predict sales using the transaction date as a feature.
  4. Use the google-cloud-aiplatform SDK to create a TimeSeriesDataset and trigger an AutoMLForecastingTrainingJob.

Key Takeaways

The question tests whether you choose an in-warehouse time-series model over platform-based AutoML or custom training, with the main trap being AutoML Forecasting's sophistication despite its extra data movement and operational overhead.

This PMLE scenario tests how to forecast daily demand for 50,000 SKUs while keeping data and model inside BigQuery. The answer is BigQuery ML ARIMA_PLUS (A), which handles holiday and weekly seasonality without Vertex AI data movement.

Common mistake: Option D is the most tempting wrong answer because AutoML Forecasting handles time-series seasonality, but it requires a Vertex AI TimeSeriesDataset and training job, adding data staging and operational complexity that the stem explicitly asks to minimize.

Expert Analysis

The stem's real constraint This is less a model-quality question than a deployment-boundary question. The company needs 30-day daily forecasts for 50,000 SKUs, with holidays and weekly seasonality, while keeping data and model in the warehouse and minimizing egress plus operational complexity. BigQuery ML's CREATE MODEL... OPTIONS(model_type='ARIMA_PLUS') is the direct fit: with the SKU column as the time-series ID, one statement trains and forecasts each series inside BigQuery, and ARIMA_PLUS models trend, seasonality, and holiday effects. That makes A the correct choice.

Why the adjacent options lose B runs a custom PyTorch LSTM on Agent Platform after pulling a CSV through Cloud Storage, which adds data movement, container maintenance, and training/serving operations. D's AutoML Forecasting can model time series, but it requires a Vertex AI TimeSeriesDataset and AutoMLForecastingTrainingJob; that introduces data staging and platform orchestration outside the warehouse. Reviewers make the same distinction, noting that B and D 'move the workload to Agent Platform' and add complexity. C is weaker still: LINEAR_REG with transaction date as a feature does not natively capture weekly cycles or holiday spikes and does not provide the same built-in multi-step forecasting behavior.

What this knowledge covers beyond this question The transferable decision is SQL-first in-warehouse forecasting versus a managed ML platform. ARIMA_PLUS also exposes settings for forecast horizon, holiday region, and confidence intervals, so you should recognize when those built-ins satisfy the requirement. The answer would flip if the scenario required custom deep-learning architectures, rich exogenous inputs that BQML cannot express, or integration with an existing Vertex AI pipeline—and if the business explicitly accepted data export and extra operations. Then D or B could be defensible. If the requirement were only a simple trend and the team did not need seasonality, C might be a coarse baseline, but it would not meet this stem.

Operational consequence Choosing B or D means exporting five years of daily SKU sales, scheduling retraining and model deployment, and managing extra failure points and cost. Choosing C risks forecasts that miss holiday peaks and weekly patterns, producing stockouts or excess inventory. A keeps the workflow in BigQuery SQL, where access control, slots, and model refresh stay close to the data.

Official Reference

Exam Strategy

Before comparing model quality, underline the stem's delivery constraints—'keep data and model within the data warehouse' and 'minimize data egress.' Eliminate any option that stages CSV/Cloud Storage data or creates a Vertex AI dataset or training job. Then verify that the remaining model type natively supports the required time-series behavior: ARIMA_PLUS handles seasonality and holidays, whereas LINEAR_REG with a date column does not. This constraint-first filter transfers to BQML versus Vertex AI questions.

Frequently Asked Questions

Does BigQuery ML ARIMA_PLUS train a separate forecast for each SKU automatically?

Yes. When you set the SKU column as the time-series ID, ARIMA_PLUS fits and forecasts each series independently within the same CREATE MODEL statement, avoiding 50,000 separate model objects you must manage.

Why is LINEAR_REG with transaction date as a feature insufficient for this forecast?

A date encoded as a numeric feature does not inherently capture weekly cycles, holiday spikes, or a 30-day multi-step horizon. ARIMA_PLUS is purpose-built to model those time-series effects.

When would AutoML Forecasting on Vertex AI be a better fit than BigQuery ML?

If the team needs custom Vertex AI pipeline integration, advanced preprocessing, or models beyond BQML's options and accepts staging data outside BigQuery, AutoML Forecasting becomes reasonable; the stem's egress and simplicity constraints favor A.

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
    Similar to Q215 and Q340 Why A is right • Forecasting 50,000 SKUs inside the warehouse: one CREATE MODEL statement with the time series ID set to the SKU trains a separate forecast for every SKU in parallel, with no data leaving BigQuery. • Holidays and weekly seasonality are handled automatically: ARIMA_PLUS models trend, seasonality and holiday effects. • Minimum cost and complexity: no egress, no custom code. Why the others are wrong • B: A PyTorch LSTM on Agent Platform with CSV exports moves the data out and adds custom code. • C: Linear regression on the date alone doesn't capture seasonality or holidays. • D: AutoML Forecasting exports the data out of the warehouse and adds more setup.
  2. n2183712847 👍 1 Answered A
    Correct answer: A. BigQuery ML’s ARIMA_PLUS is designed for time-series forecasting directly within BigQuery and can automatically model seasonality and holiday effects while keeping the data and model in the warehouse; B and D move the workload to Agent Platform, adding operational complexity and potential data movement, while C’s linear regression does not natively handle time-series seasonality and holiday effects.

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.

Practice All PMLE Questions

Access 65 questions with complete answers and detailed explanations.

View Full PMLE Practice Test →

← Back to PMLE Study Guide