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 while enforcing live Dataform governance rules before any query is executed.
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.
- Strict Governance Guardrails: Restricting the AI to certified
gold_layerand selectsilver_layertables prevents unauthorized cross-domain joins and keeps metrics aligned with business definitions. 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 prevent join hallucinations, the AI is restricted from reading raw schemas; instead, it routes user intent strictly through the validated transformation paths mapped in the dependency graph below:
The system is evaluated against a test suite of 23 complex business queries (spanning strict financial logic, windowed growth calculations, and ambiguous business jargon) to measure the governance layer's impact on reliability.
| Evaluation Metric (n=23) | Baseline (Schema-Only) | Champion (Governed RAG) | Business Impact / Trade-off |
|---|---|---|---|
| Dry-Run Syntax Accuracy | 100.0% | 100.0% | Zero syntactic crashes across both models |
| Schema Precision | 91.3% | 95.7% | +4.4 pts in resolving ambiguous business terminology |
| Semantic Logic Match *(LLM Judge)*¹ | 78.3% (18/23) | 91.3% (21/23) | +13.0 pts: misses on unwritten financial logic fell from 5 to 2 |
| Average Latency | ~9.45s | ~11.24s | +1.79s trade-off for hybrid search and validation |
¹ Evaluation Methodology (LLM-as-a-Judge): Semantic accuracy was programmatically evaluated using Gemini 3.7 Flash as an automated judge. Generated SQL queries were compared against Ground Truth eval_queries.json queries to verify semantic logic, CTE calculations, and business metric alignment beyond strict syntax.
- The Translation Gap (Schema Precision): Standard LLM prompting failed to map non-technical jargon (e.g., "serial returners") to raw table names, yielding a 91.3% schema precision. Hybrid search narrows this gap, routing queries directly to certified analytical marts at 95.7% precision.
- Silent Financial Errors (Logic Match): While the baseline produced syntactically valid SQL, it missed unwritten business rules (such as omitting canceled orders from revenue calculations). Injecting governed Dataform assertions raised the logic match by 13.0 points, from 5 misses to 2.
- LLM-as-a-Judge vs. Deterministic Testing: Deterministic assertions (e.g., Pandas
.equals()) penalize models for intelligently structuring derived metrics. An LLM-as-a-Judge evaluation grades logical equivalence, allowing flexible, correct outputs without failing CI/CD checks. - 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;
==========================================================
This engine is split into three primary automated modules that map directly to the Medallion architecture workflow:
1_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.7-flash, executes the self-healing dry-run loop, and outputs the final governed SQL strictly routed through certified Dataform models.3_run_evals.py(The CI/CD Validator): The LLM-as-a-Judge evaluation suite. It programmatically tests the generated SQL against a golden dataset to check semantic logic and schema precision before deployment.