<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Ravish Garg on Medium]]></title>
        <description><![CDATA[Stories by Ravish Garg on Medium]]></description>
        <link>https://medium.com/@garg-ravish?source=rss-539e90d8d864------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*GK52M7JJSWmBGiE4At_2dw.png</url>
            <title>Stories by Ravish Garg on Medium</title>
            <link>https://medium.com/@garg-ravish?source=rss-539e90d8d864------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 07:38:03 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@garg-ravish/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Stop Searching, Start Reading: Building a Hybrid Macro-to-Micro RAG Pipeline on BigQuery]]></title>
            <link>https://medium.com/google-cloud/stop-searching-start-reading-building-a-hybrid-macro-to-micro-rag-pipeline-on-bigquery-e636aff6687e?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/e636aff6687e</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[knowledge-graph]]></category>
            <category><![CDATA[generative-ai-tools]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Thu, 26 Mar 2026 09:29:26 GMT</pubDate>
            <atom:updated>2026-03-26T09:29:26.206Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>How sequential vector + vectorless retrieval achieves both massive scale and reasoning-level precision — with SQL you can run today</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ms_TabBdFCh_3IArnN5xRQ.jpeg" /><figcaption><strong>Hybrid Macro-to-Micro Architecture</strong></figcaption></figure><p>You’ve built a RAG pipeline. It works great on blog posts, product descriptions, and maybe even some internal wikis. Then someone drops a 200-page pharmaceutical regulatory filing into your system and asks:</p><blockquote><em>“What are the specific drug interaction warnings for Compound X referenced in Section 4.2, accounting for the dosage modifications detailed in Appendix F?”</em></blockquote><p>Your vector search returns five chunks that mention <em>“drug interactions”</em> and <em>“dosage”</em>. None of them is from Section 4.2 or Appendix F. The generative model, doing its best with fragments of context, confidently produces a partially wrong answer. In pharma, being partially wrong can be dangerous.</p><p>Sound familiar? If you’ve been building RAG systems against complex, structured documents — legal contracts, financial filings, clinical trial protocols, engineering manuals — you’ve probably hit this wall. The dirty secret of vector-based retrieval is that <strong>semantic similarity isn’t the same thing as relevance</strong>. Your embedding model is doing advanced pattern matching, not reading comprehension.</p><p>But here’s the thing: the answer isn’t to throw out vector search. It’s to recognise that finding the right <em>document</em> and finding the right <em>paragraph within that document</em> are fundamentally different problems — and they need different tools.</p><p>That’s exactly what the <strong>Hybrid Macro-to-Micro Architecture</strong> solves. And the best part? You can build the entire thing natively in BigQuery using SQL.</p><h3>The Two-Brain Problem in Enterprise RAG</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*riKAxTt_CRaA4JFdv8N_ag.jpeg" /></figure><p>Let me paint a picture. Imagine you’re a regulatory affairs analyst at a global pharmaceutical company. Your enterprise document lake contains over 40,000 documents: FDA submissions, clinical trial reports, pharmacovigilance case files, manufacturing SOPs, and post-market surveillance records. These documents are dense, highly structured, and internally cross-referenced. A typical FDA submission alone can run hundreds of pages with deeply nested sections, appendices, and regulatory cross-references.</p><p>When you ask your RAG system a question, it needs to do two fundamentally different cognitive tasks:</p><p><strong>Task 1 — The Haystack Problem:</strong> Identify which 3–5 documents out of 40,000 are relevant to your query. This is a breadth problem. You need speed, scale, and the ability to disambiguate between thousands of similarly titled documents <em>(“Clinical Study Report” appears in roughly half the corpus)</em>.</p><p><strong>Task 2 — The Needle Problem:</strong> Within those specific documents, find the <em>exact</em> section, table, or cross-referenced appendix that answers your question. This is a depth problem. You need structural awareness, the ability to follow internal references, and the intelligence to keep a financial table intact rather than shredding it across arbitrary chunk boundaries.</p><blockquote>Vector search is exceptional at Task 1 and consistently fails at Task 2. Vectorless retrieval (think: PageIndex-style agentic traversal) excels at Task 2 but falls apart at Task 1 when facing a multi-document corpus with identically named sections.</blockquote><p>The Hybrid Macro-to-Micro architecture doesn’t pick sides. It runs both approaches sequentially, and BigQuery is uniquely positioned to orchestrate the entire pipeline without your data ever leaving the warehouse.</p><h3>The Architecture: How Macro Meets Micro</h3><p>Here’s the mental model. Think of it as a two-stage funnel:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MAiYtGZ0kA3xIOuG7sWUJQ.jpeg" /></figure><p><strong>Stage 1 — Macro Retrieval (The Vector Phase)</strong> BigQuery’s VECTOR_SEARCH function scans embeddings across your entire document corpus. It uses semantic similarity to rapidly narrow tens of thousands of documents down to the top 3–5 most relevant files. This phase handles disambiguation, noise filtering, and entity resolution at a massive scale.</p><p><strong>Stage 2 — Micro Retrieval (The Vectorless Phase)</strong> Once target documents are isolated, the pipeline shifts paradigms entirely. BigQuery retrieves pre-computed JSON hierarchical trees representing each document’s structural layout — chapters, sections, subsections, tables, appendices — all preserved as nested relationships. Using ML.GENERATE_TEXTAn LLM agent traverses these trees like a human expert reading a table of contents, identifies the exact structural nodes that contain the answer, and extracts unfragmented text for final synthesis.</p><p>The beauty here is that each stage neutralises the other’s weaknesses. The vector phase handles the multi-document disambiguation that trips up structural systems, while the vectorless phase resolves the context destruction and cross-reference blindness that plagues chunking-based approaches.</p><p><strong>Let’s build it.</strong></p><h3>Phase 1: Setting Up the Macro Stage in BigQuery</h3><h3>The Schema: Storing Documents and Embeddings</h3><p>First, we need a BigQuery table that holds both the chunked text for vector search and the structural metadata for the Micro stage.</p><pre>-- Table for vector-based Macro retrieval<br>CREATE TABLE IF NOT EXISTS `your_project.rag_pipeline.document_chunks` (<br>  chunk_uuid       STRING       NOT NULL,<br>  document_id      STRING       NOT NULL,<br>  document_title   STRING,<br>  file_location    STRING,       -- Cloud Storage URI<br>  chunk_text       STRING       NOT NULL,<br>  metadata         JSON,         -- Category, author, date, doc_type<br>  embeddings       ARRAY&lt;FLOAT64&gt; -- Vector representation<br>);<br><br><br>-- Companion table for Micro stage: structural trees per document<br>CREATE TABLE IF NOT EXISTS `your_project.rag_pipeline.document_trees` (<br>  document_id      STRING       NOT NULL,<br>  document_title   STRING,<br>  file_location    STRING,<br>  structural_tree  JSON         NOT NULL,  -- Hierarchical TOC as nested JSON<br>  raw_nodes        JSON         NOT NULL   -- Node ID -&gt; raw text mapping<br>);</pre><p>The document_chunks table stores the traditional vector-search artefacts. The document_trees table stores the <strong>PageIndex-style</strong> hierarchical trees and their associated raw text nodes — the material the Micro stage will use.</p><h3>Generating Embeddings at Scale</h3><p>BigQuery lets you generate embeddings directly in SQL using a remote connection to Vertex AI: no data egress, no Python glue code.</p><pre>-- Step 1: Create a remote connection to Vertex AI<br>-- (Done once via BigQuery Console or bq CLI)<br><br>-- Step 2: Create a remote embedding model<br>CREATE OR REPLACE MODEL `your_project.rag_pipeline.embedding_model`<br>  REMOTE WITH CONNECTION `your_project.us.vertex_ai_connection`<br>  OPTIONS (ENDPOINT = &#39;text-embedding-005&#39;);<br><br>-- Step 3: Generate embeddings for all chunks<br>UPDATE `your_project.rag_pipeline.document_chunks` AS target<br>SET target.embeddings = source.text_embedding<br>FROM (<br>  SELECT<br>    chunk_uuid,<br>    ml_generate_embedding_result AS text_embedding<br>  FROM<br>    ML.GENERATE_EMBEDDING(<br>      MODEL `your_project.rag_pipeline.embedding_model`,<br>      (SELECT chunk_uuid, chunk_text AS content<br>       FROM `your_project.rag_pipeline.document_chunks`<br>       WHERE embeddings IS NULL),<br>      STRUCT(TRUE AS flatten_json_output)<br>    )<br>  WHERE LENGTH(ml_generate_embedding_status) = 0<br>) AS source<br>WHERE target.chunk_uuid = source.chunk_uuid;</pre><h3>Indexing for Speed</h3><p>Brute-force cosine similarity across billions of rows isn’t practical. BigQuery’s native vector indexing fixes that:</p><pre>-- Create an IVF index for fast approximate nearest neighbor search<br>CREATE OR REPLACE VECTOR INDEX macro_retrieval_index<br>  ON `your_project.rag_pipeline.document_chunks`(embeddings)<br>  OPTIONS (<br>    index_type = &#39;IVF&#39;,<br>    distance_type = &#39;COSINE&#39;,<br>    ivf_options = &#39;{&quot;num_lists&quot;: 500}&#39;<br>  );</pre><p>The index partitions the vector space into 500 clusters, so queries only scan a small fraction of the total data. BigQuery handles index maintenance automatically as new documents are ingested.</p><h3>Executing the Macro Search</h3><p>Now the actual retrieval. Given a user query, the Macro stage returns the top candidate documents:</p><pre>-- Macro Stage: Find top 5 most relevant DOCUMENTS<br>-- (not chunks — we aggregate to document level)<br>WITH ranked_chunks AS (<br>  SELECT<br>    query.query AS user_query,<br>    base.document_id,<br>    base.document_title,<br>    base.chunk_text,<br>    distance<br>  FROM<br>    VECTOR_SEARCH(<br>      TABLE `your_project.rag_pipeline.document_chunks`,<br>      &#39;embeddings&#39;,<br>      (<br>        SELECT<br>          ml_generate_embedding_result AS embedding,<br>          content AS query<br>        FROM<br>          ML.GENERATE_EMBEDDING(<br>            MODEL `your_project.rag_pipeline.embedding_model`,<br>            (SELECT &#39;What are the drug interaction warnings for Compound X in the Phase III trial?&#39; AS content),<br>            STRUCT(TRUE AS flatten_json_output)<br>          )<br>      ),<br>      top_k =&gt; 20,<br>      OPTIONS =&gt; &#39;{&quot;fraction_lists_to_search&quot;: 0.05}&#39;<br>    )<br>),<br>-- Aggregate to document level: pick top 5 distinct documents<br>top_documents AS (<br>  SELECT<br>    document_id,<br>    document_title,<br>    MIN(distance) AS best_distance,<br>    COUNT(*) AS matching_chunks<br>  FROM ranked_chunks<br>  GROUP BY document_id, document_title<br>  ORDER BY best_distance ASC<br>  LIMIT 5<br>)<br>SELECT * FROM top_documents;</pre><p>Notice the two-level approach: we retrieve the top 20 <em>chunks</em> but aggregate them into unique <em>documents</em>, ranking them by their best-matching chunk. This gives the Micro stage a focused set of documents to work with.</p><h3>Phase 2: The Micro Stage — Where Reasoning Replaces Matching</h3><p>This is where the architecture gets genuinely interesting. We’ve identified our target documents. Now we need to find the <em>exact</em> answer within them — without chunking artefacts, without losing table structure, and with the ability to follow cross-references.</p><h3>How the Structural Tree Works</h3><p>During ingestion (handled offline by a framework like PageIndex or a custom pipeline), each document is analysed by an LLM that detects its natural structural boundaries: chapter titles, section headings, table layouts, and appendix markers. The output is a compressed JSON tree — essentially, an intelligent table of contents.</p><p>Here’s a simplified example of what that tree looks like for a pharmaceutical Clinical Study Report:</p><pre>{<br>  &quot;document_id&quot;: &quot;CSR-2024-0047&quot;,<br>  &quot;title&quot;: &quot;Phase III Clinical Study Report — Compound X&quot;,<br>  &quot;nodes&quot;: [<br>    {<br>      &quot;node_id&quot;: &quot;N001&quot;,<br>      &quot;title&quot;: &quot;1. Synopsis&quot;,<br>      &quot;summary&quot;: &quot;Executive summary of trial design, patient population, primary endpoints.&quot;,<br>      &quot;page_range&quot;: &quot;1-4&quot;,<br>      &quot;children&quot;: []<br>    },<br>    {<br>      &quot;node_id&quot;: &quot;N002&quot;,<br>      &quot;title&quot;: &quot;4. Clinical Pharmacology&quot;,<br>      &quot;summary&quot;: &quot;Pharmacokinetic and pharmacodynamic data including absorption, distribution, metabolism.&quot;,<br>      &quot;page_range&quot;: &quot;45-78&quot;,<br>      &quot;children&quot;: [<br>        {<br>          &quot;node_id&quot;: &quot;N003&quot;,<br>          &quot;title&quot;: &quot;4.2 Drug Interactions&quot;,<br>          &quot;summary&quot;: &quot;Documented interactions with CYP3A4 inhibitors, warfarin, and SSRIs. Includes dose adjustment tables.&quot;,<br>          &quot;page_range&quot;: &quot;58-67&quot;,<br>          &quot;children&quot;: []<br>        }<br>      ]<br>    },<br>    {<br>      &quot;node_id&quot;: &quot;N004&quot;,<br>      &quot;title&quot;: &quot;Appendix F: Dosage Modification Guidelines&quot;,<br>      &quot;summary&quot;: &quot;Detailed dosage adjustment tables for renal impairment, hepatic impairment, and drug interactions.&quot;,<br>      &quot;page_range&quot;: &quot;189-196&quot;,<br>      &quot;children&quot;: []<br>    }<br>  ]<br>}</pre><p>This tree is small enough to fit entirely in an LLM’s context window. That’s the key insight — the model reads this <em>compressed map</em> rather than the full 200-page document, then decides exactly which sections to retrieve.</p><h3>Orchestrating Agentic Traversal in BigQuery</h3><p>The Micro stage runs as a BigQuery Stored Procedure that orchestrates multiple ML.GENERATE_TEXT calls in sequence. Here&#39;s the full implementation:</p><pre>CREATE OR REPLACE PROCEDURE `your_project.rag_pipeline.micro_retrieval`(<br>  IN user_query STRING,<br>  IN target_document_id STRING,<br>  OUT final_answer STRING<br>)<br>BEGIN<br>  DECLARE structural_tree JSON;<br>  DECLARE navigation_response STRING;<br>  DECLARE selected_node_ids ARRAY&lt;STRING&gt;;<br>  DECLARE retrieved_context STRING;<br><br>-- Step 1: Retrieve the structural tree for the target document<br>  SET structural_tree = (<br>    SELECT structural_tree<br>    FROM `your_project.rag_pipeline.document_trees`<br>    WHERE document_id = target_document_id<br>  );<br>  -- Step 2: NAVIGATION PHASE - LLM reasons over the tree<br>  -- The model reads the compressed JSON map and determines<br>  -- which nodes are most likely to contain the answer.<br>  SET navigation_response = (<br>    SELECT ml_generate_text_llm_result<br>    FROM ML.GENERATE_TEXT(<br>      MODEL `your_project.rag_pipeline.gemini_model`,<br>      (SELECT CONCAT(<br>        &#39;You are a document navigation expert. Given the following user query &#39;,<br>        &#39;and a structural tree representing a document&#39;&#39;s hierarchy, &#39;,<br>        &#39;identify the specific node_ids that contain the information needed to answer the query. &#39;,<br>        &#39;Consider cross-references: if the answer requires synthesizing data from &#39;,<br>        &#39;multiple sections (e.g., a main section and an appendix), include ALL relevant node_ids. &#39;,<br>        &#39;Return ONLY a JSON array of node_id strings. No explanation.\n\n&#39;,<br>        &#39;USER QUERY: &#39;, user_query, &#39;\n\n&#39;,<br>        &#39;DOCUMENT TREE:\n&#39;, TO_JSON_STRING(structural_tree)<br>      ) AS prompt),<br>      STRUCT(<br>        0.1 AS temperature,        -- Near-deterministic for navigation<br>        1024 AS max_output_tokens,<br>        TRUE AS flatten_json_output<br>      )<br>    )<br>  );<br>  -- Step 3: Parse the selected node IDs from the LLM response<br>  SET selected_node_ids = ARRAY(<br>    SELECT TRIM(JSON_VALUE(node_id))<br>    FROM UNNEST(JSON_EXTRACT_ARRAY(navigation_response)) AS node_id<br>  );<br>  -- Step 4: EXTRACTION PHASE - Fetch raw, unfragmented text for selected nodes<br>  SET retrieved_context = (<br>    SELECT STRING_AGG(<br>      CONCAT(&#39;--- Node: &#39;, node_id, &#39; ---\n&#39;, node_text),<br>      &#39;\n\n&#39;<br>    )<br>    FROM (<br>      SELECT<br>        JSON_VALUE(node, &#39;$.node_id&#39;) AS node_id,<br>        JSON_VALUE(node, &#39;$.text&#39;) AS node_text<br>      FROM<br>        `your_project.rag_pipeline.document_trees`,<br>        UNNEST(JSON_EXTRACT_ARRAY(raw_nodes, &#39;$&#39;)) AS node<br>      WHERE<br>        document_id = target_document_id<br>        AND JSON_VALUE(node, &#39;$.node_id&#39;) IN UNNEST(selected_node_ids)<br>    )<br>  );<br>  -- Step 5: SYNTHESIS PHASE - Generate final answer from precise context<br>  SET final_answer = (<br>    SELECT ml_generate_text_llm_result<br>    FROM ML.GENERATE_TEXT(<br>      MODEL `your_project.rag_pipeline.gemini_model`,<br>      (SELECT CONCAT(<br>        &#39;Answer the following question using ONLY the provided context. &#39;,<br>        &#39;Cite specific node IDs for every claim. &#39;,<br>        &#39;If the context references another section that is not provided, state that explicitly.\n\n&#39;,<br>        &#39;QUESTION: &#39;, user_query, &#39;\n\n&#39;,<br>        &#39;CONTEXT:\n&#39;, retrieved_context<br>      ) AS prompt),<br>      STRUCT(<br>        0.2 AS temperature,<br>        4096 AS max_output_tokens,<br>        TRUE AS flatten_json_output<br>      )<br>    )<br>  );<br>END;</pre><p>Let’s break down what just happened:</p><ol><li><strong>Navigation Phase</strong> — The LLM received a compressed structural map (not the full document) and <em>reasoned</em> about where the answer lives. For our pharma query, it would identify both “N003” (Drug Interactions, Section 4.2) and “N004” (Appendix F: Dosage Modifications) — following the cross-reference just like a human expert would.</li><li><strong>Extraction Phase</strong> — BigQuery fetched the <em>complete, unfragmented text</em> of those specific nodes. No chunking. The drug interaction table stays intact with its headers, footnotes, and dosage adjustment figures.</li><li><strong>Synthesis Phase</strong> — The model received focused, structurally-preserved context and generated a grounded answer with node-level citations.</li></ol><p>The entire pipeline is executed within BigQuery. The sensitive pharmaceutical data never left the governed warehouse perimeter.</p><h3>The Full Pipeline: Wiring Macro and Micro Together</h3><p>Here’s how to orchestrate both stages as a single callable procedure:</p><pre>CREATE OR REPLACE PROCEDURE `your_project.rag_pipeline.hybrid_rag_query`(<br>  IN user_query STRING,<br>  OUT final_answer STRING<br>)<br>BEGIN<br>  DECLARE target_doc_ids ARRAY&lt;STRING&gt;;<br><br>-- ============================<br>  -- MACRO STAGE: Vector Retrieval<br>  -- ============================<br>  SET target_doc_ids = ARRAY(<br>    SELECT document_id<br>    FROM (<br>      SELECT<br>        base.document_id,<br>        MIN(distance) AS best_distance<br>      FROM<br>        VECTOR_SEARCH(<br>          TABLE `your_project.rag_pipeline.document_chunks`,<br>          &#39;embeddings&#39;,<br>          (<br>            SELECT ml_generate_embedding_result AS embedding<br>            FROM ML.GENERATE_EMBEDDING(<br>              MODEL `your_project.rag_pipeline.embedding_model`,<br>              (SELECT user_query AS content),<br>              STRUCT(TRUE AS flatten_json_output)<br>            )<br>          ),<br>          top_k =&gt; 20,<br>          OPTIONS =&gt; &#39;{&quot;fraction_lists_to_search&quot;: 0.05}&#39;<br>        )<br>      GROUP BY document_id<br>      ORDER BY best_distance ASC<br>      LIMIT 3<br>    )<br>  );<br>  -- ============================<br>  -- MICRO STAGE: Structural Traversal<br>  -- ============================<br>  -- Run Micro retrieval against each candidate document<br>  -- and select the best-grounded answer<br>  CALL `your_project.rag_pipeline.micro_retrieval`(<br>    user_query,<br>    target_doc_ids[OFFSET(0)],  -- Start with best-matching document<br>    final_answer<br>  );<br>END;</pre><p>In production, you’d iterate across the top 3 documents and use a final arbitration step to select the best-grounded answer. But even this simplified version demonstrates the core pattern: <strong>vector search for breadth, structural traversal for depth</strong>.</p><h3>Looking Forward: BigQuery Graph as a Micro Stage Accelerator</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GICTAdfj2yAhlC3TUzCDvA.jpeg" /></figure><p>Here’s where things get really exciting for the future of this architecture. <strong>BigQuery Graph</strong>, currently in private preview, introduces native property graph support with the ISO-standard Graph Query Language (GQL). And it maps <em>remarkably</em> well to the structural traversal problem that the Micro stage solves.</p><h3>Why Graphs Fit the Micro Stage</h3><p>Think about what the Micro stage is actually doing. It’s navigating a hierarchical tree of nodes connected by parent-child and cross-reference relationships. That’s a graph traversal problem. Currently, we’re using an LLM to reason over a serialised JSON tree, which works, but it burns tokens and introduces latency from multiple sequential ML.GENERATE_TEXT calls.</p><p>BigQuery Graph could potentially replace or augment this with <em>native graph queries</em> that traverse document structure deterministically in milliseconds.</p><h3>What This Could Look Like</h3><p>Imagine representing your document structure as a property graph:</p><pre>-- Create node tables from the document structure<br>CREATE TABLE IF NOT EXISTS `your_project.rag_pipeline.doc_sections` (<br>  node_id        STRING NOT NULL,<br>  document_id    STRING NOT NULL,<br>  title          STRING,<br>  summary        STRING,<br>  page_range     STRING,<br>  section_text   STRING,     -- Raw text content<br>  node_type      STRING      -- &#39;chapter&#39;, &#39;section&#39;, &#39;appendix&#39;, &#39;table&#39;<br>);<br><br>CREATE TABLE IF NOT EXISTS `your_project.rag_pipeline.doc_relationships` (<br>  source_node_id  STRING NOT NULL,<br>  target_node_id  STRING NOT NULL,<br>  document_id     STRING NOT NULL,<br>  relationship    STRING    -- &#39;CONTAINS&#39;, &#39;REFERENCES&#39;, &#39;AMENDS&#39;, &#39;SUPERSEDES&#39;<br>);<br>-- Define the document as a property graph<br>CREATE PROPERTY GRAPH `your_project.rag_pipeline.document_graph`<br>  NODE TABLES (<br>    doc_sections<br>      KEY (node_id)<br>      LABEL Section<br>      PROPERTIES (title, summary, page_range, section_text, node_type)<br>  )<br>  EDGE TABLES (<br>    doc_relationships<br>      KEY (source_node_id, target_node_id)<br>      SOURCE KEY (source_node_id) REFERENCES doc_sections (node_id)<br>      DESTINATION KEY (target_node_id) REFERENCES doc_sections (node_id)<br>      LABEL RELATES_TO<br>      PROPERTIES (relationship)<br>  );</pre><p>With this graph defined, the cross-reference traversal that currently requires multiple LLM calls could be expressed as a single GQL query:</p><pre>-- Find Section 4.2 and ALL sections it cross-references<br>-- This traversal happens in milliseconds, not seconds<br>SELECT *<br>FROM GRAPH_TABLE(<br>  `your_project.rag_pipeline.document_graph`<br>  MATCH<br>    (s:Section {title: &#39;4.2 Drug Interactions&#39;})<br>    -[r:RELATES_TO {relationship: &#39;REFERENCES&#39;}]-&gt;<br>    (ref:Section)<br>  RETURN<br>    s.node_id AS source_section,<br>    s.section_text AS source_text,<br>    ref.node_id AS referenced_section,<br>    ref.title AS referenced_title,<br>    ref.section_text AS referenced_text<br>);</pre><p>Even more powerful, you could do multi-hop traversals to find <em>transitive</em> relationships:</p><pre>-- Multi-hop: Find all content reachable from a starting section<br>-- within 3 relationship hops<br>SELECT *<br>FROM GRAPH_TABLE(<br>  `your_project.rag_pipeline.document_graph`<br>  MATCH<br>    (start:Section {node_id: &#39;N003&#39;})<br>    -[r:RELATES_TO]-&gt;{1,3}<br>    (reached:Section)<br>  RETURN<br>    start.title AS origin,<br>    reached.node_id AS reachable_node,<br>    reached.title AS reachable_title,<br>    reached.summary AS reachable_summary<br>);</pre><h3>The Hybrid Graph+LLM Micro Stage</h3><p>The practical architecture would combine both approaches. Use BigQuery Graph for <em>deterministic</em> structural traversal — following explicit cross-references, resolving parent-child hierarchies, identifying related appendices — and reserve the LLM for <em>inferential</em> navigation where the relationship isn’t explicitly encoded in the document structure.</p><p>This hybrid within-a-hybrid could dramatically reduce token costs and latency in the Micro stage while maintaining the reasoning capability for ambiguous cases.</p><p>BigQuery Graph is currently in private preview, so these patterns aren’t production-ready yet. But the architectural alignment is striking: the same warehouse that stores your embeddings, runs your vector search, and hosts your LLM connections can also natively traverse your document structure as a graph. No external graph database required.</p><h3>Wrapping Up</h3><p>The Hybrid Macro-to-Micro Architecture isn’t a theoretical exercise — it’s a practical response to a real limitation that every enterprise RAG deployment eventually hits. Vector search is brilliant for finding relevant documents in a massive corpus. It’s terrible at understanding the internal structure of those documents.</p><p>By running both paradigms sequentially within BigQuery — vector search for breadth, structural traversal for depth — you get the best of both worlds without managing separate infrastructure for vector databases, graph databases, and LLM orchestration. Everything stays in the warehouse. Your security policies stay intact. And your analysts get answers they can actually trust.</p><p>The future of enterprise RAG isn’t vector <em>or</em> vectorless. It’s vector <em>then</em> vectorless. And BigQuery is the platform that makes that sequential handoff feel seamless.</p><p>Now go build something your analysts will actually want to use.</p><h3>References</h3><ul><li><a href="https://cloud.google.com/bigquery/docs/vector-search-intro"><strong>BigQuery Vector Search Documentation</strong></a> — Official guide to embeddings and vector search in BigQuery</li><li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/search_functions#vector_search"><strong>VECTOR_SEARCH Function Reference</strong></a> — Complete syntax and parameters for the VECTOR_SEARCH function</li><li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-text"><strong>ML.GENERATE_TEXT Documentation</strong></a> — Reference for calling Gemini and other LLMs from BigQuery SQL</li><li><a href="https://cloud.google.com/bigquery/docs/generate-text"><strong>AI.GENERATE_EMBEDDING Function</strong></a> — Generating embeddings natively in BigQuery</li><li><a href="https://github.com/VectifyAI/PageIndex"><strong>PageIndex GitHub Repository</strong></a> — Open-source framework for vectorless, reasoning-based RAG</li><li><a href="https://pageindex.ai/blog/pageindex-intro"><strong>PageIndex: Next-Generation Vectorless RAG</strong></a> — Technical deep dive into the PageIndex framework</li><li><a href="https://cloud.google.com/bigquery/docs/json-data"><strong>Working with JSON Data in BigQuery</strong></a> — Official documentation on BigQuery’s native JSON support</li><li><a href="https://cloud.google.com/bigquery/docs/search-intro"><strong>BigQuery Search Index Documentation</strong></a> — Full-text search indexing for text and JSON columns</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e636aff6687e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/stop-searching-start-reading-building-a-hybrid-macro-to-micro-rag-pipeline-on-bigquery-e636aff6687e">Stop Searching, Start Reading: Building a Hybrid Macro-to-Micro RAG Pipeline on BigQuery</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery Just Gave Your Data a Voice: 6 Conversational Analytics Updates That Change Everything]]></title>
            <link>https://medium.com/google-cloud/bigquery-just-gave-your-data-a-voice-6-conversational-analytics-updates-that-change-everything-a49db2e6614f?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/a49db2e6614f</guid>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[conversational-ai]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Sat, 14 Mar 2026 01:15:57 GMT</pubDate>
            <atom:updated>2026-03-14T01:15:57.047Z</atom:updated>
            <content:encoded><![CDATA[<p>How ObjectRef, BQML functions, and smarter agents are turning BigQuery into an AI-powered data analyst you can actually talk to</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yVqZxFPWd6xwmvXPBf8QOA.jpeg" /></figure><p>You know that feeling when a business user walks over to your desk (or pings you on Slack at 4:47 PM on a Friday) asking, <em>“Hey, can you quickly pull last quarter’s sales numbers broken down by region… oh, and can you also check if there’s anything weird in the January data?”</em></p><p>You sigh. “Quickly” never means quickly. There’s SQL to write, tables to join, maybe some anomaly detection model to dust off. Forty-five minutes later, you’ve got the answer — and three more follow-up questions waiting in your inbox.</p><h4><strong>What if that business user could just… ask the data directly?</strong></h4><p>That’s exactly where BigQuery’s Conversational Analytics is heading, and the latest batch of updates (currently in Preview) makes the whole experience dramatically more powerful. We’re talking about chatting with images stored in Cloud Storage, running predictive forecasts with a simple prompt, and even having the agent suggest your next question for you.</p><p>Let me walk you through all six updates, why they matter, and how you can start using them today.</p><h3>A Quick Primer: What Is Conversational Analytics in BigQuery?</h3><p>Before we dive in, let’s set some context. Conversational Analytics in BigQuery was unveiled in preview in January 2026, bringing a sophisticated AI-powered reasoning engine directly into BigQuery Studio. It’s powered by Gemini for Google Cloud.</p><p>But here’s what makes it different from slapping a chatbot on top of a database: it uses your business metadata and production logic to build trust between the user and their data. When you ask a question, the agent doesn’t just blindly generate SQL. It goes through a multi-stage workflow — reasoning, generating SQL, executing, visualizing, and summarizing — grounded in your actual schema, metadata, and custom instructions.</p><p>You create <strong>Data Agents</strong> in the Agent Catalog within BigQuery Studio. Each agent connects to specific knowledge sources (tables, views, UDFs) and can be configured with custom instructions, verified queries (golden queries), and glossary terms. Once published, these agents can be accessed through BigQuery Studio, the Conversational Analytics API, and even Looker Studio Pro.</p><p>Now let’s look at what’s new.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xdfTzxmnLhfCipXpm0SBAQ.jpeg" /></figure><h3>1. ObjectRef Support: Your Unstructured Data Just Joined the Conversation</h3><p>This one’s a big deal. Until now, conversational analytics worked great with structured, tabular data — rows and columns, the stuff BigQuery was born for. But what about the PDFs sitting in your Cloud Storage bucket? The product images? The scanned invoices?</p><p>BigQuery introduced the ObjectRef data type — a reference to any object in Cloud Storage with a URI and additional metadata — that lets you integrate unstructured data like images, documents, and audio directly into BigQuery tables.</p><p>Now, conversational analytics can work with these ObjectRef references. You can ask questions about data that lives in PDFs and images alongside your structured data, all from the same chat interface.</p><h3>How ObjectRef Works Under the Hood</h3><p>An ObjectRef value is a STRUCT containing object storage and access control metadata, including the Cloud Storage URI, object version, an authorizer (Cloud resource connection), and details like content type and file size.</p><p>There are three key functions you’ll use:</p><ul><li><strong>OBJ.MAKE_REF</strong> — Creates an ObjectRef value that points to a Cloud Storage object</li><li><strong>OBJ.FETCH_METADATA</strong> — Fetches the Cloud Storage metadata for an ObjectRef</li><li><strong>OBJ.GET_ACCESS_URL</strong> — Generates secure, time-limited signed URLs for reading or writing the object</li></ul><p>Here’s a practical example. Say you have a products table and want to add product images stored in Cloud Storage:</p><pre>-- Create a table with structured data + ObjectRef for product images<br>CREATE OR REPLACE TABLE `myproject.retail.products_enriched` AS<br>SELECT<br>  p.*,<br>  OBJ.FETCH_METADATA(<br>    OBJ.MAKE_REF(<br>      CONCAT(&#39;gs://product-images/&#39;, p.product_id, &#39;.jpg&#39;),<br>      &#39;us-central1.my-gcs-connection&#39;<br>    )<br>  ) AS product_image_ref<br>FROM `myproject.retail.products` p;</pre><p>Once this is set up, you can ask the conversational analytics agent things like:</p><blockquote>“Show me all products in the electronics category and describe what’s in their product images.”</blockquote><p>The agent can reason across both the structured product data and the unstructured image data. Behind the scenes, it uses generative AI functions with Gemini models to analyze the referenced objects. Pretty wild, right?</p><h3>2. BQML Support: Forecasting and Anomaly Detection in Plain English</h3><p>Here’s where things get <em>really</em> interesting. Conversational analytics now supports BigQuery ML functions including AI.FORECAST, AI.DETECT_ANOMALIES, and AI.GENERATE (along with variants like AI.GENERATE_BOOL, AI.GENERATE_INT, and AI.GENERATE_DOUBLE).</p><p>What does this mean practically? Instead of writing complex ML queries, you can just ask:</p><blockquote>“Forecast our daily revenue for the next 30 days.”</blockquote><blockquote>“Are there any anomalies in our website traffic over the past quarter?”</blockquote><blockquote>“Generate a sentiment score for each customer review.”</blockquote><p>Behind the scenes, the agent uses functions like AI.FORECAST to predict trends or AI.DETECT_ANOMALIES to surface outliers in real-time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HTZx5aAKqcwP4ccu7aULYA.jpeg" /></figure><h3>A Real-World Scenario: Inventory Forecasting</h3><p>Let’s say you’re a retail operations manager. You’ve got historical sales data in BigQuery, and you need to figure out how much stock to order for next month. Previously, you’d need a data scientist to build an ARIMA model, evaluate it, and generate predictions. Now?</p><p>Just type into the conversational analytics chat:</p><blockquote>“Forecast daily sales of ‘Premium Wireless Headphones’ for the next 14 days based on the last 6 months of data.”</blockquote><p>The agent generates and executes something like this behind the scenes:</p><pre>-- The agent generates this automatically from your natural language prompt<br>SELECT *<br>FROM AI.FORECAST(<br>  (SELECT<br>     DATE(order_date) AS date,<br>     SUM(quantity) AS daily_sales<br>   FROM `myproject.retail.orders`<br>   WHERE product_name = &#39;Premium Wireless Headphones&#39;<br>     AND order_date &gt;= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)<br>   GROUP BY date),<br>  horizon =&gt; 14,<br>  confidence_level =&gt; 0.95<br>);</pre><p>And for anomaly detection, you could ask:</p><blockquote>“Detect any unusual spikes or drops in daily transaction volumes over the past 90 days.”</blockquote><p>Which triggers something like:</p><pre>-- Agent-generated anomaly detection query<br>WITH daily_transactions AS (<br>  SELECT<br>    DATE(transaction_timestamp) AS date,<br>    COUNT(*) AS transaction_count<br>  FROM `myproject.payments.transactions`<br>  GROUP BY date<br>)<br>SELECT *<br>FROM AI.DETECT_ANOMALIES(<br>  (SELECT * FROM daily_transactions<br>   WHERE date &lt;= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)),<br>  (SELECT * FROM daily_transactions<br>   WHERE date &gt; DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)),<br>  data_col =&gt; &#39;transaction_count&#39;,<br>  timestamp_col =&gt; &#39;date&#39;<br>);</pre><p>This takes analytics from the era of model training to the era of conversational analytics, where a forecast that used to require SQL syntax knowledge, ARIMA parameter understanding, and model retraining schedules can now be generated with a single sentence.</p><h3>3. Chat with BigQuery Results: Your SQL Editor Gets an AI-Partner</h3><p>This is one of those features that sounds simple but changes your daily workflow. You can now start a conversation directly from query results in BigQuery Studio’s SQL editor.</p><p>Just ran a complex query and got back a results table? Instead of writing another query to dig deeper, you can now click to chat with those results. Ask follow-up questions, request different aggregations, or explore the data conversationally — all without leaving the SQL editor.</p><p>Think of it like this: you write your initial SQL, get results, and then switch to a conversational mode to explore the data interactively. It bridges the gap between the precision of SQL and the exploratory nature of natural language.</p><p>For example, after running a query that returns monthly revenue by product category:</p><blockquote>“Which category had the biggest month-over-month decline?”</blockquote><blockquote>“Show me a chart comparing the top 3 categories.”</blockquote><blockquote>“What percentage of total revenue does electronics represent?”</blockquote><p>This is particularly useful for ad-hoc exploration where you don’t want to write a dozen variations of the same query.</p><h3>4. Enhanced Partitioned Table Support: Faster Queries, Lower Costs</h3><p>If you’ve been working with BigQuery for any length of time, you know that table partitioning is essential for controlling costs and performance. The conversational analytics agent is now smart enough to take advantage of this.</p><p>The agent can optimize SQL queries by using partitioned columns such as date ranges on a date-partitioned table, which can improve query performance and reduce costs.</p><p>So when you ask, <em>“Show me last month’s revenue”</em>, the agent doesn’t scan the entire table. It automatically adds the appropriate partition filter to the WHERE clause, potentially saving you significant bytes scanned (and dollars spent).</p><p>Here’s what the agent would generate for a date-partitioned table:</p><pre>-- The agent automatically applies partition pruning<br>SELECT<br>  region,<br>  SUM(revenue) AS total_revenue,<br>  COUNT(DISTINCT customer_id) AS unique_customers<br>FROM `myproject.analytics.sales`<br>WHERE<br>  -- Agent adds partition filter automatically<br>  order_date BETWEEN &#39;2026-02-01&#39; AND &#39;2026-02-28&#39;<br>GROUP BY region<br>ORDER BY total_revenue DESC;</pre><p>Without this optimization, the agent might have scanned the entire table, potentially terabytes of data for a query that only needs one month. This is the kind of intelligence that makes conversational analytics practical for production use, not just a cool demo.</p><h3>5. Labels for Agent-Generated Queries: Finally, Visibility Into What the Agent Is Doing</h3><p>This update is all about governance, and honestly, it’s one that enterprise teams have been waiting for.</p><p>BigQuery jobs initiated by the conversational analytics agent are now labeled in BigQuery Job History, and you can identify, filter, and analyze them by referencing labels like {&#39;ca-bq-job&#39;: &#39;true&#39;}.</p><p>Why does this matter? Three key reasons:</p><p><strong>Cost monitoring and attribution.</strong> When your team is chatting away with data agents, those conversations are running SQL queries under the hood. Now you can track exactly how much compute those agent-generated queries are consuming. This is critical for teams with slot reservations or on-demand pricing.</p><p><strong>Auditing agent activity.</strong> In regulated industries — finance, healthcare, government — you need to know what queries ran, when, and who triggered them. These labels give you a clear audit trail of all agent-initiated jobs.</p><p><strong>Performance analysis.</strong> You can now identify slow or expensive agent-generated queries and use that insight to improve your agent’s configuration. Maybe you need to add verified queries for common expensive patterns, or adjust your agent’s instructions.</p><p>Here’s how you’d query the job history to analyze agent activity:</p><pre>-- Find all conversational analytics agent jobs<br>SELECT<br>  job_id,<br>  user_email,<br>  creation_time,<br>  total_bytes_processed,<br>  total_slot_ms,<br>  query<br>FROM `region-us`.INFORMATION_SCHEMA.JOBS<br>WHERE<br>  EXISTS (<br>    SELECT 1<br>    FROM UNNEST(labels) AS label<br>    WHERE label.key = &#39;ca-bq-job&#39; AND label.value = &#39;true&#39;<br>  )<br>  AND creation_time &gt; TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)<br>ORDER BY total_bytes_processed DESC;</pre><p>This gives you a clear picture of what the agent has been doing, how much it’s costing, and where there might be optimization opportunities.</p><h3>6. Suggested Next Questions (Clickable): Guided Data Exploration</h3><p>The last update is about user experience, and it’s surprisingly impactful. The agent now suggests follow-up questions that are directly clickable in the Google Cloud console.</p><p>After every response, the agent proposes relevant next questions based on the context of your conversation and the data you’re exploring. Instead of having to think about what to ask next, you can just click a suggestion and keep going.</p><p>This is a game-changer for business users who might not know what questions to ask. The agent essentially becomes a guided analytics companion, helping users discover insights they might not have thought to look for.</p><p>Imagine you ask about monthly revenue and the agent responds with the data plus three clickable suggestions:</p><ul><li><em>“How does this compare to the same period last year?”</em></li><li><em>“Which product categories drove the most growth?”</em></li><li><em>“Are there any anomalies in the recent trends?”</em></li></ul><p>Click, click, click — and you’ve gone from a simple revenue check to a comprehensive business review in under two minutes.</p><h3>Getting Started: Your First Data Agent in 5 Minutes</h3><p>Ready to try this out? Here’s the quickest path to getting hands-on:</p><p><strong>Step 1:</strong> Open BigQuery Studio in the Google Cloud Console and navigate to the <strong>Agents</strong> tab in the left panel.</p><p><strong>Step 2:</strong> Click <strong>Create Agent</strong> in the Agent Catalog. Give it a name and description relevant to your use case.</p><p><strong>Step 3:</strong> Add your knowledge sources — select the tables, views, or UDFs the agent should have access to.</p><p><strong>Step 4:</strong> (Optional but recommended) Add custom instructions, glossary terms, and verified queries to improve accuracy.</p><p><strong>Step 5:</strong> Publish the agent and start a conversation.</p><p>For programmatic access, you can also use the Conversational Analytics API with the Python SDK:</p><pre>from google.cloud import geminidataanalytics_v1alpha<br># Initialize the client<br>client = geminidataanalytics_v1alpha.DataAgentServiceClient()<br># Define your data sources<br>table_refs = geminidataanalytics_v1alpha.DatasourceReferences(<br>    bq=geminidataanalytics_v1alpha.BigQueryTableReferences(<br>        table_references=[<br>            geminidataanalytics_v1alpha.BigQueryTableReference(<br>                project_id=&quot;your-project-id&quot;,<br>                dataset_id=&quot;your-dataset&quot;,<br>                table_id=&quot;your-table&quot;<br>            )<br>        ]<br>    )<br>)<br># Create the agent context<br>context = geminidataanalytics_v1alpha.Context(<br>    system_instruction=&quot;You are a sales analytics assistant. &quot;<br>    &quot;Focus on revenue trends and customer metrics.&quot;,<br>    datasource_references=table_refs<br>)<br># Create the agent<br>agent = client.create_data_agent(<br>    parent=f&quot;projects/your-project-id/locations/us&quot;,<br>    data_agent=geminidataanalytics_v1alpha.DataAgent(<br>        display_name=&quot;Sales Analytics Agent&quot;,<br>        context=context<br>    )<br>)<br>print(f&quot;Agent created: {agent.name}&quot;)</pre><h3>Practical Applications Across Industries</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CKFru7R-asFhUaXqDVBVmQ.jpeg" /></figure><p><strong>E-Commerce</strong> Combine product images (via ObjectRef) with sales data to ask questions like <em>“Which product images correlate with higher conversion rates?”</em> or <em>“Forecast demand for our top 10 SKUs for next quarter.”</em></p><p><strong>Healthcare</strong> Reference medical imaging PDFs alongside patient records. Use anomaly detection to identify unusual patterns in lab results or patient admission rates.</p><p><strong>Financial Services</strong> Detect anomalous trading volumes, forecast cash flow, and let compliance teams audit all agent-generated queries with labeled job history.</p><p><strong>Manufacturing</strong> Analyze quality control images stored in Cloud Storage alongside defect rate data. Forecast production output and detect equipment anomalies before failures occur.</p><p><strong>Marketing</strong> Chat with campaign performance data, forecast ROI on upcoming campaigns, and let non-technical marketers explore attribution data with suggested questions.</p><h3>Wrapping Up</h3><p>These six updates to BigQuery Conversational Analytics represent a genuine shift in how we interact with data. We’ve gone from <em>“let me write a SQL query for you”</em> to <em>“let me analyze your structured and unstructured data, run predictive models, optimize for cost, track everything for compliance, and suggest what you should explore next.”</em></p><p>The combination of ObjectRef support, BQML integration, and improved governance features makes this something you can actually deploy in a production environment — not just a flashy demo.</p><p>If you’re already on BigQuery, there’s really no reason not to spin up a data agent and start experimenting. The worst that happens? You save 45 minutes on that Friday afternoon data pull.</p><p>And honestly, isn’t that worth it?</p><h3>References</h3><ul><li><a href="https://docs.cloud.google.com/bigquery/docs/conversational-analytics"><strong>Conversational Analytics Overview — BigQuery Documentation</strong></a> — Comprehensive guide to creating data agents and having conversations</li><li><a href="https://cloud.google.com/blog/products/data-analytics/introducing-conversational-analytics-in-bigquery"><strong>Conversational Analytics in BigQuery — Google Cloud Blog</strong></a> — Official launch announcement with architecture details</li><li><a href="https://cloud.google.com/blog/products/data-analytics/new-objectref-data-type-brings-unstructured-data-into-bigquery"><strong>ObjectRef Data Type — Google Cloud Blog</strong></a> — Deep dive into ObjectRef capabilities</li><li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/objectref_functions"><strong>ObjectRef Functions Reference</strong></a> — Official SQL reference for OBJ.MAKE_REF, OBJ.FETCH_METADATA, OBJ.GET_ACCESS_URL</li><li><a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-detect-anomalies"><strong>AI.DETECT_ANOMALIES Function</strong></a> — Official syntax and examples for anomaly detection</li><li><a href="https://cloud.google.com/blog/products/data-analytics/build-data-agents-with-conversational-analytics-api"><strong>Build Data Agents with Conversational Analytics API</strong></a> — Tutorial for programmatic agent creation using Python SDK</li><li><a href="https://docs.cloud.google.com/bigquery/docs/release-notes"><strong>BigQuery Release Notes</strong></a> — Latest feature updates and announcements</li><li><a href="https://codelabs.developers.google.com/ca-in-bigquery"><strong>Introduction to Conversational Analytics Codelab</strong></a> — Hands-on step-by-step tutorial</li></ul><p><strong>Tags:</strong> BigQuery, Conversational Analytics, Google Cloud, AI Agents, BigQuery ML, ObjectRef, Generative AI</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a49db2e6614f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/bigquery-just-gave-your-data-a-voice-6-conversational-analytics-updates-that-change-everything-a49db2e6614f">BigQuery Just Gave Your Data a Voice: 6 Conversational Analytics Updates That Change Everything</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[One SQL Query, Three Continents: BigQuery’s Global Queries Just Changed the Game]]></title>
            <link>https://medium.com/google-cloud/one-sql-query-three-continents-bigquerys-global-queries-just-changed-the-game-b4b79d42f205?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/b4b79d42f205</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Tue, 03 Mar 2026 04:53:50 GMT</pubDate>
            <atom:updated>2026-03-03T04:53:50.625Z</atom:updated>
            <content:encoded><![CDATA[<h4>How Google Cloud’s newest preview feature lets you JOIN tables across regions with a single SQL statement — pure zero-ETL analytics.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Bt5TCPBGrUVHYWuNB3XZ5g.jpeg" /></figure><blockquote>Imagine this: customer profiles sitting in us-central1, transaction logs in europe-west1 for GDPR compliance, and your APAC sales team’s dataset humming along in asia-southeast1. Your CFO wants a consolidated revenue report by end of day.</blockquote><p>With <strong>Global Queries</strong> in BigQuery <em>(currently in preview)</em>, the answer is simple — you write one standard SQL query that references all three regions, and BigQuery handles the cross-region orchestration for you. It figures out where the data lives, fetches only what’s needed from each region, and delivers a unified result set.</p><p>This is a big step forward for teams operating across geographies. Data stays exactly where it needs to be for compliance and sovereignty, while your analysts get the unified view they’ve always wanted — on demand, in seconds.</p><p>Let me show you what this looks like in practice.</p><h3>What Exactly Are Global Queries?</h3><p>At its core, global queries let you run a single SQL statement that references datasets stored in multiple Google Cloud regions. Behind the scenes, BigQuery does something pretty clever:</p><ol><li>It figures out which region should be the “primary” execution location (either you tell it, or it determines this automatically).</li><li>It runs sub-queries in each remote region, collecting only the data that’s actually needed.</li><li>It copies just those filtered results to the primary region.</li><li>It runs the final joined query with everything in one place.</li><li>Results come back to you like nothing unusual happened.</li></ol><p>The key detail here is step 2 — BigQuery doesn’t blindly replicate entire tables across the globe. It applies what Google calls <em>pushdowns</em>, meaning it pushes WHERE clauses and column selections down to the remote region first. So if your remote table has 500 million rows but your filter narrows it to 10,000, only those 10,000 rows travel across the wire.</p><p>That’s a massive difference both in cost and performance.</p><h3>The Real-World Scenario: GlobalCart E-Commerce</h3><p>Let’s walk through a realistic scenario to see how this works end-to-end.</p><p><strong>GlobalCart</strong> is a mid-sized e-commerce company selling electronics across three markets: North America, Europe, and Asia-Pacific. Due to data sovereignty requirements and latency optimization, their BigQuery setup looks like this:</p><ul><li><strong>us-central1</strong> — Product catalog (~50K products) and global customer master (~2M customers)</li><li><strong>europe-west1</strong> — EU transaction records (~15M rows, GDPR-compliant storage)</li><li><strong>asia-southeast1</strong> — APAC transaction records (~8M rows)</li></ul><p>The analytics team needs to answer questions like: <em>“What are our top 10 products by revenue across all markets, and which customer segments are driving that growth?”</em></p><p>With global queries, answering this is as straightforward as writing a single SQL statement — BigQuery joins data across all three regions in real time, giving the analytics team fresh, on-demand insights without any intermediate data movement or pipeline orchestration.</p><h3>Setting Things Up</h3><p>Before you can fire off cross-region queries, you need to enable the feature. This is an explicit opt-in — BigQuery won’t accidentally move your data across borders.</p><h3>Step 1: Enable Global Query Execution</h3><p>You need to enable two things: the ability to <strong>run</strong> global queries in your primary region, and the ability to <strong>read data from</strong> each remote region. Both are set at the project level using ALTER PROJECT SET OPTIONS.</p><pre>-- Enable running global queries from us-central1 (where our final query executes)<br>ALTER PROJECT `globalcart-analytics`<br>SET OPTIONS (<br>  `region-us-central1.enable_global_queries_execution` = true<br>);<br>-- Allow global queries to pull data from Europe<br>ALTER PROJECT `globalcart-analytics`<br>SET OPTIONS (<br>  `region-europe-west1.enable_global_queries_data_access` = true<br>);<br>-- Allow global queries to pull data from APAC<br>ALTER PROJECT `globalcart-analytics`<br>SET OPTIONS (<br>  `region-asia-southeast1.enable_global_queries_data_access` = true<br>);</pre><p>Give it a few minutes to propagate. These settings are project-level, so they only need to be done once.</p><h3>Step 2: Grant the Right Permission</h3><p>Running global queries requires bigquery.jobs.createGlobalQuery permission.</p><p>In the Cloud Console, navigate to <strong>IAM &amp; Admin → Roles → Create Role</strong>, add the bigquery.jobs.createGlobalQuery permission, and assign it to the people who need it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U2WLHKavdB-X-dIBNRLfmg.jpeg" /></figure><h3>Writing Your First Global Query</h3><p>Here’s the fun part. Once everything is enabled, you just write SQL — exactly as you would if all the data lived in one region.</p><h3>Query 1: Consolidated Revenue Report Across All Markets</h3><pre>-- Tell BigQuery where to execute the final query<br>SET @@location = &#39;us-central1&#39;;<br>-- Combine transactions from Europe and APAC<br>WITH all_transactions AS (<br>  SELECT<br>    order_id,<br>    customer_id,<br>    product_id,<br>    quantity,<br>    unit_price,<br>    order_date<br>  FROM `globalcart-analytics.eu_sales.transactions`<br>  WHERE order_date &gt;= &#39;2025-01-01&#39;<br>  UNION ALL<br>  SELECT<br>    order_id,<br>    customer_id,<br>    product_id,<br>    quantity,<br>    unit_price,<br>    order_date<br>  FROM `globalcart-analytics.apac_sales.transactions`<br>  WHERE order_date &gt;= &#39;2025-01-01&#39;<br>),<br>-- Join with product catalog stored in the US<br>revenue_by_product AS (<br>  SELECT<br>    p.product_name,<br>    p.category,<br>    t.order_date,<br>    SUM(t.quantity * t.unit_price) AS revenue,<br>    COUNT(DISTINCT t.order_id) AS total_orders<br>  FROM all_transactions t<br>  INNER JOIN `globalcart-analytics.product_catalog.products` p<br>    ON t.product_id = p.product_id<br>  GROUP BY p.product_name, p.category, t.order_date<br>)<br>SELECT<br>  product_name,<br>  category,<br>  SUM(revenue) AS total_revenue,<br>  SUM(total_orders) AS total_orders<br>FROM revenue_by_product<br>GROUP BY product_name, category<br>ORDER BY total_revenue DESC<br>LIMIT 10;</pre><p>That’s it. Pure SQL, spanning three regions. BigQuery pulls the filtered transaction data from Europe and APAC, ships only the columns and rows it needs to us-central1, joins everything with the product catalog, and returns your top 10 — all in a single, streamlined execution.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ds5xXVqU2j0QXRiV_ICPsA.jpeg" /></figure><h3>Query 2: Customer Segment Analysis Across Regions</h3><p>Now let’s go a bit deeper. The marketing team wants to know how different customer segments perform across markets.</p><pre>SET @@location = &#39;us-central1&#39;;<br>WITH all_transactions AS (<br>  SELECT customer_id, product_id, quantity, unit_price, &#39;EU&#39; AS market<br>  FROM `globalcart-analytics.eu_sales.transactions`<br>  WHERE order_date BETWEEN &#39;2025-01-01&#39; AND &#39;2025-12-31&#39;<br>  UNION ALL<br>  SELECT customer_id, product_id, quantity, unit_price, &#39;APAC&#39; AS market<br>  FROM `globalcart-analytics.apac_sales.transactions`<br>  WHERE order_date BETWEEN &#39;2025-01-01&#39; AND &#39;2025-12-31&#39;<br>)<br>SELECT<br>  c.customer_segment,<br>  t.market,<br>  COUNT(DISTINCT t.customer_id) AS unique_customers,<br>  ROUND(SUM(t.quantity * t.unit_price), 2) AS total_spend,<br>  ROUND(AVG(t.quantity * t.unit_price), 2) AS avg_order_value<br>FROM all_transactions t<br>INNER JOIN `globalcart-analytics.customer_master.customers` c<br>  ON t.customer_id = c.customer_id<br>GROUP BY c.customer_segment, t.market<br>ORDER BY total_spend DESC;</pre><p>Notice how we’re adding a literal string column &#39;EU&#39; and &#39;APAC&#39; as a market identifier. This is a handy pattern when you&#39;re unioning data from different regional datasets — it lets you slice your results by geography without needing a separate region lookup table.</p><h3>Query 3: Insert Results Into a Destination Table</h3><p>Here’s something worth knowing — when your global query writes results into a destination table, BigQuery automatically runs the query in the region where that table lives. You don’t even need to set @@location.</p><pre>-- BigQuery automatically routes this to us-central1<br>-- because that&#39;s where the destination table lives<br>INSERT INTO `globalcart-analytics.reporting.monthly_global_summary`<br>  (month, market, category, revenue, order_count)<br>SELECT<br>  DATE_TRUNC(t.order_date, MONTH) AS month,<br>  &#39;EU&#39; AS market,<br>  p.category,<br>  SUM(t.quantity * t.unit_price) AS revenue,<br>  COUNT(*) AS order_count<br>FROM `globalcart-analytics.eu_sales.transactions` t<br>INNER JOIN `globalcart-analytics.product_catalog.products` p<br>  ON t.product_id = p.product_id<br>WHERE t.order_date &gt;= &#39;2025-01-01&#39;<br>GROUP BY month, market, p.category;</pre><p>This is great for building scheduled reporting pipelines that pull from multiple regions into a centralized summary table — clean, direct, and fully automated.</p><h3>Best Practices for Getting the Most Out of Global Queries</h3><p>Global queries are powerful out of the box, and following a few best practices will help you maximize their efficiency:</p><p><strong>Optimize your cost footprint</strong> — You’re paying for compute in each region touched by the query, plus data transfer costs between regions, plus 8 hours of temporary storage in the primary region for the replicated data. The great news is that BigQuery’s WHERE clause pushdowns significantly reduce what gets transferred — so filtering aggressively on remote tables keeps costs lean.</p><p><strong>Choose your primary location wisely</strong> — Run the query in the region where the <em>most</em> data lives. If your EU transactions dataset is 15M rows and your product catalog is 50K rows, run the query in europe-west1 so only the small catalog gets transferred. This flips our earlier examples, but the principle is simple: minimize what crosses the wire.</p><p><strong>Start small, then scale up</strong> — A great workflow is to validate your query logic on a subset of data first, then expand to full production tables. This lets you confirm the results look right and gives you a clear picture of the data transfer costs before running at scale.</p><p><strong>Keep remote table references clean</strong> — Stick to simple column types in your WHERE clauses on remote tables. BigQuery&#39;s pushdown optimizer works best when it can clearly determine what to filter remotely.</p><p><strong>Governance is built in</strong> — The feature is disabled by default and requires explicit opt-in at both the project and user level. Your existing VPC Service Controls are respected, so data won’t move in ways that violate your security policies.</p><h3>Why This Matters</h3><p>Global queries unlock something genuinely exciting for multi-region organizations: the ability to query data exactly where it lives, when you need it, without moving or duplicating anything upfront. For companies operating under data residency regulations — which is increasingly the norm — this means you get full compliance <em>and</em> full analytical power at the same time.</p><p>The feature is currently in preview, with continued enhancements on the way as it progresses toward general availability. But even today, for use cases like consolidated reporting, ad-hoc cross-regional analysis, and periodic summary generation, global queries are already simplifying how multi-region BigQuery architectures get designed.</p><p>If your data lives across regions, you now have a direct path to unified analytics — and it starts with a single SELECT statement.</p><h3>References</h3><ul><li><a href="https://docs.cloud.google.com/bigquery/docs/global-queries"><strong>BigQuery Global Queries Documentation</strong></a> — Official guide covering enablement, permissions, pricing, and query execution</li><li><a href="https://cloud.google.com/blog/products/data-analytics/new-global-queries-in-bigquery-span-data-from-multiple-regions"><strong>Google Cloud Blog: Global Queries Announcement</strong></a> — Product announcement with architecture overview and customer testimonial</li><li><a href="https://cloud.google.com/bigquery/pricing"><strong>BigQuery Pricing</strong></a> — Detailed breakdown of compute, storage, and data transfer costs</li></ul><p><strong>Tags:</strong> BigQuery · Google Cloud · Global Queries · Cross-Region Analytics · SQL · Data Engineering · Cloud Data Warehouse</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b4b79d42f205" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/one-sql-query-three-continents-bigquerys-global-queries-just-changed-the-game-b4b79d42f205">One SQL Query, Three Continents: BigQuery’s Global Queries Just Changed the Game</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Just Got a Brain: Introducing BigQuery’s Native AI Functions]]></title>
            <link>https://medium.com/google-cloud/sql-just-got-a-brain-introducing-bigquerys-native-ai-functions-7847778a1a8e?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/7847778a1a8e</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[generative-ai]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Sat, 15 Nov 2025 00:33:18 GMT</pubDate>
            <atom:updated>2025-11-15T00:33:18.967Z</atom:updated>
            <content:encoded><![CDATA[<h4>How to filter, rank, and classify data using Gemini models directly inside your queries — no prompt engineering required.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bP9d7GYNF0Gw6J4xj1HgGg.png" /><figcaption>Generated by AI</figcaption></figure><p><em>Let’s be honest:</em> for years, there has been a massive disconnect between where our data lives (databases) and where the “magic” happens (AI models).</p><p>If you wanted to analyze the <em>sentiment</em> of a customer review or classify a product image, you usually had to build a complex ETL pipeline. You’d export the data, push it through a Python script, call an API, wait for the rate limits, format the JSON, and finally shove it back into a table. It was brittle, expensive, and frankly, a headache.</p><p>BigQuery has been chipping away at this wall with BQML, but today, they’ve smashed right through it.</p><p>I’m excited to dive into the <strong>BigQuery-managed AI functions</strong>. We aren’t talking about generic text generation here. We are talking about purpose-built SQL functions — <strong>AI.IF</strong>, <strong>AI.SCORE</strong>, and <strong>AI.CLASSIFY</strong> — that allow you to treat Gemini’s reasoning capabilities just like any other logical operator.</p><p>Whether you are a data engineer tired of managing external scripts or an analyst wanting to query “intent” rather than keywords, this is going to change your workflow.</p><h3>Why This Matters Now</h3><p>We are in a weird phase of the GenAI hype cycle. Everyone knows LLMs are powerful, but “productionizing” them on large datasets is notoriously difficult.</p><p>The two biggest blockers? <strong>Complexity</strong> and <strong>Performance</strong>.</p><blockquote>Crafting the perfect prompt to get a consistent “True/False” or a JSON list from an LLM is an art form. Furthermore, running an LLM on a million rows of data is usually a recipe for a timeout (or a bankrupting cloud bill).</blockquote><p>These new BigQuery functions address both:</p><ol><li><strong>No Prompt Tuning:</strong> The functions are pre-wired. You don’t write prompts; you pass arguments.</li><li><strong>Built-in Optimization:</strong> BigQuery handles the heavy lifting. It optimizes the prompt structure for Gemini and, more importantly, the <strong>query planner</strong> knows how to handle these functions to minimize costs and latency.</li></ol><h3><strong>Meet the Trio</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G4JpeY0hmkPoEpeQyBDxNw.png" /><figcaption>Generate by AI</figcaption></figure><p>These three functions (formerly known as the AI Query Engine) operate directly on your data — text, images, or multimodal.</p><h4>1. AI.IF: The Semantic Filter</h4><p>Standard SQL WHERE clauses rely on exact matches or RegEx. AI.IF brings semantic understanding to your filters and joins. It evaluates natural language conditions and returns a boolean.</p><p><strong>Think of it as:</strong> “Select rows where <em>[Concept]</em> is true,” rather than “Select rows where column equals string.”</p><pre>SELECT title<br>FROM bbc_news.fulltext<br>WHERE AI.IF((&quot;News related to Google: &quot;, body))</pre><h4>2. AI.CLASSIFY: The Organizer</h4><p>Grouping unstructured data is a nightmare. AI.CLASSIFY takes input (text or images) and maps it to a specific set of categories you define. This makes GROUP BY clauses on unstructured data possible.</p><p><strong>Think of it as:</strong> An automatic dynamic categorization engine.</p><pre>SELECT<br>  AI.CLASSIFY(body, categories =&gt; [&#39;tech&#39;, &#39;sport&#39;, &#39;business&#39;]) AS category,<br>  COUNT(*)<br>FROM bbc_news.fulltext<br>GROUP BY category</pre><h4>3. AI.SCORE: The Ranker</h4><p>Sometimes you don’t want a binary “yes/no” or a category. You want nuance. AI.SCORE ranks rows based on how well they match a natural language criteria, returning a float value.</p><p><strong>Think of it as:</strong> A “vibe check” for your data, sorted by intensity.</p><pre>SELECT review,<br>  AI.SCORE((&quot;Rate sentiment 1-10:&quot;, review)) AS score<br>FROM imdb.reviews<br>ORDER BY score DESC</pre><h3>Use Case: Transforming Retail Logs into Customer Insights</h3><p>Let’s stop looking at snippets and build something real.</p><p>For this example, we are using a raw <strong>Retail Sales Dataset</strong>. It contains transaction logs with columns like Transaction ID, Date, Customer ID, Gender, Age, Product Category, and Total Amount.</p><p><strong>The Challenge:</strong> The data tells us <em>what</em> happened (e.g., A 34-year-old Male bought ‘Beauty’ products for $150), but it doesn’t tell us <em>who</em> they are or the <em>context</em> of the purchase.</p><ol><li>We want to segment customers into “Marketing Personas” without writing a 100-line rule engine.</li><li>We want to identify “Holiday Rush” purchases based on dates and categories.</li><li>We want to rank transactions by “Luxury Potential” to target high-net-worth individuals.</li></ol><h3>Step 1: Automating Customer Personas with AI.CLASSIFY</h3><p>Usually, assigning a “Persona” requires complex logic: <em>“If Age &lt; 25 and Spend &gt; 500 is Gen Z Spender, but if Age &gt; 50…”</em></p><p>With AI.CLASSIFY, we simply feed the demographics and spending data to the model and let it categorize the customer for us.</p><pre>SELECT<br>  `Customer ID` AS ID,<br>  Age,<br>  Gender,<br>  `Product Category` AS Category,<br>  `Total Amount` AS Amount,<br>  AI.CLASSIFY((CAST(Age as STRING), Gender, `Product Category`, CAST(`Total Amount` AS STRING)), categories  =&gt; ARRAY[&quot;Trend Hunter&quot;, &quot;Tech Enthusiast&quot;,<br>    &quot;Budget Shopper&quot;, &quot;Luxury Connoisseur&quot;, &quot;Casual Buyer&quot;],  connection_id =&gt; &#39;us.VertexAI-External&#39;) AS marketing_persona<br>FROM<br>  dataset.retail_sales_dataset<br>LIMIT 10; </pre><p><strong>The Result:</strong></p><ul><li>BigQuery looks at <strong>Customer 453</strong> (Female, 26, bought Clothing for $1000) and tags her as a <strong>“Luxury Connoisseur”</strong>.</li><li>It looks at <strong>Customer 605</strong> (Male, 37, bought Electronics for $1000) and tags him as a <strong>“Tech Enthusiast”</strong>.</li></ul><p>We just enriched our raw logs with qualitative marketing segments in a single query.</p><h3>Step 2: Contextual Filtering with AI.IF</h3><p>Our marketing team wants to send a “Post-Holiday” retention email. They need to find transactions that likely happened during a holiday rush context.</p><p>A standard SQL query would need a list of every holiday date. AI.IF understands calendars and context natively.</p><pre>SELECT <br>    `Transaction ID`,<br>    Date,<br>    `Product Category`,<br>    `Total Amount`<br>FROM dataset.retail_sales_dataset<br>WHERE <br>    -- We ask a semantic question about the date and category<br>    AI.IF(<br>        (&quot;Is this transaction date likely part of a major winter holiday shopping season?&quot;, cast(Date as STRING)),  connection_id =&gt; &#39;us.VertexAI-External&#39;<br>    )<br>    AND `Total Amount` &gt; 100;</pre><p><strong>The Result:</strong></p><ul><li>The query automatically filters for dates like <strong>2023–11–24</strong> (Black Friday window) and <strong>2023–12–26</strong> (Boxing Day), ignoring transactions from random dates in February or April. It understands “Winter Season” without us hard-coding BETWEEN ‘2023–11–01’ AND ‘2023–12–31’.</li></ul><h3>Step 3: Ranking Value with AI.SCORE</h3><p>We want to identify “Splurge” purchases to offer a premium loyalty program. Sorting by price isn’t enough — spending $50 on “Beauty” is different than spending $50 on “Electronics.” We need a score that understands the <em>relative</em> value of the purchase within its category.</p><pre>SELECT <br>    `Transaction ID`,<br>    `Product Category`,<br>    `Price per Unit`,<br>    `Total Amount`,<br>    -- Score how &#39;luxurious&#39; or high-end this purchase feels<br>    AI.SCORE(<br>        (&quot;Rate this purchase on a &#39;Splurge&#39; scale of 1-10 based on the item category and price per unit.&quot;, `Product Category`, cast(`Price per Unit` as STRING)),  connection_id =&gt; &#39;us.VertexAI-External&#39;<br>    ) AS splurge_score<br>FROM dataset.retail_sales_dataset<br>ORDER BY splurge_score DESC<br>LIMIT 5;</pre><p><strong>What happens here?</strong><br>The output confirms that the model applies <strong>category-specific reasoning</strong> rather than just sorting by raw numbers.</p><p><strong>1. The High Rollers (Scores 9.0–10.0)</strong></p><p>In the top results, we see purchases of <strong>$500</strong> receiving nearly perfect scores. However, notice the nuance:</p><ul><li><strong>Beauty Products at $500 receive a 10.0:</strong> The model understands that $500 is an exceptionally high price for a single beauty item (likely a luxury device or premium skincare), representing the absolute ceiling of that category.</li><li><strong>Electronics at $500 receive a 9.0:</strong> While $500 is expensive, the model likely knows that electronics pricing extends into the thousands (e.g., high-end laptops), so it holds back the perfect 10.</li></ul><p><strong>2. The Budget Buys (Score 1.0)</strong></p><p>At the bottom of the list, the model consistently assigns a <strong>1.0</strong> to items priced at <strong>$25 — $30</strong> across all categories. Whether it is a t-shirt (“Clothing”) or a cable (“Electronics”), Gemini correctly identifies these as entry-level, functional purchases with zero “splurge” factor.</p><p><strong>Why this beats standard SQL:</strong><br>A standard ORDER BY Price would mix these categories indiscriminately. AI.SCORE normalizes the value, allowing you to find the “top tier” customers across <em>every</em> category, regardless of the raw price point.</p><h3>Proven by SemBench</h3><p>To validate this, Google helped construct <strong>SemBench</strong>, a systematic benchmark for semantic query engines.</p><p>In recent tests (paper-2025–10–01), BigQuery (using Gemini 2.5 Flash) was plotted against other systems like LOTUS and Palimpzest. The results highlighted BigQuery’s efficiency:</p><ul><li><strong>Latency:</strong> BigQuery averaged <strong>42.2s</strong> for complex workloads (compared to hundreds of seconds for others).</li><li><strong>Cost:</strong> It remained highly cost-effective (averaging <strong>$0.55</strong> for the benchmark workload) while maintaining high quality/accuracy.</li></ul><p><em>Note: SemBench emphasizes the trade-offs between accuracy, cost, and efficiency. BigQuery sits comfortably on the Pareto frontier, balancing these better than many standalone systems.</em></p><h3>Conclusion</h3><p>The gap between “Data” and “AI” is closing fast. With AI.IF, AI.SCORE, and AI.CLASSIFY, BigQuery isn’t just storing your data anymore — it’s understanding it.</p><p>We can now move from asking “What data matches this string?” to “What data matches this <em>intent</em>?” And doing that within the secure, governed, and scalable environment of BigQuery is a massive leap forward.</p><p><strong>Ready to try it?</strong><br>Check out the <a href="https://www.google.com/url?sa=E&amp;q=https%3A%2F%2Fgithub.com%2FGoogleCloudPlatform%2Fgenerative-ai%2Fblob%2Fmain%2Fgemini%2Fuse-cases%2Fapplying-llms-to-data%2Fbigquery_ai_operators.ipynb">GitHub Notebook</a> to grab the code samples and run them in your own project.</p><p><em>Have you tried the new AI functions yet? Drop a comment below on how you’re using semantic filtering in your pipelines!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7847778a1a8e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/sql-just-got-a-brain-introducing-bigquerys-native-ai-functions-7847778a1a8e">SQL Just Got a Brain: Introducing BigQuery’s Native AI Functions</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ How I Built a Chrome Extension to Tutor My Child (With Zero Coding Experience!)]]></title>
            <link>https://medium.com/google-cloud/how-i-built-a-chrome-extension-to-tutor-my-child-with-zero-coding-experience-d4617c3f969d?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/d4617c3f969d</guid>
            <category><![CDATA[chrome]]></category>
            <category><![CDATA[chrome-extension]]></category>
            <category><![CDATA[education]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Tue, 19 Aug 2025 04:50:09 GMT</pubDate>
            <atom:updated>2025-08-19T04:50:09.337Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tp7GDGxhXQFEuQPBH6uDdA.png" /></figure><p>As parents, we all want to give our kids the best shot at success — especially in those <em>pivotal exams</em> like the <strong>11+</strong>. But let me be honest: between work, daily responsibilities, and household chaos, I often felt I wasn’t giving enough support when it came to tricky study moments.</p><p>Some Maths questions needed breaking down step by step. Some English, Verbal, or Non-Verbal reasoning challenges just didn’t “click” right away. And while I tried my best to help in the evenings, I wanted those hours to be focused on <strong>revision and bonding</strong>, not untangling the day’s academic roadblocks from scratch.</p><p>That was the lightbulb moment 💡. What if I could <em>create something</em> that helped my child become more independent and confident while studying?</p><p>And so, my little EdTech adventure began.</p><h3>From Parent to Accidental Developer</h3><p>Here’s the thing: I’m <em>not</em> a professional programmer. In fact, I’d call myself “code-curious” at best. But I had one powerful ally — <strong>Gemini Code Assist</strong>.</p><p>This AI tool guided me through building my very first <strong>Chrome Extension</strong>. It was like having a patient mentor who explained each step, turned my foggy ideas into code, and never judged when I messed up.</p><p>The result? An extension that takes any tricky question — Maths, English, Verbal, or otherwise — and provides <strong>step-by-step guided explanations</strong> so learning suddenly feels less intimidating.</p><h3>Why Google Gemini Made This Possible</h3><p>The secret ingredient behind it all was <strong>Google Gemini</strong>. With the <strong>free Gemini API Key from Google AI Studio</strong>, I was able to plug advanced AI into my extension without spending a penny.</p><p>And here’s where the magic happens: Whenever my child needs to go deep on a specific concept, <strong>Gemini with Guided Learning</strong> steps in like a 24/7 tutor. It doesn’t just throw answers; it <em>teaches</em>, breaking things down in simple, digestible ways.</p><p>This meant two major wins:</p><ol><li>My child could immediately get unstuck on tough questions without waiting for me.</li><li>By evening, we were revising with confidence and actually enjoying the process — no more frustrated sighs or mini-meltdowns.</li></ol><h3>A Solution Built for More Than Just My Family</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/370/1*dtdNNRYLZJC-4bzJIf1Vwg.png" /></figure><p>What began as a “survival hack” for my household ended up becoming something bigger. I realized this could help countless other parents walking the same road.</p><p>That’s why I made the extension public:</p><ul><li>📂 Code published on GitHub 👉 <a href="https://github.com/gargravish/LearnSphere-Extension">LearnSphere Extension</a></li><li>🛒 Submitted to the <strong>Google Web Store</strong> (currently awaiting approval, so soon it’ll be just one click to install).</li></ul><p>For something I never imagined I could build, it’s incredibly rewarding to think this might help other kids feel more independent in their learning journey — and help parents reclaim some sanity in the process.</p><h3>The Economics of AI + Parenting</h3><p>One of my favorite parts? This entire solution is <strong>free to run</strong>. No hidden subscription fees. No expensive tutoring contracts. Just clever integration of:</p><ul><li>🛠️ <strong>Gemini Code Assist</strong> → to build the thing.</li><li>🤖 <strong>Google Gemini API Key from AI Studio</strong> → to power it.</li></ul><p>It’s an empowering reminder that today’s tools can level the playing field. You don’t need to be a coder. You don’t need a big budget. You just need a problem worth solving and the curiosity to take that first step.</p><h3>Reflections: Lessons Learned</h3><p>If I had to distill my journey into a few takeaways, they’d be:</p><ul><li><strong>AI lowers the barrier to entry.</strong> Even as a novice, code stopped feeling scary when Gemini guided me.</li><li><strong>Free tools can have huge impact.</strong> The economics of this project make it accessible to almost anyone.</li><li><strong>Solving real problems is the best motivator.</strong> This wasn’t about “learning to code” — it was about helping my child (and now potentially other parents too).</li><li><strong>Sharing multiplies the impact.</strong> Putting it on GitHub and the Web Store means what started in my home might ripple outward.</li></ul><h3>What’s Next?</h3><p>Right now, I’m waiting for the Chrome Web Store approval. Once that’s live, it will be simpler than ever for parents to install in one click.</p><p>In the meantime, if you’re a parent navigating the rollercoaster of 11+ prep — or just curious about how AI can transform everyday learning — I’d love for you to check out the repo and share your feedback.</p><p>If I can build something like this with zero prior programming background, I can’t help but wonder… what could <strong>you</strong> build with the right AI tools in your corner?</p><p>✨ Here’s to making learning less stressful, more empowering, and maybe even a little fun.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d4617c3f969d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/how-i-built-a-chrome-extension-to-tutor-my-child-with-zero-coding-experience-d4617c3f969d">🚀 How I Built a Chrome Extension to Tutor My Child (With Zero Coding Experience!)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Pattern Matching with SQL: Real-Time Anomaly Detection Using BigQuery’s MATCH_RECOGNIZE]]></title>
            <link>https://medium.com/google-cloud/pattern-matching-with-sql-real-time-anomaly-detection-using-bigquerys-match-recognize-c35124bf35c1?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/c35124bf35c1</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[fraud-detection]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Tue, 29 Jul 2025 02:22:39 GMT</pubDate>
            <atom:updated>2025-07-29T02:22:39.550Z</atom:updated>
            <content:encoded><![CDATA[<p>You know the drill. The fraud team pings you:<br><em> “Can we get a report of users who start with normal transactions, suddenly spike their activity, and then go radio silent?”</em></p><p>They make it sound so obvious. But that kind of pattern isn’t about individual rows — it’s about how rows <em>relate</em> over time. You don’t just want data. You want to see behavior.</p><h3><strong>Enter: MATCH_RECOGNIZE.</strong></h3><p>BigQuery’s MATCH_RECOGNIZE clause is SQL’s answer to behavioral pattern detection. Think of it as regular expressions for rows instead of strings. You describe a sequence of events, and BigQuery pulls out every matching stretch of time—no more wrestling with window functions or recursive CTEs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QASh49GEmMmehKF1kBBu-g.png" /><figcaption>Co-Created by Author and AI</figcaption></figure><h3>🎯 Use Case: Detecting Suspicious Banking Behavior</h3><p>Let’s get real. Imagine you work at a neobank. You want to catch this pattern:</p><ul><li>A customer makes several small deposits (maybe testing the waters)</li><li>Suddenly, they make a large withdrawal (cue dramatic music)</li><li>All this happens within a short time window</li></ul><p>This is classic “bust-out” fraud: build trust, then cash out big and vanish. You want to catch it <em>before</em> the money’s gone.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0PvL-Nmil_FfOwxshhMBnA.png" /><figcaption>Created by AI</figcaption></figure><h3>👀 What’s MATCH_RECOGNIZE Really Doing?</h3><p>Instead of asking “what’s in my data?”, you’re asking “how do these rows <em>unfold</em> over time?”</p><p>Here’s the magic:</p><pre>MATCH_RECOGNIZE (  <br>  PARTITION BY customer_id  <br>  ORDER BY transaction_date  <br>  MEASURES  <br>    FIRST(small_deposit.transaction_date) AS first_small_deposit_date,  <br>    LAST(large_withdrawal.transaction_date) AS large_withdrawal_date,  <br>    LAST(large_withdrawal.amount) AS large_withdrawal_amount,  <br>    DATE_DIFF(LAST(large_withdrawal.transaction_date), FIRST(small_deposit.transaction_date), DAY) AS days_between  <br>  AFTER MATCH SKIP PAST LAST ROW  <br>  PATTERN (small_deposit{3,} large_withdrawal)  <br>  DEFINE  <br>    small_deposit AS transaction_type = &#39;deposit&#39; AND amount &lt; 500,  <br>    large_withdrawal AS transaction_type = &#39;withdrawal&#39; AND amount &gt; 10000  <br>)  <br>WHERE days_between &lt;= 30;</pre><p><strong>What’s happening here?</strong></p><ul><li>PARTITION BY slices data by customer</li><li>ORDER BY keeps events in time order</li><li>PATTERN says: at least 3 small deposits, then a big withdrawal</li><li>DEFINE gives meaning to those labels</li><li>MEASURES extracts the juicy details: when it started, when it ended, how much was withdrawn, and how many days it took</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ntp2xWydZiCmL_rmdEsw1g.jpeg" /><figcaption>Co-Created by Author &amp; AI</figcaption></figure><h3>💡 Real-World Example: “Bust-Out” Fraud Detection</h3><p>Let’s see it in action. Here’s a working code snippet you can run in BigQuery:</p><pre>WITH transactions AS (  <br>  SELECT &#39;cust005&#39; AS customer_id, DATE(&#39;2024-03-01&#39;) AS transaction_date, &#39;deposit&#39; AS transaction_type, 50.00 AS amount UNION ALL  <br>  SELECT &#39;cust005&#39;, DATE(&#39;2024-03-07&#39;), &#39;deposit&#39;, 50.00 UNION ALL  <br>  SELECT &#39;cust456&#39;, DATE(&#39;2025-01-12&#39;), &#39;deposit&#39;, 400.00 UNION ALL  <br>  SELECT &#39;cust005&#39;, DATE(&#39;2024-03-08&#39;), &#39;deposit&#39;, 50.00 UNION ALL  <br>  SELECT &#39;cust789&#39;, DATE(&#39;2025-01-18&#39;), &#39;withdrawal&#39;, 12000.00 UNION ALL  <br>  SELECT &#39;cust005&#39;, DATE(&#39;2024-03-08&#39;), &#39;withdrawal&#39;, 5000.00 UNION ALL  <br>  SELECT &#39;cust456&#39;, DATE(&#39;2025-02-01&#39;), &#39;deposit&#39;, 100.00 UNION ALL  <br>  SELECT &#39;cust456&#39;, DATE(&#39;2025-02-05&#39;), &#39;deposit&#39;, 250.00 UNION ALL  <br>  SELECT &#39;cust456&#39;, DATE(&#39;2025-02-10&#39;), &#39;withdrawal&#39;, 5000.00  <br>)  <br>SELECT  <br>  customer_id,  <br>  first_small_deposit_date,  <br>  large_withdrawal_date,  <br>  large_withdrawal_amount,  <br>  days_between  <br>FROM transactions  <br>MATCH_RECOGNIZE (  <br>  PARTITION BY customer_id  <br>  ORDER BY transaction_date  <br>  MEASURES  <br>    FIRST(small_deposit.transaction_date) AS first_small_deposit_date,  <br>    LAST(large_withdrawal.transaction_date) AS large_withdrawal_date,  <br>    LAST(large_withdrawal.amount) AS large_withdrawal_amount,  <br>    DATE_DIFF(LAST(large_withdrawal.transaction_date), FIRST(small_deposit.transaction_date), DAY) AS days_between  <br>  AFTER MATCH SKIP PAST LAST ROW  <br>  PATTERN (small_deposit{3,} large_withdrawal)  <br>  DEFINE  <br>    small_deposit AS transaction_type = &#39;deposit&#39; AND amount &lt; 500,  <br>    large_withdrawal AS transaction_type = &#39;withdrawal&#39; AND amount &gt;= 5000  <br>)  <br>WHERE days_between &lt;= 30;</pre><p><strong>What do you get?</strong><br> A list of customers who made at least three small deposits, then a big withdrawal, all within 30 days. That’s your “bust-out” pattern, served up on a SQL platter.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k9Jc50uBIqwl4l27mlIDlA.jpeg" /></figure><h3>🛠 Under the Hood: What You Need to Know</h3><ul><li><strong>MEASURES</strong>: Extract and name key values per match</li><li><strong>DEFINE</strong>: Map symbolic names to conditions — like small_deposit or large_withdrawal</li><li><strong>PATTERN</strong>: Use regex-like syntax to express row sequences (e.g., A+ B C?)</li><li><strong>AFTER MATCH SKIP</strong>: Controls overlapping — PAST LAST ROW finds non-overlapping matches</li><li><strong>MATCH_NUMBER()</strong> and <strong>CLASSIFIER()</strong>: Give you powerful tracking within matches</li></ul><h3>🧠 Why It Matters</h3><p>Normally, you’d need to write gnarly self-joins, window functions, or recursive CTEs to catch these behavioral shifts. With MATCH_RECOGNIZE, you just <em>describe</em> what you want and let BigQuery do the heavy lifting.</p><p>This isn’t just handy. It’s transformational.</p><p>You can use this for:</p><ul><li>Fraud detection</li><li>Customer journey segmentation</li><li>Session boundary detection</li><li>Anomaly tracking in sensor logs</li><li>Real-time behavioral analytics</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PgDEPz5HB1vQEbiYF1Scfw.jpeg" /><figcaption>Co-Created by Author &amp; AI</figcaption></figure><h3>⚡ Pro Tips</h3><ul><li>Use PARTITION BY to scale across customers</li><li>Keep PATTERN simple: prefer A+ over A{,100}</li><li>Beware: large partitions can blow up memory</li><li>Add OPTIONS (use_longest_match = TRUE) if you want the widest match possible</li></ul><h3>🚀 Final Thought</h3><p>The MATCH_RECOGNIZE clause brings event stream logic straight into SQL. It’s declarative, expressive, and ridiculously powerful for data teams that live in the messiness of sequences, gaps, and behaviors—not just static snapshots.</p><h3><strong>Resources</strong></h3><p><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#match_recognize_clause">Query syntax | BigQuery | Google Cloud</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c35124bf35c1" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/pattern-matching-with-sql-real-time-anomaly-detection-using-bigquerys-match-recognize-c35124bf35c1">Pattern Matching with SQL: Real-Time Anomaly Detection Using BigQuery’s MATCH_RECOGNIZE</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Google Cloud Data Insights: Key Updates and Announcements]]></title>
            <link>https://garg-ravish.medium.com/google-cloud-data-insights-key-updates-and-announcements-a5448eedb550?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/a5448eedb550</guid>
            <category><![CDATA[genai]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[gcp]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Tue, 03 Jun 2025 10:16:15 GMT</pubDate>
            <atom:updated>2025-06-03T10:16:15.104Z</atom:updated>
            <content:encoded><![CDATA[<p>(April 2025 — May 2025)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2OenvudMa-K3pt2I67ByGQ.png" /><figcaption>Generated by AI</figcaption></figure><p>Hello fellow data enthusiasts and cloud architects! It’s that time again to dive into the latest happenings across the Google Cloud Data Platform, and the recent waves of announcements from April and May 2025 brought a wealth of updates designed to make your data workflows even more powerful, flexible, and integrated. From significant leaps in AI/ML capabilities within BigQuery and Vertex AI to streamlined orchestration and enhanced governance features, let’s unpack the key developments that landed recently.</p><blockquote>Grab your favorite beverage, and let’s explore the key announcements!</blockquote><h3>BigQuery: Integrating AI, Enhancing Development, and Boosting Performance</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YNhGDUWpAAA4wUA5yG5A2Q.png" /><figcaption>Generated by AI</figcaption></figure><p>BigQuery continues its evolution as the cornerstone of the Google Cloud Data Platform, with several key announcements recently enhancing its capabilities for both development and performance.</p><ul><li>Python code defined in <a href="https://cloud.google.com/bigquery/docs/dataframes-dbt">BigQuery DataFrames directly using the </a><a href="https://cloud.google.com/bigquery/docs/dataframes-dbt">dbt-bigquery adapter, now available in preview</a>. This bridges the gap, allowing you to leverage the familiar dbt workflow with the flexibility of Python processing directly within BigQuery.</li><li>A preview feature now allows you to use your standard <a href="https://cloud.google.com/bigquery/docs/schedule-pipelines#create-schedule">Google Account user credentials for authorizing the creation, scheduling, and running of pipelines</a>, as well as scheduling notebooks and data preparations.</li><li><a href="https://cloud.google.com/bigquery/docs/use-spark">Ability to create a serverless Spark session</a> and execute PySpark code within a BigQuery notebook recently became generally available.</li><li>Column metadata indexing for both <a href="https://cloud.google.com/bigquery/docs/metadata-indexing-managed-tables">BigQuery tables</a> and <a href="https://cloud.google.com/bigquery/docs/metadata-caching-external-tables">external tables</a> has also reached general availability.</li><li>You can now use BigQuery and BigQuery DataFrames to enable multimodal analysis, transformation, and data engineering (ELT) workflows in both SQL and Python. Use multimodal data features to do the following:<br> - Integrate unstructured data into standard tables by using <a href="https://cloud.google.com/bigquery/docs/analyze-multimodal-data#objectref_values">ObjectRef</a> values, and then work with this data in analysis and transformation workflows by using <a href="https://cloud.google.com/bigquery/docs/analyze-multimodal-data#objectrefruntime_values">ObjectRefRuntime</a> values.<br> - Use generative AI to analyze multimodal data and generate embeddings by using <a href="https://cloud.google.com/bigquery/docs/analyze-multimodal-data#generative_ai_functions">BigQuery ML SQL functions</a> or <a href="https://cloud.google.com/bigquery/docs/analyze-multimodal-data#generative_ai_methods">BigQuery DataFrames methods</a> with Gemini and multimodal embedding models.<br> - <a href="https://cloud.google.com/bigquery/docs/analyze-multimodal-data#multimodal_dataframes">Create multimodal DataFrames</a> in BigQuery DataFrames, and then use <a href="https://cloud.google.com/bigquery/docs/analyze-multimodal-data#object_transformation_methods">object transformation methods</a> to transform images and chunk PDF files.<br> - <a href="https://cloud.google.com/bigquery/docs/multimodal-data-sql-tutorial">Use Python user-defined functions (UDFs)</a> to transform images and chunk PDF files.</li></ul><h3>Dataproc Serverless: Simplifying Spark and Cost Optimization</h3><ul><li>Spark on BigQuery is GA now. This feature lets you create a serverless Spark session directly within a BigQuery Studio notebook, significantly simplifying the process of creating, running, and testing Spark jobs by tightly integrating them with your BigQuery environment.</li></ul><h3>Cloud Composer: Orchestration Enhancements and Migration Paths</h3><ul><li>For developers working locally, the <a href="https://cloud.google.com/composer/docs/composer-3/run-local-airflow-environments">Composer Local Development CLI</a> tool is now available in Cloud Composer 3 environments.</li><li>Monitoring capabilities for Cloud Composer 3 environments have been enhanced with <a href="https://cloud.google.com/composer/docs/composer-3/monitor-environments#dag-processor-metrics">new metrics</a> available that show the number of active Airflow components, including schedulers, DAG processors, triggerers, and web servers.</li><li>Data lineage capabilities in Cloud Composer now universally use <a href="https://airflow.apache.org/docs/apache-airflow-providers-openlineage/stable/guides/user.html">OpenLineage</a> in all supported regions.</li></ul><h3>Dataplex Universal Catalog: Boosting Governance and Discovery</h3><ul><li>A major general availability announcement is the <a href="https://cloud.google.com/dataplex/docs/export-metadata">bulk export of universal catalog metadata</a>. This allows you to export metadata into Cloud Storage, making it available for tasks requiring comprehensive retrieval and enabling querying and analysis of the exported metadata directly in BigQuery.</li><li><a href="https://cloud.google.com/bigquery/docs/automatic-discovery">Automating the discovery of data</a> in Cloud Storage buckets also reached general availability. This feature automatically scans your Cloud Storage data, extracts and catalogs metadata, and creates BigLake, external, or object tables, making that data readily available for analytics and AI workloads.</li><li>Enhancing data security and compliance, encrypting <a href="https://cloud.google.com/dataplex/docs/cmek">Dataplex data with customer-managed encryption keys (CMEK)</a> is now available.</li></ul><h3>Google Cloud Managed Service for Apache Kafka: Enhanced Security and Integration</h3><ul><li><a href="https://cloud.google.com/managed-service-for-apache-kafka/docs/kafka-connect-overview">Kafka Connect support</a> is now available in public preview. This feature offers a curated set of built-in connector plugins hosted in Connect clusters, enabling you to easily stream data at scale between Managed Service for Apache Kafka clusters and other systems, including external Kafka deployments, BigQuery, Cloud Storage, or Pub/Sub.</li></ul><h3>Vertex AI: Rapid Expansion in Generative AI and Core Platform Enhancements</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Qm4h0Czq__ajr_tn5pSWcw.png" /><figcaption>Generated by AI</figcaption></figure><ul><li>General availability of <a href="https://console.cloud.google.com/vertex-ai/publishers/mistralai/model-garden/mistral-ocr-2505">Mistral OCR</a>, an Optical Character Recognition API for document understanding.</li><li>Anthropic’s high-capability models, <a href="https://console.cloud.google.com/vertex-ai/publishers/anthropic/model-garden/claude-opus-4">Claude Opus 4</a> and <a href="https://console.cloud.google.com/vertex-ai/publishers/anthropic/model-garden/claude-sonnet-4">Claude Sonnet 4</a>, also became generally available on Vertex AI.</li><li>The Gemini models continue their rapid evolution, with the public preview version of <a href="https://cloud.google.com/vertex-ai/generative-ai/docs/models/gemini/2-5-flash">Gemini 2.5 Flash</a> being updated, and a preview offering of Gemini 2.0 Flash with image generation capabilities becoming available.</li><li>The new Vertex AI Agent Engine, recently made generally available, also received preview updates, including <a href="https://cloud.google.com/vertex-ai/generative-ai/docs/agent-engine/manage/overview">console support for managing deployed agents</a> and <a href="https://cloud.google.com/vertex-ai/generative-ai/docs/agent-engine/sessions/overview">sessions support for Agent Development Kit (ADK) agents</a>. The Agent Development Kit (ADK) itself is also available in preview, with preview integrations into the Vertex AI Agent Engine for ADK integration, Example Store, and LlamaIndex Query Pipeline integration.</li></ul><h3>Workflows: Expanded Connectors and Execution Insights</h3><p>Workflows, Google Cloud’s serverless orchestration platform, recently delivered new connectors and enhanced visibility into execution details. -</p><ul><li>Support for a <a href="https://cloud.google.com/workflows/docs/reference/connectors/gke/Overview">Kubernetes API connector</a> is now generally available. This allows your workflows to interact directly with Kubernetes objects in a Google Kubernetes Engine cluster, enabling deeper automation of containerized workloads.</li><li>For better organization and management, support to <a href="https://cloud.google.com/workflows/docs/create-manage-tags">create and manage tags</a> for grouping workflows and other resources is now available.</li></ul><p>These recent announcements paint a picture of a Google Cloud Data Platform that is rapidly evolving, with a strong emphasis on integrating cutting-edge AI/ML capabilities directly into data workflows, enhancing developer productivity through streamlined tools and environments, providing greater control and visibility for governance and operations, and expanding the platform’s reach and flexibility.</p><p>As data professionals, staying on top of these updates is crucial for building efficient, scalable, and intelligent data solutions. I’ll continue to bring you the key insights as they emerge.</p><p>Until next time, happy data wrangling!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a5448eedb550" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Forecasting the Future with BigQueryML TimesFM: A Game-Changer in Time Series Analysis]]></title>
            <link>https://medium.com/google-cloud/forecasting-the-future-with-bigqueryml-timesfm-a-game-changer-in-time-series-analysis-43a627151118?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/43a627151118</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[forecasting]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[timeseries]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Wed, 16 Apr 2025 07:04:20 GMT</pubDate>
            <atom:updated>2025-04-22T10:58:28.128Z</atom:updated>
            <content:encoded><![CDATA[<p><a href="https://open.spotify.com/episode/2x559rcoWQFbeGA2qjSJ8B?si=fzElMPdIRb-_02DDkH9PxQ">Forecasting the Future with BigQueryML TimesFM: A Game-Changer in Time Series Analysis</a></p><p>Forecasting time series is crucial for businesses to predict trends, plan resource allocation, and make decisions, and many traditional time series forecasting methods, such as <strong>Autoregressive Integrated Moving Average (ARIMA)</strong>, are limited when working with large datasets. But, Foundation models built on Large Language Models (LLMs) are revolutionizing time series forecasting.</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*RAMb_bBrvqF2fe4mzhjjAg.png" /><figcaption>Generated by AI</figcaption></figure><p><strong>Google Research’s TimesFM (Time Series Foundation Model)</strong> stands out as a significant contribution to this new era. Pre-trained on billions of data points, TimesFM exhibits impressive zero-shot performance, often approaching the accuracy of models trained specifically for a given dataset, but without requiring that dataset-specific training. Most importantly, TimesFM has now been built directly into BigQuery ML, making its forecasting power available as a single SQL function: AI.FORECAST, therefore, this means data analysts without ML experience can get predictions for their time series data using familiar SQL syntax.</p><h4>What makes TimesFM a “foundation model”?</h4><ul><li><strong>Pre-trained like LLMs:</strong> TimesFM is trained on a large corpus of time series data, and this allows it to learn and generalize time patterns such as seasonality, trend, and noise.</li><li><strong>Zero-shot learning:</strong> TimesFM’s pre-training enables it to make accurate predictions on unseen time series datasets without the need for fine-tuning, because this contrasts with regular models.</li></ul><p>The model learns effectively from various data and the pre-training data is large and diverse. It includes nearly real data from Google searches and Wikipedia views, and sampled data from mathematical models, because it also includes various test data sets from a wide range of fields.</p><p>In terms of design, TimesFM adopts the same model architecture as auto LLMs, a decoder-only Transformer, thus it is designed for time series data. It uses a “patch” mechanism, so it slices time series data into small segments, which are analogous to the “tokens” in text. A new feature is that it can create long patches from short ones, and this allows it to make long-term predictions more efficiently.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VfeibQivNywtGoq8Fd9h2w.png" /><figcaption>Generated by AI</figcaption></figure><h3>Forecasting with AI.FORECAST:</h3><blockquote>When using AI.FORECAST, you are <em>not</em> creating a new model or connecting to an external endpoint like gemini-2.0-flash via CREATE MODEL REMOTE. Instead, you are calling a function that uses a pre-existing, managed TimesFM model within Google Cloud. There&#39;s no need to specify a model like gemini-2.0-flash or manage a CONNECTION resource.</blockquote><p>The syntax is straightforward:</p><pre>SELECT *<br>FROM AI.FORECAST(<br>    {TABLE &#39;project.dataset.table&#39; | (query_statement) },<br>    data_col =&gt; &#39;your_value_column_name&#39;,<br>    timestamp_col =&gt; &#39;your_timestamp_column_name&#39;<br>    -- Optional, defaults to TimesFM 2.0<br>    -- [, model =&gt; &#39;TimesFM 2.0&#39;]<br>    -- Optional, for multiple series<br>    [, id_cols =&gt; [&#39;id_column_1&#39;, &#39;id_column_2&#39;]]<br>    -- Optional, default 10<br>    [, horizon =&gt; number_of_steps_to_forecast]<br>    -- Optional, default 0.95<br>    [, confidence_level =&gt; probability_value]<br>);</pre><h4>Key Parameters:</h4><ul><li><strong>data_col</strong>: The column containing numerical values to forecast.</li><li><strong>timestamp_col</strong>: The column with time points (DATE, TIMESTAMP)</li><li><strong>horizon</strong>: Number of future time points to predict.</li><li><strong>confidence_level</strong>: Probability for prediction intervals (e.g., 0.95 for 95%).</li></ul><h3><strong>Fintech Use Case: Forecasting Daily Mobile Payment Volume</strong></h3><p>Let’s consider a Fintech company that needs to forecast daily transaction volumes for different mobile payment channels. Accurate forecasts help in liquidity management, fraud detection, and resource allocation.</p><p><strong>Scenario:</strong> Predict the daily number of transactions for various mobile payment channels (e.g., App Payments, QR Code Payments, SMS Payments) for the next 7 days.</p><p><strong>Dummy Dataset:</strong> We’ll create a dummy BigQuery table representing historical daily transaction volumes.</p><pre>-- Create a dummy table for mobile payment transactions<br>CREATE OR REPLACE TEMP TABLE MobilePayments AS<br>SELECT<br>  DATE(&#39;2025-01-01&#39;) AS transaction_date,<br>  &#39;App Payments&#39; AS channel,<br>  CAST(1500 + RAND() * 500 AS INT64) AS transactions_volume -- Base volume + random noise<br>UNION ALL<br>SELECT<br>  DATE(&#39;2025-01-02&#39;) AS transaction_date,<br>  &#39;App Payments&#39; AS channel,<br>  CAST(1550 + RAND() * 500 AS INT64) AS transactions_volume<br>UNION ALL<br>-- ... Add more rows for &#39;App Payments&#39; and other channels<br>-- spanning a reasonable period (e.g., 1 year daily data)<br>SELECT<br>  DATE(&#39;2025-01-01&#39;) AS transaction_date,<br>  &#39;QR Code Payments&#39; AS channel,<br>  CAST(800 + RAND() * 300 AS INT64) AS transactions_volume<br>UNION ALL<br>SELECT<br>  DATE(&#39;2025-01-02&#39;) AS transaction_date,<br>  &#39;QR Code Payments&#39; AS channel,<br>  CAST(820 + RAND() * 300 AS INT64) AS transactions_volume<br>-- ... Add more data for &#39;QR Code Payments&#39;<br>UNION ALL<br>SELECT<br>  DATE(&#39;2025-01-01&#39;) AS transaction_date,<br>  &#39;SMS Payments&#39; AS channel,<br>  CAST(300 + RAND() * 100 AS INT64) AS transactions_volume<br>UNION ALL<br>SELECT<br>  DATE(&#39;2025-01-02&#39;) AS transaction_date,<br>  &#39;SMS Payments&#39; AS channel,<br>  CAST(310 + RAND() * 100 AS INT64) AS transactions_volume<br>-- ... Add more data for &#39;SMS Payments&#39;<br>-- Ensure data for each channel spans the same date range.<br>-- For a real scenario, you&#39;d use your actual historical data.<br>;</pre><h4>Generating Forecasts with AI.FORECAST:</h4><p>To forecast daily transaction volume for <em>all</em> channels simultaneously for the next 7 days:</p><pre>-- Forecast daily transaction volume for all channels for the next 7 days<br>SELECT *<br>FROM AI.FORECAST(<br>    TABLE MobilePayments,<br>    data_col =&gt; &#39;transactions_volume&#39;, -- Column with the numerical values<br>    timestamp_col =&gt; &#39;transaction_date&#39;, -- Column with the time points<br>    id_cols =&gt; [&#39;channel&#39;], -- Column identifying each separate time series (payment channel)<br>    horizon =&gt; 7, -- Forecast 7 days ahead<br>    confidence_level =&gt; 0.90 -- Request a 90% prediction interval<br>);</pre><p>This single query leverages the power of TimesFM to generate forecasts for each distinct payment channel identified in the channel column.</p><h4><strong>Interpreting the Results:</strong></h4><p>The AI.FORECAST function returns a table with columns like:</p><ul><li>channel: Identifier for the specific time series (e.g., &#39;App Payments&#39;).</li><li>forecast_timestamp: The date for each forecasted point.</li><li>forecast_value: The model&#39;s predicted transaction volume for that date.</li><li>prediction_interval_lower_bound and prediction_interval_upper_bound: The range within which the actual value is expected to fall, based on the specified confidence_level.</li><li>confidence_level: The requested confidence level (e.g., 0.90).</li><li>ai_forecast_status: Indicates if the forecast for that series was successful or contains an error message.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*R6Gl42ACuU1ZY7HIIg4iwg.png" /></figure><h3>When to Choose TimesFM vs. ARIMA_PLUS:</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pZ6Fd_lDqpt8ZYNRBT4t2g.png" /></figure><h3>Conclusion</h3><p>Google’s TimesFM, integrated into BigQuery ML, represents a paradigm shift in time series forecasting. By combining the power of foundation models with the simplicity of SQL, it empowers data professionals to generate sophisticated forecasts effortlessly.</p><p>Whether you’re optimizing trading strategies, managing supply chains, or predicting customer demand, TimesFM in BigQuery ML is a powerful tool to have in your arsenal.</p><h3>Resources</h3><ul><li><a href="https://research.google/blog/a-decoder-only-foundation-model-for-time-series-forecasting/">A decoder-only foundation model for time-series forecasting</a></li><li><a href="https://cloud.google.com/bigquery/docs/timesfm-time-series-forecasting-tutorial">Forecast a time series with a TimesFM univariate model | BigQuery | Google Cloud</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=43a627151118" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/forecasting-the-future-with-bigqueryml-timesfm-a-game-changer-in-time-series-analysis-43a627151118">Forecasting the Future with BigQueryML TimesFM: A Game-Changer in Time Series Analysis</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Typed GenAI in Your Warehouse: BigQueryML Generative AI Functions]]></title>
            <link>https://medium.com/google-cloud/typed-genai-in-your-warehouse-bigqueryml-generative-ai-functions-6340a05eb932?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/6340a05eb932</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[generative-ai]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[vertex-ai]]></category>
            <category><![CDATA[healthcare]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Tue, 08 Apr 2025 16:39:31 GMT</pubDate>
            <atom:updated>2025-04-08T16:39:31.261Z</atom:updated>
            <content:encoded><![CDATA[<iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fopen.spotify.com%2Fembed%2Fepisode%2F2T6iA2bZNM9kK7o8g3Upxs%3Futm_source%3Doembed&amp;display_name=Spotify&amp;url=https%3A%2F%2Fopen.spotify.com%2Fepisode%2F2T6iA2bZNM9kK7o8g3Upxs&amp;image=https%3A%2F%2Fimage-cdn-ak.spotifycdn.com%2Fimage%2Fab67656300005f1f1f48a6d36e3a964c453b47f7&amp;type=text%2Fhtml&amp;schema=spotify" width="456" height="152" frameborder="0" scrolling="no"><a href="https://medium.com/media/2560ca904a4cbb822703ee7955dbfad5/href">https://medium.com/media/2560ca904a4cbb822703ee7955dbfad5/href</a></iframe><h3>Introduction</h3><p>The integration of Generative AI capabilities into BigQueryML marks a pivotal moment in data analytics. With Google’s Gemini models now available directly within SQL workflows, organizations can infuse large language model (LLM) intelligence into their data without leaving BigQuery. In this blog, I’ll break down how to use BigQueryML Generative AI functions and walk through a real-world healthcare industry use case with code examples.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TLeq9JrSFMWaxsuQVU1NQw.png" /><figcaption>Generated by AI</figcaption></figure><h3>Understanding BigQuery’s AI.GENERATE_* Function Family</h3><p>In addition to ML.GENERATE_TEXT() and ML.GENERATE_EMBEDDING(), BigQuery now introduces four specialized Generative AI functions designed to analyze text within your tables on a row-by-row basis:</p><ol><li><strong>AI.GENERATE</strong> — Returns STRING output for tasks like summarization, entity extraction, or translation</li><li><strong>AI.GENERATE_BOOL</strong> — Returns BOOLEAN output for yes/no questions or binary classification</li><li><strong>AI.GENERATE_DOUBLE</strong> — Returns FLOAT64 (double) output for numerical scores or values</li><li><strong>AI.GENERATE_INT</strong> — Returns INT64 (integer) output for counting or whole number metrics</li></ol><p>What makes these functions powerful is their ability to return strongly-typed data directly from the LLM, eliminating the need for complex parsing logic or post-processing.</p><h3>Let’s understand it better with a Healthcare Use Case: Analyze Patient Feedback</h3><h4>The Challenge: From Unstructured Text to Actionable Insights</h4><p>Healthcare organizations collect vast amounts of patient feedback, clinical notes, and documentation. Traditional analysis methods struggle with:</p><ul><li>Extracting specific data points from free-form text</li><li>Converting qualitative information into quantifiable metrics</li><li>Ensuring consistent interpretation of subjective content</li><li>Scaling analysis across thousands or millions of records</li></ul><blockquote><strong>The solution?</strong></blockquote><blockquote>BigQuery’s new family of Generative AI functions that leverage Google’s powerful Gemini models to analyze text and return structured, typed results.</blockquote><h3>Step-by-Step Implementation</h3><h4>Prerequisites: Setting Up the Connection to Vertex AI</h4><p>Before using these functions, you need to establish a secure connection between BigQuery and Vertex AI. Here’s how:</p><ol><li><strong>Enable the necessary APIs.<br>- </strong>Vertex AI API<br>- BigQuery Connection API</li><li><strong>Create a Cloud Resource Connection</strong>:<br>- Use <a href="https://cloud.google.com/bigquery/docs/default-connections">Default</a> Connection {In-preview}</li></ol><h4>The Dataset</h4><p>Imagine a hospital system collecting patient feedback through post-visit surveys. These surveys contain valuable unstructured text that could inform improvements in patient care, but manually analyzing thousands of responses is impractical. Our example table healthcare_dataset.patient_feedback contains:</p><pre>feedback_id | patient_id | department | visit_date | feedback_text<br>-----------|------------|------------|------------|-------------<br>F1001 | P5432 | Cardiology | 2025-03-10 | &quot;The cardiologist was very thorough and explained my condition clearly. However, I waited over an hour past my appointment time, which was frustrating. The nurse who took my vitals was very kind and apologetic about the delay.&quot;<br>F1002 | P6789 | Emergency | 2025-03-11 | &quot;I came in with severe chest pain. The triage was quick, but then I waited 3 hours before seeing a doctor. The pain medication they gave me helped, and the doctor ordered appropriate tests. I&#39;m giving 2 stars because of the long wait time.&quot;<br>F1003 | P7123 | Pediatrics | 2025-03-12 | &quot;My 5-year-old daughter had a great experience. The waiting area had toys to keep her occupied, and the pediatrician was patient and gentle during the examination. The nurse even gave her a sticker afterward which made her day!&quot;</pre><blockquote><strong>Our Objective:</strong></blockquote><blockquote>We want to extract the following insights from each feedback entry:<br>- A concise summary of the feedback (STRING)<br>- Whether the feedback mentions wait time issues (BOOLEAN)<br>- A patient satisfaction score from 0.0 to 1.0 (DOUBLE)<br>- The number of distinct staff members mentioned (INTEGER)</blockquote><h4>Implementation with BigQuery SQL &amp; Gemini 2.0 Flash</h4><pre>-- Comprehensive patient feedback analysis<br>SELECT<br>  feedback_id,<br>  department,<br>  visit_date,<br>  feedback_text,<br>  <br>  -- Generate a concise summary (STRING)<br>  AI.GENERATE(<br>    STRUCT(&#39;Summarize this patient feedback in one sentence: &#39;, feedback_text),<br>    connection_id =&gt; &#39;my-healthcare-project.us-central1.vertex-ai-gemini-connection&#39;,<br>    endpoint =&gt; &#39;gemini-2.0-flash&#39;<br>  ).result AS feedback_summary,<br>  <br>  -- Detect wait time issues (BOOLEAN)<br>  AI.GENERATE_BOOL(<br>    STRUCT(&#39;Does this patient feedback mention long wait times, delays, or waiting issues? Answer only TRUE or FALSE: &#39;, feedback_text),<br>    connection_id =&gt; &#39;my-healthcare-project.us-central1.vertex-ai-gemini-connection&#39;,<br>    endpoint =&gt; &#39;gemini-2.0-flash&#39;<br>  ).result AS mentions_wait_time,<br>  <br>  -- Calculate patient satisfaction score (DOUBLE)<br>  AI.GENERATE_DOUBLE(<br>    STRUCT(&#39;Rate the overall patient satisfaction in this feedback on a scale from 0.0 (completely dissatisfied) to 1.0 (completely satisfied). Respond with only the number: &#39;, feedback_text),<br>    connection_id =&gt; &#39;my-healthcare-project.us-central1.vertex-ai-gemini-connection&#39;,<br>    endpoint =&gt; &#39;gemini-2.0-flash&#39;,<br>    model_params =&gt; JSON &#39;{&quot;temperature&quot;: 0.2}&#39;<br>  ).result AS satisfaction_score,<br>  <br>  -- Count mentioned staff members (INTEGER)<br>  AI.GENERATE_INT(<br>    STRUCT(&#39;How many distinct healthcare staff members (e.g., doctor, nurse, technician, receptionist) are mentioned in this patient feedback? Respond with only an integer number: &#39;, feedback_text),<br>    connection_id =&gt; &#39;my-healthcare-project.us-central1.vertex-ai-gemini-connection&#39;,<br>    endpoint =&gt; &#39;gemini-2.0-flash&#39;<br>  ).result AS staff_mentioned_count<br>FROM<br>  healthcare_dataset.patient_feedback;</pre><h4>Understanding the Query Components</h4><p>Let’s break down each function call:</p><ol><li><strong>AI.GENERATE for Summarization</strong>:<br>- We use STRUCT to combine our instruction with the feedback text<br>- The prompt clearly requests a one-sentence summary<br>- We access the .result field to get the STRING output</li><li><strong>AI.GENERATE_BOOL for Wait Time Detection</strong>:<br>- The prompt explicitly asks for a TRUE/FALSE answer<br>- This binary classification helps identify a common pain point</li><li><strong>AI.GENERATE_DOUBLE for Satisfaction Scoring</strong>:<br>- We specify the exact range (0.0 to 1.0) for consistency<br>- We set a lower temperature (0.2) to reduce randomness in scoring<br>- This creates a standardized metric from subjective text</li><li><strong>AI.GENERATE_INT for Staff Mention Counting</strong>:<br>- The prompt provides examples of what constitutes “staff”<br>- We explicitly request an integer response<br>- This helps quantify the breadth of the patient’s experience</li></ol><h4>Expected Results</h4><p>The query would transform our unstructured feedback into structured insights:</p><pre>feedback_id | feedback_summary | mentions_wait_time | satisfaction_score | staff_mentioned_count<br>------------|------------------|-------------------|-------------------|---------------------<br>F1001 | &quot;Patient appreciated thorough cardiologist but was frustrated by hour-long wait despite kind nursing staff.&quot; | TRUE | 0.7 | 2<br>F1002 | &quot;Patient experienced quick triage but long wait in emergency room, received helpful pain medication and appropriate tests.&quot; | TRUE | 0.4 | 2<br>F1003 | &quot;Five-year-old had a positive pediatric visit with engaging waiting area, gentle examination, and received a sticker reward.&quot; | FALSE | 0.9 | 2</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wPtYWVvqUWdOFRQ9T8hq7A.png" /><figcaption>Generated by AI</figcaption></figure><h3>Actionable Insights from Structured Data</h3><p>With this enriched data, healthcare administrators can now:</p><ol><li><strong>Filter for specific issues</strong>: WHERE mentions_wait_time = TRUE</li><li><strong>Identify departments with satisfaction challenges</strong>: GROUP BY department ORDER BY AVG(satisfaction_score)</li><li><strong>Track trends over time</strong>: GROUP BY EXTRACT(MONTH FROM visit_date)</li><li><strong>Correlate staff interactions with satisfaction</strong>: WHERE staff_mentioned_count &gt; 2</li></ol><h3>Conclusion: Transforming Healthcare Analytics with Typed GenAI</h3><p>BigQuery’s AI.GENERATE_* functions represent a significant advancement in making Generative AI accessible and practical for healthcare analytics. By bringing the power of Gemini models directly to your data warehouse:</p><ul><li><strong>Data analysts</strong> can extract structured insights from patient feedback without ML expertise</li><li><strong>Healthcare administrators</strong> can quantify subjective experiences for data-driven decisions</li><li><strong>Clinical teams</strong> can identify patterns and improvement opportunities at scale</li></ul><p>The ability to generate strongly-typed outputs (STRING, BOOL, FLOAT64, INT64) directly from unstructured text eliminates complex data pipelines and custom parsing logic, streamlining the path from raw feedback to actionable insights.</p><p>As healthcare organizations continue to prioritize patient experience, these tools provide a powerful way to listen at scale and transform qualitative feedback into quantitative metrics that drive meaningful improvements in care delivery.</p><h3>Resources</h3><p><a href="https://cloud.google.com/bigquery/docs/generative-ai-overview#generative_ai_functions">Generative AI overview | BigQuery | Google Cloud</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6340a05eb932" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/typed-genai-in-your-warehouse-bigqueryml-generative-ai-functions-6340a05eb932">Typed GenAI in Your Warehouse: BigQueryML Generative AI Functions</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding What Really Drives Your Conversion Rate with BigQueryML Contribution Analysis]]></title>
            <link>https://medium.com/google-cloud/understanding-what-really-drives-your-conversion-rate-with-bigqueryml-contribution-analysis-1980ac707bf0?source=rss-539e90d8d864------2</link>
            <guid isPermaLink="false">https://medium.com/p/1980ac707bf0</guid>
            <category><![CDATA[gcp-weekly]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Ravish Garg]]></dc:creator>
            <pubDate>Fri, 04 Apr 2025 11:53:32 GMT</pubDate>
            <atom:updated>2025-04-04T11:53:32.515Z</atom:updated>
            <content:encoded><![CDATA[<iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fopen.spotify.com%2Fembed%2Fepisode%2F5TYxl4YK21DAtOvusPnHLV%3Futm_source%3Doembed&amp;display_name=Spotify&amp;url=https%3A%2F%2Fopen.spotify.com%2Fepisode%2F5TYxl4YK21DAtOvusPnHLV&amp;image=https%3A%2F%2Fimage-cdn-fa.spotifycdn.com%2Fimage%2Fab67656300005f1feb45ae0fe7f9c69352942316&amp;type=text%2Fhtml&amp;schema=spotify" width="456" height="152" frameborder="0" scrolling="no"><a href="https://medium.com/media/501f2576f3006fea62585d94fcc87be5/href">https://medium.com/media/501f2576f3006fea62585d94fcc87be5/href</a></iframe><p>In the fast-paced world of e-commerce, data isn’t just helpful; it’s the lifeblood of your business. We meticulously track clicks, sessions, and sales, but understanding <em>why</em> key metrics like website conversion rates fluctuate can feel like searching for a needle in a digital haystack. Did that marketing campaign really work? Was it the website redesign? Or something else entirely?</p><p>Often, the answer lies hidden within complex, multi-dimensional data. Traditional methods might point fingers, but they don’t always reveal the full story. This is where BigQueryML Contribution Analysis steps in — a powerful, yet accessible, feature within Google Cloud’s BigQuery that helps you pinpoint the precise factors driving changes in your critical metrics, using familiar SQL.</p><p>Think of it as a sophisticated detective for your data, uncovering the <em>how</em> and <em>why</em> behind performance shifts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qIB9gvxQkn0Z4UIu5p8kAA.png" /><figcaption>Generated by AI</figcaption></figure><h3>Understanding the Core Concepts</h3><p>Before diving in, let’s grasp the basics of BigQueryML Contribution Analysis:</p><ol><li><strong>Metric:</strong> This is the quantifiable value you want to analyze — the key performance indicator whose change you need to understand. For e-commerce, the website conversion rate (transactions / visitors) is a prime candidate.</li><li><strong>Test vs. Control Data:</strong> You need two datasets representing different periods or conditions to compare. For instance, you might compare the ‘Current Quarter’ (test data) against the ‘Previous Quarter’ (control data) to see what drove the change in your metric.</li><li><strong>Dimensions:</strong> These are the categorical variables or factors you suspect might influence your metric. In e-commerce, dimensions could include marketing channels (organic search, paid ads, social media), customer segments (location, purchase history), device types (desktop, mobile), or product categories. The analysis examines unique combinations of these dimensions (segments).</li></ol><h3>A Practical E-commerce Example: Decoding Conversion Rate Shifts</h3><p>Let’s say your online retail business noticed a significant shift in its overall website conversion rate between Q1 (control) and Q2 (test). You want to know <em>which</em> marketing channels were the main drivers behind this change.</p><p><strong>Step 1: Prepare Your Data</strong></p><p>First, you’ll need to consolidate your website traffic and transaction data into a single BigQuery table. This table should include:</p><ul><li>period: Identifying &#39;Previous Quarter&#39; and &#39;Current Quarter&#39;.</li><li>marketing_channel: The source of the visit (e.g., &#39;Organic Search&#39;, &#39;Paid Search&#39;).</li><li>total_conversions: Transactions per channel per period.</li><li>total_visits: Visits per channel per period.</li><li>conversion_rate: Calculated as total_conversions / total_visits.</li><li>is_test: A boolean column (TRUE for &#39;Current Quarter&#39;, FALSE for &#39;Previous Quarter&#39;) derived from the period.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/732/1*8DXFi4lOhUlbpbfNJsGo6Q.png" /><figcaption>Generated by AI</figcaption></figure><p><strong>Step 2: Create the Contribution Analysis Model</strong></p><p>With your data ready, you can create the model directly in BigQuery using SQL:</p><pre>CREATE OR REPLACE MODEL your_dataset.conversion_rate_contribution_model<br>OPTIONS(<br>  model_type = &#39;CONTRIBUTION_ANALYSIS&#39;,<br>  contribution_metric = &#39;conversion_rate&#39;, -- The metric we&#39;re analyzing [cite: 42]<br>  dimension_id_cols = [&#39;marketing_channel&#39;], -- The factor we&#39;re investigating [cite: 42]<br>  is_test_col = &#39;is_test&#39;, -- Column distinguishing test/control periods [cite: 43]<br>  min_apriori_support = 0.01 -- Optional: Ignores very small segments [cite: 14, 43]<br>) AS<br>SELECT<br>  period,<br>  marketing_channel,<br>  total_conversions,<br>  total_visits,<br>  conversion_rate,<br>  CASE WHEN period = &#39;Current Quarter&#39; THEN TRUE ELSE FALSE END AS is_test<br>FROM<br>  your_dataset.your_historical_data_table;</pre><p><strong>Step 3: Get the Insights</strong></p><p>Once the model is trained, retrieve the results with the ML.GET_INSIGHTS function:</p><pre>SELECT *<br>FROM ML.GET_INSIGHTS(MODEL your_dataset.conversion_rate_contribution_model);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/739/1*U_F8koX2xFBz0phoLl7EQg.png" /><figcaption>Generated by AI</figcaption></figure><p>The output will detail the contribution of each marketing channel segment to the overall change in conversion rate between the two quarters. A positive value means that channel helped drive the conversion rate up in Q2 compared to Q1, while a negative value indicates it pulled the rate down.</p><h3>How Does This Compare to Attribution or Marketing Mix Modeling?</h3><p>It’s important to distinguish Contribution Analysis from other common techniques:</p><ul><li><strong>Attribution Models (First-touch, Last-touch, Multi-touch):</strong> These models focus on assigning credit for a <em>single</em> conversion event to various marketing touchpoints along the customer journey. They tell you <em>which</em> channels influenced a sale but often struggle to explain broader shifts in overall metrics like the conversion rate for the entire site.</li><li><strong>Marketing Mix Modeling (MMM):</strong> MMM is typically a top-down approach looking at the impact of various marketing activities (and external factors) on sales over longer periods, often using aggregated data.</li></ul><p>BigQueryML Contribution Analysis offers a complementary, more granular view. It directly analyzes the specific segments <em>within your dataset</em> that statistically contribute to a change in a chosen metric between two defined points in time. It gives you a weighted value for each factor’s impact, helping you understand the <em>drivers</em> of change, not just attributing the final conversion. For analysts already using BigQuery, it provides a potentially faster, more integrated path to these kinds of insights compared to setting up complex MMM.</p><h3>Why Use BigQueryML Contribution Analysis?</h3><ul><li><strong>Actionable Insights:</strong> Go beyond <em>what</em> changed to <em>why</em> it changed. Identify exactly which segments (marketing channels, regions, device types, etc.) are driving or hindering performance.</li><li><strong>SQL-Based:</strong> Leverage existing SQL skills within the familiar BigQuery environment — no need to be a deep ML expert.</li><li><strong>Targeted Optimization:</strong> Use the insights to focus your efforts. If ‘Paid Search on Mobile’ is driving growth, perhaps invest more there. If ‘Social Media’ conversions dropped, investigate why.</li><li><strong>Complementary View:</strong> Use it alongside attribution data for a more complete understanding of marketing performance.</li></ul><h3>Get Started</h3><p>Understanding the drivers behind your key metrics is crucial for sustained e-commerce growth. BigQueryML Contribution Analysis provides a powerful, accessible way to dissect performance changes and gain actionable insights directly within your data warehouse. Explore its capabilities with your own data — you might be surprised by the drivers you uncover. Refer following resources for more details:</p><ul><li><a href="https://cloud.google.com/bigquery/docs/contribution-analysis">Contribution analysis overview | BigQuery | Google Cloud</a></li><li><a href="https://cloud.google.com/blog/products/data-analytics/introducing-a-new-contribution-analysis-model-in-bigquery">Introducing a new contribution analysis model in BigQuery | Google Cloud Blog</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1980ac707bf0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/understanding-what-really-drives-your-conversion-rate-with-bigqueryml-contribution-analysis-1980ac707bf0">Understanding What Really Drives Your Conversion Rate with BigQueryML Contribution Analysis</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>