Snowflake Data Validation CLI - Complete Usage Guide¶

SQL Server ODBC Driver¶

For SQL Server as a source database, you need the Microsoft ODBC Driver for SQL Server.

Recommended Version: ODBC Driver 17 or 18 for SQL Server

Installation Instructions:

• Linux:

  # Ubuntu/Debian
  curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
  curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list
  apt-get update
  ACCEPT_EULA=Y apt-get install -y msodbcsql18
  

  Copy

• macOS:

  # Using Homebrew
  brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
  brew update
  HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18
  

  Copy

• Windows: Download and install from Microsoft’s official download page

Verification:

# List available drivers (should show ODBC Driver 17 or 18 for SQL Server)
odbcinst -q -d

Documentation: Microsoft ODBC Driver for SQL Server

Teradata ODBC Driver¶

For Teradata as a source database, you need the Teradata ODBC Driver.

Recommended Version: Teradata ODBC Driver 17.20 or higher

Installation Instructions:

1. Download the Teradata Tools and Utilities (TTU) package from Teradata Downloads

2. Select your operating system and download the appropriate installer

3. Run the installer and select “ODBC Driver” during installation

4. Configure the driver according to Teradata’s documentation

Note: You may need to create a Teradata account to access the download page.

Configuration: After installation, you may need to configure the ODBC driver:

• Linux/macOS: Edit /etc/odbc.ini and /etc/odbcinst.ini

• Windows: Use the ODBC Data Source Administrator

Documentation: Teradata ODBC Driver Documentation

Amazon Redshift ODBC Driver¶

For Amazon Redshift as a source database, you need the Amazon Redshift ODBC Driver or a PostgreSQL ODBC Driver (since Redshift is PostgreSQL-compatible).

Option 1: Amazon Redshift ODBC Driver (Recommended)

Recommended Version: Amazon Redshift ODBC Driver 2.x

Installation Instructions:

• Download from Amazon Redshift ODBC Driver Download

• Choose your operating system and architecture

• Follow the installation wizard

Option 2: PostgreSQL ODBC Driver (Alternative)

• Linux:

  # Ubuntu/Debian
  sudo apt-get install odbc-postgresql
  
  # RHEL/CentOS/Fedora
  sudo yum install postgresql-odbc
  

  Copy

• macOS:

  # Using Homebrew
  brew install psqlodbc
  

  Copy

• Windows: Download from PostgreSQL ODBC Driver

Verification:

# List available drivers (should show Amazon Redshift or PostgreSQL drivers)
odbcinst -q -d

Documentation: Amazon Redshift ODBC Driver Documentation

Phase 1: Start with a Sample Dataset¶

Goal: Verify configuration and establish baseline

Approach:

• Test with 1-2 small tables first (< 100,000 rows)

• Choose tables with diverse data types to validate type mapping

• Verify connectivity and authentication work correctly

• Confirm output format meets your needs

Example Configuration:

tables:
  - source_table_name: "small_reference_table"
    target_table_name: "small_reference_table"
    source_schema_name: "dbo"
    target_schema_name: "PUBLIC"
    target_database_name: "MIGRATION_DB"
    where_clause: "reference_id <= 1000"  # Limit to first 1000 rows on source
    target_where_clause: "reference_id <= 1000"  # Limit to first 1000 rows on target
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"

What to Verify:

• ✅ Connection to both source and target successful

• ✅ Schema validation passes

• ✅ Row count matches

• ✅ Data types mapped correctly

• ✅ Validation report generated successfully

Understanding where_clause vs target_where_clause:

The tool provides two filtering options:

• where_clause: Applied only to the source table

  where_clause: "ProductID <= 45"  # Filters only the source table
  

  Copy

• target_where_clause: Applied only to the target table (Snowflake)

  target_where_clause: "ProductID <= 45"  # Filters only the target table
  

  Copy

Common Usage Patterns:

