{"id":1655,"date":"2026-03-30T11:51:07","date_gmt":"2026-03-30T11:51:07","guid":{"rendered":"https:\/\/cms.research.wpp.com\/?post_type=research_feed&p=1655"},"modified":"2026-06-24T13:21:48","modified_gmt":"2026-06-24T13:21:48","slug":"data-discovery-agent-pod-technical-walkthrough","status":"publish","type":"research_feed","link":"https:\/\/cms.research.wpp.com\/?research_feed=data-discovery-agent-pod-technical-walkthrough","title":{"rendered":"Data Discovery Agent Pod: Technical walkthrough"},"content":{"rendered":"\n

Turning raw ad platform schemas into actionable intelligence through AI-powered semantic mapping.<\/em><\/p>\n\n\n\n

TL;DR<\/h1>\n\n\n\n

Up to 90%\u00a0of enterprise data sits unused,\u00a0and our BigQuery advertising warehouse was no exception:\u00a014 platforms,\u00a0179 tables, 2,709 columns, 15.8 billion records, zero shared schema, and answering a question as simple as whether Pinterest carries geo data required a two-week manual investigation. Every platform invented its own vocabulary (Facebook says amount_spent<\/code>, Google says cost_micros<\/code>, TikTok says spend<\/code>)\u00a0and manual reconciliation cost analysts two to four weeks per pass,\u00a0producing a spreadsheet that started decaying the moment it was finished.\u00a0<\/p>\n\n\n\n

We built the Data Discovery Agent,\u00a0an AI-powered pipeline that connects to BigQuery,\u00a0samples real data for grounding,\u00a0crawls external connector documentation,\u00a0and runs Gemini LLM inference with versioned prompts to autonomously annotate every column with confidence-scored mappings.\u00a0The agent compressed weeks of manual work into hours,\u00a0took us from zero structured understanding of our warehouse to a 58%\u00a0average completeness\u00a0(column presence per canonical sub-modality;\u00a0signal strength and population validation planned for Q2)\u00a0baseline across all 14 platforms,\u00a0and replaced tribal-knowledge spreadsheets with a searchable,\u00a0versioned genome report that turns\u00a0“do we have that data?”\u00a0from a research project into a dashboard lookup.<\/p>\n\n\n\n

\n

If you don’t care about the technical details, read our blog post <\/a>instead. <\/strong><\/strong><\/p>\n<\/blockquote>\n\n\n\n

\n\n\n\n

Technical walkthrough<\/h1>\n\n\n\n

Introduction<\/h1>\n\n\n\n

Advertising data warehouses are,\u00a0by nature,\u00a0enormous and messy.\u00a0Every major ad platform\u00a0–\u00a0Facebook,\u00a0Google,\u00a0TikTok,\u00a0and a dozen others\u00a0–\u00a0exposes its own schema,\u00a0its own naming conventions,\u00a0and its own definition of what a\u00a0“click”\u00a0or a\u00a0“spend”\u00a0actually means.\u00a0When these platforms feed into a centralised\u00a0Google BigQuery<\/strong><\/a>\u00a0data lake,\u00a0the result is a sprawling collection of tables and columns that no single analyst can hold in their head.\u00a0In our case:\u00a014 advertising platforms, 179 tables, and 2,709 columns<\/strong>\u00a0–\u00a0with over 15.8 billion records behind them.<\/p>\n\n\n\n

Historically,\u00a0making sense of this warehouse required manual schema annotation:\u00a0a data analyst opening each table,\u00a0reading every column name,\u00a0pulling sample rows,\u00a0cross-referencing API documentation,\u00a0and deciding which canonical category each column belonged to.\u00a0Conservatively,\u00a0that process took\u00a0two to four weeks<\/strong>\u00a0of focused work for a single pass.\u00a0And the result started decaying immediately\u00a0–\u00a0platforms update schemas quarterly,\u00a0sometimes monthly.<\/p>\n\n\n\n

To eliminate this bottleneck,\u00a0we architected and deployed the\u00a0Data Discovery Agent<\/strong>:\u00a0an AI-powered platform that autonomously discovers,\u00a0maps,\u00a0and scores the completeness of advertising data across an entire BigQuery data lake.\u00a0The agent connects to BigQuery,\u00a0fetches every table and column,\u00a0extracts real sample data for grounding,\u00a0crawls external documentation for cross-reference,\u00a0invokes\u00a0Vertex AI<\/strong><\/a>\u00a0LLM inference with structured prompts,\u00a0and delivers a fully annotated mapping with confidence scores\u00a0–\u00a0compressing weeks of manual work into hours.<\/p>\n\n\n\n

The system is built as a production-grade\u00a0FastAPI<\/strong><\/a>\u00a0web application,\u00a0deployed on\u00a0Google Cloud Run<\/strong><\/a>,\u00a0with a complete admin control panel,\u00a0role-based access via Google OAuth,\u00a0and a storage abstraction layer that operates identically on local filesystems and Google Cloud Storage.\u00a0This document provides a comprehensive technical walkthrough of its architecture,\u00a0pipeline,\u00a0deployment,\u00a0and results.<\/p>\n\n\n\n

\n\n\n\n

Agent architecture<\/h1>\n\n\n\n

The Data Discovery Agent is structured as a\u00a0five-stage sequential pipeline<\/strong>\u00a0orchestrated by a FastAPI backend.\u00a0Unlike a chatbot or a single-prompt wrapper,\u00a0the agent executes an end-to-end workflow of discovery,\u00a0sampling,\u00a0documentation crawling,\u00a0LLM inference,\u00a0and enrichment reporting\u00a0–\u00a0with minimal human intervention.\u00a0The human’s role shifts from\u00a0doing the mapping<\/em>\u00a0to\u00a0reviewing the mapping<\/em>.<\/p>\n\n\n\n

The high-level data flow is illustrated below:<\/p>\n\n\n\n

\"\"
Figure 1 – Data Discovery Agent pipeline: five stages from BigQuery data lake to executive dashboard.<\/em><\/figcaption><\/figure>\n\n\n\n


The architecture was inspired by a clean separation of concerns.\u00a0The application core follows a layered pattern with dedicated service modules.<\/p>\n\n\n\n

Service module inventory<\/h2>\n\n\n\n

The backend is organised into focused service modules,\u00a0each responsible for a distinct domain:<\/p>\n\n\n\n

Service Module<\/strong><\/th>Core Responsibility<\/strong><\/th><\/tr><\/thead>
BigQuery Service<\/strong><\/td>Connects to the BigQuery data lake, discovers datasets and tables, fetches column schemas and row counts, and saves timestamped fetch snapshots.<\/td><\/tr>
Sample Service<\/strong><\/td>Extracts real sample rows from BigQuery tables using background-threaded parallel queries. Supports pause, resume, and abort controls.<\/td><\/tr>
Adverity Service<\/strong><\/td>Crawls official Adverity connector documentation pages using Playwright, extracting structured field lists (name, description, dimension\/metric).<\/td><\/tr>
Inference Service<\/strong><\/td>Builds versioned LLM prompts, manages sync and batch inference pipelines via Vertex AI, and parses structured JSON responses into column-to-submodality mappings.<\/td><\/tr>
BQ Filter Service<\/strong><\/td>Manages regex-based include\/exclude rules that control which BigQuery datasets and tables appear in a fetch.<\/td><\/tr>
User Service<\/strong><\/td>Handles user CRUD operations, domain allow-lists, email ban-lists, and role-based access control (admin\/user).<\/td><\/tr>
LLM Config Service<\/strong><\/td>Loads and manages LLM model configurations, including model identifiers and API parameters.<\/td><\/tr>
Vertex Model Service<\/strong><\/td>Discovers available Vertex AI regions and publisher models for inference.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Key system capabilities<\/h2>\n\n\n\n
    \n\n
  1. Autonomous Schema Discovery:<\/strong>\u00a0The agent connects to BigQuery, applies configurable regex filters, and discovers all advertising-platform datasets, tables, and column schemas without manual intervention.<\/li>\n\n\n
  2. Evidence-Grounded LLM Inference:<\/strong>\u00a0Every mapping decision is grounded in real data – column names, data types, and actual sample values – not just schema metadata. This dramatically reduces hallucination risk.<\/li>\n\n\n
  3. Dual-Direction Mapping:<\/strong>\u00a0The system performs both forward mapping (BigQuery columns to canonical sub-modalities) and reverse mapping (Adverity connector fields to sub-modalities), closing the loop from “what do we have?” to “what should we enable?”<\/li>\n\n\n
  4. Completeness Scoring:<\/strong>\u00a0Each platform receives a quantitative completeness score measuring how many of the 24 canonical sub-modalities are covered, turning vague data quality concerns into actionable metrics.<\/li>\n\n\n
  5. Versioned Prompt Engineering:<\/strong>\u00a0All LLM prompts are version-tracked with SHA-256 content hashing, ensuring that every inference result can be traced back to the exact prompt that produced it.<\/li>\n\n\n
  6. Transparent Storage Abstraction:<\/strong>\u00a0A unified storage layer allows the application to operate identically on local filesystems (development) and Google Cloud Storage (production) by changing a single environment variable.<\/li>\n\n\n
  7. Production-Grade Access Control:<\/strong>\u00a0Google OAuth 2.0 authentication with domain allow-lists, email ban-lists, and admin\/user role separation ensures secure, auditable access.<\/li>\n\n<\/ol>\n\n\n\n

    The modality schema: a canonical taxonomy<\/h2>\n\n\n\n

    At the heart of the agent’s mapping logic is a canonical\u00a0modality schema<\/strong>\u00a0–\u00a0a structured taxonomy that defines what advertising data\u00a0means<\/em>,\u00a0independent of any platform’s naming conventions.\u00a0The schema is organised around five high-level\u00a0modalities<\/strong>,\u00a0each representing a fundamental category of advertising measurement:<\/p>\n\n\n\n

    Modality<\/strong><\/th>Sub-modalities<\/strong><\/th>What It Captures<\/strong><\/th><\/tr><\/thead>
    Performance<\/strong><\/td>7<\/td>The numbers: spend, impressions, clicks, video plays, conversions, leads, CTR<\/td><\/tr>
    Creative<\/strong><\/td>4<\/td>What the ad looked like: creative IDs, asset paths, ad names, format types<\/td><\/tr>
    Audience<\/strong><\/td>6<\/td>Who saw it: gender, age group, interests, custom audiences, behavioural segments<\/td><\/tr>
    Geo<\/strong><\/td>5<\/td>Where they saw it: countries, regions, cities, postal codes, designated market areas<\/td><\/tr>
    Brand<\/strong><\/td>2<\/td>Who paid for it: brand name, advertiser identity<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

    This yields\u00a024 sub-modalities<\/strong>\u00a0in total\u00a0–\u00a0the atomic units of the mapping.\u00a0The LLM’s task is to determine,\u00a0for each BigQuery column across all 179 tables,\u00a0which sub-modality\u00a0(if any)\u00a0it corresponds to,\u00a0and with what confidence.<\/p>\n\n\n\n

    The modality dictionary is fully editable through the admin UI,\u00a0meaning the taxonomy can be extended or refined without touching code.\u00a0It is injected into every LLM prompt as structured context,\u00a0ensuring consistent mapping behaviour across inference runs.<\/p>\n\n\n\n

    \n\n\n\n

    Technology stack<\/h1>\n\n\n\n
    Component<\/strong><\/th>Technology<\/strong><\/th>Role<\/strong><\/th><\/tr><\/thead>
    Web Framework<\/strong><\/td>FastAPI<\/a><\/td>Application backend, API routing, middleware, and session management<\/td><\/tr>
    LLM Engine<\/strong><\/td>Google Gemini<\/a>\u00a0(via\u00a0Vertex AI<\/a>)<\/td>Powers all schema-mapping and recommendation inference across the Gemini model family (including Gemini Flash for batch runs); chosen for low latency, strong instruction-following, and structured JSON output<\/td><\/tr>
    Data Warehouse<\/strong><\/td>Google BigQuery<\/a><\/td>Primary data source; the agent queries dataset schemas, table metadata, and sample rows<\/td><\/tr>
    Batch Inference<\/strong><\/td>Vertex AI Batch Prediction<\/a><\/td>Processes bulk LLM requests asynchronously via JSONL files staged in GCS; more cost-effective for full runs across all platforms<\/td><\/tr>
    Documentation Crawling<\/strong><\/td>Playwright<\/a><\/td>Headless browser automation for crawling Adverity connector documentation pages and extracting structured field lists<\/td><\/tr>
    Frontend<\/strong><\/td>Jinja2<\/a>\u00a0+\u00a0HTMX<\/a><\/td>Server-side rendered HTML templates with HTMX for interactive, partial-page updates without a JavaScript framework<\/td><\/tr>
    Authentication<\/strong><\/td>Google OAuth 2.0<\/a>\u00a0(via\u00a0Authlib<\/a>)<\/td>Secure user login with domain allow-lists, email ban-lists, and admin\/user role separation<\/td><\/tr>
    Application Data<\/strong><\/td>Google Cloud Storage<\/a><\/td>Persistent store for fetch results, inference outputs, platform configs, user records, and Adverity documentation<\/td><\/tr>
    Deployment<\/strong><\/td>Google Cloud Run<\/a><\/td>Serverless container hosting with auto-scaling, health probes, and secret-backed environment variables<\/td><\/tr>
    Infrastructure<\/strong><\/td>Terraform<\/a><\/td>Infrastructure-as-code: GCS buckets, Artifact Registry, Cloud Run service, IAM roles, Secret Manager secrets<\/td><\/tr>
    Containerisation<\/strong><\/td>Docker<\/a>\u00a0(multi-stage build)<\/td>Reproducible builds with a builder stage for dependency installation and a slim runtime stage running as non-root<\/td><\/tr>
    Secret Management<\/strong><\/td>Google Secret Manager<\/a><\/td>Stores OAuth credentials and session-signing keys, injected into Cloud Run as environment variables<\/td><\/tr>
    Language<\/strong><\/td>Python 3.11<\/td>All application code, managed via\u00a0Poetry<\/a>\u00a0for dependency resolution<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n\n\n\n

    Pipeline deep-dive<\/h1>\n\n\n\n

    The following five stages take the system from a raw,\u00a0unannotated data warehouse to a fully mapped,\u00a0scored,\u00a0and actionable genome report.<\/p>\n\n\n\n

    Stage 1 – Discovery<\/h2>\n\n\n\n

    The agent connects to Google BigQuery and discovers all staging datasets matching a configurable naming pattern\u00a0(e.g.,\u00a0^bq_cgh_mp_.*_staging$<\/code>).\u00a0For each dataset,\u00a0it fetches the full table inventory:\u00a0column names,\u00a0data types,\u00a0and row counts.\u00a0Intelligent regex filters\u00a0–\u00a0managed through the admin UI via\u00a0bq_filters.json<\/code>\u00a0–\u00a0exclude temporary artefacts such as tables prefixed with\u00a0stg_<\/code>,\u00a0suffixed with\u00a0_tmp<\/code>\u00a0or\u00a0_dbt_tmp<\/code>,\u00a0and any datasets that don’t match the expected naming convention.<\/p>\n\n\n\n

    Results are saved as a\u00a0timestamped fetch snapshot<\/strong>\u00a0(fetches\/fetch_YYYYMMDD_HHMMSS\/results.json<\/code>).\u00a0Multiple fetches can coexist,\u00a0allowing admins to compare schema evolution over time.\u00a0Each fetch captures the complete state of the warehouse at a point in time\u00a0–\u00a0the starting point for all downstream inference work.<\/p>\n\n\n\n

    Current warehouse dimensions:<\/strong><\/p>\n\n\n\n

    Metric<\/strong><\/th>Count<\/strong><\/th><\/tr><\/thead>
    Advertising Platforms<\/td>14<\/td><\/tr>
    BigQuery Datasets<\/td>17<\/td><\/tr>
    Tables<\/td>179<\/td><\/tr>
    Total Columns<\/td>2,709<\/td><\/tr>
    Total Records<\/td>15.8 billion+<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

    Platforms covered<\/h3>\n\n\n\n
    #<\/th>Platform<\/th>#<\/th>Platform<\/th><\/tr><\/thead>
    1<\/td>Amazon DSP<\/td>8<\/td>Pinterest<\/td><\/tr>
    2<\/td>Facebook Ads<\/td>9<\/td>Snapchat<\/td><\/tr>
    3<\/td>Google Ads<\/td>10<\/td>TikTok<\/td><\/tr>
    4<\/td>Google Ads (YouTube)<\/td>11<\/td>The Trade Desk<\/td><\/tr>
    5<\/td>Google DV360<\/td>12<\/td>Twitter\/X Ads<\/td><\/tr>
    6<\/td>Google DV360 (YouTube)<\/td>13<\/td>Xandr<\/td><\/tr>
    7<\/td>LinkedIn<\/td>14<\/td>IAS<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

    Stage 2 – Sampling<\/h2>\n\n\n\n

    Column names alone are often ambiguous.\u00a0A column called\u00a0cost<\/code>\u00a0could mean total spend,\u00a0cost-per-click,\u00a0or an internal ID.\u00a0The agent resolves this ambiguity by extracting\u00a0actual sample data rows<\/strong>\u00a0from each table using background-threaded parallel queries against BigQuery.<\/p>\n\n\n\n

    These real values become critical evidence for the LLM:\u00a0seeing\u00a014.50<\/code>,\u00a00.83<\/code>,\u00a0127.99<\/code>\u00a0in a\u00a0cost<\/code>\u00a0column strongly suggests monetary spend,\u00a0not an identifier.\u00a0Seeing\u00a0M<\/code>,\u00a0F<\/code>,\u00a0Unknown<\/code>\u00a0in a\u00a0gender<\/code>\u00a0column confirms an audience dimension more reliably than the column name alone.<\/p>\n\n\n\n

    The extraction process supports\u00a0pause, resume, and abort<\/strong>\u00a0controls\u00a0–\u00a0essential when sampling across hundreds of tables with billions of rows.\u00a0Progress is tracked per-platform and persisted to storage,\u00a0so an interrupted extraction can resume exactly where it left off.\u00a0Samples are saved as\u00a0samples_<platform>.json<\/code>\u00a0per fetch and are injected into the LLM prompt alongside the schema metadata.<\/p>\n\n\n\n

    Stage 3 – Documentation crawling<\/h2>\n\n\n\n

    The agent crawls the official connector documentation for each platform\u00a0–\u00a0specifically,\u00a0the authoritative\u00a0“Most Used Fields”\u00a0pages published by\u00a0Adverity<\/a>,\u00a0the data integration platform that feeds data into the BigQuery warehouse.\u00a0Using\u00a0Playwright<\/a>\u00a0for headless browser automation,\u00a0the crawler fetches each documentation page and extracts a structured field list:\u00a0name,\u00a0description,\u00a0and whether the field is a dimension or metric.<\/p>\n\n\n\n

    Each crawled page is saved in three formats\u00a0–\u00a0HTML,\u00a0Markdown,\u00a0and structured JSON\u00a0–\u00a0under\u00a0adverity_docs\/<\/code>\u00a0in the storage backend.\u00a0These documents serve as the agent’s\u00a0“reference manual”:\u00a0a ground-truth checklist of what fields each platform\u00a0could<\/em>\u00a0provide,\u00a0independent of what currently exists in the warehouse.<\/p>\n\n\n\n

    The crawl engine runs as a background task with progress tracking,\u00a0with a configurable delay between requests to avoid rate-limiting.\u00a0Platform URLs are managed through the admin UI,\u00a0making it straightforward to add new data sources.<\/p>\n\n\n\n

    Stage 4 – LLM inference<\/h2>\n\n\n\n

    This is the core intellectual step\u00a0–\u00a0where the agent reads every column and produces an annotated mapping.\u00a0The Inference Service builds\u00a0versioned prompts<\/strong>\u00a0containing three layers of context:<\/p>\n\n\n\n

      \n\n
    1. Table schemas<\/strong>\u00a0– column names and data types for every table in the platform’s dataset.<\/li>\n\n\n
    2. Sample data<\/strong>\u00a0– real row values extracted in Stage 2, providing grounding evidence.<\/li>\n\n\n
    3. Modality definitions<\/strong>\u00a0– the target taxonomy of modalities and sub-modalities the LLM must map to.<\/li>\n\n<\/ol>\n\n\n\n

      These structured prompts are sent to models from the\u00a0Google Gemini<\/a><\/strong>\u00a0family via Vertex AI.\u00a0The model returns structured JSON:\u00a0for each canonical sub-modality,\u00a0it identifies matching columns,\u00a0assigns a\u00a0confidence score<\/strong>\u00a0(0.0 to 1.0),\u00a0and provides natural-language reasoning for each match.<\/p>\n\n\n\n

      Inference modes<\/h3>\n\n\n\n

      Two inference modes are supported,\u00a0selectable through the admin UI:<\/p>\n\n\n\n

      Mode<\/strong><\/th>Mechanism<\/strong><\/th>Best For<\/strong><\/th><\/tr><\/thead>
      Sync<\/strong><\/td>One LLM request per platform, processed sequentially via\u00a0POST \/api\/inference\/sync<\/code><\/td>Quick testing, single-platform runs<\/td><\/tr>
      Batch<\/strong><\/td>Prompts written to JSONL files in GCS, submitted as Vertex AI batch prediction jobs, polled for completion<\/td>Full production runs across all 14 platforms; significantly more cost-effective<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

      For batch mode,\u00a0the workflow is:\u00a0write prompts to JSONL,\u00a0submit batch jobs,\u00a0poll status until completion,\u00a0collect and parse results.\u00a0The UI provides real-time progress tracking for all active jobs.<\/p>\n\n\n\n

      Prompt engineering<\/h3>\n\n\n\n

      All prompt templates live in\u00a0app\/prompts\/<\/code>\u00a0and are version-tracked with semantic versioning and SHA-256 content hashing via\u00a0versions.json<\/code>.\u00a0This means every inference result records exactly which prompt version and content hash produced it\u00a0–\u00a0critical for reproducibility.<\/p>\n\n\n\n

      The base modality mapping prompt instructs the LLM to:<\/p>\n\n\n\n