From Legacy SQL to Business-Friendly Mapping (Part 1)- Parsing Queries and Extracting Metadata

The start of a practical series on migrating old SQL code into modularized, business-friendly data transformations.

Why Legacy SQL Migration Matters

If you’ve worked in data engineering for more than a few years, you know the reality: enterprises are full of legacy SQL. Complex, deeply nested queries built over decades. Business logic buried inside layers of joins, unions, and transformations.

The problem? These queries are:

• Opaque: nobody really understands what they do anymore.
• Brittle: a single schema change can break entire workflows.
• Hard to test: no modularization, no clear lineage.

To move toward model-driven data engineering and business-friendly mapping, we need to bring these queries into a controlled, structured framework.

That’s what this series is about.

The Migration Journey

This series will walk through a step-by-step migration pipeline:

1. Parsing & Metadata Extraction — breaking queries into their components and extracting input/output metadata.
2. Modularization — splitting monolithic queries into logical blocks, much like dbt models.
3. Business-Friendly Mapping Transformation — converting SQL into higher-level YAML mappings that represent transformations in business terms.
4. Validation & Optimization — generating validation checklists and applying optimization rules for scalable, maintainable pipelines.

In this first article, we’ll focus on Step 1: Parsing and Extracting Metadata.

Step 1: Parsing Queries

The foundation of controlled migration is parsing. Instead of rewriting SQL by hand, we systematically analyze queries to:

• Identify input tables (sources).
• Identify output columns (targets).
• Capture transformations (joins, filters, aggregations).

This is not just technical parsing. It’s about preparing the query for migration rules and validation later in the process.

Example: Legacy Query

SELECT
    c.customer_id,
    c.name,
    SUM(o.amount) AS total_spent,
    CASE WHEN c.status = 'A' THEN 'Active' ELSE 'Inactive' END AS customer_status
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_date >= '2022-01-01'
GROUP BY c.customer_id, c.name, c.status;

From this query we can extract: – Inputs: customers, orders – Outputs: customer_id, name, total_spent, customer_status – Logic: filter on order date, aggregation on amount, business rule on status

Step 2: Extracting Metadata

Parsing alone isn’t enough. We also need metadata to guide migration:

• Source model metadata — what’s the schema of customers and orders?
• Target model metadata — how do these map to the business-oriented model?
• Business rules metadata — are there known mappings for status, definitions of “active”, or column lineage?

By combining query parsing with metadata lookup, we can generate:

• A migration checklist — ensuring input/output alignment.
• A ChatGPT prompt — guiding AI-assisted migration with context.

Generating a Migration Prompt

Imagine we’ve parsed the query above and enriched it with metadata. We can then generate a custom ChatGPT migration prompt like:

“Rewrite this SQL into modular blocks (dbt style). Sources: customers (with keys: customer_id, status) and orders (with keys: order_id, customer_id, order_date). Target columns: customer_id, name, total_spent, customer_status. Maintain the business rule that active = status A. Ensure modular CTEs for filtering, joining, and aggregation.”

This prompt is query-specific but systematic — ready for controlled AI assistance.

Step 3: Preparing the Validation Checklist

For every query migrated, we generate a validation checklist to ensure functional equivalence:

• Do input tables match?
• Do output columns match in name, type, and meaning?
• Are business rules preserved?
• Are aggregations and joins intact?

This checklist becomes the safety net for migration: nothing moves forward without verification.

Why This Matters for Model-Driven Data Engineering

This approach ensures we don’t just “translate SQL.” We bring queries into a governed, model-driven framework where:

• Metadata anchors transformations in business semantics.
• AI assistance is directed, not uncontrolled.
• Modularization lays the foundation for business-friendly mapping.

In short: it’s the bridge between legacy SQL sprawl and modern data engineering.

Coming Next

In the next article, we’ll cover how to modularize parsed SQL into logical blocks — bringing structure and readability to complex queries.