# Pattern 1: Apply same filter to both source and target
tables:
  - source_table_name: "products"
    where_clause: "ProductID <= 100"           # Filter source
    target_where_clause: "ProductID <= 100"     # Filter target with same condition
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"

# Pattern 2: Different filters for source and target
# Useful when target has additional test data to exclude
tables:
  - source_table_name: "products"
    where_clause: "ProductID <= 100"                    # Filter source
    target_where_clause: "ProductID <= 100 AND ProductID != 5"  # Exclude test data in target
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"

# Pattern 3: Filter only source (validate all target data)
tables:
  - source_table_name: "products"
    where_clause: "ProductID <= 100"  # Only validate subset from source
    # No target_where_clause - validates all matching rows in target
    validations:
      - validation_type: "row_count"

Phase 2: Verify Small Subsets of Production Tables¶

Goal: Test against actual production data patterns with limited scope

Approach:

• Select a subset of rows from production tables (10,000 - 100,000 rows)

• Use where_clause and target_where_clause to restrict data

• Focus on recent data or specific partitions

• Validate critical business columns first

Example Configuration:

tables:
  - source_table_name: "large_transactions_table"
    target_table_name: "large_transactions_table"
    source_schema_name: "sales"
    target_schema_name: "SALES"
    target_database_name: "MIGRATION_DB"
    where_clause: "transaction_date >= '2024-01-01' AND transaction_date < '2024-02-01'"
    target_where_clause: "transaction_date >= '2024-01-01' AND transaction_date < '2024-02-01'"
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"
        columns_to_validate:
          - "transaction_id"
          - "amount"
          - "customer_id"
          - "transaction_date"

Key Points:

• 🎯 Use date ranges or ID ranges to limit scope

• 🎯 Test different data patterns (recent vs. historical, high-volume dates)

• 🎯 Validate critical business columns before validating all columns

Phase 3: Partition-Based Validation for Large Tables¶

Goal: Validate large tables efficiently using partitioning strategy

⚠️ IMPORTANT: For tables with millions or billions of rows, validating the entire table at once is:

• Resource-intensive (high compute costs)

• Time-consuming (can take hours/days)

• Risky (harder to identify specific issue patterns)

Recommended Approach for Large Tables:

Strategy 1: Date-Based Partitioning

Validate data in chunks based on date ranges:

# Validation 1: January 2024
tables:
  - source_table_name: "orders"
    target_table_name: "orders"
    source_schema_name: "dbo"
    target_schema_name: "PUBLIC"
    target_database_name: "MIGRATION_DB"
    where_clause: "order_date >= '2024-01-01' AND order_date < '2024-02-01'"
    target_where_clause: "order_date >= '2024-01-01' AND order_date < '2024-02-01'"
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"

# Validation 2: February 2024
# Create separate config with: 
# where_clause: "order_date >= '2024-02-01' AND order_date < '2024-03-01'"
# target_where_clause: "order_date >= '2024-02-01' AND order_date < '2024-03-01'"

Strategy 2: Modulo-Based Sampling

Use modulo arithmetic to sample evenly distributed rows:

tables:
  - source_table_name: "customers"
    target_table_name: "customers"
    source_schema_name: "dbo"
    target_schema_name: "PUBLIC"
    target_database_name: "MIGRATION_DB"
    where_clause: "customer_id % 100 = 0"  # 1% sample - evenly distributed
    target_where_clause: "customer_id % 100 = 0"
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"

Strategy 3: Statistical Sampling

For very large tables (> 100M rows), validate representative samples:

tables:
  - source_table_name: "clickstream_events"
    target_table_name: "clickstream_events"
    source_schema_name: "dbo"
    target_schema_name: "PUBLIC"
    target_database_name: "MIGRATION_DB"
    where_clause: "event_id % 10000 = 0"  # ~0.01% sample - statistically distributed
    target_where_clause: "event_id % 10000 = 0"
    validations:
      # First: Validate aggregates and row count (fast)
      - validation_type: "row_count"
      - validation_type: "aggregate_metrics"
      
      # Second: Validate a statistical sample of rows
      - validation_type: "column_level"

