From SAP/ERP to an AI-powered Fresh Food Retail Solution on GCP

An end-to-end architecture: data acquisition, ingestion, storage, transformation, forecasting, inventory optimization, dashboards, SAP write-back, and production operations.

The idea in one picture: reliable data goes in, calibrated forecasts and explainable order recommendations come out. SAP stays the system of record.

Let's consider a realistic industrial project: integrating an existing SAP or ERP system with a cloud-based AI solution for fresh food demand forecasting, inventory optimization, waste reduction, and financial performance monitoring.

The objective is not to replace SAP or the ERP. The objective is to build a complementary Data & AI platform that acquires enterprise data, transforms it into reliable datasets, generates predictions, optimizes decisions, and exposes the results through dashboards and operational interfaces.

We'll cover the complete lifecycle: data acquisition → ingestion → storage → transformation → data quality → feature engineering → model training → deployment → inference → inventory optimization → dashboards → monitoring and maintenance.

I'll use Google Cloud Platform (GCP), with a practical architecture suitable for an enterprise environment.

The lifecycle covered in this article, closed by a monitoring and retraining loop.

1. Define the existing information system

Imagine a retailer operating 150 supermarkets selling fresh products such as strawberries, tomatoes, meat, fish, and dairy products.

The company already uses SAP S/4HANA or another ERP for its business operations.

1.1 Existing systems and their data

Existing system	Data produced	Example
SAP/ERP	Sales, orders, purchasing, costs, suppliers, financial transactions	120 units of strawberries sold in a store
POS system	Transactions and actual sales	12 strawberries purchased at 10:35
Inventory management	Stock levels, movements, write-offs	40 units currently available
Warehouse management	Receipts, batches, expiry dates, movements	60 units received from a supplier
Pricing and promotion system	Prices, discounts, promotions	20% discount this weekend
Supplier system	Lead times, delivery schedules, availability	Next delivery expected tomorrow
External weather API	Weather observations and forecasts	Temperature expected to reach 29°C
Product master data	Product IDs, categories, shelf life, unit costs	Strawberries: expected shelf life of 4 days

These systems do not necessarily share the same database, data model, identifiers, or update frequency.

For example, SAP might identify strawberries using a material number, while a POS system uses a local product identifier. The data platform must reconcile these identifiers.

Three systems, three identifiers for the same punnet of strawberries. The platform owns the mapping.

1.2 What are we building?

We will build five interconnected capabilities:

1. Data platformCentralize and prepare data from existing systems.

2. Forecasting platformPredict future demand and quantify uncertainty.

3. Decision engineCalculate replenishment quantities and evaluate inventory policies.

4. Business intelligenceExplain performance through dashboards and financial KPIs.

5. Operational integrationMake approved recommendations available to planners and, if authorized, send orders or recommendations back to SAP.

The key architectural principle is to separate the operational ERP from the analytical platform.

SAP remains the system of record for business transactions. GCP hosts the analytical workloads and AI capabilities.

Analytics never runs on the transactional system. Recommendations travel back only through controlled, supported interfaces.

2. The complete architecture at a glance

Here is the logical architecture, from source systems to business users.

Logical architecture. Blue = acquisition and ingestion, green = storage and transformation, purple = ML, amber = decisions and consumption.

This is the logical design. Not every GCP service is required in every implementation. The best architecture depends on source-system capabilities, latency requirements, data volume, security requirements, and the number of stores and products.

3. Phase 1: Data acquisition from SAP/ERP

The first engineering question is not which ML model to use. It is:

The first questionHow do we reliably extract data from an existing enterprise information system without disrupting its operations?

There are several integration patterns. We choose based on what the source system supports and how quickly the data is needed.

Pick the pattern from what the source supports and what the business actually needs, not from what sounds modern.

3.1 Integration pattern A: SAP APIs and OData

SAP systems can expose business data through APIs, including OData services, depending on the SAP product, configuration, and available interfaces.

Example:

SAP S/4HANAOData / REST API

→

Cloud Run Jobauthenticate and extract

→

Cloud Storage / BigQuery

GCP services:

Service	Role
Cloud Run Jobs	Execute scheduled extraction tasks
Cloud Scheduler	Trigger extraction jobs periodically
Secret Manager	Store API credentials and secrets
Cloud Storage	Store extracted files when a file-based landing zone is appropriate
BigQuery	Store replicated business data for analytics
Cloud Logging	Record execution logs and failures

Example

Every night at 01:00, a Cloud Scheduler job triggers a Cloud Run Job.

The job retrieves the previous day's sales from SAP through an authorized API and writes the extracted data to Cloud Storage or BigQuery.

A typical record might contain:

sale_date	store_id	product_id	quantity	unit_price
2026-10-09	STR001	PROD123	12	€2.50
2026-10-09	STR001	PROD456	8	€3.20

The extraction process should use incremental retrieval where possible, rather than repeatedly downloading the entire history.

For example, it can retrieve records modified since the last successful extraction.

However, a timestamp-based extraction must account for late-arriving records and corrections. A robust process may reread a recent time window and deduplicate records using stable business identifiers.

Re-reading a short overlap window catches late-arriving records and corrections; stable business identifiers make the reload idempotent.

When to use itWhen the ERP exposes suitable APIs and daily or hourly refreshes are sufficient.

LimitationAPI rate limits, pagination, source-system performance, and changes to API contracts must be managed.

3.2 Integration pattern B: Database replication and change data capture

Suppose the ERP data is available through a supported relational database or an intermediate operational database.

Instead of repeatedly querying the entire dataset, you may capture changes as they happen.

Source Database

→

Datastreamdatabase changes

→

BigQuery

→

Dataformtransformations

Main GCP service: Datastream.

Datastream is a serverless change data capture (CDC) and replication service. It can capture changes from supported source databases and replicate them to destinations such as BigQuery or Cloud Storage.

