Data Testing — Pipelines, Ingestion, Upstream/Downstream & Structured vs Unstructured¶
What this is: the complete data-testing foundation — what a data pipeline is, how ingestion works, every component explained, snapshots and their role in downstream testing, upstream vs downstream applications with examples, data types, structured vs unstructured data, and how to test each. The hands-on code lives in the companion: Python for Data Testing.
1. What Is a Data Pipeline?¶
A data pipeline is an automated series of steps that moves data from where it is produced to where it is used, transforming it along the way. Think of it as a factory conveyor belt: raw material (source data) enters at one end, is cleaned, shaped, and combined at stations along the belt, and finished goods (analytics-ready data) come out the other end.
flowchart LR
SRC["📥 Sources<br/>apps, DBs, APIs, files, events"] --> ING["🚚 Ingestion<br/>batch or streaming"]
ING --> STG["🗃️ Staging / Raw zone<br/>land data as-is"]
STG --> TR["⚙️ Transformation<br/>clean · join · aggregate"]
TR --> ST["🏛️ Storage<br/>warehouse / lake"]
ST --> SRV["📤 Serving<br/>BI, ML, APIs, reports"]
ORCH["🕹️ Orchestration (Airflow etc.)"] -.-> ING & TR & SRV
MON["📊 Monitoring & data quality"] -.-> ING & TR & STTwo dominant patterns:
| Pattern | Order | Where transform happens | Typical stack |
|---|---|---|---|
| ETL (Extract → Transform → Load) | Transform before loading | A processing engine mid-pipeline | Informatica, SSIS, Spark |
| ELT (Extract → Load → Transform) | Load raw first, transform inside the warehouse | The warehouse itself (SQL) | Fivetran + dbt + Snowflake/BigQuery |
Modern cloud stacks favour ELT: land everything raw (cheap storage), then transform with versioned SQL — which also makes testing easier, because every intermediate table is queryable.
2. What Is Data Ingestion?¶
Ingestion is the first pipeline stage: collecting data from source systems and landing it in your platform. The key design (and testing) split is batch vs streaming:
| Batch ingestion | Streaming ingestion | |
|---|---|---|
| How | Scheduled bulk loads (hourly/nightly) | Continuous event-by-event flow |
| Tech | SFTP files, DB extracts, API pulls | Kafka, Kinesis, Pub/Sub, CDC |
| Latency | Minutes–hours | Seconds or less |
| Test focus | Completeness of each load, schedule reliability, re-run safety | Ordering, duplicates, late/out-of-order events, exactly-once semantics |
| Classic bug | Partial file loaded after job crash | The same event consumed twice (double-counted revenue) |
Ingestion tests that matter most:
- Row-count reconciliation — source count vs landed count per load (the #1 catch-all).
- Schema contract — the incoming file/API still has the expected columns and types (upstream teams change things without telling you).
- Idempotency / re-run safety — re-running yesterday's load must not duplicate rows.
- Late & malformed data — a corrupt row should go to a reject/quarantine table, not silently vanish or kill the whole load.
3. Components of a Data Pipeline (Each Explained)¶
| Component | What it does | What QA tests there |
|---|---|---|
| Sources | Systems that produce data — OLTP databases, apps, SaaS APIs, log files, IoT events | Availability, contract/schema, sample-data fidelity |
| Ingestion layer | Moves data in (batch jobs, connectors, Kafka consumers, CDC) | Counts, duplicates, ordering, re-run safety |
| Staging / raw zone | Lands data unchanged ("bronze") — the audit copy | Raw = source (byte/row fidelity), load timestamps |
| Transformation engine | Cleans, deduplicates, joins, aggregates (dbt models, Spark jobs, SQL) | Business-rule correctness, join integrity, null handling |
| Storage | Warehouse (Snowflake, BigQuery, Redshift) or lake (S3/Parquet, Delta) | Partitioning, schema evolution, permissions |
| Serving layer | Marts, dashboards, ML feature stores, APIs | Query results vs expected, freshness, SLA |
| Orchestrator | Schedules and sequences steps, handles retries (Airflow, Dagster, ADF) | Dependency order, failure handling, alert firing |
| Monitoring / DQ layer | Data-quality checks, lineage, freshness alerts (Great Expectations, dbt tests) | The tests themselves — do they catch seeded bad data? |
4. How a Data Pipeline Works — A Concrete Run¶
Nightly example: an e-commerce company builds a daily sales report.
01:00 Orchestrator triggers the DAG (the dependency graph of steps)
01:01 Ingest: pull yesterday's orders from the OLTP DB (batch extract)
→ land 84,213 rows into raw.orders (reconcile: source said 84,213 ✅)
01:10 Ingest: consume clickstream events from Kafka → raw.events
01:20 Transform 1: clean — trim strings, standardise currency, null-check emails
01:30 Transform 2: join orders ↔ customers ↔ products (star schema)
01:45 Transform 3: aggregate → daily_sales_by_region
01:50 DQ gate: row counts within ±5% of 7-day average · no null order_ids ·
revenue total matches source ledger → PASS
01:55 Publish to the mart; dashboard cache refreshed
02:00 Freshness SLA met (report ready before 08:00 business start)
Every arrow in that run is a test point: did the right number of rows move, did the transformation apply the business rule correctly, and did the quality gate actually gate?
5. Snapshots — and How They're Used in Downstream Testing¶
A snapshot is a captured copy of a dataset at a specific point in time — "the customers table as of midnight, 1 June."
Why snapshots exist:
- History for slowly changing data. Source systems overwrite in place (a customer's address today replaces yesterday's). Snapshots (or SCD Type-2 tables built from them) preserve what the data looked like when — essential for "revenue by customer region at the time of purchase".
- Reproducibility. Live data changes constantly; a snapshot is frozen, so tests against it are deterministic.
How snapshots power downstream testing:
| Use | How it works |
|---|---|
| Golden baseline / regression | Run the pipeline on a frozen input snapshot → capture the output → that output becomes the expected result. Every code change re-runs on the same snapshot and diffs against the baseline. Any difference = a regression (or an intended change to re-baseline). |
| Snapshot comparison (before/after) | Snapshot a table before and after a migration or refactor; assert row-by-row equality (or explained differences only). |
| Time-travel debugging | "The report was right Monday, wrong Tuesday" → diff the Monday vs Tuesday snapshots of each upstream table to find which input changed. |
| Test-environment seeding | Restore a (masked!) production snapshot into staging so downstream apps are tested against realistic data shapes and volumes. |
| Drift detection | Compare today's snapshot statistics (null %, distinct counts, distributions) against last week's — silent upstream changes surface as drift. |
The key testing idea: pin the input, pin the expectation. Non-reproducible inputs are to data testing what non-determinism is to LLM testing — snapshots are how you get determinism back.
6. Upstream vs Downstream Applications¶
Position is relative to where you stand in the flow: upstream = before you (produces your input), downstream = after you (consumes your output).
flowchart LR
U1["🏪 POS system"] & U2["🌐 Web/app events"] & U3["🏦 Payment gateway"] & U4["📇 CRM (Salesforce)"] -->|upstream| P["⚙️ YOUR PIPELINE<br/>warehouse & marts"]
P -->|downstream| D1["📈 BI dashboards<br/>(Power BI / Tableau)"]
P --> D2["🤖 ML models<br/>(churn, fraud, forecasting)"]
P --> D3["📧 Marketing automation<br/>(segments, campaigns)"]
P --> D4["🧾 Finance & regulatory<br/>reports"]
P --> D5["🔌 Data APIs / partner feeds"]| Upstream examples | Downstream examples | |
|---|---|---|
| What they are | Systems that generate the data you ingest | Systems that consume what your pipeline produces |
| Examples | OLTP order database, POS tills, website clickstream, payment gateway (Adyen/Stripe), CRM (Salesforce), HR system (Workday), third-party market-data feeds | Executive BI dashboards, ML models (fraud scoring, churn prediction), marketing segmentation tools, finance/regulatory reporting, recommendation engines, RAG knowledge bases |
| Testing concern | Contract testing — will their changes break my ingestion? (schema change, new enum value, format shift) | Impact testing — will my changes break their consumption? (renamed column kills a dashboard; shifted distribution silently degrades an ML model) |
| Classic incident | Upstream adds a new order status "partially_refunded" → your transform's CASE statement buckets it as NULL → revenue under-reported |
You "fix" a join that was double-counting → the churn model, trained on the double-counted feature, starts mis-scoring |
Rule of thumb: you inherit upstream risk and export downstream risk. Data contract tests guard the first; impact analysis + snapshot regression guard the second.
7. Data Basics — Types of Data¶
7.1 The three shapes of data¶
| Shape | Definition | Examples | Where stored |
|---|---|---|---|
| Structured | Fixed schema — rows and typed columns | Orders table, CSV of transactions, customer records | Relational DBs, warehouses |
| Semi-structured | Self-describing, flexible nesting — no rigid table | JSON, XML, YAML, Avro, API payloads, logs | Document DBs, lakes, VARIANT columns |
| Unstructured | No inherent schema at all | PDFs, emails, images, audio, video, free text | Object storage (S3/blob), content systems |
7.2 Common column data types (the vocabulary)¶
| Family | Types | Testing gotchas |
|---|---|---|
| Numeric | INT, BIGINT, DECIMAL(p,s), FLOAT |
Float rounding in money (always use DECIMAL for currency); overflow on BIGINT ids |
| Text | VARCHAR(n), TEXT, CHAR |
Truncation at n; encoding (UTF-8 vs Latin-1); trailing whitespace |
| Temporal | DATE, TIMESTAMP, TIMESTAMPTZ |
Timezones (the #1 data bug family); DST edges; epoch vs ISO formats |
| Boolean | BOOLEAN |
Sources encode as 1/0, "Y"/"N", "true"/"True" — mapping errors |
| Complex | ARRAY, STRUCT/OBJECT, JSON |
Null vs empty array; nested schema drift |
| Identifiers | UUID, surrogate keys |
Uniqueness, referential integrity |
8. Structured vs Unstructured — How to Test Each¶
8.1 Testing structured data (the six dimensions of data quality)¶
Structured data has a schema, so tests are precise assertions on rows and columns:
| Dimension | Question | Example test |
|---|---|---|
| Completeness | Is anything missing? | Row-count reconciliation vs source; email IS NOT NULL for active customers |
| Uniqueness | Any duplicates? | COUNT(*) = COUNT(DISTINCT order_id) |
| Validity | Do values obey rules? | status IN ('placed','shipped','delivered','cancelled'); amount ≥ 0 |
| Consistency | Does it agree across tables/systems? | Sum of order_lines.amount = orders.total; warehouse revenue = ledger revenue |
| Accuracy | Does it match reality/source? | Sample-based field-by-field compare to source system |
| Timeliness / freshness | Is it up to date? | MAX(loaded_at) > NOW() - INTERVAL '2 hours' |
Plus structural tests: schema checks (columns, types, order), referential integrity (every customer_id in orders exists in customers), and business-rule tests (discount ≤ price). Tooling: SQL assertions, dbt tests, Great Expectations / Pandera, and pandas-based checks — see the Python companion.
8.2 Testing unstructured data¶
No schema → you can't assert cell values. Instead you test around the content, at three levels:
- File/object level (deterministic): the file exists, size > 0, correct format (magic bytes —
%PDF,\x89PNG), checksum matches after transfer, count of objects matches manifest, no corrupt/unreadable files. - Extraction level: unstructured data is usually converted to structured/semi-structured downstream (OCR text from PDFs, transcripts from audio, labels from images). Test the extraction pipeline: extraction success rate, text-length sanity vs source, spot-check accuracy on a labelled golden sample (e.g., "these 50 PDFs must yield these key fields"), table/layout fidelity.
- Metadata & statistical level: every object has required metadata (source, timestamp, classification); population statistics stay in range (avg document length, language mix, image resolution distribution) — drift here signals upstream change.
Bridge to AI testing: unstructured-data testing is exactly what RAG ingestion testing does with documents (chunking fidelity, OCR accuracy), and extraction quality is scored with the same golden-sample technique as LLM evaluation. Data testing and AI testing meet here.
8.3 Side-by-side¶
| Structured | Unstructured | |
|---|---|---|
| Assertion style | Exact (counts, sums, constraints) | Indirect (integrity, extraction quality, statistics) |
| Ground truth | The source system | A labelled golden sample |
| Automation | SQL / dbt / pandas — fully deterministic | File checks deterministic; content checks sample-based |
| Failure signature | Wrong number somewhere | Silent content loss (parsed 90% of the PDF, dropped the table) |
9. Data Structures Involved in Data Testing¶
The structures you'll actually manipulate when writing data tests:
| Structure | What it is | Role in testing |
|---|---|---|
| Table / relation | Rows × typed columns | The primary test subject (assert on rows/aggregates) |
| DataFrame | In-memory table (pandas/Spark) | The workbench — load, compare, profile datasets in code |
| Schema | Column names + types + constraints | The contract you assert against (and version) |
| Primary / foreign keys | Uniqueness + relationships | Uniqueness and referential-integrity tests |
| Partition | Physical split (usually by date) | Test scope + completeness per partition |
| Snapshot | Point-in-time frozen copy | Baselines, diffs, reproducibility (§5) |
| Dictionary / hash map | Key→value lookup | Row-level compare by key; O(1) reconciliation |
| Set | Unique unordered values | Fast "what's in A but not B" (missing/extra keys) |
| DAG | Directed acyclic graph of steps | Orchestration order + lineage/impact analysis |
| Queue / log | Ordered event stream (Kafka topic) | Streaming tests — ordering, offsets, exactly-once |
The set-difference trick alone (source_keys - target_keys = lost rows, target_keys - source_keys = phantom rows) solves half of real-world reconciliation — implemented in the Python companion.
10. Quick Reference¶
| Question | One-line answer |
|---|---|
| Data pipeline? | Automated flow: sources → ingest → transform → store → serve, orchestrated + monitored |
| Ingestion? | Getting data in — batch (scheduled loads) or streaming (continuous events) |
| ETL vs ELT? | Transform before load vs load raw then transform in-warehouse (modern default) |
| Snapshot? | Point-in-time frozen copy → golden baselines, diffs, reproducible tests |
| Upstream? | Systems producing your input (OLTP, CRM, payments) — contract-test them |
| Downstream? | Systems consuming your output (BI, ML, finance) — impact-test for them |
| Structured testing? | Six DQ dimensions + schema + referential integrity, in SQL/dbt/pandas |
| Unstructured testing? | File integrity → extraction quality vs golden sample → statistical drift |
Where to Go Next¶
- Python for Data Testing — pandas, DataFrames, and runnable versions of every test above
- RAG Automation Testing Roadmap — data testing applied to AI ingestion
- AI Rollout — Part 2 — ingestion & vector data testing in the enterprise strategy
- Advanced API Testing — testing the APIs that feed pipelines