How to Build a Modern Data Stack for E-Commerce Logistics on Google Cloud

1. Executive Summary & Business Context

In the highly competitive European e-commerce landscape of 2026, managing return logistics (Reverse Logistics) is no longer a peripheral operational issue; it is a core determinant of profitability. In the fashion and apparel sectors, return rates consistently hover between 40% and 50%.

The primary business challenge is the inability to accurately calculate the True Margin of an individual Stock Keeping Unit (SKU). Traditional financial reporting relies on a generalized allocation of costs. For example, if total monthly shipping and warehouse costs are €100,000, and the company shipped 10,000 orders, a flat €10 cost is attributed to every order. This “average cost” method masks critical inefficiencies.

A specific dress might have a high conversion rate and appear profitable based on the initial sale price versus the cost of goods sold (COGS). However, if that dress has a 60% return rate, incurs cross-border return shipping fees, and requires significant repacking labor, the actual True Margin for that SKU might be deeply negative. Marketing departments often unwittingly scale ad campaigns for these “loss leaders” because they lack visibility into the downstream logistics costs.

1.1. The Technical Problem: Data Silos and Asynchronous Events

To calculate True Margin, data must be unified. However, the data lifecycle of a returned e-commerce order is inherently asynchronous and fragmented across multiple independent SaaS platforms:

  1. E-commerce Frontend (e.g., Shopify): Captures the initial transaction, customer details, gross revenue, and applied discounts (Day 0).
  2. Logistics Provider (e.g., DHL/DPD): Generates billing data based on volumetric weight and cross-border zones. This data often arrives in batched CSV files via SFTP (Day 2 to Day 20).
  3. Warehouse Management System (WMS): Tracks the physical receipt of the returned item, condition grading, and repacking labor costs. This is often communicated via webhooks (Day 15 to Day 30).

1.2. The Objective

The objective is to design, deploy, and automate an ELT (Extract, Load, Transform) data platform on Google Cloud. This platform will ingest data from Shopify, DHL, and the WMS, model the asynchronous events into a unified Slowly Changing Dimension (SCD Type 2) framework, and output a granular, SKU-level True Margin data mart.

2. Solution Architecture (2026 Standards)

We utilize a Modern Data Stack tailored for Google Cloud, focusing on robust, highly scalable micro-batching.

  • Extraction & Loading (EL): Airbyte Cloud / OSS. Handles API pagination, rate limiting, and incremental extraction logic out-of-the-box, moving raw JSON/CSV data directly into BigQuery.
  • Storage & Compute: Google BigQuery. Serving as the central Data Warehouse.
  • Transformation (T): Google Cloud Dataform. Natively integrated into BigQuery, it allows data engineers to build SQL-based dependency graphs, manage transformations via Git, and automate table materializations.
  • Infrastructure as Code (IaC): Terraform. Defines the infrastructure declaratively.
  • Orchestration: Cloud Scheduler & Dataform API. Used to trigger the pipeline sequentially.

3. Step 1: Infrastructure Deployment (Terraform)

The following Terraform configuration establishes a secure perimeter, provisions the data warehouse structure, creates strict Identity and Access Management (IAM) roles, and initializes the Dataform environment.

3.1. BigQuery and Dataform Setup

# provider.tf
terraform {
  required_version = ">= 1.9.0"
  backend "gcs" {
    bucket  = "tf-state-ecom-data-prod"
    prefix  = "terraform/state"
  }
}

provider "google" {
  project = "ecom-logistics-prod-2026"
  region  = "europe-west3" # Frankfurt (EU Data Residency)
}

# bigquery.tf
locals {
  datasets = {
    raw_data   = "Stores raw, untransformed data loaded by Airbyte"
    staging    = "Stores cleaned and historically tracked data (SCD2)"
    data_marts = "Stores final business-facing aggregation tables"
  }
}

resource "google_bigquery_dataset" "dwh_layers" {
  for_each                   = local.datasets
  dataset_id                 = each.key
  description                = each.value
  location                   = "europe-west3"
  delete_contents_on_destroy = false 
}