CDC can capture:

New records.
Updated records.
Deleted records, where supported.
Changes to inventory and transactional tables.

For example, a stock movement is recorded in the operational system at 10:35. The corresponding change can subsequently propagate to the analytical environment without waiting for the next full extraction.

Important SAP distinction

Do not assume direct database accessDo not assume that every SAP deployment exposes its underlying database for direct replication.

For SAP S/4HANA, SAP ECC, SAP Business Warehouse, and SAP Datasphere, the integration options differ.

Depending on the environment, suitable approaches may include:

SAP-supported APIs and OData services.
SAP extractors or other SAP data integration mechanisms.
SAP SLT or other supported replication infrastructure.
SAP Datasphere or existing enterprise integration platforms.
Supported database CDC where the source and configuration permit it.

Datastream is not a universal, direct connector to every SAP application table.

When to use CDCWhen changes need to reach the analytical platform relatively quickly and the source technology and permissions support it.

When not to use itWhen the source exposes only business APIs, when the latency benefit is unnecessary, or when direct replication would violate operational or security constraints.

3.3 Integration pattern C: Events and streaming

Consider a POS system that publishes an event whenever a sale occurs.

POS Systemsale event

→

Pub/Sub

→

Dataflow

→

BigQuery

GCP services:

Pub/Sub: Event ingestion and asynchronous messaging.
Dataflow: Streaming transformations, validation, aggregation, and routing.
BigQuery: Analytical storage and querying.
Cloud Monitoring: Pipeline health and latency monitoring.

An event could conceptually contain:

Field	Example
event_id	EVT-001
event_time	10:35:12
store_id	STR001
product_id	PROD123
quantity	2
unit_price	2.50

Dataflow can validate the event, normalize its structure, and write it to the appropriate destination.

The architecture should also account for duplicate events, late-arriving events, schema changes, and replay requirements.

For reliable processing, define event identifiers, event-time semantics, retry behavior, and a strategy for malformed records.

A streaming pipeline needs an explicit answer for the messy cases: duplicates, late events, and records that fail validation.

Does fresh food retail need streaming?

Not necessarily.

Business requirement	Appropriate approach
Daily store-level demand forecasts	Scheduled batch ingestion
Hourly sales monitoring	Frequent batch or streaming
Near-real-time stock visibility	CDC or streaming, depending on source capabilities
Daily replenishment planning	Batch forecasting and optimization
Automated decisions triggered by stock changes	Event-driven architecture, if the business case justifies it

A common mistake is introducing streaming everywhere because it appears more sophisticated.

Rule of thumbUse streaming when the business requires low latency. Use batch when scheduled processing meets the business requirement.

For many retailers, a hybrid architecture is appropriate.

4. Phase 2: Data ingestion and orchestration

Data acquisition answers how the data leaves the source system. Ingestion defines how it enters and moves through the analytical platform.

These are related but distinct responsibilities.

4.1 Recommended ingestion architecture

For scheduled data:

Cloud Scheduler

→

Cloud Run Jobs

→

Source API / ERP

→

Cloud Storagelanding files

→

BigQueryraw tables

For event-driven data:

POS / Operational Events

→

Pub/Sub

→

Dataflow

→

BigQuery

For more complex workflows, introduce an orchestration layer.

Which orchestration service?

Service	Purpose	When to use
Cloud Scheduler	Time-based triggers	Daily, hourly, or weekly jobs
Cloud Run Jobs	Execute containerized batch tasks	API extraction, data validation, custom batch work
Workflows	Coordinate API calls and service steps	Relatively straightforward multi-service workflows
Cloud Composer	Managed Apache Airflow	Complex DAGs, dependencies, retries, scheduling, backfills
Vertex AI Pipelines	Orchestrate ML workflows	Training, evaluation, model registration, and deployment workflows
Dataform	Manage SQL transformations in BigQuery	Versioned, dependency-aware analytical SQL workflows

These services are not interchangeable.

For example, Vertex AI Pipelines is appropriate for model training and evaluation, while Cloud Composer may coordinate the broader data engineering workflow.

Keep it proportionateYou do not need Cloud Composer simply to schedule three daily tasks.

4.2 Example: Daily forecasting workflow

Suppose the retailer generates its forecasts at 04:00 every morning.

An illustrative morning schedule, not a performance guarantee. The two gates in red are where the workflow can stop or degrade safely.

This is an illustrative schedule, not a performance guarantee.

Fail safelyThe workflow should stop or degrade safely if critical inputs are missing. For example, it should not issue replenishment recommendations based on stale inventory data without explicitly accounting for that risk.

5. Phase 3: Data lake and data warehouse architecture

Now we need to decide where the data lives and how it is organized.

For this project, I would generally begin with Cloud Storage and BigQuery, rather than deploying a large collection of storage technologies.

5.1 Cloud Storage versus BigQuery

Capability	Cloud Storage	BigQuery
Main purpose	Object storage	Analytical data warehouse
Typical content	Files, raw extracts, archives, model artifacts	Tables, analytical datasets, KPI aggregates
Formats	CSV, JSON, Parquet, Avro, and others	Native tables and supported external data sources
SQL analytics	Not its primary purpose	Core capability
Data transformations	Usually performed by another service	SQL transformations and analytical processing
Historical retention	Suitable for raw-file archives	Suitable for structured analytical history

Cloud Storage can serve as a durable landing zone, while BigQuery provides the principal analytical environment.

For some ingestion patterns, data can go directly into BigQuery. A separate raw-file copy is useful when replay, auditability, or source-file retention matters.

5.2 A three-layer BigQuery architecture

A common pattern is to divide datasets into three logical layers.

Raw data is never edited in place. Each layer is derived from the one before it, so any error can be fixed by reprocessing.

Layer 1: Raw

