Analytics with the Data Agent Kit and Antigravity IDE

1. Introduction

It's Monday morning and the CFO just pinged you. Average order value is down 7% this month, but total revenue is flat. Something doesn't add up, and the board wants answers by Friday.

Your company, Cymbal Pets, is one of the largest online pet supply retailers in the US. The data you need is scattered across three Google Cloud services: transactional data in BigQuery, customer and product records in Cloud SQL, and marketing files in Cloud Storage. Normally, pulling together a cross-service investigation like this means switching between consoles, writing connection boilerplate, and stitching results together manually.

In this codelab, you'll use the Google Cloud Data Agent Kit (DAK) in the Antigravity IDE to investigate the anomaly using natural language. You describe what you're looking for, and the AI agent handles the connections, SQL, and cross-service joins across BigQuery, Cloud SQL, and Cloud Storage. Once you've cracked the case, you'll ask the agent to build a dbt pipeline that operationalizes your findings, debug a real data modeling bug, and deliver a forecast-backed recommendation to the CFO.

New to Antigravity? If this is your first time using the IDE, check out the fantastic Getting Started with Google Antigravity codelab for a comprehensive primer.

What you'll do

• Discover data assets across BigQuery, Cloud SQL, and Cloud Storage using the Knowledge Catalog
• Investigate an anomaly by querying multiple services in a single conversation using MCP Tools
• Build a dbt pipeline to stage and join cross-service data with staging models and automated tests
• Debug a data modeling issue as the agent self-diagnoses and refactors a fan-out bug
• Forecast future trends and deliver a data-driven recommendation using BigQuery's AI.FORECAST

What you'll need

• A web browser such as Chrome
• A Google Cloud project with billing enabled
• Basic familiarity with SQL and Google Cloud Console

This codelab is for intermediate data practitioners (analytics engineers, data analysts, data scientists).

The resources created in this codelab should cost less than $5.

Choose Your Tool: This lab uses the Antigravity IDE. We will soon release alternate versions of this exact same investigation tailored for Antigravity 2.0, the Antigravity CLI and Claude Code!

2. Before You Begin

In this section, you'll run a setup script that provisions your entire lab environment: a BigQuery dataset with order data, a Cloud SQL Postgres instance with customer and product data, and a Cloud Storage bucket with promotional campaign records. The script takes about 8-10 minutes to complete, with Cloud SQL provisioning as the bottleneck.

Start Cloud Shell

You will use Google Cloud Shell to run the setup script.

1. Click Activate Cloud Shell at the top of the Google Cloud Console.

2. Once connected, set your project ID and confirm your environment:

gcloud config set project <<YOUR_PROJECT_ID>>
export PROJECT_ID=$(gcloud config get-value project)

You should see a message similar to:

Your active configuration is: [cloudshell-####]
Updated property [core/project]

Clone the Repository

Clone the codelab repository to your Cloud Shell environment:

cd ~/
git clone --filter=blob:none --no-checkout https://github.com/GoogleCloudPlatform/devrel-demos.git
cd ~/devrel-demos
git sparse-checkout init --cone
git sparse-checkout set codelabs/agentic-data-labs
git checkout main
cd codelabs/agentic-data-labs/

Run the Setup Script

The setup script prepares your entire lab environment in a few minutes. It handles enabling APIs, loading and augmenting BigQuery data, uploading promo assets to GCS, and then automatically spawns a background worker to provision and configure Cloud SQL Postgres in the background while you start the codelab.

The script securely auto-generates a Cloud SQL password and saves it to your .env file for you automatically.

cd ~/devrel-demos/codelabs/agentic-data-labs/scripts
chmod +x setup.sh setup_sql.sh
./setup.sh

When it finishes, you'll see a summary of your foreground environment:

╔══════════════════════════════════════════════════════╗
║   Base Setup complete!                               ║
╚══════════════════════════════════════════════════════╝

Your core BigQuery and GCS assets are ready.
Cloud SQL is currently provisioning in the background and will be fully ready by Step 4.

  BigQuery:   YOUR_PROJECT_ID.cymbal_pets
              ├── orders
              └── order_items

  GCS:        gs://YOUR_PROJECT_ID-cymbal-pets-raw
              └── promo_events.json

While you continue with the next steps of the lab, the database is being provisioned and seeded in the background. You can monitor its progress at any time in a separate terminal panel using:

tail -f /tmp/cloudsql_setup.log

Notice the data architecture: transactional data (orders and order items) lives in BigQuery, while operational data (customers, pet profiles, and products) will live in Cloud SQL. This split mirrors how data is often distributed across services in real organizations, and it's what makes a cross-service investigation interesting.

The base dataset here is the official Cymbal Pets e-commerce dataset (~1.9M orders across 92k customers), augmented with business-to-business records that embed the mystery you're about to investigate.

Section Recap: You ran the setup script to bootstrap your lab environment and kicked off background database provisioning.

3. Set Up the IDE and Data Agent Kit

Open the Antigravity IDE

You don't need to wait for Cloud SQL to finish! Go ahead and open the Antigravity IDE and connect it to your Google Cloud project.

1. If you haven't already, download and install the Antigravity IDE from the Google Antigravity download page.
2. Launch the Antigravity IDE desktop application.
3. Create a new, empty folder on your local machine (e.g named agentic-data-labs), and open it in the IDE by choosing Open Folder. This will act as your local workspace for the codelab.

Install the Data Agent Kit Extension

The Google Cloud Data Agent Kit extension provides deep integration with Google Cloud data services directly within your editor, allowing you to interact with BigQuery, Cloud SQL, Cloud Storage, and more without switching contexts.

1. In the Antigravity IDE, click the Extensions icon in the Activity Bar on the far left side of the screen (it looks like four squares).
2. In the search bar at the top of the Extensions pane, type Google Cloud Data Agent Kit.
3. Locate the extension named Google Cloud Data Agent Kit published by googlecloudtools
4. Click the Install button.
5. A prompt may appear asking, "Do you trust publisher ‘googlecloudtools' and their extensions?". Click Trust Publishers & Install to proceed.

Once installed, you'll see a new Google Cloud Data Agent Kit icon appear in the Activity Bar on the far left of the Antigravity IDE.

Authenticate and Configure the Extension

After installation, connect the extension to your Google Cloud project.

1. An onboarding page titled "Welcome to Google Cloud Data Agent Kit" should automatically open. If you aren't signed into your Cloud account, follow any prompts to allow access.
2. In the Configuration Summary section, locate the project field. Click the dropdown and select your Google Cloud project. Set your region as us-central1. Then select Configure MCP Servers.

3. Under the MCP Configuration pane, click to enable BigQuery and Cloud SQL. Then click Get Started.

Already installed or welcome page didn't open? You can open the extension settings manually at any time. Click the Google Cloud Data Agent Kit icon in the Activity Bar on the far left, then select Settings, to toggle your configuration.

Note on active skills/MCPs: If you have other custom skills or MCP servers active in your IDE, they might interfere with the expected flow of this lab. If the agent behaves unexpectedly, consider disabling them for this lab.

Explore Configuration Options

Once setup is complete, you'll land on the "Get started with Google Cloud Data Agent Kit" page.

1. Under "Setup & Configuration", click Get Started.
2. This opens the Data Agent Configuration panel. Explore the tabs:
   • Project and Region: Verify your selected Project ID and check that the required APIs (Cloud Storage API, BigQuery API, Catalog API, and Cloud SQL Admin API) are enabled.
   • BigQuery: Configure the default location for your BigQuery queries. Use the region us-central1.
   • Configure MCP Servers: View the enabled MCP servers (BigQuery, Notebooks, Cloud SQL, etc.) that allow AI agents to securely interact with your data.
   • Skills: Explore pre-built skills that provide agents with specialized capabilities for complex data tasks.

Section Recap: You opened the Antigravity IDE, connected it to your Google Cloud project, configured the Data Agent Kit remote MCP servers, and verified the connection by running a query against BigQuery.

4. Discover Your Data

Time to set the scene. Here's the situation: the CFO says average order value dropped 7% last month, but total revenue is flat. Before you start asking the agent to investigate, you should first understand what data you're working with.

In this section, you'll manually explore the Data Agent Kit panel to get a lay of the land. Understanding your data before you start querying it is a critical first step in any investigation.

Explore BigQuery Tables

1. In the Data Agent Kit panel, under CATALOG, expand your project → BigQuery → cymbal_pets.
2. Click on the orders table. A new tab opens showing the table's details.
3. Explore the tabs along the left side of the table viewer:
   • Data: Preview actual rows. Scroll through the dataset and examine the columns.
   • Schema: Review the column names and types. Notice fields like order_type and promo_code which will become important later.
   • Other tabs (Details, Insights, Data Profile, etc.): Access metadata, data lineage, and quality details that you would normally find in the Google Cloud console—all without leaving your editor.

4. Now click on the order_items table and review its schema. Notice the quantity and price fields.

Spotting clues early. As you browse the data, you might already notice some things that seem unusual: a B2B-Wholesale order type and a promo_code field. Don't worry about connecting the dots yet. The agent will help with that.

Explore Cloud SQL Tables

The setup script also placed customer, pet, and product data in a PostgreSQL database in Cloud SQL.

5. In the Data Agent Kit panel, click on Universal Search under the CATALOG section.
6. In the search box, type pet_profiles and press Enter.
7. In the search results, click on the PostgreSQL Table result for pet_profiles (under your project's Cloud SQL instance). Notice that the sidebar accordion automatically expands, showing you exactly where the table lives in the database tree. Now click on the customers table located right above it in the tree to open its details, and explore the Schema and Details tabs.

Explore Cloud Storage Files

Finally, marketing and promotional campaign records are stored as raw JSON files in Cloud Storage.

8. In the Data Agent Kit panel on the left, expand the CLOUD STORAGE section. Locate your project's raw bucket (YOUR_PROJECT_ID-cymbal-pets-raw).
9. Click on the promo_events.json file inside the bucket. A new editor tab opens, allowing you to view the raw JSON Lines content of the marketing campaigns directly inside the IDE.

Take Stock

Here's what you now know about the data landscape:

Service	Tables	What's There
BigQuery	orders, order_items	~1.9M orders, ~4.3M line items, date range 2023-2025
Cloud SQL	customers, pet_profiles, products	~92K customers, ~7.6K pet profiles, 206 products
Cloud Storage	promo_events.json	Promotional campaign records

Why is the data split across services? This mirrors real-world architectures. Cloud SQL is an OLTP (transactional) database. It's optimized for fast, row-level operations like updating a customer's address or processing a single order. BigQuery is an OLAP (analytical) warehouse. It's better for scanning millions of rows to answer questions like "what's our average order value this month?" In most organizations, you'll find data living in both, which is why cross-service investigation tools like the Data Agent Kit are so important.

The data is spread across three services. In a traditional workflow, you'd need to set up connections, write integration code, and manually join results. In the next step, you'll let the AI agent handle all of that through a single conversation.

Section Recap: You used the Data Agent Kit panel to manually explore the data architecture across BigQuery, Cloud SQL, and Cloud Storage. You now know where the data lives and what fields are available, so you're ready to start the investigation.

5. Follow the Numbers

Now the investigation begins. You'll use the Chat pane to ask the AI agent to pull Average Order Value (AOV) data from BigQuery. AOV is a business metric representing the average dollar amount spent per order. The agent will query on your behalf using MCP Tools, and you'll be able to see every SQL query it runs.

Pull the Average Order Value Trend

1. In the Chat pane on the right side of the IDE, type the following prompt and press Enter:

   Calculate our monthly average order value from August 2024 through January 2025
   using the orders and order_items tables in BigQuery.
   

2. Approve Data Access Permissions. It's healthy to be cautious about AI agents running queries on your databases. The Data Agent Kit keeps you in control by pausing to ask for explicit permission before accessing data. When prompted, you can choose:
   • Allow this time: Approves a single use (ideal for auditing high-risk queries).
   • Always allow: Approves ongoing use of this specific tool for the session.
   • No: Blocks the action completely.
   For the smoothest lab experience, select Yes, and always allow.Note: Permissions are granted on a per-tool basis. You will likely see a few more prompts shortly as the agent uses new tools (like list_table_ids or execute_sql_readonly). Feel free to "always allow" these as well.

3. Watch the agent work. The Chat pane doubles as a transparency log for everything the agent does. Instead of a black box, the agent shows you its reasoning and actions in real time.
4. Once the agent finishes, click the Worked for Xm dropdown below your prompt to expand the full work log. Here you can inspect exactly how it got your answer:
   • Explored: Expand these items to see the agent reading files, browsing folders, or calling MCP tools (like datacloud_bigquery_remote / list_table_ids and execute_sql_readonly). You can view the exact JSON arguments passed to the tools and the SQL executed.
   • Ran: Expand these items to see any terminal commands the agent executed, such as gcloud config list.

5. Review the results. The agent should return a table of monthly AOV values. Look at the numbers yourself: prior months hover around ~$110, then January dips to around ~$103. That's the anomaly the CFO flagged.

What are MCP Tools and Agent Skills? MCP Tools are the secure connections that let the agent interact with external services like BigQuery and Cloud SQL. Agent Skills are pre-built instruction sets (like discovering-gcp-data-assets) that teach the agent how to approach specific tasks. Together, they form an auditability layer. Click into any step to see exactly what the agent did and why.

Drill Down by Channel

The overall AOV dropped, but where is the drop coming from? Let's find out.

5. In the Chat pane, type:

   January looks lower than the prior months. 
   Break down January's AOV by order_type to see what's going on?
   

6. The agent runs another BigQuery query, this time grouping by order_type. Review the results carefully.You should see something striking: Online and Offline AOV remain stable at ~$110. But there's a new channel, B2B-Wholesale, with a much lower AOV (around ~$75). This new channel is dragging down the blended average.
7. The agent may proactively suggest investigating the B2B customers. If it doesn't, that's fine. You'll do that in the next step.

The agent connects the dots. Notice that the agent interprets the results and may volunteer insights like "The B2B-Wholesale channel appears to be a new order type that wasn't present in prior months." This kind of proactive analysis is a key feature of the DAK agent.

Section Recap: You spotted the January AOV dip yourself from a neutral data pull, then drilled in by order_type to identify B2B-Wholesale as the new channel pulling down the blended average. Now you need to find out who these B2B customers are.

6. Cross the Service Boundary

You've identified B2B-Wholesale as the anomalous channel in BigQuery, but the customer data lives in Cloud SQL. With the Data Agent Kit, you can keep the same conversation going and it handles the service boundary.

Investigate the B2B Customers

1. In the Chat pane, type:

   Who are these B2B customers? Their profiles should be in our Cloud SQL database. 
   Check for:
   - Who they are
   - When they signed up
   - Whether they're new or existing customers
   

2. Watch the Chat pane carefully. You should see a different MCP Tool appear this time. The agent is now querying Cloud SQL instead of BigQuery.The agent connects to the cymbal-pets-ops Cloud SQL Postgres instance and runs a query against the customers table. Click Show Details to see the SQL.
3. Review the results. The agent should surface several key findings:
   • All B2B customers have customer_type = 'Business'
   • They all signed up within the last 30 days (January 2025)
   • Their last_name values are business names like "Pet Supply Co", "Animal Care LLC", and "Happy Paws Inc"
   • There are about 100 of them, a cohort that didn't exist before this month

Crossing service boundaries. The agent queried across two different Google Cloud services (BigQuery and Cloud SQL) within the same conversation. Notice the different MCP Tool name in the Chat pane. No connection setup, no credential configuration. The agent handled that automatically.

Connect the Promo Code

4. The agent may notice on its own that many B2B orders in BigQuery carry a promo_code value of BIGORDER25. If it volunteers this observation, great. The investigation is naturally progressing.If the agent doesn't mention the promo code, nudge it:

   I noticed a promo_code field on the orders table in BigQuery. 
   Check what promo codes appear on the B2B-Wholesale orders?
   

5. The agent queries BigQuery again and finds that approximately 92% of B2B-Wholesale orders have promo_code = 'BIGORDER25'. Nearly all B2B activity is tied to a single promotional campaign.The agent should now be curious about where this promo code came from. It may ask whether there's promotional data anywhere else in the environment. (There is, in Cloud Storage.)

Section Recap: The agent queried Cloud SQL to reveal that B2B customers are all new businesses that signed up in January 2025. Combined with the BigQuery finding that ~92% of their orders carry promo_code = 'BIGORDER25', the trail now points toward a promotional campaign. Time to find the source.

7. Find the Missing Piece

Two services down, one to go. You know what happened (B2B orders are dragging down AOV) and who is doing it (new Business customers from the last 30 days). Now you need to find why, and the answer is in Cloud Storage.

Check the GCS Bucket

1. In the Chat pane, type:

   Good catch on the promo code. 
   We might have promotional campaign data in our GCS bucket. 
   Can you check what's there?
   

2. The agent doesn't have a pre-configured MCP tool for Cloud Storage, so it automatically pivots to using its terminal tool to run gcloud storage commands. It will ask for permission to run commands like gcloud storage ls. Allow these commands, then expand the Ran log in the Chat pane to see the exact CLI commands it used to read and parse the promo_events.json file.
3. The agent should identify three promotional campaigns in the file:

   Campaign	Promo Code	Discount	Target	Dates
   Summer Pet Care Sale	PETSUMMER15	15% off	All	Jun 2024
   B2B Wholesale Push	BIGORDER25	25% off	B2B	Jan 2025
   Loyalty Member Holiday Bonus	LOYAL10	10% off	Loyalty Members	Dec 2024

   There it is. The BIGORDER25 promo code maps to a campaign called B2B Wholesale Push: 25% off for B2B customers with a minimum order quantity of 50 units. This is the smoking gun.

Three services, one conversation. In the last three sections, the agent has queried BigQuery (transactional data), Cloud SQL (customer data), and Cloud Storage (promotional data), all from the same chat thread, without any configuration or context-switching on your part. This is the core value of the Data Agent Kit: a single AI agent that operates across your entire data estate.

Put It All Together

4. Ask the agent to synthesize everything it's found:

   Put it all together. 
   What happened to our average order value?
   

5. The agent delivers a clear, structured synthesis connecting all three data sources. It should explain something like:
   1. The AOV drop is real, but it's not a decline in existing business. Online and Offline AOV remain stable at ~$110.
   2. A new B2B-Wholesale channel appeared in January 2025, with ~25,000 orders at a much lower AOV (~$75-100).
   3. The B2B customers are 100 new business accounts that all signed up within the last 30 days (Cloud SQL).
   4. The activity is driven by a promotional campaign ("B2B Wholesale Push") offering 25% off bulk orders with a 50-unit minimum (Cloud Storage).
   5. Revenue is flat because the high volume of B2B orders offsets the lower prices. However, unit margins are heavily compressed (eroded by ~65%) under the 25% wholesale discount, severely threatening overall profitability when shipping and operational overhead are factored in.
   This is the moment the investigation clicks. The CFO's question has a clear answer: AOV dropped because a marketing-driven B2B program flooded January with high-volume, low-price orders. The existing business is healthy.

Section Recap: You found the smoking gun in Cloud Storage: a B2B promotional campaign offering 25% off bulk orders. The agent synthesized findings across all three services into a clear narrative. The investigation phase is complete. Next, you'll operationalize these findings.

8. Build the Pipeline

You've cracked the case. Now the CFO wants this analysis to update automatically. In this section, you'll ask the agent to build a dbt project that stages the BigQuery data and produces a fact table for ongoing AOV analysis.

This is where the agent shifts from investigator to engineer. You'll see it scaffold an entire dbt project and run the full pipeline, all from a single prompt.

What is dbt? dbt (data build tool) is a framework that lets data practitioners transform data in their warehouse using SQL. It treats SQL like code, adding version control, automated testing, and dependency management.

Scaffold the dbt Project

1. In the Chat pane, type the following prompt. This is deliberately goal-oriented rather than step-by-step. You're telling the agent what you want, not how to build it:

   I want to productionize our AOV analysis so it updates automatically. Build a dbt project that:
   1. Creates staging models for the BigQuery tables (orders and order_items) and a mart called fct_order_analysis that calculates AOV by channel and month
   2. Add a uniqueness test on order_id and run dbt build
   

2. Observe Self-Correction: If you expand the "Worked for Ns" log, you may see the agent check for dbt and, upon finding it missing, automatically run commands to create a Python virtual environment (.venv). It's handling the environment setup for you!

3. Review the Implementation Plan: The agent will generate a formal implementation plan. You can review its proposed files and architecture, add comments if needed, and click Proceed to let the agent execute the plan.

4. Watch the Chat pane as the agent executes its plan, writing the necessary .sql files and YAML configurations. When it finishes and successfully compiles the project, it will present a summary of the changes. Click Accept all to add these files to your workspace.

5. Explore the newly generated dbt project in the Explorer on the left. You should see a structure similar to:

   dbt/
   ├── models/
   │   ├── marts/
   │   │   └── fct_order_analysis.sql
   │   └── staging/
   │       ├── schema.yml
   │       ├── sources.yml
   │       ├── stg_order_items.sql
   │       └── stg_orders.sql
   ├── dbt_project.yml
   └── profiles.yml
   

6. Click on the .sql model files to review the SQL the agent generated. Pay attention to how it handles:
   • Staging models: Clean, renamed columns with source references
   • The mart model: The join logic and AOV calculation by channel

Reviewing the Mart Code: Open models/marts/fct_order_analysis.sql. Notice how it aggregates line items first to keep the fact table at the order grain. Tip: Only edit source files inside the models/ directory, not the auto-generated ones in /target/!

7. Check the Chat pane for the agent's confirmation that all models materialized and all tests passed. The AOV results from the mart should confirm what you found during the investigation:

   - Online: ~$110
   - Offline: ~$110
   - B2B-Wholesale: ~$75 to $77
   

Section Recap: The agent built a dbt project from a single goal-oriented prompt: scaffolded staging and mart models, ran a successful dbt build, and confirmed the AOV anomaly. Next, you'll throw a curveball to see how the agent handles complexity.

9. When Tests Fail, the Agent Debugs

The pipeline works, but it only uses BigQuery data. The product team wants to enrich the analysis with customer and pet profile data from Cloud SQL so they can recommend products based on dietary needs. This means the agent needs to bridge the Cloud SQL boundary and handle a subtle data modeling bug—a classic dimensional modeling "fan-out" join.

What is a "fan-out"? If one order belongs to a customer who has 3 pets, a standard SQL JOIN will create 3 identical rows for that single order (one for each pet). This "fans out" the data and ruins metrics like Average Order Value because it counts the same order's revenue 3 times! To fix this, you must aggregate the pets into a list (array) before joining.

Depending on the model you are using and its reasoning capabilities, the agent will handle this request in one of two ways: Proactively avoiding the bug (Option A) or Self-healing after a test failure (Option B). Let's see which path your agent takes!

Trigger the Request

1. In the Chat pane, type:

   Enrich fct_order_analysis with customer data and pet profile data from our Cloud SQL database. 
   Include customer type and each customer's pets and dietary needs so we can recommend products. 
   Keep the uniqueness test on order_id and run dbt build.
   

2. Watch the agent work. It will discover the Cloud SQL tables, figure out how to bridge the data into BigQuery (via federated query or materialized copy), create new staging models, and modify fct_order_analysis.sql.

Option A: The Proactive Agent (Bug Avoidance)

If you are using an advanced reasoning model, the agent may detect the grain shift before writing any code. It realizes that since a customer can own multiple pets, a direct join will duplicate orders and immediately fail the uniqueness test you requested on order_id.

1. Observe the Proactive Aggregation: In its Chat pane explanation or Walkthrough artifact, the agent may note that it pre-aggregated the pet data before joining it to prevent a "classic fan-out". It will typically do this by collapsing multiple pets per customer using an aggregation function (e.g., ARRAY_AGG() or STRING_AGG()).
2. Check the Results: The dbt build runs and passes successfully on the first try because the agent proactively guarded the fact table's granularity. You can verify this by checking the generated Walkthrough artifact, which often shows the successful test output alongside the query results.

If your agent did this, congrats! You've witnessed proactive AI engineering. Take a moment to review the generated SQL in fct_order_analysis.sql to see how it structured the aggregation, then skip ahead to the next section, Deliver the Answer.

Option B: The Self-Healing Agent (Debugging & Diagnostics)

If the model writes a naive direct left join first, the SQL query itself will run successfully, but the automated dbt test suite will catch the grain shift!

1. Observe the Test Failure: You will see the failure reported in the Chat pane execution progress logs:

   Completed with 1 error
   
   Failure in test unique_fct_order_analysis_order_id
   Got 287 results, configured to fail if != 0
   

   The uniqueness test on order_id found duplicate entries because customers with multiple pets fanned out the orders.
2. Let the Agent Diagnose & Self-Heal: Since the test failed, ask the agent to debug it. In the Chat pane, type:

   The uniqueness test failed. Can you figure out why and fix it?
   

3. Watch the Diagnosis: The agent will query the data, discover the one-to-many relationship in pet_profiles, explain that joining it directly changes the grain from one-row-per-order to one-row-per-order-per-pet, and rewrite the model to pre-aggregate the pet profiles:

   -- Pre-aggregating pets per customer to resolve fan-out
   LEFT JOIN (
     SELECT
       customer_id,
       COUNT(*) AS num_pets,
       STRING_AGG(DISTINCT pet_type, ', ') AS pet_types,
       STRING_AGG(DISTINCT dietary_needs, ', ') AS dietary_needs
     FROM pet_profiles
     GROUP BY customer_id
   ) pet_agg ON c.customer_id = pet_agg.customer_id
   

4. Verify the Fix: The agent runs dbt build again, and this time all models materialize and all tests pass successfully!

Section Recap: Whether your agent proactively avoided the bug or successfully self-healed after a test failure, you've seen it bridge the Cloud SQL boundary, integrate customer and pet profile data, and maintain perfect fact table granularity. The pipeline is now robust, complete, and fully tested!

10. Deliver the Answer

It's Thursday. You started the week with a worried CFO and scattered data across three cloud services. Now you have the root cause and a production pipeline. Time to deliver the answer, along with a forward-looking recommendation backed by a quantitative forecast.

Write the Executive Summary

1. In the Chat pane, type:

   Write an executive summary covering:
   - Main findings and the quantitative margin impact
   - Project AOV for the subsequent quarter if the B2B program continues at its current trajectory
   - A data-driven recommendation
   

2. Watch the agent work.

   A Note on Output Formats & Variation: Large language models are non-deterministic. Depending on the active model, the agent may output the executive summary directly in the Chat pane, or it may create a dedicated Markdown file (like executive_summary_cfo.md or output.txt) and link it in chat.

   The exact numbers, calculated totals (such as potential lost margin or average AOV drops), and formatting layout will fluctuate from run to run. What matters is the core structural logic of the findings.

3. Review the agent's executive summary. A typical and well-structured response should address:
   • Core Finding: January AOV dropped solely due to the new B2B-Wholesale channel. Online & Offline remain stable at ~$110.
   • Root Cause: The "B2B Wholesale Push" (25% off bulk orders) attracted 100 new accounts, driving ~25,000 orders.
   • Margin Impact: Wholesale orders compressed average unit profit by ~65% (from ~$7.50 to ~$2.60).
   • Revenue: Flat overall revenue as high B2B volume offsets the lower prices.

Forecast AOV with AI.FORECAST

4. The agent should also generate a forward-looking projection. Look for an MCP Tool call where the agent runs an AI.FORECAST query against BigQuery. This uses the built-in TimesFM foundation model to project AOV forward 90 days based on historical trends.The query should project AOV 90 days forward under two scenarios: campaign continuation (structurally depressed AOV) vs. campaign termination (recovery to ~$110).

Nudge (if the agent doesn't use AI.FORECAST): If the agent provides a qualitative projection instead of a quantitative one, try:

Use BigQuery's AI.FORECAST function to generate a 90-day forecast with confidence intervals.

5. Review the agent's strategic recommendations. A robust set of recommendations might include:
   • Restructure discounts: Implement margin floors or cap bulk discounts to protect unit-level margins.
   • Enforce stricter MOQs: Prevent retail buyers from abusing wholesale pricing.
   • Separate reporting: Track retail and B2B divisions independently to avoid masking retail performance.

The Full Story

What began on Monday as a fire drill over a 7% drop in Average Order Value has a clear resolution for the CFO:

• Retail Health: Core retail channels remain healthy and stable at baseline.
• Wholesale Influx: The AOV drop is entirely due to the new B2B Wholesale channel and the BIGORDER25 campaign.
• Margin Impact: The 25% bulk discount heavily eroded unit margins, threatening profitability despite flat revenue.
• Strategic Forecast: An AI.FORECAST projection shows that restructuring wholesale tiers will restore the blended AOV.

You deliver a data-backed recommendation to establish wholesale margin floors and separate retail/B2B reporting.

Section Recap: You asked the agent to write an executive summary with margin analysis, generate an AI.FORECAST projection, and deliver a data-driven recommendation. The investigation is complete.

11. Clean Up

To avoid incurring ongoing charges to your Google Cloud account, delete the resources created in this codelab by running the teardown script.

1. In the Terminal panel at the bottom of the Antigravity IDE (or in Cloud Shell), navigate to the codelab directory and run:

cd ~/devrel-demos/codelabs/agentic-data-labs/scripts
chmod +x teardown.sh
./teardown.sh

2. The script will display all the resources it plans to delete and ask for confirmation before proceeding:
   • Cloud SQL instance (cymbal-pets-ops): This is the most expensive resource
   • BigQuery dataset (cymbal_pets): All tables and models
   • Cloud Storage bucket (gs://YOUR_PROJECT_ID-cymbal-pets-raw)
   • BigQuery connection (cymbal-pets-cloudsql)
3. Type y to confirm. The teardown takes about 2-3 minutes.

[INFO]  Deleting Cloud SQL instance cymbal-pets-ops...
[ OK ]  Cloud SQL instance deleted.
[INFO]  Deleting BigQuery dataset cymbal_pets...
[ OK ]  BigQuery dataset deleted.
[INFO]  Deleting GCS bucket gs://YOUR_PROJECT_ID-cymbal-pets-raw...
[ OK ]  GCS bucket deleted.

Don't forget this step! The Cloud SQL instance incurs charges even when idle. If you skip the teardown, you can always run it later or delete the entire Google Cloud project you used for this codelab.

12. Congratulations!

You've successfully completed The Cymbal Pets Investigation! You went from a vague CFO question to a fully operationalized, forecast-backed recommendation, using an AI agent that works across your entire Google Cloud data estate.

What you accomplished

1. 🔍 Explored across services: Discovered and previewed assets in BigQuery, Cloud SQL, and Cloud Storage using the Data Agent Kit's Knowledge Catalog.
2. 🕵️‍♂️ Investigated with AI: Queried multiple services in a single chat pane conversation using MCP Tools to trace the AOV anomaly to a bulk B2B promotional campaign.
3. 🔧 Built a production pipeline: Scaffolded a complete dbt project to clean, join, and test transactional and customer data.
4. 🐛 Debugged a fan-out bug: Observed the agent automatically diagnose a granularity issue and refactor the dbt SQL model to pre-aggregate customer pet profiles.
5. 📈 Forecasted and recommended: Used BigQuery's built-in AI.FORECAST to model AOV trends and delivered a data-driven recommendation to the CFO.

Key concepts

Concept	What You Learned
MCP Tools	Secure, auditable connections that let the AI agent query services like BigQuery, Cloud SQL, Spanner, and other databases on your behalf, with every call visible in the Chat pane
Agent Skills	Pre-built instruction sets (like dbt-bigquery or discovering-gcp-data-assets) that teach the agent domain-specific best practices without you having to prompt for them
Cross-service investigation	The agent queries multiple Google Cloud services in a single conversation — no connection setup, no context-switching between consoles
Goal-oriented prompting	Telling the agent what you want ("build a dbt project that calculates AOV by channel") rather than how, and letting it choose the implementation approach
Data Agent Kit	The extension that binds everything together — MCP Tools, Agent Skills, and data discovery — giving you access to your entire Google Cloud data estate from within your IDE of choice

Next steps

• Read the Data Agent Kit documentation to learn more about its capabilities
• Learn about BigQuery ML and AI functions including AI.FORECAST, AI.GENERATE, and AI.EMBED
• Try building your own cross-service investigation with the Antigravity IDE on your own data