# dataform.tf
resource "google_dataform_repository" "dwh_repo" {
  provider     = google-beta
  name         = "ecom-logistics-transformations"
  region       = "europe-west3"
  git_remote_settings {
    url                                 = "https://github.com/your-org/ecom-dataform-models.git"
    default_branch                      = "main"
    authentication_token_secret_version = "projects/ecom-logistics-prod-2026/secrets/github-token/versions/latest"
  }
}

4. Step 2: Data Ingestion Configuration (Airbyte)

With the raw_data dataset provisioned, Airbyte is configured to extract data from the source systems.

  1. Shopify (Orders & Products): Configured as Incremental Sync - Append. Every time an order is updated, a new row is appended to raw_data.shopify_orders.
  2. WMS (Warehouse Events): Ingests webhooks via a custom REST API connector. Contains payload data: {"order_id": "123", "status": "RETURNED", "timestamp": "...", "labor_cost_eur": 2.50}. Lands in raw_data.wms_status_events.
  3. DHL (Courier Invoices): Reads monthly CSV billing files via SFTP, mapped by Tracking Number. Lands in raw_data.courier_invoices.

At the end of this step, the raw_data dataset contains highly granular, append-only logs. The data requires heavy transformation to build the True Margin view.

5. Step 3: Data Transformation (Google Cloud Dataform)

Dataform transforms the append-only logs into a structured, relational model.

5.1. Project Initialization

The workflow_settings.yaml defines the default output location for the transformed data.

# workflow_settings.yaml
dataformCoreVersion: "3.0.0"
defaultProject: "ecom-logistics-prod-2026"
defaultLocation: "europe-west3"
defaultDataset: "staging"
defaultAssertionDataset: "dataform_assertions"

5.2. Source Declarations

We declare the raw tables loaded by Airbyte so Dataform can reference them dynamically.

// definitions/sources/declarations.js
declare({
  database: "ecom-logistics-prod-2026",
  schema: "raw_data",
  name: "shopify_orders",
  description: "Raw append-only stream of Shopify orders"
});

declare({
  database: "ecom-logistics-prod-2026",
  schema: "raw_data",
  name: "wms_status_events",
  description: "Raw warehouse status updates and labor costs"
});

declare({
  database: "ecom-logistics-prod-2026",
  schema: "raw_data",
  name: "courier_invoices",
  description: "Itemized shipping costs from DHL"
});

5.3. Implementing Slowly Changing Dimensions (SCD Type 2)

Because orders change states asynchronously (Created -> Shipped -> Returned -> Refunded), we cannot simply use a standard UPDATE statement. We need to track the exact timeline of the order to calculate costs accurately.

We achieve this using BigQuery Window Functions (LEAD) to create an SCD Type 2 view over the append-only log.

-- definitions/staging/dim_orders_scd2.sqlx
config {
  type: "view",
  schema: "staging",
  description: "SCD Type 2 view tracking the historical state of every order"
}

WITH deduplicated_events AS (
  -- WMS and Shopify might send duplicate events; we take the latest per timestamp
  SELECT 
    order_id,
    sku,
    status,
    labor_cost_eur,
    event_timestamp
  FROM (
    SELECT 
      order_id, sku, status, labor_cost_eur, event_timestamp,
      ROW_NUMBER() OVER (PARTITION BY order_id, event_timestamp ORDER BY _airbyte_emitted_at DESC) as rn
    FROM ${ref("wms_status_events")}
  )
  WHERE rn = 1
)

SELECT
  order_id,
  sku,
  status,
  labor_cost_eur,
  event_timestamp AS valid_from,
  -- Calculate the end of this status's validity period
  LEAD(event_timestamp) OVER (PARTITION BY order_id ORDER BY event_timestamp) AS valid_to,
  -- Identify the current active state of the order
  CASE 
    WHEN LEAD(event_timestamp) OVER (PARTITION BY order_id ORDER BY event_timestamp) IS NULL 
    THEN TRUE 
    ELSE FALSE 
  END AS is_current_status
FROM deduplicated_events

5.4. Building the True Margin Data Mart

The final data mart joins the initial order revenue with the aggregated historical costs (warehouse labor and shipping).

