APEX Activewear is a simulated e-commerce dataset with 436K+ transactions totaling $48.85M in realized lifetime revenue. While the underlying BigQuery warehouse is robust, pointing standard Large Language Models (LLMs) directly at raw schemas introduces operational and financial risks.
Out-of-the-box LLMs can look more reliable than they are. They generate SQL that runs, but fail on three fronts:
- Silent Financial Errors: The model writes code that executes without syntax errors, but miscalculates metrics by ignoring unwritten transformation logic, such as aggregating gross sales without filtering out a 24% return rate or misinterpreting "Ghost Revenue" assertions.
- The Business Translation Gap: Standard models fail to map ambiguous, plain-English stakeholder terminology (e.g., "serial returners" or "product drag") to certified gold-layer table structures.
- Runaway Infrastructure Costs: Poorly optimized SQL—whether written by human analysts or generated by ungrounded AI—can trigger unintentional fan-outs, sub-optimal joins, and unnecessary full table scans that quietly inflate cloud compute bills.
To reduce these risks, the engine translates plain-English intent into SQL grounded in live Dataform governance rules, and validates every query (dry-run, scan cost, confidence) before it is returned.
I engineered a custom Retrieval-Augmented Generation (RAG) governance engine that translates natural-language intent into governed BigQuery SQL. This Python CLI backend is designed as the governance layer for future stakeholder-facing interfaces (e.g., Streamlit or Slackbots).
Key Technical Implementations:
- Dynamic Dataform & Schema Ingestion: The indexing pipeline programmatically parses JSON Golden Few-Shot Queries, CSV governance rules, and BigQuery
INFORMATION_SCHEMAto construct and maintain a dynamically updating semantic vector index. - Native BigQuery Hybrid Search: Pushed the search workload directly into the warehouse using BigQuery
VECTOR_SEARCH(dense semantic intent) and BigQuery Text Indexes (sparse keyword matching). I engineered custom Reciprocal Rank Fusion (RRF) scoring natively in Python to mathematically merge these dataframes, optimizing context retrieval without relying on bloated external frameworks. - Agentic Self-Healing & HITL Escalation: Operates as a dynamic feedback mechanism driven by a $0 BigQuery dry-run API and an LLM-as-a-Judge semantic evaluator. If a syntax error triggers, the system automatically self-heals by injecting the stack trace back into the LLM. However, if the query exceeds cost thresholds (e.g., >500 MB scan) or falls below semantic confidence scores (<0.85), it triggers a Human-in-the-Loop (HITL) escalation, pausing execution in the CLI and prompting the stakeholder to approve, decline, or manually course-correct the AI.
- Governance Guardrails: The model's context is built only from certified
gold_layerand selectsilver_layertables, and it is instructed to query nothing else, which keeps metrics aligned with business definitions. These guardrails are prompt-level: they steer the model but are not a hard technical block. In a production deployment, this boundary would be reinforced by masking PII at the database level and routing the model only through anonymized analytical marts.
The engine works across a Dataform Medallion architecture that joins interconnected entity domains (users, distribution centers, products, and online events).
The underlying topology consists of:
- Raw Ingestion Layer: 6 foundational source declarations managing event and transactional data.
- Silver Staging & Quality: Standardized views protected by automated logic, including logistical timeline validations and revenue status assertions.
- Gold Analytical Marts: 10+ certified dimensional models powering aggregations like RFM segmentation, cohort retention, and global fulfillment tracking.
To reduce join hallucinations, the AI is grounded in the certified Silver and Gold schemas rather than raw tables, so user intent follows the validated transformation paths mapped in the dependency graph below:
The engine is benchmarked against a schema-only baseline (same generation model, but no retrieval, no governance rules and no dry-run self-healing) on a suite of business questions spanning strict financial logic, windowed growth calculations, and ambiguous business jargon.
Each generated query is scored on three metrics:
- Dry-run validity: does BigQuery accept the SQL?
- Schema precision: does the SQL reference the expected tables, matched by exact table name?
- Semantic logic match: does an LLM-as-a-Judge consider the SQL equivalent in logic to the ground-truth query in eval_queries.json?
Results are being re-validated before they are published here. The benchmark is being tightened in four ways:
- Independent judge: a Claude model grades the Gemini-generated SQL, so the grader is from a different model family than the generator. Baseline and engine are scored with the identical prompt and rubric, and each verdict carries a one-line reason and a failure category.
- Held-out question set:
leakage_check.pycompares every benchmark question with the few-shot examples the engine retrieves, so near-duplicates can be rewritten and results reflect unseen questions. - Exact table matching: schema precision matches table names exactly, so a table such as
user_churn_datacan no longer pass by appearing insideuser_churn_data_current. - Statistical reporting: results will be reported with confidence intervals, split by held-out versus seen-pattern questions.
Known limitation: the remaining failures concentrate in ambiguous business jargon, where the model must map a plain-English phrase (for example "serial returners") to the right certified table.
- LLM-as-a-Judge vs. Deterministic Testing: Result-set equality checks (e.g., Pandas
.equals()) penalize models for intelligently structuring derived metrics, so semantic correctness is graded by an LLM judge that evaluates logical equivalence. Schema precision stays deterministic (exact table-name match). To limit self-preference bias, the judge comes from a different model family than the generator. - RAG vs. Fine-Tuning: Fine-tuning locks in static table structures that break as warehouse schemas evolve. Hybrid Search RAG with dynamic
INFORMATION_SCHEMAindexing keeps the engine aligned with live data contracts at zero retraining cost.
The terminal runs below show the engine's ability to ingest complex business intent, navigate the Medallion architecture, and output cost-validated SQL.
LLM-generated SQL can be costly if it queries unoptimized datasets. To mitigate this, the engine executes a $0 BigQuery API dry-run and a semantic confidence check before final execution.
In the run below, the engine intercepts an initial schema error, self-heals, and then validates the final query's compute cost (15.68 MB) and confidence score (0.92) before outputting the approved SQL.
User Prompt:
List the country, total spend, and average order value for customers in the High churn risk tier who placed more than 3 orders in 2024.
🔍 View Validated Governed SQL
================ FINAL APPROVED SQL QUERY ================
WITH qualifying_users AS (
SELECT
oi.user_id,
u.country,
COUNT(DISTINCT oi.order_id) AS total_orders,
SUM(CAST(oi.sale_price AS NUMERIC)) AS total_user_spend
FROM `apex-activewear.silver_layer.stg_order_items` oi
JOIN `apex-activewear.silver_layer.stg_users` u
ON oi.user_id = u.user_id
JOIN `apex-activewear.gold_layer.customer_churn_scores` cs
ON oi.user_id = cs.user_id
WHERE EXTRACT(YEAR FROM oi.created_at) = 2024
AND oi.status NOT IN ('Returned', 'Cancelled')
AND cs.is_churn_risk = TRUE
GROUP BY oi.user_id, u.country
HAVING COUNT(DISTINCT oi.order_id) > 3
)
SELECT
country,
ROUND(SUM(total_user_spend), 2) AS total_spend,
ROUND(SAFE_DIVIDE(SUM(total_user_spend), SUM(total_orders)), 2) AS average_order_value
FROM qualifying_users
GROUP BY country
ORDER BY total_spend DESC;
==========================================================Standard LLMs struggle with multi-step period-over-period calculations. The engine translates business requests into advanced LAG() window math and dynamic Common Table Expressions (CTEs), without explicit prompt engineering.
User Prompt:
What is the month-over-month revenue growth rate and total distinct buyer count for top product categories in 2023, excluding returned items?
🔍 View Governed SQL with Window Math
================ FINAL APPROVED SQL QUERY ================
WITH monthly_category_metrics AS (
SELECT
p.category,
EXTRACT(MONTH FROM oi.created_at) AS order_month,
COUNT(DISTINCT oi.user_id) AS distinct_buyers,
SUM(CAST(oi.sale_price AS NUMERIC)) AS total_revenue
FROM `apex-activewear.silver_layer.stg_order_items` oi
JOIN `apex-activewear.silver_layer.stg_products` p
ON oi.product_id = p.product_id
WHERE EXTRACT(YEAR FROM oi.created_at) = 2023
AND oi.status NOT IN ('Returned', 'Cancelled')
GROUP BY p.category, order_month
),
mom_calculations AS (
SELECT
category,
order_month,
distinct_buyers,
total_revenue,
LAG(total_revenue) OVER (PARTITION BY category ORDER BY order_month) AS prev_month_revenue
FROM monthly_category_metrics
)
SELECT
category,
order_month,
total_revenue,
distinct_buyers,
ROUND(SAFE_DIVIDE(total_revenue - prev_month_revenue, prev_month_revenue) * 100, 2) AS mom_revenue_growth_pct
FROM mom_calculations
ORDER BY category, order_month;
==========================================================
The code lives in src/ and the benchmark data in data/. The modules map directly to the Medallion architecture workflow:
build_bq_hybrid_index.py(The Governance Indexer): The ingestion pipeline. It reads schemas, queries, and business assertions from the Silver and Gold layers, generates embeddings viagemini-embedding-001, and overwrites the activeai_governance_indextable in BigQuery.text_to_sql_engine.py(The RAG Execution Engine): The user-facing operational layer. It takes user input, performs the hybrid search against the index, promptsgemini-3.8-flash, executes the self-healing dry-run loop, and outputs SQL grounded in certified Dataform models.baseline_evals.py(The Baseline): Schema-only prompting with no retrieval, governance rules or self-healing, used as the comparison point for the engine.3_run_evals.py(The Evaluation Suite): Runs the engine on the benchmark questions and grades the generated SQL against ground truth for dry-run validity, schema precision and semantic logic.leakage_check.py(The Split Auditor): Checks whether benchmark questions overlap with the few-shot examples the engine retrieves, so results reflect held-out performance.judge.py(The Shared Judge, being wired in): One Claude-based judge used for both the baseline and the engine, with a fixed prompt, temperature 0 and a recorded model version.