data_check is a simple data validation tool. In its most basic form it will execute SQL queries and compare the results against CSV or Excel files. But there are more advanced features:

• CSV checks: compare SQL queries against CSV files
• Excel support: Use Excel (xlsx) instead of CSV
• multiple environments (databases) in the configuration file
• populate tables from CSV or Excel files
• execute any SQL files on a database
• more complex pipelines
• run any script/command (via pipelines)
• simplified checks for empty datasets and full table comparison
• lookups to reuse the same data in multiple queries
• test data generation

data_check is tested with these databases:

• PostgreSQL
• MySQL
• SQLite
• Oracle
• Microsoft SQL Server

Partially supported:

• DuckDB
• Databricks

Other databases supported by SQLAlchemy might also work.

You need Python 3.9 or above to run data_check. The easiest way to install data_check is via pipx:

pipx install data-check

The data_check Git repository is also a sample data_check project. Clone the repository, switch to the folder and run data_check:

git clone git@github.com:andrjas/data_check.git cd data_check/example data_check 

This will run the tests in the checks folder using the default connection as set in data_check.yml.

See the documentation how to install data_check in different environments with additional database drivers and other usages of data_check.

data_check has a simple layout for projects: a single configuration file and a folder with the test files. You can also organize the test files in subfolders.

data_check.yml # The configuration file checks/ # Default folder for data tests some_test.sql # SQL file with the query to run against the database some_test.csv # CSV file with the expected result subfolder/ # Tests can be nested in subfolders 

This is the default mode when running data_check. data_check expects a SQL file and a CSV file. The SQL file will be executed against the database and the result is compared with the CSV file. If they match, the test is passed, otherwise it fails.

If data_check finds a file named data_check_pipeline.yml in a folder, it will treat this folder as a pipeline check. Instead of running CSV checks it will execute the steps in the YAML file.

Example project with a pipeline:

data_check.yml checks/ some_test.sql # this test will run in parallel to the pipeline test some_test.csv sample_pipeline/ data_check_pipeline.yml # configuration for the pipeline data/ my_schema.some_table.csv # data for a table data2/ some_data.csv # other data some_checks/ # folder with CSV checks check1.sql check1.csl ... run_this.sql # a SQL file that will be executed cleanup.sql other_pipeline/ # you can have multiple pipelines that will run in parallel data_check_pipeline.yml ... 

The file sample_pipeline/data_check_pipeline.yml can look like this:

steps: # this will truncate the table my_schema.some_table and load it with the data from data/my_schema.some_table.csv - load: data # this will execute the SQL statement in run_this.sql - sql: run_this.sql # this will append the data from data2/some_data.csv to my_schema.other_table - load: file: data2/some_data.csv table: my_schema.other_table mode: append # this will run a python script and pass the connection name - cmd: "python3 /path/to/my_pipeline.py --connection {{CONNECTION}}" # this will run the CSV checks in the some_checks folder - check: some_checks

Pipeline checks and simple CSV checks can coexist in a project.

See the documentation how to setup data_check, how to create a new project and more options.

MIT