Strategy 4: Progressive Partition Validation

Validate multiple partitions progressively:

# config_q1.yaml - Validate Q1 2024
tables:
  - source_table_name: "orders"
    target_table_name: "orders"
    where_clause: "order_date >= '2024-01-01' AND order_date < '2024-04-01'"
    target_where_clause: "order_date >= '2024-01-01' AND order_date < '2024-04-01'"
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"

# config_q2.yaml - Validate Q2 2024 (run after Q1 passes)
tables:
  - source_table_name: "orders"
    target_table_name: "orders"
    where_clause: "order_date >= '2024-04-01' AND order_date < '2024-07-01'"
    target_where_clause: "order_date >= '2024-04-01' AND order_date < '2024-07-01'"
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"

# Validate Q1 2024
sdv sqlserver run-validation --data-validation-config-file config_q1.yaml

# If Q1 passes, validate Q2 2024
sdv sqlserver run-validation --data-validation-config-file config_q2.yaml

# Continue for remaining quarters

Phase 4: Full Validation¶

Goal: Complete comprehensive validation after successful subset testing

When to Run Full Validation:

• ✅ Sample validations pass successfully

• ✅ Subset validations show no data quality issues

• ✅ You have allocated sufficient time and compute resources

• ✅ Preferably during off-peak hours

Considerations:

• Use asynchronous validation for large datasets to avoid timeouts

• Consider using script generation mode to run validations in parallel

• Monitor resource consumption on both source and target systems

• Plan for validation to run during maintenance windows

Example:

# Generate scripts for parallel execution
sdv sqlserver generate-validation-scripts \
  --data-validation-config-file full_validation_config.yaml \
  --output-directory ./validation_scripts

# Review generated scripts and execute in parallel or scheduled

1. Use Appropriate Validation Types¶

Not all tables need all validation types:

# For reference/lookup tables (small, static)
validations:
  - validation_type: "schema"
  - validation_type: "row_count"
  - validation_type: "column_level"  # Full validation OK

# For large transaction tables
validations:
  - validation_type: "schema"
  - validation_type: "row_count"
  - validation_type: "aggregate_metrics"  # Use aggregates instead of row-by-row

2. Prioritize Critical Columns¶

For large tables, validate critical business columns first:

validations:
  - validation_type: "column_level"
    columns_to_validate:
      # Start with business-critical columns
      - "customer_id"
      - "transaction_amount"
      - "transaction_date"
      # Add more columns after initial validation succeeds

3. Leverage Partitioning Metadata¶

If your tables are partitioned, validate partition by partition:

# Config 1: Validate partition 1
tables:
  - source_table_name: "orders"
    target_table_name: "orders"
    where_clause: "partition_key = '2024-01'"
    target_where_clause: "partition_key = '2024-01'"
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"

# Config 2: Validate partition 2
tables:
  - source_table_name: "orders"
    target_table_name: "orders"
    where_clause: "partition_key = '2024-02'"
    target_where_clause: "partition_key = '2024-02'"
    validations:
      - validation_type: "row_count"
      - validation_type: "column_level"

4. Use Asynchronous Validation for Production¶

For production environments, use asynchronous validation:

# Async validation returns immediately, processes in Snowflake
sdv sqlserver run-async-validation \
  --data-validation-config-file config.yaml \
  --poll-interval 30 \
  --max-wait-time 3600

Estimate Query Costs¶

Before running validation on large tables:

1. Estimate row counts:

   -- Source
   SELECT COUNT(*) FROM source_table WHERE <partition_filter>;
   
   -- Target
   SELECT COUNT(*) FROM target_table WHERE <partition_filter>;
   

   Copy

2. Estimate data volume:

   -- Check table size
   SELECT 
       table_name,
       row_count,
       bytes,
       bytes / (1024*1024*1024) as size_gb
   FROM information_schema.tables
   WHERE table_name = 'your_table';
   

   Copy

