Problem:
The company was facing major delays in decision making and issue resolution due to crunched bandwidth of analysts and engineers.
Most of the questions asked by leaders and customers of the company data warehouse, were repeated and just different enough from the previous ask that this constituted a high-frequency, high-effort P0 bottleneck for all analysts.
We needed
- A semantic layer on top of the data warehouse to enforce consistent metric definitions and standards; and
- AI/automations on the repeated queries to reduce human effort in the analytics loop.
System:
We built
- Semantic layer: An auto-tagging and auto-schema-enforce system of data tables in BigQuery
- Waygent: AI agent swarm based on Openclaw, enriched with BigQuery context; available on Slack.
Constraints:
Scale:
We built for ~500 employees ;querying Waygent 24/7/365; firing ~10K threads per day.
Latency:
We achieved 1-3 second latency (P95) on cache hit (including Claude API response).
Cost:
Waygent cost us approx. $50 / day in query cost (BigQuery charges per TB scanned, not per TFLOPS compute per second).
We ensured appropriate partitioning and indexing of the base tables and efficient join paths – all available in the Bigquery SKILL.md to Waygent – to further cut query cost.
Data warehouse:
Much of our product data is generated on platform, and some event data is sent by frontend as well.
All data ends up in the product DB (MongoDB), which is then captured in an operations log of Mongo CRUD statements, parsed and executed into BigQuery, our data warehouse.
We then deduplicate, normalise, invert the time-grain of the data from updation to creation time, and enrich with business logic and facts in a Bronze-Silver-Gold Delta Lake strategy (datasets named: raw-staging-clean). The gold tables (dataset: clean) serve as the base for all downstream queries.
Data flow and semantic layer architecture
Waygent runtime architecture
Messy data & Reliability:
The org policy, in order to boost developer velocity and reduce time-to-deploy, was that new engineering tables could be created without review or data contract, and the job of transforming the messy data into clean tables was on the data platform (BigQuery Dataform, GCP’s data pipeline internal tool).
Hence, many tables had no metadata, no relationship definitions, and no constraints. This meant that while Waygent could still function and serve query results to users, the reported data was largely incorrect.
We built an auto-tagging system based on leadership-vetted product truth to tag every table in Bigquery’s clean dataset with columnar descriptions.
We also decomposed each org-wide metric into its smallest constituents. Example: Weekly Active Teachers is split into Weekly (we find activity by creation time in clean.game), Active (we lookup only games that have non-zero participant count in clean.game), Teachers (we define a teacher based on the occupation_group in clean.user).
Such context, across all metrics, was enforced twice – once in Bigquery SKILL.md referred by sub-agents ,and once in the SOUL.md as an essential check before reporting to the user.
This reduced Waygent’s query error rate massively, with our analysts passing Waygent-written queries on ~ 50 high-impact repeat questions scoring 48/50 (48 questions correctly answered as per the correct SQL query written by our in-house analytics experts).
Adoption:
Following rollout, repeated analytics requests shifted from analyst-owned tickets to self-service Slack workflows, contributing to roughly a 90% reduction in new analytics support tickets.
As we communicated that Waygent can make mistakes through unavoidable hallucinations, we presume the reported data is cross-verified by each individual user, and deemed Waygent’s company-wide launch a success.
My contribution:
I built the semantic layer for Waygent, and the Bigquery SKILL.md, as well as actively optimising the APIs to reduce latency, increase available threads manually during launch (and further implement the autoscale policy).
Impact:
The launch of Waygent resulted in the dispersal of the analytics function within the org into each individual function.
Writing a SQL query now became much the same as writing a document or filling a spreadsheet – part of everyone’s internal org toolkit.
This resulted in cost-savings of ~$400K per annum, in organisational rightsizing as well as reduction in poorly optimised, high-cost queries written by individual org members.