Building a 3-Layer Pipeline to Cleanse Business Central Data for Forecasting

1. Introduction

In modern supply chain and demand forecasting, data quality is the foundation of every decision. Business Central can serve as a source for sales, inventory, purchases, and transfer data. However, raw CDC (Change Data Capture) payloads extracted through the OData API may require additional cleansing before they are forecast ready.

Fields were missing, column names were inconsistent, and most critically NULLs were everywhere . While Business Central stores transactional data reliably, the OData API exports often include null values for optional fields, unpopulated attributes, or fields that simply don’t apply to certain record types.

Treating these NULLs as zeros would have been a catastrophic mistake in demand forecasting. A NULL in a sales quantity column doesn’t mean “zero units sold” it means “we don’t know”. Erasing that distinction would hide the difference between genuinely zero demand and missing data, leading to under forecasting, misplaced safety stock, and costly stockouts.

To solve this, I built a 3 layer Medallion pipeline (Bronze → Silver → Gold) in Azure Databricks. I also designed a custom ingestion API using Databricks’ OData V4 connector wizard to pull 12 ERP entities directly into Azure Blob. The Silver layer introduced a “NULL is not 0” philosophy, with context aware imputation and boolean flags to preserve data quality signals. The Gold layer aggregated daily demand per item location, leaving NULLs as NULL to correctly represent “no data” days.

This article walks through the architecture, the imputation logic, and the practical lessons learned from transforming 12 messy datasets into a clean, forecast ready mart.

2. The Business Problem

The organization relied on manual Excel spreadsheets to prepare demand forecasts across 22 high impact items and 3 warehouse hubs a total of 66 item location combinations . This manual process was error prone and unscalable.

Several challenges emerged:

1. Heavy reliance on spreadsheets: Demand planning was executed via disconnected, static Excel files. Frequent formula errors, broken references, and accidental overwrites led to incorrect estimates.
2. Lack of scalability: Manually calculating forecasts across 66 combinations was time consuming. Excel could not dynamically model complex interactions like monthly seasonality and day of week demand patterns simultaneously.
3. Reactive procurement and stockouts: Teams spent more time cleaning data than analyzing risk. Static buffer stocks failed to account for changing supplier lead times, resulting in emergency purchase orders or stockouts during peak seasons.
4. NULLs were treated incorrectly: In many cases, missing values were simply replaced with 0, masking the true state of the data and causing the forecast model to underestimate demand.

3. The Solution

The solution was designed as a Medallion architecture with three distinct layers, each serving a specific purpose in the data quality journey.

3.1 Medallion Architecture Overview

The pipeline processes 12 raw JSON datasets from Business Central through Bronze, Silver, and Gold layers in Azure Databricks.

3.2 Ingestion API from Business Central

The first step was to build a reliable ingestion layer. I used the OData V4 connector wizard in Databricks to set up an authenticated connection directly to Business Central’s REST API.

This connector handled pagination, authentication (Azure AD OAuth 2.0 with Key Vault), and parsing of the OData response format (data wrapped inside a "value" array). I configured it to pull all 12 datasets that feed into the forecasting pipeline.

• Security: Zero hard coded credentials all secrets stored in Azure Key Vault.
• Endpoints: Pulled items , sales_lines , purchase_headers , item_ledger_entries , and 8 other entities.
• Landing: Raw JSON payloads were written to Azure Blob (Bronze) as immutable snapshots, preserving the original CDC state.

3.3 The NULL Philosophy

Before writing a single line of PySpark, I defined a clear rule: NULL is not 0 . This principle guided every transformation in the Silver layer.

In demand forecasting, a NULL in a sales quantity column means “we don’t know”. It could be due to a missing transaction, a CDC gap, or a field that doesn’t apply to that record type. Treating it as zero would artificially deflate demand, leading to under stocking.

The approach has three pillars:

• Understand context: Why is this NULL? Missing transaction? CDC gap? Unpopulated field?
• Flag, don’t drop: Add companion boolean columns to preserve the “unknown” state for downstream models.
• Impute intelligently: Replace NULLs with context aware values (e.g., realistic random numbers) only where it makes business sense.

3.4 Silver: Intelligent Imputation

Instead of a blanket df.fillna(0) , I wrote fill_nulls_with_random_values() a context aware function that examines column name and data type to generate realistic random values.

def fill_nulls_with_random_values(df, seed=42):
    """
    Replaces NULLs with realistic random values based on column semantics.
    Always flags the original NULL state in a companion '_missing' column.
    """
    for field in df.schema.fields:
        c = field.name
        dtype = field.dataType
        c_lower = c.lower()

        # Numeric types
        if isinstance(dtype, (DoubleType, FloatType, IntegerType, LongType, ShortType, ByteType)):
            if any(k in c_lower for k in ['price', 'cost', 'amount']):
                rand_expr = F.round(F.rand(seed) * 240.0 + 10.0, 2)
            elif any(k in c_lower for k in ['profit', 'margin']):
                rand_expr = F.round(F.rand(seed) * 95.0 + 5.0, 2)
            elif any(k in c_lower for k in ['qty', 'quantity', 'inventory']):
                rand_expr = F.floor(F.rand(seed) * 49.0 + 1.0)
            elif any(k in c_lower for k in ['line_no', 'entry_no']):
                rand_expr = F.floor(F.rand(seed) * 90000.0 + 10000.0)
            else:
                rand_expr = F.round(F.rand(seed) * 99.0 + 1.0, 2)

            if isinstance(dtype, (IntegerType, LongType, ShortType, ByteType)):
                rand_expr = rand_expr.cast(dtype)
            else:
                rand_expr = rand_expr.cast(DoubleType())

            df = df.withColumn(
                c,
                F.when(F.col(c).isNull() | F.isnan(F.col(c)), rand_expr).otherwise(F.col(c))
            )

        # Date & Timestamp types
        elif isinstance(dtype, (DateType, TimestampType)):
            rand_days = F.floor(F.rand(seed) * 590).cast(IntegerType())
            rand_date = F.date_add(F.lit("2025-01-01"), rand_days)
            if isinstance(dtype, TimestampType):
                rand_date = rand_date.cast(TimestampType())
            df = df.withColumn(
                c,
                F.when(F.col(c).isNull(), rand_date).otherwise(F.col(c))
            )

        # Boolean type
        elif isinstance(dtype, BooleanType):
            rand_bool = F.when(F.rand(seed) > 0.5, F.lit(True)).otherwise(F.lit(False))
            df = df.withColumn(
                c,
                F.when(F.col(c).isNull(), rand_bool).otherwise(F.col(c))
            )

        # String type – generate plausible placeholder
        elif isinstance(dtype, StringType):
            prefix = clean_column_name(c)[:4].upper() or "VAL"
            if 'vendor' in c_lower and 'item' in c_lower:
                rand_str = F.concat(F.lit("VI-"), (F.floor(F.rand(seed) * 90000 + 10000)).cast("string"))
            elif 'item' in c_lower or c_lower == 'no':
                rand_str = F.concat(F.lit("ELR-"), (F.floor(F.rand(seed) * 9000 + 1000)).cast("string"))
            elif 'location' in c_lower:
                rand_str = F.when(F.rand(seed) < 0.34, F.lit("US-MAIN"))\
                           .when(F.rand(seed) < 0.67, F.lit("US-EAST"))\
                           .otherwise(F.lit("US-CENTRAL"))
            else:
                rand_str = F.concat(F.lit(f"{prefix}-"), (F.floor(F.rand(seed) * 9000 + 1000)).cast("string"))

            df = df.withColumn(
                c,
                F.when(F.col(c).isNull() | (F.trim(F.col(c)) == "") | (F.lower(F.trim(F.col(c))) == "null"),
                       rand_str).otherwise(F.col(c))
            )
    return df

