The Agentic Cloud Data Migration Guide

2. The Incremental Strangler Pattern

"Big Bang" migrations—where you shut down the business on Friday and pray the new cloud DB works on Monday—are fundamentally reckless. The modern approach is the Strangler Fig Pattern, running systems in parallel and relying on continuous agentic verification.

3. Why ELT Defeats ETL

For a decade, the industry standard was Extract, Transform, Load (ETL). Data engineers wrote complex Python, Spark, or Java pipelines that extracted data, transformed it in memory, and loaded it. This is an anti-pattern in 2026. We now use ELT (Extract, Load, Transform).

4. Append-Only Architectures & Soft Deletion

During migration, data state is fluid. Never execute a DELETE or an in-place UPDATE while a pipeline is running. If an update corrupts data, you lose the historical state and must restart the pipeline from scratch.

-- Legacy ETL: Destructive
DELETE FROM user_roles WHERE user_id = '123';

-- Modern ELT: Append-only deprecation
INSERT INTO user_roles_history (user_id, role, status, updated_at)
VALUES ('123', 'admin', 'DEPRECATED', CURRENT_TIMESTAMP());

5. Hybrid Networking

Migrating petabytes over the public internet via VPNs guarantees latency spikes and massive egress fees. Enterprise networking features bypass the public internet entirely.

6. CDC & Import Services

Batch uploads are obsolete. Continuous replication via Change Data Capture (CDC) reads directly from the legacy database's transaction logs (e.g., Postgres WAL, Oracle Redo logs) with near-zero performance impact.

7. Cloud Migration Pipelines

Below are the canonical architectures for moving data from on-premise transactional systems into cloud analytical and operational stores.

8. The Staging Strategy (Raw String Tables)

To facilitate ELT, land your data into "Bronze" tables where every column is a string. If you try to cast a date during network transit and the legacy DB outputted "99/99/9999", the entire batch fails. Copying string data will NEVER fail.

-- 1. Land into Bronze (String Types)
CREATE TABLE stg_legacy_users (
  id VARCHAR, full_name VARCHAR, birth_date VARCHAR
);
COPY INTO stg_legacy_users FROM @s3_migration_stage;

-- 2. Transform into Silver (Inside the DW safely)
INSERT INTO silver_users
SELECT
  CAST(id AS BIGINT),
  TRIM(full_name),
  TRY_CAST(birth_date AS DATE) -- Yields NULL instead of crashing
FROM stg_legacy_users;

9. Profiling & Data Integrity Verification

A successful migration requires mathematical proof of parity. However, doing a single MD5 hash of an entire table is useless for debugging. When a table hash fails, you must know which row and which column drifted.

-- Agent runs this on both DBs, then outer joins the results
SELECT 
  user_id AS primary_key,
  MD5(
    COALESCE(full_name::text, '') || '|' || 
    COALESCE(email::text, '') || '|' || 
    COALESCE(status::text, '')
  ) AS row_checksum
FROM users;

SELECT 
  COUNT(*) AS total_rows,
  
  -- Check column distinctness
  COUNT(DISTINCT department_id) AS distinct_depts,
  
  -- Catch floating point or precision truncation issues
  SUM(account_balance) AS total_balance_checksum,
  
  -- Catch data loss (empty strings vs actual NULLs)
  SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_emails,
  SUM(CASE WHEN email = '' THEN 1 ELSE 0 END) AS empty_emails
FROM users;

10. The Schema Minefield (Dialect Differences)

SQL is not universally standard. Moving from Oracle to Postgres to Snowflake introduces subtle dialect differences that will silently break application logic if not handled during migration.

• 1. NULLS FIRST vs NULLS LAST In an ORDER BY col DESC, Postgres and Snowflake put NULLs at the top. Oracle puts them at the bottom. Always explicitly declare ORDER BY col DESC NULLS LAST in your migration views.
• 2. Array Support & Junction Tables Traditional relational databases use mapping tables for many-to-many relationships. Modern DWs like BigQuery strongly prefer nested REPEATED arrays. AI agents must denormalize these during the Silver layer phase.
• 3. Enforcing UTC & UTF-8 Legacy systems often store dates as TIMESTAMP WITHOUT TIME ZONE, assuming local server time. The cloud requires UTC. Cast all legacy times to TIMESTAMP WITH TIME ZONE at offset. Similarly, force UTF-8 encoding to avoid silent corruption from WIN1252/Latin1 text.
• 4. Hard Limits & Float Precision Postgres limits tables to ~1600 columns. BigQuery maxes out at 10,000. Oracle's NUMBER does not map cleanly to standard FLOAT. Always map to strict DECIMAL(p,s) for financial data to prevent rounding differences.

11. GitOps & Change Management

Never execute a migration DDL command directly via a console. Use declarative GitOps tools. AI Agents generate the migration scripts, commit them to a Git repository, and CI/CD pipelines apply the changes.

12. Ecosystem Comparison: DW, Graph, and OLTP

13. Queues, PubSub & Orchestration