Validating Data from PostgreSQL¶

This page covers PostgreSQL-specific setup for Data validation. For workflow field definitions, see Data validation configuration reference.

Prerequisites¶

Npgsql driver on Workers (no ODBC install). Same ssl_mode guidance as Migrating Data from PostgreSQL.

Connectivity¶

Reuse [connections.source.postgresql] Worker TOML from data migration:

[connections.source.postgresql]
auth_method = "standard"
username = "scai"
password = "your_password"
database = "analytics"
host = "db.example.com"
port = "5432"
ssl_mode = "Require"
use_copy = true

Set source_platform: postgresql in the validation workflow YAML. Set ssl_mode in Worker TOML to match your PostgreSQL host’s TLS requirement (Disable, Allow, Prefer, Require, or VerifyFull).

Example workflow excerpt:

source_platform: postgresql
target_database: TARGET_DB
schema_mappings:
  public: PUBLIC
validation_configuration:
  schema_validation: true
  metrics_validation: true
  row_validation: false
tables:
  - fully_qualified_name: public.customers
    column_names_to_partition_by:
      - customer_id
  - fully_qualified_name: public.orders
    column_names_to_partition_by:
      - order_id
    index_column_list:
      - order_id
    validation_configuration:
      row_validation: true

Data type mappings¶

PostgreSQL type	Snowflake target type	Supported for validation	Notes
integer, bigint, numeric, decimal	NUMBER	Yes
real, double precision	FLOAT	Yes
boolean	BOOLEAN	Yes
date	DATE	Yes
time, timestamp types	TIME / TIMESTAMP	Yes
character varying, text	VARCHAR	Yes
bytea	BINARY	Yes
uuid	VARCHAR	Yes
json, jsonb, xml	VARIANT	No
money	NUMBER	No
bit, bit varying	BINARY	No
point	GEOMETRY	No
oid	VARCHAR	No
interval	INTERVAL or VARCHAR	Yes	Depends on `intervalHandling`. Match the setting used for migration. See INTERVAL columns and intervalHandling.
range types	VARCHAR	No	Compared as VARCHAR text
PostGIS geometry/geography	GEOMETRY / GEOGRAPHY	No

INTERVAL columns and intervalHandling¶

PostgreSQL’s interval type can store year-month and day-time fields in the same value. Snowflake has no single interval qualifier that spans both families.

Validation uses the same intervalHandling choice as migration. Neither setting is universally better: choose (and keep consistent) based on how the table was migrated and what you need on Snowflake.

`intervalHandling`	Comparison behavior	Implications
`"interval"` (default)	Compares as Snowflake `INTERVAL DAY TO SECOND`	Matches tables migrated with `intervalHandling: "interval"`. Mixed year-month and day-time values were folded into day-time during migration (via total seconds), so calendar-accurate year-month precision is already lost on the target. Validation normalizes both sides the same way.
`"varchar"`	Compares as text	Matches tables migrated with `intervalHandling: "varchar"`. Preserves mixed-family interval text, but you don’t compare against a native `INTERVAL` column.

Set intervalHandling at the workflow root or per table, and keep it aligned with the migration workflow for the same objects. A mismatch between migration and validation produces false MISMATCH results. For the property reference, see INTERVAL data type handling. For the migration-side choice, see Migrating Data from PostgreSQL.

Prompt:

The EVENTS table was migrated with intervalHandling set to varchar. Use the same setting for validation.

Platform-specific considerations¶

Starting validation: Ask the agent to generate a validation workflow with the depth you need (L1/L2/L3 per table).