Cloud Migration Testing: What AWS, Azure, and GCP Teams Get Wrong
The project was six months in the making. Three teams involved. A hard cutover date. And by the end of go-live week, the revenue reports in the new cloud environment were off by 23% compared to the legacy system.
The row counts matched. The checksums passed. The migration was, by every standard metric, successful.
The problem was a timestamp timezone mismatch that only appeared in one geographic market, a Parquet schema evolution issue that introduced silent NULLs in three fact tables, and an ADLS encoding bug that corrupted accented characters in customer names. None of these were caught by the testing framework the team had in place.
This is the real failure mode in cloud migration testing. It is not that teams don't test. It is that the tests they run are platform-agnostic when the bugs are platform-specific.
AWS, Azure, and GCP each have their own data type handling, encoding defaults, null semantics, and schema evolution behaviours. A testing checklist that works perfectly for an on-prem-to-
Redshift migration will miss entirely different classes of bugs in an on-prem-to-BigQuery migration.
In this guide, we break down:
Why standard cloud migration testing tools miss platform-specific failure modes
The gotchas specific to AWS (S3, Redshift, Glue, Athena)
The gotchas specific to Azure (ADLS Gen2, Synapse Analytics, Azure Data Factory)
The gotchas specific to GCP (BigQuery ARRAY/STRUCT, case sensitivity, partition expiry)
The cross-platform validation checks that should be in every migration test suite, regardless of target cloud
All patterns include the specific queries and checks you need to catch these issues before they reach production.
Why Standard Cloud Migration Testing Fails
Most migration testing frameworks are built around a simple premise: if the row counts match and the checksums agree, the data moved correctly.
This works when you are moving data between systems with identical semantics. It breaks when you are not.
AWS, Azure, and GCP each have their own opinions about data types, encoding, null handling, timezone storage, and schema evolution. When you copy data from an on-premises Oracle warehouse into BigQuery, you are not just moving bytes. You are translating between two systems with fundamentally different assumptions about what those bytes mean.
The standard checklist misses this translation layer entirely.
What standard testing catches:
Row count differences (a table with 10M rows should still have 10M rows)
Checksum mismatches on aggregated numeric columns
Missing tables or columns at the schema level
Obvious type incompatibilities that cause hard errors during load
What standard testing misses:
Silent type coercions that change values without raising errors
Platform-specific null handling that converts empty strings to NULL (or vice versa)
Timezone offset shifts that make timestamps appear correct in aggregate but wrong per-record
Encoding corruption that only surfaces in non-ASCII characters
Schema evolution behaviours that introduce new NULL columns without failing the pipeline
Partition pruning semantics that silently exclude recent data from query results
The frustrating part is that these failures often pass every standard test. The row count is right.
The SUM of numeric columns matches. The job exits with code 0. The data is still wrong.
Platform-specific testing fills this gap. Instead of asking "did the data move?", it asks "did the data move with the same meaning it had before?"
The sections below break down the specific failure patterns for each major cloud platform, and the queries you need to catch them.
AWS-Specific Gotchas: S3, Glue, Redshift, and Athena
1. S3 Has No Native Type Enforcement
S3 is object storage. It holds files. It does not care what is inside them.
When you land Parquet files in S3, the type metadata lives inside the Parquet file footer, not in S3 itself. If a source system changes a column from INT64 to STRING and continues writing files to the same S3 prefix, the new files will happily coexist with the old ones. Athena and Glue will read both. You will not get an error. You will get mixed types that cause silent cast failures downstream.
Detection query (Athena):
-- Check for type inconsistencies across Parquet files in the same prefix
SELECT
"$path" AS file_path,
typeof(order_id) AS order_id_type,
COUNT(*) AS row_count
FROM your_database.your_table
GROUP BY 1, 2
HAVING COUNT(DISTINCT typeof(order_id)) OVER () > 1;If this returns more than one row per column, your schema has drifted across files.
2. Glue Catalog Does Not Auto-Update on Parquet Schema Evolution
When Parquet files gain new columns, Glue does not automatically reflect the new schema in its catalog. The old column definition stays. Athena queries return NULL for the new columns. No error is raised.
Run Glue crawlers on a schedule that matches your ingestion frequency, and add schema change notifications:
import boto3
glue = boto3.client('glue')
def check_glue_schema_drift(database: str, table: str) -> dict:
"""Compare Glue catalog schema against latest Parquet file footer."""
response = glue.get_table(DatabaseName=database, Name=table)
catalog_columns = {
col['Name']: col['Type']
for col in response['Table']['StorageDescriptor']['Columns']
}
# Get location and check actual Parquet schema via pyarrow
location = response['Table']['StorageDescriptor']['Location']
# ... read Parquet footer from S3 and compare
return {"catalog": catalog_columns, "actual": actual_columns}3. Redshift DISTKEY and SORTKEY Affect Query Results Silently
Redshift is a distributed database. Data is physically distributed across nodes based on the DISTKEY. When you migrate tables and forget to carry over distribution and sort keys, query performance degrades — but more importantly, window functions and ORDER BY queries that relied on data locality can return different row orderings.
This does not break correctness for most aggregations, but it breaks anything relying on deterministic row order: row_number(), lead/lag functions, and any downstream process that expects consistent ordering.
Migration checklist query:
-- Verify distribution keys are preserved after migration
SELECT
tablename,
distkey,
sortkey1,
sortkey1enc
FROM pg_table_def
WHERE schemaname = 'public'
AND tablename IN ('orders', 'transactions', 'events')
ORDER BY tablename;4. TIMESTAMPTZ Timezone Handling
Redshift stores TIMESTAMPTZ values in UTC internally. When you query them, it converts to the session timezone. On-premises databases often store timestamps in local time without a timezone offset.
The migration risk: a timestamp stored as 2024-01-15 09:00:00 in a local-time system becomes 2024-01-15 09:00:00 UTC in Redshift. In New York (UTC-5 in winter), that timestamp is now 5 hours off.
Detection query:
-- Check for timezone offset in migrated timestamp columns
SELECT
COUNT(*) AS total_rows,
SUM(CASE
WHEN DATE_PART('hour', event_timestamp AT TIME ZONE 'UTC') !=
DATE_PART('hour', event_timestamp AT TIME ZONE 'America/New_York')
THEN 1 ELSE 0
END) AS rows_with_offset_mismatch
FROM events
WHERE event_timestamp >= CURRENT_DATE - 7;If rows_with_offset_mismatch is non-zero, you have a timezone handling issue to resolve before cutover.
Azure-Specific Gotchas: ADLS Gen2, Synapse, and Azure Data Factory
1. ADLS Gen2 Encoding Defaults
Azure Data Lake Storage Gen2 is encoding-agnostic at the storage layer. Files are bytes. The encoding is whatever the writer used.
The problem: on-premises systems running on Windows often write CSV files in Windows-1252 (also called Latin-1). ADLS Gen2 has no mechanism to enforce or validate encoding on ingest. When downstream tools read these files assuming UTF-8 — which is the default for most modern data tools — characters outside the basic ASCII range get corrupted silently.
This hits customer names, addresses, product descriptions, and any field containing accented characters, special symbols, or non-Latin scripts. The corruption is not always obvious: é becomes �, or the character is dropped entirely, depending on the reader.
Detection query (after loading to Synapse):
-- Find rows with replacement characters (encoding corruption indicator)
SELECT
COUNT(*) AS total_rows,
SUM(CASE
WHEN customer_name LIKE '%�%'
OR customer_name != TRY_CONVERT(NVARCHAR(500),
CONVERT(VARBINARY(MAX), customer_name), 65001)
THEN 1 ELSE 0
END) AS corrupted_rows,
CAST(
SUM(CASE WHEN customer_name LIKE '%�%' THEN 1 ELSE 0 END) * 100.0
/ COUNT(*) AS DECIMAL(5,2)
) AS corruption_rate_pct
FROM staging.customers;Run this on your staging table before promoting to production. A non-zero corruption_rate_pct means your source encoding does not match your reader's assumption.
Fix: Explicitly specify encoding in your ingestion pipeline. In Azure Data Factory, set encodingName in the dataset definition. For Databricks, use spark.read.option("encoding", "windows-1252").
2. Synapse Analytics PolyBase Column Delimiter Mismatches
Synapse PolyBase is fast for bulk loads. It is also rigid. If your CSV files use a delimiter that does not match the FIELDTERMINATOR in the EXTERNAL TABLE definition, PolyBase does not fail gracefully. It reads each row as a single column, produces wildly wrong column counts, and either fails on column count mismatch or silently loads malformed data depending on your REJECT_TYPE setting.
The most common mismatch: pipe-delimited files (|) from legacy systems loaded with a comma FIELDTERMINATOR.
Validation before load:
import csv
from collections import Counter
def detect_delimiter(file_sample: str, candidates=(',', '|', '\t', ';')) -> str:
"""Detect the most likely delimiter from a file sample."""
counts = {}
for delim in candidates:
reader = csv.reader(file_sample.splitlines(), delimiter=delim)
column_counts = [len(row) for row in reader]
# Consistent column count = likely delimiter
most_common = Counter(column_counts).most_common(1)[0]
counts[delim] = (most_common[1], most_common[0]) # (consistency, col_count)
return max(counts, key=lambda d: counts[d][0])Run this on a sample of each source file before building your PolyBase external table definition.
3. Azure Data Factory Empty String to NULL Conversion
ADF has a setting called "Treat empty as null" in its data flow transformations. It is off by default, but many teams enable it because it "cleans up" the data.
The problem: this converts every empty string — '' — to NULL. For fields where empty string and NULL have different business meanings, this is a data corruption event, not a cleanup.
A phone number field with '' means the customer left it blank. A phone number field with NULL might mean the field was not shown to the customer at all. After ADF transforms them both to NULL, that distinction is gone.
Detection query (compare source vs. target):
-- Compare empty string vs. NULL counts between source and target
SELECT
'source' AS layer,
SUM(CASE WHEN phone = '' THEN 1 ELSE 0 END) AS empty_string_count,
SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS null_count
FROM source_db.customers
UNION ALL
SELECT
'target',
SUM(CASE WHEN phone = '' THEN 1 ELSE 0 END),
SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END)
FROM synapse_db.customers;If the source has 5,000 empty strings and the target has 0 empty strings but 5,000 more NULLs, ADF converted them.
4. Hierarchical Namespace Path Resolution
ADLS Gen2 supports two modes: flat namespace (like a blob store) and hierarchical namespace (like a real file system with true directory semantics). When you enable hierarchical namespace, path resolution changes.
In flat namespace, a path like container/2024/01/data.parquet is just a blob name with slashes in it. In hierarchical namespace, it is a real directory tree. Rename operations, permission inheritance, and atomic directory moves behave differently in each mode.
Teams that build ingestion pipelines against flat namespace and then migrate to ADLS Gen2 with hierarchical namespace enabled sometimes find that directory-level rename operations (used for atomic partition commits) behave unexpectedly or require different API calls.
Verify your ingestion pattern against the target namespace mode before migration cutover.
GCP and BigQuery Gotchas
1. ARRAY and STRUCT Flattening from JSON Sources
BigQuery supports nested and repeated fields natively. JSON sources with nested objects map cleanly to STRUCT types. JSON sources with arrays map to ARRAY types. This is one of BigQuery's genuine advantages over traditional columnar databases.
The migration risk is the reverse direction: when you have ARRAY or STRUCT columns in BigQuery and need to validate them against a flat relational source, or when your ingestion pipeline flattens JSON into separate rows instead of using BigQuery's native nested types.
Flattening an ARRAY creates one row per element. A customer with 3 orders becomes 3 rows. The row count is now 3x higher than the source, and this is correct — but it looks like data inflation to any tool running a simple row-count comparison.
Detection query:
-- Validate row count after ARRAY flattening
WITH
source_counts AS (
SELECT customer_id, ARRAY_LENGTH(order_history) AS order_count
FROM `project.dataset.customers`
)
SELECT
COUNT(*) AS customer_count,
SUM(order_count) AS expected_flattened_rows
FROM source_counts;
-- Compare against flattened table
SELECT COUNT(*) AS actual_flattened_rows
FROM `project.dataset.customer_orders_flat`;Expected flattened rows should equal actual. If they diverge, the flattening logic dropped or duplicated records.
2. Case-Sensitive String Comparisons
BigQuery string comparisons are case-sensitive by default. Most traditional databases — SQL Server, MySQL, Oracle with default collation — are case-insensitive.
This breaks JOIN conditions silently. A JOIN on customer_id where one side has CUST-001 and the other has cust-001 will produce zero matches in BigQuery. The same JOIN on SQL Server would return results.
After migration, row counts in joined tables drop. The rows are not missing. They just do not match anymore.
Detection query:
-- Find join keys that only match with case-insensitive comparison
SELECT
COUNT(*) AS case_sensitive_matches,
(
SELECT COUNT(*)
FROM `project.dataset.orders` o
JOIN `project.dataset.customers` c
ON LOWER(o.customer_id) = LOWER(c.customer_id)
) AS case_insensitive_matches
FROM `project.dataset.orders` o
JOIN `project.dataset.customers` c
ON o.customer_id = c.customer_id;If the two counts differ, you have case mismatches in your join keys that were previously hidden by case-insensitive collation.
3. DATE vs. DATETIME vs. TIMESTAMP Timezone Behaviour
BigQuery has three distinct temporal types with different timezone semantics:
DATE: No timezone. Just a calendar date. 2024-01-15.
DATETIME: No timezone. A local datetime with no offset. 2024-01-15 09:00:00.
TIMESTAMP: UTC. Always. Stored as microseconds since Unix epoch, displayed in UTC or a specified timezone.
The migration risk: on-premises systems often store timestamps as local-time datetimes without explicit timezone offsets. When you map these to TIMESTAMP in BigQuery, the values are interpreted as UTC. A 09:00:00 local time in Sydney (UTC+11) becomes 09:00:00 UTC, which is 20:00:00 Sydney time the previous day.
For time-series analysis, event attribution, and SLA calculations, this is a significant error.
Detection query:
-- Compare event hour distribution before and after migration
-- If timezone handling is correct, the distribution should match
SELECT
EXTRACT(HOUR FROM event_timestamp AT TIME ZONE 'Australia/Sydney') AS hour_sydney,
COUNT(*) AS event_count
FROM `project.dataset.events`
WHERE DATE(event_timestamp) = '2024-01-15'
GROUP BY 1
ORDER BY 1;Compare this against the same query on your source system. If the hour distribution has shifted by a fixed offset, you have a timezone handling mismatch.
4. Partition Expiry Silently Drops Data
BigQuery tables can have partition expiry configured at the table level. When a partition expires, BigQuery deletes it. No error. No warning. The data is gone.
This is a useful feature for managing storage costs on high-volume event tables. It becomes a migration risk when:
You migrate a table without checking if partition expiry is configured on the target
The expiry window is shorter than the historical data range you need
You discover weeks later that your historical data has been silently deleted
Detection query:
-- Check partition expiry settings on target tables
SELECT
table_name,
partition_expiration_days,
CASE
WHEN partition_expiration_days IS NULL THEN 'No expiry'
WHEN partition_expiration_days < 90 THEN 'WARNING: Short expiry'
ELSE 'OK'
END AS expiry_status
FROM `project.dataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE option_name = 'partition_expiration_days'
OR table_name IN (
SELECT table_name
FROM `project.dataset.INFORMATION_SCHEMA.PARTITIONS`
GROUP BY 1
)
ORDER BY table_name;Run this immediately after migration setup and before loading historical data. If any critical table has an expiry window shorter than your retention requirement, fix it before data loads.
Cross-Platform Checks That Standard Tools Miss
Beyond platform-specific gotchas, there are categories of validation that matter on every cloud migration and that most standard testing frameworks do not cover.
Referential Integrity
Relational databases enforce foreign key constraints. Cloud data warehouses generally do not. BigQuery, Redshift, and Synapse all allow you to define foreign keys, but they do not enforce them at write time. You can insert an orders row with a customer_id that does not exist in the customers table, and the warehouse will not complain.
This means orphaned records accumulate silently. They only surface when a JOIN fails to return expected results or when downstream reports show fewer matched records than they should.
-- Check for orphaned records after migration
SELECT
COUNT(*) AS orphaned_orders
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;Run this for every foreign key relationship in your source schema. Any non-zero result is an integrity gap.
NULL vs. Empty String Semantics
Different source systems have different conventions for representing missing data. Oracle treats empty string and NULL as the same thing (empty string is NULL in Oracle). MySQL, SQL Server, and PostgreSQL treat them differently. Cloud warehouses follow the SQL standard: NULL is unknown, empty string is a known empty value.
When you migrate from Oracle to any cloud warehouse, every field that was empty string in Oracle was actually NULL. When you migrate from SQL Server to Oracle, empty strings become NULLs. These conversions happen silently.
-- Compare NULL and empty string counts between source and target
SELECT
column_name,
SUM(CASE WHEN col_value IS NULL THEN 1 ELSE 0 END) AS null_count,
SUM(CASE WHEN col_value = '' THEN 1 ELSE 0 END) AS empty_string_count,
COUNT(*) AS total_rows
FROM (
SELECT 'email' AS column_name, email AS col_value FROM customers
UNION ALL
SELECT 'phone', phone FROM customers
UNION ALL
SELECT 'address_line2', address_line2 FROM customers
)
GROUP BY column_name;Compare the output against the same query on your source. If NULL count increases and empty string count decreases by the same amount, the conversion happened.
Float Precision Drift
Floating-point arithmetic is not associative. (a + b) + c does not always equal a + (b + c) in floating point. When you change the execution engine — from on-premises SQL Server to BigQuery, for example — the order of operations in aggregate functions may change, producing slightly different results for FLOAT and DOUBLE columns.
This is usually a sub-penny difference on financial figures, but it is enough to fail a strict checksum comparison. More importantly, it can accumulate into meaningful differences at scale.
-- Compare aggregated float columns with a tolerance threshold
SELECT
ABS(source_total - target_total) AS absolute_diff,
ABS(source_total - target_total) / NULLIF(source_total, 0) AS relative_diff,
CASE
WHEN ABS(source_total - target_total) / NULLIF(source_total, 0) > 0.0001
THEN 'FAIL'
ELSE 'PASS'
END AS precision_check
FROM (
SELECT
(SELECT SUM(amount) FROM source_db.transactions) AS source_total,
(SELECT SUM(amount) FROM target_db.transactions) AS target_total
) t;A relative difference above 0.01% (0.0001) on financial aggregates warrants investigation. Below that threshold, it is likely floating-point arithmetic variation rather than a data error.
Sequence Gaps After Migration
Auto-incrementing primary keys often reset or skip values during migration. The target table starts at 1. The source table had IDs 1 through 10 million. After migration, both look right in isolation, but any system that hardcodes or caches IDs from the old system now points to the wrong records.
-- Check for unexpected gaps in a sequence column
SELECT
MIN(id) AS min_id,
MAX(id) AS max_id,
COUNT(*) AS row_count,
MAX(id) - MIN(id) + 1 AS expected_count_if_sequential,
MAX(id) - MIN(id) + 1 - COUNT(*) AS gap_count
FROM orders;If gap_count is significantly higher than expected, investigate whether IDs were reassigned during migration or whether records are missing.
Partition Alignment Between Source and Target
When migrating time-partitioned tables, verify that partition boundaries match. A source table partitioned by MONTH migrated to a target partitioned by DAY will have identical data but different query performance characteristics. More critically, if partition boundaries use different timezone assumptions, the same records can land in different partitions on source and target.
-- Validate daily record counts across partition boundaries
SELECT
partition_date,
source_count,
target_count,
source_count - target_count AS diff
FROM (
SELECT DATE(event_timestamp) AS partition_date, COUNT(*) AS source_count
FROM source_db.events
GROUP BY 1
) s
FULL OUTER JOIN (
SELECT DATE(event_timestamp) AS partition_date, COUNT(*) AS target_count
FROM target_db.events
GROUP BY 1
) t USING (partition_date)
WHERE ABS(COALESCE(source_count, 0) - COALESCE(target_count, 0)) > 0
ORDER BY partition_date;Any partition with a non-zero diff points to either a timezone handling issue or missing records in that date range.
Build a Migration Test Suite That Catches What Standard Tools Miss
Cloud migration testing fails when the test suite treats the target platform as a neutral destination. AWS, Azure, and GCP each make specific decisions about encoding, type handling, null semantics, timezone storage, and schema evolution. Those decisions change the meaning of your data even when the bytes move correctly.
A migration test suite that catches real failures needs to be platform-aware:
For AWS: Validate Glue catalog schema against actual Parquet file footers. Check TIMESTAMPTZ offset handling before cutover. Verify DISTKEY and SORTKEY are preserved on Redshift tables.
For Azure: Detect encoding mismatches in ADLS Gen2 source files before load. Validate CSV delimiters before PolyBase table creation. Check ADF empty string to NULL conversion on every nullable column.
For GCP: Validate row counts after ARRAY flattening. Test JOIN conditions with case-normalized keys before switching from case-insensitive source systems. Audit partition expiry settings before loading historical data.
Cross-platform: Test referential integrity with explicit orphan checks. Compare NULL and empty string counts between source and target. Validate float precision with tolerance thresholds, not strict equality. Check partition alignment at daily boundaries.
The queries in this guide are the starting point. Add them to your migration runbook as pre-cutover gates, not post-hoc audits. A failure caught in staging is a migration success. A failure caught in production is a six-month cleanup project.
If you want to automate these checks across your pipeline runs — not just at migration time, but as ongoing data quality validation — Vexdata runs automated validation on your data pipelines and flags anomalies before they reach reporting. See the cloud migration testing page for how teams use Vexdata during and after cloud migrations.
Comments