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.

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:
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.
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.
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:
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_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.
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:
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:
source_id is always a string — integers, UUIDs, and vendor-prefixed strings all become strings. You never lose the original value.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.
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.
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).
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.
When you can't match on email (B2B with inconsistent emails, anonymized datasets, IoT device data), you need a deterministic approach. Options include:
rapidfuzz or Levenshtein distance — good for company names with minor variations ("Acme Corp" vs "Acme Corporation")phonenumbers libraryFor 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.
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.
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.
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.
Even after you've aligned grain to "daily," you still have to handle the fact that "daily" means different things to different sources:
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.
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.
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.
Scenario: You're building a unified customer_daily_activity table for a SaaS company. You have three sources:
app_events with columns: user_id (int), event_type (varchar), event_ts (timestamptz), session_id (uuid)id (charge ID), customer (cus_XXX), amount (integer cents), created (unix timestamp), customer_email (string)AccountId, Email, NumberOfEmployees, AnnualRevenue, LastActivityDateYour tasks:
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.
Write a grain-alignment function that aggregates app_events to customer-day grain, producing event_count and unique_sessions per customer per day.
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.
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).
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?
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).
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.
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.
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.
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.
Cross-source data harmonization is the unglamorous backbone of a trustworthy data warehouse. The core disciplines you've practiced here are:
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.