Contains data as received from source systems, with minimal transformation.

Example datasets:

raw_sap
raw_pos
raw_inventory
raw_weather

Example table:

raw_pos.sales_transactions

Typical fields include source transaction IDs, event timestamps, product identifiers, quantities, prices, and ingestion timestamps.

Why preserve the raw layer?

Auditability.
Debugging.
Reprocessing after transformation errors.
Recovering from changes in business logic.
Investigating differences between the source system and analytical outputs.

Raw data should have appropriate access controls and retention policies.

Layer 2: Clean and standardized

This layer reconciles identifiers, types, timestamps, and business conventions.

Example tables:

clean.sales
clean.inventory_movements
clean.inventory_snapshots
clean.products
clean.promotions
clean.supplier_deliveries

Examples of transformations:

Convert timestamps to a common time standard while retaining local business dates.
Standardize product identifiers.
Remove duplicate transaction events.
Validate negative quantities and returns.
Normalize units of measure.
Identify missing product or store mappings.
Reconcile transaction totals against source-system control totals.

A critical ruleDo not simply delete anomalous records. Some unusual values are legitimate business events, such as product returns, corrections, or inventory write-offs.

Layer 3: Business-ready analytical data

This layer contains the tables consumed by forecasting, optimization, and BI.

Example tables:

Table	Grain
`mart.daily_product_store_sales`	One row per product, store, and business day
`mart.daily_inventory`	One row per product, store, and snapshot date
`mart.product_master`	One row per product or product version
`mart.promotion_calendar`	One row per applicable promotion and scope
`mart.supplier_performance`	Defined supplier and evaluation period
`mart.daily_financial_kpis`	One row per defined reporting entity and period

The grain of a table is what one row represents.

Defining it precisely prevents double counting and ambiguous joins.

For example, joining daily sales to a product table that contains several historical versions of the same product may duplicate sales unless the correct product version is selected.

Illustrative example of why the grain of every table must be defined before joining.

6. Phase 4: Data modeling and quality control

Before training an AI model, establish that the data represents the business correctly.

This is especially important in retail because apparent demand changes may be caused by stockouts, product substitutions, missing transactions, or inconsistent inventory snapshots.

6.1 Core analytical entities

A useful starting point is:

A logical relationship diagram. The physical model may use denormalized tables for performance.

This is a logical relationship diagram. The physical model may use denormalized tables for performance.

Why this matters

Suppose a product has:

Sales: 20 units.
Opening inventory: 25 units.
Deliveries: 10 units.
Closing inventory: 8 units.
Recorded waste: 2 units.

The inventory movement equation is:

I_close = I_open + R − S − W + A

where:

I_close: closing inventory.
I_open: opening inventory.
R: received quantity.
S: sold quantity.
W: waste or write-offs.
A: other net inventory adjustments.

For this example:

8 = 25 + 10 − 20 − 2 + A

Therefore:

A = −5

The books do not close: five units disappeared without a record.

The discrepancy suggests an additional net reduction of five units, which could represent transfers, unrecorded movements, timing differences, or data-quality issues.

Why it mattersThe equation is not just an accounting exercise. Incorrect inventory data will lead to incorrect replenishment decisions.

6.2 Data quality services and implementation

A practical implementation can use:

Dataform: SQL transformations and assertions.
BigQuery: SQL validation and reconciliation queries.
Cloud Logging: Pipeline errors and validation outcomes.
Cloud Monitoring: Alerts when critical checks fail.
Dataplex Universal Catalog: Data discovery, governance, and supported data-quality and lineage capabilities, depending on the deployed configuration.

Example checks:

Check	Rule	Action on failure
Sales uniqueness	Transaction key should not be duplicated	Deduplicate or quarantine
Product mapping	Product ID must map to a known product	Quarantine unmapped records
Inventory freshness	Snapshot must be recent enough	Delay recommendations or flag stale data
Sales reconciliation	Totals should reconcile to agreed source controls	Investigate differences
Shelf-life validity	Expiry date should be consistent with product and batch rules	Flag for review
Promotion integrity	Promotion dates and prices must be valid	Exclude or correct invalid records
Feature freshness	Forecast inputs must meet freshness requirements	Block the affected model run

Data-quality thresholds should be business-specific.

For example, a missing weather observation might be tolerable, while a missing inventory snapshot for a store's high-risk fresh products might invalidate that store's recommendations.

7. Phase 5: Feature engineering and feature management

At this stage, the analytical data is clean enough to construct model inputs.

For fresh food, feature engineering is not simply calculating yesterday's sales. We need features that capture calendar effects, changing demand, promotions, availability, and perishability.

7.1 Example forecasting input

Assume the target is:

TargetPredict tomorrow's demand for strawberries at a particular store.

The model input could contain:

Feature family	Example features
Calendar	Day of week, month, holiday, school vacation
Historical demand	Sales yesterday, sales 7 days ago, rolling 7-day average
Demand dynamics	Recent trend, volatility, recent forecast errors
Pricing	Current price, planned price, discount percentage
Promotions	Promotion type, start date, planned campaign
Weather	Temperature, precipitation, weather forecast
Availability	Opening stock, hours available, recent stockout indicators
Supply	Planned deliveries, supplier lead time
Product	Category, shelf life, storage conditions
Store	Location, format, historical demand profile

An example feature table:

store_id	product_id	forecast_date	lag_1	lag_7	rolling_mean_7	price	temp_forecast
STR001	PROD123	2026-10-10	110	125	116	2.50	24

The numerical values are illustrative.

Important: historical sales are not necessarily demand

If a store had 10 strawberries available and sold all 10, observed sales are 10. Actual customer demand may have been 10, 15, or 25.

When stock runs out, sales are capped. The true demand is hidden above the line.

This is a censored observation.

If we train a forecasting model naively on all sales records, stockouts can create a systematic underestimation problem.