-- definitions/marts/mart_true_margin_sku.sqlx
config {
  type: "table",
  schema: "data_marts",
  description: "SKU-level True Margin accounting for all reverse logistics costs"
}

WITH current_order_state AS (
  SELECT order_id, status 
  FROM ${ref("dim_orders_scd2")}
  WHERE is_current_status = TRUE
),

accumulated_wms_costs AS (
  -- Sum all labor costs incurred throughout the return process
  SELECT order_id, SUM(labor_cost_eur) AS total_wms_labor_cost
  FROM ${ref("dim_orders_scd2")}
  GROUP BY order_id
),

logistics_costs AS (
  -- Aggregate shipping fees (outbound + return shipping)
  SELECT tracking_number, SUM(total_cost) AS total_shipping_cost
  FROM ${ref("courier_invoices")}
  GROUP BY tracking_number
)

SELECT
  so.order_id,
  so.sku,
  so.created_at AS order_date,
  cos.status AS current_order_status,
  so.gross_revenue_eur,
  so.cogs_eur AS cost_of_goods_sold,
  COALESCE(lc.total_shipping_cost, 0) AS logistics_cost,
  COALESCE(awc.total_wms_labor_cost, 0) AS reverse_logistics_labor_cost,
  
  -- The Core Business Metric: True Margin
  (so.gross_revenue_eur 
   - so.cogs_eur 
   - COALESCE(lc.total_shipping_cost, 0) 
   - COALESCE(awc.total_wms_labor_cost, 0)
  ) AS true_margin_eur

FROM ${ref("shopify_orders")} so
LEFT JOIN current_order_state cos ON so.order_id = cos.order_id
LEFT JOIN accumulated_wms_costs awc ON so.order_id = awc.order_id
-- Assuming tracking number is mapped in the Shopify raw data
LEFT JOIN logistics_costs lc ON so.tracking_number = lc.tracking_number 

-- Filter to only include latest version of the Shopify order row
QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY so.updated_at DESC) = 1

6. Step 4: Orchestration and Production Deployment

To ensure data freshness, the Dataform pipeline must be executed on a schedule. In Google Cloud, this is handled securely via Cloud Scheduler, which invokes the Dataform REST API using a dedicated Service Account.

# orchestration.tf
resource "google_service_account" "scheduler_sa" {
  account_id   = "sa-scheduler-dataform"
  display_name = "Cloud Scheduler Service Account"
}

resource "google_project_iam_member" "scheduler_dataform_editor" {
  project = "ecom-logistics-prod-2026"
  role    = "roles/dataform.editor"
  member  = "serviceAccount:${google_service_account.scheduler_sa.email}"
}

resource "google_cloud_scheduler_job" "dataform_daily_run" {
  name             = "trigger-dwh-transformations"
  description      = "Compiles and executes Dataform models daily"
  schedule         = "0 2 * * *" # Runs every day at 2:00 AM
  time_zone        = "Europe/Berlin"
  region           = "europe-west3"

  http_target {
    http_method = "POST"
    # Note: In a full production setup, this URI triggers a Cloud Workflow which handles
    # Compilation -> Execution sequentially. For brevity, this targets the invocation endpoint.
    uri         = "https://dataform.googleapis.com/v1beta1/projects/ecom-logistics-prod-2026/locations/europe-west3/repositories/ecom-logistics-transformations/workflowInvocations"
    
    oauth_token {
      service_account_email = google_service_account.scheduler_sa.email
    }
    
    body = base64encode(jsonencode({
      compilationResult = "projects/ecom-logistics-prod-2026/locations/europe-west3/repositories/ecom-logistics-transformations/compilationResults/LATEST"
    }))
  }
}

7. Conclusion and Business Impact

By implementing this architecture, the e-commerce company successfully dismantles data silos.

The financial department transitions from the “average cost” method to precise, SKU-level unit economics. The Dataform SCD Type 2 logic guarantees that asynchronous logistical events are perfectly aligned with the original transaction. Finally, marketing teams can connect BI tools directly to the mart_true_margin_sku table, allowing them to instantly halt ad spend on items that appear profitable on Day 1, but yield a negative True Margin by Day 30 due to reverse logistics.

Similar Posts