Jaco van der Laan

Reverse Engineering SQL into Functional Metadata Blocks

18/10/2025  ·  Data Engineering  ·  Metadata

‹ ›

From legacy SQL to declarative YAML: abstracting PIVOTs, NORMALIZE, and beyond into reusable metadata

When we reverse engineer existing SQL code into functional or logical metadata building blocks, we move from code-first to metadata-first thinking.
The goal is not to simply parse SQL, but to understand what it does — and then represent that behavior in a declarative, platform-independent metadata model that can later generate optimized SQL for different data platforms.

This article explores how we handle advanced SQL commands such as PIVOT, NORMALIZE, and UNION, and how we abstract them into functional metadata that can be used across any platform.

1. From Imperative SQL to Declarative Metadata

When reverse engineering, we often start with long, nested SQL scripts that describe how to compute something rather than what it represents.

In a model-driven approach, we break these scripts down into functional metadata blocks, such as:

  • select – column mapping
  • derive – column transformations
  • filter – WHERE logic
  • aggregate – grouping logic
  • union, pivot, or normalize – structural transformations

Each block is represented in YAML as declarative metadata, defining intent rather than implementation. When needed, these blocks are later recompiled into working SQL optimized for a specific data platform — whether Databricks, Snowflake, Teradata, or DuckDB.

2. Handling Complex SQL Constructs

PIVOT

A PIVOT statement is a good example of a SQL construct that cannot always be parsed by open-source SQL parsers like SQLGlot.
When reverse engineering such code, we first manually transform the PIVOT into an equivalent SELECT with MAX() and GROUP BY to make it syntactically standard and parsable.

– Original (unparsable) PIVOT
SELECT * FROM sales
PIVOT (
SUM(amount) FOR quarter IN (‘Q1’, ‘Q2’, ‘Q3’, ‘Q4’)
);

– Transformed (standard SQL)
SELECT
customer_id,
MAX(CASE WHEN quarter = ‘Q1’ THEN amount END) AS q1,
MAX(CASE WHEN quarter = ‘Q2’ THEN amount END) AS q2,
MAX(CASE WHEN quarter = ‘Q3’ THEN amount END) AS q3,
MAX(CASE WHEN quarter = ‘Q4’ THEN amount END) AS q4
FROM sales
GROUP BY customer_id;

Metadata YAML:

operation: pivot
group_by: [customer_id]
pivot_column: quarter
aggregations:
amount: MAX

When the SQL is generated again, the generator chooses the best native implementation — e.g., PIVOT syntax in Snowflake or an equivalent GROUP BY query in DuckDB.

NORMALIZE (Teradata)

NORMALIZE in Teradata is used to compress timelines, merging consecutive records with identical attribute values and continuous date ranges.

Most SQL engines don’t have this function, so we model it declaratively:

operation: normalize
group_by: [customer_id]
effective_date_column: valid_from
end_date_column: valid_to
attributes: [address, postal_code]

The SQL generator translates this metadata into LAG()/LEAD() logic or other windowed constructs as appropriate for the target platform.

UNION as Logical Supertype

A UNION operation is one of the simplest to model functionally.
We define that a logical supertype entity is composed of multiple subtype entities.

entity: party
union_of:
– person
– organization

This follows a supertype–subtype pattern, where:

  • Functional subtypes represent logical variations (e.g., PERSON and ORGANIZATION unioned into PARTY).
  • Source subtypes are our term for multiple source-specific entities representing the same concept in a harmonized layer.

The generator emits a UNION ALL across the subtype datasets, with minimal configuration required.

Functional Subtypes via Discriminator Columns

Sometimes we don’t union entities but split one base entity into multiple functional subtypes using a discriminator column.

entity: party
subtypes:
– name: person
discriminator:
column: party_type
value: ‘P’
– name: organization
discriminator:
column: party_type
value: ‘O’

At generation time, this translates into filtered queries on the base entity.

