تجزیه و تحلیل با کیت عامل داده و محیط توسعه یکپارچه آنتی‌گراویتی

۱. مقدمه

صبح دوشنبه است و مدیر ارشد مالی همین الان با شما تماس گرفت. میانگین ارزش سفارش‌ها در این ماه ۷ درصد کاهش یافته، اما درآمد کل ثابت مانده است. یک جای کار می‌لنگد و هیئت مدیره تا جمعه پاسخ می‌خواهد.

شرکت شما، Cymbal Pets، یکی از بزرگترین خرده‌فروشان آنلاین لوازم حیوانات خانگی در ایالات متحده است. داده‌های مورد نیاز شما در سه سرویس Google Cloud پراکنده است: تاریخچه فروش و سفارش در BigQuery ، سوابق مشتری و محصول در Cloud SQL و فایل‌های بازاریابی در Cloud Storage . معمولاً، جمع‌آوری یک تحقیق بین سرویسی مانند این به معنای جابجایی بین کنسول‌ها، نوشتن متن‌های تکراری و اتصال نتایج به صورت دستی است.

در این آزمایشگاه کد، شما از کیت عامل داده ابری گوگل (DAK) در محیط برنامه‌نویسی Antigravity برای بررسی ناهنجاری با استفاده از زبان طبیعی استفاده خواهید کرد. شما آنچه را که به دنبال آن هستید توصیف می‌کنید و عامل هوش مصنوعی، اتصالات، SQL و اتصالات بین سرویسی را در سراسر BigQuery، Cloud SQL و Cloud Storage مدیریت می‌کند. پس از حل مسئله، از عامل می‌خواهید که یک خط لوله داده ابری (dbt pipeline) برای عملیاتی کردن یافته‌های شما، اشکال‌زدایی یک اشکال مدل‌سازی داده‌های واقعی و ارائه یک توصیه مبتنی بر پیش‌بینی به مدیر ارشد مالی (CFO) ایجاد کند.

کاری که انجام خواهید داد

• با استفاده از Knowledge Catalog، دارایی‌های داده را در BigQuery ، Cloud SQL و Cloud Storage کشف کنید.
• با استفاده از ابزارهای MCP ، با پرس و جو از چندین سرویس در یک مکالمه، یک ناهنجاری را بررسی کنید
• ساخت یک خط لوله dbt برای مرحله‌بندی و اتصال داده‌های بین سرویس‌ها با مدل‌های مرحله‌بندی و تست‌های خودکار
• اشکال‌زدایی یک مشکل مدل‌سازی داده‌ها، همزمان با خود-تشخیصی و اصلاح یک اشکال فانکشنال توسط عامل
• پیش‌بینی روندهای آینده و ارائه پیشنهاد مبتنی بر داده با استفاده از AI.FORECAST BigQuery.FORECAST

آنچه نیاز دارید

• یک مرورگر وب مانند کروم
• محیط برنامه‌نویسی ضد جاذبه
• یک پروژه گوگل کلود با قابلیت پرداخت و دسترسی به کنسول گوگل کلود (توصیه می‌کنیم برای آزمایش‌های عملی از یک پروژه جدید و اختصاصی استفاده کنید)
• آشنایی اولیه با SQL و کنسول ابری گوگل

این آزمایشگاه کد برای متخصصان داده سطح متوسط ​​(مهندسان تجزیه و تحلیل، تحلیلگران داده، دانشمندان داده) است.

منابع ایجاد شده در این آزمایشگاه کد باید کمتر از ۵ دلار هزینه داشته باشند. حتماً دستورالعمل‌های پاکسازی در انتهای آزمایشگاه را برای حذف منابع تأمین‌شده دنبال کنید.

۲. قبل از شروع

در این بخش، یک اسکریپت راه‌اندازی اجرا خواهید کرد که کل محیط آزمایشگاه شما را آماده‌سازی می‌کند: یک مجموعه داده BigQuery با داده‌های سفارش، یک نمونه Cloud SQL Postgres با داده‌های مشتری و محصول، و یک مخزن ذخیره‌سازی ابری با سوابق کمپین‌های تبلیغاتی. تکمیل این اسکریپت حدود ۸ تا ۱۰ دقیقه طول می‌کشد، و آماده‌سازی Cloud SQL به عنوان گلوگاه عمل می‌کند.

انتخاب یا ایجاد پروژه

یک پروژه موجود را انتخاب کنید یا یک پروژه جدید در کنسول Google Cloud ایجاد کنید .

تأیید صورتحساب

مطمئن شوید که پرداخت برای پروژه Google Cloud شما فعال است. می‌توانید با دنبال کردن این راهنما ، اطلاعات بیشتری در مورد نحوه انجام این کار کسب کنید.

شروع پوسته ابری

برای اجرای اسکریپت راه‌اندازی، از Google Cloud Shell استفاده خواهید کرد.

1. کنسول گوگل کلود را باز کنید و در بالای پنجره روی فعال‌سازی کلود شل کلیک کنید.

2. پس از اتصال، شناسه پروژه خود را تنظیم کرده و محیط خود را تأیید کنید:

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

شما باید پیامی مشابه زیر را ببینید:

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

مخزن را کلون کنید

مخزن codelab را در محیط Cloud Shell خود کلون کنید:

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/

اسکریپت راه‌اندازی را اجرا کنید

اسکریپت راه‌اندازی، کل محیط آزمایشگاه شما را به‌طور خودکار آماده می‌کند تا بتوانید مستقیماً وارد مرحله‌ی بررسی شوید:

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

پس از اتمام، خلاصه‌ای از محیط پیش‌زمینه خود را مشاهده خواهید کرد:

╔══════════════════════════════════════════════════════╗
║   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

در حالی که شما مراحل بعدی آزمایشگاه را ادامه می‌دهید، پایگاه داده در پس‌زمینه در حال آماده‌سازی و بارگذاری است. می‌توانید پیشرفت آن را در هر زمان در یک پنل ترمینال جداگانه با استفاده از موارد زیر رصد کنید:

tail -f /tmp/cloudsql_setup.log

به معماری داده‌ها توجه کنید: سوابق فروش تاریخی (سفارش‌ها و اقلام سفارش) در BigQuery نگهداری می‌شوند، در حالی که داده‌های عملیاتی برنامه (مشتریان، پروفایل‌های حیوانات خانگی و محصولات) در Cloud SQL نگهداری می‌شوند. این تقسیم‌بندی، سازمان‌های دنیای واقعی را منعکس می‌کند که در آن‌ها انبارهای تحلیلی و پایگاه‌های داده عملیاتی، قطعات مختلف پازل را در خود جای می‌دهند.

