How to compare data between PostgreSQL and BigQuery using DVT?

Set up connections for PostgreSQL and BigQuery using the connections documentation, then use commands like 'validate column' with --source-conn and --target-conn flags to specify tables or custom queries. Results can be output to stdout or stored in BigQuery for analysis.

DVT vs Great Expectations for data validation?

DVT excels at cross-database comparisons during migrations with its extensive connector support and scalability for large datasets, while Great Expectations is better for data quality testing within a single environment with richer assertion libraries and profiling. DVT is more migration-focused.

How to handle out of memory errors when validating large tables in DVT?

Use the 'generate-table-partitions' command to split the validation into smaller chunks based on primary keys, then run validations in parallel using Kubernetes or Cloud Run Jobs. This is documented in the scaling section to avoid MemoryError.

Can DVT validate real-time data streams?

No, DVT is designed for batch validation of static datasets, not real-time streams. It requires full table scans or queries, so it's not suitable for streaming pipelines without intermediate batch storage.

How to set up DVT for on-prem databases without GCP access?

Create connections for on-prem databases like Oracle or MySQL, but note that features like BigQuery result storage or GCS config files won't work directly. You may need to use local storage alternatives and configure network endpoints as per the on-prem documentation.

What are the limitations of row hash validation in DVT?

Row hash validation is not supported for FileSystem connections, and SHA256 isn't available on Teradata without a custom UDF. It also requires primary keys and can be memory-intensive, necessitating partitioning for large tables.

dvt — Database Comparison CLI Tool

What is dvt?

The Data Validation Tool (DVT) is an open-source Python command-line tool that compares data between different database systems and data warehouses. It solves the problem of ensuring data accuracy and consistency during migration projects by automating validation checks between source and target environments.

Target Audience

Data engineers, database administrators, and DevOps professionals involved in data migration, replication, or ETL pipeline validation across heterogeneous data systems.

Value Proposition

Developers choose DVT for its extensive connector support, ability to handle large-scale validations through partitioning, and its automation-friendly CLI and YAML configuration, which replaces manual SQL comparison scripts.

Utility to compare data between homogeneous or heterogeneous environments to ensure source and target tables match

Use Cases

Best For

Validating data after migrating from on-premises databases to cloud data warehouses
Comparing ETL pipeline outputs between different processing engines
Ensuring schema and data consistency during database version upgrades
Automating data quality checks in continuous integration pipelines
Verifying data replication accuracy between heterogeneous systems
Performing large-scale row-level comparisons across partitioned datasets

Not Ideal For

Real-time data validation or streaming pipelines, as DVT is designed for batch comparisons and lacks built-in streaming support.
Projects with heavily nested or array-based data structures, since DVT explicitly does not support complex columns for column or row validations.
Teams operating entirely outside Google Cloud Platform without GCP services, due to its optimization for BigQuery, GCS, and Secret Manager integration.
Simple, single-database checks where lightweight SQL scripts or GUI tools would suffice, given DVT's CLI complexity and setup overhead.

Pros & Cons

Pros

Broad Connector Support

Supports 15+ data sources including BigQuery, PostgreSQL, Oracle, and Snowflake, enabling cross-platform validations without custom SQL for each pair.

Scalable Partitioning

Automatically generates partitions for large datasets and supports distributed runs via Kubernetes or Cloud Run Jobs, handling billions of rows efficiently as documented in the scaling section.

Flexible Validation Types

Offers column, row, schema, and custom query validations with detailed options like group by and calculated fields, replacing manual comparison scripts.

Configuration-Driven Workflow

Uses YAML/JSON config files for defining validations, making it easy to automate, version control, and repeat checks in CI/CD pipelines.

Cons

Complex Data Type Limitations

Does not support nested or complex columns for column or row validations, which can be a blocker for modern data warehouses with JSON or array fields.

GCP-Centric Architecture

Optimized for GCP services like BigQuery and Secret Manager; on-prem setups require extra configuration for endpoints and lack seamless integration with non-GCP clouds.

Memory-Intensive Row Validations

Row-level comparisons can cause MemoryError on large tables, forcing users to manually partition data with generate-table-partitions, adding operational complexity.

Frequently Asked Questions

What is dvt?

Target Audience

Data engineers, database administrators, and DevOps professionals involved in data migration, replication, or ETL pipeline validation across heterogeneous data systems.

Value Proposition

Use Cases

Best For

Validating data after migrating from on-premises databases to cloud data warehouses
Comparing ETL pipeline outputs between different processing engines
Ensuring schema and data consistency during database version upgrades
Automating data quality checks in continuous integration pipelines
Verifying data replication accuracy between heterogeneous systems
Performing large-scale row-level comparisons across partitioned datasets

Not Ideal For

Real-time data validation or streaming pipelines, as DVT is designed for batch comparisons and lacks built-in streaming support.
Projects with heavily nested or array-based data structures, since DVT explicitly does not support complex columns for column or row validations.
Teams operating entirely outside Google Cloud Platform without GCP services, due to its optimization for BigQuery, GCS, and Secret Manager integration.
Simple, single-database checks where lightweight SQL scripts or GUI tools would suffice, given DVT's CLI complexity and setup overhead.

Pros & Cons

Pros

Broad Connector Support

Supports 15+ data sources including BigQuery, PostgreSQL, Oracle, and Snowflake, enabling cross-platform validations without custom SQL for each pair.

Scalable Partitioning

Automatically generates partitions for large datasets and supports distributed runs via Kubernetes or Cloud Run Jobs, handling billions of rows efficiently as documented in the scaling section.

Flexible Validation Types

Offers column, row, schema, and custom query validations with detailed options like group by and calculated fields, replacing manual comparison scripts.

Configuration-Driven Workflow

Uses YAML/JSON config files for defining validations, making it easy to automate, version control, and repeat checks in CI/CD pipelines.

Cons

Complex Data Type Limitations

Does not support nested or complex columns for column or row validations, which can be a blocker for modern data warehouses with JSON or array fields.

GCP-Centric Architecture

Optimized for GCP services like BigQuery and Secret Manager; on-prem setups require extra configuration for endpoints and lack seamless integration with non-GCP clouds.

Memory-Intensive Row Validations

Row-level comparisons can cause MemoryError on large tables, forcing users to manually partition data with generate-table-partitions, adding operational complexity.

Frequently Asked Questions

dvt

What is dvt?

Overview

Use Cases

Best For

Not Ideal For

Pros & Cons

Pros

Cons

Frequently Asked Questions

Related Projects

Found a gem we're missing?

dvt

What is dvt?

Overview

Use Cases

Best For

Not Ideal For

Pros & Cons

Pros

Cons

Frequently Asked Questions

Related Projects

Found a gem we're missing?