<?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 Rodrigo Souza on Medium]]></title>
        <description><![CDATA[Stories by Rodrigo Souza on Medium]]></description>
        <link>https://medium.com/@rodrigo.radk?source=rss-b9e6292a9b38------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*A9CkE7bJU6sJxk_B0zfcwg.jpeg</url>
            <title>Stories by Rodrigo Souza on Medium</title>
            <link>https://medium.com/@rodrigo.radk?source=rss-b9e6292a9b38------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 03:09:15 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rodrigo.radk/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[Talk to Your Data Where You Work: Integrating BigQuery Data Agents with Google Chat]]></title>
            <link>https://medium.com/google-cloud/talk-to-your-data-where-you-work-integrating-bigquery-data-agents-with-google-chat-805891f239a9?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/805891f239a9</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-gemini]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[conversational-ai]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Wed, 02 Sep 2026 02:53:41 GMT</pubDate>
            <atom:updated>2026-09-02T02:53:41.305Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5GaPHJSjbcVFLJBDAx8e-Q.jpeg" /></figure><p><strong>TL;DR:</strong> You can bring <a href="https://docs.cloud.google.com/bigquery/docs/create-data-agents">BigQuery Data Agents</a> (Conversational Analytics) directly into Google Chat by deploying a lightweight, serverless Workspace Add-on using Google Apps Script. This pattern requires zero servers, virtual machines, or middleware. Business stakeholders can converse with governed BigQuery datasets, ask questions in plain English, and receive the answers right inside their team spaces and direct messages.</p><p>If you have ever worked in data analytics, you know how much time is lost to the “Ad-Hoc Request Loop.” A business leader needs a quick metric to make a decision, so they message you in Google Chat: <em>“What were our top 5 selling product categories last month”?</em> To answer, an analyst must switch tabs, open the console, write a multi-table SQL query, verify the numbers, format the output, and paste it back into the chat thread.</p><p>Five minutes later, the stakeholder replies: <em>“Can we break that down by return status”?</em></p><p>The loop restarts. This friction creates a persistent bottleneck. Business users wait for answers, while data professionals spend hours fielding repetitive ad-hoc requests instead of focusing on high-impact strategic models.</p><p>Conversational Analytics in BigQuery changed the game by introducing intelligent Data Agents grounded in your curated schemas and business semantics. A common misconception is that having agents created in BigQuery means that business stakeholders need to leave their daily communication hub and log into the Google Cloud Console to talk to their data.</p><p>In this tutorial we will clarify this misconception and build an end-to-end, completely serverless integration connecting BigQuery Data Agents directly to Google Chat using Google Apps Script. Your users will be able to query enterprise datasets naturally, while Apps Script handles authentication and conversation session lifecycle.</p><h3>The Architecture: A Fully Serverless Bridge</h3><p>Instead of spinning up dedicated Cloud Run microservices or managing containerized webhook infrastructure, we can leverage Google Apps Script as a native serverless orchestrator. Because Apps Script runs natively inside Google Workspace, it provides built-in identity management and frictionless OAuth handling.</p><p>The flow operates in four streamlined steps:</p><ol><li><strong>User Interaction</strong>: A team member sends a direct message to the bot asking a question in natural language.</li><li><strong>Session Orchestration</strong>: Google Apps Script captures the event and checks its User Properties cache for an existing conversation session tied to that specific Chat Space ID. If none exists, it calls the Gemini Data Analytics API to initialize one.</li><li><strong>Conversational Inference</strong>: Apps Script relays the prompt to the BigQuery Data Agent via the Conversational Analytics REST API (geminidataanalytics.googleapis.com), passing the user&#39;s OAuth bearer token.</li><li><strong>Structured Formatting &amp; Response</strong>: The BigQuery Data Agent reasons over the database, generates and executes the query, and returns both conversational text and structured data rows. Apps Script parses the payload, formats the rows into clean GitHub-flavored markdown tables, and posts the response directly into the Google Chat thread.</li></ol><h3>Prerequisites</h3><p>Before building the connector, ensure you have the following items configured:</p><ol><li><strong>Google Cloud Project</strong>: A GCP project with billing enabled and the Gemini Data Analytics API active.</li><li><strong>BigQuery Data Agent</strong>: A deployed BigQuery Data Agent. Note your <strong>Data Agent ID</strong>, <strong>Region/Location</strong>, and <strong>Project ID</strong>. If you have not created an agent yet, you can configure one in BigQuery Studio pointing to any dataset, such as the public bigquery-public-data.thelook_ecommerce dataset.</li><li><strong>Google Workspace Account</strong>: Standard Google Workspace credentials with administrative or developer permissions to access Google Chat and Apps Script.</li></ol><h3>Step 1: Create the Google Apps Script Project</h3><p>We will start by creating a new standalone Apps Script project to serve as the integration backend.</p><ol><li>Navigate to <a href="https://script.google.com/">script.google.com</a> in your browser.</li><li>Click <strong>New Project</strong> in the upper-left corner.</li><li>Click the default title (Untitled project) at the top, rename the project to <strong>BigQuery Chat Agent</strong>, and press Enter.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/895/1*ArMuNgsXyG_ETnqgsBfXpw.png" /></figure><h3>Step 2: Configure the Manifest (appsscript.json)</h3><p>To enable the script to receive Google Chat events and make authorized calls to Google Cloud APIs, we must update the script manifest file.</p><ol><li>In the left sidebar of the Apps Script editor, click the gear icon for <strong>Project Settings</strong>.</li><li>Check the box labeled <strong>Show “appsscript.json” manifest file in editor</strong>.</li><li>Return to the code editor by clicking the <strong>Editor</strong> (&lt;&gt;) icon on the left panel.</li><li>Select appsscript.json from the file list and replace its entire contents with the following configuration:</li></ol><pre>{<br>  &quot;timeZone&quot;: &quot;America/New_York&quot;,<br>  &quot;dependencies&quot;: {},<br>  &quot;exceptionLogging&quot;: &quot;STACKDRIVER&quot;,<br>  &quot;runtimeVersion&quot;: &quot;V8&quot;,<br>  &quot;oauthScopes&quot;: [<br>    &quot;https://www.googleapis.com/auth/script.external_request&quot;,<br>    &quot;https://www.googleapis.com/auth/cloud-platform&quot;<br>  ],<br>  &quot;addOns&quot;: {<br>    &quot;common&quot;: {<br>      &quot;name&quot;: &quot;BigQuery Data Agent&quot;,<br>      &quot;logoUrl&quot;: &quot;https://developers.google.com/workspace/chat/images/quickstart-app-avatar.png&quot;<br>    },<br>    &quot;chat&quot;: {}<br>  }<br>}</pre><p>5. Click the <strong>Save</strong> (floppy disk) icon or press Cmd+S (Ctrl+S).</p><p>This manifest registers the script as a Workspace Add-on extending Google Chat. It also declares two essential OAuth scopes: script.external_request to call external REST endpoints, and cloud-platform to authenticate against the Gemini Data Analytics API.</p><h3>Step 3: Implement the Connection Code (Code.gs)</h3><p>Before pasting the code below, you need your <strong>Data Agent ID</strong> (agentId). If you are wondering where to find it in Google Cloud, you have two quick options:</p><ul><li><strong>Option 1: From the Cloud Console URL</strong>: Navigate to <strong>BigQuery &gt; Agents</strong> (or <strong>Agents Hub</strong>) and open the <strong>Agent catalog</strong> tab. Click on your agent to view or edit it. Look at your browser’s address bar: the URL contains the resource identifier directly following dataAgents/ (for example, .../dataAgents/agent_984a2eb8-06bd-4b46-b5bd-d712c4d73549). Copy the agent_... string.</li><li><strong>Option 2: From Cloud Shell or Terminal</strong>: Run a quick REST call using your application default credentials:</li></ul><pre>curl -s -H &quot;Authorization: Bearer $(gcloud auth application-default print-access-token)&quot; \<br>  &quot;https://geminidataanalytics.googleapis.com/v1/projects/[YOUR_PROJECT_ID]/locations/[LOCATION]/dataAgents&quot; \<br>  | jq -r &#39;.dataAgents[] | &quot;\(.displayName // &quot;Unnamed&quot;): \(.name)&quot;&#39;</pre><p>Now we will write the core integration logic. Open Code.gs in the editor, delete any boilerplate code, and paste the following implementation:</p><pre>/**<br> * Responds to a message sent in Google Chat.<br> */<br>function onMessage(event) {<br>  // Extract user text and space ID supporting both Add-on and legacy event structures<br>  const userText = event.chat?.messagePayload?.message?.text || event.message?.text || &quot;&quot;;<br>  const spaceId = event.chat?.messagePayload?.space?.name || event.space?.name || &quot;&quot;;<br><br>  // Replace these with your actual BigQuery Data Agent details:<br>  const projectId = &quot;[YOUR_GCP_PROJECT_ID]&quot;;<br>  const location = &quot;[YOUR_LOCATION_ID]&quot;; // e.g. &quot;global&quot; or &quot;us&quot;<br>  const agentId = &quot;[YOUR_DATA_AGENT_ID]&quot;; // e.g. &quot;agent_xxxx-xxxx-xxxx&quot;<br>  const agentPath = `projects/${projectId}/locations/${location}/dataAgents/${agentId}`;<br><br>  const token = ScriptApp.getOAuthToken();<br><br>  // Get or create conversation session name associated with this space in user properties<br>  const propKey = `conv_${spaceId}`;<br>  let convName = PropertiesService.getUserProperties().getProperty(propKey);<br><br>  if (!convName) {<br>    convName = createConversation(projectId, location, agentPath, token);<br>    PropertiesService.getUserProperties().setProperty(propKey, convName);<br>  }<br><br>  const answer = callAgentChat(projectId, location, agentPath, convName, userText, token);<br><br>  // Return message wrapped in Workspace Add-on format<br>  return {<br>    hostAppDataAction: {<br>      chatDataAction: {<br>        createMessageAction: {<br>          message: {<br>            text: answer<br>          }<br>        }<br>      }<br>    }<br>  };<br>}<br><br>/**<br> * Calls the API to initialize a new conversation session resource.<br> */<br>function createConversation(projectId, location, agentPath, token) {<br>  const url = `https://geminidataanalytics.googleapis.com/v1/projects/${projectId}/locations/${location}/conversations`;<br>  const response = UrlFetchApp.fetch(url, {<br>    method: &quot;post&quot;,<br>    contentType: &quot;application/json&quot;,<br>    headers: { &quot;Authorization&quot;: &quot;Bearer &quot; + token },<br>    payload: JSON.stringify({ agents: [agentPath] }),<br>    muteHttpExceptions: true<br>  });<br>  if (response.getResponseCode() !== 200) {<br>    throw new Error(&quot;Failed to create conversation: &quot; + response.getContentText());<br>  }<br>  return JSON.parse(response.getContentText()).name;<br>}<br><br>/**<br> * Sends a message within the conversation session and retrieves the agent response.<br> */<br>function callAgentChat(projectId, location, agentPath, convName, promptText, token) {<br>  const url = `https://geminidataanalytics.googleapis.com/v1/projects/${projectId}/locations/${location}:chat`;<br>  const response = UrlFetchApp.fetch(url, {<br>    method: &quot;post&quot;,<br>    contentType: &quot;application/json&quot;,<br>    headers: { &quot;Authorization&quot;: &quot;Bearer &quot; + token },<br>    payload: JSON.stringify({<br>      parent: `projects/${projectId}/locations/${location}`,<br>      messages: [{ userMessage: { text: promptText } }],<br>      conversation_reference: {<br>        conversation: convName,<br>        data_agent_context: { data_agent: agentPath }<br>      }<br>    }),<br>    muteHttpExceptions: true<br>  });<br>  if (response.getResponseCode() !== 200) {<br>    return &quot;Sorry, error contacting data agent: &quot; + response.getContentText();<br>  }<br><br>  const messages = JSON.parse(response.getContentText());<br>  let finalResponseText = &quot;&quot;;<br>  let structuredTable = &quot;&quot;;<br><br>  for (let i = 0; i &lt; messages.length; i++) {<br>    const sysMsg = messages[i].systemMessage;<br>    if (sysMsg) {<br>      if (sysMsg.text &amp;&amp; sysMsg.text.textType === &quot;FINAL_RESPONSE&quot;) {<br>        finalResponseText = sysMsg.text.parts.join(&quot;\n&quot;);<br>      }<br>      if (sysMsg.data &amp;&amp; sysMsg.data.result) {<br>        structuredTable = formatDataForChat(sysMsg.data.result);<br>      }<br>    }<br>  }<br><br>  if (!finalResponseText) {<br>    finalResponseText = &quot;Here is the response from the agent:&quot;;<br>  }<br><br>  // Convert markdown headers (### Header) into Google Chat bold (*Header*)<br>  finalResponseText = finalResponseText.replace(/^#{1,6}\s*(.*)$/gm, &quot;*$1*&quot;);<br><br>  // Append aligned table if not already present in response text<br>  if (structuredTable &amp;&amp; !finalResponseText.includes(&quot;```&quot;)) {<br>    finalResponseText += &quot;\n&quot; + structuredTable;<br>  }<br><br>  return finalResponseText;<br>}<br><br>/**<br> * Formats a structured data result into an aligned monospace table for Google Chat.<br> */<br>function formatDataForChat(resultObj) {<br>  const rows = resultObj.data;<br>  const fields = resultObj.schema?.fields || [];<br>  if (!rows || rows.length === 0) return &quot;&quot;;<br><br>  const columns = fields.map(f =&gt; f.name);<br>  if (columns.length === 0) {<br>    columns.push(...Object.keys(rows[0]));<br>  }<br><br>  // Calculate the maximum width needed for each column<br>  const colWidths = columns.map(col =&gt; col.length);<br>  for (let i = 0; i &lt; rows.length; i++) {<br>    for (let j = 0; j &lt; columns.length; j++) {<br>      const valStr = String(rows[i][columns[j]] ?? &quot;&quot;);<br>      if (valStr.length &gt; colWidths[j]) {<br>        colWidths[j] = valStr.length;<br>      }<br>    }<br>  }<br><br>  // Build header and divider<br>  const header = columns.map((col, j) =&gt; col.padEnd(colWidths[j])).join(&quot; | &quot;);<br>  const separator = colWidths.map(w =&gt; &quot;-&quot;.repeat(w)).join(&quot;-+-&quot;);<br><br>  // Build aligned data rows<br>  const rowLines = rows.map(row =&gt; {<br>    return columns.map((col, j) =&gt; {<br>      const val = row[col];<br>      const valStr = String(val ?? &quot;&quot;);<br>      const isNum = typeof val === &quot;number&quot; || (!isNaN(val) &amp;&amp; valStr.trim() !== &quot;&quot;);<br>      return isNum ? valStr.padStart(colWidths[j]) : valStr.padEnd(colWidths[j]);<br>    }).join(&quot; | &quot;);<br>  });<br><br>  return &quot;\n```\n&quot; + header + &quot;\n&quot; + separator + &quot;\n&quot; + rowLines.join(&quot;\n&quot;) + &quot;\n```&quot;;<br>}<br><br>/**<br> * Temporary helper function to trigger OAuth consent screen authorization.<br> */<br>function triggerAuthorization() {<br>  Logger.log(ScriptApp.getOAuthToken());<br>}</pre><h3>Deep Dive into the Implementation</h3><p>Take a moment to review three key techniques implemented above:</p><ol><li><strong>Stateful Session Caching</strong>: Conversational Analytics requires a persistent conversation resource to maintain multi-turn dialogue context. By storing convName in PropertiesService.getUserProperties() indexed by conv_${spaceId}, each Google Chat room or DM keeps its own independent thread history. Subsequent questions like <em>&quot;Can you filter that by completed orders?&quot;</em> seamlessly retain prior context.</li><li><strong>Zero-Key Authentication</strong>: Instead of generating, rotating, and securing service account JSON keys, we call ScriptApp.getOAuthToken(). This provides a short-lived OAuth bearer token inherit to the authenticated Workspace caller.</li><li><strong>Google Chat Compatible Table Formatting</strong>: Google Chat does not natively parse GitHub-Flavored Markdown tables or heading hashes (###). Instead of dumping unstyled pipes and hyphens, our formatDataForChat utility dynamically calculates column widths, right-aligns numbers, and wraps the output in a monospace code block (```). It also converts heading hashes to *bold*, ensuring your database records display cleanly aligned on both desktop and mobile screens.</li></ol><h3>Step 4: Associate Apps Script with Your Google Cloud Project</h3><p>By default, Apps Script projects run inside a generic internal Google Cloud project. To ensure the generated OAuth tokens are authorized to call APIs in your project, you must link the script directly to your GCP project.</p><ol><li>Open the <a href="https://console.cloud.google.com/">Google Cloud Console</a>.</li><li>On your Project Dashboard (or under <strong>IAM &amp; Admin &gt; Settings</strong>), locate and copy your numeric <strong>Project Number</strong> (not the alphanumeric Project ID).</li><li>Switch back to your Apps Script tab.</li><li>Click the gear icon (<strong>Project Settings</strong>) in the left navigation menu.</li><li>Scroll down to <strong>Google Cloud Platform (GCP) Project</strong> and click <strong>Change project</strong>.</li><li>Paste your GCP Project Number into the text field and click <strong>Set project</strong>.</li></ol><h3>Step 5: Authorize the Apps Script Scopes</h3><p>Before Google Chat can route events to the script, you must run the script once manually to ensure your account has authorized the declared OAuth scopes.</p><ol><li>In the Apps Script editor, open Code.gs.</li><li>In the toolbar at the top, locate the function dropdown menu next to the “Debug” button. Select triggerAuthorization.</li><li>Click <strong>Run</strong>.</li><li>Check the <strong>Execution log</strong> at the bottom of the editor:</li></ol><ul><li><strong>Scenario A (Already Authorized)</strong>: If your account already trusts the script, you will not see a popup. Instead, you will see an Info entry displaying a long string (your generated OAuth token) followed by Execution completed. This indicates your script is already authorized and actively generating tokens. You are ready for Step 6!</li><li><strong>Scenario B (First-Time Prompt)</strong>: If an <strong>Authorization Required</strong> dialog appears, click <strong>Review permissions</strong>, select your Google Workspace account, and grant the requested permissions. If prompted with a verification notice, click <strong>Advanced &gt; Go to BigQuery Chat Agent (unsafe)</strong> to complete the one-time approval.</li></ul><p>5. Once complete, you can optionally delete the triggerAuthorization function from the bottom of Code.gs.</p><h3>Step 6: Create a Versioned Add-on Deployment</h3><p>To connect our script to Google Chat, we need a stable Deployment ID.</p><ol><li>In the upper-right corner of the Apps Script interface, click <strong>Deploy &gt; New deployment</strong>.</li><li>In the dialog, click the gear icon next to <strong>Select type</strong> and choose <strong>Add-on</strong>.</li><li>In the Description field, enter Initial Deploy (or a version tag of your choice).</li><li>Click <strong>Deploy</strong>.</li><li>Copy the generated <strong>Deployment ID</strong> string shown in the confirmation dialog.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*54gRqJeric5lTX0haJIBgg.png" /></figure><p><strong>IMPORTANT!!!</strong></p><p><strong>Updating Code After Deployment</strong>: Simply saving edits in Code.gs updates your development draft, but does not update the versioned deployment running in Google Chat. Whenever you make code modifications in the future, do not click &quot;New deployment&quot; (which creates a new Deployment ID). Instead, navigate to <strong>Deploy &gt; Manage deployments</strong>, select your deployment, click the <strong>Edit (pencil)</strong> icon, set <strong>Version</strong> to <strong>New version</strong>, and click <strong>Deploy</strong>. Google Chat will immediately execute your latest code while preserving the exact same Deployment ID.</p><h3>Step 7: Enable and Configure the Google Chat API in Google Cloud Console</h3><p>With the deployment registered, we can now configure the Google Chat application in the Google Cloud Console.</p><ol><li>In the Google Cloud Console, navigate to <strong>APIs &amp; Services &gt; Library</strong>.</li><li>Search for Google Chat API, select it, and click <strong>Enable</strong>.</li><li>Once enabled, click the <strong>Configuration</strong> tab at the top of the Google Chat API management page.</li><li>Populate the application settings as follows:</li></ol><ul><li><strong>App name</strong>: BigQuery Data Agent (or your preferred name).</li><li><strong>Avatar URL</strong>: Use any publicly accessible HTTPS image URL, or use the default quickstart avatar: <a href="https://developers.google.com/workspace/chat/images/quickstart-app-avatar.png.">https://developers.google.com/workspace/chat/images/quickstart-app-avatar.png.</a></li><li><strong>Description</strong>: Query BigQuery databases using natural language directly from Chat.</li><li><strong>Interactive features</strong>: Toggle the switch to <strong>ON</strong> (blue).</li><li><strong>Functionality</strong>: Check the box for <strong>Join spaces</strong>.</li><li><strong>Connection settings</strong>: Select <strong>Apps Script project</strong> and paste the <strong>Deployment ID</strong> you copied in Step 6.</li><li><strong>Visibility</strong>: Check the box to restrict visibility to specific people and domains, and enter your corporate email address. This ensures only authorized testers can interact with the app during initial verification.</li></ul><p>5. Scroll to the bottom of the page and click <strong>Save</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mA8Fpgmhv1ljWal2XA9LNg.png" /></figure><h3>Step 8: Test Your Conversational Data Agent in Google Chat</h3><p>Everything is connected. Let’s test the conversational workflow inside Google Chat.</p><ol><li>Open <a href="https://chat.google.com/">Google Chat</a> in your browser or desktop application.</li><li>Click the <strong>+</strong> (New Chat) icon and search for BigQuery Data Agent.</li><li>Click to start a direct message with your new app, or add the agent to any team space.</li><li>Send your first natural language query:</li></ol><pre>&quot;What were the top 5 product categories by completed revenue in <br>theLook eCommerce, and how many distinct orders did each have?&quot;</pre><h3>Under the Hood: The Agent in Action</h3><p>When you submit that prompt, the BigQuery Data Agent reasons over the connected schema, automatically generating and executing the following query:</p><pre>SELECT <br>  p.category, <br>  ROUND(SUM(oi.sale_price), 2) AS total_revenue,<br>  COUNT(DISTINCT oi.order_id) AS order_count<br>FROM `bigquery-public-data.thelook_ecommerce.order_items` oi<br>JOIN `bigquery-public-data.thelook_ecommerce.products` p<br>  ON oi.product_id = p.id<br>WHERE oi.status = &#39;Complete&#39;<br>GROUP BY 1<br>ORDER BY total_revenue DESC<br>LIMIT 5;</pre><p>Within seconds, the agent returns the exact findings, formatted cleanly by our Apps Script table parser:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Uy0_f8BCjWCC_4FaykTrug.png" /></figure><p>Now test multi-turn statefulness by submitting a follow-up prompt without re-specifying the table or previous context:</p><pre>&quot;Which one had the highest average order value?&quot;</pre><p>Because the conversation ID was persisted in Apps Script user properties for your chat space, the BigQuery Data Agent retains memory of the previous result and immediately answers:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/973/1*gJfH7ElwDW9ZOoRTqAm4sA.png" /></figure><h3>The Verdict: Why This Architecture Works</h3><p>Deploying BigQuery Data Agents directly into Google Chat with Google Apps Script provides distinct advantages for engineering teams and business stakeholders:</p><ol><li><strong>Zero Infrastructure Overhead</strong>: There are no containers to patch, clusters to scale, or Cloud Run services to monitor. Google Apps Script provides an elastic, maintenance-free execution environment.</li><li><strong>Stateful Team Collaboration</strong>: By persisting the conversation session name per space, entire project teams can interact with the agent collaboratively inside a shared room. Every team member sees the questions and answers in context.</li><li><strong>Enterprise Governance and Semantic Consistency</strong>: The agent’s answers are strictly grounded in your BigQuery Data Agent definitions, curated views, and semantic rules. You eliminate hallucinations while democratizing access to business metrics.</li><li><strong>Frictionless Workspace Adoption</strong>: Users do not need SQL proficiency or Cloud Console permissions. They ask questions in natural language directly inside the messaging tool they already use throughout the day.</li></ol><p>When you are ready to roll out the chat app across your entire organization, you can publish the Workspace Add-on internally using the Google Workspace Marketplace SDK.</p><h3>Take Action</h3><p>Bringing conversational AI directly to the tools where your team collaborates turns data into immediate action. Create your first <a href="https://docs.cloud.google.com/bigquery/docs/create-data-agents">BigQuery Data Agent</a> in BigQuery Studio, link it to Google Chat with the Apps Script connector above, and let your team talk to their data where they work.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=805891f239a9" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/talk-to-your-data-where-you-work-integrating-bigquery-data-agents-with-google-chat-805891f239a9">Talk to Your Data Where You Work: Integrating BigQuery Data Agents with Google Chat</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[From What to Why: Unlocking Key Driver Analysis in BigQuery with AI.KEY_DRIVERS]]></title>
            <link>https://medium.com/google-cloud/from-what-to-why-unlocking-key-driver-analysis-in-bigquery-with-ai-key-drivers-e102e8c4eefd?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/e102e8c4eefd</guid>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Tue, 23 Jun 2026 05:37:33 GMT</pubDate>
            <atom:updated>2026-06-23T05:37:33.870Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MQPURH31Yv1X99wme7nPDQ.png" /></figure><p><strong>TL;DR:</strong> BigQuery has launched <a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-key-drivers"><strong>AI.KEY_DRIVERS</strong></a><strong> </strong>in Public Preview. By combining it with TimesFM-based <a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-detect-anomalies"><strong>AI.DETECT_ANOMALIES</strong></a> and <a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-forecast"><strong>AI.FORECAST</strong></a>, you can now build a complete, zero-model-management analytics loop (Forecast, Detect, and Root-Cause) directly in SQL, executing complex multi-dimensional analysis with a 100x+ speed-up compared to the multi-step contribution modeling flow.</p><p>If you have ever worked in data analytics, you know the dread of the “Metric Drop” alert. Your Slack ping goes off: Daily Revenue in the UK has dropped by 40%. Immediately, the fire drill begins. You have to answer two questions:</p><ol><li><strong>What happened? </strong>(Confirming the drop is a statistically significant anomaly, not just regular weekend noise).</li><li><strong>Why did it happen? </strong>(Root-cause analysis across millions of rows, slicing by country, device, and marketing campaign to find the culprit).</li></ol><p>Traditionally, answering <strong>“Why”</strong> meant writing endless GROUP BY combinations, manually building complex multi-way self-joins, or spinning up a multi-step Machine Learning pipeline to calculate contribution analysis. Today, that bottleneck is gone.</p><p>With the Public Preview release of <strong>AI.KEY_DRIVERS</strong> alongside TimesFM-powered <strong>AI.DETECT_ANOMALIES </strong>and <strong>AI.FORECAST</strong>, BigQuery now provides a complete, out-of-the-box advanced analytical lifecycle. You can move from anomaly detection to root-cause analysis in a single, BQ-scale SQL statement, running up to 100x faster compared to the multi-step contribution modeling workflow. Let’s walk through this complete lifecycle using a real-world scenario on BigQuery public data.</p><h4>The Scenario: The Credit Card Payment Outage</h4><p>Imagine you run operations for a taxi fleet platform in Chicago. You monitor daily trips across several dimensions: taxi companies and payment methods (Cash, Credit Card, Mobile).</p><p>On June 1, 2023, a silent configuration bug breaks credit card readers for Flash Cab taxis. Cash and Mobile payments continue to work fine, and other taxi companies are unaffected.</p><p>To make this hands-on tutorial 100% reproducible with zero setup, we will simulate this outage on top of the Chicago Taxi Trips public dataset. You won’t need to load any data or create tables, as you can run these queries directly in your BigQuery Studio console.</p><h4>Step 1: The Relational Foundation (Zero Setup)</h4><p>To keep the analysis queries short and easy to read without persisting physical tables in your GCP project, we will use a temporary table (CREATE TEMP TABLE). This aggregates daily taxi trips from the public dataset and injects the simulated 90% credit card drop on June 1st.</p><blockquote>Note: Because temporary tables are session-scoped, you must run all the SQL blocks in this tutorial together as a single script in the same BigQuery Studio tab (separated by semicolons), or explicitly enable Session Mode in your query settings. This ensures the temporary table remains visible across all subsequent analysis steps.</blockquote><p>Here is the initialization statement:</p><pre>CREATE OR REPLACE TEMP TABLE daily_taxi_trips AS (<br>  WITH DailyTaxiTrips AS (<br>    SELECT<br>      EXTRACT(DATE FROM trip_start_timestamp) AS trip_date,<br>      company,<br>      payment_type,<br>      COUNT(*) AS trip_count<br>    FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`<br>    WHERE trip_start_timestamp &gt;= &#39;2023-05-01&#39; AND trip_start_timestamp &lt; &#39;2023-06-16&#39;<br>      AND company IN (&#39;Flash Cab&#39;, &#39;Taxi Affiliation Services&#39;, &#39;Sun Taxi&#39;, &#39;City Service&#39;)<br>      AND payment_type IN (&#39;Credit Card&#39;, &#39;Cash&#39;, &#39;Mobile&#39;)<br>    GROUP BY 1, 2, 3<br>  ),<br>  DatasetWithAnomaly AS (<br>    SELECT<br>      trip_date,<br>      company,<br>      payment_type,<br>      CASE<br>        -- Artificially drop Flash Cab Credit Card trips by 90% on June 1st<br>        WHEN trip_date = &#39;2023-06-01&#39; AND company = &#39;Flash Cab&#39; AND payment_type = &#39;Credit Card&#39;<br>        THEN CAST(trip_count * 0.10 AS INT64)<br>        ELSE trip_count<br>      END AS trip_count<br>    FROM DailyTaxiTrips<br>  )<br>  SELECT * FROM DatasetWithAnomaly<br>);</pre><h4>Step 2: “What Happened?” (Anomaly Detection via AI.DETECT_ANOMALIES)</h4><p>Before diagnosing the cause, we need to mathematically confirm that the drop on June 1st is an actual anomaly, and not a standard weekly dip. Using BigQuery’s built-in, out-of-the-box TimesFM model, we can run anomaly detection directly on our daily data without training or managing a model:</p><pre>SELECT <br>  time_series_timestamp AS trip_date,<br>  time_series_data AS actual_trips,<br>  is_anomaly,<br>  lower_bound,<br>  upper_bound,<br>  anomaly_probability<br>FROM AI.DETECT_ANOMALIES(<br>  (<br>    SELECT trip_date, trip_count<br>    FROM daily_taxi_trips<br>    WHERE company = &#39;Flash Cab&#39; AND payment_type = &#39;Credit Card&#39;<br>  ),<br>  data_col =&gt; &#39;trip_count&#39;,<br>  timestamp_col =&gt; &#39;trip_date&#39;,<br>  target_last_n_points =&gt; 15<br>)<br>WHERE is_anomaly = true <br>  AND time_series_data &lt; lower_bound; -- Focus only on drops/outages</pre><p>BigQuery instantly flags 2023–06–01 as a drop anomaly:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m1KW1vNMQkdIfEO98HoMaQ.png" /></figure><p>With actual trips dropping to 112 (far below the expected lower bound of 493), this mathematically confirms a highly statistically significant anomaly.</p><h4>Step 3: “Why Did It Happen?” (Root-Cause via AI.KEY_DRIVERS)</h4><p>Now that the anomaly is confirmed, we need to find the root cause. Traditionally, this would require slicing and dicing the data by company and payment_type manually. With AI.KEY_DRIVERS, BigQuery does the multi-dimensional search for you, automatically comparing the anomalous day (the Interest Dataset) against the previous week’s baseline (the Reference Dataset) and identifying the statistically significant driver.</p><p>Note that this same root-cause discovery capability is also accessible via natural language queries inside BigQuery Studio’s Conversational Analytics panel when configured with BigQuery Data Agents.</p><pre>SELECT drivers, metric_interest, metric_reference, difference, unexpected_difference<br>FROM AI.KEY_DRIVERS(<br>  (<br>    SELECT<br>      trip_count,<br>      company,<br>      payment_type,<br>      -- Label June 1st as interest, and preceding week as reference<br>      (trip_date = &#39;2023-06-01&#39;) AS is_interest<br>    FROM daily_taxi_trips<br>    WHERE trip_date = &#39;2023-06-01&#39; OR (trip_date &gt;= &#39;2023-05-25&#39; AND trip_date &lt;= &#39;2023-05-31&#39;)<br>  ),<br>  metric_col =&gt; &#39;trip_count&#39;,<br>  dimension_cols =&gt; [&#39;company&#39;, &#39;payment_type&#39;],<br>  interest_label_col =&gt; &#39;is_interest&#39;,<br>  min_apriori_support =&gt; 0 -- Capture detailed low-volume segments<br>)<br>ORDER BY unexpected_difference ASC<br>LIMIT 3;</pre><p>Without writing any JOINs or manual calculations, the query returns:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hMErOnKjCMZYiWJsf3H5EQ.png" /></figure><p><strong>Interpretation: </strong>The unexpected drop is driven almost entirely by the segment where company = Flash Cab and payment_type = Credit Card. The automated driver analysis isolated the bug immediately.</p><h4>Step 4: “What’s Next?” (Forecasting via AI.FORECAST)</h4><p>Now that we have successfully root-caused the issue (credit card terminal failure) and patched the bug, we transition from reactive troubleshooting back to proactive planning. To prepare for the upcoming week, we want to forecast future demand for Flash Cab credit card trips for the next 7 days (beyond our current dataset’s June 15th timeline). Using AI.FORECAST, we can project this demand instantly without manual model training or deployment:</p><pre>SELECT <br>  forecast_timestamp AS trip_date,<br>  forecast_value AS expected_trips,<br>  prediction_interval_lower_bound AS lower_bound,<br>  prediction_interval_upper_bound AS upper_bound<br>FROM AI.FORECAST(<br>  (<br>    SELECT trip_date, trip_count<br>    FROM daily_taxi_trips<br>    WHERE company = &#39;Flash Cab&#39; AND payment_type = &#39;Credit Card&#39;<br>  ),<br>  data_col =&gt; &#39;trip_count&#39;,<br>  timestamp_col =&gt; &#39;trip_date&#39;,<br>  horizon =&gt; 7<br>);</pre><p>BigQuery outputs daily predictions from June 16th to June 22nd, accounting for seasonality, with no manual training required.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/926/1*bBBeKs1GnvbW9DCT2Tm3ag.png" /></figure><blockquote><strong>Pro-Tip: </strong>Does training a forecast on an uncleaned anomaly skew the forecast? Yes, but mostly in the prediction bounds. Foundation models like TimesFM are highly robust to single outliers, keeping the point forecast almost identical. However, the model expands the prediction intervals to account for the historical volatility. For production use cases, it is best practice to clean or impute known transient outages (e.g. using a CASE statement) before running your forecasts.</blockquote><h4>The Verdict: Why Out-of-the-Box Advanced Analytics is a Game-Changer</h4><ol><li><strong>Zero Model Management Overhead</strong>: There is no need to run separate CREATE MODEL commands, train parameters, or manage model lifecycles. Functions like AI.FORECAST and AI.DETECT_ANOMALIES run on Google’s TimesFM engine on the fly.</li><li><strong>100x+ Performance Improvement: </strong>Standard BQML contribution analysis typically requires running two distinct modeling and evaluation stages. By running directly inside the Dremel query execution engine, AI.KEY_DRIVERS consolidates this into a single step, cutting runtimes from minutes to seconds.</li><li><strong>Clean Insight Pruning:</strong> BigQuery automatically filters out overlapping or redundant dimensional results, ensuring that you only see the most descriptive, actionable segments rather than noisy overlaps.</li></ol><p>Key Driver Analysis is transforming BigQuery from a data warehouse into an active reasoning engine. The next time a critical metric drops, don’t waste time slicing and dicing. Run the Forecast-Detect-Analyze loop and pinpoint the root cause in seconds.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e102e8c4eefd" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/from-what-to-why-unlocking-key-driver-analysis-in-bigquery-with-ai-key-drivers-e102e8c4eefd">From What to Why: Unlocking Key Driver Analysis in BigQuery with AI.KEY_DRIVERS</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[Context is king: Powering BigQuery Data Agents with BigQuery Graph]]></title>
            <link>https://medium.com/google-cloud/context-is-king-powering-bigquery-data-agents-with-bigquery-graph-99bf367d1315?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/99bf367d1315</guid>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[graphql]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[bigquery-graph]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Thu, 23 Apr 2026 14:12:58 GMT</pubDate>
            <atom:updated>2026-04-23T14:12:58.613Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sKeYk2SWJg7aL5w1Dp_r5g.png" /></figure><p><strong>TL;DR:</strong> BigQuery has launched <a href="https://docs.cloud.google.com/bigquery/docs/graph-overview">BigQuery Graph (Preview)</a>. When combined with the new <a href="https://docs.cloud.google.com/bigquery/docs/conversational-analytics">BigQuery Conversational Analytics (Data Agents)</a>, graphs become the ultimate context engine for AI, allowing you to ask complex, multi-hop questions over your data without writing a single line of SQL.</p><p>When building agile data analytics platforms, one of the most persistent hurdles is providing AI with the right context. Large Language Models (LLMs) are incredibly capable, but they may struggle to infer the complex, multi-hop realities of production data.</p><p>Traditionally, solving this meant engineering massive, wide views or hoping the model could accurately build complex JOIN logic on the fly.</p><p>Enter a game-changing combination: <a href="https://docs.cloud.google.com/bigquery/docs/graph-overview"><strong>BigQuery Graph</strong></a> and <a href="https://docs.cloud.google.com/bigquery/docs/conversational-analytics"><strong>BigQuery Conversational Analytics (Data Agents)</strong></a>.</p><p>Google Cloud’s release of BigQuery Graph allows you to build native property graphs directly over your existing data. When combined with BigQuery Data Agents, <strong>graphs become the ultimate context engine for AI</strong>. By explicitly defining relationships as nodes and edges, every connection brings immediate, valuable context to the agent. You can ask complex, relationship-driven questions and get accurate answers without writing a single line of SQL.</p><p>Let’s walk through a multi-hop scenario to demonstrate how to architect a complex graph and use it as a hyper-contextual source for your Data Agent.</p><h3>The Scenario: Multi-Hop E-Commerce Topology</h3><p>To demonstrate the power of graph context, we need a scenario where relationships matter. In a real-world ecosystem, users browse, buy, and interact with products supplied by different brands.</p><p>We will build a topology with three node types and three edge types:</p><ul><li><strong>Nodes:</strong> Customers, Products, Brands</li><li><strong>Edges:</strong> Views (Customer to Product), Purchases (Customer to Product), Supplies (Brand to Product)</li></ul><h3>Step 1: The Relational Foundation</h3><p>BigQuery Graph requires strict uniqueness for nodes to map edges correctly. We will define our base tables using PRIMARY KEY constraints.</p><p>Run the following SQL to generate the schema and sample data:</p><pre>-- 1. Create Node Tables<br>CREATE OR REPLACE TABLE `your_project.your_dataset.customers` (<br>    customer_id STRING,<br>    name STRING,<br>    segment STRING,<br>    PRIMARY KEY (customer_id) NOT ENFORCED<br>);<br><br>CREATE OR REPLACE TABLE `your_project.your_dataset.brands` (<br>    brand_id STRING,<br>    brand_name STRING,<br>    PRIMARY KEY (brand_id) NOT ENFORCED<br>);<br><br>CREATE OR REPLACE TABLE `your_project.your_dataset.products` (<br>    product_id STRING,<br>    product_name STRING,<br>    category STRING,<br>    price FLOAT64,<br>    PRIMARY KEY (product_id) NOT ENFORCED<br>);<br><br>-- Populate Nodes<br>INSERT INTO `your_project.your_dataset.customers` VALUES (&#39;C1&#39;, &#39;Alex&#39;, &#39;Premium&#39;), (&#39;C2&#39;, &#39;Jordan&#39;, &#39;Standard&#39;), (&#39;C3&#39;, &#39;Casey&#39;, &#39;Premium&#39;);<br>INSERT INTO `your_project.your_dataset.brands` VALUES (&#39;B1&#39;, &#39;DataTech&#39;), (&#39;B2&#39;, &#39;CloudGear&#39;);<br>INSERT INTO `your_project.your_dataset.products` VALUES <br>(&#39;P1&#39;, &#39;Cloud-Native Architecture Guide&#39;, &#39;Books&#39;, 45.00),<br>(&#39;P2&#39;, &#39;Mechanical Keyboard&#39;, &#39;Electronics&#39;, 150.00),<br>(&#39;P3&#39;, &#39;Ergonomic Mouse&#39;, &#39;Electronics&#39;, 80.00);<br><br>-- 2. Create Edge Tables (Relationships)<br>CREATE OR REPLACE TABLE `your_project.your_dataset.purchases` (<br>    purchase_id STRING,<br>    customer_id STRING,<br>    product_id STRING,<br>    purchase_date DATE,<br>    PRIMARY KEY (purchase_id) NOT ENFORCED<br>);<br><br>CREATE OR REPLACE TABLE `your_project.your_dataset.views` (<br>    view_id STRING,<br>    customer_id STRING,<br>    product_id STRING,<br>    view_date DATE,<br>    PRIMARY KEY (view_id) NOT ENFORCED<br>);<br><br>CREATE OR REPLACE TABLE `your_project.your_dataset.brand_supplies` (<br>    supply_id STRING,<br>    brand_id STRING,<br>    product_id STRING,<br>    PRIMARY KEY (supply_id) NOT ENFORCED<br>);<br><br>-- Populate Edges<br>INSERT INTO `your_project.your_dataset.brand_supplies` VALUES (&#39;S1&#39;, &#39;B1&#39;, &#39;P1&#39;), (&#39;S2&#39;, &#39;B2&#39;, &#39;P2&#39;), (&#39;S3&#39;, &#39;B2&#39;, &#39;P3&#39;);<br>INSERT INTO `your_project.your_dataset.views` VALUES (&#39;V1&#39;, &#39;C1&#39;, &#39;P2&#39;, &#39;2023-10-01&#39;), (&#39;V2&#39;, &#39;C1&#39;, &#39;P3&#39;, &#39;2023-10-02&#39;), (&#39;V3&#39;, &#39;C2&#39;, &#39;P1&#39;, &#39;2023-10-03&#39;);<br>INSERT INTO `your_project.your_dataset.purchases` VALUES (&#39;O1&#39;, &#39;C1&#39;, &#39;P1&#39;, &#39;2023-10-05&#39;), (&#39;O2&#39;, &#39;C2&#39;, &#39;P3&#39;, &#39;2023-10-10&#39;), (&#39;O3&#39;, &#39;C3&#39;, &#39;P2&#39;, &#39;2023-10-12&#39;);</pre><p><strong><em>Note: Replace </em></strong><strong><em>your_project.your_dataset with your actual BigQuery project and dataset IDs.</em></strong></p><h3>Step 2: Instantiating the BigQuery Graph</h3><p>Now, we elevate this relational data into a multi-dimensional Graph. This explicit DDL statement is what gives our AI agent its “map of reality.”</p><pre>CREATE OR REPLACE PROPERTY GRAPH `your_project.your_dataset.ecommerce_graph`<br>  NODE TABLES (<br>    `your_project.your_dataset.customers` AS Customer,<br>    `your_project.your_dataset.products` AS Product,<br>    `your_project.your_dataset.brands` AS Brand<br>  )<br>  EDGE TABLES (<br>    `your_project.your_dataset.purchases` AS Purchased<br>      SOURCE KEY (customer_id) REFERENCES Customer(customer_id)<br>      DESTINATION KEY (product_id) REFERENCES Product(product_id),<br>      <br>    `your_project.your_dataset.views` AS Viewed<br>      SOURCE KEY (customer_id) REFERENCES Customer(customer_id)<br>      DESTINATION KEY (product_id) REFERENCES Product(product_id),<br>      <br>    `your_project.your_dataset.brand_supplies` AS SuppliedBy<br>      SOURCE KEY (brand_id) REFERENCES Brand(brand_id)<br>      DESTINATION KEY (product_id) REFERENCES Product(product_id)<br>  );</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s3zNJ1unFCie6mKQKBxdxw.png" /></figure><h3>Step 3: Configuring the BigQuery Data Agent</h3><p>With the complex graph established, we plug it into the analytical engine.</p><ol><li>Open the <strong>Agents pane</strong> in BigQuery Studio.</li><li>Click <strong>Create New Agent </strong>and add a name to it.</li><li>Click <strong>Add Source</strong>.</li><li>Click on <strong>Graphs </strong>and select the ecommerce_graph object (notice that, as for now, you can only select one graph per agent).</li><li><em>(Best Practice)</em> Supply base instructions: <strong><em>“You are an AI data assistant. Use the provided ecommerce graph to analyze customer behavior, product interactions (views vs. purchases), and brand attribution.”</em></strong></li><li><strong>Save </strong>and <strong>Publish </strong>the agent.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nyeBjem9UHYcIQofJpNvkw.png" /></figure><h3>Step 4: Testing Multi-Hop Context Awareness</h3><p>If we only provided flat tables to the agent, asking it to untangle views vs. purchases across different brands would require the LLM to generate the SQL query with (possible) multiple self-joins and subqueries , which has a higher chance of hallucinations.</p><p>Because we grounded the agent in ecommerce_graph, the model intrinsically understands the paths. Let&#39;s look at a few prompts.</p><p><strong>Prompt 1: Intersecting Edge Types</strong></p><pre>&quot;Which customers viewed the Ergonomic Mouse but did not purchase it?&quot;</pre><p><strong>Agent Translation Logic:</strong> The agent recognizes it needs to find nodes of type Customer connected to the Product (&#39;Ergonomic Mouse&#39;) via a Viewed edge, but explicitly filtering out those connected via a Purchased edge.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/925/1*TzumrvxEBSl64rGJVeCzmA.png" /></figure><p><strong>Prompt 2: Deep Context (Multi-Hop Traversal)</strong></p><pre>&quot;Did any Premium segment customers buy products supplied by CloudGear?&quot;</pre><p><strong>Agent Translation Logic:</strong></p><p>This requires a deep traversal: Customer (Filter: Segment=&#39;Premium&#39;) -&gt; Purchased (Edge) -&gt; Product &lt;- SuppliedBy (Edge) &lt;- Brand (Filter: Name=&#39;CloudGear&#39;).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/926/1*inH1-kMnu7K2wDLR3ITesQ.png" /></figure><h3>The Verdict: Graphs are the Missing Link for Data Agents</h3><p>Integrating BigQuery Graph with Data Agents fundamentally changes how we approach Conversational Analytics.</p><ol><li><strong>Deterministic Business Logic:</strong> Instead of hoping an LLM infers that a “View” is different from a “Purchase,” the graph’s EDGE TABLES define those physics explicitly.</li><li><strong>Mitigating Hallucinations:</strong> By restricting the AI’s contextual universe to a pre-defined topological map, the rate of invalid schema joins drops to near zero.</li><li><strong>Advanced Analytics at Scale:</strong> Startups and enterprises alike can bypass weeks of complex view engineering. The CREATE PROPERTY GRAPH statement serves as a universal semantic layer.</li></ol><p>If you are deploying BigQuery Data Agents, do not leave your data flat. Define the relationships, build the graph, and let the AI leverage the full context of your architecture.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=99bf367d1315" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/context-is-king-powering-bigquery-data-agents-with-bigquery-graph-99bf367d1315">Context is king: Powering BigQuery Data Agents with BigQuery Graph</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[Talk to Your Data: Unlocking Conversational Analytics in BigQuery]]></title>
            <link>https://medium.com/google-cloud/talk-to-your-data-unlocking-conversational-analytics-in-bigquery-4346e6226439?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/4346e6226439</guid>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[conversational-ai]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Thu, 12 Feb 2026 05:13:35 GMT</pubDate>
            <atom:updated>2026-02-12T22:43:39.771Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4eZgdwkW9vzYEnEjPYISkA.png" /></figure><p><strong>TL;DR:</strong> Conversational Analytics in BigQuery is now in <a href="https://docs.cloud.google.com/bigquery/docs/conversational-analytics">Public Preview</a>! This new feature introduces intelligent Data Agents directly into BigQuery Studio. You can now chat with your data using natural language to generate SQL, visualize results, and even run predictive models like forecasts and anomaly detection with no complex coding required.</p><p>If you have ever worked in data, you know the “Ad-Hoc Request” loop. It starts with a simple question from a stakeholder:</p><p><strong><em>“Why did sales drop in Q3?”</em></strong></p><p>To answer that, an analyst has to write a complex SQL query, validate the data, export it to a spreadsheet or BI tool, and then email it back. Then comes the follow-up:</p><p><strong><em>“Okay, but what if we filter by region?”</em></strong></p><p>And the loop restarts.</p><p>It creates a bottleneck. Business users are stuck waiting for answers, and data analysts are stuck writing repetitive SQL instead of focusing on high-value strategy.</p><p>That is why I am thrilled about the <a href="https://docs.cloud.google.com/bigquery/docs/conversational-analytics"><strong>Public Preview of Conversational Analytics in BigQuery</strong></a>.</p><p>We are moving from “writing queries” to “having conversations.” Let’s look at how this changes the game for both business users and data pros.</p><h4><strong>The Old Way vs. The Conversational Way</strong></h4><p>Previously, getting insights meant you had to know the schema, understand the table relationships, and write the SQL syntax perfectly. If you didn’t know SQL, you were blocked.</p><p>With Conversational Analytics, BigQuery introduces <strong>Data Agents</strong>. These aren’t just chatbots. They are sophisticated reasoning engines grounded in your specific business context.</p><ul><li><strong>For Business Users:</strong> You ask questions in plain English (“Show me the top 5 products by revenue last month”) and get answers, charts, and summaries instantly.</li><li><strong>For Analysts:</strong> You act as the “coach” for the agent. You define the logic once (synonyms, calculations, verified queries), and the agent handles the ad-hoc requests forever.</li></ul><h4>How It Works: A Step-by-Step Guide</h4><p>Let’s walk through a real-world scenario. Imagine we are analyzing a public bikeshare dataset. We want to understand ridership trends without writing a single SELECT *.</p><p><strong>Step 1: Create a Data Agent</strong></p><p>In BigQuery Studio, we start by creating a <strong>New Agent</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8I3ichB-QkcEd__5fEKHtg.png" /></figure><p>Think of this as defining the “knowledge base” for the AI. We select the specific tables we want the agent to be an expert on, in this case, our bigquery-public-data.austin_bikeshare.bikeshare_trips table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AmM6LKI33ZJQKanrHeHuzw.png" /></figure><p>This is critical for governance. The agent doesn’t just hallucinate on random data since it is restricted to the <em>specific</em> assets you authorize.</p><p><strong>Step 2: Teach the Agent (Context &amp; Instructions)</strong></p><p>This is where the magic happens. An agent is only as good as its instructions. We can provide <strong>context</strong> to ensure the agent speaks our business language.</p><p>For example, we can tell the agent:</p><ul><li><em>“When I ask for ‘peak hours’, I mean trips between 7 AM and 9 AM and 5 PM and 7 PM.”</em></li><li><em>“Always filter out trips shorter than 60 seconds as they are likely errors.”</em></li></ul><p>We can even provide some pretty nice parameters:</p><ul><li><strong>Verified Queries (formerly Golden Queries): </strong>These are pre-written SQL snippets that tell the agent exactly how to calculate complex metrics like “Customer Lifetime Value” or “Monthly Active Users.” This ensures that the numbers the CEO sees in the chat match the numbers in the official dashboard.</li><li><strong>Glossary: </strong>You may define terms, definitions and synonyms that may be very unique to your organization, so the agent “knows” what you are talking about.</li><li><strong>Labels: </strong>Perfect for tracking costs. Those will be attached to the SQL statements that the agent runs. It allows you to easily filter these specific labels and analyze how the users are interacting with the agents (and what kind of query the agents are building and running).</li><li><strong>Maximum bytes billed: </strong>You don’t want to have your agents going rogue and triggering very expensive queries. This option allows you to add a maximum amount of bytes per query. BigQuery runs a Dry Run calculation before each execution and it will throw an error if the query will cross the maximum limit. This is a perfect cost guard rail for user/agent interaction.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3GUGocg9ZWznz5nmSsPMIw.png" /></figure><p><strong>Step 3: Just Ask</strong></p><p>Now, we just chat.</p><p><strong>User:</strong> <em>“Show me the total number of trips per month for 2024.”</em></p><p><strong>BigQuery Agent:</strong></p><ol><li>Generates the correct SQL query (grouping by month, filtering by year).</li><li>Execute it securely within your project boundaries.</li><li>Returns a data table <em>and</em> automatically generates a chart.</li><li>Provides a natural language summary, insights and follow-up questions.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/853/1*78d69I-fYrjxwgmxjI7RRQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/853/1*HlpfxeepncsWu7AAgtpCXQ.png" /></figure><p>You can verify the trust immediately by clicking on the “down arrow” to see exactly what code the agent wrote.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1001/1*lAnFnU8GBdwQMDkj31U-KA.png" /></figure><p><strong>Step 4: Go Advanced with Predictive AI</strong></p><p>Conversational Analytics isn’t limited to simple aggregations. Because it is built on BigQuery’s AI capabilities, it can trigger advanced Machine Learning functions.</p><p>Let’s say we want to look forward.</p><p><strong>User:</strong> <em>“Forecast the number of trips for the next 30 days.”</em></p><p>Behind the scenes, the agent understands this intent and can leverage AI.FORECAST to generate a time-series prediction.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/773/1*MKqsPWyfR-LTMl87ai_vMg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/1*yNyMqAtSx-MDaFKzlJ-81Q.png" /></figure><p><strong>User:</strong> <em>“Find outliers in trip duration for June 2024 based on the previous 180 days.”</em></p><p>The agent triggers AI.DETECT_ANOMALIES to surface data points that don&#39;t look right.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/777/1*qEFJU8r0ctdTpCY3vnjtuw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/1*-wIgV-E0iSnTxb3mobOZxw.png" /></figure><p>We just performed predictive analytics and anomaly detection in seconds, using only natural language.</p><p><strong>Why This Matters</strong></p><p>This launch is about breaking down the “knowledge wall.”</p><ul><li><strong>Trust:</strong> Unlike generic AI that might make up facts, these agents are grounded in your metadata and Verified Queries.</li><li><strong>Productivity:</strong> Analysts stop being SQL translators and start being data architects.</li><li><strong>Security:</strong> Everything runs within BigQuery’s existing security perimeter. Users can only ask about data they have permission to see.</li></ul><p>Whether you are a business user who wants answers <em>now</em>, or a developer looking to embed these agents into your own apps via the API, this is the most intuitive way to interact with data.</p><p><a href="https://docs.cloud.google.com/bigquery/docs/conversational-analytics"><strong>Conversational Analytics is now available in preview</strong></a><strong>.</strong> Give it a spin in BigQuery Studio and let me know what you find!</p><p><strong>Want to learn more? </strong>Check these awesome blog posts:</p><p><a href="https://medium.com/@kolban1/conversational-analytics-with-bigquery-demonstration-ffae7059ac7c">Conversational Analytics with BigQuery demonstration</a> — by <a href="https://medium.com/@kolban1">Neil Kolban</a></p><p><a href="https://medium.com/@kolban1/using-gcp-conversational-analytics-bigquery-from-adk-tools-afad3b2e2da5">Using GCP Conversational Analytics (BigQuery) from ADK tools</a> — by <a href="https://medium.com/@kolban1">Neil Kolban</a></p><p><a href="https://medium.com/google-cloud/power-up-your-adk-agent-building-secure-data-agents-with-gemini-enterprise-vertexai-agent-engine-23020870d3fd">Power up your ADK Agent: Building Secure Data Agents with Gemini Enterprise, VertexAI Agent Engine and ADK</a> — by <a href="https://medium.com/@abhisaxj">Abhijit Chowdhury</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4346e6226439" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/talk-to-your-data-unlocking-conversational-analytics-in-bigquery-4346e6226439">Talk to Your Data: Unlocking Conversational Analytics in 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[Adeus, Pipelines: Criando Busca com IA em Segundos com Embeddings Autônomos do BigQuery]]></title>
            <link>https://medium.com/google-cloud-brasil/adeus-pipelines-criando-busca-com-ia-em-segundos-com-embeddings-aut%C3%B4nomos-do-bigquery-5c9b952f18f2?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/5c9b952f18f2</guid>
            <category><![CDATA[vector-search]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[inteligencia-artificial]]></category>
            <category><![CDATA[vector-embeddings]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Wed, 10 Dec 2025 15:01:43 GMT</pubDate>
            <atom:updated>2025-12-10T15:01:43.806Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3RG7viyKaNn_Ms68kQaqbQ.png" /></figure><p><strong>TL;DR: </strong>O BigQuery lançou a <a href="https://docs.cloud.google.com/bigquery/docs/autonomous-embedding-generation?hl=pt-br">Geração Autônoma de <em>Embeddings</em></a> e o <a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-search">AI.SEARCH</a> (<em>Public Preview</em>), eliminando a necessidade de pipelines de embedding externos. Agora você pode definir uma coluna de <em>embedding</em> no seu DDL usando GENERATED ALWAYS AS AI.EMBED, que gerencia automaticamente a criação de vetores em segundo plano sempre que você insere dados. Isso permite executar buscas semânticas em texto imediatamente usando a nova função AI.SEARCH, possibilitando a construção de fluxos de trabalho de RAG inteiramente via SQL.</p><p>Se você já construiu um sistema de Geração Aumentada por Recuperação (RAG) ou um mecanismo de busca semântica, sabe que os tutoriais de “Hello World” são fáceis, mas colocar em produção é outra história.</p><p>A dificuldade geralmente não está no banco de dados vetorial em si, mas sim no pipeline. Você precisa configurar um <em>trigger</em> de evento, escrever uma Cloud Function ou um job no Dataflow para chamar uma API de modelo de <em>embedding</em>, lidar com tentativas de repetição (<em>retries</em>) e gravar o vetor de volta no seu banco de dados. É frágil, complexo e, honestamente, é muito código de integração (<em>glue code</em>) para algo que deveria ser simples.</p><p>É por isso que estou incrivelmente empolgado com os novos recursos de <a href="https://docs.cloud.google.com/bigquery/docs/autonomous-embedding-generation?hl=pt-br">Geração Autônoma de Embeddings</a> (<em>Autonomous Embedding Generation</em>) e <a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-search">AI.SEARCH</a>, agora em <em>Public Preview</em> no BigQuery.</p><p>Estamos mudando de “gerenciar pipelines” para “gerenciar dados”. Vamos entender como podemos ir de texto bruto para busca semântica completa em segundos, sem sair do SQL.</p><h3><strong>O Jeito Antigo vs. O Jeito Autônomo</strong></h3><p>Anteriormente, se você quisesse executar busca vetorial em seus dados no BigQuery, precisava orquestrar manualmente o processo de <em>embedding</em>. Você era responsável por manter os <em>embeddings</em> sincronizados com seus dados de origem.</p><p>Com este lançamento, o BigQuery abstrai essa complexidade. Agora temos três capacidades revolucionárias:</p><ol><li><strong>GENERATED ALWAYS AS AI.EMBED(…)</strong>: Uma sintaxe DDL que gera automaticamente embeddings para novos registros.</li><li><strong>AI.SEARCH()</strong>: Uma nova função que permite realizar buscas de similaridade diretamente em dados de texto sem precisar lidar com o vetor de embedding você mesmo.</li><li><a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/search_functions#vector_search"><strong>VECTOR_SEARCH()</strong></a> <strong>Aprimorado</strong>: A capacidade de passar texto em linguagem natural diretamente para a busca vetorial para um controle mais granular.</li></ol><h3>O Caso de Uso: Analisando Avaliações de Entrega de Comida</h3><p>Vamos testar isso com um cenário do mundo real. Imagine que estamos analisando o feedback de clientes para uma plataforma de entrega de comida. Queremos encontrar avaliações sobre “segurança alimentar” ou “rapidez na entrega”, mas os clientes expressam essas coisas de infinitas maneiras (“chegou frio”, “o motorista se atrasou”, “gosto estranho”). A busca por palavras-chave falharia aqui. Por isso precisamos da busca semântica.</p><h3>Passo 1: Crie a tabela</h3><p>Em vez de criar uma tabela padrão e construir um pipeline separado para preenchê-la, definimos a lógica de <em>embedding</em> diretamente no esquema da tabela.</p><pre>CREATE TABLE my_project.reviews_dataset.customer_feedback (<br>    review_id STRING,<br>    customer_id STRING,<br>    review_text STRING,<br>    -- Aqui está a mágica:<br>    text_embedding STRUCT&lt;result ARRAY&lt;FLOAT64&gt;, status STRING&gt;<br>    GENERATED ALWAYS AS (AI.EMBED(<br>        review_text,<br>        connection_id =&gt; &#39;us.my_connection&#39;,<br>        endpoint =&gt; &#39;gemini-embedding-001&#39;<br>    ))<br>    STORED OPTIONS (asynchronous = TRUE)<br>);</pre><p>Observe a cláusula GENERATED ALWAYS. Estamos dizendo ao BigQuery: “Sempre que uma linha for adicionada a esta tabela, chame automaticamente o modelo gemini-embedding-001 via Vertex AI e armazene o vetor nesta coluna.”</p><p>A parte STORED OPTIONS (asynchronous = TRUE) é crítica, pois ela garante que seus comandos INSERT permaneçam rápidos. Os <em>embeddings</em> são processados em segundo plano, para que suas aplicações transacionais não fiquem bloqueadas esperando a resposta do LLM.</p><h3>Passo 2: Insira Dados</h3><p>Agora, apenas inserimos dados na tabela.</p><pre>INSERT INTO my_project.reviews_dataset.customer_feedback <br>(review_id, customer_id, review_text)<br>VALUES <br>(&#39;101&#39;, &#39;cust_A&#39;, &#39;O motorista se perdeu e a pizza estava praticamente congelada quando chegou.&#39;),<br>(&#39;102&#39;, &#39;cust_B&#39;, &#39;Absolutamente deliciosa, muito quente e chegou 10 minutos adiantada!&#39;),<br>(&#39;103&#39;, &#39;cust_C&#39;, &#39;Encontrei um cabelo na minha sopa. Padrões de higiene inaceitáveis.&#39;);</pre><p>Nos bastidores, o BigQuery envia essas descrições para o modelo de <em>embedding</em> e preenche a coluna text_embedding para nós.</p><h3>Passo 3: Busque</h3><p>É aqui que a nova função AI.SEARCH brilha. No passado, para fazer uma busca semântica, você teria que gerar um <em>embedding</em> para sua consulta (“entrega atrasada”) e depois executar um cálculo de distância vetorial.</p><p>Agora, você apenas faz a pergunta.</p><pre>SELECT <br>    base.review_text, <br>    distance<br>FROM AI.SEARCH(<br>    TABLE `my_project.reviews_dataset.customer_feedback`, <br>    &#39;review_text&#39;, <br>    &#39;entrega atrasada&#39;<br>)<br>ORDER BY distance ASC<br>LIMIT 5;</pre><p>O BigQuery lida com o <em>embedding</em> do texto da sua consulta e com a busca de similaridade na tabela.</p><p><strong>Resultado</strong>: Você provavelmente verá a avaliação sobre o “motorista se perdeu” no topo da lista, mesmo que a palavra “atrasado” não tenha aparecido no texto. O significado semântico coincidiu.</p><h3>Quando usar o quê?</h3><p>Você pode estar se perguntando: “Uso AI.SEARCH ou o clássico VECTOR_SEARCH?”</p><ul><li>Use AI.SEARCH quando quiser velocidade e simplicidade. É “conteúdo em primeiro lugar” (<em>content-first</em>), o que significa que você não precisa se preocupar com a matemática subjacente dos vetores. É perfeito para prototipagem rápida e aplicações RAG padrão.</li><li>Use VECTOR_SEARCH quando precisar de controle refinado. Essa função foi atualizada para aceitar entrada de texto em linguagem natural também, mas ela expõe todos os parâmetros ajustáveis na API, dando a você controle máximo sobre desempenho e recall.</li></ul><h3>Por Que Isso Importa</h3><p>Este lançamento trata de diminuir a barreira de entrada para análise com IA. Frequentemente falamos sobre democratizar a IA, e é assim que isso se parece na prática. Ao tratar <em>embeddings</em> apenas como outra coluna gerada, semelhante a um campo calculado, permitimos que qualquer pessoa que saiba SQL construa mecanismos de busca semântica sofisticados.</p><p>Sem boilerplate em Python. Sem pipelines gerenciados. Apenas Criar, Inserir e Buscar.</p><p>Este recurso está atualmente em <em>preview</em>, mas mesmo agora, para qualquer pessoa construindo aplicações GenAI no Google Cloud, esta é a maneira mais simplificada de lidar com dados vetoriais.</p><p>Experimente e me conte o que você construiu!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5c9b952f18f2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud-brasil/adeus-pipelines-criando-busca-com-ia-em-segundos-com-embeddings-aut%C3%B4nomos-do-bigquery-5c9b952f18f2">Adeus, Pipelines: Criando Busca com IA em Segundos com Embeddings Autônomos do BigQuery</a> was originally published in <a href="https://medium.com/google-cloud-brasil">google-cloud-brasil</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Goodbye, Pipelines: Building AI Search in Seconds with BigQuery Autonomous Embeddings]]></title>
            <link>https://medium.com/google-cloud/goodbye-pipelines-building-ai-search-in-seconds-with-bigquery-autonomous-embeddings-bd7dd68e2fe8?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/bd7dd68e2fe8</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[embedding]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Sat, 06 Dec 2025 07:00:41 GMT</pubDate>
            <atom:updated>2025-12-06T07:00:41.306Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b4tZa8q8mjty6cm624mnVQ.png" /></figure><p><strong>TL;DR: </strong>BigQuery has launched <a href="https://docs.cloud.google.com/bigquery/docs/autonomous-embedding-generation">Autonomous Embedding Generation</a> and <a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-search">AI.SEARCH</a> (Public Preview), effectively removing the need for external embedding pipelines. You can now define an embedding column in your DDL using GENERATED ALWAYS AS AI.EMBED, which automatically handles vector generation in the background whenever you insert data. This allows you to run semantic searches on raw text immediately using the new AI.SEARCH function, enabling you to build RAG workflows entirely within SQL.</p><p>If you have ever built a Retrieval-Augmented Generation (RAG) system or a semantic search engine, you know that the “Hello World” tutorials are easy, but production is a different beast.</p><p>The friction usually isn’t the vector database itself , it’s the <strong>pipeline</strong>. You have to set up an event trigger, write a Cloud Function or Dataflow job to call an embedding model API, handle retries, and write the vector back to your database. It’s brittle, it’s complex, and honestly, it’s a lot of glue code for something that should be simple.</p><p>That is why I am incredibly excited about the new <a href="https://docs.cloud.google.com/bigquery/docs/autonomous-embedding-generation"><strong>Autonomous Embedding Generation</strong></a> and <a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-search"><strong>AI.SEARCH</strong></a> features now in Public Preview for BigQuery.</p><p>We are moving from “managing pipelines” to “managing data.” Let’s look at how we can go from raw text to full semantic search in literal seconds, without leaving SQL.</p><h3>The Old Way vs. The Autonomous Way</h3><p>Previously, if you wanted to run vector search on your data in BigQuery, you had to manually orchestrate the embedding process. You were responsible for keeping the embeddings in sync with your source data.</p><p>With this launch, BigQuery abstracts away that complexity. We now have three game-changing capabilities:</p><ol><li><strong>GENERATED ALWAYS AS AI.EMBED(...)</strong>: A DDL syntax that automatically generates embeddings for new records.</li><li><strong>AI.SEARCH()</strong>: A new function that lets you perform similarity searches directly on text data without needing to handle the embedding vector yourself.</li><li><strong>Enhanced </strong><strong>VECTOR_SEARCH()</strong>: The ability to pass natural language text directly into vector search for more granular control.</li></ol><h3>The Use Case: Analyzing Food Delivery Reviews</h3><p>Let’s try this out with a real-world scenario. Imagine we are analyzing customer feedback for a food delivery platform. We want to find reviews about “food safety” or “delivery speed,” but customers phrase these things in infinite ways (“it arrived cold”, “the driver was late”, “weird taste”). Keyword search fails here, that is why we need semantic search.</p><h3>Step 1: Create Table</h3><p>Instead of creating a standard table and building a separate pipeline to fill it, we define the embedding logic directly in the table schema.</p><pre>CREATE TABLE my_project.reviews_dataset.customer_feedback (<br>    review_id STRING,<br>    customer_id STRING,<br>    review_text STRING,<br>    -- Here is the magic:<br>    text_embedding STRUCT&lt;result ARRAY&lt;FLOAT64&gt;, status STRING&gt;<br>    GENERATED ALWAYS AS (AI.EMBED(<br>        review_text,<br>        connection_id =&gt; &#39;us.my_connection&#39;,<br>        endpoint =&gt; &#39;text-embedding-005&#39;<br>    ))<br>    STORED OPTIONS (asynchronous = TRUE)<br>);</pre><p>Notice the GENERATED ALWAYS clause. We are telling BigQuery: &quot;Whenever a row is added to this table, automatically call the text-embedding-005 model via Vertex AI and store the vector in this column.&quot;</p><p>The STORED OPTIONS (asynchronous = TRUE) part is critical, since it ensures that your INSERT statements remain fast. The embeddings are processed in the background, so your transactional applications aren&#39;t blocked waiting for the model to respond.</p><h3>Step 2: Insert Data</h3><p>Now, we just insert data.</p><pre>INSERT INTO my_project.reviews_dataset.customer_feedback <br>(review_id, customer_id, review_text)<br>VALUES <br>(&#39;101&#39;, &#39;cust_A&#39;, &#39;The driver got lost and the pizza was basically frozen by the time it arrived.&#39;),<br>(&#39;102&#39;, &#39;cust_B&#39;, &#39;Absolutely delicious, piping hot, and arrived 10 minutes early!&#39;),<br>(&#39;103&#39;, &#39;cust_C&#39;, &#39;I found a hair in my soup. Unacceptable hygiene standards.&#39;);</pre><p>Behind the scenes, BigQuery sends these descriptions to the embedding model, and populates the text_embedding column for us.</p><h3>Step 3: Search</h3><p>This is where the new AI.SEARCH function shines. In the past, to query this, you would have to generate an embedding for your query (&quot;late delivery&quot;) and then run a vector distance calculation.</p><p>Now, you just ask the question.</p><pre>SELECT <br>    base.review_text, <br>    distance<br>FROM AI.SEARCH(<br>    TABLE `my_project.reviews_dataset.customer_feedback`, <br>    &#39;review_text&#39;, <br>    &#39;late delivery&#39;<br>)<br>ORDER BY distance ASC<br>LIMIT 5;</pre><p>BigQuery handles the embedding of your query text <em>and</em> the similarity search against the table.</p><p><strong>The Result:</strong> You’ll see the review about “driver got lost” at the top of the list, even though the word “late” didn’t appear in the text. The semantic meaning matched.</p><h3>When to use what?</h3><p>You might be wondering, “Do I use AI.SEARCH or the classic VECTOR_SEARCH?&quot;</p><ul><li><strong>Use </strong><strong>AI.SEARCH</strong> when you want speed and simplicity. It’s &quot;content-first,&quot; meaning you don&#39;t need to worry about the underlying mathematics of the vectors. It is perfect for rapid prototyping and standard RAG applications.</li><li><strong>Use </strong><strong>VECTOR_SEARCH</strong> when you need fine-tuned control. The team has upgraded <a href="https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/search_functions#vector_search">this function</a> to accept natural language text input now as well, but it exposes every tunable parameter in the API, giving you maximum control over performance and recall.</li></ul><h3>Why This Matters</h3><p>This launch is about lowering the barrier to entry for AI analytics. We often talk about “democratizing AI,” and this is what it looks like in practice. By treating embeddings as just another generated column, similar to a calculated field, we enable anyone who knows SQL to build sophisticated semantic search engines.</p><p>There is no Python boilerplate. No managed pipelines. Just CREATE, INSERT, and SEARCH.</p><p>This feature is currently in preview, but even now, for anyone building GenAI applications on Google Cloud, this is the most streamlined way to handle vector data.</p><p>Give it a spin and let me know what you build!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bd7dd68e2fe8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/goodbye-pipelines-building-ai-search-in-seconds-with-bigquery-autonomous-embeddings-bd7dd68e2fe8">Goodbye, Pipelines: Building AI Search in Seconds with BigQuery Autonomous Embeddings</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[IA sem Esforço no BigQuery: Conheça as Novas “Managed AI Functions”]]></title>
            <link>https://medium.com/google-cloud-brasil/ia-sem-esfor%C3%A7o-no-bigquery-conhe%C3%A7a-as-novas-managed-ai-functions-c9ee3cb41009?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/c9ee3cb41009</guid>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Thu, 13 Nov 2025 23:36:15 GMT</pubDate>
            <atom:updated>2025-11-13T23:36:15.641Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4B1qynJTUPNRY__X.png" /></figure><p><strong>TL;DR:</strong> As novas funções de IA (<a href="https://docs.cloud.google.com/bigquery/docs/generative-ai-overview#managed_ai_functions">Managed AI Functions</a>) do BigQuery (em Preview) chegaram para automatizar suas tarefas de IA mais comuns. Funções como AI.IF, AI.SCORE e AI.CLASSIFY usam o Gemini para lidar com filtragem, classificação e categorização complexas para você, permitindo que você foque na sua pergunta de negócio, não na engenharia de prompt.</p><p>No meu post anterior, <a href="https://www.google.com/search?q=https://medium.com/google-cloud/unleash-row-level-ai-in-bigquery-scalar-llm-functions-for-smarter-sql-40b66563880f">Unleash Row-Level AI in BigQuery: Scalar LLM Functions for Smarter SQL</a> (Libere a IA em Nível de Linha no BigQuery: Funções Escalares de LLM para um SQL Mais Inteligente), exploramos o poder revolucionário das funções escalares fundamentais como AI.GENERATE. Essas ferramentas oferecem acesso granular, linha a linha, a grandes modelos de linguagem (LLMs), oferecendo flexibilidade incrível para tarefas de IA personalizadas diretamente no SQL.</p><p>Essa flexibilidade é fantástica quando você precisa criar um resultado muito específico. Mas e quanto às tarefas comuns do dia a dia? E se você não quiser projetar o prompt perfeito apenas para descobrir se a avaliação de um cliente é positiva ou negativa? É aqui que entra a próxima evolução do BigQuery: as <strong>Managed AI functions</strong> (atualmente em Preview).</p><p>Em vez de fornecer uma ferramenta flexível e de uso geral, essas novas funções são criadas especificamente para automatizar trabalhos rotineiros e específicos. Elas são:</p><ul><li>AI.IF</li><li>AI.SCORE</li><li>AI.CLASSIFY</li></ul><p>A parte “gerenciada” é fundamental. O BigQuery assume o trabalho pesado de engenharia de prompt, seleção de modelo e ajuste de parâmetros nos bastidores, usando o Gemini para otimizar resultados consistentes e de alta qualidade. Você simplesmente declara sua intenção — filtrar, pontuar ou classificar — e deixa o BigQuery lidar com o “como”. Assim como as funções fundamentais, cada uma retorna um único valor escalar (BOOL, FLOAT64 ou STRING), tornando-as perfeitas para integração perfeita em suas cláusulas WHERE, ORDER BY e GROUP BY.</p><p>Vamos ver como essas novas funções simplificam os fluxos de trabalho comuns de análise de dados.</p><h3>Nossos Dados de Exemplo: Feedback do Cliente</h3><p>Para ver essas funções em ação, usaremos uma tabela simples de feedback de clientes. Isso dá às nossas funções de IA texto não estruturado para trabalhar. Você pode acompanhar criando esta tabela em seu próprio projeto BigQuery.</p><p>Primeiro, crie a tabela customer_feedback:</p><pre>CREATE OR REPLACE TABLE seu_dataset.customer_feedback (<br>  review_id STRING NOT NULL,<br>  review_text STRING<br>);</pre><p>Em seguida, insira nossas avaliações de exemplo:</p><pre>INSERT INTO seu_dataset.customer_feedback (review_id, review_text)<br>VALUES<br>  (&#39;r_001&#39;, &#39;Isso aqui é incrível! Minha esposa adorou. Foi o presente de aniversário perfeito.&#39;),<br>  (&#39;r_002&#39;, &#39;Estou extremamente frustrado. Meu pacote chegou com 10 dias de atraso e a caixa estava danificada. Quero saber onde está meu reembolso.&#39;),<br>  (&#39;r_003&#39;, &#39;O produto é ok, mas acho que fui cobrado duas vezes no meu cartão de crédito. Alguém pode verificar isso, por favor?&#39;),<br>  (&#39;r_004&#39;, &#39;Acabei de fazer um pedido, mas preciso atualizar o endereço de entrega. Como posso fazer isso antes que seja enviado?&#39;),<br>  (&#39;r_005&#39;, &#39;Lixo absoluto. Quebrou após dois usos. A qualidade é terrível. Não comprem isso.&#39;),<br>  (&#39;r_006&#39;, &#39;5 estrelas! Eu daria 6 se pudesse. A configuração foi fácil e funciona melhor do que o anunciado. Recomendo muito.&#39;);</pre><h3>AI.IF: Filtragem Inteligente Simplificada</h3><p>A função AI.IF é sua nova ferramenta para filtragem baseada em IA. Ela avalia o texto (ou mesmo dados multimodais) em relação a um prompt simples em linguagem natural e retorna um BOOL (verdadeiro/falso). Isso a torna perfeita para qualquer cláusula WHERE.</p><p>Vamos encontrar avaliações que falam sobre “ideias de presentes”. Em vez de instruções LIKE complexas, podemos apenas perguntar.</p><pre>SELECT<br>  review_id,<br>  review_text<br>FROM<br>  seu_dataset.customer_feedback<br>WHERE<br>  AI.IF((<br>    &#39;esta avaliação descreve um item que seria um bom presente?&#39;,<br>    review_text),<br>    connection_id =&gt; &#39;us.sua-conexao&#39;<br>  );</pre><p><strong>Resultado:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/544/1*OXDMNjFknfg2N28o2L-vcg.png" /></figure><p>É isso. Nenhuma engenharia de prompt complexa foi necessária. Fizemos uma pergunta direta, e o AI.IF retornou verdadeiro para as linhas que correspondiam à nossa intenção.</p><h3>AI.SCORE: Classificação e Pontuação Impulsionadas por IA</h3><p>A próxima é AI.SCORE. Esta função avalia uma entrada com base no seu prompt e retorna um FLOAT64 (um número). Isso é incrivelmente útil para classificar linhas por qualquer critério que você possa descrever: sentimento, qualidade, urgência e muito mais.</p><p>Seu lugar natural é em uma cláusula ORDER BY. Vamos encontrar as duas avaliações de usuários mais negativas para priorizá-las para nossa equipe de suporte.</p><pre>SELECT<br>  review_id,<br>  review_text,<br>  AI.SCORE((<br>    &#39;quão negativo é o sentimento desta avaliação? Use um intervalo de 0 a 10, sendo 10 o sentimento mais negativo e 0 o sentimento mais positivo&#39;, review_text),<br>    connection_id =&gt; &#39;us.sua-conexao&#39;<br>  ) AS negativity_score<br>FROM<br>  seu_dataset.customer_feedback<br>ORDER BY<br>  negativity_score DESC<br>LIMIT 2;</pre><p><strong>Resultado:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/697/1*FJriqhnJ-Pr8PePOkdwn5w.png" /></figure><p>A função atribuiu uma pontuação numérica de “negatividade”, que podemos usar como qualquer outra coluna para ordenar nossos resultados. Isso abre possibilidades poderosas para encontrar os documentos “mais relevantes” ou os tickets de suporte “mais urgentes”.</p><h3>AI.CLASSIFY: Categorização sem Esforço</h3><p>Por fim, AI.CLASSIFY foi projetada para um trabalho específico: classificar texto em um conjunto de categorias predefinidas que você fornece. Ela retorna uma STRING que corresponde a uma das categorias da sua lista.</p><p>Isso a torna a parceira perfeita para uma cláusula GROUP BY. Vamos analisar nosso feedback e contar quantos tickets se enquadram em nossos principais grupos de suporte: &#39;Faturamento&#39;, &#39;Envio&#39; ou &#39;Qualidade do Produto&#39;.</p><pre>SELECT<br>  AI.CLASSIFY(<br>    review_text,<br>    categories =&gt; [&#39;Faturamento&#39;, &#39;Envio&#39;, &#39;Qualidade do Produto&#39;, &#39;Outros&#39;],<br>    connection_id =&gt; &#39;us.sua-conexao&#39;<br>  ) AS support_category,<br>  COUNT(1) AS ticket_count<br>FROM<br>  seu_dataset.customer_feedback<br>GROUP BY<br>  support_category;</pre><p><strong>Resultado:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/454/1*kPUtC_FzrpzC0r8sOXliPw.png" /></figure><p>Isso é uma enorme economia de tempo. Apenas fornecemos um array com nossas categorias desejadas, e o AI.CLASSIFY lidou com a lógica complexa de ler o texto e atribuir o melhor ajuste. Isso é ideal para roteamento de tickets, análise de temas e moderação de conteúdo.</p><h3>Conclusão: A Ferramenta de IA Certa para o Trabalho</h3><p>O BigQuery está construindo um kit de ferramentas de IA abrangente, e é importante saber qual ferramenta usar.</p><p>As funções escalares fundamentais que discutimos no meu post anterior (como AI.GENERATE) são suas ferramentas de trabalho flexíveis e poderosas. Elas são os &quot;martelos&quot; e &quot;furadeiras&quot; que você usa quando precisa de controle total para construir algo personalizado.</p><p>Essas novas funções de IA Gerenciada — AI.IF, AI.SCORE e AI.CLASSIFY — são diferentes. Elas são seu kit de ferramentas especializado e pré-configurado. Elas são os &quot;torquímetros&quot; e &quot;níveis a laser&quot; projetados para fazer um trabalho específico perfeitamente, com configuração mínima e sem adivinhação.</p><p>Ao automatizar as tarefas de IA mais comuns, o BigQuery está tornando mais fácil do que nunca passar de dados brutos para insights inteligentes, permitindo que você foque na sua lógica de negócios, não nas tecnicalidades da IA. Essas funções (atualmente em Preview) são uma nova adição poderosa à caixa de ferramentas do analista, simplificando fluxos de trabalho que antes eram complexos e demorados.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c9ee3cb41009" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud-brasil/ia-sem-esfor%C3%A7o-no-bigquery-conhe%C3%A7a-as-novas-managed-ai-functions-c9ee3cb41009">IA sem Esforço no BigQuery: Conheça as Novas “Managed AI Functions”</a> was originally published in <a href="https://medium.com/google-cloud-brasil">google-cloud-brasil</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Effortless AI in BigQuery: Meet the New Managed AI Functions]]></title>
            <link>https://medium.com/google-cloud/effortless-ai-in-bigquery-meet-the-new-managed-ai-functions-fc7d8dcefc61?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/fc7d8dcefc61</guid>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Wed, 29 Oct 2025 04:32:39 GMT</pubDate>
            <atom:updated>2025-10-29T04:32:39.915Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wSsKtRyrAU84WLeqazNFhg.png" /></figure><p><strong>TL;DR: </strong>BigQuery’s new <a href="https://docs.cloud.google.com/bigquery/docs/generative-ai-overview#managed_ai_functions">Managed AI functions</a> (in Preview) are here to automate your most common AI tasks. Functions like <strong>AI.IF</strong>, <strong>AI.SCORE</strong>, and <strong>AI.CLASSIFY</strong> use Gemini to handle complex filtering, ranking, and categorization for you, letting you focus on your business question, not on prompt engineering.</p><p>In my previous post, <a href="https://medium.com/google-cloud/unleash-row-level-ai-in-bigquery-scalar-llm-functions-for-smarter-sql-a03ec16d4a90">Unleash Row-Level AI in BigQuery: Scalar LLM Functions for Smarter SQL</a>, we explored the game-changing power of foundational scalar functions like AI.GENERATE. These tools give you granular, row-by-row access to large language models, offering incredible flexibility for custom AI tasks directly in SQL.</p><p>That flexibility is fantastic when you need to craft a very specific outcome. But what about the common, everyday tasks? What if you don’t want to engineer the perfect prompt just to find out if a customer review is positive or negative? This is where BigQuery’s next evolution, <a href="https://docs.cloud.google.com/bigquery/docs/generative-ai-overview#managed_ai_functions">Managed AI functions</a> (currently in Preview), comes in.</p><p>Instead of providing a flexible, all-purpose tool, these new functions are purpose-built to automate specific, routine jobs. They are:</p><ul><li>AI.IF</li><li>AI.SCORE</li><li>AI.CLASSIFY</li></ul><p>The “managed” part is key. BigQuery takes on the hard work of prompt engineering, model selection, and parameter tuning behind the scenes, using Gemini to optimize for high-quality, consistent results. You simply state your intent — filter, score, or classify — and let BigQuery handle the “how.” Like the foundational functions, they each return a single scalar value (BOOL, FLOAT64, or STRING), making them perfect for seamless integration into your WHERE, ORDER BY, and GROUP BY clauses.</p><p>Let’s walk through how these new functions simplify common data analysis workflows.</p><p><strong>Our Sample Data: Customer Feedback</strong></p><p>To see these functions in action, we’ll use a simple table of customer feedback. This gives our AI functions unstructured text to work with. You can follow along by creating this table in your own BigQuery project.</p><p>First, create the table customer_feedback:</p><pre>CREATE OR REPLACE TABLE your_dataset.customer_feedback (<br>  review_id STRING NOT NULL,<br>  review_text STRING<br>);</pre><p>Next, insert our sample reviews:</p><pre>INSERT INTO your_dataset.customer_feedback (review_id, review_text)<br>VALUES<br>  (&#39;r_001&#39;, &#39;This thing is amazing! My wife loves it. It made for the perfect birthday present.&#39;),<br>  (&#39;r_002&#39;, &#39;I am extremely frustrated. My package arrived 10 days late and the box was damaged. I want to know where my refund is.&#39;),<br>  (&#39;r_003&#39;, &#39;The product is okay, but I think I was double-charged on my credit card. Can someone please look into this?&#39;),<br>  (&#39;r_004&#39;, &#39;I just placed an order, but I need to update the delivery address. How can I do that before it ships?&#39;),<br>  (&#39;r_005&#39;, &#39;Absolute garbage. It broke after two uses. The quality is terrible. Do not buy this.&#39;),<br>  (&#39;r_006&#39;, &#39;5 stars! I would give it 6 if I could. The setup was easy and it works better than advertised. Highly recommend.&#39;);</pre><p><strong>AI.IF: Intelligent Filtering Made Simple</strong></p><p>The AI.IF function is your new tool for AI-powered filtering. It evaluates text (or even multi-modal data) against a simple, natural language prompt and returns a BOOL (true/false). This makes it a perfect drop-in for any WHERE clause.</p><p>Let’s find reviews that talk about “gift ideas.” Instead of complex LIKE statements, we can just ask.</p><pre>SELECT<br>  review_id,<br>  review_text<br>FROM<br>  your_dataset.customer_feedback<br>WHERE<br>  AI.IF((<br>    &#39;is this review describing an item that would be a good gift?&#39;,<br>    review_text),<br>    connection_id =&gt; &#39;us.your-connection&#39;<br>  );</pre><p><strong>Result:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/533/1*UgOSub_A3XhsrXBUk5RPKg.png" /></figure><p>That’s it. No complex prompt engineering was needed. We asked a direct question, and AI.IF returned true for the rows that matched our intent.</p><p><strong>AI.SCORE: AI-Driven Ranking and Scoring</strong></p><p>Next is AI.SCORE. This function rates an input based on your prompt and returns a FLOAT64 (a number). This is incredibly useful for ranking rows by any criteria you can describe: sentiment, quality, urgency, and more.</p><p>Its natural home is in an ORDER BY clause. Let’s find the two most negative user reviews to prioritize them for our support team.</p><pre>SELECT<br>  review_id,<br>  review_text,<br>  AI.SCORE(( <br>    &#39;how negative is the sentiment of this review?&#39;,review_text),<br>    connection_id =&gt; &#39;us.your-connection&#39;<br>  ) AS negativity_score<br>FROM<br>  your_dataset.customer_feedback<br>ORDER BY<br>  negativity_score DESC<br>LIMIT 2;</pre><p><strong>Result:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/690/1*Lt-LpeQiaanDrDFk6LXPfQ.png" /></figure><p>The function assigned a numerical “negativity” score, which we can then use just like any other column to sort our results. This opens up powerful possibilities for finding the “most relevant” documents or the “most urgent” support tickets.</p><p><strong>AI.CLASSIFY: Effortless Categorization</strong></p><p>Finally, AI.CLASSIFY is designed for one specific job: classifying text into a set of pre-defined categories you provide. It returns a STRING that matches one of the categories from your list.</p><p>This makes it the perfect partner for a GROUP BY clause. Let’s analyze our feedback and count how many tickets fall into our main support buckets: ‘Billing’, ‘Shipping’, or ‘Product Quality’.</p><pre>SELECT<br>  AI.CLASSIFY(<br>    review_text,<br>    categories =&gt; [&#39;Billing&#39;, &#39;Shipping&#39;, &#39;Product Quality&#39;, &#39;Other&#39;],<br>    connection_id =&gt; &#39;us.your-connection&#39;<br>  ) AS support_category,<br>  COUNT(1) AS ticket_count<br>FROM<br>  your_dataset.customer_feedback<br>GROUP BY<br>  support_category;</pre><p><strong>Result:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/454/1*VHy6OzwYCtvMBjhirRNSzg.png" /></figure><p>This is a massive time-saver. We just supplied an array of our desired categories, and AI.CLASSIFY handled the complex logic of reading the text and assigning the best fit. This is ideal for ticket routing, theme analysis, and content moderation.</p><p><strong>Conclusion: The Right AI Tool for the Job</strong></p><p>BigQuery is building a comprehensive AI toolkit, and it’s important to know which tool to reach for.</p><p>The foundational scalar functions we discussed in my <a href="https://medium.com/google-cloud/unleash-row-level-ai-in-bigquery-scalar-llm-functions-for-smarter-sql-a03ec16d4a90">previous post</a> (like AI.GENERATE) are your flexible, powerful workshop tools. They are the “hammers” and “power drills” you turn to when you need full control to build something custom.</p><p>These new <a href="https://docs.cloud.google.com/bigquery/docs/generative-ai-overview#managed_ai_functions">Managed AI functions</a> — AI.IF, AI.SCORE, and AI.CLASSIFY — are different. They are your specialized, pre-configured toolkit. They are the “torque wrenches” and “laser levels” designed to do one specific job perfectly, with minimal setup and no guesswork.</p><p>By automating the most common AI tasks, BigQuery is making it easier than ever to move from raw data to intelligent insight, letting you focus on your business logic, not on the technicalities of AI. These functions (currently in Preview) are a powerful new addition to the analyst’s toolbox, simplifying workflows that were once complex and time-consuming.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fc7d8dcefc61" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/effortless-ai-in-bigquery-meet-the-new-managed-ai-functions-fc7d8dcefc61">Effortless AI in BigQuery: Meet the New Managed 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[Unleash Row-Level AI in BigQuery: Scalar LLM Functions for Smarter SQL]]></title>
            <link>https://medium.com/google-cloud/unleash-row-level-ai-in-bigquery-scalar-llm-functions-for-smarter-sql-a03ec16d4a90?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/a03ec16d4a90</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[gemini]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Tue, 06 May 2025 03:25:28 GMT</pubDate>
            <atom:updated>2025-05-06T03:25:28.340Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Rl35teWacO5zPsWk0jFVlA.jpeg" /></figure><p><strong>TL;DR:</strong> BigQuery’s scalar AI functions (AI.GENERATE, _BOOL, _INT, _DOUBLE) bring LLM power to each row of your data. This post demonstrates how to use them in SQL for tasks like extracting text, getting real-time data with Google Search grounding, boolean classification for filtering, and extracting numbers — all setting the stage for the upcoming AI Query Engine.</p><p>The world of data analytics is constantly evolving, and BigQuery continues to be at the forefront, empowering users to not just query data, but to truly understand and interact with it. For years, BigQuery ML has been bringing machine learning closer to your data. Now, it’s taking another giant leap forward with the introduction of powerful, <strong>row-wise (scalar) LLM functions</strong>: <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate">AI.GENERATE</a>, <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool">AI.GENERATE_BOOL</a>, <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double">AI.GENERATE_DOUBLE</a>, and <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int">AI.GENERATE_INT</a>.</p><p>These functions are set to revolutionize how we approach data manipulation and analysis using AI directly within SQL. Because they return scalar values, they can be seamlessly integrated almost anywhere a value is needed in your queries — in SELECT lists to generate new insights per row, in WHERE clauses for AI-driven filtering, within JOIN conditions for intelligent data linking, and even in GROUP BY clauses for dynamic, AI-assisted categorization.</p><p>These foundational scalar functions are doing more than just enhancing today’s workflows; they are paving the way for even more advanced capabilities. Think of them as the building blocks for the upcoming AI Query Engine operators that created such a buzz at <a href="https://cloud.google.com/blog/products/data-analytics/data-analytics-innovations-at-next25?e=13802955#:~:text=are%20also%20announcing-,BigQuery%20AI%20query%20engine,-to%20support%20advanced">Google Cloud Next ’25</a>, promising a future where AI is an even more integral and intuitive part of your data conversations.</p><p>In this post, we’ll dive into practical examples of each of these new scalar AI functions. We’ll use a common dataset to illustrate how you can start leveraging them today to enrich your data, derive deeper insights, and simplify complex text processing tasks — all directly within BigQuery.</p><p><strong>Setting the Stage: Data and Setup</strong></p><p>For these examples, we’ll use a simple table containing text snippets related to various financial transactions, travel plans, and currency interests. This will give our AI functions some rich, unstructured text to work with. Please feel free to create them in your environment, so you may follow along.</p><p>First, create our source table, transaction_texts, in your preferred dataset:</p><pre>CREATE OR REPLACE TABLE your_dataset.transaction_texts (<br> text_id STRING NOT NULL,<br> description STRING<br>);</pre><p>Now, populate it with some sample data:</p><pre>INSERT INTO your_dataset.transaction_texts (text_id, description)<br>VALUES<br> (&#39;tt_001&#39;, &#39;Planning my trip from California to the UK next month, then a quick hop over to Switzerland. Need to check conversion for US Dollar to British Pound and then USD to Swiss Franc.&#39;),<br> (&#39;tt_002&#39;, &#39;Our company just closed a deal with a Japanese client for JPY 1,500,000. We also need to pay a supplier in Canada 5,000 CAD. Our books are in USD.&#39;),<br> (&#39;tt_003&#39;, &#39;I saw an interesting item online priced at 75 EUR. Wondering what that would be in my local AUD. Also curious about the general trend of EUR against AUD.&#39;),<br> (&#39;tt_004&#39;, &#39;For our European expansion, we are tracking key rates like Euro to US Dollar and also Euro to British Pound closely this quarter.&#39;),<br> (&#39;tt_005&#39;, &#39;Received a refund of 300 GBP. Need to convert this back to USD. Also, I have a small balance in INR from my last trip.&#39;);</pre><p><strong>Common Parameters</strong></p><p>For all the AI function calls in these examples, there are two parameters that are always required: connection_id and endpoint. The connection_id can be created following <a href="https://cloud.google.com/bigquery/docs/bigquery-ml-remote-model-tutorial#Create-Connection">these steps</a>. In this case, we are creating a connection to Vertex AI, which hosts our model.</p><p>For the <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-remote-model#endpoint">endpoint</a>, we will inform which model we want to use. It is worth mentioning that only Gemini models are supported on these new functions. For this particular post, we will be using Gemini 2.0 Flash.</p><p>You may also notice that, as of now, we need to keep passing these parameters over and over again, in every function call. I really hope that, when AI Engine becomes available, some sort of abstraction will be created, so we can reuse the same configuration over multiple functions, which would make our lives even easier!</p><p><strong>AI.GENERATE: Extracting and Enriching Textual Data</strong></p><p>The AI.GENERATE() function is your go-to for generating or transforming text, returning a STRING value. We can use it for summarization, extraction, rephrasing and basically anything that would return a STRING answer. It is like talking to an LLM, but in a row based fashion.</p><p>Let’s begin by asking the model to extract all currency pairs that it finds in our text. This will give us an understanding of what kind of currency conversion our customers are interested in.</p><pre>SELECT<br>   tt.text_id,<br>   tt.description,<br>   AI.GENERATE(<br>       (&#39;Analyze the following text and identify up to two currency pairs that are explicitly mentioned or strongly implied for conversion or comparison. &#39;,<br>       &#39;Express each pair using their standard three-letter codes in the format &quot;CODE1/CODE2&quot; (e.g., &quot;USD/EUR&quot;). &#39;,<br>       &#39;If multiple pairs are found, list them comma-separated (e.g., &quot;USD/EUR, USD/GBP&quot;). &#39;,<br>       &#39;If no clear pairs, output &quot;N/A&quot;. Text: &quot;&#39;,<br>       tt.description,&#39;&quot;&#39;),<br>     connection_id =&gt; &#39;us.your-connection&#39;,<br>     endpoint =&gt; &#39;gemini-2.0-flash&#39;  <br>   ).result AS identified_pairs_string<br> FROM<br>   your_dataset.transaction_texts AS tt<br>order by text_id; </pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/799/0*2D00kOLQPyzbqbDV" /></figure><p>You can already see how powerful a simple example can be! For each row in our table, we are asking Gemini to identify what are the currencies (and the relationship between them) in an open text format. This is a true conversation and reasoning using your data and getting important value out of it.</p><p>Another very useful feature that we can use on this function is Google search grounding. Now that you have identified the currencies, how about giving the current exchange rate between them?</p><pre>WITH ExtractedCurrencyPairs AS (<br> SELECT<br>   tt.text_id,<br>   tt.description,<br>   AI.GENERATE(<br>       (&#39;Analyze the following text and identify up to two currency pairs that are explicitly mentioned or strongly implied for conversion or comparison. &#39;,<br>       &#39;Express each pair using their standard three-letter codes in the format &quot;CODE1/CODE2&quot; (e.g., &quot;USD/EUR&quot;). &#39;,<br>       &#39;If multiple pairs are found, list them comma-separated (e.g., &quot;USD/EUR, USD/GBP&quot;). &#39;,<br>       &#39;If no clear pairs, output &quot;N/A&quot;. Text: &quot;&#39;,<br>       tt.description,&#39;&quot;&#39;),<br>     connection_id =&gt; &#39;us.your-connection&#39;,<br>     endpoint =&gt; &#39;gemini-2.0-flash&#39;  <br>   ).result AS identified_pairs_string<br> FROM<br>   your_dataset.transaction_texts AS tt<br>)<br>SELECT<br> ecp.text_id,<br> ecp.description AS original_text,<br> ecp.identified_pairs_string,<br> TRIM(individual_pair) AS currency_pair_to_search, -- Cleaned individual pair from the UNNEST<br> AI.GENERATE(<br>   (&#39;What is the current exchange rate for the currency pair &#39;,<br>     TRIM(individual_pair)),<br>   connection_id =&gt; &#39;us.your-connection&#39;,<br>   endpoint =&gt; &#39;gemini-2.0-flash&#39;,   <br>   model_params =&gt; JSON &#39;{&quot;tools&quot;: [{&quot;googleSearch&quot;: {}}]}&#39; -- Enabling Google Search<br> ).result AS current_exchange_rate<br>FROM<br> ExtractedCurrencyPairs AS ecp,<br> UNNEST(SPLIT(ecp.identified_pairs_string, &#39;,&#39;)) AS individual_pair -- Unnest the comma-separated string of pairs<br>order by text_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*rDUM7eodMqdGjDiT" /></figure><p>Explanation:</p><ul><li>In the ExtractedCurrencyPairs CTE, the first AI.GENERATE call processes each description to identify currency pairs (like “USD/GBP”). The prompt guides the LLM to format this as a comma-separated string.</li><li>The main SELECT statement then takes this string of pairs, uses SPLIT and UNNEST to handle each pair individually.</li><li>The second AI.GENERATE call is where the grounding happens. Its prompt asks for the current exchange rate for the specific pair.</li><li>The magic for grounding is in model_params =&gt; JSON ‘{“tools”: [{“googleSearch”: {}}]}’. This tells the LLM to use Google Search to find the most up-to-date information to answer the prompt.</li></ul><p>This example beautifully shows how AI.GENERATE can perform initial text processing and then leverage grounding for real-time data enrichment, all within one SQL query. The output for current_exchange_rate_info will be a string, even listing multiple providers and market commentary.</p><p><strong>AI.GENERATE_BOOL: Classifying Data with True/False Logic</strong></p><p>Next up is AI.GENERATE_BOOL. This function is perfect when you need a clear TRUE or FALSE output based on your text data, ideal for classification tasks and filtering.</p><p>Let’s say that we want to filter only the rows that are primarily business-related. We can easily use AI.GENERATE_BOOL in a where clause:</p><pre>SELECT<br> tt.text_id,<br> tt.description<br>FROM<br> your_dataset.transaction_texts AS tt<br> where AI.GENERATE_BOOL(<br>   (&#39;Is the primary subject of the following text related to a commercial business activity, a companys financial operations, or corporate travel, as opposed to personal finances, personal travel, or general individual currency interest?&#39;,<br>     tt.description),<br>   connection_id =&gt; &#39;us.your-connection&#39;,<br>   endpoint =&gt; &#39;gemini-2.0-flash&#39;   <br> ).result = true<br>order by text_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*pi2F8vxIxge5y-r3" /></figure><p>Running this, you’d expect tt_002 (“Our company just closed a deal…”) and tt_004 (“For our European expansion…”) to be TRUE, while others like tt_001 (personal trip) would ideally be FALSE. This is a powerful feature. We just asked a direct question that can be answered with a yes/no (true/false) and we use that for filtering in a where clause. This kind of classification can be incredibly useful for segmenting data, routing workflows, filtering based on perceived urgency and so on.</p><p><strong>AI.GENERATE_DOUBLE: Extracting Numerical Values from Text</strong></p><p>When you need to pull out numerical figures like prices, scores, or measurements from text and use them as actual numbers, AI.GENERATE_DOUBLE is the function you need.</p><p>Let’s try to extract the first clear monetary amount mentioned in our descriptions.</p><pre>SELECT<br> tt.text_id,<br> tt.description,<br> AI.GENERATE_DOUBLE(<br>   (&#39;From the following text, extract the first clear monetary value mentioned.&#39;,<br>   &#39;If no clear monetary value is found, respond with 0.0. &#39;<br>   &#39;Text: &quot;&#39;,tt.description,&#39;&quot;&#39;),<br>   connection_id =&gt; &#39;us.your-connection&#39;,<br>   endpoint =&gt; &#39;gemini-2.0-flash&#39;  <br> ).result AS extracted_monetary_amount<br>FROM<br> your_dataset.transaction_texts AS tt<br>order by text_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/848/0*3paFpaQvFZgUCI1G" /></figure><p>This example seems simple, but it is actually pretty interesting. In the past, if you were working with LLM responses and you wanted a specific numeric value out of it, you would need to do some proper prompt engineering and parse the result to a numeric value. Now it is very straightforward: you only worry about the prompt and let AI.GENERATE_DOUBLE do the parsing for you.</p><p><strong>AI.GENERATE_INT: Counting and Quantifying from Text</strong></p><p>Finally, AI.GENERATE_INT is used when you need to extract or derive whole numbers from your text. This could be counts, IDs (if simple numerics), or other integer quantities.</p><p>Let’s use it to count the number of distinct financial “events” described in each text.</p><pre>SELECT<br> tt.text_id,<br> tt.description,<br> AI.GENERATE_INT(<br>   (&#39;Analyze the following text. Count how many distinct financial events such as &quot;deals made&quot;, &quot;payments to be made&quot;, &quot;payments received&quot;, &quot;refunds issued&quot;, &quot;refunds received&quot;, or &quot;items explicitly priced for purchase&quot; are described. &#39;,<br>    &#39;For example, &quot;closed a deal and need to pay a supplier&quot; would be 2 events. &quot;An item priced at X&quot; is 1 event. Mere currency conversion planning or rate tracking are not events. &#39;,<br>    &#39;Respond with only the total numerical count of these distinct financial events. If no such events are described, respond with &quot;0&quot;. Text: &quot;&#39;,<br>    tt.description,&#39;&quot;&#39;),<br>   connection_id =&gt; &#39;us.your-connection&#39;,<br>   endpoint =&gt; &#39;gemini-2.0-flash&#39;<br> ).result AS number_of_financial_events<br>FROM<br> your_dataset.transaction_texts AS tt<br>order by text_id; </pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/855/0*z6Gpzkw-pFkFVLiU" /></figure><p>For our data, we’d expect tt_002 to result in 2 events, while tt_001 and tt_004 would likely be 0. Another simple example, but showcases how you can use AI to retrieve insights out of your data, in an automated fashion, using only SQL and natural language.</p><p><strong>Beyond SELECT: The True Flexibility of Scalar AI Functions</strong></p><p>The examples above primarily use these AI functions in the SELECT list to generate new columns. However, their true power lies in their scalar nature. Because they return a single value per row, you can integrate them into other parts of your SQL queries (like we saw in our boolean example). Here are some more ideas of how they could be used:</p><ul><li><strong>In a WHERE clause:</strong> Imagine filtering customer feedback to only process highly negative reviews: WHERE AI.GENERATE_BOOL(‘Does this review express strong negative sentiment?…’, feedback_column, …).result = TRUE</li><li><strong>In a GROUP BY clause:</strong> You could dynamically categorize transaction descriptions: GROUP BY AI.GENERATE(‘Categorize this transaction type: …’, description_column, …).result</li><li><strong>In a JOIN condition:</strong> For more complex scenarios, you might even use an AI function to help define join logic based on semantic similarity or extracted attributes (though this requires careful planning).</li></ul><p>This flexibility opens up a new world of possibilities for building intelligent data processing pipelines directly within BigQuery.</p><p><strong>Conclusion: A New Era of AI-Infused SQL</strong></p><p>The introduction of row-wise scalar LLM functions — AI.GENERATE, AI.GENERATE_BOOL, AI.GENERATE_DOUBLE, and AI.GENERATE_INT — marks a significant step in democratizing access to powerful generative AI capabilities directly within BigQuery. They offer unparalleled flexibility for enriching, classifying, and transforming your data using familiar SQL.</p><p>These functions are more than just new tools; they represent a foundational shift, empowering data practitioners to embed AI intelligence into every level of their data interaction. And as we saw at Google Cloud Next ’25, they are the building blocks for the even more intuitive and powerful AI Query Engine operators on the horizon.</p><p>The ability to perform sophisticated, row-level AI processing without moving data out of BigQuery and even combining these functions with capabilities like Google Search grounding for real-time context, is a game-changer. I encourage you to start experimenting with these functions on your own datasets. The potential to unlock new insights and automate complex tasks is immense!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a03ec16d4a90" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/unleash-row-level-ai-in-bigquery-scalar-llm-functions-for-smarter-sql-a03ec16d4a90">Unleash Row-Level AI in BigQuery: Scalar LLM Functions for Smarter SQL</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 data preparation: A Step-by-Step Guide]]></title>
            <link>https://medium.com/google-cloud/bigquery-data-preparation-a-step-by-step-guide-e358e5a91d15?source=rss-b9e6292a9b38------2</link>
            <guid isPermaLink="false">https://medium.com/p/e358e5a91d15</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data-preparation]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <category><![CDATA[dat]]></category>
            <dc:creator><![CDATA[Rodrigo Souza]]></dc:creator>
            <pubDate>Tue, 11 Mar 2025 01:53:55 GMT</pubDate>
            <atom:updated>2025-03-11T01:53:55.836Z</atom:updated>
            <content:encoded><![CDATA[<p>TL;DR: Learn how to use BigQuery data preparation, an AI-powered visual interface to transform raw data into analysis-ready tables. This guide walks you through a step-by-step example, demonstrating how to use its features for data cleansing, transformation, and enrichment.</p><h3>Introduction</h3><p>BigQuery data preparation is a powerful feature within BigQuery Studio that empowers users to build AI-assisted, visual data pipelines. This allows you to transform raw data from various sources into refined BigQuery tables, ready for analysis and insights.</p><p><strong>Why is data preparation important?</strong></p><p>In today’s data-driven world, organizations often deal with data that is:</p><ul><li><strong>Raw and Unstructured:</strong> Data might come from various sources like applications, websites, or external databases, often requiring cleaning and transformation.</li><li><strong>Inconsistent:</strong> Data formats, missing values, and errors can hinder analysis and lead to inaccurate conclusions.</li><li><strong>Not Optimized for Analysis:</strong> Raw data might not be in the ideal format for specific analytical tasks.</li></ul><p>BigQuery data preparation addresses these challenges by providing a user-friendly interface to:</p><ul><li><strong>Cleanse Data:</strong> Remove errors, handle missing values, and standardize formats.</li><li><strong>Transform Data:</strong> Convert data types, extract relevant information, and create new features.</li><li><strong>Enrich Data:</strong> Combine data from multiple sources to gain a more comprehensive view.</li></ul><p><strong>Use Cases for BigQuery data preparation</strong></p><p>This feature is valuable for various scenarios, including:</p><ul><li><strong>Creating data pipelines for data warehousing.</strong></li><li><strong>Preparing data for business intelligence (BI) dashboards and reports.</strong></li><li><strong>Building machine learning (ML) datasets.</strong></li><li><strong>Enabling self-service data preparation for business users.</strong></li></ul><p>In this example, a business user is notified that new raw data on web/app interactions is available. Many businesses rely on analyzing user behavior on their websites or apps to make informed decisions. This data often resides in raw, unprocessed formats, making it difficult to extract meaningful insights. For example, imagine a marketing team trying to understand why a recent campaign didn’t perform as expected. They have access to website click-stream data, but it’s a jumbled mess of timestamps, user IDs, and product codes. BigQuery data preparation can help them transform this raw data into a clean, structured format, allowing them to identify trends, segment users, and ultimately optimize their marketing strategy.</p><p>Before the data is used by the rest of the team, the user will prepare the data for their teammates by using data preparation’s features. Let’s see how the business user would accomplish that.</p><h3>Want to follow along in your console? Check this initial setup first</h3><p>If you want to follow the steps below on your cloud console and do a “hands on” experience, you will need to create the tables that are going to be used throughout the post. You may check the setup steps <a href="https://medium.com/@rodrigo.radk/bigquery-data-preparation-setup-a8d61e71b801">here</a>. Otherwise, just keep reading below!</p><h3>Navigating the data preparation interface</h3><p>It all starts from the web_app_interaction_external table, which is the table that the user wants to transform before sharing it with the rest of the team. To open the data preparation interface, click on the three dots next to the web_app_interaction_external table and go to <strong>Open in/Data preparation</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/0*HpMiSNLxr3n9oH1Z" /></figure><p>The data preparation interface is divided into two main sections:</p><ul><li>The left section shows a sample of the data.</li><li>The right section shows Gemini suggestions for transforming the data.</li></ul><p>You can click on each column to see specific suggestions for that column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KKZ4O3VpDmE09JUO" /></figure><h3>Transforming and Refining Your Data</h3><p>Data preparation has several ways to easily transform your data, ranging from AI suggestions, user intention, user input and code generation using natural language. Let’s go over all of them using practical examples.</p><p>The first thing you might notice is that the session_start_time column contains different time zones and is in STRING format. To convert the column to timestamp and handle the different time zones, click on the session_start_time column name and then click on the <strong>PREVIEW</strong> button for a particular suggestion. If the transformation looks good, click on the <strong>APPLY</strong> button.</p><p>Data preparation is very flexible. If a specific suggestion that you are looking for does not show up, you can always click <strong>EDIT</strong> and create it using natural language in the prompt or even add the command manually:</p><pre>COALESCE(SAFE.PARSE_TIMESTAMP(&#39;%Y-%m-%d %H:%M:%S %Z&#39;, session_start_time))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/789/0*CVrzyakFzk5i4NFJ" /></figure><p>You might also notice that the purchase_made column is not following the company standard of TRUE/FALSE for BOOLEAN values.</p><p>To fix this, click on the purchase_made column name and then click on the <strong>PREVIEW</strong> button for the suggestion. If the transformation looks good, click on the <strong>APPLY</strong> button.</p><pre>CASE WHEN purchase_made = &#39;YES&#39; THEN TRUE ELSE FALSE END</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/701/0*M5rz1Qi8sV0QuWyY" /></figure><p>The product_json column contains a JSON object, but the user only wants the product name. To extract the product name from the JSON object, click on the product_json column name and then click on the <strong>PREVIEW</strong> button on the suggestion that extracts the product name from the JSON object.</p><pre>JSON_EXTRACT_SCALAR(product_json, r&#39;$.product_name&#39;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/817/0*iaO1ZQ2sEFFlxk2B" /></figure><p>Before applying the transformation, you might notice that it would be applied directly to the product_json column, which would be the incorrect column name.</p><p>To fix this, click on <strong>EDIT</strong>, then <strong>Target column</strong>, then <strong>CREATE NEW COLUMN</strong>, add product_name and click <strong>SAVE</strong>. Then, click on <strong>PREVIEW</strong> and then <strong>APPLY</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lID7vQDvI6dIAb-KFZUyNw.png" /></figure><p>You might notice that the newly created product_name column contains extra information within it. Upon analyzing it, you notice that the data contains product name and product size separated by a pipe character.</p><p>To fix this, you can further transform the product_name column. Let’s do this one using the power of natural language transformations! First, click on the product_name column, <strong>ADD STEP</strong> and then <strong>Transformation</strong>.</p><p>Then, enter the command using natural language. A good prompt would look like this: “This expression removes everything before the pipe symbol (|) from product_name”. Click “Send input”. Also remember to create a new target column for this transformation.</p><pre>REGEXP_REPLACE(product_name, r&#39;.*\|&#39;, r&#39;&#39;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*EmhfsWWaliMmoICf" /></figure><p>Now that you have extracted the product size into a separate column, you can safely change the product_name column to remove the size information from it.</p><p>To do this, click on the product_name column, then click <strong>PREVIEW</strong> and <strong>APPLY</strong> on the suggestion to remove everything after the pipe symbol.</p><pre>REGEXP_REPLACE(product_name, r&#39;\|.*&#39;, r&#39;&#39;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*nisSm8Feww9KdlRP" /></figure><h3>Intelligent Transformations based on user’s intention</h3><p>Data preparation also allows you to transform data by simply editing it directly and capturing what is the user&#39;s intention. Let’s see how this works with the time_on_site column.</p><p>The user noticed that time_on_site data is being delivered in seconds. However, the team needs it in minutes. The user simply goes to the column, adds a division by 60 and waits to see if Gemini gives a suggestion based on their intention. As you can see, Gemini gave the right suggestion that the user is probably trying to convert from seconds to minutes and it even suggests it to be rounded at 2 decimal places!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/965/0*1G5Pd8EDsU6Q0d2S" /></figure><p>If the suggestion does not show up right away, you may try to edit a few more rows, dividing them by 60 as well, so Gemini can properly get the intention of what you are trying to achieve.</p><pre>COALESCE(ROUND(CAST(time_on_site AS NUMERIC) / CAST(60 AS NUMERIC), 2), time_on_site)</pre><p>The user also knows that distance_to_store data was populated in miles. However, his team is used to do analyzes using the metric system, in kilometers. The user knows that miles can be converted to kilometers by multiplying it by 1.609. When the user adds this intention to the data preparation, Gemini is able to understand that the user wants to apply a conversion from miles to kilometers!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/978/0*IoDq0ic5j3GHAlGh" /></figure><pre>COALESCE(ROUND(distance_to_store * CAST(1.609 AS NUMERIC), 3), distance_to_store)</pre><h3>Handling Missing Data</h3><p>The user may also add default values to columns that contain null. The user wants the discount_code column to always have some value in it. The user picks a row that is null, types what is the default value that they want to see and Gemini suggests the transformation to it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/937/1*gikowHUQKwG9NUcNmrFnXQ.png" /></figure><pre>CASE WHEN discount_code is null THEN &#39;n/a&#39; ELSE discount_code END</pre><p>Sometimes the user just wants to filter out the null values. In this case, the user clicks on device_info and selects the suggestion of keeping only the rows that are not empty.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*pSZnGP2UMTst4wVb" /></figure><pre>device_info &lt;&gt; &#39;&#39;</pre><h3>Joining and Removing Columns</h3><p>To gain a more comprehensive understanding, users can combine data from multiple tables based on related columns, bringing together information that might be scattered across different sources. Data preparation allows the users to join the current data with other existing tables, enhancing or complementing the data that they need to prepare for their team.</p><p>Imagine that the user wants to join this web/app data with a customer table that exists in BigQuery. The user can easily achieve this by creating a Join, which is also powered by Gemini.</p><p>Once the user selects the table, Gemini automatically suggests which columns to join! In this case, Gemini suggested the join to be by email, which is a common column between the two tables. The user may also select the join type, which in this case will be an Inner join. Once the join is created, we may see all the columns from the customer table added to the data preparation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/964/1*m8_NjLT_KBadb22_dY70_g.png" /></figure><p>Now that the customer table is also joined, the user realizes that some columns are not needed to be available in the final result. One of them is customer_email, which has the same information as the user_email column. Data preparation allows the users to remove any column by going in the SCHEMA tab and dropping them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/846/1*7zqkqiHf9vODSlZwYBeg1Q.png" /></figure><h3>Saving and executing your data preparation</h3><p>Once the data is prepared, you can save the data preparation object. Depending on your organization’s policies, business users may be able to create a new table directly or they may need to share the data preparation object with a data engineer. If the later one is the case, the business user can easily share their data preparation object with the data engineer within the console.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wLSPBbEFfurS8BHWraewVA.png" /></figure><p>Independently if it’s the business user or the data engineer, the next step is to add a destination where this data will be loaded to. You can add a Destination step and configure the project/dataset/table as needed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/864/1*tfma7TJsG2SRqsvyKeP2GQ.png" /></figure><p>Data preparation gives the flexibility of selecting from different write modes (Full refresh, Append or Incremental) giving the users options on how the data should be ingested into the final table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ai2pT2VZF6iO1OEzeQ_0Bw.png" /></figure><p>After we select how the data should be written to our table, we may want to run the data preparation to see the data flowing to the destination table. Users have the option to run an ad hoc execution directly from the data preparation object or to create a schedule, which will execute the data preparation during specified time intervals. Let’s do both. First, let’s do an ad hoc execution.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LlnFNtZyr_M1mppOWncSTA.png" /></figure><p>We may follow the execution status right from the data preparation screen. We may even see what were the commands that were run as part of the data preparation. As you may see, all code is generated as SQL and it is executed in BigQuery itself, so it is transparent to the users as it does not require any extra configuration.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vZtfkQHahgp9NXoP9e6iSA.png" /></figure><p>Now let’s see how we could easily schedule this data preparation to run on a regular basis. We simply create a schedule and define its frequency.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*B2o7HzUh-XeTiazoTUVwqw.png" /></figure><p>The schedule is saved and it can be viewed and managed with all other scheduled objects in BigQuery, on the Scheduling menu.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1006/1*qwT24-Lg9x9Qhj51Od2P5A.png" /></figure><h3>Viewing the final results</h3><p>Now it’s time to see our final results! We may go to our dataset and see that a new table was created there. When we preview it, we may see the final data in the final format that was prepared. All of this without a single line of code written by the user [or very few if you needed to adjust any suggestion]!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bM3tvZhmC20aTqdhc9gzow.png" /></figure><h3>Saving and Version Controlling Your Work</h3><p>Version control is essential for any data workflow. Although data preparation doesn’t have direct Git integration yet, you can still export your work as a YAML file and save it in your Git repository within BigQuery. By exporting and saving data preparation objects in Git, users can track changes, revert to previous versions, and collaborate effectively.</p><p>To do this, go to the data preparation object and click DOWNLOAD. It will save a .yaml file in your system.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*xRJVrxdAe5M2hq6r" /></figure><p>As we upload our yaml file to Git, we can also see the graph representation of our data preparation. As with any other Git interaction, we add a comment to our version and commit/push the change to our repository.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N6XfuAMkEbQwVbAjzE2h5g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AXZuCLGo8tWfqzmdBxJKrw.png" /></figure><h3>Conclusion</h3><p>BigQuery data preparation is a powerful and user-friendly tool that simplifies the process of preparing data for analysis. From data cleansing and transformation to enrichment and delivery, it streamlines the journey from raw data to actionable insights.</p><p>In this blog post, we’ve shown you how it can handle a variety of data preparation tasks, empowering you to take control of your data. And with the rise of generative AI, we can expect even more intuitive and automated data preparation experiences in the future.</p><p>Ready to simplify your data transformation process? Dive into <a href="https://cloud.google.com/bigquery/docs/data-prep-introduction">BigQuery data preparation</a> now and experience the ease of building AI-powered data pipelines!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e358e5a91d15" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/bigquery-data-preparation-a-step-by-step-guide-e358e5a91d15">BigQuery data preparation: A Step-by-Step Guide</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>