خلاصه بخش: شما اسکریپت راه‌اندازی را برای راه‌اندازی مجدد محیط آزمایشگاه خود اجرا کردید و آماده‌سازی پایگاه داده در پس‌زمینه را آغاز کردید.

۳. IDE و Data Agent Kit را راه‌اندازی کنید

نرم‌افزار Antigravity IDE را باز کنید.

لازم نیست منتظر بمانید تا Cloud SQL تمام شود! Antigravity IDE را باز کنید و آن را به پروژه Google Cloud خود متصل کنید.

1. اگر هنوز Antigravity IDE را دانلود و نصب نکرده‌اید، آن را از صفحه دانلود Google Antigravity دانلود و نصب کنید.
2. برنامه دسکتاپ Antigravity IDE را اجرا کنید.
3. یک پوشه جدید و خالی روی دستگاه محلی خود ایجاد کنید (مثلاً با نام agentic-data-labs ) و با انتخاب Open Folder آن را در IDE باز کنید. این پوشه به عنوان فضای کاری محلی شما برای codelab عمل خواهد کرد.

افزونه Data Agent Kit را نصب کنید

افزونه Google Cloud Data Agent Kit یک مرورگر کاتالوگ داده، مهارت‌های عامل و سرورهای MCP را برای BigQuery، Cloud SQL و Cloud Storage اضافه می‌کند، بنابراین می‌توانید از طریق ویرایشگر، این سرویس‌ها را جستجو و بررسی کنید.

1. در محیط توسعه آنتی‌گراویتی (Antigravity IDE)، روی آیکون افزونه‌ها (Extensions) در نوار فعالیت (Activity Bar) در سمت چپ صفحه کلیک کنید (شکل آن شبیه چهار مربع است).
2. در نوار جستجو در بالای پنل افزونه‌ها، عبارت Google Cloud Data Agent Kit تایپ کنید.
3. اولین نتیجه با نام Google Cloud Data Agent Kit (منتشر شده توسط googlecloudtools ) را پیدا کنید.
4. روی دکمه نصب کلیک کنید.
5. ممکن است پیامی ظاهر شود که می‌پرسد: «آیا به ناشر «googlecloudtools» و افزونه‌های آن اعتماد دارید؟» برای ادامه، روی «اعتماد به ناشران و نصب» کلیک کنید.

پس از نصب، آیکون جدید Google Cloud Data Agent Kit را در نوار فعالیت (Activity Bar) در سمت چپ Antigravity IDE مشاهده خواهید کرد.

تأیید اعتبار و پیکربندی افزونه

پس از نصب، افزونه را به پروژه Google Cloud خود متصل کنید.

1. یک صفحه‌ی شروع با عنوان «به کیت عامل داده‌های ابری گوگل خوش آمدید» باید به‌طور خودکار باز شود. اگر وارد حساب ابری خود نشده‌اید، برای اجازه دسترسی، هرگونه درخواستی را دنبال کنید.
2. در بخش خلاصه پیکربندی ، فیلد پروژه را پیدا کنید. روی منوی کشویی کلیک کنید و پروژه Google Cloud خود را انتخاب کنید. منطقه خود را به عنوان us-central1 تنظیم کنید. سپس پیکربندی سرورهای MCP را انتخاب کنید.

3. در زیر پنل پیکربندی MCP ، برای فعال کردن BigQuery و Cloud SQL کلیک کنید. سپس روی Get Started کلیک کنید.

بررسی گزینه‌های پیکربندی

پس از اتمام راه‌اندازی، به صفحه «شروع به کار با Google Cloud Data Agent Kit» خواهید رسید.

1. در بخش «تنظیمات و پیکربندی»، روی «شروع به کار » کلیک کنید.
2. این پنل پیکربندی کیت عامل داده را باز می‌کند. تب‌ها را بررسی کنید:
   • پروژه و منطقه: شناسه پروژه انتخابی خود را تأیید کنید و بررسی کنید که APIهای مورد نیاز (Cloud Storage API، BigQuery API، Catalog API و Cloud SQL Admin API) فعال باشند.
   • BigQuery: مکان پیش‌فرض برای کوئری‌های BigQuery خود را پیکربندی کنید. از ناحیه us-central1 استفاده کنید.
   • پیکربندی سرورهای MCP: سرورهای MCP فعال (BigQuery، Notebooks، Cloud SQL و غیره) را که به عوامل هوش مصنوعی اجازه می‌دهند تا به طور ایمن با داده‌های شما تعامل داشته باشند، مشاهده کنید.
   • مهارت‌ها: مهارت‌های از پیش ساخته شده‌ای را بررسی کنید که قابلیت‌های تخصصی را برای وظایف پیچیده داده در اختیار عامل‌ها قرار می‌دهند.

خلاصه بخش: شما Antigravity IDE را باز کردید، آن را به پروژه Google Cloud خود متصل کردید و سرورهای MCP از راه دور Data Agent Kit را پیکربندی کردید.

۴. داده‌های خود را کشف کنید

وقت آن رسیده که صحنه را آماده کنیم. وضعیت از این قرار است: مدیر مالی می‌گوید میانگین ارزش سفارش‌ها ماه گذشته ۷ درصد کاهش یافته، اما درآمد کل ثابت مانده است. قبل از اینکه از نماینده بخواهید موضوع را بررسی کند، ابتدا باید بفهمید با چه داده‌هایی کار می‌کنید.

در این بخش، شما به صورت دستی پنل Data Agent Kit را بررسی خواهید کرد تا با محیط آن آشنا شوید. درک داده‌ها قبل از شروع پرس‌وجو، اولین گام حیاتی در هر تحقیقی است.

جداول BigQuery را بررسی کنید

1. در پنل Data Agent Kit، در زیر CATALOG ، پروژه خود را باز کنید → BigQuery → cymbal_pets .
2. روی جدول orders کلیک کنید. یک برگه جدید باز می‌شود که جزئیات جدول را نشان می‌دهد.
3. زبانه‌های سمت چپ نمایشگر جدول را بررسی کنید:
   • داده‌ها : پیش‌نمایش ردیف‌های واقعی. در مجموعه داده‌ها پیمایش کنید و ستون‌ها را بررسی کنید.
   • طرحواره : نام‌ها و انواع ستون‌ها را مرور کنید. به فیلدهایی مانند order_type و promo_code توجه کنید که بعداً مهم خواهند شد.
   • تب‌های دیگر (جزئیات، بینش‌ها، نمایه داده‌ها و غیره) : به فراداده‌ها، تبار داده‌ها و جزئیات کیفی که معمولاً در کنسول Google Cloud پیدا می‌کنید، دسترسی داشته باشید، همه اینها بدون ترک ویرایشگر شما.