We therefore need stockout indicators, availability information, and—where appropriate—models that estimate latent demand rather than treating all observed sales as unconstrained demand.

7.2 Which GCP services?

Service	Role
BigQuery SQL	Feature construction, rolling aggregates, joins, and historical datasets
Dataform	Versioned SQL transformation dependencies
Dataflow	High-volume or streaming feature computation where required
Vertex AI Feature Store	Optional feature management and online serving capabilities, subject to the chosen product architecture
Cloud Storage	Optional storage for feature snapshots or training exports

My initial recommendationBuild most forecasting features in BigQuery and manage their transformations with Dataform.

Introduce a dedicated feature-serving architecture only when there is a concrete requirement for consistent online features, low-latency inference, or shared feature reuse across multiple models.

A feature store is not mandatory for a batch forecasting system.

8. Phase 6: Model development and training

Now we can develop and compare forecasting models.

The key distinction is between developing a model and operating a model in production.

A model that performs well in a notebook is not yet a production forecasting service.

8.1 Model-development architecture

BigQueryhistorical features and targets

↓

Vertex AI Pipeline

↓

Training step

↓

Validation and testing

↓

Compare against baseline

↓

Model evaluation gates

↓

Vertex AI Model

↓

Model Registry

GCP services:

Vertex AI Pipelines: Orchestrate reproducible ML workflows.
Vertex AI Training: Run training jobs using supported environments or custom training containers.
BigQuery: Supply historical data and store evaluation results.
Cloud Storage: Store training artifacts and other model outputs.
Vertex AI Model Registry: Manage model versions and metadata.
Cloud Logging and Monitoring: Record execution outcomes and operational metrics.

For a SQL-heavy pipeline, Dataform and BigQuery can handle much of the data preparation before Vertex AI starts training.

8.2 Which forecasting models should we train?

I would begin with a controlled comparison rather than selecting a fashionable model immediately.

Candidate	Why consider it?	Main limitation
Seasonal-naive baseline	Simple reference for weekly patterns	Cannot adapt to complex drivers
LightGBM	Strong tabular baseline, fast training, supports quantile objectives	Does not automatically model every temporal dependency
Quantile LightGBM	Estimates different conditional demand quantiles	Separate quantile models require calibration and crossing checks
TFT	Supports complex time-series and known future covariates	Greater complexity and data requirements
N-HiTS or PatchTST	Alternative deep-learning forecasting architectures	Must demonstrate improvement on the target data
Time-series foundation model	May transfer useful temporal patterns	Zero-shot performance is not guaranteed to beat a domain-specific model

The output may be:

A point forecast.
Several quantile forecasts.
A calibrated predictive distribution.
Forecasts for multiple future horizons.

For inventory optimization, uncertainty matters because ordering 100 units at the 50th percentile may produce different stockout and waste outcomes from ordering at the 90th percentile.

8.3 Model evaluation

Use rolling-origin backtesting.

Blue = training window, amber = validation window. The origin rolls forward so every model is judged on data it has never seen, in time order.

The precise window depends on available history and seasonal patterns.

For each model, evaluate:

Forecast quality

MAE or WAPE where appropriate.
Quantile pinball loss.
Prediction interval coverage.
Performance by product, store, season, and demand regime.

Business outcomes

Expected waste.
Stockout rate and fill rate.
Inventory value.
Gross profit.
Inventory turnover and GMROI.

Not by MAE aloneA model should not be approved solely because its overall MAE is lower. It must improve the relevant business outcomes without violating operational constraints.

9. Phase 7: Model deployment and inference

Once a model passes evaluation, we need to decide how predictions will be generated.

There are two main serving patterns.

9.1 Pattern A: Batch inference

This is my preferred starting point for daily fresh food replenishment.

Cloud Scheduler

↓

Forecast workflow

↓

Vertex AI batch predictionor custom batch inference

↓

Forecasts stored in BigQuery

↓

Decision engine

The exact batch mechanism depends on the selected model and supported deployment interface. Some implementations may run a custom inference container on Vertex AI; others may use scheduled BigQuery ML inference or a batch job.

Example output:

forecast_date	store_id	product_id	p50_demand	p80_demand	p95_demand
2026-10-10	STR001	PROD123	115	135	155

These are illustrative values.

The predictions become records in BigQuery. The inventory decision engine can then join them with current inventory, supplier lead times, product shelf life, and business constraints.

Why batch is often preferable

Demand forecasts are needed at defined planning times.
The prediction volume can be large.
Predictions can be audited and reproduced.
The cost model is relatively straightforward.
Dashboards can query persisted forecasts rather than invoking models interactively.

9.2 Pattern B: Online inference

Use an online endpoint when a prediction is needed on demand.

Planner application / ERP extension

↓

API layer

↓

Cloud Run application

↓

Vertex AI Endpoint

↓

Prediction

A user might open a product page and request a fresh forecast for a particular store and product.

GCP services:

Vertex AI Endpoint for supported online model serving.
Cloud Run for an application or API layer.
API Gateway or Apigee where API management requirements justify them.
IAM and service identities for authentication and authorization.
Cloud Logging and Cloud Monitoring for observability.

Not automaticAn online endpoint is not automatically needed just because the solution is an AI application.

If a planner needs the same forecast that was calculated at 04:00, reading it from BigQuery is often simpler and cheaper than invoking the model again.

Question	Batch inference	Online inference
Forecast needed	At fixed planning times	On demand, with fresh inputs
Where the result lives	Persisted in BigQuery	Returned per request
Auditability	Easy to reproduce and audit	Requires request/response logging
Typical starting point	Daily fresh food replenishment	Interactive planner tools

10. Phase 8: The inventory decision engine

This is where the solution moves beyond forecasting.

A demand model predicts what customers may buy. The decision engine determines what the retailer should order, subject to costs and constraints.