3. Start with small partitions: If estimated size > 10 GB, break into smaller chunks

Compute Warehouse Sizing (Snowflake)¶

• Small tables (< 1M rows): XS or S warehouse

• Medium tables (1M - 10M rows): S or M warehouse

• Large tables (> 10M rows): M or L warehouse, use partitioning

• Very large tables (> 100M rows): L or XL warehouse, mandatory partitioning

Check Version¶

Display the current installed version of the Snowflake Data Validation CLI:

# Using full command name
snowflake-data-validation --version

# Using the alias
sdv --version

Output Example:

snowflake-data-validation 1.2.3

Use Cases:

• Verify successful installation

• Check which version is currently installed

• Confirm version before reporting issues

• Ensure compatibility with documentation

Help¶

Display general help information:

# General help
snowflake-data-validation --help
sdv --help

# Command-specific help
sdv sqlserver --help
sdv teradata run-validation --help
sdv redshift generate-validation-scripts --help

Common Commands¶

Run Validation:

sdv sqlserver run-validation \
  --data-validation-config-file ./configs/sqlserver_validation.yaml

Generate Scripts:

sdv sqlserver generate-validation-scripts \
  --data-validation-config-file /path/to/config.yaml

Get Templates:

sdv sqlserver get-configuration-files \
  --templates-directory ./templates

For complete documentation, see SQL Server Commands Reference.

Common Commands¶

Run Validation:

sdv teradata run-validation \
  --data-validation-config-file ./configs/teradata_validation.yaml

Generate Scripts:

sdv teradata generate-validation-scripts \
  ./config.yaml \
  --output-directory ./scripts

Get Templates:

sdv teradata get-configuration-files \
  --templates-directory ./templates

For complete documentation, see Teradata Commands Reference.

Common Commands¶

Run Validation:

sdv redshift run-validation \
  --data-validation-config-file ./configs/redshift_validation.yaml

Generate Scripts:

sdv redshift generate-validation-scripts \
  --data-validation-config-file /path/to/config.yaml

Get Templates:

sdv redshift get-configuration-files \
  --templates-directory ./templates

For complete documentation, see Redshift Commands Reference.

Platform Configuration Options¶

source_platform (required)

• Type: String

• Valid Values: SqlServer, Teradata, Redshift, Snowflake

• Description: The source database platform for validation

• Example: source_platform: SqlServer

target_platform (required)

• Type: String

• Valid Values: Snowflake

• Description: The target database platform (currently only Snowflake is supported)

• Example: target_platform: Snowflake

output_directory_path (required)

• Type: String (path)

• Description: Directory where validation results, logs, and reports will be saved

• Example: output_directory_path: /home/user/validation_output

max_threads (optional)

• Type: String or Integer

• Valid Values: "auto" or positive integer (1-32)

• Default: "auto"

• Description: Controls parallelization for validation operations

  • "auto": Automatically detects optimal thread count based on CPU cores

  • Integer value: Specifies exact number of threads to use

• Examples:

  max_threads: auto        # Auto-detect optimal threads
  max_threads: 4           # Use exactly 4 threads
  max_threads: 16          # Use 16 threads
  

  Copy

target_database (required for Teradata only)

• Type: String

• Description: Target database name in Snowflake for Teradata validations

• Example: target_database: PROD_DB

source_validation_files_path (optional)

• Type: String (path)

• Description: Path to the directory containing the source validation files.

• Example: source_validation_files_path: /path/to/source_validation_file/directory

target_validation_files_path (optional)

• Type: String (path)

• Description: Path to the directory containing the target validation files.

• Example: target_validation_files_path: /path/to/targetvalidation_file/directory

Source Connection Configuration¶

source_connection:
  mode: credentials
  host: "sqlserver.company.com"
  port: 1433
  username: "sqlserver_user"
  password: "secure_password"
  database: "source_database"
  trust_server_certificate: "no"   # Optional: yes/no
  encrypt: "yes"                   # Optional: yes/no/optional