4. حالا روی جدول order_items کلیک کنید و طرحواره آن را بررسی کنید. به فیلدهای quantity و price توجه کنید.

جداول SQL ابری را کاوش کنید

اسکریپت راه‌اندازی همچنین داده‌های مشتری، حیوان خانگی و محصول را در یک پایگاه داده PostgreSQL در Cloud SQL قرار داد.

5. در پنل Data Agent Kit، در بخش CATALOG روی Universal Search کلیک کنید.
6. در کادر جستجو، pet_profiles را تایپ کنید و Enter را بزنید.
7. در نتایج جستجو، روی نتیجه‌ی جدول PostgreSQL برای pet_profiles (در زیر نمونه‌ی Cloud SQL پروژه‌تان) کلیک کنید. توجه داشته باشید که آکاردئون نوار کناری به طور خودکار گسترش می‌یابد و دقیقاً محل قرارگیری جدول در درخت پایگاه داده را به شما نشان می‌دهد. اکنون روی جدول customers که درست بالای آن در درخت قرار دارد کلیک کنید تا جزئیات آن باز شود و زبانه‌های Schema و Details را بررسی کنید.

کاوش در فایل‌های ذخیره‌سازی ابری

در نهایت، سوابق کمپین‌های بازاریابی و تبلیغاتی به صورت فایل‌های خام JSON در فضای ذخیره‌سازی ابری ذخیره می‌شوند.

8. در پنل Data Agent Kit در سمت چپ، بخش CLOUD STORAGE را باز کنید. فایل خام پروژه خود ( YOUR_PROJECT_ID-cymbal-pets-raw ) را پیدا کنید.
9. روی فایل promo_events.json داخل سطل کلیک کنید. یک تب ویرایشگر جدید باز می‌شود که به شما امکان می‌دهد محتوای خام JSON کمپین‌های بازاریابی را مستقیماً در داخل IDE مشاهده کنید.

سهام بگیرید

آنچه اکنون در مورد داده‌ها می‌دانید این است:

داده‌ها در سه سرویس پخش شده‌اند. در یک گردش کار سنتی، شما باید اتصالات را تنظیم کنید، کد یکپارچه‌سازی را بنویسید و نتایج را به صورت دستی به هم متصل کنید. در مرحله بعدی، به عامل هوش مصنوعی اجازه می‌دهید تا همه این کارها را از طریق یک مکالمه واحد انجام دهد.

خلاصه بخش: شما از پنل Data Agent Kit برای بررسی دستی معماری داده‌ها در BigQuery، Cloud SQL و Cloud Storage استفاده کردید. اکنون می‌دانید داده‌ها کجا ذخیره می‌شوند و چه فیلدهایی در دسترس هستند، بنابراین آماده شروع بررسی هستید.

۵. اعداد را دنبال کنید

اکنون تحقیقات آغاز می‌شود. شما از طریق پنل چت از عامل هوش مصنوعی می‌خواهید داده‌های میانگین ارزش سفارش (AOV) را از BigQuery دریافت کند. AOV یک معیار تجاری است که نشان دهنده میانگین مبلغ خرج شده به ازای هر سفارش است. عامل با استفاده از ابزارهای MCP از طرف شما پرس و جو می‌کند و شما می‌توانید هر پرس و جوی SQL که اجرا می‌کند را مشاهده کنید.

روند میانگین ارزش سفارش را بررسی کنید

1. در پنل چت در سمت راست IDE، عبارت زیر را تایپ کرده و Enter را فشار دهید:

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

2. مجوزهای دسترسی به داده‌ها را تأیید کنید. احتیاط در مورد اجرای کوئری‌ها توسط عامل‌های هوش مصنوعی روی پایگاه‌های داده شما، امری سالم است. کیت عامل داده با مکث و درخواست مجوز صریح قبل از دسترسی به داده‌ها، کنترل را در دست شما نگه می‌دارد. در صورت درخواست، می‌توانید موارد زیر را انتخاب کنید:
   • این زمان را مجاز کنید: یک بار استفاده را تأیید می‌کند (ایده‌آل برای حسابرسی پرس‌وجوهای پرخطر).
   • همیشه مجاز: استفاده مداوم از این ابزار خاص را برای جلسه تأیید می‌کند.
   • خیر: عمل را به طور کامل مسدود می‌کند.

برای روان‌ترین تجربه آزمایشگاهی، بله را انتخاب کنید و همیشه اجازه دهید (always allow ). توجه: مجوزها بر اساس هر ابزار اعطا می‌شوند. احتمالاً به زودی با استفاده عامل از ابزارهای جدید (مانند list_table_ids یا execute_sql_readonly ) چند پیام دیگر نیز خواهید دید. در صورت تمایل می‌توانید این موارد را نیز "همیشه اجازه دهید" (always allow).

3. کار نماینده را تماشا کنید. صفحه چت به عنوان یک گزارش شفاف برای هر کاری که نماینده انجام می‌دهد، عمل می‌کند. به جای یک جعبه سیاه، نماینده استدلال و اقدامات خود را در زمان واقعی به شما نشان می‌دهد.
4. پس از اتمام کار، روی منوی کشویی Worked for Xm در زیر اعلان خود کلیک کنید تا گزارش کامل کار را مشاهده کنید. در اینجا می‌توانید دقیقاً بررسی کنید که چگونه پاسخ شما را دریافت کرده است:
   • بررسی‌شده: این موارد را باز کنید تا ببینید عامل در حال خواندن فایل‌ها، مرور پوشه‌ها یا فراخوانی ابزارهای MCP (مانند datacloud_bigquery_remote / list_table_ids و execute_sql_readonly ) است. می‌توانید آرگومان‌های JSON دقیق ارسالی به ابزارها و SQL اجرا شده را مشاهده کنید.
   • اجرا: این موارد را باز کنید تا هرگونه دستور ترمینالی که عامل اجرا کرده است، مانند gcloud config list را ببینید.

5. نتایج را بررسی کنید. نماینده باید جدولی از مقادیر ماهانه AOV را ارائه دهد. خودتان به اعداد نگاه کنید: ماه‌های قبل حدود ۱۱۰ دلار بوده، سپس در ژانویه به حدود ۱۰۳ دلار کاهش می‌یابد. این همان ناهنجاری است که مدیر ارشد مالی گزارش کرده است.

کانال‌ها را بررسی کنید

