Migrating 300 processes by hand isn't slow because of volume. It's slow because of hidden order.
Clínica Alemana's clinical-studies reporting ran on ~300 SAS Enterprise Guide
processes (.egp files). Each one produces the gestion tables that
feed clinical studies — and each one is a graph of dozens of interdependent steps, with a
hard deadline to reach BigQuery by 31 May 2027.
The trap is subtle. When you open an .egp, the steps appear in a
visual order. But that order is not the execution order.
In one real process, 24 of 32 nodes were out of sequence — a step
reading a WORK table that a later step hadn't created yet. Translate in
visual order and the SQL fails, or worse, runs and produces silently wrong data.
With a 2–3 person team, doing this by hand 300 times wasn't an option. The system had to resolve the order, flag what needs human judgement, and keep every translation consistent — so the migration could scale without losing control.
A developer opens each .egp, reverse-engineers the dependency graph by
hand, guesses the order, and translates. The most expensive error —
migrating wrong in silence — only surfaces weeks later in a dual-run
mismatch nobody can explain.
The engine resolves the true execution order deterministically, marks every risky pattern before translation, and hands the AI a process-specific prompt plus a shared master standard. The developer validates — they don't reverse-engineer.
A six-step pipeline. Each step consumes what the previous one produced.
The system is a Python pipeline of six ordered steps, plus two GUI apps. The order matters: extraction feeds lineage, lineage feeds governance, and the final step — the core — reorders each process and prepares the AI material. Nothing in the chain depends on the AI for correctness; the AI only translates what the deterministic code has already resolved.
stable IDs · folders
dependencies · waves
Excel · dictionary
sprint plan
HTML monitor
reorder · risks · AI
Reordering isn't a heuristic. It's a topological sort over the real data dependencies.
The heart of the system reads every step's SQL, extracts which tables it
creates and which it reads, and builds a dependency graph. Then it
resolves the execution order with Kahn's algorithm (topological sort) —
deterministic, reproducible, and verifiable. The same process always yields the same
order, and the engine self-validates: no step may read a WORK table that
an earlier step hasn't created.
This is the deliberate boundary: the deterministic code decides the order; the AI never does. The AI receives a correct sequence and only translates SQL.
The same .egp always produces the same execution order. No prompt drift, no run-to-run variance.
After ordering, the engine checks that every WORK read resolves to an earlier create. If not, it flags a cycle for manual review.
Validated on 16 real processes, 5 to 48 nodes. The 48-node case has one table feeding 11 parallel branches — it resolved cleanly.
WORK."PRESUP1"n → normalizedFROM a t1, b t2 with aliasesCREATE VIEWPROC TRANSPOSE temp tablesBefore translating, the engine warns about what will break in dual-run.
Every migration has points only a human should decide. Instead of letting the AI guess,
the engine scans each process and surfaces the risky patterns up front, in the
prompt and in the governance Excel. One real scan found a hardcoded Oracle password in
plain text — password=aqgestudio — and flagged it as a security risk that
must never reach BigQuery.
connect to oracle blocks are grants/DDL. Resolved as IAM/permissions, not SQL.MDY, DATEPART, INTNX — the #1 source of dual-run differences.%MACRO / &var require redesign — BigQuery has no macros.RETAIN, FIRST., LAST. rewritten as window functions..egp cache. The contract is incomplete — review the original.🚫 SIN CÓDIGO per step), the AI prompt ("special attention"), the global
index, and the governance Excel (a red-highlighted row with a comment). The alert lives wherever
a person might make a wrong decision.
Rules written once, consistent across 300 processes. The master prompt closes the gap.
A subtle failure mode appeared early: the AI made inconsistent architecture decisions.
One process used CTEs and CREATE OR REPLACE; another used temp tables and
DELETE + INSERT for the same shape of problem. Both work — but 300 processes
in 300 different styles is unmaintainable technical debt.
The fix separates the WHAT from the HOW. The process prompt carries the what (this process's ordered steps, tables, risks). The master prompt carries the how (fixed architecture, a SAS→BigQuery date recipe, output format, dual-run checklist) — written once, attached to all 300.
Ordered steps, source and target tables, detected risks. Unique to each .egp. This is what changes 300 times.
CTEs by default, temp only on 3+ reuse. A date/type recipe. A target-decision tree. TODO[CATEGORY] tags for human review. Written once.
Nothing is "migrated" until SAS and BigQuery agree. Dual-run is the close.
Every candidate query closes with a dual-run checklist — row counts, sums of key numeric columns, and a date sample compared between SAS and BigQuery. The raw layer is kept precisely as this validation checkpoint. The maturity curve tells the story: across six batches of real cases, the engine absorbed five parser patterns, then one, then zero, then zero. It reached stability.
%MACRO still
need a person. Recognising that precisely is what makes the system trustworthy — it tells
you when something needs a human, instead of failing in silence.