10.1 Architecture

Forecasts are only one input. Inventory, supplier data, costs, and business policies shape the final order.

The decision engine can be implemented as a containerized application on Cloud Run, a batch processing job, or a more specialized optimization workload, depending on the problem's scale and solver requirements.

BigQuery is useful for storing inputs and outputs, but I would not force every complex optimization algorithm into SQL.

10.2 Example of a recommendation

Suppose the system estimates demand for tomorrow as follows:

Quantity	Value
Median forecast	115
80th-percentile forecast	135
Current inventory	35
Confirmed delivery before the planning horizon	20
Available inventory plus confirmed delivery	55

If the business policy selects the 80th percentile as its initial target:

Q = max(0, 135 − 55) = 80

The order quantity is the gap between the chosen demand quantile and what is already available or on its way.

The initial recommendation is to order 80 units.

Only a simplified calculationFor fresh products, we must also consider existing inventory's age, expiry dates, supplier pack sizes, minimum order quantities, shelf life, delivery timing, and the risk of waste. The final order quantity may therefore differ from 80.

Which GCP services?

Service	Purpose
Cloud Run Jobs	Execute daily optimization and simulation
Vertex AI	Host ML models and orchestrate model workflows
BigQuery	Store forecasts, inventory inputs, recommendations, and results
Cloud Storage	Store larger simulation outputs or artifacts when needed
Workflows or Cloud Composer	Coordinate complex multi-stage processes
Cloud Logging	Capture decisions, failures, and execution details

For a mathematical optimizer, a custom container can package the algorithm and its dependencies. The service that runs it should be selected based on runtime, CPU/memory requirements, and whether the optimization is a scheduled batch task or an interactive request.

10.3 Simulation and optimization

A simulation evaluates a policy under many plausible scenarios.

For example, simulate:

Different demand outcomes.
Supplier delays.
Variable shelf life.
Product waste.
Price reductions.
Stockouts and substitution.
Different replenishment policies.

Simulate, score, adjust, repeat. The score uses documented costs, not model feature importance.

The optimizer then searches for a policy that meets the business objective.

max_π 𝔼[ Gross Profit(π) − Relevant Costs(π) ]

subject to constraints such as:

Fill Rate(π) ≥ α
Inventory Value(π) ≤ B

where:

π is an inventory policy.
α is the minimum required fill rate.
B is an inventory budget.

The optimization process should use consistent financial definitions and costs. Otherwise, it may recommend a policy that appears mathematically optimal but is not commercially useful.

11. Phase 9: Exposing the solution through dashboards and APIs

The solution now has data, forecasts, recommendations, and KPIs. We need to make them accessible to business users.

There are two principal interfaces: business intelligence dashboards and operational applications.

11.1 Dashboard architecture

Forecasting + Inventory Decision Engine

↓

BigQuery business tables

↓

Looker / BI layer

↓

Dashboard for business users

Primary GCP choice: Looker, when the retailer needs governed semantic models, shared KPI definitions, controlled access, and reusable analytics.

Alternative: Looker Studio, when lightweight dashboards and rapid visualization are sufficient.

The choice also depends on licensing, existing BI investments, and enterprise governance.

Dashboard 1: Executive performance

Purpose: Understand whether the solution improves business outcomes.

KPIs:

Revenue and gross profit.
Waste value and waste rate.
Stockout rate and fill rate.
Inventory value.
Inventory turnover.
GMROI.
Forecast accuracy.

Mock-up of the executive view. Tiles show the KPI family and the direction to watch, not real results.

Example questions:

Did gross profit improve after deploying the solution?
Is waste decreasing?
Are service levels improving?
Is the inventory reduction releasing working capital?
Which product categories contribute most to the results?

Dashboard 2: Demand forecasting

Purpose: Understand model quality and demand changes.

Visualizations:

Actual versus forecast demand.
P50, P80, and P95 forecasts.
Forecast error by product and store.
Prediction interval coverage.
Performance during promotions and holidays.
Recent forecast bias.
Demand drift indicators.

Mock-up with synthetic curves. The shaded bands show the P50–P80 and P80–P95 ranges; coverage monitoring checks that actuals fall inside them as often as promised.

Dashboard 3: Inventory operations

Purpose: Help store managers and replenishment planners act.

Columns might include:

Store	Product	Current stock	Forecast demand	Recommended order	Expiry risk
STR001	Strawberries	35	115	80	Medium
STR001	Tomatoes	70	60	0	Low
STR002	Lettuce	12	25	18	High

These are illustrative recommendations, not actual calculations for a specific retailer.

The dashboard should also expose the recommendation's explanation, data freshness, and relevant constraints.

11.2 What if the business needs an interactive application?

Suppose a planner wants to:

Select a store.
Select a product.
Inspect demand forecasts.
Change the target service level.
Simulate a different order quantity.
Compare the financial consequences.
Approve the final recommendation.

That requires more than a static BI report.

A possible architecture is:

A thin API layer between the planner's screen and the data, the decision logic, and (optionally) a live model.

The frontend could be built using React or another suitable framework and deployed using a managed hosting architecture. Cloud Run can host the backend API.

API Gateway or Apigee may be appropriate if the organization requires centralized API management, quotas, authentication policies, and developer access controls.

For an internal application, the exact frontend hosting service should be chosen based on the organization's existing platform and security requirements.

12. Phase 10: Sending recommendations back to SAP/ERP

This is a critical boundary in the architecture.

A dashboard recommendation is not the same as an ERP transaction.

The company must decide whether the AI system is allowed to create proposals, draft orders, or execute approved transactions.

12.1 Three integration levels

Autonomy is earned. Each level adds capability and risk, and each has to prove itself before the next.

Level 1: Read-only decision support

SAP

→

GCP

→

Forecasts and recommendations

