google-pso-data-validator 8.5.0
A package to enable easy data validation
Requires Python: >=3.9

# Data Validation Tool

The Data Validation Tool is an open sourced Python CLI tool based on the
[Ibis framework](https://ibis-project.org/docs/)
that compares heterogeneous data source tables with multi-leveled validation
functions.

Data validation is a critical step in a data warehouse, database, or data lake
migration project where data from both the source and the target tables are
compared to ensure they are matched and correct after each migration step
(e.g. data and schema migration, SQL script translation, ETL migration, etc.).
The Data Validation Tool (DVT) provides an automated and repeatable solution to
perform this task.

DVT supports the following validations:
* Column validation (count, sum, avg, min, max, stddev, group by)
* Row validation (Not supported for FileSystem connections)
* Schema validation
* Custom Query validation
* Ad hoc SQL exploration

DVT supports the following connection types:

*   [AlloyDB](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#alloydb)
*   [BigQuery](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#google-bigquery)
*   [DB2](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#db2)
*   [FileSystem](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#filesystem)
*   [Hive](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#hive)
*   [Impala](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#impala)
*   [MSSQL](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#mssql-server)
*   [MySQL](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#mysql)
*   [Oracle](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#oracle)
*   [Postgres](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#postgres)
*   [Redshift](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#redshift)
*   [Spanner](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#google-spanner)
*   [Snowflake](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#snowflake)
*   [Sybase ASE](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#sybase)
*   [Teradata](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#teradata)

The [Connections](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md) page provides details about how to create
and list connections for the validation tool.

### Disclaimer
This is not an officially supported Google product. Please be aware that bugs may lurk, and that we reserve the right to make small backwards-incompatible changes. Feel free to open bugs or feature requests, or contribute directly
(see [CONTRIBUTING.md](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/CONTRIBUTING.md) for details).

## Installation

The [Installation](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/installation.md) page describes the prerequisites and
setup steps needed to install and use the Data Validation Tool.

## Usage

Before using this tool, you will need to create connections to the source and
target tables. Once the connections are created, you can run validations on
those tables. Validation results can be printed to stdout (default) or outputted
to BigQuery (recommended). DVT also allows you to save and edit validation
configurations in a YAML or JSON file. This is useful for running common validations or
updating the configuration.

### Managing Connections

Before running validations, DVT requires setting up a source and target connection.
These connections can be stored locally or in a GCS directory. To create connections,
please review the [Connections](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md) page.

### Running Validations

The CLI is the main interface to use this tool and it has several different
commands which can be used to create and run validations. DVT is designed to run in
an environment connected to GCP services, specifically, BigQuery, GCS and Secret manager.
If DVT is being run on-premises or in an environment with restricted access to GCP services, see
[running DVT  at on-prem](#running-dvt-at-on-prem). Below are the command syntax and options for running validations.

Alternatives to running DVT in the CLI include deploying DVT to Cloud Run, Cloud Functions, or Airflow
([Examples Here](https://github.com/GoogleCloudPlatform/professional-services-data-validator/tree/develop/samples)). See the [Validation Logic](https://github.com/GoogleCloudPlatform/professional-services-data-validator#validation-logic) section
to learn more about how DVT uses the CLI to generate SQL queries.

Note that we do not support nested or complex columns for column or row validations.

#### Column Validations

Below is the command syntax for column validations. To run a grouped column
validation, simply specify the `--grouped-columns` flag.

You can specify a list of string columns for aggregations in order to calculate
an aggregation over the `length(string_col)`. Similarly, you can specify timestamp/date
columns for aggregation over the `unix_seconds(timestamp_col)`. Running an aggregation
over all columns ('*') will only run over numeric columns, unless the
`--wildcard-include-string-len` or `--wildcard-include-timestamp` flags are present.

```
data-validation
  [--verbose or -v ]
                        Verbose logging
  [--log-level or -ll]
                        Log Level to be assigned. Supported levels are (DEBUG,INFO,WARNING,ERROR,CRITICAL). Defaults to INFO.
  validate column
  --source-conn or -sc SOURCE_CONN
                        Source connection details
                        See: *Data Source Configurations* section for each data source
  --target-conn or -tc TARGET_CONN
                        Target connection details
                        See: *Connections* section for each data source
  --tables-list or -tbls SOURCE_SCHEMA.SOURCE_TABLE=TARGET_SCHEMA.TARGET_TABLE
                        Comma separated list of tables in the form schema.table=target_schema.target_table. Or shorthand schema.* for all tables.
                        Target schema name and table name are optional.
                        i.e 'bigquery-public-data.new_york_citibike.citibike_trips'
  [--grouped-columns or -gc GROUPED_COLUMNS]
                        Comma separated list of columns for Group By i.e col_a,col_b
  [--count COLUMNS]     Comma separated list of columns for count or * for all columns
  [--sum COLUMNS]       Comma separated list of columns for sum or * for all numeric
  [--min COLUMNS]       Comma separated list of columns for min or * for all numeric
  [--max COLUMNS]       Comma separated list of columns for max or * for all numeric
  [--avg COLUMNS]       Comma separated list of columns for avg or * for all numeric
  [--std COLUMNS]       Comma separated list of columns for stddev_samp or * for all numeric.
                        Please note that not all supported SQL engines give results from STDDV_SAMP (or engine specific equivalent) that
                        are comparable across all other supported SQL engines. This option may produce unreliable results.
  [--exclude-columns or -ec]
                        Flag to indicate the list of columns provided should be excluded and not included.
  [--result-handler or -rh CONNECTION_NAME.SCHEMA.TABLE or BQ_PROJECT_ID.DATASET.TABLE]
                        Specify a BigQuery or PostgreSQL connection name as destination for validation results.
                        Also supports legacy BigQuery format BQ_PROJECT_ID.DATASET.TABLE.
                        See: *Validation Reports* section
  [--bq-result-handler or -bqrh PROJECT_ID.DATASET.TABLE or CONNECTION_NAME.DATASET.TABLE]
                        This option has been deprecated and will be removed in a future release.
  [--service-account or -sa PATH_TO_SA_KEY]
                        Service account to use for BigQuery result handler output.
  [--wildcard-include-string-len or -wis]
                        If flag is present, include string columns in aggregation as len(string_col)
  [--wildcard-include-timestamp or -wit]
                        If flag is present, include timestamp/date columns in aggregation as unix_seconds(ts_col)
  [--cast-to-bigint or -ctb]
                        If flag is present, cast all int32 columns to int64 before aggregation
  [--filters SOURCE_FILTER:TARGET_FILTER]
                        Colon separated string values of source and target filters.
                        If target filter is not provided, the source filter will run on source and target tables.
                        See: *Filters* section
  [--config-file or -c CONFIG_FILE]
                        YAML Config File Path to be used for storing validations and other features. Supports GCS and local paths.
                        See: *Running DVT with YAML Configuration Files* section
  [--config-file-json or -cj CONFIG_FILE_JSON]
                        JSON Config File Path to be used for storing validations only for application purposes.
  [--threshold or -th THRESHOLD]
                        Float value. Maximum pct_difference allowed for validation to be considered a success. Defaults to 0.0
  [--labels or -l KEY1=VALUE1,KEY2=VALUE2]
                        Comma-separated key value pair labels for the run.
  [--format or -fmt FORMAT]
                        Format for stdout output. Supported formats are (text, csv, json, table). Defaults to table.
  [--filter-status or -fs STATUSES_LIST]
                        Comma separated list of statuses to filter the validation results. Supported statuses are (success, fail). If no list is provided, all statuses are returned.

```

The default aggregation type is a 'COUNT *', which will run in addition to the validations you specify. To remove this default,
use [YAML configs](https://github.com/GoogleCloudPlatform/professional-services-data-validator/tree/develop#running-dvt-with-yaml-configuration-files).

The [Examples](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/examples.md) page provides many examples of how a tool can be used to run powerful validations without writing any queries.

#### Row Validations

(Note: Row hash validation not supported for FileSystem connections.
In addition, please note that SHA256 is not a supported function on Teradata systems.
If you wish to perform this comparison on Teradata you will need to
[deploy a UDF to perform the conversion](https://github.com/akuroda/teradata-udf-sha2/blob/master/src/sha256.c).)

Below is the command syntax for row validations. In order to run row level validations we require
unique columns to join row sets, which are either inferred from the source/target table or provided
via the `--primary-keys` flag, and either the `--hash`, `--concat` or `--comparison-fields` flags.
See *Primary Keys* section.

The `--comparison-fields` flag specifies the values (e.g. columns) whose raw values will be compared
based on the primary key join. The `--hash` flag will run a checksum across specified columns in
the table. This will include casting to string, sanitizing the data (ifnull, rtrim, upper), concatenating,
and finally hashing the row.

Under the hood, row validation uses
[Calculated Fields](https://github.com/GoogleCloudPlatform/professional-services-data-validator#calculated-fields) to
apply functions such as IFNULL() or RTRIM(). These can be edited in the YAML or JSON config file to customize your row validation.

```
data-validation
  [--verbose or -v ]
                        Verbose logging
  [--log-level or -ll]
                        Log Level to be assigned. Supported levels are (DEBUG,INFO,WARNING,ERROR,CRITICAL). Defaults to INFO.
  validate row
  --source-conn or -sc SOURCE_CONN
                        Source connection details
                        See: *Data Source Configurations* section for each data source
  --target-conn or -tc TARGET_CONN
                        Target connection details
                        See: *Connections* section for each data source
  --tables-list or -tbls SOURCE_SCHEMA.SOURCE_TABLE=TARGET_SCHEMA.TARGET_TABLE
                        Comma separated list of tables in the form schema.table=target_schema.target_table
                        Target schema name and table name are optional.
                        i.e 'bigquery-public-data.new_york_citibike.citibike_trips'
  --comparison-fields or -comp-fields FIELDS
                        Comma separated list of columns to compare. Can either be a physical column or an alias
                        See: *Calculated Fields* section for details
  --hash COLUMNS        Comma separated list of columns to hash or * for all columns
  --concat COLUMNS      Comma separated list of columns to concatenate or * for all columns (use if a common hash function is not available between databases)
  --max-concat-columns INT, -mcc INT
                        Maximum number of columns used in one --hash or --concat validation. When there are more columns in the validation, the validation will be split into multiple validations. There are engine specific defaults, so most users do not need to use this option unless they encounter errors.
  [--primary-keys PRIMARY_KEYS, -pk PRIMARY_KEYS]
                        Comma separated list of primary key columns, when not specified the value will be inferred
                        from the source or target table if available.  See *Primary Keys* section
  [--exclude-columns or -ec]
                        Flag to indicate the list of columns provided should be excluded from hash or concat instead of included.
  [--result-handler or -rh CONNECTION_NAME.SCHEMA.TABLE or BQ_PROJECT_ID.DATASET.TABLE]
                        Specify a BigQuery or PostgreSQL connection name as destination for validation results.
                        Also supports legacy BigQuery format BQ_PROJECT_ID.DATASET.TABLE.
                        See: *Validation Reports* section
  [--bq-result-handler or -bqrh PROJECT_ID.DATASET.TABLE or CONNECTION_NAME.DATASET.TABLE]
                        This option has been deprecated and will be removed in a future release.
  [--service-account or -sa PATH_TO_SA_KEY]
                        Service account to use for BigQuery result handler output.
  [--filters SOURCE_FILTER:TARGET_FILTER]
                        Colon separated string values of source and target filters.
                        If target filter is not provided, the source filter will run on source and target tables.
                        See: *Filters* section
  [--config-file or -c CONFIG_FILE]
                        YAML Config File Path to be used for storing validations and other features. Supports GCS and local paths.
                        See: *Running DVT with YAML Configuration Files* section
  [--config-file-json or -cj CONFIG_FILE_JSON]
                        JSON Config File Path to be used for storing validations only for application purposes.
  [--labels or -l KEY1=VALUE1,KEY2=VALUE2]
                        Comma-separated key value pair labels for the run.
  [--format or -fmt FORMAT]
                        Format for stdout output. Supported formats are (text, csv, json, table). Defaults to table.
  [--use-random-row or -rr]
                        Finds a set of random rows of the first primary key supplied.
  [--random-row-batch-size or -rbs]
                        Row batch size used for random row filters (default 10,000).
  [--filter-status or -fs STATUSES_LIST]
                        Comma separated list of statuses to filter the validation results. Supported statuses are (success, fail). If no list is provided, all statuses are returned.
  [--case-insensitive-match, -cim]
                        Performs a case insensitive match by adding an UPPER() before comparison.
```
#### Generate Partitions for Large Row Validations

When performing row validations, Data Validation Tool brings each row into memory and can run into MemoryError. Below is the command syntax for generating partitions in order to perform row validations on large dataset (table or custom-query) to alleviate MemoryError. Each partition contains a range of primary key(s) and the ranges of keys across partitions are distinct. The partitions have nearly equal number of rows. See *Primary Keys* section

The command generates and stores multiple YAML validations each representing a chunk of the large dataset using filters (`WHERE primary_key(s) >= X AND primary_key(s) < Y`) in YAML files. The parameter parts-per-file, specifies the number of validations in one YAML file. Each yaml file will have parts-per-file validations in it - except the last one which will contain the remaining partitions (i.e. parts-per-file may not divide partition-num evenly). You can then run the validations in the directory serially (or in parallel in multiple containers, VMs) with the `data-validation configs run --config-dir PATH` command as described [here](https://github.com/GoogleCloudPlatform/professional-services-data-validator#yaml-configuration-files).

The command takes the same parameters as required for `Row Validation` *plus* a few parameters to support partitioning. Single and multiple primary keys are supported and keys can be of any indexable type, except for date and timestamp type. You can specify tables that are being validated or the source and target custom query. A parameter used in earlier versions, ```partition-key``` is no longer supported.

```
data-validation
  [--verbose or -v ]
                        Verbose logging
  [--log-level or -ll]
                        Log Level to be assigned. Supported levels are (DEBUG,INFO,WARNING,ERROR,CRITICAL). Defaults to INFO.
  generate-table-partitions
  --source-conn or -sc SOURCE_CONN
                        Source connection details
                        See: *Data Source Configurations* section for each data source
  --target-conn or -tc TARGET_CONN
                        Target connection details
                        See: *Connections* section for each data source
  --tables-list or -tbls SOURCE_SCHEMA.SOURCE_TABLE=TARGET_SCHEMA.TARGET_TABLE
                        Comma separated list of tables in the form schema.table=target_schema.target_table
                        Target schema name and table name are optional.
                        i.e 'bigquery-public-data.new_york_citibike.citibike_trips'
                        Either --tables-list or --source-query (or file) and --target-query (or file) must be provided
  --source-query SOURCE_QUERY, -sq SOURCE_QUERY
                        Source sql query
                        Either --tables-list or --source-query (or file) and --target-query (or file) must be provided
  --source-query-file  SOURCE_QUERY_FILE, -sqf SOURCE_QUERY_FILE
                        File containing the source sql command. Supports GCS and local paths.
  --target-query TARGET_QUERY, -tq TARGET_QUERY
                        Target sql query
                        Either --tables-list or --source-query (or file) and --target-query (or file) must be provided
  --target-query-file TARGET_QUERY_FILE, -tqf TARGET_QUERY_FILE
                        File containing the target sql command. Supports GCS and local paths.
  --comparison-fields or -comp-fields FIELDS
                        Comma separated list of columns to compare. Can either be a physical column or an alias
                        See: *Calculated Fields* section for details
  --hash COLUMNS        Comma separated list of columns to hash or * for all columns
  --concat COLUMNS      Comma separated list of columns to concatenate or * for all columns (use if a common hash function is not available between databases)
  --config-dir CONFIG_DIR, -cdir CONFIG_DIR
                        Directory Path to store YAML Config Files
                        GCS: Provide a full gs:// path of the target directory. Eg: `gs://<BUCKET>/partitions_dir`
                        Local: Provide a relative path of the target directory. Eg: `partitions_dir`
                        For custom queries, the yaml files are stored in CONFIG_DIR and, for tables the yamls are stored in a directory named <source_schema>.<table> within CONFIG_DIR.
  --partition-num INT, -pn INT
                        Number of partitions into which the table should be split, e.g. 1000 or 10000
                        In case this value exceeds the row count of the source/target table, it will be decreased to max(source_row_count, target_row_count)
  [--primary-keys PRIMARY_KEYS, -pk PRIMARY_KEYS]
                        Comma separated list of primary key columns, when not specified the value will be inferred
                        from the source or target table if available.  See *Primary Keys* section
  [--result-handler or -rh CONNECTION_NAME.SCHEMA.TABLE or BQ_PROJECT_ID.DATASET.TABLE]
                        Specify a BigQuery or PostgreSQL connection name as destination for validation results.
                        Also supports legacy BigQuery format BQ_PROJECT_ID.DATASET.TABLE.
                        See: *Validation Reports* section
  [--bq-result-handler or -bqrh PROJECT_ID.DATASET.TABLE or CONNECTION_NAME.DATASET.TABLE]
                        This option has been deprecated and will be removed in a future release.
  [--service-account or -sa PATH_TO_SA_KEY]
                        Service account to use for BigQuery result handler output.
  [--parts-per-file INT], [-ppf INT]
                        Number of partitions in a yaml file, default value 1.
  [--filters SOURCE_FILTER:TARGET_FILTER]
                        Colon separated string values of source and target filters.
                        If target filter is not provided, the source filter will run on source and target tables.
                        See: *Filters* section
  [--labels or -l KEY1=VALUE1,KEY2=VALUE2]
                        Comma-separated key value pair labels for the run.
  [--format or -fmt FORMAT]
                        Format for stdout output. Supported formats are (text, csv, json, table). Defaults to table.
  [--filter-status or -fs STATUSES_LIST]
                        Comma separated list of statuses to filter the validation results. Supported statuses are (success, fail). If no list is provided, all statuses are returned.
  [--case-insensitive-match, -cim]
                        Performs a case insensitive match by adding an UPPER() before comparison.
```
#### Schema Validations

Below is the syntax for schema validations. These can be used to compare case insensitive column names and
types between source and target.

Note: An exclamation point before a data type (`!string`) signifies the column is non-nullable or required.

```
data-validation
  [--verbose or -v ]
                        Verbose logging
  [--log-level or -ll]
                        Log Level to be assigned. Supported levels are (DEBUG,INFO,WARNING,ERROR,CRITICAL). Defaults to INFO.
  validate schema
  --source-conn or -sc SOURCE_CONN
                        Source connection details
                        See: *Data Source Configurations* section for each data source
  --target-conn or -tc TARGET_CONN
                        Target connection details
                        See: *Connections* section for each data source
  --tables-list or -tbls SOURCE_SCHEMA.SOURCE_TABLE=TARGET_SCHEMA.TARGET_TABLE
                        Comma separated list of tables in the form schema.table=target_schema.target_table. Or shorthand schema.* for all tables.
                        Target schema name and table name are optional.
                        e.g.: 'bigquery-public-data.new_york_citibike.citibike_trips'
  [--result-handler or -rh CONNECTION_NAME.SCHEMA.TABLE or BQ_PROJECT_ID.DATASET.TABLE]
                        Specify a BigQuery or PostgreSQL connection name as destination for validation results.
                        Also supports legacy BigQuery format BQ_PROJECT_ID.DATASET.TABLE.
                        See: *Validation Reports* section
  [--bq-result-handler or -bqrh PROJECT_ID.DATASET.TABLE or CONNECTION_NAME.DATASET.TABLE]
                        This option has been deprecated and will be removed in a future release.
  [--service-account or -sa PATH_TO_SA_KEY]
                        Service account to use for BigQuery result handler output.
  [--config-file or -c CONFIG_FILE]
                        YAML Config File Path to be used for storing validations and other features. Supports GCS and local paths.
                        See: *Running DVT with YAML Configuration Files* section
  [--config-file-json or -cj CONFIG_FILE_JSON]
                        JSON Config File Path to be used for storing validations only for application purposes.
  [--format or -fmt]    Format for stdout output. Supported formats are (text, csv, json, table).
                        Defaults  to table.
  [--filter-status or -fs STATUSES_LIST]
                        Comma separated list of statuses to filter the validation results. Supported statuses are (success, fail).
                        If no list is provided, all statuses are returned.
  [--exclusion-columns or -ec EXCLUSION_COLUMNS]
                        Comma separated list of columns to be excluded from the schema validation, e.g.: col_a,col_b.
  [--allow-list or -al ALLOW_LIST]
                        Comma separated list of data-type mappings of source and destination data sources which will be validated in case of missing data types in destination data source. e.g: "decimal(4,2):decimal(5,4),!string:string"
  [--allow-list-file ALLOW_LIST_FILE, -alf ALLOW_LIST_FILE]
                        YAML file containing default --allow-list mappings. Can be used in conjunction with --allow-list.
                        e.g.: samples/allow_list/oracle_to_bigquery.yaml or gs://dvt-allow-list-files/oracle_to_bigquery.yaml
                        See example files in samples/allow_list/.
```

#### Custom Query Column Validations

Below is the command syntax for custom query column validations.

```
data-validation
  [--verbose or -v ]
                        Verbose logging
  [--log-level or -ll]
                        Log Level to be assigned. Supported levels are (DEBUG,INFO,WARNING,ERROR,CRITICAL). Defaults to INFO.
  validate custom-query column
  --source-conn or -sc SOURCE_CONN
                        Source connection details
                        See: *Data Source Configurations* section for each data source
  --target-conn or -tc TARGET_CONN
                        Target connection details
                        See: *Connections* section for each data source
  --source-query SOURCE_QUERY, -sq SOURCE_QUERY
                        Source sql query
                        Either --source-query or --source-query-file must be provided
  --source-query-file  SOURCE_QUERY_FILE, -sqf SOURCE_QUERY_FILE
                        File containing the source sql command. Supports GCS and local paths.
  --target-query TARGET_QUERY, -tq TARGET_QUERY
                        Target sql query
                        Either --target-query or --target-query-file must be provided
  --target-query-file TARGET_QUERY_FILE, -tqf TARGET_QUERY_FILE
                        File containing the target sql command. Supports GCS and local paths.
  [--grouped-columns or -gc GROUPED_COLUMNS]
                        Comma separated list of columns for Group By i.e col_a,col_b
  [--count COLUMNS]     Comma separated list of columns for count or * for all columns
  [--sum COLUMNS]       Comma separated list of columns for sum or * for all numeric
  [--min COLUMNS]       Comma separated list of columns for min or * for all numeric
  [--max COLUMNS]       Comma separated list of columns for max or * for all numeric
  [--avg COLUMNS]       Comma separated list of columns for avg or * for all numeric
  [--std COLUMNS]       Comma separated list of columns for stddev_samp or * for all numeric.
                        Please note that not all supported SQL engines give results from STDDV_SAMP (or engine specific equivalent) that
                        are comparable across all other supported SQL engines. This option may produce unreliable results.
  [--exclude-columns or -ec]
                        Flag to indicate the list of columns provided should be excluded and not included.
  [--result-handler or -rh CONNECTION_NAME.SCHEMA.TABLE or BQ_PROJECT_ID.DATASET.TABLE]
                        Specify a BigQuery or PostgreSQL connection name as destination for validation results.
                        Also supports legacy BigQuery format BQ_PROJECT_ID.DATASET.TABLE.
                        See: *Validation Reports* section
  [--bq-result-handler or -bqrh PROJECT_ID.DATASET.TABLE or CONNECTION_NAME.DATASET.TABLE]
                        This option has been deprecated and will be removed in a future release.
  [--service-account or -sa PATH_TO_SA_KEY]
                        Service account to use for BigQuery result handler output.
  [--config-file or -c CONFIG_FILE]
                        YAML Config File Path to be used for storing validations and other features. Supports GCS and local paths.
                        See: *Running DVT with YAML Configuration Files* section
  [--config-file-json or -cj CONFIG_FILE_JSON]
                        JSON Config File Path to be used for storing validations only for application purposes.
  [--labels or -l KEY1=VALUE1,KEY2=VALUE2]
                        Comma-separated key value pair labels for the run.
  [--format or -fmt FORMAT]
                        Format for stdout output. Supported formats are (text, csv, json, table). Defaults to table.
  [--filter-status or -fs STATUSES_LIST]
                        Comma separated list of statuses to filter the validation results. Supported statuses are (success, fail). If no list is provided, all statuses are returned.
```

The default aggregation type is a 'COUNT *'. If no aggregation flag (i.e count,
sum , min, etc.) is provided, the default aggregation will run.

The [Examples](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/examples.md)
page provides few examples of how this tool can be used to run custom query validations.


#### Custom Query Row Validations

(Note: Custom query row validation is not supported for FileSystem connections. Struct and array data types are not currently supported.)

Below is the command syntax for row validations. In order to run row level
validations you need to pass `--hash` flag which will specify the fields
of the custom query result that will be concatenated and hashed. The primary key should be included
in the SELECT statement of both source_query.sql and target_query.sql.  See *Primary Keys* section

Below is the command syntax for custom query row validations.

```
data-validation
  [--verbose or -v ]
                        Verbose logging
  [--log-level or -ll]
                        Log Level to be assigned. Supported levels are (DEBUG,INFO,WARNING,ERROR,CRITICAL). Defaults to INFO.
  validate custom-query row
  --source-conn or -sc SOURCE_CONN
                        Source connection details
                        See: *Data Source Configurations* section for each data source
  --target-conn or -tc TARGET_CONN
                        Target connection details
                        See: *Connections* section for each data source
  --source-query SOURCE_QUERY, -sq SOURCE_QUERY
                        Source sql query
                        Either --source-query or --source-query-file must be provided
  --source-query-file SOURCE_QUERY_FILE, -sqf SOURCE_QUERY_FILE
                        File containing the source sql command. Supports GCS and local paths.
  --target-query TARGET_QUERY, -tq TARGET_QUERY
                        Target sql query
                        Either --target-query or --target-query-file must be provided
  --target-query-file TARGET_QUERY_FILE, -tqf TARGET_QUERY_FILE
                        File containing the target sql command. Supports GCS and local paths.
  --comparison-fields or -comp-fields FIELDS
                        Comma separated list of columns to compare. Can either be a physical column or an alias
                        See: *Calculated Fields* section for details
  --hash '*'            '*' to hash all columns.
  --concat COLUMNS      Comma separated list of columns to concatenate or * for all columns
                        (use if a common hash function is not available between databases)
  --max-concat-columns INT, -mcc INT
                        Maximum number of columns used in one --hash or --concat validation. When there are more columns in the validation, the validation will be split into multiple validations. There are engine specific defaults, so most users do not need to use this option unless they encounter errors.
  [--primary-keys PRIMARY_KEYS, -pk PRIMARY_KEYS]
                       Common column between source and target queries for join
  [--exclude-columns or -ec]
                        Flag to indicate the list of columns provided should be excluded from hash or concat instead of included.
  [--result-handler or -rh CONNECTION_NAME.SCHEMA.TABLE or BQ_PROJECT_ID.DATASET.TABLE]
                        Specify a BigQuery or PostgreSQL connection name as destination for validation results.
                        Also supports legacy BigQuery format BQ_PROJECT_ID.DATASET.TABLE.
                        See: *Validation Reports* section
  [--bq-result-handler or -bqrh PROJECT_ID.DATASET.TABLE or CONNECTION_NAME.DATASET.TABLE]
                        This option has been deprecated and will be removed in a future release.
  [--service-account or -sa PATH_TO_SA_KEY]
                        Service account to use for BigQuery result handler output.
  [--config-file or -c CONFIG_FILE]
                        YAML Config File Path to be used for storing validations and other features. Supports GCS and local paths.
                        See: *Running DVT with YAML Configuration Files* section
  [--config-file-json or -cj CONFIG_FILE_JSON]
                        JSON Config File Path to be used for storing validations only for application purposes.
  [--labels or -l KEY1=VALUE1,KEY2=VALUE2]
                        Comma-separated key value pair labels for the run.
  [--format or -fmt FORMAT]
                        Format for stdout output. Supported formats are (text, csv, json, table). Defaults to table.
  [--use-random-row or -rr]
                        Finds a set of random rows of the first primary key supplied.
  [--random-row-batch-size or -rbs]
                        Row batch size used for random row filters (default 10,000).
  [--filter-status or -fs STATUSES_LIST]
                        Comma separated list of statuses to filter the validation results. Supported statuses are (success, fail). If no list is provided, all statuses are returned.
  [--case-insensitive-match, -cim]
                        Performs a case insensitive match by adding an UPPER() before comparison.
```

The [Examples](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/examples.md)
page provides few examples of how this tool can be used to run custom query row validations.

#### Dry Run Validation

The `validate` command takes a `--dry-run` command line flag that prints source
and target SQL to stdout as JSON in lieu of performing a validation:

```
data-validation
  [--verbose or -v ]
                        Verbose logging
  [--log-level or -ll]
                        Log Level to be assigned. Supported levels are (DEBUG,INFO,WARNING,ERROR,CRITICAL). Defaults to INFO.
  validate
  [--dry-run or -dr]    Prints source and target SQL to stdout in lieu of performing a validation.
```

For example, this flag can be used as follows:

```shell
> data-validation validate --dry-run row \
  -sc my_bq_conn \
  -tc my_bq_conn \
  -tbls bigquery-public-data.new_york_citibike.citibike_stations \
  --primary-keys station_id \
  --hash '*'
{
    "source_query": "SELECT `hash__all`, `station_id`\nFROM ...",
    "target_query": "SELECT `hash__all`, `station_id`\nFROM ..."
}
```
#### Running DVT at on-prem
On-premises environments can have limited access to GCP services. DVT supports using BigQuery for storing validation results, GCS for storage and
the Secret Manager for secrets. You may also use BigQuery and Spanner as a source or target for validation. Service
APIs (i.e. bigquery.googleapis.com) may not always be accessible due to firewall restrictions. Work with your network
adminstrator to identify the way to access these services. They may set up a [Private Service Connect Endpoint](https://cloud.google.com/vpc/docs/about-accessing-google-apis-endpoints). DVT supports accessing source and target tables in Spanner and BigQuery via endpoints set up in your network. Connection Parameters for [Spanner](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#google-spanner) and [BigQuery](https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#google-bigquery) outline regarding how to specify endpoints.

### Running DVT with YAML Configuration Files

Running DVT with YAML configuration files is the recommended approach if:
* you want to customize the configuration for any given validation OR
* you want to run DVT at scale (i.e. run multiple validations sequentially or in parallel)

We recommend generating YAML configs with the `--config-file <file-name>` flag when running a validation command, which supports
GCS and local paths.

You can use the `data-validation configs` command to run and view YAMLs.

```
data-validation
  [--verbose or -v ]
                        Verbose logging
  [--log-level or -ll]
                        Log Level to be assigned. Supported levels are (DEBUG,INFO,WARNING,ERROR,CRITICAL). Defaults to INFO.
  configs run
  [--config-file or -c CONFIG_FILE]
                        Path to YAML config file to run. Supports local and GCS paths.
  [--config-dir or -cdir CONFIG_DIR]
                        Directory path containing YAML configs to be run sequentially. Supports local and GCS paths.
  [--dry-run or -dr]    If this flag is present, prints the source and target SQL generated in lieu of running the validation.
  [--kube-completions or -kc]
                        Flag to indicate usage in Kubernetes index completion mode.
                        See *Scaling DVT* section
```

```
data-validation configs list
  [--config-dir or -cdir CONFIG_DIR]
                        GCS or local directory from which to list validation YAML configs. Defaults to current local directory.
```

```
data-validation configs get
  [--config-file or -c CONFIG_FILE] GCS or local path of validation YAML to print.
```

View the complete YAML file for a Grouped Column validation on the