Aggregations

For aggregates, we only need to define:

  • Which columns form the key
  • How the others aggregate

operation: aggregate
primary_key: [customer_id]
aggregations:
revenue: SUM
last_purchase_date: MAX

This generates a standard GROUP BY query in any SQL dialect.

3. Other Advanced Constructs and Their Metadata Equivalents

While PIVOT, NORMALIZE, and UNION are frequent examples, many other SQL patterns can be modeled functionally and expressed declaratively in YAML.

Below are several real-world constructs we handle in the same way:

UNPIVOT

SQL:

SELECT customer_id, quarter, amount
FROM sales
UNPIVOT (amount FOR quarter IN (q1, q2, q3, q4));

Metadata:

operation: unpivot
key_columns: [customer_id]
value_column: amount
unpivot_columns: [q1, q2, q3, q4]
pivot_name: quarter

MERGE (SCD Upsert)

SQL:

MERGE INTO target t
USING source s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

Metadata:

operation: merge
target: target
source: source
keys: [id]
updates:
– column: value
expression: s.value
inserts:
– id: s.id
– value: s.value

WINDOW (Analytic Functions)

SQL:

SELECT
customer_id,
order_date,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

Metadata:

operation: derive
columns:
– name: running_total
expression: SUM(amount)
window:
partition_by: [customer_id]
order_by: [order_date]

EXPLODE / FLATTEN (Arrays or JSON)

SQL:

SELECT id, value
FROM source, LATERAL FLATTEN(input => json_array);

Metadata:

operation: explode
source_column: json_array
output_columns:
– name: value

DEDUPLICATE

SQL:

SELECT
FROM (
SELECT , ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY update_ts DESC) AS rn
FROM customers
) WHERE rn = 1;

Metadata:

operation: deduplicate
keys: [customer_id]
order_by: [update_ts DESC]
keep: first

HIERARCHY / RECURSIVE

SQL:

WITH RECURSIVE hierarchy AS (
SELECT id, parent_id, 0 AS level FROM org WHERE parent_id IS NULL
UNION ALL
SELECT o.id, o.parent_id, h.level + 1
FROM org o JOIN hierarchy h ON o.parent_id = h.id
)
SELECT * FROM hierarchy;

Metadata:

operation: hierarchy
entity: org
parent_key: parent_id
child_key: id
output_columns: [level]

CASE Logic

SQL:

SELECT
CASE
WHEN score >= 90 THEN ‘A’
WHEN score >= 75 THEN ‘B’
ELSE ‘C’
END AS grade
FROM exams;

Metadata:

operation: derive
columns:
– name: grade
cases:
– when: score >= 90
then: ‘A’
– when: score >= 75
then: ‘B’
– else: ‘C’

QUALIFY (Functional Filtering on Window Results)

Instead of describing the SQL syntax, we capture the intent of a post-window filter:

operation: filter
applied_after: derive
criteria:
– description: Keep the latest order per customer
based_on:
function: ROW_NUMBER
partition_by: [customer_id]
order_by: [order_date DESC]
keep: first

How it works

  • description documents business intent.
  • based_on describes analytic context, not SQL code.
  • keep: first indicates which rows to retain.

The SQL generator then decides whether to use QUALIFY (Snowflake) or emulate it with a subquery (Databricks, DuckDB).

SET DIFFERENCE (EXCEPT / MINUS)

SQL:

SELECT * FROM source_a
EXCEPT
SELECT * FROM source_b;

Metadata:

operation: set_difference
left: source_a
right: source_b
mode: except

Summary Table

4. Why This Matters

By abstracting SQL behavior into metadata:

  • We can generate optimized SQL for any platform.
  • We achieve traceable lineage between entities and attributes.
  • We decouple business logic from implementation details.

In short, this approach turns legacy SQL code into executable design — a foundation for metadata-driven, model-based automation that is easier to maintain, document, and evolve.