Unit Test (Data)

A data unit test validates a single transformation—like a SQL view, Python function, or dbt model—in complete isolation from its upstream sources and downstream dependencies. It uses a fixed, minimal dataset (fixture) as input to ensure the test is deterministic and repeatable. The output is compared against a known, expected result. This isolation prevents flaky tests caused by external data changes and pinpoints logic errors to a specific component.

• Example: Testing a calculate_discount function with a fixture containing 5 specific order records to verify the discount logic outputs exact, pre-calculated amounts.

The primary goal is to verify business logic and transformation rules, not to performance-test on large datasets. Tests use small, representative fixtures that cover edge cases and branching logic (e.g., NULL handling, conditional aggregations, type casting). This contrasts with integration tests that use larger volumes of data to validate performance and scale.

• Key checks: Data type conversions, conditional CASE statements, join logic correctness, aggregation functions (SUM, AVG), and custom scalar functions.
• Anti-pattern: Using a full copy of a production table as the test input, which is slow and non-deterministic.

Data unit tests are defined declaratively (stating what the outcome should be) using configuration in YAML, SQL, or a framework-specific DSL, rather than imperative procedural code. They are designed for automated execution within CI/CD pipelines or data pipeline orchestration tools (like Airflow, dbt Cloud). A test failure typically blocks pipeline progression, acting as a quality gate.

• Framework Examples: dbt test (YAML/SQL), Great Expectations (JSON/YAML Expectation Suites), Soda Core (YAML checks).
• Automation Trigger: Tests run on every pull request, commit to a data model, or as a pipeline-gated step before merging to production.

Reliable data unit tests depend on fixtures—small, static files (CSV, JSON, Parquet) or seed data that define the test input. The expected output is also predefined, often as a golden dataset. This golden dataset is a trusted, version-controlled artifact that serves as the source of truth for the transformation's correct behavior. Maintaining these fixtures is a core part of test hygiene.

• Fixture Management: Stored in version control (e.g., tests/fixtures/).
• Golden Dataset Creation: Often generated once from a verified manual run of the transformation and then used for all future regression testing.

Because they are isolated and use small fixtures, data unit tests must execute quickly (typically in milliseconds or seconds). This speed enables a high test coverage strategy, where many small tests validate individual columns, calculations, and rules across a data asset. High coverage ensures most transformation logic is verified, reducing the risk of bugs propagating downstream.

• Metric: Test coverage measures the percentage of columns or business rules in a dataset that have associated unit tests.
• Benefit: Fast feedback to developers during the development cycle, enabling Test-Driven Development (TDD) for data.

It is critical to distinguish data unit tests from data integration tests. Unit tests are isolated, logic-focused, and fast. Integration tests validate the interaction between multiple components (e.g., a full pipeline from source to target table), often using larger, anonymized production-like data in a staging environment.

• Unit Test: "Does my clean_phone_number UDF return '+1-800-555-1234' for input '8005551234'?"
• Integration Test: "Does the nightly customers pipeline correctly join 10 source tables and produce the expected row count and schema for the entire dataset?" A robust testing strategy employs both layers for comprehensive data quality.

This test confirms that a dataset's structure matches its defined contract. It verifies column names, data types, and nullability constraints.

• Key Checks: Column existence, data type (e.g., INTEGER, TIMESTAMP), and whether columns are nullable or not.
• Example: A test ensuring a customer_id column is of type VARCHAR and NOT NULL.
• Tools: Often implemented using declarative testing frameworks like dbt tests or Soda Core checks defined in YAML.

This test ensures that key columns contain only unique values, enforcing data integrity and preventing duplicate records.

• Key Checks: Verifies that a specified column or combination of columns (a composite key) has no duplicate values.
• Example: A test asserting that the transaction_id column in a fact table is unique.
• Impact: Critical for maintaining referential integrity in dimensional models and preventing aggregation errors.