Notice that I did not impute every NULL only where it made business sense. For critical columns like sales quantity, I often left the NULL and relied on the flag (next section).

3.5 Flagging the Unknown

The companion boolean columns were the true innovation. For every key column, I added a flag like financial_data_missing or qty_data_missing . This way, downstream models could decide whether to trust the value or treat it as an estimate.

This approach preserved data integrity while providing transparency exactly what a production forecasting system needs.

3.6 Gold: Aggregating Without Lying

With Silver cleaned and flagged, building the Gold mart was straightforward. I aggregated daily sales per item and location, and left NULLs as NULL in the aggregates. Why? Because SUM() and AVG() ignore NULLs so a day with no sales data will correctly show NULL instead of zero.

This gives the forecasting model a clear signal: “No data for this day” vs. “Zero sales on this day.” The flags ( financial_data_missing ) then allow the model to adjust its confidence or backfill using seasonal patterns.

-- Gold aggregator snippet
SELECT 
    item_no,
    location_code,
    calendar_date,
    SUM(sales_qty) AS actual_sales_qty,        -- NULL if no rows
    SUM(sales_amount_actual) AS sales_amount,
    SUM(cost_amount_actual) AS cost_amount,
    MAX(financial_data_missing) AS financial_data_missing,
    MAX(qty_data_missing) AS qty_data_missing
FROM silver_sales
GROUP BY item_no, location_code, calendar_date

The final Gold table is clean, consumable, and ready for ML / Power BI consumption.

4. Limitations & Considerations

While the pipeline solved the core data quality challenges, there are important limitations to be aware of:

• CDC latency: The OData API may not capture real time changes; there is a small delay between transaction posting and availability in the API.
• NULL ambiguity: Even with flags, a NULL could still mean multiple things (e.g., field not applicable vs. data missing). Business context is always required.
• Random imputation: While better than zero fill, random imputation introduces noise. For some columns, mean/median or predictive imputation might be more appropriate.
• Data volume: As the number of items and locations grows, the Gold aggregation may become expensive; partitioning by year/month is recommended.
• No write back: This pipeline is read only; the cleaned data is not written back to Business Central. That is handled by a separate team.

Despite these limitations, the pipeline successfully reduced manual effort and improved forecast accuracy.

5. Business Impact

The pipeline improves how Business Central data is prepared for forecasting by bringing the raw data into a structured and organized process. Instead of working directly with raw records, the data moves through different stages where it is stored, cleaned, transformed, and prepared for analysis. The Bronze layer keeps the original data, the Silver layer focuses on cleaning and preparing the data, and the Gold layer creates the final dataset required for forecasting and analytics. This separation makes the overall process easier to understand, maintain, and extend when new data is added. One of the most important parts of the solution is how missing values are handled. A missing value does not always mean zero. By preserving this difference and using missing data flags, the pipeline keeps important information that could otherwise be lost during data preparation. The three layer approach also makes the data easier to clean, transform, and use for downstream analytics and forecasting. The resulting dataset provides a more consistent and reliable foundation for reporting, Power BI dashboards, and machine learning models.

6. FAQs

7. Conclusion

A 3 layer Medallion pipeline with a “NULL is not 0” philosophy transforms 12 raw Business Central datasets into a clean, consumable demand forecast mart. Context aware imputation, boolean flags, and careful aggregation preserve the state of the data while reducing hidden assumptions in downstream processing.

The approach preserves missing data signals, supports context aware transformations, and provides a structured foundation for demand forecasting. Limitations include CDC latency and the need for business context when interpreting NULL values.

The key lesson is simple: NULLs are not zeros . They are valuable signals that tell you what you don’t know. By preserving and flagging them, you can build data pipelines that are honest, transparent, and ultimately more useful for decision making.