AOV کلی کاهش یافته است، اما این کاهش از کجا ناشی می‌شود؟ بیایید بفهمیم.

1. در پنل چت، تایپ کنید:

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

2. عامل یک پرس‌وجوی BigQuery دیگر اجرا می‌کند، این بار بر اساس order_type گروه‌بندی می‌کند. نتایج را با دقت بررسی کنید. باید نکته‌ی قابل توجهی ببینید: AOV آنلاین و آفلاین روی حدود ۱۱۰ دلار ثابت باقی می‌مانند. اما یک کانال جدید به نام B2B-Wholesale با AOV بسیار پایین‌تر (حدود ۷۵ دلار) وجود دارد. این کانال جدید میانگین ترکیبی را پایین می‌کشد.
3. ممکن است نماینده به صورت پیشگیرانه پیشنهاد بررسی مشتریان B2B را بدهد. اگر این اتفاق نیفتد، اشکالی ندارد. این کار را در مرحله بعدی انجام خواهید داد.

خلاصه بخش: شما افت AOV ژانویه را از یک داده خنثی مشاهده کردید، سپس با استفاده از order_type بررسی کردید تا B2B-Wholesale به عنوان کانال جدیدی که میانگین ترکیبی را پایین می‌آورد، شناسایی کنید. اکنون باید بفهمید که این مشتریان B2B چه کسانی هستند.

۶. عبور از مرز سرویس

شما B2B-Wholesale به عنوان کانال غیرعادی در BigQuery شناسایی کرده‌اید، اما داده‌های مشتری در Cloud SQL قرار دارد. با Data Agent Kit، می‌توانید همان مکالمه را ادامه دهید و این کیت، مرز سرویس را مدیریت می‌کند.

مشتریان B2B را بررسی کنید

1. در پنل چت ، تایپ کنید:

   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. با دقت به پنل چت نگاه کنید. این بار باید یک ابزار MCP متفاوت ظاهر شود. اکنون عامل به جای BigQuery، در حال پرس و جو از Cloud SQL است. عامل به نمونه cymbal-pets-ops Cloud SQL Postgres متصل می‌شود و یک پرس و جو را روی جدول customers اجرا می‌کند. برای مشاهده SQL، روی Show Details کلیک کنید.
3. نتایج را بررسی کنید. نماینده باید چندین یافته کلیدی را آشکار کند:
   • همه مشتریان B2B دارای customer_type = 'Business' هستند.
   • همه آنها ظرف 30 روز گذشته (ژانویه 2025) ثبت نام کرده اند
   • مقادیر last_name آنها نام‌های تجاری مانند "شرکت تأمین حیوانات خانگی"، "شرکت مراقبت از حیوانات" و "شرکت پنجه‌های شاد" است.
   • حدود ۱۰۰ نفر از آنها وجود دارند، گروهی که قبل از این ماه وجود نداشتند.

کد تبلیغاتی را وصل کنید

4. ممکن است نماینده خودش متوجه شود که بسیاری از سفارش‌های B2B در BigQuery دارای مقدار promo_code برابر با BIGORDER25 هستند. اگر داوطلبانه این مشاهده را انجام دهد، عالی است. تحقیقات به طور طبیعی در حال پیشرفت است. اگر نماینده کد تخفیف را ذکر نکرد، آن را به او گوشزد کنید:

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

5. نماینده دوباره از BigQuery پرس و جو می‌کند و متوجه می‌شود که تقریباً ۹۲٪ از سفارش‌های عمده‌فروشی B2B دارای promo_code = 'BIGORDER25' هستند. تقریباً تمام فعالیت‌های B2B به یک کمپین تبلیغاتی واحد گره خورده است. نماینده ممکن است در مرحله بعد به دنبال داده‌های تبلیغاتی در جای دیگری از محیط باشد. (اینجا در فضای ذخیره‌سازی ابری است.)

خلاصه بخش: نماینده از Cloud SQL پرس‌وجو کرد تا نشان دهد که مشتریان B2B همگی کسب‌وکارهای جدیدی هستند که در ژانویه ۲۰۲۵ ثبت‌نام کرده‌اند. با توجه به یافته BigQuery مبنی بر اینکه حدود ۹۲٪ از سفارشات آنها حاوی promo_code = 'BIGORDER25' هستند، اکنون ردپا به یک کمپین تبلیغاتی اشاره دارد. وقت آن است که منبع را پیدا کنیم.

۷. قطعه گمشده را پیدا کنید

دو سرویس از کار افتادند، یکی دیگر باید برود. می‌دانید چه اتفاقی افتاده (سفارش‌های B2B ارزش AOV را پایین می‌آورند) و چه کسی این کار را می‌کند (مشتریان تجاری جدید از 30 روز گذشته). حالا باید دلیلش را پیدا کنید و جواب در فضای ذخیره‌سازی ابری است.

سطل GCS را بررسی کنید

1. در پنل چت ، تایپ کنید:

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

2. این عامل ابزار MCP از پیش پیکربندی‌شده‌ای برای ذخیره‌سازی ابری ندارد، بنابراین به‌طور خودکار به استفاده از ابزار ترمینال خود برای اجرای دستورات gcloud storage روی می‌آورد. برای اجرای دستوراتی مانند gcloud storage ls درخواست مجوز می‌کند. به این دستورات اجازه دهید، سپس گزارش Ran را در پنجره چت باز کنید تا دستورات دقیق CLI که برای خواندن و تجزیه فایل promo_events.json استفاده کرده است را ببینید.
3. نماینده باید سه کمپین تبلیغاتی را در پرونده شناسایی کند:

   کمپین	کد تخفیف	تخفیف	هدف	خرما
   حراج تابستانی مراقبت از حیوانات خانگی	PETSUMMER15	۱۵٪ تخفیف	همه	ژوئن ۲۰۲۴
   فشار عمده فروشی B2B	BIGORDER25	۲۵٪ تخفیف	کسب و کار به کسب و کار (B2B)	ژانویه ۲۰۲۵
   جایزه تعطیلات اعضای وفادار	LOYAL10	۱۰٪ تخفیف	اعضای وفادار	دسامبر ۲۰۲۴

   دلیلش همین است. کد تخفیف BIGORDER25 به کمپینی به نام B2B Wholesale Push مربوط می‌شود: ۲۵٪ تخفیف برای مشتریان B2B با حداقل سفارش ۵۰ واحد.

همه را کنار هم بگذارید

4. از نماینده بخواهید هر آنچه را که پیدا کرده است، ترکیب کند:

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