source_connection:
  mode: credentials
  host: "teradata.company.com"
  username: "teradata_user"
  password: "secure_password"
  database: "source_database"

source_connection:
  mode: credentials
  host: "redshift-cluster.region.redshift.amazonaws.com"
  port: 5439
  username: "redshift_user"
  password: "secure_password"
  database: "source_database"

Target Connection (Snowflake)¶

Snowflake connections support three modes: name, default, and credentials (IPC only and SnowConvert exclusive).

target_connection:
  mode: name
  name: "my_snowflake_connection"

target_connection:
  mode: default

Enable Chunking¶

tables:
  - fully_qualified_name: database.schema.large_table
    use_column_selection_as_exclude_list: false
    column_selection_list: []
    chunk_number: 100  # Split into 100 chunks
    index_column_list:
      - primary_key_column

Increase Thread Count¶

max_threads: 32  # Use maximum threads for parallel processing

3. Filter Data¶

tables:
  - fully_qualified_name: database.schema.large_table
    use_column_selection_as_exclude_list: false
    column_selection_list: []
    where_clause: "created_date >= '2024-01-01' AND region = 'US'"
    target_where_clause: "created_date >= '2024-01-01' AND region = 'US'"

Selective Column Validation¶

tables:
  - fully_qualified_name: database.schema.large_table
    use_column_selection_as_exclude_list: true
    column_selection_list:
      - large_text_column
      - large_binary_column
      - xml_column

Step 1: Generate Scripts¶

sdv sqlserver generate-validation-scripts \
  --data-validation-config-file config.yaml

This generates SQL scripts in the output directory.

Step 2: Execute Scripts Manually¶

Execute the generated scripts on source and target databases, saving results to CSV files.

Step 3: Run Async Validation¶

sdv sqlserver run-async-validation \
  --data-validation-config-file config.yaml

This compares the pre-generated metadata files.

1. Main Validation Report (validation_report.csv)¶

This consolidated report contains results from schema and metrics validations for all tables.

2. Row Validation Reports (per table)¶

Separate reports generated for each table when row validation is enabled, containing detailed row-level comparison results.

Schema Validation¶

Compares structural metadata between source and target tables:

• Column existence: Ensures columns present in source exist in target

• Data types: Validates column data types match (with configurable mappings)

• Nullability: Checks if NULL constraints match between source and target

• Primary keys: Verifies primary key definitions

• Column precision/scale: Validates numeric precision and scale values

• Character length: Compares VARCHAR/CHAR column lengths

Example Schema Validation Row:

SCHEMA VALIDATION,mydb.myschema.customers,customer_id,DATA_TYPE,INT,NUMBER,FAILURE,"Data type mismatch detected"

Metrics Validation¶

Compares statistical metrics calculated on column data:

• Row count: Total number of rows in the table

• min: Minimum value in numeric/date columns

• max: Maximum value in numeric/date columns

• count: Count of non-null values

• count_distinct: Number of distinct values

• avg, sum, stddev, variance: Statistical measures (can be excluded via configuration)

Example Metrics Validation Row:

METRICS VALIDATION,mydb.myschema.orders,order_date,max,2024-12-31,2024-12-31,SUCCESS,""

Row Validation¶

Generates separate per-table reports comparing actual row data using MD5 checksums to detect differences.

Interpreting Schema Validation Results¶

Success Scenario:

• All columns exist in both source and target

• Data types match (considering configured type mappings)

• Nullability constraints are consistent

• All structural attributes align

Common Failure Scenarios:

1. Data Type Mismatch

   • Source: VARCHAR(50), Target: VARCHAR(100)

   • Status: May be SUCCESS if within tolerance, or FAILURE if strict matching is required

2. Missing Column

   • Source has column phone_number, target does not

   • Status: NOT_FOUND_TARGET

3. Nullability Difference

   • Source: NOT NULL, Target: NULL

   • Status: FAILURE

