Databricks Migration Architect: Legacy EDW to Lakehouse

Scope legacy migrations with Lakebridge by looking below the line, mapping the submerged 20%—logic, orchestration, access, and behavior layers—where budgets fail and governance matters.

Turn 18 months of Oracle AWR data into a to-do list by ranking top SQL by total database time, mapping wait events, and migrating by pain to Lakeflow declarative pipelines.

Learn to profile across Teradata, Oracle, and SQL Server with a portable five-question checklist—frequency, cost, skew, staleness, and criticality—and normalize results into a single workload schema for lakehouse migration.

Build both a dependency graph and a usage heat map to drive migrating legacy edw to lakehouse, surface dynamic sql edges and hot core objects, and retire cold isolated ones.

Develop a defensible workload inventory as the source of truth for migration decisions, assigning verifiable verdicts (lift, redesign, retire) per object and tracing each cell to a rerunnable query.

Use a six-dimension migration scorecard—technical complexity, pl/sql depth, value, change frequency, data volume, and dependency fanout—to rate workloads 1–5 and decide re-host or re-architect, guided by a tco.

Present tco to the board with one slide showing three-year net savings against legacy costs and a lakehouse, using a concise narrative, a defensible arc, and an editable three-year model.

Configure a federated Databricks connection to Oracle and SQL Server with Unity Catalog, using a connection and foreign catalog, secret scopes, and read-only grants.

Analyze why federation incurs latency and cost and identify how pushdown, profiling, and delta strategies reduce cross-source data movement, especially for hot tables.

Master the data type mapping matrix for Oracle to Databricks migrations, mapping numbers with precision and scale to decimal, avoiding float, and normalizing timestamps to UTC.

Lakebridge, a free ai-powered Databricks migration tool, analyzes source DDL with the analyzer and converts Oracle DDL to Databricks sql with the converter, delivering about 80% auto-conversion.

Spot the silent precision-loss bug hiding in money columns typed as double, causing sums to diverge from rounding of 0.1 and 0.2. Cast to decimal(38,10), backfill during migration, reconcile totals.

Split period columns into start and end timestamps, convert latitude and longitude to doubles, replace the Teradata primary index with liquid clustering, and audit with drift diff for exact parity.

Switch from index reflex to delta data skipping, using per-file min/max statistics, clustering, and compaction to prune files and read the query profile for fast queries.

Choose cluster keys from query history, using system.query.history to identify the most frequent filters, rank by frequency and cardinality, cap at four, and document the evidence.

Diagnose why over-clustering incurs compute costs in a lakehouse migration and apply remediation to drop clustering on small, static, or mismatched-key tables for lower spend and pruning gains.

Treat a procedure as a process with state, not a single query. Map explicit cursors, multi-statement transactions, session scope temp tables, and procedural control flow to spark patterns via autopsy.

Use the autopsy method to migrate: color-code lines into green data movement, amber logic, grey control, then extract amber as the migration scope and migrate in spark to the lakehouse.

Reconstruct the hidden execution graph from cascading triggers by mapping trigger bodies to a directed graph of table updates, then topologically sort for a safe, explicit Lakeflow declarative pipeline.

Translate intent, not the loop, to transform cursor-based Oracle logic into parallel, set-based Spark operations using window functions, enabling one atomic write and sub-4-minute lakehouse migrations.

Migrate the Oracle row-level trigger to a Delta change data feed and replace the DBMS_SCHEDULER with Lakeflow jobs, revealing dependencies, retries, and a visible audit trail.

learn to craft deterministic PL/SQL to PySpark translation prompts by defining four blocks: context, contract, constraints, and self-audit, pinning the model, and pasting real schema for reproducible migrations.

explore how swapping dense_rank for rank in a window function can silently drop rows, and learn to catch it with the invariant that max rank equals distinct keys.

Explore how semantic drift—grain drift, null drift, boundary drift—can make a migrated legacy EDW to lakehouse report look correct while hiding errors, and learn golden dataset diffing with full-result hashes.

Explore the transaction-semantics gap between Oracle's per-statement commits and Spark delta writes, showing why faithful transpilation fails and a redesign with a single atomic merge on a natural key.

Build a versioned prompt library in git, with yaml structure for production-ready prompts. Apply the six-point gate audit, including golden diff, to ensure zero missing or extra rows.

Frame data ingestion as a contract with three terms—schedule, capacity, and manifest—and implement a fail-fast guard that enforces these bounds at the door.

Compare jdbc bulk reads of a 10-terabyte table with auto loader on exported files, illustrating brittleness, no checkpoints, and the benefits of checkpointed, partitioned, incremental reads.

Compare vendor CDC tools like Fivetran, Qlik, and Arcion for log-based data capture and understand why buy versus build hinges on maintenance costs and available connectors.