This test validates that relationships between tables are intact, ensuring all foreign key values have a corresponding primary key in the related table.

• Key Checks: Confirms that values in a foreign key column exist in the referenced primary key column.
• Example: A test ensuring every product_id in an orders table exists in the products dimension table.
• Purpose: Prevents orphaned records and ensures join operations return correct results.

This test verifies that data values fall within an expected, predefined domain or range, catching outliers and invalid entries.

• Key Checks: Values in a column are within a set list (e.g., status in ['open', 'closed', 'pending']) or a numeric range (e.g., age between 0 and 120).
• Dynamic Thresholds: Advanced implementations may use statistical baselines instead of hard-coded ranges.
• Example: A test ensuring a discount_percentage column contains values between 0 and 100.

This test validates the volume and timeliness of data, acting as a smoke test for pipeline completeness.

• Volume Checks: Asserts that the number of rows in a table meets expectations (e.g., more than zero, within 10% of yesterday's count).
• Freshness Checks: Validates that the most recent record's timestamp is within a specified time window (e.g., data is less than 1 hour old).
• Use Case: Often used as a pipeline-gated test to block executions that produce empty or stale datasets.

This test encodes domain-specific rules that data must satisfy, representing core business rule validation.

• Key Checks: Complex logic expressed in SQL or Python, such as "total invoice amount equals the sum of line item amounts" or "a customer's lifetime value cannot be negative."
• Implementation: Can be written as a SQL WHERE clause that should return zero rows if the rule holds true.
• Example: A test verifying that the revenue column in a summary table matches the aggregated sales from the underlying transaction table.

A programmatic check embedded within a data pipeline that verifies a specific condition about the data and raises an error or warning if the condition is false. While a unit test validates transformation logic, an assertion is the atomic building block of that test.

• Examples: Checking for non-null values, verifying row counts match expectations, ensuring a value is within a defined range.
• Key Role: Acts as the executable validation rule that makes a unit test pass or fail.

A test that validates the correct interaction and data flow between multiple connected components of a data pipeline. It operates at a higher level than a unit test.

• Scope: Tests the integration of multiple SQL views, Python functions, or pipeline stages.
• Purpose: Ensures that joins, aggregations, and sequencing across components produce the expected final output.
• Analogy: If a unit test checks a single function, an integration test checks the entire workflow that uses that function.

A declarative or programmatic statement that defines a condition data must satisfy to be considered valid. Unit tests are often composed of one or more data quality rules executed against the output of a transformation.

• Types: Schema rules (data type, nullability), statistical rules (value range, distribution), and business logic rules.
• Implementation: Can be expressed in YAML (e.g., Soda Core), SQL (e.g., dbt tests), or Python (e.g., Great Expectations).

A trusted, high-quality reference dataset that serves as the source of truth for expected results in data testing. It is the benchmark against which the output of a unit test is compared.

• Usage: Contains known-good input/output pairs for a transformation.
• Critical for: Validating that refactored or optimized code produces identical results to a legacy, verified implementation.
• Maintenance: Must be version-controlled and updated only with intentional, reviewed changes.

A software development methodology applied to data engineering where data quality tests (including unit tests) are written before the data pipeline or transformation code is implemented.

• Process: 1. Write a failing test for a desired transformation. 2. Write the minimal code to make the test pass. 3. Refactor the code while ensuring tests continue to pass.
• Benefit: Ensures the pipeline is built to satisfy explicit, automated quality requirements from the outset, leading to more robust and verifiable systems.

A data quality test whose failure prevents a data pipeline from proceeding to the next stage of execution. A critical unit test is often configured as a pipeline gate.

• Function: Acts as a quality checkpoint or circuit breaker within an orchestration tool like Apache Airflow or Prefect.
• Objective: To block corrupted or invalid data from propagating downstream to consumers, data warehouses, or machine learning models.
• Outcome: On failure, the pipeline halts or alerts, triggering an incident response.