Integration Test (Data)

Unlike unit tests that check isolated logic, integration tests verify the handoffs and data flow between connected pipeline components. This includes testing:

• Data connectors and ingestion from sources (APIs, databases, files).
• Transformation logic across multiple processing steps (e.g., SQL joins, Spark jobs).
• Load operations into target systems (data warehouses, feature stores). The goal is to catch bugs that only appear when components interact, such as schema mismatches, data type coercion errors, or incorrect join logic.

Effective integration tests use data that accurately mimics production in volume, variety, and relationships. Common strategies include:

• Subsetted production data (anonymized if necessary).
• Synthetically generated data that replicates production distributions and constraints.
• Golden datasets with known, verified outputs. The data must be complex enough to exercise edge cases like NULL handling, duplicate records, and referential integrity across systems. Testing with trivial or perfectly clean data fails to reveal integration flaws.

These tests enforce the data contracts between pipeline stages. They verify that the output of one component meets the schema, quality, and semantic expectations of the downstream consumer. Key validations include:

• Schema conformity: Column names, data types, and constraints are preserved.
• Business logic integrity: Aggregations and calculations remain correct after data moves between systems.
• Idempotency: Re-running the pipeline with the same input produces identical, non-duplicated output. This ensures the pipeline as a whole delivers a reliable data product.

Integration tests must manage and validate state across systems that are inherently stateful, such as databases, message queues, and object stores. This involves:

• Setup and teardown: Creating and cleaning up test databases, topics, or buckets.
• Isolation: Using separate test environments or containers to avoid polluting production.
• Concurrency checks: Ensuring the pipeline handles simultaneous reads/writes correctly. Testing stateful interactions is more complex and slower than testing stateless functions, requiring careful orchestration.

Integration tests act as gated checks within a CI/CD pipeline for data. A failing test should prevent bad data from propagating. This is implemented through:

• Pipeline-gated tests: Critical validation steps that halt pipeline execution on failure.
• Continuous testing: Automated execution on schedule or on data arrival.
• Canary releases: Routing a small percentage of live data through a new pipeline version and validating it before full cutover. This shifts data quality validation left, catching integration issues before they impact downstream analytics or machine learning models.

Beyond checking for errors, integration tests assess the overall fitness of the integrated data for its intended use. This encompasses:

• Freshness: Data is delivered within the expected SLA.
• Volume: Record counts are within expected ranges after each processing stage.
• Accuracy: Values are correctly transformed and joined, verified against a trusted source.
• Consistency: The same business logic applied in different parts of the pipeline yields matching results. These tests often leverage dynamic thresholds based on historical patterns rather than static rules.

A comprehensive test that validates the entire Extract, Transform, Load (ETL) process from source to target. This involves:

• Extracting data from a source (e.g., an API, database table).
• Applying all business logic transformations (joins, filters, aggregations).
• Loading the final dataset into a target system (data warehouse, feature store).
• Asserting that the output matches the expected schema, row counts, and key metric values derived from a golden dataset. This test catches errors in transformation logic, data type mismatches, and connectivity issues between systems.

Tests that verify the correctness of SQL joins or DataFrame merges across multiple source tables. Key checks include:

• Referential Integrity: Ensuring foreign keys in a fact table have corresponding primary keys in dimension tables, preventing orphaned records.
• Cardinality: Validating that a one-to-many join does not unintentionally create a many-to-many relationship, leading to data duplication.
• Business Logic: Confirming that join conditions (e.g., date ranges, status flags) correctly filter and combine records as defined by domain rules. A failure here often indicates flawed business logic or dirty source data.

Tests that ensure summary statistics and Key Performance Indicators (KPIs) are computed accurately from raw data. This is critical for financial reporting and dashboards. Examples include:

• Validating that SUM(revenue) matches a pre-calculated total.
• Ensuring COUNT(DISTINCT user_id) for daily active users is correct.
• Checking that complex window functions (e.g., running totals, moving averages) produce the expected results. These tests often use a small, hand-calculated dataset as the source of truth to verify the aggregation logic.

Tests that validate pipeline resilience to changes in source data structure. This is essential for continuous integration of data pipelines. Scenarios tested:

• Adding a new nullable column to a source table should not break the ingestion job.
• Removing an unused column that the pipeline depends on should trigger a controlled failure.
• Changing a data type (e.g., INT to BIGINT) should be handled gracefully, often through schema validation rules. These tests prevent production outages when upstream systems evolve.

Integration tests that measure and validate Service Level Objectives (SLOs) for timeliness. This goes beyond unit testing to measure the performance of the integrated system.

• Freshness: Asserting that a dashboard table is updated within 15 minutes of the source transaction.
• Latency: Measuring the total time from a source system event to its appearance in the consuming application.
• Throughput: Verifying the pipeline can process the expected volume of records within the required time window. These are often pipeline-gated tests that fail if SLOs are breached.

Tests that validate the pipeline's behavior when integrated components fail or produce invalid data. This tests robustness, not just happy paths.

• Malformed Records: Injecting a record with an invalid JSON structure into a streaming source to ensure it is routed to a dead letter queue (DLQ) for inspection without halting the entire pipeline.
• API Failure: Simulating a temporary outage of a third-party API and verifying the pipeline implements retry logic with exponential backoff.
• Duplicate Detection: Testing that a deduplication step correctly handles the same record being ingested multiple times. These tests ensure the system degrades gracefully.

An isolated test that validates the logic and output of a single data transformation—such as a SQL view, a Python function, or a dbt model—against a fixed set of input data. It focuses on the correctness of one component in isolation before integration.

• Purpose: Verify the internal logic of a transformation (e.g., a calculated column, a filter condition).
• Scope: Single function, query, or model.
• Example: Testing that a net_revenue column correctly calculates gross_revenue - tax - discount for a given input dataframe.

A formal agreement, often codified as code or schema definitions, that specifies the expected interface for a data product. It establishes clear guarantees between data producers and consumers, serving as the foundation for integration tests.

• Key Elements: Schema (column names, data types), data freshness (SLA), semantic meaning, and quality guarantees.
• Role in Testing: Provides the "specification" against which integration tests validate that the pipeline's output conforms to the promised interface.
• Example: A Protobuf schema or an Avro schema published by a service, guaranteeing the structure of its output Kafka topic.

A data quality test whose failure prevents the pipeline from proceeding to the next stage. It acts as a mandatory quality gate, blocking invalid or anomalous data from propagating downstream. Integration tests are often implemented as gated tests.

• Mechanism: Integrated into pipeline orchestration (e.g., Airflow, Dagster) to fail the task or DAG run.
• Objective: Enforce quality at stage boundaries, ensuring only valid data moves from source → transform → sink.
• Example: A test that validates referential integrity after a join; if it fails, the load to the data warehouse is halted.

A trusted, high-quality reference dataset that represents the expected output of a data pipeline or transformation. It serves as the source of truth for validating integration test results.

• Creation: Curated from historical, verified production data or manually constructed for complex business logic.
• Use Case: In integration testing, the pipeline's output is compared (e.g., via a data diff) against the golden dataset to validate correctness.
• Management: Must be version-controlled and updated deliberately as business rules evolve.

The automated coordination and scheduling of data quality test execution. For integration tests, this involves managing dependencies, triggering suites based on pipeline events, and handling retries or failures.

• Components: Test execution engine, dependency resolver, scheduler, result aggregator.
• Frameworks: Tools like Great Expectations Checkpoints, dbt Cloud Slim CI, or custom orchestration within platforms like Apache Airflow and Prefect.
• Outcome: Ensures integration tests run at the correct point in the pipeline lifecycle (e.g., post-merge, pre-deployment, on a production schedule).

The process of testing data against complex, domain-specific logic that defines correct business relationships and calculations. This is the core objective of many integration tests, moving beyond basic schema checks.

• Scope: Validates that the semantics of the data are correct after multiple transformations.
• Examples:
  • Ensuring total monthly sales in a summary table equals the sum of daily transactions.
  • Validating that customer tier assignments follow the correct business logic across joined tables.
• Implementation: Often requires custom SQL or Python assertions that encode business knowledge.