5. این عامل، یک ترکیب واضح و ساختاریافته ارائه می‌دهد که هر سه منبع داده را به هم متصل می‌کند. این ترکیب باید چیزی شبیه به موارد زیر را توضیح دهد:
   1. کاهش AOV واقعی است، اما این کاهش در کسب و کار موجود نیست. AOV آنلاین و آفلاین در حدود ۱۱۰ دلار ثابت مانده است.
   2. یک کانال جدید B2B-Wholesale در ژانویه ۲۰۲۵ ظاهر شد که حدود ۲۵۰۰۰ سفارش با AOV بسیار پایین‌تر (حدود ۷۵ تا ۱۰۰ دلار) داشت.
   3. مشتریان B2B، ۱۰۰ حساب تجاری جدید هستند که همگی در ۳۰ روز گذشته ثبت‌نام کرده‌اند (Cloud SQL).
   4. این فعالیت توسط یک کمپین تبلیغاتی ("فشار عمده فروشی B2B") هدایت می‌شود که 25٪ تخفیف برای سفارش‌های عمده با حداقل 50 واحد (ذخیره سازی ابری) ارائه می‌دهد.
   5. درآمد ثابت است زیرا حجم بالای سفارشات B2B، قیمت‌های پایین‌تر را جبران می‌کند. با این حال، حاشیه سود هر واحد به شدت تحت تأثیر تخفیف عمده‌فروشی ۲۵ درصدی کاهش یافته است (حدود ۶۵٪ کاهش یافته است)، که با در نظر گرفتن هزینه‌های حمل و نقل و سربار عملیاتی، سودآوری کلی را به شدت تهدید می‌کند.
   این لحظه‌ای است که تحقیقات کلید می‌خورد. سوال مدیر مالی یک پاسخ واضح دارد: AOV کاهش یافت زیرا یک برنامه B2B مبتنی بر بازاریابی، ژانویه را با سفارش‌های با حجم بالا و قیمت پایین پر کرد. کسب‌وکار موجود سالم است.

خلاصه بخش: شما علت را در فضای ذخیره‌سازی ابری پیدا کردید: یک کمپین تبلیغاتی B2B که 25٪ تخفیف برای سفارش‌های عمده ارائه می‌دهد. نماینده، یافته‌ها را در هر سه سرویس در یک روایت واضح ترکیب کرد. مرحله تحقیق کامل شده است. در مرحله بعد، این یافته‌ها را عملیاتی خواهید کرد.

۸. ساخت خط لوله

شما معما را حل کردید. حالا مدیر ارشد مالی می‌خواهد این تحلیل به‌طور خودکار به‌روزرسانی شود. در این بخش، از نماینده می‌خواهید یک پروژه dbt بسازد که داده‌های BigQuery را مرحله‌بندی کرده و یک جدول حقایق برای تحلیل مداوم AOV تولید کند.

اینجاست که عامل از محقق به مهندس تغییر می‌کند. خواهید دید که کل یک پروژه dbt را چارچوب‌بندی می‌کند و کل خط لوله را اجرا می‌کند، همه اینها از یک اعلان واحد.

پروژه dbt را داربست بندی کنید

1. در پنل چت ، عبارت زیر را تایپ کنید. این عبارت عمداً هدف‌گرا است و نه گام به گام. شما به اپراتور می‌گویید که چه می‌خواهید، نه اینکه چگونه آن را بسازد:

   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. به خود-اصلاحی توجه کنید: اگر گزارش «Worked for Ns» را باز کنید، ممکن است ببینید که عامل dbt را بررسی می‌کند و پس از یافتن آن، به طور خودکار دستوراتی را برای ایجاد یک محیط مجازی پایتون ( .venv ) اجرا می‌کند. این برنامه تنظیمات محیط را برای شما انجام می‌دهد!

3. بررسی طرح پیاده‌سازی: عامل یک طرح پیاده‌سازی رسمی ایجاد می‌کند. شما می‌توانید فایل‌ها و معماری پیشنهادی آن را بررسی کنید، در صورت نیاز نظرات خود را اضافه کنید و روی «ادامه» کلیک کنید تا عامل طرح را اجرا کند.

4. در حالی که عامل، طرح خود را اجرا می‌کند و فایل‌های .sql لازم و پیکربندی‌های YAML را می‌نویسد، پنجره چت را تماشا کنید. پس از اتمام و کامپایل موفقیت‌آمیز پروژه، خلاصه‌ای از تغییرات ارائه می‌شود. برای افزودن این فایل‌ها به فضای کاری خود، روی «پذیرش همه» کلیک کنید.

5. پروژه dbt تازه ایجاد شده را در اکسپلورر سمت چپ بررسی کنید. باید ساختاری مشابه زیر را ببینید:

   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. برای بررسی SQL تولید شده توسط عامل، روی فایل‌های مدل .sql کلیک کنید. به نحوه مدیریت آن توجه کنید:
   • مدل‌های مرحله‌بندی : ستون‌های تمیز و تغییر نام یافته با ارجاعات منبع
   • مدل مارت : منطق اتصال و محاسبه AOV بر اساس کانال
   • مدیریت پرداخت‌های مهمان : ممکن است متوجه محدودیت COALESCE(customer_type, 'Guest') یا null شوید. این مدل، خریدهای مهمان خرد را که بدون حساب کاربری انجام می‌شوند، مدل‌سازی می‌کند و به جای حذف رکوردهای ناقص، درآمد سفارش معتبر را حفظ می‌کند.

7. برای تأیید نماینده مبنی بر اینکه همه مدل‌ها ساخته شده‌اند و همه آزمایش‌ها با موفقیت انجام شده‌اند، پنجره چت را بررسی کنید (یا روی مصنوع Walkthrough تولید شده کلیک کنید). نتایج AOV از مارت باید آنچه را که در طول تحقیقات یافته‌اید، تأیید کند:

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

خلاصه بخش: عامل یک پروژه dbt را از یک دستور هدف‌گرا ساخت: مدل‌های مرحله‌بندی و mart داربست‌بندی شده، یک dbt build موفق را اجرا کرد و ناهنجاری AOV را تأیید کرد. در مرحله بعد، شما یک آزمایش انجام خواهید داد تا ببینید که عامل چگونه پیچیدگی را مدیریت می‌کند.

۹. وقتی آزمایش‌ها با شکست مواجه می‌شوند، عامل اشکال‌زدایی می‌کند

این خط لوله کار می‌کند، اما فقط از داده‌های BigQuery استفاده می‌کند. تیم محصول می‌خواهد تجزیه و تحلیل را با داده‌های پروفایل مشتری و حیوان خانگی از Cloud SQL غنی کند تا بتواند محصولات را بر اساس نیازهای غذایی توصیه کند. این بدان معناست که عامل باید مرز Cloud SQL را پر کند و یک اشکال ظریف مدل‌سازی داده، یک اتصال "fan-out" مدل‌سازی ابعادی کلاسیک را مدیریت کند.