Interpreting Metrics Validation Results¶

Success Scenario:

• Row counts match exactly

• Statistical metrics are within configured tolerance (default: 0.1%)

• All calculated metrics align between source and target

Common Failure Scenarios:

1. Row Count Mismatch

   • Source: 10,000 rows, Target: 9,998 rows

   • Status: FAILURE

   • Action: Investigate missing rows

2. Min/Max Value Differences

   • Source max date: 2024-12-31, Target max date: 2024-12-30

   • Status: FAILURE

   • Action: Check for incomplete data migration

3. Statistical Variance

   • Source count_distinct: 1,000, Target count_distinct: 995

   • Status: FAILURE (if beyond tolerance)

   • Action: Investigate potential duplicates or missing values

Interpreting Row Validation Results¶

Success Scenario:

• All rows in source have matching rows in target (by MD5 checksum)

• No orphaned rows in either database

• Primary key values align correctly

Common Failure Scenarios:

1. Row Content Mismatch

   • Same primary key, different column values

   • Result: FAILURE

   • Action: Use provided SQL queries to investigate specific differences

2. Missing Rows

   • Row exists in source but not in target

   • Result: NOT_FOUND_TARGET

   • Action: Check migration completeness

3. Extra Rows

   • Row exists in target but not in source

   • Result: NOT_FOUND_SOURCE

   • Action: Investigate unexpected data in target

Quick Assessment¶

1. Filter by STATUS column: Focus on FAILURE, WARNING, NOT_FOUND_SOURCE, and NOT_FOUND_TARGET rows

2. Group by VALIDATION_TYPE: Assess schema issues separately from metrics issues

3. Group by TABLE: Identify which tables have the most issues

Investigating Failures¶

For Schema Validation:

1. Review the EVALUATION_CRITERIA to understand what attribute failed

2. Compare SOURCE_VALUE vs SNOWFLAKE_VALUE

3. Check if differences are acceptable (e.g., VARCHAR size increase)

4. Update type mappings or schema definitions if needed

For Metrics Validation:

1. Review the metric that failed (e.g., row_count, max, min)

2. Calculate the difference magnitude

3. Determine if within acceptable business tolerance

4. Use the detailed queries to investigate source of discrepancy

For Row Validation:

1. Open the table-specific row validation report

2. Identify rows with FAILURE status

3. Use the provided SOURCE_QUERY and TARGET_QUERY to retrieve actual row data

4. Compare column-by-column to identify specific field differences

5. Investigate why values differ (data type conversion, truncation, transformation)

Tolerance Settings¶

The comparison_configuration.tolerance setting affects metrics validation:

comparison_configuration:
  tolerance: 0.01  # 1% tolerance for numeric comparisons

• Values within tolerance are marked as SUCCESS

• Values beyond tolerance are marked as FAILURE

Validation Levels¶

Control which validations run and therefore which reports are generated:

validation_configuration:
  schema_validation: true    # Validates table/column structure
  metrics_validation: true   # Validates statistical metrics
  row_validation: false      # Validates individual row data (resource intensive)

Excluded Metrics¶

Exclude specific metrics from validation:

validation_configuration:
  exclude_metrics: true  # Excludes avg, sum, stddev, variance

Maximum Failed Rows¶

Limit the number of failed rows reported in row validation:

validation_configuration:
  max_failed_rows_number: 100  # Report up to 100 failed rows per table

All Validations Showing FAILURE¶

Possible Causes:

• Incorrect database/schema mappings in configuration

• Type mapping file not loaded correctly

• Connection to wrong target database

Solution: Verify database_mappings, schema_mappings, and connection settings

Row Validation Shows All NOT_FOUND_TARGET¶

Possible Causes:

• Target table empty or not migrated yet

• Incorrect target table name

• Primary key/index columns mismatch

Solution: Verify target table exists and contains data, check column mappings

Metrics Validation Shows Large Differences¶

Possible Causes:

• Incomplete data migration

• Data type conversion issues causing value changes

