Jaco van der Laan

Metadata-Driven SQL Optimization and Migration

12/10/2025  ·  Data Engineering  ·  SQL Code Generation

‹ ›

How to use metadata and rules to automate SQL refactoring across data platforms

Summary

Manual SQL optimization and migration are slow, inconsistent, and error-prone. By encoding SQL transformation patterns and optimization heuristics as metadata-driven rules, we can automatically refactor queries — from Oracle to Databricks, from inefficient to optimized — while ensuring semantic equivalence through automated testing.

This article outlines a metadata-centric approach to SQL optimization and migration, linking it directly to the semantic validation framework introduced in the previous article.

The problem: fragile and manual SQL migrations

Every organization that has evolved its data platform knows this pain:
thousands of analytical queries, built over years, must be migrated or optimized for a new engine.

  • Oracle to Databricks
  • Teradata to Snowflake
  • SQL Server to BigQuery

Each platform has its own dialect, optimizer behavior, and best practices.
Traditional migrations involve search–replace scripts, manual tweaks, and hope. The result? Inconsistent performance and broken logic hidden in thousands of files.

What we need is a systematic, metadata-driven approach that turns these transformations into reusable knowledge.

From SQL text to structured metadata

The first step is to stop thinking of SQL as text and start treating it as metadata.

When parsed, a query can be represented as:

  • a logical tree (SELECT → JOIN → FILTER → AGGREGATE)
  • with annotated attributes (columns, functions, filters, joins)
  • and relationships between entities (tables, aliases, expressions)

This structural representation makes SQL machine-understandable.
Once stored in a metadata model (e.g., in DuckDB or a YAML structure), it becomes possible to apply rules like:

  • Replace correlated subqueries with JOINs
  • Inline unnecessary CTEs
  • Push filters down before joins
  • Convert implicit joins to ANSI syntax
  • Use Databricks functions for date/time logic

Defining optimization rules as metadata

Optimization patterns can be expressed as rules in a metadata schema.

For example:

Press enter or click to view image in full size

Each rule contains:

  • pattern definition (regex or AST expression)
  • transformation logic
  • target dialect
  • severity / recommendation level
  • documentation link

The same metadata can drive static analysis, migration refactoring, and linting in VSCode.

Automated optimization pipeline

A metadata-driven SQL optimizer follows this lifecycle:

Parse & register SQL metadata

  • Extract structure, dependencies, and lineage.
  • Store in a metadata repository (e.g., DuckDB or a YAML-based catalog).

Apply rule-based transformations

  • Match optimization and migration patterns.
  • Rewrite query trees with consistent mappings.

Regenerate optimized SQL

  • Reconstruct text from transformed metadata.
  • Include versioning and lineage tags.

Validate equivalence

  • Run both queries on the same dataset.
  • Use the equivalence-testing framework from the previous article to confirm identical results.

Publish metrics

  • Performance gain (runtime, cost).
  • Rule coverage and confidence scores.

Example: from Oracle to Databricks

Input SQL (Oracle-style)

SELECT
NVL(a.amount, 0) + NVL(b.amount, 0) AS total,
SYSDATE AS load_ts
FROM orders a, customers b
WHERE a.customer_id = b.id(+);

Optimized Metadata Rules Applied

Output SQL (Databricks)

SELECT
COALESCE(a.amount, 0) + COALESCE(b.amount, 0) AS total,
CURRENT_TIMESTAMP() AS load_ts
FROM orders a
LEFT JOIN customers b ON a.customer_id = b.id;

Then, your unit test for equivalence (as described in the previous article) confirms identical results.

Metadata as the source of truth

The strength of this approach is that metadata becomes the foundation for:

  • SQL generation (from logical models)
  • rule-based refactoring
  • impact analysis and lineage
  • semantic validation
  • quality scoring

This unlocks what we might call Semantic DevOps for Data — an environment where SQL logic evolves safely, automatically, and intelligently.

Integrating GenAI in the loop

GenAI can amplify this process by:

  • Proposing new optimization rules by analyzing past diffs and execution plans.
  • Explaining the rationale for a transformation in plain English.
  • Suggesting test datasets to verify tricky cases (e.g., NULL-handling).
  • Grouping patterns of anti-patterns found in the existing codebase.

This turns the optimizer into a learning system — one that improves with every migration.

Key Takeaways

✅ Store SQL as structured metadata, not raw text.
✅ Define cross-platform rules as declarative patterns.
✅ Automate transformations and regenerations through a pipeline.
✅ Validate equivalence automatically before deployment.
✅ Use GenAI to evolve and explain your rule base.

Why this matters

For large-scale migrations and refactoring programs, this methodology transforms SQL codebases into manageable knowledge graphs — searchable, traceable, and optimizable by design.
It removes the fear of changing SQL and replaces it with confidence through metadata.

You don’t just migrate faster — you migrate smarter.

🧩 Related articles