Nine months in production. The analytics layer that powered LATAM's internal RAG assistant — from raw interaction logs to cohort retention to a 98% drop in retrieval errors.
LATAM employs ~40,000 people across 5 countries — pilots, cabin crew, mechanics, sales, ground ops, support. Every one of them needs answers from a sprawl of HR policies, IT runbooks, travel rules, benefits programs, and union agreements. Before Amelia, those answers lived in PDFs, intranet pages, and the institutional memory of overworked specialists.
Amelia is a corporate RAG (Retrieval-Augmented Generation) assistant grounded in LATAM's own indexed documentation. The hard problem wasn't building it — it was knowing whether it actually worked at scale, for whom, and where it was lying. That's what this dashboard answers.
"What's the COBUS reimbursement window?" → Slack a colleague → Email HR → Wait 2 days → Maybe wrong policy version.
Same question · 7-second answer · cites source · 553K of these resolved this way in 9 months.
The serving stack is not the conventional "FastAPI + external vector DB" sandwich. The RAG is invoked as a BigQuery Remote Function — backed by a Cloud Run endpoint with a max_batching_rows of 10 — which means retrieval, generation, and analytics share the same warehouse. Embeddings live in BigQuery itself (staging/embeddings/), not in a separate vector store. There is no glue code between "the AI service" and "the analytics" because they are the same system.
Every interaction emits a structured event into a Pub/Sub topic (monitoring_topic) which fans out into a four-layer Dataform pipeline. The internal codename for the analytics layer is Metatron — and it owns adoption, latency, retrieval quality, content gaps, and HR adherence in one place.
A REMOTE function in BigQuery calls Cloud Run with (interaction_id, user_profile, query).
Top-k cosine similarity against document_embeddings + freshness & access filters.
GPT-4o called with retrieved chunks + user-profile context + cited sources.
Same call writes both the response and a quality event back to monitoring_topic.
rh_plus. Read-shared with HR analytics.-- definitions/remote_functions/rag.sqlx
-- The RAG itself is a BigQuery Remote Function backed by Cloud Run.
-- Retrieval, generation, and analytics share the same warehouse.
CREATE OR REPLACE FUNCTION ${constants.RAG_REMOTE_FUNCTION_PATH}(
interaction_id STRING,
user_profile JSON,
query STRING
) RETURNS JSON
REMOTE WITH CONNECTION ${constants.BQ_REMOTE_CONNECTION}
OPTIONS (
user_defined_context = [('function', '/monitoring/rag')],
endpoint = ${constants.CLOUD_RUN_ENDPOINT},
max_batching_rows = 10
);Headline adoption is healthy — but averages hide the segments that need attention. Soporte (office workers) sit at 85% adoption. Tripulantes Mando (cockpit crew) sit at 35%. That's not an ML metric — that's a product surface decision: cabin crew don't sit at desks, and the desktop-first UI was leaving them behind.
Chat Libre (free-form chat) dominates volume at 70% — but RH+ (the RAG-grounded HR assistant) carries the highest user-base reach. They serve different jobs-to-be-done, not the same one with different UIs.
-- definitions/reporting/products_user_avg.sqlx
-- Real query: counts unique users by product per month, with annual cumulative.
config {
type: "table",
name: "product_user_avg",
dependencies: ["interaction_all_population"],
bigquery: { partitionBy: "start_month_date" }
}
WITH base AS (
SELECT
start_month_date,
COUNT(DISTINCT CASE WHEN total_rh_plus_interaction > 0 THEN employee_email END) AS rh_plus,
COUNT(DISTINCT CASE WHEN total_chat_libre_interaction > 0 THEN employee_email END) AS chat_libre,
COUNT(DISTINCT CASE WHEN total_images_interaction > 0 THEN employee_email END) AS art_maker,
COUNT(DISTINCT CASE WHEN total_other_assistants_interaction > 0 THEN employee_email END) AS other,
COUNT(DISTINCT employee_email) AS total_amelia
FROM ${ref("interaction_all_population")}
GROUP BY 1
)
SELECT
start_month_date,
rh_plus, chat_libre, art_maker, other, total_amelia,
SUM(total_amelia) OVER (PARTITION BY EXTRACT(YEAR FROM start_month_date)
ORDER BY start_month_date) AS total_amelia_annual
FROM base;Knowledge-worker VPs (Finance, Legal, IT, Comercial) cluster above 86%. Operations-heavy areas (Carga, Operaciones, CEO Brasil) sit between 42–53% — not because they don't need it, because the surface they need it on isn't a desktop browser.
-- definitions/reporting/adherence_current_staffing.sqlx
-- Real query: joins HR staffing (LATAMers only, function_name <> 'EXTERNO')
-- against active users to compute adoption per VP.
config { type: "view", name: "adherence_current_staffing" }
WITH dotacion AS (
SELECT
LOWER(employee_email) AS correo_electronico,
organizational_unit_2_name AS VP2_DESCRIP,
organizational_unit_3_name AS VP3_DESCRIP,
area_name AS poblacion,
role_name AS rol_empleado
FROM ${ref("current_staffing_rrhh")}
WHERE function_name <> 'EXTERNO' -- LATAMers only
),
active_users AS (
SELECT DISTINCT employee_email
FROM ${ref("interaction_all_population")}
WHERE total_rh_plus_interaction
+ total_chat_libre_interaction
+ total_images_interaction
+ total_other_assistants_interaction > 0
)
SELECT
d.VP2_DESCRIP,
COUNT(DISTINCT d.correo_electronico) AS headcount,
COUNT(DISTINCT a.employee_email) AS active_users,
SAFE_DIVIDE(COUNT(DISTINCT a.employee_email),
COUNT(DISTINCT d.correo_electronico)) AS adoption_rate
FROM dotacion d
LEFT JOIN active_users a ON d.correo_electronico = a.employee_email
GROUP BY 1 ORDER BY adoption_rate DESC;This is the chart that changed the product roadmap. A 50-point gap between Soporte (85%) and Tripulante Mando (35%) made the case for mobile-first redesign. Adoption isn't an ML problem — it's a UX-and-content-curation problem with an ML-shaped surface.
RH+ is the volume base, Chat Libre is the engagement base, Asistentes is the growth signal. The custom-assistants product (orange) didn't exist before Nov 2024 and reached 320 monthly active users by Apr 2025 — a separate growth curve riding on top of the platform.
Per-user intensity tells you whether people came back. Chat Libre averages 22 interactions per user — these are heavy users. RH+ averages 4 interactions per user — closer to "I have one question, get me an answer." Two products, two engagement shapes, one platform.
SELECT product_name,
COUNT(DISTINCT user_id)
FROM ${ref("interactions_full")}
GROUP BY 1;SELECT product_name,
SAFE_DIVIDE(COUNT(*),
COUNT(DISTINCT user_id))
FROM ${ref("interactions_full")}
GROUP BY 1;SELECT assistant_name,
COUNT(*) AS interactions
FROM ${ref("amelia_hub_activity")}
GROUP BY 1
ORDER BY 2 DESC LIMIT 5;Travel policy (Sta Travel) and password resets dominate. This is the chart that sets content curation priorities — every category here is an ROI bet on which sources to keep fresh and well-chunked. The Ops team's job market for AI isn't "smarter model"; it's "this week's COBUS update indexed by Friday."
The LLM does not throw exceptions when it can't ground an answer — it apologises. "Lo siento." "Desculpe." "Sorry." "La información recuperada no contiene…" Tracking RAG failure at scale meant pattern-matching the assistant's response across three languages. A single CASE statement, run nightly across interaction_full, became the entire error rate.
October 2024: 7,577 RH+ apology responses. May 2025: 31. The model didn't change — GPT-4o stayed the same. What changed: chunking strategy on long-tail policy docs, retrieval-time freshness filters, and a weekly review where each pattern-matched failure category drove the content team's curation queue. Error reduction is a content loop, not an SRE task.
Late-May latency spike (12.9–13.4s on RH+): traced to an upstream content refresh that doubled the corpus on travel-policy categories. Retrieval time grew first — generation stayed flat. That's why the latency split lives in the same table: index_search_elapsed_time and response_generation_elapsed_time are separate columns from day one. Resolved with retrieval-time filtering + reduced chunk overlap on long policy docs.
-- definitions/reporting/products_error_rate.sqlx
-- Real error detection: pattern-match the LLM's apology phrases in ES / PT / EN.
-- The model never returned error codes — it apologised. So we parse for that.
config {
type: "table",
name: "products_error_rate",
dependencies: ["interaction_all_population"],
bigquery: { partitionBy: "start_month_date" }
}
WITH assistant_product AS (
SELECT
DATE_TRUNC(DATE(interaction_timestamp), MONTH) AS year_month,
product_name,
interaction_id,
CASE
WHEN LOWER(assistant) LIKE '%lo siento%' THEN 'Error: Lo siento'
WHEN LOWER(assistant) LIKE '%desculpe%' THEN 'Error: Desculpe'
WHEN LOWER(assistant) LIKE '%sorry%' THEN 'Error: I am sorry'
WHEN LOWER(assistant) LIKE '%la información recuperada no%' THEN 'Error: ES retrieval miss'
WHEN LOWER(assistant) LIKE '%a informação recuperada não%' THEN 'Error: PT retrieval miss'
WHEN LOWER(assistant) LIKE '%the retrieved information does not%' THEN 'Error: EN retrieval miss'
WHEN LOWER(assistant) LIKE '%something went wrong%' THEN 'Error: Generic'
ELSE NULL
END AS error_answer
FROM ${ref("interaction_full")}
WHERE DATE(interaction_timestamp) >= '2024-09-09'
)
SELECT
year_month, product_name,
COUNT(DISTINCT interaction_id) AS total_interactions,
COUNTIF(error_answer IS NOT NULL) AS total_errors,
SAFE_DIVIDE(COUNTIF(error_answer IS NOT NULL),
COUNT(DISTINCT interaction_id)) AS error_rate
FROM assistant_product
WHERE product_name IN ('rh_plus', 'chat_libre', 'art_maker')
GROUP BY 1, 2;
Pattern-matched apologies catch failures the user can see. They miss the worst case: the model answers confidently with the wrong thing. So the pipeline runs an LLM-as-judge eval suite in staging/llm_evaluations/, configured in dataform.json with four real metrics — three for runtime RAG behaviour, one for offline regression testing against a golden set.
ANSWER_RELEVANCY
Does the answer actually address the question? Catches when the model retrieves useful context but generates off-topic text.
HALLUCINATION
Did the answer go beyond what the retrieved context supports? Catches the dangerous case: confident answer, no source.
CONTEXTUAL_RELEVANCY
Was the retrieved context actually relevant to the question? Diagnoses retrieval-side failure separate from generation-side.
ANSWER_CORRECTNESS
Golden-set regression check. A curated set of questions with ground-truth answers, run on every meaningful corpus or model change.
Synthetic evaluation at the document level too: document_synthetic_questions.sqlx generates K questions per document so we can compute top_k_accuracy against retrieval — i.e. "if a user asked something this document is supposed to answer, does retrieval return it in top-k?" That number is what tells us whether the corpus is the problem or the prompt is.
REMOTE function backed by Cloud Run (max_batching_rows=10). Retrieval and analytics share the warehouse — no glue code, no separate vector DB, no double source-of-truth.
products_error_rate CASEs over apology phrases and separates "retrieval miss" from "generic failure". Every CASE branch maps to a fix in a different team.
reporting/test/ — 34 files. New logic lands there first, runs against prod data in parallel, gets row-diffed against the live model, and only then promotes. Zero shipped regressions in 9 months.
EXTERNAL_QUERY() — no nightly dump, no drift, no out-of-band ETL.
index_search_elapsed_time and response_generation_elapsed_time are separate columns in the staging table. When latency spikes, the chart tells you which team needs to look — content/retrieval or model/inference — without re-instrumenting.
retention_usage_details with explicit tiers — frequent (above-mean monthly use), intensive (above-mean monthly interactions), is_high_engaged (>10/month), is_churned_this_month. 8,124 frequent / 6,835 intensive — those are the real numbers.
retention_unique_users_cohort with a HAVING SUM(interactions) > 0 guard plus FIRST_VALUE(retained_users) OVER (PARTITION BY cohort_month ORDER BY months_since_cohort) as the cohort_size denominator. Retention numbers stopped being optimistic.
"Adoption isn't an ML problem. It's a UX-and-content-curation problem with an ML-shaped surface."
This case study integrates data engineering (Dataform · BigQuery · Pub/Sub) with the operational reality of running an AI product in production. If that's the kind of work your team is hiring for, let's talk.
Contact Javier