• Filter/WHERE clause differences between source and target queries

Solution: Review migration logs, verify row counts first, check data transformations

Report File Not Generated¶

Possible Causes:

• Output directory doesn’t exist or lacks write permissions

• Validation configuration has all levels set to false

• Application crashed before report generation

Solution: Check output path permissions, review logs for errors, enable at least one validation level

Issue: “Configuration file not found”¶

Symptom:

Configuration file not found: ./config.yaml

Solution:

• Verify the file path is correct

• Use absolute paths: /home/user/configs/validation.yaml

• Check file permissions

Issue: “Connection error”¶

Symptom:

Connection error: Unable to connect to database

Solutions:

1. Verify connection parameters:

   source_connection:
     host: correct-hostname.com  # Verify hostname
     port: 1433                  # Verify port
     username: valid_user        # Verify username
     password: correct_password  # Verify password
   

   Copy

2. Check network connectivity:

   # Test connection to SQL Server
   telnet sqlserver.company.com 1433
   
   # Test connection to Teradata
   telnet teradata.company.com 1025
   
   # Test connection to Redshift
   telnet redshift-cluster.amazonaws.com 5439
   

   Copy

3. Verify firewall rules allow connections from your machine

4. For SQL Server SSL issues:

   source_connection:
     trust_server_certificate: "yes"  # Try this if SSL errors occur
     encrypt: "optional"
   

   Copy

Issue: “Invalid parameter”¶

Symptom:

Invalid parameter: max_threads must be 'auto' or a positive integer

Solution:

# Correct
max_threads: auto
max_threads: 4

# Incorrect
max_threads: "4"      # Remove quotes
max_threads: 0        # Must be positive
max_threads: -1       # Must be positive

Issue: “Table not found”¶

Symptom:

Table not found: database.schema.table_name

Solutions:

1. Verify fully qualified name:

   # For SQL Server and Teradata
   fully_qualified_name: database_name.schema_name.table_name
   
   # For Redshift (no database in FQN)
   fully_qualified_name: schema_name.table_name
   

   Copy

2. Check case sensitivity:

   tables:
     - fully_qualified_name: MyDatabase.MySchema.MyTable
       is_case_sensitive: true  # Match exact case
   

   Copy

3. Verify table exists in source database

Issue: “YAML formatting error”¶

Symptom:

Error in the format of config.yaml

Solutions:

1. Check indentation (use spaces, not tabs)

   # Correct
   tables:
     - fully_qualified_name: db.schema.table
       column_selection_list:
         - column1
         - column2
   

   Copy

   Incorrect example (mixed indentation with tabs):

   tables:
   	- fully_qualified_name: db.schema.table
   	  column_selection_list:
   	    - column1
   

   Copy

2. Quote special characters:

   password: "p@ssw0rd!"    # Quote passwords with special chars
   where_clause: "name = 'O''Brien'"  # Escape quotes
   

   Copy

3. Validate YAML syntax using online validators or:

   python -c "import yaml; yaml.safe_load(open('config.yaml'))"
   

   Copy

Issue: “Validation fails with tolerance errors”¶

Symptom:

Metrics validation failed: Difference exceeds tolerance

Solution: Adjust tolerance in configuration:

comparison_configuration:
  tolerance: 0.05  # Increase to 5% tolerance

Issue: “Out of memory errors with large tables”¶

Solutions:

1. Enable chunking:

   tables:
     - fully_qualified_name: large_table
       chunk_number: 100  # Process in smaller chunks
   

   Copy

2. Reduce thread count:

   max_threads: 4  # Use fewer threads
   

   Copy

3. Filter data:

   tables:
     - fully_qualified_name: large_table
       where_clause: "created_date >= '2024-01-01'"
   

   Copy

4. Exclude large columns:

   tables:
     - fully_qualified_name: large_table
       use_column_selection_as_exclude_list: true
       column_selection_list:
         - large_blob_column
         - large_text_column
   

   Copy