بسته به مدلی که استفاده می‌کنید و قابلیت‌های استدلال آن، عامل این درخواست را به یکی از دو روش زیر مدیریت خواهد کرد: اجتناب فعال از باگ (گزینه الف) یا خوددرمانی پس از شکست آزمایش (گزینه ب). بیایید ببینیم عامل شما کدام مسیر را انتخاب می‌کند!

فعال کردن درخواست

1. در پنل چت ، تایپ کنید:

   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. کار عامل را تماشا کنید. این عامل جداول Cloud SQL را کشف می‌کند، نحوه‌ی اتصال داده‌ها به BigQuery (از طریق پرس‌وجوی فدرال یا کپی مادی) را کشف می‌کند، مدل‌های مرحله‌بندی جدید ایجاد می‌کند و fct_order_analysis.sql را تغییر می‌دهد.

گزینه الف: عامل پیشگیرانه (اجتناب از باگ)

اگر از یک مدل استدلال پیشرفته استفاده می‌کنید، عامل ممکن است قبل از نوشتن هر کدی، تغییر دانه را تشخیص دهد. از آنجا که یک مشتری می‌تواند چندین حیوان خانگی داشته باشد، یک اتصال مستقیم، سفارش‌ها را تکرار می‌کند و آزمون منحصر به فرد بودن مورد درخواست شما را در order_id با شکست مواجه می‌کند.

1. به تجمیع پیشگیرانه توجه کنید : در توضیحات صفحه چت یا مصنوعات راهنمای گام به گام، ممکن است نماینده خاطرنشان کند که قبل از اتصال داده‌های حیوانات خانگی، آنها را از قبل تجمیع کرده است تا از "انباشت کلاسیک" جلوگیری کند. این کار معمولاً با حذف چندین حیوان خانگی به ازای هر مشتری با استفاده از یک تابع تجمیع (مثلاً ARRAY_AGG() یا STRING_AGG() ) انجام می‌شود.
2. بررسی نتایج : dbt build اجرا می‌شود و در اولین تلاش با موفقیت انجام می‌شود، زیرا عامل به طور پیشگیرانه از جزئیات جدول فکت محافظت کرده است. می‌توانید این موضوع را با بررسی مصنوع Walkthrough تولید شده تأیید کنید، که اغلب خروجی تست موفقیت‌آمیز را در کنار نتایج پرس و جو نشان می‌دهد.

عامل از بروز اشکال جلوگیری کرد. SQL تولید شده در fct_order_analysis.sql را بررسی کنید تا ببینید چگونه تجمیع را ساختاردهی کرده است، سپس به بخش بعدی، یعنی Deliver the answer ، بروید.

گزینه ب: عامل خوددرمانگر (اشکال‌زدایی و تشخیص)

اگر مدل ابتدا یک اتصال چپ مستقیم ساده بنویسد، خود پرس‌وجوی SQL با موفقیت اجرا می‌شود، اما مجموعه dbt test تغییر دانه را تشخیص می‌دهد!

1. مشاهده‌ی شکست تست : شکست گزارش‌شده را در گزارش‌های پیشرفت اجرای پنل چت مشاهده خواهید کرد:

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

   آزمون منحصر به فرد بودن روی order_id ورودی‌های تکراری را نشان داد، زیرا مشتریانی که چندین حیوان خانگی داشتند، سفارش‌ها را به صورت پراکنده ارسال می‌کردند.
2. بگذارید عامل تشخیص دهد و خود را درمان کند : از آنجایی که آزمایش ناموفق بود، از عامل بخواهید آن را اشکال‌زدایی کند. در پنجره چت ، تایپ کنید:

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

3. تشخیص را مشاهده کنید : عامل داده‌ها را جستجو می‌کند، رابطه یک به چند را در 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. رفع مشکل را تأیید کنید : عامل دوباره dbt build اجرا می‌کند و این بار همه مدل‌ها پیاده‌سازی می‌شوند و همه آزمایش‌ها با موفقیت انجام می‌شوند!

خلاصه بخش: چه نماینده شما به طور پیشگیرانه از بروز اشکال جلوگیری کرده باشد و چه پس از یک شکست آزمایشی، با موفقیت خود را ترمیم کرده باشد، مشاهده کرده‌اید که چگونه مرز Cloud SQL را پر می‌کند، داده‌های پروفایل مشتری و حیوان خانگی را ادغام می‌کند و برای هر سفارش یک ردیف در جدول fact نگه می‌دارد. این خط لوله کامل و آزمایش شده است!

۱۰. پاسخ را ارائه دهید

پنجشنبه است. شما هفته را با یک مدیر مالی نگران و داده‌های پراکنده در سه سرویس ابری شروع کردید. حالا ریشه مشکل و یک خط تولید دارید. وقت آن است که پاسخ را ارائه دهید، همراه با یک توصیه آینده‌نگر که با یک پیش‌بینی کمی پشتیبانی می‌شود.

خلاصه اجرایی را بنویسید

1. در پنل چت ، تایپ کنید:

   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. کار نماینده را تماشا کنید.
3. خلاصه اجرایی نماینده را مرور کنید. یک پاسخ معمول و ساختارمند باید موارد زیر را در بر بگیرد:
   • یافته اصلی : AOV ژانویه صرفاً به دلیل کانال جدید B2B-Wholesale کاهش یافت. آنلاین و آفلاین در حدود ۱۱۰ دلار ثابت ماندند.
   • علت ریشه‌ای : «فشار عمده‌فروشی B2B» (۲۵٪ تخفیف برای سفارش‌های عمده) ۱۰۰ حساب کاربری جدید جذب کرد و منجر به حدود ۲۵۰۰۰ سفارش شد.
   • تأثیر حاشیه سود : سفارشات عمده‌فروشی، میانگین سود واحد را حدود ۶۵ درصد کاهش دادند (از حدود ۷.۵۰ دلار به حدود ۲.۶۰ دلار).
   • درآمد : درآمد کلی ثابت مانده است زیرا حجم بالای معاملات B2B، قیمت‌های پایین‌تر را جبران می‌کند.

پیش‌بینی AOV با AI.FORECAST

