Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Data Engineering

Implementing Cross-Source Data Harmonization in Transformation Pipelines

When three source systems describe the same customer with three different key formats, three date representations, and three incompatible revenue definitions, your warehouse will silently lie to you. Learn how to align schemas, surrogate keys, and temporal grain across multiple ingestion sources before anything reaches your warehouse.

⚡ Practitioner22 min readNov 15, 2026Updated Nov 15, 2026
Implementing Cross-Source Data Harmonization in Transformation Pipelines
On this page
  • Aligning Schemas, Keys, and Grain Across Multiple Ingestion Sources Before Loading to Your Warehouse
  • Introduction
  • Prerequisites
  • Why Harmonization Belongs in the Transformation Layer
  • Step 1: Auditing Your Sources — Building a Source Map
  • Step 2: Defining a Canonical Schema
  • Step 3: Building Source-Specific Mappers
  • Step 4: Surrogate Key Resolution — Linking the Same Entity Across Sources
  • The Easy Case: Shared Email as a Match Key
  • The Harder Case: No Shared Identifier
Step 5: Aligning Grain — The Most Underappreciated Problem
  • Identifying Grain Explicitly
  • Resolving Grain Before Joining
  • Step 6: Handling Temporal Misalignment
  • Step 7: Validating Harmonized Output Before Loading
  • Step 8: Structuring the Harmonization Layer in Your Pipeline DAG
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • "My customer counts don't add up after harmonization"
  • "Timestamps are off by exactly one hour on certain days"
  • "Revenue numbers differ between my harmonized table and the source BI reports"
  • "Schema evolution in one source is breaking all three mappers"
  • "The pipeline runs fine but the warehouse has duplicate customer_keys after backfill"
  • Summary & Next Steps
  • Implementing Cross-Source Data Harmonization in Transformation Pipelines

    Aligning Schemas, Keys, and Grain Across Multiple Ingestion Sources Before Loading to Your Warehouse


    Introduction

    You've finally connected all your data sources to your pipeline. The Salesforce CRM extract is running, the PostgreSQL transactional database is syncing, and the third-party billing API is delivering JSON payloads on schedule. Everything looks green. Then you try to join customers from Salesforce with accounts from your billing system and immediately hit a wall: Salesforce uses AccountId as a 15-character alphanumeric string, billing uses a UUID, and your Postgres DB uses an integer sequence that maps to neither. The order_date field is a Unix timestamp in one source, an ISO 8601 string in another, and a localized datetime with a timezone offset in the third. And somehow two of the systems count "revenue" differently — one includes taxes, one doesn't.

    This is cross-source data harmonization, and it's one of the most underestimated problems in data engineering. It's not glamorous. There's no neat framework that solves it in an afternoon. But getting it wrong means your warehouse is filled with data that looks clean but silently produces wrong answers. Getting it right means every analyst and BI tool downstream can trust that customer_id means the same thing everywhere, that revenue is revenue, and that a daily grain actually means daily.

    By the end of this lesson, you'll have concrete techniques and production-ready code for aligning schemas, standardizing keys, and enforcing consistent grain across multiple ingestion sources — before anything touches your warehouse.

    What you'll learn:

    • How to audit and document schema mismatches, key conflicts, and grain differences across source systems
    • Techniques for building a canonical schema that multiple sources can be mapped into
    • How to implement surrogate key resolution and cross-source entity matching
    • Strategies for aligning temporal grain (hourly, daily, weekly) across sources with different aggregation semantics
    • How to structure your transformation layer so harmonization logic is testable, maintainable, and not buried in one-off SQL

    Prerequisites

    You should be comfortable with SQL transformations and have some experience running Python-based data pipelines. If you haven't yet, review Building Your First Data Pipeline with Python and What is a Data Pipeline? Architecture and Core Concepts for Data Engineers before continuing. You should also have a basic mental model of the difference between ETL and ELT — if not, ETL vs ELT: Choosing the Right Approach covers the trade-offs you'll need to reason about here.


    Why Harmonization Belongs in the Transformation Layer

    Before we touch any code, let's settle an architectural question: where does harmonization happen?

    If you've worked in a modern ELT-style stack, your instinct might be to load raw data into the warehouse first and transform it later using something like dbt. That's often the right call for single-source transforms. But cross-source harmonization introduces a complication: some harmonization decisions require comparing sources, which is awkward when each source lands in its own raw schema and you're trying to reconcile them in SQL.

    There are two workable models:

    Model A — Harmonize in the warehouse (ELT) Load all sources raw, then use SQL transformation models to produce canonical tables. This works well when all sources land in the same warehouse and you have strong SQL tooling. The raw data is preserved and re-transformable if your harmonization logic changes.

    Model B — Harmonize in the pipeline (ETL) Normalize each source to a canonical schema in Python (or Spark) before writing to the warehouse. This is better when your sources don't all land in the same system, when you're sending data to a streaming sink, or when you want to enforce contracts before data ever hits storage.

    In practice, most production pipelines use a hybrid: you do structural harmonization (types, naming, units) in the pipeline, and semantic harmonization (entity resolution, grain alignment) in the warehouse layer. That's the approach we'll follow here.

    Key insight

    Harmonization is not a one-time data cleaning job. It's an ongoing architectural commitment. Every time a source changes its schema or a new source is added, your harmonization layer needs to handle it. Design accordingly from the start.


    Step 1: Auditing Your Sources — Building a Source Map

    You can't harmonize what you haven't catalogued. Before writing a single transform, build a source map: a structured document (or database table) that captures, for each source:

    • Entity name — what real-world thing does this table or endpoint represent?
    • Primary key — what identifies a unique record?
    • Key type and format — integer? UUID? composite? vendor-assigned?
    • Temporal fields — what date/time fields exist, and in what format/timezone?
    • Grain — what does one row represent? One event? One day's summary? One transaction?
    • Known oddities — nulls in required fields, source-specific business logic baked into values, etc.

    Here's a realistic example. Suppose you're building a unified customer analytics warehouse pulling from three sources:

    Attribute Salesforce CRM Stripe Billing PostgreSQL Orders
    Entity Account Customer user
    Primary key AccountId (15-char string) customer_id (cus_XXXX prefix) user_id (integer)
    Email field Email email email_address
    Created date CreatedDate (ISO 8601, UTC) created (Unix timestamp) created_at (timestamptz)
    Revenue field AnnualRevenue (estimated, USD) total_spend (cumulative, USD cents) order_total (per-order, USD)
    Grain One row per account One row per customer One row per order

    This table alone tells you that you have three different grains, three different key systems, two different date representations, and revenue measured in at least three incompatible ways. That's your harmonization backlog.

    Tip

    Automate your source map generation where you can. A Python script that connects to each source, introspects column names and types, and writes results to a metadata table will pay dividends when sources evolve. You can wire this into your orchestration layer as a pre-flight check that runs before each pipeline execution.


    Step 2: Defining a Canonical Schema

    A canonical schema is the agreed-upon structure every source gets mapped into. Think of it as a contract between your ingestion layer and your warehouse. It defines:

    • Standard column names
    • Standard data types
    • Standard units (all monetary values in USD cents as integers, all timestamps as UTC ISO 8601 strings, etc.)
    • Required vs. optional fields

    Here's a canonical customer schema in Python using dataclasses, which makes it easy to validate and document:

    from dataclasses import dataclass, field
    from typing import Optional
    from datetime import datetime
    
    @dataclass
    class CanonicalCustomer:
        """
        Canonical customer record for warehouse load.
        All sources must be mapped to this structure before writing.
        """
        # Surrogate key assigned by the harmonization layer
        customer_key: str                        # UUID, assigned during harmonization
    
        # Source tracking
        source_system: str                       # 'salesforce' | 'stripe' | 'postgres'
        source_id: str                           # Original ID in source system (always string)
    
        # Identity fields
        email: str                               # Lowercase, stripped
        name: Optional[str] = None              # Full name or company name
    
        # Temporal fields — always UTC ISO 8601
        created_at: Optional[str] = None        # e.g. "2023-04-15T10:30:00Z"
        updated_at: Optional[str] = None
    
        # Financial — always integer USD cents
        annual_revenue_cents: Optional[int] = None
    
        # Metadata
        harmonized_at: str = field(
            default_factory=lambda: datetime.utcnow().isoformat() + "Z"
        )
        is_active: bool = True
    

    Notice a few decisions embedded here:

    1. source_id is always a string — integers, UUIDs, and vendor-prefixed strings all become strings. You never lose the original value.
    2. Money is always integer cents — not floats. Float arithmetic on currency is a silent data quality hazard.
    3. Timestamps are always UTC ISO 8601 strings — this sidesteps timezone hell at the warehouse level.
    4. customer_key is a surrogate — we'll cover how to assign this in the next section.

    Warning

    Resist the temptation to make your canonical schema too permissive. If you add Optional to everything "for flexibility," you end up with a schema that every source technically satisfies while providing zero guarantees. Your canonical schema should enforce the minimum requirements your downstream consumers actually need.


    Step 3: Building Source-Specific Mappers

    Each source needs its own mapper — a function that takes raw source data and returns a CanonicalCustomer (or raises an explicit error when it can't). Do not write one monolithic transform that handles all sources with a tangle of conditionals. Separate mappers are independently testable and independently deployable.

    import uuid
    from datetime import datetime, timezone
    from typing import Dict, Any
    
    def map_salesforce_account(raw: Dict[str, Any]) -> CanonicalCustomer:
        """Map a Salesforce Account record to CanonicalCustomer."""
        
        # Salesforce CreatedDate is ISO 8601 with timezone: "2023-04-15T10:30:00.000+0000"
        created_at = None
        if raw.get("CreatedDate"):
            dt = datetime.fromisoformat(raw["CreatedDate"].replace("+0000", "+00:00"))
            created_at = dt.astimezone(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
        
        # AnnualRevenue is a float in USD — convert to integer cents
        revenue_cents = None
        if raw.get("AnnualRevenue") is not None:
            revenue_cents = int(round(float(raw["AnnualRevenue"]) * 100))
        
        return CanonicalCustomer(
            customer_key=str(uuid.uuid4()),  # Temporary; resolved against master in next step
            source_system="salesforce",
            source_id=str(raw["AccountId"]),
            email=(raw.get("Email") or "").strip().lower(),
            name=raw.get("Name"),
            created_at=created_at,
            annual_revenue_cents=revenue_cents,
        )
    
    
    def map_stripe_customer(raw: Dict[str, Any]) -> CanonicalCustomer:
        """Map a Stripe Customer object to CanonicalCustomer."""
        
        # Stripe 'created' is a Unix timestamp (integer seconds)
        created_at = None
        if raw.get("created"):
            dt = datetime.fromtimestamp(raw["created"], tz=timezone.utc)
            created_at = dt.strftime("%Y-%m-%dT%H:%M:%SZ")
        
        # Stripe total_spend is in cents already, but verify the currency
        revenue_cents = None
        if raw.get("metadata", {}).get("total_spend_cents") is not None:
            revenue_cents = int(raw["metadata"]["total_spend_cents"])
        
        return CanonicalCustomer(
            customer_key=str(uuid.uuid4()),
            source_system="stripe",
            source_id=str(raw["id"]),  # e.g. "cus_NxAlzVPBDGrpSk"
            email=(raw.get("email") or "").strip().lower(),
            name=raw.get("name"),
            created_at=created_at,
            annual_revenue_cents=revenue_cents,
        )
    
    
    def map_postgres_user(raw: Dict[str, Any]) -> CanonicalCustomer:
        """Map a PostgreSQL users row to CanonicalCustomer."""
        
        # PostgreSQL timestamptz comes in as a Python datetime from psycopg2
        created_at = None
        if raw.get("created_at") and isinstance(raw["created_at"], datetime):
            created_at = raw["created_at"].astimezone(timezone.utc).strftime(
                "%Y-%m-%dT%H:%M:%SZ"
            )
        
        # No revenue at this grain — it's per-order, not per-user
        return CanonicalCustomer(
            customer_key=str(uuid.uuid4()),
            source_system="postgres",
            source_id=str(raw["user_id"]),
            email=(raw.get("email_address") or "").strip().lower(),
            name=raw.get("full_name"),
            created_at=created_at,
            annual_revenue_cents=None,  # Intentionally null — not available at this grain
        )
    

    The key discipline here: every transformation decision is explicit and documented. When a future engineer asks "why is revenue null for Postgres customers?" the answer is right there in the code comment.


    Step 4: Surrogate Key Resolution — Linking the Same Entity Across Sources

    The hardest part of cross-source harmonization isn't cleaning data — it's figuring out that AccountId: 0015g00001ABC123 in Salesforce and cus_NxAlzVPBDGrpSk in Stripe are the same human being. This is entity resolution, and it ranges from trivially easy (both systems share a unique external ID) to genuinely difficult (fuzzy name matching with no shared identifiers).

    The Easy Case: Shared Email as a Match Key

    If your systems all reliably capture email (and you've normalized it to lowercase — which your mappers above do), email is a reasonable match key for customers. Build a resolution table:

    -- In your warehouse or staging database
    CREATE TABLE customer_entity_map (
        customer_key        UUID PRIMARY KEY,
        salesforce_id       VARCHAR,
        stripe_id           VARCHAR,
        postgres_user_id    BIGINT,
        canonical_email     VARCHAR NOT NULL,
        created_at          TIMESTAMPTZ DEFAULT NOW(),
        updated_at          TIMESTAMPTZ DEFAULT NOW()
    );
    
    CREATE UNIQUE INDEX idx_entity_map_email ON customer_entity_map(canonical_email);
    

    Then, before assigning customer_key, look up the email in this map:

    import psycopg2
    from typing import Optional
    
    def resolve_customer_key(
        conn,
        email: str,
        source_system: str,
        source_id: str
    ) -> str:
        """
        Resolve or create a canonical customer_key for a given email.
        Updates the entity map with the new source reference.
        """
        with conn.cursor() as cur:
            # Check if this email already has a canonical key
            cur.execute(
                "SELECT customer_key FROM customer_entity_map WHERE canonical_email = %s",
                (email,)
            )
            row = cur.fetchone()
            
            if row:
                # Entity already known — update the source reference
                customer_key = str(row[0])
                cur.execute(
                    f"""
                    UPDATE customer_entity_map
                    SET {source_system}_id = %s, updated_at = NOW()
                    WHERE canonical_email = %s
                    """,
                    (source_id, email)
                )
            else:
                # New entity — create a canonical key
                customer_key = str(uuid.uuid4())
                cur.execute(
                    """
                    INSERT INTO customer_entity_map 
                        (customer_key, canonical_email, {col})
                    VALUES (%s, %s, %s)
                    """.format(col=f"{source_system}_id"),
                    (customer_key, email, source_id)
                )
            
            conn.commit()
            return customer_key
    

    Warning

    Email as a match key sounds bulletproof but isn't. Corporate customers often have multiple email addresses per company. Service accounts use shared inboxes. B2B customers change email domains after acquisitions. Always build your entity map to allow multiple source records to map to one canonical key — and plan for manual override capabilities from day one.

    The Harder Case: No Shared Identifier

    When you can't match on email (B2B with inconsistent emails, anonymized datasets, IoT device data), you need a deterministic approach. Options include:

    • Fuzzy name matching using rapidfuzz or Levenshtein distance — good for company names with minor variations ("Acme Corp" vs "Acme Corporation")
    • Phone number normalization using phonenumbers library
    • Composite blocking — first narrow candidates by country + postal code, then apply name similarity within that block
    • ML-based entity resolution using tools like Splink or Zingg for high-volume cases

    For most practitioner use cases, the email + fallback strategy covers 90%+ of records. Build the probabilistic matching as a separate process that flags uncertain matches for review rather than silently merging entities.


    Step 5: Aligning Grain — The Most Underappreciated Problem

    Grain misalignment is what happens when you join two tables that look compatible but represent different levels of aggregation. It produces results that are numerically wrong in ways that are very hard to catch.

    Classic example: you have orders from Postgres at transaction grain (one row = one order), and you join to Stripe customer data (one row = one customer with a total_spend already summed). If you count rows per customer across the join, you'll double-count Stripe spend.

    Identifying Grain Explicitly

    Add grain documentation to every table or dataset in your pipeline. A simple convention is a comment in your model or transform file:

    # GRAIN: One row per customer per day per source_system
    # PRIMARY KEY: (customer_key, event_date, source_system)
    # NOTE: revenue_cents represents gross transaction value,
    #       excluding refunds. Refunds are in customer_refunds table.
    

    This seems like overkill until it's 11pm and you're debugging a discrepancy in an executive dashboard.

    Resolving Grain Before Joining

    The correct pattern: decide what grain your output needs, then aggregate or explode each source to match before the join. Never join at different grains and aggregate after.

    import pandas as pd
    from typing import List, Dict, Any
    
    def aggregate_orders_to_customer_daily(
        orders: List[Dict[str, Any]]
    ) -> pd.DataFrame:
        """
        Aggregate Postgres order-grain records to customer-day grain.
        Input: one row per order
        Output: one row per (customer_key, order_date)
        """
        df = pd.DataFrame(orders)
        
        # Normalize the date to date-only (drop time component)
        df["order_date"] = pd.to_datetime(df["created_at"]).dt.date
        
        daily = (
            df.groupby(["customer_key", "order_date"])
            .agg(
                order_count=("order_id", "count"),
                gross_revenue_cents=("order_total_cents", "sum"),
                distinct_products=("product_id", "nunique"),
            )
            .reset_index()
        )
        
        return daily
    
    
    def normalize_stripe_to_customer_daily(
        stripe_events: List[Dict[str, Any]]
    ) -> pd.DataFrame:
        """
        Stripe charge events are already at event grain — aggregate to daily.
        Input: one row per charge event
        Output: one row per (customer_key, charge_date)
        """
        df = pd.DataFrame(stripe_events)
        
        df["charge_date"] = pd.to_datetime(df["created_at"]).dt.date
        
        daily = (
            df.groupby(["customer_key", "charge_date"])
            .agg(
                charge_count=("charge_id", "count"),
                charged_cents=("amount_cents", "sum"),
                refunded_cents=("amount_refunded_cents", "sum"),
            )
            .reset_index()
            .assign(
                net_revenue_cents=lambda x: x["charged_cents"] - x["refunded_cents"]
            )
        )
        
        return daily
    

    Now when you join order_daily to stripe_daily on (customer_key, date), both sides are at the same grain and the join is semantically safe.

    Key insight

    Grain alignment errors are almost never caught by standard data quality checks because the data is valid — it's just at the wrong level of aggregation. The only reliable defense is explicit grain documentation and pre-join aggregation as a code pattern enforced by convention or tooling.


    Step 6: Handling Temporal Misalignment

    Even after you've aligned grain to "daily," you still have to handle the fact that "daily" means different things to different sources:

    • Salesforce syncs at 2am UTC
    • Stripe uses midnight UTC for day boundaries
    • Your Postgres transactional DB runs in US/Eastern time and your DevOps team never got around to converting timestamps to UTC

    This produces a subtle bug: on a given calendar day, the same transaction can appear on different dates in different source tables depending on timezone.

    The fix requires establishing a pipeline timezone contract — every source gets its timestamps converted to UTC before any date truncation. Your mappers already do this. But you also need to handle the edge cases:

    from datetime import datetime, timezone, timedelta
    from zoneinfo import ZoneInfo  # Python 3.9+
    
    def normalize_to_utc_date(raw_timestamp: Any, source_timezone: str) -> str:
        """
        Convert a source timestamp to a UTC date string (YYYY-MM-DD).
        
        Parameters
        ----------
        raw_timestamp : str, int, or datetime
            The raw timestamp from the source system.
        source_timezone : str
            IANA timezone string for the source, e.g. "America/New_York"
        
        Returns
        -------
        str
            UTC date as 'YYYY-MM-DD'
        """
        if isinstance(raw_timestamp, (int, float)):
            # Unix timestamp
            dt = datetime.fromtimestamp(raw_timestamp, tz=timezone.utc)
        elif isinstance(raw_timestamp, str):
            dt = datetime.fromisoformat(raw_timestamp)
            if dt.tzinfo is None:
                # Naive datetime — assume source timezone
                source_tz = ZoneInfo(source_timezone)
                dt = dt.replace(tzinfo=source_tz)
            dt = dt.astimezone(timezone.utc)
        elif isinstance(raw_timestamp, datetime):
            if raw_timestamp.tzinfo is None:
                source_tz = ZoneInfo(source_timezone)
                raw_timestamp = raw_timestamp.replace(tzinfo=source_tz)
            dt = raw_timestamp.astimezone(timezone.utc)
        else:
            raise ValueError(f"Unsupported timestamp type: {type(raw_timestamp)}")
        
        return dt.strftime("%Y-%m-%d")
    

    Tip

    Store your source timezone configuration in a central config file or metadata table alongside your source map. Hardcoding "America/New_York" directly in transform functions makes it very easy to forget to update when a source changes its data center or when daylight saving time causes off-by-one-hour bugs.


    Step 7: Validating Harmonized Output Before Loading

    Harmonization logic produces subtle bugs that only manifest in aggregates or joins — not in row-level validation. You need a validation layer that checks structural and semantic invariants.

    This integrates naturally with the data quality practices described in Data Quality: Validation, Testing, and Monitoring Pipelines. Here's a focused harmonization validation layer:

    from dataclasses import dataclass
    from typing import List, Tuple
    import pandas as pd
    
    @dataclass
    class HarmonizationValidationResult:
        passed: bool
        errors: List[str]
        warnings: List[str]
    
    def validate_harmonized_customers(df: pd.DataFrame) -> HarmonizationValidationResult:
        """
        Validate a DataFrame of harmonized CanonicalCustomer records.
        Returns structured result with errors (blocking) and warnings (non-blocking).
        """
        errors = []
        warnings = []
        
        # --- Structural checks ---
        required_cols = ["customer_key", "source_system", "source_id", "email"]
        missing = [c for c in required_cols if c not in df.columns]
        if missing:
            errors.append(f"Missing required columns: {missing}")
        
        # --- Key uniqueness: source_system + source_id must be unique ---
        dupes = df.duplicated(subset=["source_system", "source_id"]).sum()
        if dupes > 0:
            errors.append(
                f"{dupes} duplicate (source_system, source_id) combinations found. "
                "This indicates a mapper bug or double-processing."
            )
        
        # --- Email format ---
        invalid_emails = df[
            ~df["email"].str.match(r"^[^@\s]+@[^@\s]+\.[^@\s]+$", na=False)
        ]
        if len(invalid_emails) > 0:
            warnings.append(
                f"{len(invalid_emails)} records have invalid or empty email addresses. "
                f"These will not resolve to canonical keys. Sample source_ids: "
                f"{invalid_emails['source_id'].head(5).tolist()}"
            )
        
        # --- Revenue sense check: no negative revenue ---
        if "annual_revenue_cents" in df.columns:
            negative_rev = df[
                df["annual_revenue_cents"].notna() & (df["annual_revenue_cents"] < 0)
            ]
            if len(negative_rev) > 0:
                errors.append(
                    f"{len(negative_rev)} records have negative annual_revenue_cents. "
                    "Revenue should be non-negative in the canonical schema."
                )
        
        # --- Source system coverage ---
        expected_sources = {"salesforce", "stripe", "postgres"}
        actual_sources = set(df["source_system"].unique())
        missing_sources = expected_sources - actual_sources
        if missing_sources:
            warnings.append(
                f"No records found from source(s): {missing_sources}. "
                "This may indicate an upstream ingestion failure."
            )
        
        return HarmonizationValidationResult(
            passed=len(errors) == 0,
            errors=errors,
            warnings=warnings
        )
    

    Wire this into your pipeline so that validation failures halt the load. Warnings should be logged and alerted on without blocking — they're signals that need investigation but shouldn't cause data gaps. This kind of structured error handling connects well to the broader patterns in Data Pipeline Error Handling and Recovery Strategies.


    Step 8: Structuring the Harmonization Layer in Your Pipeline DAG

    Harmonization isn't a single step — it's a stage with multiple concerns: mapping, resolution, grain alignment, validation, and load. If you're using Airflow or a similar orchestrator, model it explicitly as a task group rather than one big "transform" task. This aligns with orchestration patterns in Scheduling and Orchestrating Pipelines with Airflow.

    [Ingest Salesforce]  [Ingest Stripe]  [Ingest Postgres]
             |                  |                |
        [Map to Canonical] [Map to Canonical] [Map to Canonical]
             \                  |               /
              \                 |              /
               [Resolve Entity Keys (entity_map)]
                               |
               [Align Grain to Customer-Daily]
                               |
               [Validate Harmonized Output]
                          /         \
                  [PASS]            [FAIL]
                    |                 |
             [Load to DWH]    [Dead-letter + Alert]
    

    Each task in this DAG should be independently restartable. Your entity resolution step in particular needs to be idempotent — running it twice on the same input should produce the same output, not create duplicate canonical keys. For patterns on building idempotent stages, see Designing Idempotent Data Pipelines: Guaranteeing Exactly-Once Semantics in Production.

    Note

    The entity map table (the customer_entity_map from Step 4) is a stateful component of your pipeline. It accumulates knowledge about cross-source relationships over time. Back it up, version it, and treat schema changes to it with the same care you'd give a production application database.


    Hands-On Exercise

    Scenario: You're building a unified customer_daily_activity table for a SaaS company. You have three sources:

    1. A Postgres table app_events with columns: user_id (int), event_type (varchar), event_ts (timestamptz), session_id (uuid)
    2. A Stripe charge events JSON feed with fields: id (charge ID), customer (cus_XXX), amount (integer cents), created (unix timestamp), customer_email (string)
    3. A Salesforce accounts CSV export with columns: AccountId, Email, NumberOfEmployees, AnnualRevenue, LastActivityDate

    Your tasks:

    1. Write source mappers for each of the three sources following the pattern in Step 3. Your canonical schema should cover: customer_key, source_system, source_id, email, activity_date (UTC date), and any source-specific numeric metrics.

    2. Write a grain-alignment function that aggregates app_events to customer-day grain, producing event_count and unique_sessions per customer per day.

    3. Build a customer_entity_map using email as the match key. Write a function that processes records from all three sources and produces a unified list of canonical customers where Salesforce, Stripe, and Postgres IDs are linked by email.

    4. Add two validation checks specific to this scenario: one that verifies annual_revenue_cents from Salesforce is within a reasonable range (say, $0 to $10B), and one that verifies no customer appears in the daily activity table with the same customer_key and activity_date from two different source systems on the same day (which would indicate a grain error).

    5. Write a brief design note explaining what you would do if 15% of Stripe customers have no email address (they were created programmatically). How does this affect your entity resolution strategy?


    Common Mistakes & Troubleshooting

    "My customer counts don't add up after harmonization"

    Cause: Entity resolution is merging customers that shouldn't be merged, or failing to merge customers that should be.

    Debug approach: Query your customer_entity_map for entries where more than three source IDs map to one customer_key. Review a sample manually. If legitimate, your match key is working. If you're seeing unlikely merges, your email normalization may be too aggressive (e.g., normalizing +alias Gmail addresses to base address when different people use the same base email at a company).

    "Timestamps are off by exactly one hour on certain days"

    Cause: Daylight saving time. A naive timestamp from a US Eastern source is UTC-5 in winter and UTC-4 in summer. If you hardcode -5 as an offset instead of using a proper timezone library, you'll be wrong half the year.

    Fix: Always use zoneinfo or pytz with a named IANA timezone, never a fixed offset. See the normalize_to_utc_date function in Step 6.

    "Revenue numbers differ between my harmonized table and the source BI reports"

    Cause: Almost always a currency unit mismatch (dollars vs. cents) or an aggregation scope difference (gross vs. net, including refunds vs. excluding). Check your mappers and your grain-alignment aggregations. Add a reconciliation query that compares your harmonized total to source-system totals for a known time period.

    "Schema evolution in one source is breaking all three mappers"

    Cause: Tight coupling between your mappers and source schema assumptions.

    Fix: Each mapper should defensively handle missing fields (.get() with defaults, never direct key access on untrusted dicts) and log unexpected fields rather than failing silently. For a deeper treatment of this, review Schema Evolution Strategies for Production Data Pipelines: Handling Breaking Changes Without Downtime.

    "The pipeline runs fine but the warehouse has duplicate customer_keys after backfill"

    Cause: Your entity resolution was not idempotent during the backfill. Running it on historical data created new UUIDs for customers that already had canonical keys.

    Fix: Entity resolution must always check the entity map first. If you're running a backfill, process records in chronological order so that the earliest occurrence of an email gets the canonical key, not whichever batch happened to process last.

    Warning

    Backfilling harmonization logic is significantly harder than backfilling raw ingestion. If your entity map changes (you discover two canonical keys should have been merged), you need to cascade that change through every downstream table. Plan for this by keeping customer_key as a foreign key in your warehouse and using it consistently — fixing the map then rebuilding downstream is much cleaner than hunting down hardcoded source IDs across twenty SQL models.


    Summary & Next Steps

    Cross-source data harmonization is the unglamorous backbone of a trustworthy data warehouse. The core disciplines you've practiced here are:

    • Source mapping — cataloguing every source's key format, temporal fields, grain, and quirks before writing any code
    • Canonical schema design — a strict, well-typed contract that every source gets mapped into
    • Dedicated source mappers — one mapper per source, testable in isolation, never a monolithic conditional transform
    • Surrogate key resolution — building an entity map that links cross-source identities to a single canonical key
    • Grain alignment — always aggregating or exploding to your target grain before joining, never after
    • Temporal normalization — UTC everywhere, named timezones in config, never hardcoded offsets
    • Pre-load validation — structural and semantic checks that block bad data before it reaches the warehouse

    The natural next step is wiring this harmonization layer into a robust, testable pipeline. Consider exploring Pipeline Testing: Unit Tests, Integration Tests, and Data Contracts to build confidence around your mappers and validation logic, and Understanding Data Pipeline Dependencies: Upstream, Downstream, and Lineage Tracking to track how harmonization decisions propagate through your entire data platform.

    Your harmonized output is only as reliable as the data flowing into it, so also invest in monitoring. The observability patterns in Logging, Alerting, and Observability for Data Pipelines will help you detect when a source starts behaving differently before it corrupts your canonical tables.

    Harmonization is never truly "done" — it's a living layer that evolves as your business adds sources, renames systems, and discovers new ways data can be inconsistent. Build it with that in mind from day one.

    Work With Us

    From insight to implementation

    Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

    Let's Build

    Data Pipeline Fundamentals

    Previous

    Transforming Data with SQL in Your Pipeline: Filtering, Joining, and Aggregating Before It Reaches Your Warehouse

    Related Insights

    Data EngineeringPractitioner

    Implementing Airflow Dataset-Aware Scheduling and Data-Driven DAG Triggers for Dependency Management Across Multi-Team Pipelines

    23 min
    Data EngineeringFoundation

    Snowflake Query Performance Tuning: Reading Query Profiles, Identifying Spill, and Optimizing Clustering Keys for Analytics Workloads

    20 min
    Data EngineeringFoundation

    Transforming Data with SQL in Your Pipeline: Filtering, Joining, and Aggregating Before It Reaches Your Warehouse

    18 min

    On this page

    • Aligning Schemas, Keys, and Grain Across Multiple Ingestion Sources Before Loading to Your Warehouse
    • Introduction
    • Prerequisites
    • Why Harmonization Belongs in the Transformation Layer
    • Step 1: Auditing Your Sources — Building a Source Map
    • Step 2: Defining a Canonical Schema
    • Step 3: Building Source-Specific Mappers
    • Step 4: Surrogate Key Resolution — Linking the Same Entity Across Sources
    • The Easy Case: Shared Email as a Match Key
    • The Harder Case: No Shared Identifier
    • Step 5: Aligning Grain — The Most Underappreciated Problem
    • Identifying Grain Explicitly
    • Resolving Grain Before Joining
    • Step 6: Handling Temporal Misalignment
    • Step 7: Validating Harmonized Output Before Loading
    • Step 8: Structuring the Harmonization Layer in Your Pipeline DAG
    • Hands-On Exercise
    • Common Mistakes & Troubleshooting
    • "My customer counts don't add up after harmonization"
    • "Timestamps are off by exactly one hour on certain days"
    • "Revenue numbers differ between my harmonized table and the source BI reports"
    • "Schema evolution in one source is breaking all three mappers"
    • "The pipeline runs fine but the warehouse has duplicate customer_keys after backfill"
    • Summary & Next Steps