Why This Matters
Raw data: 10GB of server logs, inconsistent timestamps, missing values, duplicates.
Trusted insight: “Peak traffic occurs 2PM-4PM on weekdays; optimize server capacity accordingly.”
The gap between these is the entire data engineering discipline.
Data doesn’t start clean. It starts messy, inconsistent, incomplete. The journey from raw to trusted is deliberate transformation.
What You’ll Learn
By the end of this article, you’ll understand:
- Why data quality matters more than data quantity
- The systematic transformation pipeline: collect → clean → transform → validate → analyze
- What makes data “trusted” (reproducibility, lineage, validation)
- Common failure modes (silent errors, inconsistent definitions, lost context)
The Raw Reality: What Data Actually Looks Like
Example: E-Commerce Order Data
What you hope for:
order_id,customer_id,product,quantity,price,timestamp
1001,C501,Widget,2,19.99,2024-01-15 14:23:01
1002,C502,Gadget,1,49.99,2024-01-15 14:25:33
What you actually get:
order_id,customer_id,product,quantity,price,timestamp
1001,C501,Widget,2,19.99,2024-01-15 14:23:01
1002,C502,Gadget,1,49.99,Jan 15 2024 2:25PM
1003,C501,,2,19.99,2024-01-15T14:30:00Z
1004,NULL,Widget,0,19.99,2024-01-15 14:35:01
1002,C502,Gadget,1,49.99,2024-01-15 14:25:33
TOTAL,,,5,109.96,
Problems:
– Row 2: Inconsistent timestamp format
– Row 3: Missing product name
– Row 4: NULL customer, zero quantity
– Row 5: Duplicate of row 2
– Row 6: Summary row mixed with data
This is normal. Data is always messy.
The Transformation Pipeline: Five Stages
Stage 1: Collection
Goal: Get data from source systems.
# Extract from database
import pandas as pd
# Problem: What if connection fails mid-read?
# Problem: What if schema changed since yesterday?
# Problem: What if data is too large for memory?
def extract_orders(date):
"""Extract orders with basic validation."""
try:
df = pd.read_sql(f"""
SELECT order_id, customer_id, product,
quantity, price, timestamp
FROM orders
WHERE DATE(timestamp) = '{date}'
""", connection)
# Validate schema
expected_columns = ['order_id', 'customer_id', 'product',
'quantity', 'price', 'timestamp']
if list(df.columns) != expected_columns:
raise ValueError(f"Schema mismatch: {df.columns}")
return df
except Exception as e:
log_error(f"Extraction failed: {e}")
raise
Key principle: Fail fast with clear errors.
Stage 2: Cleaning
Goal: Fix quality issues, remove bad data.
def clean_orders(df):
"""Clean raw order data."""
# 1. Remove duplicate rows
df = df.drop_duplicates()
# 2. Remove summary/aggregate rows
df = df[df['order_id'].str.isdigit()]
# 3. Handle missing values
# Option A: Drop rows with missing critical fields
df = df.dropna(subset=['order_id', 'customer_id'])
# Option B: Fill with defaults for non-critical fields
df['product'] = df['product'].fillna('UNKNOWN')
# 4. Fix invalid values
df = df[df['quantity'] > 0] # Reject zero/negative quantities
df = df[df['price'] > 0]
# 5. Standardize formats
df['timestamp'] = pd.to_datetime(df['timestamp'],
errors='coerce')
# Drop rows where timestamp parsing failed
df = df.dropna(subset=['timestamp'])
return df
Critical decision: Drop bad data vs. fix it vs. flag it.
– Drop: Safe but loses information
– Fix: Risky (might introduce errors)
– Flag: Best for manual review, but requires workflow
Stage 3: Transformation
Goal: Derive new columns, aggregate, join.
def transform_orders(df):
"""Add derived fields and aggregations."""
# Add derived columns
df['order_total'] = df['quantity'] * df['price']
df['order_date'] = df['timestamp'].dt.date
df['hour_of_day'] = df['timestamp'].dt.hour
df['day_of_week'] = df['timestamp'].dt.day_name()
# Aggregate: Daily summary
daily_summary = df.groupby('order_date').agg({
'order_id': 'count',
'order_total': 'sum',
'customer_id': 'nunique'
}).rename(columns={
'order_id': 'order_count',
'order_total': 'revenue',
'customer_id': 'unique_customers'
})
return df, daily_summary
Key principle: Transformation logic is code — must be version-controlled, tested, reviewed.
Stage 4: Validation
Goal: Prove data quality with automated checks.
def validate_orders(df):
"""Run data quality checks."""
checks = []
# Check 1: No nulls in critical fields
critical_fields = ['order_id', 'customer_id', 'product',
'quantity', 'price', 'timestamp']
for field in critical_fields:
null_count = df[field].isnull().sum()
checks.append({
'check': f'{field}_not_null',
'passed': null_count == 0,
'details': f'{null_count} nulls found'
})
# Check 2: Reasonable value ranges
checks.append({
'check': 'quantity_positive',
'passed': (df['quantity'] > 0).all(),
'details': f"{(df['quantity'] <= 0).sum()} invalid"
})
checks.append({
'check': 'price_reasonable',
'passed': ((df['price'] > 0) & (df['price'] < 10000)).all(),
'details': f"{((df['price'] <= 0) | (df['price'] >= 10000)).sum()} outliers"
})
# Check 3: No duplicates
checks.append({
'check': 'no_duplicates',
'passed': not df.duplicated().any(),
'details': f"{df.duplicated().sum()} duplicates"
})
# Check 4: Consistent with yesterday
# (Would compare row counts, totals to previous day)
# Report
failed = [c for c in checks if not c['passed']]
if failed:
raise ValueError(f"Validation failed: {failed}")
return checks
This is what makes data “trusted”: Automated, repeatable quality checks.
Stage 5: Analysis & Serving
Goal: Answer business questions.
def analyze_traffic_patterns(df):
"""Answer: When is peak traffic?"""
# Aggregate by hour and day of week
hourly_pattern = df.groupby(['day_of_week', 'hour_of_day']).agg({
'order_id': 'count'
}).rename(columns={'order_id': 'order_count'})
# Find peak hours
peak = hourly_pattern.nlargest(5, 'order_count')
return {
'peak_hours': peak,
'insight': "Peak traffic occurs 2PM-4PM on weekdays"
}
The insight is only as good as the pipeline that produced it.
What Makes Data “Trusted”?
Property 1: Reproducibility
# Today: Run pipeline
result_today = pipeline(date='2024-01-15')
# Tomorrow: Re-run same pipeline
result_tomorrow = pipeline(date='2024-01-15')
# Must be identical
assert result_today.equals(result_tomorrow)
Requirements:
– Deterministic transformations (no randomness)
– Version-controlled code
– Immutable source data
Property 2: Lineage
Raw Data
↓ (cleaned: dropped 50 duplicates, filled 12 missing products)
Cleaned Data
↓ (transformed: added order_total, hour_of_day)
Transformed Data
↓ (validated: all checks passed)
Trusted Data
↓ (aggregated: daily summaries)
Dashboard Metric
You must be able to trace any insight back to raw data.
Property 3: Validation
# Every pipeline run
validation_results = {
'run_id': '20240115_143022',
'checks': [
{'check': 'no_nulls', 'passed': True},
{'check': 'quantity_positive', 'passed': True},
{'check': 'no_duplicates', 'passed': True},
{'check': 'row_count_reasonable', 'passed': True}
],
'metadata': {
'source_rows': 15234,
'output_rows': 15180,
'dropped': 54,
'execution_time': '3.2s'
}
}
Validation must be automatic, logged, and alerting.
Failure Modes: When Pipelines Break
Failure 1: Silent Errors
# Bug: Timestamp parsing fails silently
df['timestamp'] = pd.to_datetime(df['timestamp_raw'],
errors='ignore')
# Result: Bad timestamps remain as strings
# Analysis: "Mean order time" → crashes (can't average strings)
# OR WORSE: Converts to NaT, then dropna() loses data silently
Solution: Explicit error handling. Never use errors='ignore'.
Failure 2: Inconsistent Definitions
# Team A: "Active user" = logged in within 30 days
active_users_A = df[df['days_since_login'] <= 30]
# Team B: "Active user" = made purchase within 30 days
active_users_B = df[df['days_since_purchase'] <= 30]
# Dashboard shows both metrics
# Users confused: "Which is the real active user count?"
Solution: Single source of truth. Centralized metric definitions.
Failure 3: Lost Context
# Analyst: "Why did revenue drop 50% on Jan 15?"
# Data Engineer: *checks pipeline*
# Data Engineer: "Oh, we changed the duplicate removal logic that day"
# Analyst: "Why wasn't I told?!"
Solution: Data contracts, change logs, versioned schemas.
Cross-Domain Connections
Code: Transformation as Functions
From CODE-100.3: Functions:
# Data pipeline = function composition
trusted_data = validate(
transform(
clean(
extract(source))))
Statistics: Validation Rules
From STAT-100.1: What Does Data Tell Us?:
Statistical checks detect anomalies:
– Mean/std deviation suddenly change → data quality issue
– Distribution shape different → schema change or bug
Machine Learning: Data Quality = Model Quality
From ML-100.1: What Does Learning Mean?:
Garbage in, garbage out. Bad data → bad models, regardless of algorithm sophistication.
Pattern Passport
Pattern Observed: TRANSFORMATION with VALIDATION
How It Appears Here:
– Pipeline = sequence of deterministic transformations
– Validation = automated quality checks at each stage
– Lineage = traceability from insight back to raw data
– Trust = reproducibility + validation + lineage
Representation:
– Raw data: Unprocessed, potentially messy
– Clean data: Quality issues resolved
– Transformed data: Derived fields, aggregations
– Trusted data: Validated, documented, reproducible
Transformation Rules:
– Extract: Source → Raw
– Clean: Raw → Clean (drop/fix/flag bad data)
– Transform: Clean → Enriched (derive, join, aggregate)
– Validate: Enriched → Trusted (automated checks)
– Serve: Trusted → Insights (analysis, dashboards, models)
Assumptions:
– Source data is accessible and relatively stable
– Quality issues are detectable (not adversarial)
– Transformations are deterministic
– Validation rules capture important quality dimensions
Failure Conditions:
1. Silent errors: Bad data passes through undetected
2. Inconsistent definitions: Same term means different things
3. Lost lineage: Can’t trace insight to source
4. Non-reproducible: Re-running gives different results
Related Disciplines:
– Code CODE-100.3: Transformation as function composition
– Statistics STAT-100.1: Quality checks via statistical properties
– ML ML-100.1: Data quality determines model quality
Next Learning Steps:
1. DATA-100.2: Data Modeling — Schema design and normalization
2. DATA-200.3: Data Pipelines — Production pipeline patterns
3. DATA-300.1: Data Quality — Advanced validation strategies
Summary: Data Trust is Earned, Not Assumed
Trustworthy data requires deliberate engineering.
Three key insights:
- Messiness is normal: Raw data is always messy. Plan for it.
- Validation is critical: Automated checks make data trusted.
- Lineage enables debugging: Must trace insights to sources.
What makes data trusted:
– Reproducibility (same input → same output)
– Lineage (traceable from insight to raw)
– Validation (automated quality checks)
What breaks trust:
– Silent errors (bad data undetected)
– Inconsistent definitions (confusion across teams)
– Lost context (can’t explain changes)
– Non-determinism (random results)
The value: Trusted data enables confident decisions. Untrusted data is worse than no data.
Exercises
-
Debug the pipeline: Given
df['price'].mean() = NaN, what could have gone wrong in cleaning/transformation? -
Design validation: For user registration data (name, email, age, country), write 5 validation checks.
-
Lineage practice: Draw the lineage diagram for “Monthly revenue” metric, starting from raw transaction logs.
-
Trade-offs: When should you drop bad data vs. fix it vs. flag it for review?
Revision History
2026-07-30: Substantially revised to include concrete pipeline code, failure modes, validation examples.
2024-04-25: Originally published.
Reproducible Code
All code in this article is available at:
– Code/data_pipeline_example.py — Complete pipeline example
– validation/test_pipeline.py — Pipeline validation tests
Run tests:
cd Categories/06-Data-and-Microsoft-Fabric/100.1-From-Raw-Data-to-Insight
python validation/test_pipeline.py