4. عامل همچنین باید یک پیش‌بینی آینده‌نگر ایجاد کند. به دنبال فراخوانی ابزار MCP باشید که در آن عامل یک پرس‌وجوی AI.FORECAST را در BigQuery اجرا می‌کند. این پرس‌وجو از مدل پایه داخلی TimesFM برای پیش‌بینی AOV به مدت ۹۰ روز بر اساس روندهای تاریخی استفاده می‌کند. این پرس‌وجو باید AOV را تحت دو سناریو ۹۰ روز به جلو پیش‌بینی کند: ادامه کمپین (AOV با کاهش ساختاری) در مقابل خاتمه کمپین (بازیابی تا حدود ۱۱۰ دلار).

5. توصیه‌های استراتژیک نماینده را بررسی کنید. این توصیه‌ها باید موارد زیر را پوشش دهند:
   • تغییر ساختار تخفیف‌ها : برای محافظت از حاشیه سود در سطح واحد، تخفیف‌های کف حاشیه سود یا سقف تخفیف‌های عمده‌فروشی را اعمال کنید.
   • اعمال حداقل مقادیر سفارش (MOQ) سختگیرانه‌تر : از سوءاستفاده خریداران خرده‌فروش از قیمت‌گذاری عمده‌فروشی جلوگیری کنید.
   • گزارش‌دهی جداگانه : بخش‌های خرده‌فروشی و B2B را به‌طور مستقل پیگیری کنید تا از پنهان کردن عملکرد خرده‌فروشی جلوگیری شود.

داستان کامل

آنچه روز دوشنبه به عنوان مانوری برای مقابله با کاهش ۷ درصدی میانگین ارزش سفارش آغاز شد، برای مدیر ارشد مالی به یک نتیجه‌ی روشن رسیده است:

• 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. Return to Google Cloud Shell (where you ran the setup script) and run the teardown script:

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 ): All tables
   • BigQuery datasets ( cymbal_pets , dbt_marts ): 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 BigQuery dataset cymbal_pets...
[ OK ]  BigQuery dataset cymbal_pets deleted.
[INFO]  Deleting BigQuery dataset dbt_marts...
[ OK ]  BigQuery dataset dbt_marts deleted.
[INFO]  Deleting GCS bucket gs://YOUR_PROJECT_ID-cymbal-pets-raw...
[ OK ]  GCS bucket deleted.
[INFO]  Deleting BigQuery connection cymbal-pets-cloudsql...
[ OK ]  BQ connection deleted.
[INFO]  Deleting Cloud SQL instance cymbal-pets-ops...
[ OK ]  Cloud SQL instance deleted.

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 order 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

مراحل بعدی

• 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

۱. مقدمه

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: sales and order history 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 to operationalize your findings, debug a real data modeling bug, and deliver a forecast-backed recommendation to the CFO.

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
• Antigravity IDE
• A Google Cloud project with billing enabled and access to the Google Cloud Console (we recommend using a new, dedicated project for hands-on labs)
• 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. Be sure to follow the Clean Up instructions at the end of the lab to delete provisioned resources.

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.

Select or create a project

Choose an existing project or create a new project in the Google Cloud Console.

Verify billing

Make sure that billing is enabled for your Google Cloud project. You can learn more on how to do this by following this guide .

Start Cloud Shell

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

