DVT Runbook: Oracle / PostgreSQL to BigQuery

Step-by-step instructions and hands-on experience with Google's Data Validation Tool (DVT, package google-pso-data-validator, tested with v8.9.1). Validated end to end on macOS (Apple Silicon) with a local Oracle instance and BigQuery, using the public RNAcentral RNC_DATABASE table as test data. Last verified 2026-08-10.

note

This runbook covers the path we exercised, not every DVT feature. The full command reference is linked under References.

1. Overview

DVT is a Python CLI built on Ibis. It connects to a source and a target database, compiles separate SQL for each engine, executes both queries concurrently, and compares the results locally in Python as Pandas DataFrames in memory. The machine running DVT needs network access to both systems.

How much data crosses the wire depends on the validation type:

• Column validation transfers only aggregated scalars: each side returns one row holding one value per requested aggregate (count, sum, min, max, avg). Memory scales with the number of aggregates, not with the row count, so the result set stays in the kilobyte range whatever the table size. For row-level validation of larger data volumes, the recommended approach is sampling, described in the Validation with samples section.
• Row validation (hash or concat) fetches per-row data into memory on both sides, so memory grows with the number of rows validated. There is no streaming: both result sets are fully materialised before the comparison starts. Read Memory, sizing, and large tables before running it against a big table.

note

BigQuery-to-BigQuery validation follows the same flow. There is no server-side cross-project join: both sides are queried independently and compared client-side.

Objects you work with

• Connections for the source and the target schema.
• A result table for evaluation, in BigQuery or PostgreSQL. For quick ad hoc tests stdout is enough; for real migration validation persist results to a table.

Validation types

Type	What it checks
schema	Column existence and type compatibility
column	Row counts and SUM / MIN / MAX / AVG aggregates
row	Per-row SHA256 hash, joined on the primary key
custom-query	Business-rule SQL on both sides, compared on the output
Sampling	Random or deterministic subsets of rows, see Validation with samples

2. Prerequisites

• Any Linux or macOS machine with network access to both databases. For a local Oracle plus BigQuery setup only HTTPS egress to Google APIs is needed.
• Python 3.11. This runbook was validated on 3.11 with DVT 8.9.1, which declares Python 3.9 to 3.11 in its package metadata and runs its upstream test matrix on 3.10 to 3.12. The current release, 8.9.3 (September 2026), extends the declared range to 3.12 with the same dependency pins, so 3.12 is the alternative if you upgrade DVT. Do not start new work on 3.9 or 3.10: 3.9 has been end of life since October 2025 and 3.10 reaches end of life on 31 October 2026. Plan for 3.11's own end of life in October 2027. DVT's pinned ibis-framework 7.1.0 requires numpy<2 and pyarrow 14.x, which have no wheels for Python 3.13 or 3.14. On a too-new Python, pip attempts a pyarrow source build and fails; the first symptom is ModuleNotFoundError: No module named 'pkg_resources'. Confirmed in practice on Python 3.14.
• Google Cloud SDK (gcloud, bq) installed and authenticated, plus application default credentials from gcloud auth application-default login. DVT's BigQuery client uses those credentials, so a working bq alone is not enough.

3. Installation

macOS

brew install python@3.11

python3.11 -m venv dvt-venv        # explicitly 3.11, not plain python3
source dvt-venv/bin/activate
pip install --upgrade pip
pip install google-pso-data-validator==8.9.1
pip install oracledb==4.0.2        # Oracle driver

The pins are the versions this runbook was validated with. Newer releases may work but were not tested here.

PostgreSQL support (psycopg2) ships with the base install. No Oracle Instant Client is needed: DVT uses python-oracledb in thin mode by default. Only --thick-mode (plus Instant Client) is required for features like wallet authentication.

