28 July 2026

Automating ETL Testing with Python: A Data Validation Guide for Data Engineers

Q: What is ETL testing with Python?

ETL testing with Python uses libraries such as pandas, pytest, SQLAlchemy, and Great Expectations to automatically validate that data pipelines extract, transform, and load data correctly. Tests verify row counts, schemas, transformations, null values, referential integrity, and performance.

Q: What is the best Python library for ETL data validation?

Great Expectations is the leading Python library for ETL data validation. It enables teams to define reusable data quality expectations, automate validation, and generate human-readable reports. It is commonly used alongside pytest for complete ETL testing.

Q: How does automated ETL testing integrate with CI/CD?

Automated ETL tests integrate with GitHub Actions, Jenkins, GitLab CI, and Azure DevOps by running pytest or Great Expectations validation during every deployment. This helps identify data quality issues before changes reach production.

Q: What are the main types of ETL test cases?

The five primary ETL test types include data completeness, transformation accuracy, schema validation, referential integrity, and performance testing. Together they ensure data pipelines produce accurate and reliable outputs.

Q: How is ETL testing different from ETL migration validation?

ETL testing validates existing data pipelines during ongoing operations, while ETL migration validation compares outputs between legacy and migrated pipelines before production cutover. Both rely on similar Python-based validation techniques but serve different purposes.

Reliable data pipelines depend on reliable ETL testing. When data moves between systems through extract, transform, and load processes, errors in any stage include a missed row, a failed transformation, or a schema mismatch, which compound downstream and corrupt the reports and decisions built on top of them. Manual ETL testing cannot scale to the data volumes and pipeline complexity that modern enterprises manage.

Python has become the standard for automating ETL testing, giving data engineers a flexible, library-rich environment to validate data completeness, transformation accuracy, schema integrity, and pipeline performance. This guide covers the full approach from test case design to CI/CD integration with working code examples for each stage.

Quick Summary: Automating ETL testing with Python helps data engineers validate data quality, schema consistency, and transformation accuracy at scale. By combining pandas, pytest, SQLAlchemy, and Great Expectations, teams can detect data issues early, integrate validation into CI/CD pipelines, and ensure reliable ETL operations. The same automated validation techniques are also essential for verifying migrated ETL pipelines before production cutover.

automating-etl-testing-with-python-data-validation

Key Takeaways

Automated ETL testing is essential — Validate every run to catch data quality issues that manual checks miss.
Use pytest + Great Expectations together — pytest automates test execution, while Great Expectations enforces data quality rules.
Integrate testing into CI/CD — Prevent production issues by automatically validating every pipeline change.
Cover five core test types — Test data completeness, transformations, schema, referential integrity, and performance.
Use the same validation for ETL migrations — Row counts, schema, and output comparisons ensure migrated pipelines match legacy results.
Prioritize security and governance — Protect credentials, isolate test environments, and maintain audit logs.
Generate business-friendly validation reports — Great Expectations provides clear reports for technical and business stakeholders.

Why Automate ETL Testing?

Manual ETL testing is slow, inconsistent, and does not scale. A data engineer validating a pipeline manually checks sample rows, compares output formats, and runs spot checks for missing edge cases, rare transformation failures, and schema drift that only appear at volume or under specific data conditions.

Automated ETL testing addresses this by running the same validation logic against every record, every run, every deployment. The practical benefits are:

Consistency: The same checks run identically every time, eliminating human variability in what gets tested and what gets missed.
Speed: Automated test suites validate millions of records in the time manual checking covers hundreds.
Continuous validation: Integrated with CI/CD pipelines, automated tests run on every deployment before changes reach production.
Audit trails: Test results are logged automatically, creating documentation that supports compliance and data governance requirements.

Python Libraries for ETL Testing and Data Validation

Python's ecosystem covers every layer of ETL testing. These are the core libraries used in production ETL testing environments:

pandas — the foundation for data manipulation and comparison in ETL testing. Used for extracting data from source and target systems, comparing datasets, and identifying row-level discrepancies.
SQLAlchemy — provides database-agnostic connection management, allowing test scripts to connect to PostgreSQL, MySQL, Oracle, SQL Server, and other databases without rewriting connection logic for each.
pytest — Python's standard testing framework. ETL test cases are written as pytest functions, enabling structured test runs, detailed failure reporting, and seamless CI/CD integration.
Great Expectations — the most widely adopted Python library specifically for data validation. Allows engineers to define named expectations for data quality — row counts, column types, value ranges, uniqueness constraints — and run them automatically against pipeline outputs.
pyodbc — ODBC connector for connecting to databases including SQL Server and Oracle from Python test scripts.
dbt (data build tool) — increasingly used alongside Python for transformation testing, with built-in schema tests for uniqueness, referential integrity, and null constraints embedded directly in transformation workflows.