→

Dashboard

The system reads operational data and displays recommendations. Users continue to place orders in SAP.

This is the simplest and safest initial deployment.

Level 2: Recommendation write-back

GCP decision engine

↓

Recommendation API

↓

SAP-supported business interface

↓

Purchase requisition / draft proposal

↓

Human approval

The AI solution creates a draft or proposal using a supported SAP business interface.

Interface depends on the deploymentThe exact SAP object and interface depend on the ERP configuration and business process. Do not assume that an arbitrary API can create a purchase requisition in every SAP deployment.

Level 3: Controlled automation

GCP recommendation

↓

Business rules and validation

↓

Authorization and policy checks

↓

SAP transaction interface

↓

Order creation

↓

Confirmation and reconciliation

This level may automate order creation, but only after validation, testing, authorization, and appropriate business controls.

Examples of safeguards:

Maximum order quantity per product.
Maximum order value per store.
Approved supplier and product combinations.
Minimum remaining shelf life.
Pack-size and minimum-order constraints.
Duplicate-order prevention.
Human approval for exceptional recommendations.
Reconciliation between submitted orders and SAP confirmations.

My recommendationStart with read-only recommendations, then move to draft orders, and automate only after the system demonstrates reliable performance.

12.2 Integration services

Depending on the ERP and architecture, the integration may use:

Cloud Run for application logic.
API Gateway or Apigee for API management.
Workflows for coordinating multi-step interactions.
Pub/Sub for asynchronous events.
Secret Manager for secrets.
IAM and service identities for authorization.
Cloud Logging and Cloud Monitoring for observability.

If the organization already uses SAP Integration Suite or another enterprise integration platform, that platform may remain the preferred boundary between SAP and GCP.

There is no need to duplicate an established integration layer just because GCP provides similar capabilities.

13. Phase 11: Explainability and business auditability

For a retail business, the system should not merely display a recommendation. It should explain why the recommendation was produced.

I would maintain three separate explanations.

A. Forecast explanation

Why does the model predict demand of 135 units?

Possible evidence:

Recent sales trend.
Day of the week.
Promotion.
Weather forecast.
Price changes.
Product availability.
Model-specific feature contributions.

SHAP can help explain the contributions of input features to a model prediction. Those contributions should not be presented as proof of causality.

B. Decision explanation

Why is the system recommending an order of 80 units?

Example:

Selected demand quantile: P80
Forecast demand:           135
Current usable inventory:   35
Confirmed delivery:         20
Preliminary order:          80

Then show any subsequent adjustments for shelf life, pack sizes, supplier constraints, or waste risk.

C. Financial explanation

What is the expected impact of following the recommendation?

For example:

Expected reduction in waste.
Expected improvement in fill rate.
Expected gross-profit change.
Expected inventory-value change.
Expected increase or decrease in ordering costs.

These values should come from a documented simulation or calculation, not be inferred directly from the model's feature importance.

Architecture

Three kinds of explanation, one place to find them. Each recommendation is stored with everything needed to reconstruct it.

A useful design stores the recommendation, model version, input snapshot reference, policy version, and relevant constraints together.

That makes it possible to reconstruct why a decision was made.

14. Phase 12: CI/CD and MLOps

A production system changes over time.

Data schemas change, models are retrained, business rules evolve, and application versions are released.

We therefore need controlled deployment processes for both software and ML models.

14.1 Software delivery architecture

Every change travels the same path: commit, test, build, scan, promote.

GCP services:

Service	Role
GitHub or another Git platform	Source control and collaboration
Cloud Build	CI/CD execution
Artifact Registry	Store container images and artifacts
Cloud Run	Deploy backend services and batch workloads
Terraform	Infrastructure as code
IAM	Control deployment and runtime permissions
Cloud Logging	Deployment and runtime logs

Terraform is not mandatory, but infrastructure as code is valuable when the environment includes multiple projects, environments, and services.

14.2 Separate application deployment from model deployment

There are two distinct release pipelines.

Two release tracks. A dashboard tweak should not trigger retraining, and a new model should not ship because it was merely trained.

Application pipeline

Code change

→

tests

→

build

→

security checks

→

deploy to staging

→

integration tests

→

production release

ML pipeline

New data / retraining trigger

→

training

→

evaluation

→

compare with current model

→

approval

→

model registry

→

deployment or batch inference release

→

production monitoring

The two pipelines may interact, but they should not be conflated.

For example, changing a dashboard should not require retraining the forecasting model.

Similarly, a newly trained model should not automatically replace the production model without satisfying evaluation criteria.

14.3 Model promotion gates

Before a candidate model is promoted, verify:

Forecast performance on recent rolling backtests.
Quantile calibration.
Performance for important product categories.
Robustness during promotions and demand shocks.
Compatibility with the inference interface.
Inference latency and resource requirements, where relevant.
Expected inventory and financial performance.
No unacceptable degradation relative to the current model.

A candidate can be stored in the registry without being approved for production.

The exact deployment process depends on whether inference is batch-based, online, or a mixture of both.

15. Phase 13: Monitoring and continuous improvement

A successful deployment is not the end of the project.

The production system must continuously monitor data freshness, pipeline execution, forecast quality, decision quality, and business outcomes.

15.1 Four monitoring layers

Problems usually appear in the data first and in the financial results last. Watch all four.

Layer 1: Data monitoring

Monitor:

Missing data.
Late-arriving data.
Schema changes.
Duplicate records.
Inventory reconciliation discrepancies.
Stale supplier or weather information.
Unusual changes in the volume of incoming records.

Layer 2: Model monitoring

Monitor:

MAE or WAPE.
Quantile pinball loss.
Systematic overforecasting or underforecasting.
Prediction interval coverage.
Feature drift.
Changes in demand patterns.

For fresh food, evaluate performance by product category and store, not only through a single global metric.