1. Open the Google Cloud Console and click Activate Cloud Shell at the top of the window.

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 automatically so you can jump straight into the investigation:

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: historical sales records (orders and order items) live in BigQuery, while operational application data (customers, pet profiles, and products) lives in Cloud SQL. This split mirrors real-world organizations where analytical warehouses and operational databases hold different pieces of the puzzle.

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 (eg 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 adds a data catalog browser, agent skills, and MCP servers for BigQuery, Cloud SQL, and Cloud Storage, so you can query and inspect those services from the editor.

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 first result 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 .

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 Kit 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, and configured the Data Agent Kit remote MCP servers.

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.

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 the promo_events.json file inside the bucket. A new editor tab opens, allowing you to view the raw JSON content of the marketing campaigns directly inside the IDE.

Take stock

Here's what you now know about the data:

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.

Drill down by channel

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

1. 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?
   

2. 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.
3. The agent may proactively suggest investigating the B2B customers. If it doesn't, that's fine. You'll do that in the next step.

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

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 may next look for promotional data elsewhere in the environment. (It's 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:

   کمپین	کد تخفیف	تخفیف	هدف	خرما
   Summer Pet Care Sale	PETSUMMER15	15% off	همه	Jun 2024
   B2B Wholesale Push	BIGORDER25	۲۵٪ تخفیف	B2B	Jan 2025
   Loyalty Member Holiday Bonus	LOYAL10	۱۰٪ تخفیف	Loyalty Members	Dec 2024

   That's the cause. 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.

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 cause 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.

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 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
   • Handling guest checkouts : You may notice COALESCE(customer_type, 'Guest') or relaxed null constraints. This models retail guest purchases made without an account and preserves valid order revenue instead of dropping incomplete records.

7. Check the Chat pane (or click into the generated Walkthrough artifact) 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.

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 . Because a customer can own multiple pets, a direct join duplicates orders and fails 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 (eg, 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.

The agent avoided the bug. 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 keep one row per order in the fact table. The pipeline is complete and 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.
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).

5. Review the agent's strategic recommendations. The recommendations should cover:
   • 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. Return to Google Cloud Shell (where you ran the setup script) and run the teardown script:

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 ): All tables
   • BigQuery datasets ( cymbal_pets , dbt_marts ): 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 BigQuery dataset cymbal_pets...
[ OK ]  BigQuery dataset cymbal_pets deleted.
[INFO]  Deleting BigQuery dataset dbt_marts...
[ OK ]  BigQuery dataset dbt_marts deleted.
[INFO]  Deleting GCS bucket gs://YOUR_PROJECT_ID-cymbal-pets-raw...
[ OK ]  GCS bucket deleted.
[INFO]  Deleting BigQuery connection cymbal-pets-cloudsql...
[ OK ]  BQ connection deleted.
[INFO]  Deleting Cloud SQL instance cymbal-pets-ops...
[ OK ]  Cloud SQL instance deleted.

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 order 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

مراحل بعدی

• 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

۱. مقدمه

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: sales and order history 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 to operationalize your findings, debug a real data modeling bug, and deliver a forecast-backed recommendation to the CFO.

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
• Antigravity IDE
• A Google Cloud project with billing enabled and access to the Google Cloud Console (we recommend using a new, dedicated project for hands-on labs)
• 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. Be sure to follow the Clean Up instructions at the end of the lab to delete provisioned resources.

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.

Select or create a project

Choose an existing project or create a new project in the Google Cloud Console.

Verify billing

Make sure that billing is enabled for your Google Cloud project. You can learn more on how to do this by following this guide .

Start Cloud Shell

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

1. Open the Google Cloud Console and click Activate Cloud Shell at the top of the window.

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 automatically so you can jump straight into the investigation:

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: historical sales records (orders and order items) live in BigQuery, while operational application data (customers, pet profiles, and products) lives in Cloud SQL. This split mirrors real-world organizations where analytical warehouses and operational databases hold different pieces of the puzzle.

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 (eg 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 adds a data catalog browser, agent skills, and MCP servers for BigQuery, Cloud SQL, and Cloud Storage, so you can query and inspect those services from the editor.

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 first result 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 .

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 Kit 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, and configured the Data Agent Kit remote MCP servers.

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.

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 the promo_events.json file inside the bucket. A new editor tab opens, allowing you to view the raw JSON content of the marketing campaigns directly inside the IDE.

Take stock

Here's what you now know about the data:

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.

Drill down by channel

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

1. 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?
   

2. 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.
3. The agent may proactively suggest investigating the B2B customers. If it doesn't, that's fine. You'll do that in the next step.

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

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 may next look for promotional data elsewhere in the environment. (It's 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:

   کمپین	کد تخفیف	تخفیف	هدف	خرما
   Summer Pet Care Sale	PETSUMMER15	15% off	همه	Jun 2024
   B2B Wholesale Push	BIGORDER25	۲۵٪ تخفیف	B2B	Jan 2025
   Loyalty Member Holiday Bonus	LOYAL10	۱۰٪ تخفیف	Loyalty Members	Dec 2024

   That's the cause. 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.

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 cause 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.

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 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
   • Handling guest checkouts : You may notice COALESCE(customer_type, 'Guest') or relaxed null constraints. This models retail guest purchases made without an account and preserves valid order revenue instead of dropping incomplete records.

7. Check the Chat pane (or click into the generated Walkthrough artifact) 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.

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 . Because a customer can own multiple pets, a direct join duplicates orders and fails 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 (eg, 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.

The agent avoided the bug. 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 keep one row per order in the fact table. The pipeline is complete and 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.
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).

5. Review the agent's strategic recommendations. The recommendations should cover:
   • 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. Return to Google Cloud Shell (where you ran the setup script) and run the teardown script:

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 ): All tables
   • BigQuery datasets ( cymbal_pets , dbt_marts ): 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 BigQuery dataset cymbal_pets...
[ OK ]  BigQuery dataset cymbal_pets deleted.
[INFO]  Deleting BigQuery dataset dbt_marts...
[ OK ]  BigQuery dataset dbt_marts deleted.
[INFO]  Deleting GCS bucket gs://YOUR_PROJECT_ID-cymbal-pets-raw...
[ OK ]  GCS bucket deleted.
[INFO]  Deleting BigQuery connection cymbal-pets-cloudsql...
[ OK ]  BQ connection deleted.
[INFO]  Deleting Cloud SQL instance cymbal-pets-ops...
[ OK ]  Cloud SQL instance deleted.

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 order 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

مراحل بعدی

• 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

۱. مقدمه

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: sales and order history 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 to operationalize your findings, debug a real data modeling bug, and deliver a forecast-backed recommendation to the CFO.

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
• Antigravity IDE
• A Google Cloud project with billing enabled and access to the Google Cloud Console (we recommend using a new, dedicated project for hands-on labs)
• 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. Be sure to follow the Clean Up instructions at the end of the lab to delete provisioned resources.

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.

Select or create a project

Choose an existing project or create a new project in the Google Cloud Console.

Verify billing

Make sure that billing is enabled for your Google Cloud project. You can learn more on how to do this by following this guide .

Start Cloud Shell

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

1. Open the Google Cloud Console and click Activate Cloud Shell at the top of the window.

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 automatically so you can jump straight into the investigation:

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: historical sales records (orders and order items) live in BigQuery, while operational application data (customers, pet profiles, and products) lives in Cloud SQL. This split mirrors real-world organizations where analytical warehouses and operational databases hold different pieces of the puzzle.

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 (eg 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 adds a data catalog browser, agent skills, and MCP servers for BigQuery, Cloud SQL, and Cloud Storage, so you can query and inspect those services from the editor.

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 first result 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 .

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 Kit 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, and configured the Data Agent Kit remote MCP servers.

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.

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 the promo_events.json file inside the bucket. A new editor tab opens, allowing you to view the raw JSON content of the marketing campaigns directly inside the IDE.

Take stock

Here's what you now know about the data:

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.

Drill down by channel

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

1. 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?
   

2. 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.
3. The agent may proactively suggest investigating the B2B customers. If it doesn't, that's fine. You'll do that in the next step.

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

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 may next look for promotional data elsewhere in the environment. (It's 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:

   کمپین	کد تخفیف	تخفیف	هدف	خرما
   Summer Pet Care Sale	PETSUMMER15	15% off	همه	Jun 2024
   B2B Wholesale Push	BIGORDER25	۲۵٪ تخفیف	B2B	Jan 2025
   Loyalty Member Holiday Bonus	LOYAL10	۱۰٪ تخفیف	Loyalty Members	Dec 2024

   That's the cause. 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.

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 cause 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.

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 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
   • Handling guest checkouts : You may notice COALESCE(customer_type, 'Guest') or relaxed null constraints. This models retail guest purchases made without an account and preserves valid order revenue instead of dropping incomplete records.

7. Check the Chat pane (or click into the generated Walkthrough artifact) 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.

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 . Because a customer can own multiple pets, a direct join duplicates orders and fails 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 (eg, 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.

The agent avoided the bug. 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 keep one row per order in the fact table. The pipeline is complete and 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.
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).

5. Review the agent's strategic recommendations. The recommendations should cover:
   • 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. Return to Google Cloud Shell (where you ran the setup script) and run the teardown script:

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 ): All tables
   • BigQuery datasets ( cymbal_pets , dbt_marts ): 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 BigQuery dataset cymbal_pets...
[ OK ]  BigQuery dataset cymbal_pets deleted.
[INFO]  Deleting BigQuery dataset dbt_marts...
[ OK ]  BigQuery dataset dbt_marts deleted.
[INFO]  Deleting GCS bucket gs://YOUR_PROJECT_ID-cymbal-pets-raw...
[ OK ]  GCS bucket deleted.
[INFO]  Deleting BigQuery connection cymbal-pets-cloudsql...
[ OK ]  BQ connection deleted.
[INFO]  Deleting Cloud SQL instance cymbal-pets-ops...
[ OK ]  Cloud SQL instance deleted.

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 order 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

مراحل بعدی

• 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