Step-by-Step Guide to Automating ETL Testing with Python

Step 1 — Install Required Libraries


                                    pip install pandas sqlalchemy pyodbc pytest great_expectations

Step 2 — Define Your ETL Test Cases

Before writing code, identify what needs to be validated at each pipeline stage:

Data completeness: does the target contain the same number of records as the source after loading?
Data accuracy: are transformed values correct? Does a column multiplied by a business rule produce the expected result?
Schema validation: do column names, data types, and nullability constraints in the target match the expected schema?
Referential integrity: do foreign key relationships hold after data is loaded?
Performance: does the ETL job complete within the acceptable time window?

Defining these test cases explicitly before writing scripts ensures coverage is systematic rather than ad hoc.

Step 3 — Connect to Source and Target Databases


                                    import pandas as pd

                                    from sqlalchemy import create_engine


                                    # Connect to source and target databases

                                    source_engine = create_engine('postgresql://user:password@localhost/source_db')

                                    target_engine = create_engine('postgresql://user:password@localhost/target_db')


                                    # Extract data from source and target

                                    source_data = pd.read_sql("SELECT * FROM source_table", source_engine)

                                    target_data = pd.read_sql("SELECT * FROM target_table", target_engine)

Replace the connection strings with your actual database credentials. For production environments, store credentials in environment variables or a secrets manager — never hardcode them in scripts.

Step 4 — Validate Data Completeness and Accuracy


                                    # Row count validation

                                    assert len(source_data) == len(target_data), \

                                       f"Row count mismatch: source={len(source_data)}, target={len(target_data)}"


                                    # Column name validation

                                    assert list(source_data.columns) == list(target_data.columns), \

                                       "Column names do not match between source and target"


                                    # Transformation accuracy validation

                                    source_data['expected_value'] = source_data['existing_column'].apply(lambda x: x * 2)

                                    assert source_data['expected_value'].equals(target_data['transformed_column']), \

                                       "Transformation output does not match expected values"

Step 5 — Structure Tests with pytest


                                    import pytest

                                    import pandas as pd

                                    from sqlalchemy import create_engine


                                    source_engine = create_engine('postgresql://user:password@localhost/source_db')

                                    target_engine = create_engine('postgresql://user:password@localhost/target_db')


                                    source_data = pd.read_sql("SELECT * FROM source_table", source_engine)

                                    target_data = pd.read_sql("SELECT * FROM target_table", target_engine)


                                    def test_row_count():

                                       assert len(source_data) == len(target_data), \

                                          f"Row count mismatch: {len(source_data)} vs {len(target_data)}"


                                    def test_column_names():

                                       assert list(source_data.columns) == list(target_data.columns), \

                                          "Column schema mismatch between source and target"


                                    def test_no_null_values_in_key_column():

                                       assert target_data['id'].isnull().sum() == 0, \

                                          "Null values found in key column after load"


                                    def test_transformation_accuracy():

                                       expected = source_data['amount'] * 1.1

                                       assert expected.round(2).equals(target_data['adjusted_amount'].round(2)), \

                                          "Transformation calculation does not match expected output"

Run the full test suite:


                                    pytest test_etl.py -v

The -v flag produces verbose output showing each test name and pass/fail status useful for CI/CD logs and audit documentation.

Step 6 — Add Data Validation with Great Expectations

Great Expectations adds a structured expectation layer on top of raw assertions, allowing teams to define, version, and share data quality rules across pipelines.


                                    import great_expectations as ge


                                    # Load target data as a Great Expectations DataFrame

                                    ge_df = ge.from_pandas(target_data)


                                    # Define expectations

                                    ge_df.expect_column_values_to_not_be_null("id")

                                    ge_df.expect_column_values_to_be_unique("id")

                                    ge_df.expect_column_values_to_be_between("amount", min_value=0, max_value=1000000)

                                    ge_df.expect_table_row_count_to_equal(len(source_data))


                                    # Validate and review results

                                    results = ge_df.validate()

                                    print(results)

Great Expectations generates human-readable validation reports that can be shared with data owners and compliance teams, not just data engineers.

Step 7 — Integrate with CI/CD Pipelines

ETL tests that only run manually provide limited protection. Integrating pytest with CI/CD tools ensures tests run automatically on every pipeline deployment before changes reach production.