Layer 3: Decision monitoring

Monitor:

Recommended versus actual order quantities.
Planner acceptance and overrides.
Stockout frequency.
Waste and expiry.
Inventory value.
Supplier delivery reliability.

Layer 4: Financial monitoring

Monitor:

Revenue.
Gross profit.
GMROI.
Inventory turnover.
Working capital.
Waste costs.
Service-level performance.

Financial KPIs must use consistent definitions and appropriate comparison periods.

Revenue is not profitHigher revenue does not necessarily mean that the AI solution improved profitability. Additional sales may come with higher costs, increased waste, or greater inventory investment.

15.2 GCP monitoring architecture

Infrastructure signals go to Cloud Logging and Monitoring; business-level monitoring results can live in BigQuery next to the data they describe.

BigQuery can store business-level monitoring results, while Cloud Monitoring handles infrastructure and service metrics.

Alerts might be triggered when:

A daily forecast job fails.
The inventory snapshot is stale.
Forecast errors exceed a defined threshold.
Waste rises above an agreed limit.
The model persistently underpredicts demand.
The recommendation API becomes unavailable.

The response should depend on severity. Some failures should block recommendations; others may simply require a warning or manual review.

16. Security and governance across the whole architecture

Security is not a final deployment step. It is a cross-cutting requirement.

The architecture should address:

Concern	GCP capabilities and practices
Identity and authorization	IAM, service accounts, least-privilege permissions
Secrets	Secret Manager
Network isolation	VPC, private connectivity, firewall and egress controls
Data access	BigQuery permissions, row-level and column-level controls where appropriate
Encryption	Encryption at rest and in transit; organization-specific key requirements
Auditability	Cloud Audit Logs and application audit records
Sensitive data	Data minimization, retention policies, restricted access
Perimeter protection	VPC Service Controls where justified and compatible with the design
Service-to-service access	Dedicated service identities and authenticated APIs

Separate environments, private connectivity, and secrets kept out of code.

For SAP integration, connectivity may involve VPN or Cloud Interconnect, depending on where SAP is hosted and the organization's network requirements.

Do notDo not expose an internal SAP system directly to the public internet merely to simplify ingestion.

A production architecture should also separate development, staging, and production environments and avoid using unrestricted production data in development.

17. Putting it together: Recommended GCP architecture

For the first production version of the fresh food solution, I would keep the design relatively simple.

17.1 Recommended initial stack

Layer	Primary choice	Reason
Source integration	SAP APIs/OData or a supported replication mechanism	Respect existing enterprise interfaces
Scheduled extraction	Cloud Run Jobs	Flexible, containerized batch extraction
Scheduling	Cloud Scheduler	Simple time-based triggers
Raw-file storage	Cloud Storage	Durable landing zone and replay
Analytical storage	BigQuery	Central analytical warehouse
SQL transformation	Dataform	Versioned and dependency-aware SQL workflows
Data validation	Dataform assertions and BigQuery checks	Enforce data-quality rules
ML orchestration	Vertex AI Pipelines	Reproducible training and evaluation
ML training	Vertex AI Training or another suitable training environment	Train and evaluate models
Model management	Vertex AI Model Registry	Version and manage candidate models
Forecast generation	Scheduled batch inference	Suitable for daily planning
Decision engine	Cloud Run Jobs or an appropriate compute workload	Replenishment and simulation
BI	Looker or Looker Studio	Expose business KPIs and recommendations
Interactive API	Cloud Run	Serve the operational application
CI/CD	Cloud Build and Artifact Registry	Build and deploy application artifacts
Infrastructure	Terraform	Reproducible infrastructure
Monitoring	Cloud Logging and Cloud Monitoring	Operational visibility and alerts
Security	IAM, Secret Manager, appropriate network controls	Protect data and services

Optional services

Add these only when the requirements justify them:

Start with the green block. Pull in the dashed ones when a concrete requirement shows up.

Pub/Sub and Dataflow: Streaming events and low-latency processing.
Datastream: Supported database CDC and replication.
Cloud Composer: Complex enterprise-wide workflow orchestration.
Vertex AI online endpoints: Interactive model inference.
Vertex AI Feature Store: Feature-serving requirements.
Apigee: Advanced enterprise API management.
Dataplex Universal Catalog: Broader data governance and discovery capabilities.

The pointThe point is not to use every GCP product. It is to select a coherent set of services that covers the requirements without creating unnecessary operational complexity.

18. Production architecture: The end-to-end operational sequence

Here is how the complete system works during normal operation.

One daily loop: from source systems to approved transactions, then back into learning.

This architecture creates a feedback loop: the system observes what happened after its recommendation, measures the outcome, and uses the evidence to improve future forecasting and decisions.

Careful with retrainingRetraining should not automatically treat every resulting sales observation as true demand. Stockouts, substitutions, rejected recommendations, and operational overrides must be considered when interpreting outcomes.

19. What happens when something goes wrong?

A production design should define failure behavior before deployment.

Failure	Expected response
SAP API unavailable	Retry with limits; alert if the extraction window is missed
Duplicate sales records	Deduplicate using stable transaction identifiers and reconciliation rules
Inventory data is stale	Block or flag affected recommendations
Weather API unavailable	Use an approved fallback or mark the feature as unavailable
Forecasting job fails	Preserve the previous valid forecast only if its age is acceptable; otherwise escalate
Model performs poorly	Keep the approved model, investigate, and retrain only after validation
Optimization produces an invalid order	Reject the recommendation through business-rule validation
SAP write-back fails	Record the failed transaction, retry safely where appropriate, and reconcile status
Dashboard query fails	Alert and expose the data freshness or availability problem
A deployment introduces a regression	Roll back to the last approved application or model version

Idempotency in one picture: a retry must never create a duplicate purchase order.

