Skip to content
Ordermatic Docs

dbt Test Failure: Null Customer IDs in Shipping Addresses

Date: 2025-12-07
Severity: Medium (Data Quality Test Failure)
Status: Open - Needs Decision
Component: apps/dagster/dbt/models/silver/stg_shipping_addresses.sql

Summary

Section titled “Summary”

The dbt test not_null_stg_shipping_addresses_customer_id is failing with 11,659 null values for connection 49fc4591-1eb5-41d7-88e7-d8a88730cd83. This causes the entire dbt_transform job to fail, even though most models and tests pass successfully.

Error Details

Section titled “Error Details”
28 of 39 FAIL 11659 not_null_stg_shipping_addresses_customer_id [FAIL 11659 in 7.50s]


Failure in test not_null_stg_shipping_addresses_customer_id (models/silver/schema.yml)
  Got 11659 results, configured to fail if != 0


Done. PASS=40 WARN=0 ERROR=1 SKIP=1 NO-OP=0 TOTAL=42

Root Cause Analysis

Section titled “Root Cause Analysis”

Data Flow

Section titled “Data Flow”
┌──────────────────────────┐
│  raw_shipping_addresses  │  (from Iceberg/P21)
│  - customer_id (often    │
│    NULL in raw data)     │
└───────────┬──────────────┘
            │
            ▼
┌──────────────────────────┐
│   stg_shipping_addresses │
│   - COALESCE(            │
│       links.erp_customer │◄──┐
│       addr.customer_id)  │   │
└───────────┬──────────────┘   │
            │                  │
            │        LEFT JOIN │
            │                  │
┌───────────▼──────────────┐   │
│    stg_ship_to_links     │───┘
│    - Filtered WHERE      │
│      erp_customer_id     │
│      IS NOT NULL         │
└──────────────────────────┘

The Problem

Section titled “The Problem”

  1. stg_shipping_addresses computes customer_id as:

    COALESCE(links.erp_customer_id, addr.customer_id) AS customer_id

  2. LEFT JOIN with stg_ship_to_links - If no matching link exists, links.erp_customer_id is NULL

  3. Fallback to addr.customer_id - The raw shipping address data often doesn’t have customer_id populated (it’s optional in the source ERP system)

  4. Result: 11,659 shipping addresses have NULL customer_id because:

    • No entry exists in ship_to_links for that shipping address
    • The raw shipping_addresses table has NULL customer_id

Why This Is Expected Behavior

Section titled “Why This Is Expected Behavior”

In Prophet 21 (and many ERP systems), shipping addresses can exist in two states:

  1. Ship-To Addresses - Linked to a customer via ship_to_links (have customer_id)
  2. Standalone Addresses - Created during orders but not formally linked to a customer

The dbt test assumes ALL shipping addresses must have a customer, which is too strict for this data model.

Current Test Definition

Section titled “Current Test Definition”
apps/dagster/dbt/models/silver/schema.yml
- name: stg_shipping_addresses
  description: 'Deduplicated shipping addresses'
  columns:
    - name: customer_id
      tests:
        - not_null # ← This test is too strict

Impact

Section titled “Impact”
Impact Description
Job Failure The entire dbt_transform job fails even though 40 other tests pass
Downstream Effects Gold layer dim_shipping_addresses is skipped
Data Freshness Shipping address data becomes stale in Typesense
Section titled “Recommended Solutions”
Section titled “Option 1: Change Test Severity (Recommended)”

Change from hard failure to warning - allows pipeline to continue while flagging data quality issues:

- name: customer_id
  tests:
    - not_null:
        severity: warn
        config:
          warn_if: '>10000' # Allow known baseline

Option 2: Remove the Test

Section titled “Option 2: Remove the Test”

If shipping addresses without customers are valid business data, remove the test entirely:

# Remove customer_id not_null test
- name: customer_id
  description: 'Customer ID (nullable for standalone addresses)'

Option 3: Fix at Transformation Level

Section titled “Option 3: Fix at Transformation Level”

Assign a placeholder customer_id for orphaned addresses:

COALESCE(
  links.erp_customer_id,
  addr.customer_id,
  'ORPHANED-' || addr.erp_shipping_id  -- Fallback placeholder
) AS customer_id

Not recommended - this masks the data quality issue and creates fake IDs.

Section titled “Option 4: Improve ship_to_links Coverage”

Investigate why ship_to_links doesn’t cover all shipping addresses. This may require changes to the bronze extraction.

Recommendation

Section titled “Recommendation”

Implement Option 1 - Change the test to a warning with a threshold.

This approach:

  • Allows the pipeline to complete successfully
  • Preserves visibility into data quality issues
  • Doesn’t mask or alter the underlying data
  • Can be fine-tuned as data quality improves

Files to Modify

Section titled “Files to Modify”
File Change
apps/dagster/dbt/models/silver/schema.yml Change not_null test severity to warn

Quick Fix

Section titled “Quick Fix”
apps/dagster/dbt/models/silver/schema.yml
- name: stg_shipping_addresses
  description: 'Deduplicated shipping addresses'
  columns:
    # ... other columns ...


    - name: customer_id
      description: 'Customer ID (nullable for standalone/orphan addresses)'
      tests:
        - not_null:
            severity: warn
            config:
              warn_if: '>15000' # Allow baseline + buffer
              error_if: '>50000' # Alert if dramatically worse
Section titled “Related Issues”
  • Typesense sync runs successfully even when dbt fails (shipping addresses were synced: 5000 records)
  • The gold layer dim_shipping_addresses is skipped due to test failure

Verification After Fix

Section titled “Verification After Fix”

After implementing the fix, verify:

  1. dbt_transform job completes with warnings (not errors)
  2. dim_shipping_addresses model builds successfully
  3. Typesense sync includes shipping addresses from the gold layer