Connection definitions are stored under ~/.config/google-pso-data-validator/. Set PSO_DV_CONN_HOME (for example to a gs:// path) to share them across machines or CI.

4. Source setup and connections

4.1 Oracle source

Typical local setup on Apple Silicon: Oracle Database Free in Docker, listener published on localhost:1521, PDB service name FREEPDB1 (XE images use XEPDB1). Test table RNC_DATABASE loaded from CSV.

Oracle Database Free in Docker

# Oracle AI Database 26ai Free (image version label 23.26.0), pinned to the digest we tested
docker pull container-registry.oracle.com/database/free@sha256:696eee2ee8985af25ef0dc4cbcac14cdaadfd4545150a87d82d9724ce43c7a77

docker run -d \
  --name oracle-free \
  -p 1521:1521 \
  -e ORACLE_PWD='<sys-password>' \
  -e ORACLE_CHARACTERSET=AL32UTF8 \
  -v oracle_free_data:/opt/oracle/oradata \
  container-registry.oracle.com/database/free@sha256:696eee2ee8985af25ef0dc4cbcac14cdaadfd4545150a87d82d9724ce43c7a77

docker exec -it oracle-free sqlplus sys/'<sys-password>'@//localhost:1521/FREEPDB1 as sysdba

As SYSDBA: create the test user

CREATE USER my_user IDENTIFIED BY "<user-password>";
GRANT CONNECT, RESOURCE TO my_user;
ALTER USER my_user QUOTA UNLIMITED ON users;
GRANT DBA TO my_user;

caution

DBA was granted only to keep the test simple. On client systems, follow the least-privilege principle: a read-only user on the schemas under validation is usually enough, but an additional role may be needed to read metadata. Verify the grants with a simple schema validation first.

Register the Oracle connection

data-validation connections add \
  --connection-name ora_source Oracle \
  --host localhost \
  --port 1521 \
  --user my_user \
  --password '<user-password>' \
  --database FREEPDB1

Identifier casing

Oracle stores unquoted identifiers in UPPERCASE, but DVT's Oracle backend rides on SQLAlchemy, which inverts the convention: write Oracle schema and table names in lowercase in DVT commands (my_schema.rnc_database). Uppercase input is treated as a case-sensitive quoted identifier and fails with NoSuchTableError even though the table exists. Confirmed in practice.

4.2 PostgreSQL source (alternative)

data-validation connections add \
  --connection-name pg_source Postgres \
  --host DB_HOST \
  --port 5432 \
  --user dvt_reader \
  --password '<password>' \
  --database mydb

The DVT machine needs outbound TCP 5432 to the host. Corporate networks and sandboxes often block it; test with a plain socket connect before debugging anything else. For Cloud SQL, connect through the Auth Proxy or private IP.

4.3 BigQuery target

data-validation connections add \
  --connection-name bq_target BigQuery \
  --project-id MY_PROJECT

data-validation connections list   # verify both connections

4.4 Result stores (one-time)

BigQuery results table

curl -O https://raw.githubusercontent.com/GoogleCloudPlatform/professional-services-data-validator/v8.9.1/terraform/results_schema.json
bq mk MY_PROJECT:pso_data_validator
bq mk --table \
  --time_partitioning_field start_time \
  --clustering_fields validation_name,run_id \
  MY_PROJECT:pso_data_validator.results \
  results_schema.json

PostgreSQL results store (not exercised in this evaluation): create the writer user, the pso_data_validator schema, and its CREATE and USAGE grants with the SQL from the DVT installation guide. DVT creates the results table itself the first time a result handler references it. Then register a connection for the writer:

data-validation connections add \
  --connection-name pg_results Postgres \
  --host DB_HOST --port 5432 \
  --user dvt_results_writer --password '<password>' \
  --database mydb

Point any validation at a store with --result-handler (-rh):

Store	Flag value
BigQuery, by project id	-rh MY_PROJECT.pso_data_validator.results
BigQuery, by connection name	-rh bq_target.pso_data_validator.results
PostgreSQL, by connection name	-rh pg_results.pso_data_validator.results

Add --labels key=value,... to tag runs so you can filter them in the results table later.

4.5 Test table (optional)

We loaded this CSV (RNAcentral RNC_DATABASE, on the Aliz Drive) on both sides. For a small file the BigQuery console upload and a database client import (DBeaver: right-click the Oracle schema, Import) are the quickest routes.

Autodetect drifts types

bq load --autodetect does not mirror the source types. In our run it produced BOOL where Oracle had VARCHAR2 ('Y' / 'N') and TIMESTAMP where Oracle had a string. That is real type drift: it fails schema validation and would also break row hashing. For a clean baseline load with an explicit schema JSON that mirrors the source types, or align the source column types. Do not allow-list genuine drift away.

5. Validation commands

All examples use the ora_source and bq_target connections, the table mapping my_schema.rnc_database=data_validation.rnc_database, and persist to the BigQuery results table. Swap ora_source for pg_source for a PostgreSQL source.

5.1 Schema validation

data-validation validate schema \
  -sc ora_source -tc bq_target \
  -tbls my_schema.rnc_database=data_validation.rnc_database \
  --allow-list 'int32:int64,!int32:!int64' \
  --result-handler MY_PROJECT.pso_data_validator.results \
  --labels env=e2e-test,check=schema

The example assumes the BigQuery id column was created as REQUIRED, mirroring the Oracle primary key, which is why it lists !int32:!int64 rather than !int32:int64.

Three lessons from reading the output:

• A ! prefix means NOT NULL. An Oracle column under a PRIMARY KEY or NOT NULL constraint reports as !int32, while a nullable BigQuery column reports plain int64. Allow-list matching is an exact string match including the !, so int32:int64 does not cover !int32:int64. List every variant you accept, or make the BigQuery column REQUIRED so the constraint migrates too (then allow !int32:!int64).
• Oracle TIMESTAMP vs BigQuery TIMESTAMP is a benign mismatch. Oracle's type is timezone-less (timestamp), BigQuery's is UTC-anchored (timestamp('UTC')). Allow-list it, or use DATETIME (BigQuery's timezone-less type) for an exact match with no allow-list entry.
• Start with a small table that contains every data type you will meet in the full run. It surfaces the type mappings you need to decide on before the long validations start.

tip

Upstream publishes ready-made allow-lists per migration path. --allow-list-file gs://dvt-allow-list-files/oracle_to_bigquery.yaml covers the common Oracle to BigQuery mappings and can be combined with --allow-list. The pip package does not ship the repository's samples/ folder, so use the bucket path or download the file from the GitHub repository.

5.2 Column validation: counts and aggregates

Row count only (default when no aggregate flag is given)

data-validation validate column \
  -sc ora_source -tc bq_target \
  -tbls my_schema.rnc_database=data_validation.rnc_database \
  --result-handler MY_PROJECT.pso_data_validator.results

Count every column plus aggregates on numeric columns

data-validation validate column \
  -sc ora_source -tc bq_target \
  -tbls my_schema.rnc_database=data_validation.rnc_database \
  --count '*' \
  --sum   num_sequences,num_organisms \
  --min   id --max id --avg avg_length \
  --result-handler MY_PROJECT.pso_data_validator.results

Adjust the column lists to your table.

5.3 Row validation: SHA256 hash per row

The primary key is the join condition between the two datasets. If the table has none, generate one from a combination of columns or with a ROW_NUMBER() expression in a custom query.

Hash every column

data-validation validate row \
  -sc ora_source -tc bq_target \
  -tbls my_schema.rnc_database=data_validation.rnc_database \
  --primary-keys id \
  --hash '*' \
  --result-handler MY_PROJECT.pso_data_validator.results

Some columns cannot hash identically for representation reasons: timestamp formatting, CHAR padding, NUMBER scale rendering. Leave them out of the hash and cover them with aggregates or a custom query instead. --exclude-columns is a flag without arguments: it inverts the --hash list, so the columns named after --hash are the ones excluded and everything else is hashed.

Hash every column except the listed ones

data-validation validate row \
  -sc ora_source -tc bq_target \
  -tbls my_schema.rnc_database=data_validation.rnc_database \
  --primary-keys id \
  --hash example,project_id,reference \
  --exclude-columns \
  --result-handler MY_PROJECT.pso_data_validator.results

5.4 Custom-query validation: business rules

The source query is written in the source dialect (Oracle or PostgreSQL SQL), the target query in BigQuery SQL. They only need to agree on the output columns.

Column-style: compare an aggregate produced by arbitrary SQL on each side

data-validation validate custom-query column \
  -sc ora_source -tc bq_target \
  --source-query "SELECT count(*) AS active_dbs FROM my_schema.rnc_database WHERE alive = 'Y'" \
  --target-query "SELECT count(*) AS active_dbs FROM data_validation.rnc_database WHERE alive = 'Y'" \
  --sum active_dbs \
  --result-handler MY_PROJECT.pso_data_validator.results

DVT treats each query's result set as a table and applies the aggregate on top of it. Both queries here return a single row, so --count active_dbs would compare 1 with 1 and pass whatever the underlying counts are. --sum over that one row compares the actual values.

5.5 Validation with samples

Useful for very large tables where a full scan is too expensive. Two methods:

• Random row sample with DVT's built-in flags. Non-deterministic: each run picks different rows. Upstream recommends it for fewer than 10,000 rows with a single primary key.
• Deterministic sample with a hash predicate in a custom query. The same rows are selected on both sides on every run, so results are reproducible and the validation can be re-run after a fix.

Random sample, built in (non-deterministic)

data-validation validate row \
  -sc ora_source -tc bq_target \
  -tbls my_schema.rnc_database=data_validation.rnc_database \
  --primary-keys id \
  --hash '*' \
  --use-random-row \
  --random-row-batch-size 10 \
  --result-handler MY_PROJECT.pso_data_validator.results \
  --labels env=e2e-test,check=random-row

Seeded 1-in-10 sample via hash predicate (deterministic)

data-validation validate custom-query row \
  -sc ora_source -tc bq_target \
  --source-query "SELECT * FROM my_schema.rnc_database WHERE MOD(TO_NUMBER(SUBSTR(RAWTOHEX(STANDARD_HASH(TO_CHAR(id) || 'seed1', 'SHA256')), 1, 10), 'XXXXXXXXXX'), 10) = 0" \
  --target-query "SELECT * FROM data_validation.rnc_database WHERE MOD(CAST(CONCAT('0x', LEFT(TO_HEX(SHA256(CONCAT(CAST(id AS STRING), 'seed1'))), 10)) AS INT64), 10) = 0" \
  --primary-keys id \
  --hash '*' \
  --result-handler MY_PROJECT.pso_data_validator.results \
  --labels env=e2e-test,check=random-row-seeded

Both predicates hash the primary key with the same seed, take the first ten hex characters, and keep the rows where the value modulo 10 is zero. Change the seed to get a different sample and the modulus to change the sample size. Details in the upstream random row validation page.

5.6 Dry run

--dry-run prints the SQL DVT would run on each side instead of executing it. Use it to review the pushdown queries before running against a large production source.

data-validation validate --dry-run row \
  -sc ora_source -tc bq_target \
  -tbls my_schema.rnc_database=data_validation.rnc_database \
  --primary-keys id \
  --hash example,project_id,reference \
  --exclude-columns

5.7 Repeatability: YAML configs

For larger validation tasks generate a YAML config from a command, version it with the project, and run it from the file. Adding --config-file to any validate command writes the config instead of running the validation.

Generate

data-validation validate row \
  -sc ora_source -tc bq_target \
  -tbls my_schema.rnc_database=data_validation.rnc_database \
  --primary-keys id \
  --hash example,project_id,reference \
  --exclude-columns \
  --config-file rnc_database_row.yaml \
  --result-handler MY_PROJECT.pso_data_validator.results

Run

data-validation configs run --config-file rnc_database_row.yaml

configs run --config-dir PATH runs every YAML in a directory sequentially; local and gs:// paths both work.

5.8 Other useful options

• Result handler format. The examples use the legacy MY_PROJECT.pso_data_validator.results form. DVT still accepts it but recommends the connection-name form for real work, for example bq_target.pso_data_validator.results. Both forms are listed under Result stores.
• --filters SOURCE_FILTER:TARGET_FILTER validates a subset of a table, for example an incremental load or a date range, instead of the whole table. Not exercised in this evaluation.

6. Lessons learned

Symptom	Root cause	Fix	Test steps
pip install fails building pyarrow (pkg_resources error)	Python 3.14 venv; DVT 8.9.1 declares 3.9 to 3.11 in setup.py and tests 3.10 to 3.12 in its noxfile; 8.9.3 adds 3.12 to setup.py; the pinned dependencies have no cp313 / cp314 wheels	Recreate the venv with python3.11 (or 3.12 with DVT 8.9.3); avoid 3.9 and 3.10, which are end of life or reach it in October 2026	It's technical limitation, I used 3.11 for test
Public PostgreSQL source unusable	The read-only shared RNAcentral endpoint lacked the privileges the exercise needed	Local Oracle instance with an own user; verify grants up front	Tested with the RNAcentral public database; the provided user lacks the required privileges and they cannot be changed.
NoSuchTableError: RNC_DATABASE although the table exists	SQLAlchemy treats UPPERCASE Oracle names as case-sensitive quoted identifiers	Write Oracle identifiers in lowercase in -tbls	Tested
Allow-listed int32:int64 still fails on the id column (!int32)	! marks NOT NULL (primary key on the Oracle side); allow-list matching is exact, including the !	Add !int32:int64, or make the BigQuery column REQUIRED and allow !int32:!int64	Schema adjustment tested
alive: string vs boolean, timestamp: string vs timestamp	CSV loaded with different types on each side (bq --autodetect vs Oracle CSV import)	Reload with an explicit schema mirroring the source types; do not allow-list real drift	Oracle and Google Cloud BigQuery tested

7. Running DVT in a real project

A laptop is fine for evaluation but not for migration validation at scale. Both sides must be reachable from wherever DVT runs, so plan the execution environment around network connectivity first, then size it with the memory and throughput figures below. Upstream ships two integration samples we have not exercised yet.

7.1 Data Validation on Cloud Run (service)

Upstream sample with a README: samples/cloud_run. It wraps DVT in a request-driven Cloud Run service. For batch validation, Cloud Run Jobs are the better fit: a job task is not bound by a request timeout, runs one partition each, and gets memory sized for that partition's row count. The throughput figures below were measured that way.

7.2 Data Validation on Airflow (ideally Cloud Composer)

Upstream sample DAGs: samples/airflow. The main DAG runs DVT in-process on the Airflow worker with PythonVirtualenvOperator; the kubernetes_pod_operator variant runs it in a separate pod from a DVT container image.

Airflow workers are memory-constrained

A Composer worker has a few GB of memory in the default presets, shared by every task running on it, and is sized for orchestration rather than for holding millions of hashed rows. Running DVT in-process is fine for schema and column validation, which return a handful of scalars. For row validation, which needs about 2 GB of RAM per million rows, keep Airflow as the orchestrator and offload the DVT run to a Cloud Run Job (CloudRunExecuteJobOperator) or a Kubernetes pod (KubernetesPodOperator, GKEStartPodOperator) sized for the partition. Fan the configs from generate-table-partitions out as one job or pod per task.

8. Memory, sizing, and large tables

Column validation (counts, SUM, MIN, MAX, AVG): the database engines do the aggregation and DVT receives only scalar results. A small VM (1 vCPU / 4 GB) handles any table size.

Row validation (hash per row): all row data materialises as Pandas DataFrames in memory. The DVT team's working assumption from their internal throughput tests is about 2 GB of RAM per million rows in a single pass.

Scaling strategy for large tables (100 GB and above):

1. Use generate-table-partitions --partition-num=N to split the table by primary key range into N YAML configs, each covering a bounded row count.
2. Run the partitions in parallel on Cloud Run Jobs or Kubernetes. The upstream throughput benchmarks use --memory 4G --cpu 1 per task with 64 partitions for 100M rows, about 1.56M rows per partition.
3. Alternative controls: --use-random-row --random-row-batch-size N for a statistical sample with low memory, or --limit N, which caps the rows but skews the result because it takes an arbitrary subset with no primary key alignment.

DVT is single-threaded for the CPU-intensive comparison work. Adding vCPUs beyond one or two does not speed up a single partition. Horizontal scaling, more partitions in parallel, is the way to speed up large tables.

VM sizing guideline (single VM, partitions executed sequentially):

Validation type	Table size	Recommended minimum
Column (aggregates)	Any	1 vCPU / 4 GB (for example e2-medium)
Row (partitioned)	100 GB+	1 vCPU / 4 GB per partition (for example n2-standard-2 running partitions sequentially)
Row (single pass)	Under 2M rows	1 vCPU / 8 GB

What parallelism means here

The parallelism figures below refer to separate Cloud Run tasks running at the same time. Each task is its own 4 GB / 1 vCPU container processing one partition, and memory is per container, not shared. On a single VM running partitions sequentially 4 GB is still enough: each partition finishes and frees its memory before the next one starts. The only reason to scale a single machine vertically is running several partitions concurrently on it (four at once needs about 16 GB). That is not how DVT is designed to parallelise; it expects an external orchestrator (Cloud Run Jobs, Kubernetes, Airflow) to fan partitions out across separate workers.

Throughput reference (Oracle to BigQuery, 100M rows, Cloud Run, 4 GB / 1 vCPU per task):

Parallelism	Elapsed	Throughput
1	62 min	1.5M rows/min
4	16 min	6.2M rows/min
8	9 min	11.3M rows/min

Query concurrency vs single-threaded comparison. DVT runs the source and target SELECT queries concurrently. Once the engines return the data, the row-by-row hash comparison happens locally in Python on Pandas DataFrames and is strictly single-threaded. This is why extra vCPUs on a single partition bring no benefit.

Source connection limits (the "thundering herd"). BigQuery absorbs massive concurrency, the source usually does not. Running 50 or 100 partitions in parallel opens 50 to 100 concurrent connections to the Oracle or PostgreSQL source and triggers a burst of parallel full-table scans. Check the source's connection limits (for example max_connections) and compute headroom before scaling DVT workers up.

9. Possible next steps

• Performance evaluation with large data volumes.
• Integration test with the selected Google service (Cloud Run or Composer).
• More source engines in end-to-end tests.

10. References

• DVT repository and command reference
• Installation guide
• Connections guide (Oracle, PostgreSQL, BigQuery, Secret Manager)
• Random row validation
• Throughput and sizing notes
• python-oracledb driver
• Original runbook (Google Doc, Attila Fábián, August 2026)