For SAP write-back, idempotency is particularly important: retrying a failed request must not accidentally create duplicate purchase orders.

Likewise, the system should distinguish between a technically successful recommendation and a recommendation that was actually accepted and executed by the business.

20. Three deployment scenarios

The architecture should scale with the business need rather than begin with maximum complexity.

Capability	Scenario A: Daily decision support	Scenario B: Near-real-time operations	Scenario C: Advanced automated optimization
Data ingestion	Scheduled APIs/files	CDC and streaming where supported	Hybrid batch and streaming
Storage	BigQuery + optional Cloud Storage	BigQuery + event ingestion	BigQuery + additional storage if justified
Transformation	Dataform + SQL	Dataform + Dataflow where required	Batch and streaming transformations
Forecasting	Daily batch forecasts	Frequent forecasts for selected products	Multi-horizon probabilistic forecasts
Optimization	Scheduled inventory recommendations	Event-triggered recalculation where justified	Simulation and constrained optimization
Dashboard	Looker/Looker Studio	Dashboard with fresher operational data	Operational application and BI
ERP integration	Read-only initially	Recommendation APIs	Controlled transactional automation
Complexity	Low to moderate	Moderate to high	High
Best fit	Most initial deployments	Time-sensitive decisions	Mature, validated operations

Design the data contracts so that you can climb from A towards B or C later, without rebuilding.

For your use caseI would start with Scenario A, while designing the data contracts and architecture so that streaming can be added later if there is a real operational requirement.

21. How I would organize the actual project lifecycle

A full enterprise project needs delivery stages, not just a target architecture.

Twelve stages from discovery to continuous improvement. The pilot (stage 10) is the moment of truth.

Stage	Main activities	Deliverable
1. Discovery	Understand SAP, data owners, business KPIs, planning processes	Requirements and source-system inventory
2. Architecture	Select integration, storage, security, and deployment patterns	Architecture design and service decisions
3. Data foundation	Connect sources, ingest data, define raw and clean layers	Reliable analytical data platform
4. Data quality	Reconcile sales, inventory, products, and finance	Quality rules and monitoring
5. Baseline forecasting	Build a simple benchmark and backtesting framework	Baseline model and evaluation report
6. Advanced forecasting	Add quantile models, adaptation, and drift monitoring	Validated candidate forecasting models
7. Inventory engine	Implement policy logic, simulation, and optimization	Replenishment recommendations
8. Business integration	Define KPIs, explanations, and ERP interaction	Dashboard and operational interface
9. Production hardening	Add security, CI/CD, failure handling, and monitoring	Production-ready release
10. Controlled rollout	Compare recommendations with current operations	Pilot results and approval decision
11. Scale-up	Extend to stores, products, and additional workflows	Wider deployment
12. Continuous improvement	Monitor performance and retrain under controlled conditions	Sustainable operating process

The first pilot should cover a limited set of stores and products, with an agreed baseline and a way to measure waste, service levels, and profitability.

A model that performs well on historical data still needs operational validation before its recommendations are allowed to influence real orders.

22. The architecture decisions an engineer must be able to defend

In a Data Engineer, ML Engineer, or AI R&D interview, knowing the service names is not enough. You should be able to explain why you selected them.

For this project, I would expect you to justify the following. Tap a question to see the answer.

1. Why BigQuery rather than a transactional database?

Because the main workload is analytical: historical sales, feature generation, aggregation, model evaluation, and business reporting. SAP remains the transactional system of record.

2. Why Cloud Storage as well as BigQuery?

Cloud Storage provides a durable file-based landing zone and supports replay and archival requirements. It is optional when the source and ingestion architecture already provide adequate replayability and retention.

3. Why Dataform?

It manages SQL transformations as versioned, dependency-aware workflows within BigQuery.

4. Why Vertex AI Pipelines?

It provides a managed way to orchestrate reproducible model-training and evaluation workflows. It does not replace the wider data pipeline.

5. Why batch inference rather than an online endpoint?

Daily replenishment usually needs forecasts at scheduled planning times. Persisted predictions are easier to audit and consume than repeatedly calling an endpoint.

6. Why separate forecasting from inventory optimization?

Forecasting estimates uncertain demand. Optimization makes decisions using costs, constraints, shelf life, and business policies. Improving one does not guarantee improvement in the other.

7. Why not automate SAP orders immediately?

Incorrect recommendations can have direct financial and operational consequences. Start with decision support, measure results, introduce approvals, and automate only after adequate validation.

8. How do you know the solution creates value?

Evaluate both predictive metrics and business outcomes, using controlled pilots where feasible and accounting for seasonality, promotions, and other operational changes.

9. How do you prevent a technically successful system from producing bad recommendations?

Enforce data-freshness checks, decision constraints, inventory reconciliation, approval rules, monitoring, and safe failure behavior.

10. How do you manage model evolution?

Version data transformations, features, model artifacts, evaluation results, and decision policies. Promote new models only after they pass predefined gates.

Final recommendation

For your first end-to-end implementation, I would design the following architecture:

SAP / ERP

→

supported APIs or CDC

→

Cloud Storage / BigQuery

→

Dataform

→

Vertex AI training and evaluation

→

batch forecasting

→

inventory simulation and optimization

→

BigQuery recommendation tables

→

Looker dashboard

→

controlled SAP integration

Use Cloud Run for extraction and decision services, Cloud Scheduler for simple scheduling, Vertex AI Pipelines for ML workflows, and Cloud Build with Artifact Registry for application delivery.

Introduce Pub/Sub, Dataflow, Cloud Composer, online model endpoints, and more advanced API-management infrastructure only when the business requirements justify them.

This gives you a realistic, deployable Data & AI architecture that covers the entire lifecycle, from enterprise data acquisition to business decisions, operational integration, monitoring, and continuous improvement.

Back to the top ↑