For GitHub Actions:


                                    name: ETL Testing Pipeline


                                    on: [push, pull_request]


                                    jobs:

                                      etl-tests:

                                        runs-on: ubuntu-latest

                                        steps:

                                          - uses: actions/checkout@v3

                                          - name: Set up Python

                                            uses: actions/setup-python@v4

                                            with:

                                              python-version: '3.11'

                                          - name: Install dependencies

                                            run: pip install pandas sqlalchemy pyodbc pytest great_expectations

                                          - name: Run ETL tests

                                            run: pytest test_etl.py -v

The same approach works with Jenkins, GitLab CI, and Azure DevOps — replace the YAML structure with the equivalent pipeline configuration for your environment.

ETL Testing Best Practices for 2026

Write reusable validation functions. Avoid rewriting the same row count, schema, and null checks for every pipeline. Create a shared validation library that all pipeline test suites import, reducing maintenance overhead as pipelines grow.
Separate test environments from production. ETL tests should run against staging or test databases that mirror production structure but do not contain live data. Running destructive tests against production is a data integrity risk.
Log all test results. Use Python's logging module or a structured logging tool to automatically capture test outcomes, timestamps, and failure details. This creates the audit trail that data governance and compliance frameworks require.
Manage credentials securely. Never hardcode database credentials in test scripts. Use environment variables, AWS Secrets Manager, Azure Key Vault, or HashiCorp Vault depending on your infrastructure.
Test incrementally as well as fully. Full pipeline tests validate the complete dataset. Incremental tests validate only the records added or changed in the most recent run, which is essential for large pipelines where full scans on every run would be prohibitively slow.
Version control your test scripts. ETL test scripts are production code. Store them in Git alongside pipeline code, apply code review processes, and maintain a changelog for test logic changes.

How DataTerrain Uses Automated Validation in ETL Migration

ETL testing is not only relevant for ongoing pipeline operations, but it is also critical during ETL migration. When pipelines move from one platform to another, output validation ensures that migrated pipelines produce identical results to their legacy source before production cutover.

DataTerrain's automated any-to-any ETL migration approach applies systematic output validation at every migration stage, comparing migrated pipeline results against source outputs record by record before any go-live. This is the same principle as automated ETL testing applied specifically to migration accuracy. For organizations migrating from Oracle Data Integrator, Oracle PL/SQL, Informatica PowerCenter, or other legacy platforms to modern tools including SnapLogic, Alteryx, Microsoft Fabric, and AWS Glue, this validation layer is what ensures data accuracy from day one rather than after a correction cycle.

Our ETL migration solutions cover the full pipeline transition including automated output validation, business logic preservation, and go-live verification. For the reporting layer above ETL pipelines, our reports conversion service handles migration of legacy BI reports to modern platforms with the same validation discipline applied to every converted report.

Validating ETL pipelines — or migrating them to a modern platform?

DataTerrain applies automated output validation across every ETL migration engagement, comparing migrated pipeline results against legacy source outputs before any production cutover. 400+ clients. 17 years of ETL migration experience. Any source platform to any modern target.

Talk to a DataTerrain ETL Specialist

Frequently Asked Questions

What is ETL testing with Python?

ETL testing with Python is the practice of using Python scripts and libraries, primarily pandas, pytest, SQLAlchemy, and Great Expectations, to automatically validate that data pipelines extract, transform, and load data correctly. Tests check row counts, column schemas, transformation accuracy, null values, referential integrity, and performance against expected benchmarks.

What is the best Python library for ETL data validation?

Great Expectations is the most widely used Python library specifically for ETL data validation. It allows teams to define named expectations for data quality, run them automatically against pipeline outputs, and generate shareable validation reports. For general-purpose testing structure, pytest is the standard framework used alongside Great Expectations.

How does automated ETL testing integrate with CI/CD?

Automated ETL tests written with pytest integrate directly with CI/CD tools including GitHub Actions, Jenkins, GitLab CI, and Azure DevOps. Tests are configured to run automatically on every pipeline deployment, catching data quality issues before they reach production rather than after.

What are the main types of ETL test cases?

The five main ETL test case types are data completeness (row count validation), data accuracy (transformation output verification), schema validation (column names and data types), referential integrity (foreign key relationship checks), and performance testing (job completion time within acceptable thresholds).

How is ETL testing different from ETL migration validation?

ETL testing validates that an existing pipeline produces correct outputs on an ongoing basis. ETL migration validation specifically validates that a migrated pipeline produces identical outputs to its legacy source after migration before the new pipeline goes live. Both use similar Python-based validation techniques but serve different purposes in the data engineering lifecycle.