<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Claudiocmm on Medium]]></title>
        <description><![CDATA[Stories by Claudiocmm on Medium]]></description>
        <link>https://medium.com/@claudiofilho22?source=rss-af8e5bd722d5------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*gS8dQckg20VtGrLU7a9dBw.jpeg</url>
            <title>Stories by Claudiocmm on Medium</title>
            <link>https://medium.com/@claudiofilho22?source=rss-af8e5bd722d5------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 00:11:48 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@claudiofilho22/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[End-to-End Data Engineering Project using dbt + BigQuery + Cloud Run]]></title>
            <link>https://medium.com/@claudiofilho22/end-to-end-data-engineering-project-using-dbt-bigquery-cloud-run-eae10e0c7dc3?source=rss-af8e5bd722d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/eae10e0c7dc3</guid>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[google-cloud-run]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Claudiocmm]]></dc:creator>
            <pubDate>Sun, 06 Apr 2025 20:42:46 GMT</pubDate>
            <atom:updated>2025-04-07T22:48:54.544Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gOwEziBp2ZqCGWu65G-J4Q.png" /></figure><h3>Introduction</h3><p>This article demonstrates building an automated data pipeline using<strong> </strong><a href="https://www.getdbt.com/"><strong>dbt</strong></a><strong>, </strong><a href="https://cloud.google.com/bigquery?hl=en"><strong>BigQuery</strong></a><strong> and </strong><a href="https://cloud.google.com/run?hl=pt-BR"><strong>Cloud Run</strong></a>. You’ll see how this system collects and processes an ecommerce data.</p><p>This project is part of my GitHub portfolio showcasing various data engineering architectures to help both experienced and new data software engineers with practical examples. The complete code is available in the repository linked below.</p><p><a href="https://github.com/claudiocmm/data_engineering_projects/tree/main/GCP/pipeline-ecommerce-bq-dbt">data_engineering_projects/GCP/pipeline-ecommerce-bq-dbt at main · claudiocmm/data_engineering_projects</a></p><p>I’ve designed this modular pipeline to handle data efficiently while maintaining the scalability needed for production environments.</p><h3>Project Overview</h3><p>This project extracts, processes, and stores e-commerce data from this dataset in <a href="https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce/data?select=olist_sellers_dataset.csv"><strong>Kaggle</strong></a><strong> </strong>using a scalable and reliable modular pipeline on GCP. Key components include:</p><ul><li><strong>dbt (data build tool)</strong>: Transforms raw data in Bigquery into analytics-ready models using SQL-based transformations with version control and testing.</li><li><strong>Cloud Run</strong>: Deploys containerized data processing with all the dbt models to do the transformation.</li><li><strong>Cloud Storage</strong>: Stores the raw data files to be inserted in a raw dataset in BigQuery.</li><li><strong>Apache Airflow</strong>: Orchestrates the entire data pipeline workflow, managing dependencies between tasks and ensuring reliable execution of the data processing sequence.</li><li><strong>BigQuery</strong>: Serves as the serverless data warehouse, enabling high-performance analytics at scale with separation of storage and compute resources.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xHMYu_qhnX1blN4npMlwzA.png" /><figcaption>Architecture Diagram</figcaption></figure><p>The final result will be the modeled data in the BigQuery for data analysis:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2L60fwRCme7gDmUZvEQLTQ.png" /><figcaption>Data Model Diagram</figcaption></figure><h4><strong>Dimension Tables</strong></h4><p>These tables store descriptive attributes about business entities. Contains 4 tables:</p><ul><li>dim_customers: Stores customer information and attributes.</li><li>dim_orders: Contains order metadata and attributes.</li><li>dim_products: Holds product details and characteristics.</li><li>dim_sellers: Contains seller information.</li></ul><h4><strong>Detailed Fact Tables</strong></h4><p>These tables store transaction-level data at the most granular level. They contain metrics and foreign keys to dimension tables, contains 2 tables:</p><ul><li>fact_order_items: Likely stores individual line items for each order.</li><li>fact_order_payments: Records payment transactions related to orders.</li></ul><h4><strong>Aggregated Fact Tables</strong></h4><p>These tables contain pre-calculated aggregations and summaries. They’re optimized for reporting and typically improve query performance, contains 4 tables:</p><ul><li>vw_fact_customer_purchase_summary: Aggregated view of customer purchasing behavior.</li><li>vw_fact_product_performance: Performance metrics for products.</li><li>vw_fact_products_sales_agg_time_analysis: Time-based aggregations of product sales.</li><li>vw_fact_seller_performance: Performance metrics for sellers.</li></ul><p>The architecture follows a <a href="https://learndatamodeling.com/blog/designing-star-schema/"><strong>star schema design</strong></a> pattern common in data warehousing, where fact tables connect to dimension tables to support business intelligence and analytics.</p><h3>Architecture Decisions: Why These Technologies?</h3><h4><strong>dbt: Transform Data in Your Warehouse with Analytics Engineering</strong></h4><p>dbt (data build tool) serves as the transformation layer within the modern data stack, enabling analytics engineers to transform raw data into analytics-ready models using SQL. Key benefits include:</p><ul><li><strong>Version-controlled SQL transformations</strong>, allowing teams to collaborate using Git workflows.</li><li><strong>Built-in testing and documentation</strong>, ensuring data quality and improving knowledge sharing.</li><li><strong>Modular approach to data modeling</strong>, creating reusable building blocks for analytics. With dbt, data can be transformed into well-defined, tested models that support reliable reporting and analysis.</li></ul><h4><strong>BigQuery: Google’s Serverless Data Warehouse for Analytics at Scale</strong></h4><p>BigQuery serves as the ultimate analytics destination due to its serverless architecture, impressive query performance, and seamless integration with the Google Cloud ecosystem using federated queries. Key benefits include:</p><ul><li><strong>Separation of storage and compute resources</strong>, enabling cost-effective analysis of massive datasets.</li><li><strong>Automatic resource provisioning</strong>, eliminating the need for capacity, planning or infrastructure management.</li><li><strong>Native machine learning capabilities and versioning</strong>, facilitating predictive analytics and maintaining data lineage.</li></ul><p>With BigQuery, data can be rapidly processed, analyzed at petabyte scale, and transformed into actionable insights.</p><h4><strong>Cloud Run: Fully Managed Containerized Applications</strong></h4><p>Cloud Run functions is a serverless compute platform for deploying and scaling containerized applications with minimal configuration. Key benefits include:</p><ul><li><strong>Event-driven scaling</strong>, automatically adjusting resources from zero to peak demand.</li><li><strong>Pay-per-use pricing model</strong>, optimizing costs by charging only for actual compute time.</li><li><strong>Support for any language or library</strong>, enabling flexibility through container-based deployment.</li></ul><h4><strong>Cloud Storage: Scalable Object Storage for Any Data Type</strong></h4><p>Cloud Storage serves as a unified object storage solution for data of any type, volume, or format with industry-leading durability and availability. Key benefits include:</p><ul><li><strong>Flexible storage classes</strong>, optimizing costs based on access frequency and retention needs.</li><li><strong>Seamless integration with analytics services</strong>, enabling direct queries against stored data.</li><li><strong>Powerful lifecycle management policies</strong>, automating transitions between storage tiers and data retention.</li></ul><h4>Apache Airflow: Orchestrating the Data Pipeline</h4><p>Airflow coordinates the entire pipeline, ensuring reliable execution of data extraction, transformation, and loading (ETL) tasks. It was chosen because of:</p><ul><li><strong>Flexible DAG-based workflows</strong>, making it easy to manage dependencies.</li><li><strong>Scalability</strong>, allowing for future expansion as data volume grows.</li></ul><p>In the following sections, we will dive deeper into the infrastructure and how each of these services is utilized within the pipeline.</p><h3>Google Cloud Platform Services Setup</h3><p>Before deploying this application, you need to enable several Google Cloud Platform (GCP) services. Follow the steps below to ensure all required services are properly configured.</p><h4>Required GCP Services</h4><p>You must enable the following APIs in your Google Cloud project:</p><ol><li><strong>Cloud Build API</strong> — Allows you to build container images in the cloud.</li><li><strong>Secret Manager API</strong> — Provides secure storage for sensitive configuration values.</li><li><strong>Cloud Run Admin API</strong> — Enables deployment and management of Cloud Run services.</li><li><strong>Artifact Registry API</strong> — Securely stores and manages container images and artifacts in the cloud.</li></ol><h4>Setting Up Repository Connection</h4><p>You’ll also need to:</p><ol><li>Connect your repository <strong>Github </strong>to Cloud Build (1st generation) for continuous integration. Follow this <a href="https://cloud.google.com/build/docs/automating-builds/github/connect-repo-github#console"><strong>guide</strong></a>.</li></ol><h3>Infrastructure Overview</h3><p>In this section, we will explore the infrastructure setup for our project. The infrastructure is provisioned using <a href="https://www.terraform.io/"><strong>Terraform</strong></a>, ensuring a scalable and reproducible deployment. Below, we break down the key components and their configurations.</p><h4>1. Provider Configuration (Google Cloud)</h4><p>This configuration block sets up the Google Cloud provider for Terraform. It specifies the required provider source (hashicorp/google) and a minimum version (&gt;= 4.50.0) within the terraform block, ensuring compatibility and leveraging recent features.</p><p>The provider &quot;google&quot; block then configures the connection details, specifically setting the target Google Cloud project ID using the input variable var.project_id. This allows Terraform to authenticate and manage resources within the specified project.</p><pre>### tf file ###<br>terraform {<br>  required_providers {<br>    google = {<br>      source  = &quot;hashicorp/google&quot;<br>      version = &quot;&gt;= 4.50.0&quot; // Use a recent version<br>    }<br>  }<br>}<br><br>provider &quot;google&quot; {<br>  project = var.project_id<br>}</pre><h4>2. IAM Configuration: Service Accounts and Roles</h4><p>This Terraform configuration focuses on setting up Identity and Access Management (IAM) resources within Google Cloud. It defines dedicated service accounts for different components of the data platform (dbt, Airflow, Cloud Build) and assigns them the specific permissions required to interact with Google Cloud services, adhering to the principle of least privilege.</p><p><strong>1. Service Account Creation</strong></p><p>Three distinct service accounts are created using the google_service_account resource:</p><ul><li>A service account for dbt (dbt_service_account).</li><li>A service account for Airflow (airflow_service_account).</li><li>A service account for Cloud Build (cloud_build_service_account).</li></ul><p><strong>2. Service Account Key Generation</strong></p><p>A JSON key (google_service_account_key) is generated specifically for the Airflow service account. This key file will be used in the airflow environmet to enable access for the right services in the GCP.</p><p><strong>3. IAM Role Assignment</strong></p><p>The google_project_iam_member resource grants the necessary permissions to each service account. Key aspects of this configuration include:</p><ul><li><strong>Dynamic Role Assignment:</strong> It iterates through lists of roles defined in variables (var.dbt_required_roles, var.airflow_required_roles, var.cloud_build_required_roles). This allows you to define the exact roles needed for each service account externally (e.g., in the variables.tf file).</li><li><strong>Service Account Member:</strong> The member attribute correctly formats the service account&#39;s email for the IAM binding.</li></ul><h3>3. Variables</h3><p>The variables.tf file defines various input parameters used throughout the infrastructure setup, such as:</p><ul><li>project_id (Google Cloud project ID)</li><li>region (GCP region for resources)</li><li>dbt_service_account_id (ID for the DBT service account)</li><li>airflow_service_account_id (ID for the Airflow service account)</li><li>cloud_build_service_account_id (ID for the Cloud Build service account)</li><li>dbt_service_account_display_name (Display name for the dbt service account)</li><li>airflow_service_account_display_name (Display name for the Airflow service account)</li><li>cloud_build_service_account_display_name (Display name for the Cloud Build service account)</li><li>dbt_required_roles (List of IAM roles for the dbt service account)</li><li>airflow_required_roles (List of IAM roles for the Airflow service account)</li><li>cloud_build_required_roles (List of IAM roles for the Cloud Build service account)</li><li>gcs_bucket_location (Region for the GCS bucket)</li><li>gcs_bucket_name_prefix (Prefix for the data staging GCS bucket name)</li><li>local_data_path (Local path to dataset files for upload)</li></ul><h4>4. Google Cloud Storage Bucket for Data Staging</h4><p>We provision a Google Cloud Storage (GCS) bucket primarily for staging the initial dataset files before they are processed further.</p><p><strong>Data Staging Bucket</strong></p><ul><li>The bucket name is constructed using the prefix defined in the var.gcs_bucket_name_prefix variable.</li><li>It stores the raw CSV dataset files uploaded from the local path specified by var.local_data_path.</li><li>Configured with Standard storage class and uniform bucket-level access for consistent permissions management.</li><li>A lifecycle policy automatically deletes objects older than 30 days to manage storage costs.</li><li>Versioning is disabled for this staging bucket.</li></ul><h4>5. Cloud Build Trigger for CI/CD</h4><p>We configure a Google Cloud Build trigger to automate the build and deployment process whenever changes are pushed to the specified branch in our GitHub repository.</p><p><strong>Main Branch Build Trigger (</strong><strong>pipeline-ecommerce-bq-dbt-trigger)</strong></p><ul><li>Automatically initiates a build pipeline upon code pushes to the primary development branch (defined by var.branch_name, e.g., main).</li><li>Connects to the GitHub repository specified by var.repo_owner and var.repo_name.</li><li>Executes the build steps defined in the GCP/pipeline-ecommerce-bq-dbt/cloud_run_dbt/cloudbuild.yml configuration file.</li><li>The trigger is scoped to only run if changes occur within the GCP/pipeline-ecommerce-bq-dbt/cloud_run_dbt/ directory to avoid unecessary triggers based on files outside of this project.</li><li>Uses a dedicated Cloud Build service account (referenced via google_service_account.cloud_build_service_account.email) for executing the build with appropriate permissions.</li></ul><h4>Running this environment</h4><p>If you want to run this environment in your cloud. First set all the personal variables in variables.tf such as <strong>project_id</strong>, <strong>service accounts</strong>, <strong>roles </strong>and etc (<em>Don’t forget to enable the APIs and connect your github as mentioned at the beginning of this article</em>). After that, you can use these commands:</p><pre>## Go to the terraform directory<br>cd GCP/pipeline-ecommerce-bq-dbt/terraform<br>## Init your project<br>terraform init<br>#Run plan to check all the changes.<br>terraform plan<br>#Apply all the changes when you want.<br>terraform apply<br>#Generate the airflow keyfile service account<br>jq -r &#39;.airflow_service_account_key_base64.value&#39; outputs_tf.json | \<br>base64 --decode &gt; ../airflow/keys/airflow_service_account.json</pre><p>Now that we understand how the infrastructure will be created, we will deep dive into the pipeline itself.</p><h3>Data Pipeline in Apache Airflow</h3><h3>Introduction</h3><p>First, we will walk through the Airflow pipeline process used to orchestrate an end-to-end ETL workflow in GCP and after that we will dive into each step of this pipeline. This pipeline automates data ingestion and transformation using authenticated requests to BigQuery and Cloud run.</p><h3>Setting Up Apache Airflow with Docker</h3><p>To get started with Apache Airflow using Docker, follow these simple steps:</p><p><strong>1. Go to the airflow directory </strong><br>First, go to the airflow directory where all the config files are stored.</p><pre>cd GCP/pipeline-ecommerce-bq-dbt/airflow</pre><p>After that, check if the Service account keyfile is in the keys folder.</p><p><strong>2. Set Up the Airflow User ID</strong><br>Generate the AIRFLOW_UID value and save it to a .env file:</p><pre>echo -e &quot;AIRFLOW_UID=$(id -u)&quot; &gt; .env</pre><p><strong>3. Initialize Airflow</strong><br>Run the initialization command to set up the Airflow database and admin user:</p><pre>docker-compose up airflow-init</pre><p><strong>4. Start Airflow</strong><br>Once initialized, start the Airflow containers in detached mode:</p><pre>docker-compose up - build -d</pre><p><strong>5. Clean Up (Optional)</strong><br>To stop and delete all containers, volumes, and networks, run:</p><pre>docker-compose down</pre><p>Now, your Airflow instance should be up and running! Access the Airflow UI at http://localhost:8080 and log in with the default credentials (airflow for both username and password).</p><h3>Airflow DAG Overview</h3><p>The Directed Acyclic Graph (DAG) defines the sequence of tasks required to extract, transform, and load (ETL) data efficiently. The pipeline will do these 2 tasks:</p><ol><li><strong>Create Raw Tables:</strong> Create the raw tables in BigQuery based the CSVs in Cloud Storage.</li><li><strong>Run Cloud Service DBT:</strong> Will make a request to the cloud run to execute the dbt in the container.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/342/1*Dssr7IhFfNxJRJ_1ZSzLsQ.png" /><figcaption>Dag Graph</figcaption></figure><h4>1. <strong>Create Raw Tables</strong></h4><p>In this step we define a list variable with all the files we need to create a raw table in BigQuery and use the BigQueryExecuteQueryOperator to run the query below:</p><pre>SQL_QUERY = f&quot;&quot;&quot;<br>              LOAD DATA OVERWRITE ecommerce_raw.{file}<br>              FROM FILES (<br>              format = &#39;CSV&#39;,<br>              uris = [&#39;gs://ecommerce_data_staging/{file}.csv&#39;]);<br>            &quot;&quot;&quot;</pre><p>Replacing the {file} for the name of the csv file stored in GCS we are able to load the data for each table.</p><h4>2. <strong>Run Cloud Service dbt</strong></h4><p>For this task, we are doing a request to start the Cloud Run service. Using that, the container will run the dbt Build and deploy all the tables in BigQuery.</p><h3>Cloud Run Container and dbt Transformation</h3><p>This section explores how to leverage Google Cloud Run containers to host and execute dbt (data build tool) transformations, creating an efficient and scalable data transformation pipeline.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Po9vXdtbe0ei5-bOYKqoFQ.png" /><figcaption>Cloud Run Container explanation</figcaption></figure><h4>Why Cloud Run for dbt?</h4><p>Cloud Run provides several advantages for hosting DBT transformations:</p><ul><li><strong>Serverless execution</strong>: Pay only for the compute time used during transformations.</li><li><strong>Automatic scaling</strong>: Handle variable workloads without manual intervention.</li><li><strong>Containerized isolation</strong>: Package all dependencies consistently for each pipeline you have.</li><li><strong>Simplified deployment</strong>: Reduce infrastructure management overhead.</li></ul><h4>Folder Structure Details</h4><p>For the cloud run container we have the following tree based structure:</p><pre>cloud_run_dbt/<br>├── dbt/<br>│   ├── logs/<br>│   ├── models/<br>│   │   ├── marts/<br>│   │   ├── stg/<br>│   │   └── schema.yml<br>│   ├── target/<br>│   └── .gitignore<br>├── .user.yml<br>├── dbt_project.yml<br>├── profiles.yml<br>├── app.py<br>├── cloudbuild.yml<br>├── Dockerfile<br>└── requirements.txt</pre><p>The deployment files are organized into two major categories: dbt deployment files and cloud run deployment files. The following section provides a detailed explanation of each category.</p><h4>1. dbt Deployment Files</h4><p><strong>profiles.yml</strong></p><p>This configuration file establishes the connection between dbt and your data warehouse (BigQuery):</p><pre>pipeline_ecommerce:<br>  outputs:<br>    dev:<br>      job_execution_timeout_seconds: 300<br>      job_retries: 1<br>      location: US<br>      method: oauth<br>      priority: interactive<br>      project: ecommerce-analysis-455200<br>      dataset: ecommerce<br>      threads: 3<br>      type: bigquery<br>  target: dev</pre><p><strong>dbt_project.yml</strong></p><p>This file contains the core project configuration and model organization structure. The project utilizes two distinct datasets suffix: “stg” and “marts”. The final dataset names are formed by combining the dataset name from profiles.yml with the +dataset defined in dbt_project.yml. For example, this results in ecommerce_stg and ecommerce_marts as the complete dataset names.</p><pre>name: &#39;pipeline_ecommerce&#39;<br>version: &#39;1.0.0&#39;<br><br># This setting configures which &quot;profile&quot; dbt uses for this project.<br>profile: &#39;pipeline_ecommerce&#39;<br><br># These configurations specify where dbt should look for different types of files.<br># The `model-paths` config, for example, states that models in this project can be<br># found in the &quot;models/&quot; directory. You probably won&#39;t need to change these!<br>model-paths: [&quot;models&quot;]<br>analysis-paths: [&quot;analyses&quot;]<br>test-paths: [&quot;tests&quot;]<br>seed-paths: [&quot;seeds&quot;]<br>macro-paths: [&quot;macros&quot;]<br>snapshot-paths: [&quot;snapshots&quot;]<br><br>clean-targets:         # directories to be removed by `dbt clean`<br>  - &quot;target&quot;<br>  - &quot;dbt_packages&quot;<br><br><br>models:<br>  pipeline_ecommerce:<br>    stg:<br>      materialized: table<br>      +dataset: stg<br>      +tags:<br>          - &quot;stg&quot;<br>    marts:<br>      +dataset: marts<br>      +tags:<br>          - &quot;marts&quot;</pre><p><strong>dbt Model Organization</strong></p><p>Within the container, dbt models are organized into three objects:</p><ul><li><strong>Schema YML files</strong>: Define the expected structure and validation rules.</li><li><strong>Staging Models</strong>: Perform initial transformation of raw data sources, focusing on cleaning, renaming, and type casting. These models create an intermediate layer using CREATE OR REPLACE TABLE operations, providing a stable foundation for downstream analytics.</li><li><strong>Marts Models</strong>: Business-focused dimensional models that uses the staging transformed data for analytics-ready tables aligned with specific business domains or use cases. These models utilize CREATE VIEW and MERGE INTO operations for efficient data processing and incremental loading.</li></ul><p>This organization follows dbt best practices, creating a modular architecture that enhances maintainability and clarity. Our development workflow follows a systematic approach:</p><p>Run STG Model → Test Results → If Pass Run the Mart Model</p><p>This methodical process ensures we maintain a robust transformation pipeline with comprehensive version control and targeted tests that validate our data at each stage of processing.</p><p><strong>Example STG Table Operation:</strong></p><pre>{{ config(materialized=&#39;table&#39;) }}<br><br>SELECT DISTINCT<br>    S.product_id<br>    ,S.product_category_name<br>    ,S.product_name_lenght<br>    ,S.product_description_lenght<br>    ,S.product_photos_qty<br>    ,S.product_weight_g<br>    ,S.product_length_cm<br>    ,S.product_height_cm<br>    ,S.product_width_cm<br>    ,CURRENT_TIMESTAMP() AS last_extract_ts<br><br>FROM `ecommerce-analysis-455200.ecommerce_raw.olist_products_dataset` S</pre><p><strong>Example Marts Table Operation:</strong></p><pre>{{<br>    config(<br>        materialized=&#39;incremental&#39;,<br>        incremental_strategy=&#39;merge&#39;,<br>        unique_key=&#39;product_id&#39;,<br>        on_schema_change=&#39;fail&#39;,<br>        cluster_by = [&quot;product_category_name&quot;],<br>    )<br>}}<br><br>with source_stg as (<br>    SELECT *<br>    FROM {{ ref(&#39;dim_products_stg&#39;) }}<br>    <br>    {% if is_incremental() %}<br>        where last_extract_ts &gt; (SELECT max(last_extract_ts) FROM {{ this }})<br>    {% endif %}<br>)<br><br>SELECT<br>    S.product_id<br>    ,S.product_category_name<br>    ,S.product_name_lenght<br>    ,S.product_description_lenght<br>    ,S.product_photos_qty<br>    ,S.product_weight_g<br>    ,S.product_length_cm<br>    ,S.product_height_cm<br>    ,S.product_width_cm<br>    ,S.last_extract_ts<br>FROM source_stg S</pre><p><strong>Example Marts View Operation</strong></p><pre>{{ config(materialized=&#39;view&#39;) }}<br><br>WITH AGG_CUSTOMER_ORDER AS (<br>  SELECT<br>    C.customer_unique_id,<br>    O.order_id,<br>    O.order_purchase_timestamp,<br>    COUNT(order_item_id) as qty_itens,<br>    SUM(I.price+I.freight_value) total_purchased_value,<br>  FROM<br>    {{ ref(&#39;fact_order_items&#39;) }} I<br>  <br>  LEFT JOIN<br>    {{ ref(&#39;dim_orders&#39;) }} O ON I.order_id = O.order_id<br>  <br>  LEFT JOIN<br>    {{ ref(&#39;dim_customers&#39;) }} C ON C.customer_id = O.customer_id<br>  <br>  GROUP BY<br>    1, 2, 3<br>)<br><br>SELECT<br>  customer_unique_id,<br>  COUNT(DISTINCT order_id) AS num_orders,<br>  SUM(qty_itens) as total_itens,<br>  SUM(total_purchased_value) AS total_purchased_value,<br>  MIN(order_purchase_timestamp) first_order_purchase_timestamp,<br>  MAX(order_purchase_timestamp) last_order_purchase_timestamp<br><br>FROM AGG_CUSTOMER_ORDER<br><br>GROUP BY 1</pre><h4>2. Cloud Run Deployment Files</h4><p>For our Cloud Run deployment, we utilize several key files to create a containerized dbt environment:</p><ol><li><strong>Dockerfile</strong>: Defines the container image for running dbt in the cloud, built on top of the official ghcr.io/dbt-labs/dbt-bigquery base image, which comes pre-configured with the dbt-bigquery adapter.</li><li><strong>cloudbuild.yml</strong>: Configuration for Google Cloud Build CI/CD pipeline that automates the build and deployment process of our container to Cloud Run.</li><li><strong>app.py</strong>: Flask application that serves as the entry point and orchestration layer, receiving authenticated requests from Airflow and executing the appropriate dbt commands.</li><li><strong>requirements.txt</strong>: Python dependencies for the application.</li></ol><p>At the core of our solution is a Python Flask application (app.py) that:</p><ol><li>Receives authenticated requests from Airflow</li><li>Triggers appropriate dbt commands based on the request</li><li>Returns execution results to the orchestrator</li></ol><p>The Flask server effectively bridges the gap between our workflow orchestration system and dbt processes, providing a clean API interface for running transformations on demand.</p><p>Our deployment process leverages the official dbt-bigquery image as a foundation, installs our custom dependencies, and deploys the containerized application to Cloud Run through Google Cloud Build automation.</p><h3>Conclusion</h3><p>This approach of deploying dbt on Google Cloud Run combines the best of both worlds: dbt’s powerful transformation capabilities with Cloud Run’s serverless scalability. This containerized solution offers cost efficiency (pay-per-use), simplified operations, and seamless integration with orchestration tools like Airflow.</p><p>By organizing models into schema definitions, staging transformations, and business-focused marts, we’ve created a maintainable framework that follows industry best practices. This architecture provides the foundation for scalable, version-controlled data transformations that can grow alongside your business needs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=eae10e0c7dc3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[End-to-End Data Engineering Project using AWS + Apache Iceberg + Snowflake]]></title>
            <link>https://medium.com/@claudiofilho22/end-to-end-data-engineering-project-using-aws-apache-iceberg-snowflake-90b76e7a1082?source=rss-af8e5bd722d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/90b76e7a1082</guid>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <dc:creator><![CDATA[Claudiocmm]]></dc:creator>
            <pubDate>Thu, 13 Mar 2025 20:41:31 GMT</pubDate>
            <atom:updated>2025-04-03T23:14:08.233Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F0T-aMeF01XugCOor2qkAg.png" /></figure><h3><strong>Introduction</strong></h3><p>This article demonstrates building an automated weather data pipeline using <strong>WeatherAPI</strong>, <strong>AWS</strong> services, and<strong> Snowflake</strong>. You’ll see how this system collects and processes both real-time and forecast data from global locations through a scalable, production-ready architecture.</p><p>This project is part of my GitHub portfolio showcasing various data engineering architectures to help both experienced and new data software engineers with practical examples. The complete code is available in the repository linked below.</p><p><a href="https://github.com/claudiocmm/data_engineering_projects/tree/main/AWS/pipeline-weather-data">data_engineering_projects/AWS/pipeline-weather-data at main · claudiocmm/data_engineering_projects</a></p><p>I’ve designed this modular pipeline to handle data efficiently while maintaining the scalability needed for production environments.</p><h3>Project Overview</h3><p>The project sources real-time and forecasted weather data from <a href="https://www.weatherapi.com/"><strong>WeatherAPI</strong></a>. This data is extracted, processed, and stored using a modular pipeline designed for scalability and reliability in AWS. Key components include:</p><ul><li><strong>Amazon Lambda</strong>: Fetches weather data from WeatherAPI at scheduled intervals, handling batch requests efficiently.</li><li><strong>AWS Glue:</strong> processes and transforms the collected data into a structured format in <a href="https://iceberg.apache.org/">Apache Iceberg</a> format for further analysis.</li><li><strong>Amazon S3 &amp; Athena</strong>: Stores raw and processed data, allowing on-demand querying and exploration.</li><li><strong>Apache Airflow DAG:</strong> defines the workflow, managing dependencies between tasks and ensuring execution in the right order.</li><li><strong>Snowflake:</strong> serves as the final data warehouse, enabling efficient BI analysis and reporting.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c88svTaMyGQPBHMGHYW-QA.png" /><figcaption>Architecture Diagram</figcaption></figure><p>The final result will be data visualization analysis using these four tables in Tableau:</p><ul><li><strong>dim_locations:</strong><br>This table contains metadata about locations such as names, time zones, and other geographical attributes. <br><strong>It allows users to separate dimensional location data from the fact tables, supporting better data organization and more efficient querying.</strong></li><li><strong>weather_actual_data_timeseries :</strong><br>This table contains detailed time-series data with granular metrics such as temperature, wind speed, humidity, and precipitation. It is primarily used for training forecasting models or conducting in-depth analyses of specific timeframes. <br><strong>For example, it allows users to examine how weather conditions fluctuated hour-by-hour over a particular day or week.</strong></li><li><strong>weather_actual_data_timeseries_agg :<br></strong>This table aggregates the actual weather data by <strong>year, month, and hour</strong>, providing a higher-level view of weather patterns. Its purpose is to enable analysis of how weather behaves during the same hour across different months. <br><strong>For instance, it can reveal trends such as solar radiation peaking during daylight hours and dropping at night, helping to identify seasonal or hourly patterns.</strong></li><li><strong>weather_forecast_data_timeseries :</strong><br>This table stores forecasted weather data with detailed time-series metrics. It is designed to support decision-making by providing predictions for upcoming days. <br><strong>For example, businesses or individuals can use this data to plan activities, optimize operations, or take preventive actions based on expected weather conditions.</strong></li></ul><p>In the image below we have the data model diagram of this project:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UjJ833LF_IWIm0iVjD_yow.png" /><figcaption>Data Model Diagram</figcaption></figure><h3>Architecture Decisions: Why These Technologies?</h3><h4>Apache Iceberg: Scalable Data Lake with ACID Guarantees</h4><p><a href="https://iceberg.apache.org/"><strong>Apache Iceberg</strong></a> was chosen as the table format for structured storage in Amazon S3 due to its ability to handle large-scale data efficiently while maintaining strong consistency. Unlike traditional Hive tables, Iceberg provides:</p><ul><li><strong>ACID transactions</strong>, ensuring reliable updates and deletions.</li><li><strong>Schema evolution</strong>, allowing flexibility as data changes over time.</li><li><strong>Partition pruning</strong>, improving query performance by reading only relevant data.</li></ul><p>This makes Iceberg an excellent choice for storing and processing raw and transformed weather data in a cost-effective, high-performance manner. Following a<strong> </strong><a href="https://www.databricks.com/glossary/data-lakehouse"><strong>Data Lakehouse</strong></a> architecture.</p><h4>Snowflake: A Cloud Data Warehouse for BI and Analytics</h4><p>Snowflake acts as the final destination for analytics due to its scalability, performance, ease of use and integration with the Glue Data Catalog using Apache Iceberg tables. Key benefits include:</p><ul><li><strong>Separation of storage and compute</strong>, allowing for cost-efficient query execution.</li><li><strong>Automatic scaling</strong>, adapting to different workloads without manual tuning.</li><li><strong>Built-in time travel and data sharing</strong>, enabling historical analysis and seamless collaboration.</li></ul><p>With Snowflake, weather data can be efficiently aggregated, analyzed, and visualized for decision-making.</p><h4>AWS Glue: Serverless ETL for Transformations</h4><p>AWS Glue simplifies data processing with its serverless architecture, making it ideal for ETL workflows. It supports:</p><ul><li><strong>Integration with Apache Iceberg</strong>, allowing seamless schema evolution.</li><li><strong>Automatic schema inference</strong>, reducing manual effort in defining data structures.</li><li><strong>Parallel processing</strong>, enabling faster transformations at scale.</li></ul><p>Glue processes the ingested weather data, converting it into a structured format suitable for analytics.</p><h4>Amazon S3 &amp; Athena: Cost-Effective Data Storage &amp; Querying</h4><p>Amazon S3 stores both raw and processed data, ensuring durability and low-cost storage. Athena enables SQL-based exploration of this data without requiring infrastructure management.</p><ul><li><strong>Decoupled storage and compute</strong>, optimizing cost and performance.</li><li><strong>On-demand querying</strong>, avoiding the need for an always-on database for ad-hoc analysis.</li><li><strong>Seamless integration with Apache Iceberg</strong>, improving query efficiency.</li></ul><h4>Apache Airflow: Orchestrating the Data Pipeline</h4><p>Airflow coordinates the entire pipeline, ensuring reliable execution of data extraction, transformation, and loading (ETL) tasks. It was chosen because of:</p><ul><li><strong>Flexible DAG-based workflows</strong>, making it easy to manage dependencies.</li><li><strong>Extensive AWS integration</strong>, simplifying connections to Lambda, Glue, and S3.</li><li><strong>Scalability</strong>, allowing for future expansion as data volume grows.</li></ul><p>In the following sections, we will dive deeper into the infrastructure and how each of these services is utilized within the pipeline.</p><h3>Infrastructure Overview</h3><p>In this section, we will explore the infrastructure setup for our project. The infrastructure is provisioned using <a href="https://www.terraform.io/"><strong>Terraform</strong></a>, ensuring a scalable and reproducible deployment. Below, we break down the key components and their configurations.</p><h4>1. Provider Configuration</h4><p>The provider.tf file defines AWS as our cloud provider, setting the region to us-east-1. This file also retrieves the current AWS caller identity to reference account-related information dynamically.</p><pre>provider &quot;aws&quot; {<br>  region  = &quot;us-east-1&quot; # Change this to your desired region<br>}</pre><h4>2. IAM Roles and Policies</h4><p>We define several IAM roles and policies to grant appropriate permissions for AWS Glue, Lambda, CodeBuild, and Snowflake. Below is a breakdown of these configurations:</p><h4>AWS Glue Role and Policy</h4><ul><li>The glue_role is created to run Glue jobs, this service role allows Glue to assume the role. Furthermore, this role has an attached policy that grants access to S3, Glue, CloudWatch and logs.</li></ul><h4>Lambda Execution Role and Policy</h4><ul><li>The lambda_role is created to run Lambda jobs, this service role allows Lambda functions to assume the role. Furthermore, this role has an attached policy that grants permissions to read/write S3, interact with Athena, create logs, and use Glue services.</li></ul><h4>CodeBuild Role and Policy</h4><ul><li>The codebuild-deploy-pipeline-weather-data-role is created to run CodeBuild jobs, this service role allows <a href="https://aws.amazon.com/pt/codebuild/"><strong>CodeBuild</strong></a><strong> </strong>to assume the role. Furthermore, this role has an attached policy that grants permission to use Lambda, Glue, IAM, and S3 resources. With that, is possible to use it to run the deploy of services (Glue and Lambda) for the project.</li></ul><h4>Snowflake IAM Role and Policy</h4><p>To run queries in snowflake and connect to AWS is necessary to create an IAM role to be assumed by the snowflake external IAM user. With that, this role will be created.</p><ul><li>The snowflake_service_role allows Snowflake to assume the role with external IDs for authentication. Furthermore, this role has an attached policy that grants Snowflake access to Glue tables and S3 objects.</li></ul><h4>3. Variables and Locals</h4><p>The variables.tf file defines various input parameters used throughout the infrastructure, such as:</p><ul><li>region (AWS region)</li><li>account_id (AWS account ID)</li><li>lambda_function_name (Name of the Lambda function)</li><li>pipeline_name (Name of the data pipeline)</li><li>Snowflake-specific variables for IAM integration</li><li>Github informations to use CI/CD in CodeBuild</li></ul><p>The locals.tf file defines computed values based on variables, helping maintain consistency across Terraform resources.</p><h4>4. S3 Buckets for Data Storage</h4><p>We provision multiple S3 buckets for storing raw and processed data, scripts, and auxiliary datasets. There is two buckets:</p><p><strong>Main Data Lake Bucket</strong></p><ul><li>weather-datalake-projectsstores the raw and transformed data.</li><li>A lifecycle policy automatically cleans up temporary files after one day in /tmp.</li><li>Ownership and ACL settings ensure proper access control.</li></ul><p><strong>Scripts Storage Bucket</strong></p><ul><li>weather-datalake-projects-scripts stores Glue and Lambda scripts to use for backup and in CI/CD.</li><li>Ownership and ACL settings ensure restricted access.</li></ul><h4>5. CodeBuild for CI/CD</h4><p>The <a href="https://aws.amazon.com/en/codebuild/"><strong>AWS CodeBuild</strong></a> is an AWS service which automates the deployment of Lambda functions and Glue scripts, ensuring an efficient CI/CD pipeline connected to the github. In lambda and glue folders you can see the buildspec.yml file used to make the build happens.</p><h4>6. AWS Glue</h4><p>For AWS Glue, the terraform was created to get the file dim_locations.json and create an external table, dim_locations, using the Iceberg table format. This table’s data resides in an S3 bucket, with its schema defined to include columns like country, name, lat, lon, and location_id.</p><p>This setup enables us to catalog location data, query it directly from S3 using services like Athena, and benefit from Iceberg’s advanced data management features. Furthermore, this table will be used for the pipeline to get data only for the locations defined in it.</p><h4>Running this environment</h4><p>If you want to run this environment in your cloud. First set all the personal variables in variables.tfsuch as <strong>account_id</strong>, <strong>github</strong>, and <strong>external_ids from snowflake</strong> (<em>later in this article I will show how to get them</em>). After that, you can use this commands:</p><pre>## Go to the terraform directory<br>cd AWS/pipeline-weather-data/terraform<br>## Init your project<br>terraform init<br>#Run plan to check all the changes.<br>terraform plan<br>#Apply all the changes when you want.<br>terraform apply</pre><p>Now that we understand how the infrastructure will be created, we will deep dive into the pipeline itself.</p><h3>Data Pipeline in Apache Airflow</h3><h4>Introduction</h4><p>First, we will walk through the Airflow pipeline process used to orchestrate an end-to-end ETL workflow in AWS and after that we will dive into each step of this pipeline. This pipeline automates data ingestion, transformation, and storage for weather data using various AWS services, including S3, Glue, Athena, and Lambda.</p><h4>Setting Up Apache Airflow with Docker</h4><p>To get started with Apache Airflow using Docker, follow these simple steps:</p><p><strong>1. Go to the airflow directory</strong><br>First, go to the airflow directory where all the config files are stored.</p><pre>cd AWS/pipeline-weather-data/airflow</pre><p><strong>2. Set Up the Airflow User ID</strong><br>Generate the AIRFLOW_UID value and save it to a .env file:</p><pre>echo -e &quot;AIRFLOW_UID=$(id -u)&quot; &gt; .env</pre><p><strong>3. Initialize Airflow</strong><br>Run the initialization command to set up the Airflow database and admin user:</p><pre>docker-compose up airflow-init</pre><p><strong>4. Start Airflow</strong><br>Once initialized, start the Airflow containers in detached mode:</p><pre>docker-compose up - build -d</pre><p><strong>5. Clean Up (Optional)</strong><br>To stop and delete all containers, volumes, and networks, run:</p><pre>docker-compose down</pre><p>Now, your Airflow instance should be up and running! Access the Airflow UI at http://localhost:8080 and log in with the default credentials (airflow for both username and password).</p><h4>Airflow DAG Overview</h4><p>The Directed Acyclic Graph (DAG) defines the sequence of tasks required to extract, transform, and load (ETL) weather data efficiently. The pipeline will do these 4 tasks:</p><ol><li><strong>Update </strong><strong>dim_locations Table:</strong> Ensures location data is available.</li><li><strong>Generate Date Ranges:</strong> Computes which locations and dates require data retrieval.</li><li><strong>Invoke Lambda Functions:</strong> Fetches weather data from the API in batches.</li><li><strong>Run Glue Jobs:</strong> Processes and aggregates the raw weather data to apache icerbeg tables for further analysis.</li></ol><h4>1. Update dim_locations Table</h4><p>In this step, we’ll determine if there’s new data available to update the dim_locations table. We&#39;ll utilize Athena queries, as this table was provisioned via Terraform and get the data from the dim_locations.jsonin bucket. Each pipeline run will perform a check for new data to be inserted or updated. This step is important in case this file be updated.</p><h4>2. Generating the List of Locations and Dates to process</h4><p>The generate_list_location_dates_to_collect function dynamically determines the dates and locations that need data collection. To avoid reprocessing, it will delete all the files inside the staging folder in the project bucket. After that, it queries AWS Athena to check the latest collected date per location and generates batches for incremental data retrieval. Each one of these batches will have the format below, and each task of lambda will process your specific batch file.</p><pre>{<br>  &quot;start_date&quot;: &quot;&lt;Start Date to collect data in YYYY-mm-dd format&gt;&quot;, <br>  &quot;end_date&quot;: &quot;&lt;End Date to collect data in YYYY-mm-dd format&gt;&quot;, <br>  &quot;location_ids&quot;: &quot;[list of all locations ids to collect in <br>this start/end date timeframe]&quot;<br>}</pre><h4>3. Extract the raw data using Lambda</h4><p>This step employs AWS Lambda functions to retrieve both current and forecasted weather data. To optimize API requests and prevent timeouts when handling large datasets, the process is divided into manageable batches.</p><h4>4. Transform the data using Glue</h4><p>This step involves using AWS Glue to transform the raw data retrieved by the Lambda function. Following the successful data extraction, Glue processes the data and creates Apache Iceberg tables, which are then used for further analysis.</p><p>With a clear understanding of the pipeline’s steps, we can now delve into the core services.</p><h3>Lambda Process</h3><p>This section dives into the Lambda function responsible for fetching weather data from the WeatherAPI and storing it in our data lake on Amazon S3. The function is triggered by an event containing two essential parameters.</p><ul><li><strong>api_token</strong>: The authentication token required to make requests to the WeatherAPI.</li><li><strong>batch_id</strong>: A unique identifier for the batch being processed. This ID is used to locate the corresponding metadata file in S3, which contains the list of locations and dates to process.</li></ul><p>The metadata file follows a standardized naming convention:</p><pre>f&quot;list_locations_dates_to_process_actual_data_batch_{batch_id}.json&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9NW86_-h25Pi0i4-mrcATg.png" /></figure><h4>Understanding the Code</h4><p>The core logic resides in the _async_lambda_handler function. Here&#39;s a breakdown of what it does:</p><ol><li><strong>Initialization</strong>: It sets up variables like the S3 bucket name, pipeline name, and database name.</li><li><strong>Retrieving Location and Date Data</strong>: It fetches a list of location IDs and date ranges from an S3 object — batch metadata file generated by airflow. This list determines which locations and dates to fetch weather data for.</li><li><strong>Asynchronous Fetching</strong>: It uses aiohttp to send concurrent requests to the WeatherAPI for each location and date. The fetched data is aggregated into a single file per location_id, avoiding the creation of thousands small files which would lead to performance issues in Spark Glue transformations.</li><li><strong>Data Storage</strong>: The fetched weather data is stored as JSON files in the designated S3 bucket.</li></ol><h4><strong>Async Processing in Action: Scaling data collection without overloading the infrastructure</strong></h4><p>The asynchronous approach became essential to accelerate data collection while maintaining system reliability. While async operations enable concurrent processing of multiple requests (dramatically improving speed), we needed to balance this with two critical constraints:</p><ol><li>API server rate limits</li><li>AWS Lambda’s 15-minute timeout threshold</li></ol><p>Through rigorous testing, we determined 30 concurrent requests to be the optimal balance — maximizing throughput without overwhelming external systems or triggering Lambda timeouts.</p><p><strong>Our Batch Processing Strategy</strong><br>We process data in optimized batches (generated as json files in bucket by airflow calculated) using:<br>Number of Points = Days × Locations</p><p><strong>Example Implementation</strong><br>For a batch spanning:</p><ul><li>Start Date: 2024–01–01</li><li>End Date: 2024–01–10 (10 days)</li><li>300 locations</li></ul><p>10 days × 300 locations = 3,000 data points</p><p>We configured Airflow to enforce an 8,000-point maximum per batch through dynamic task generation. This threshold:<br><em>✓ Prevents Lambda timeout triggers<br>✓ Respects API rate limits<br>✓ Allows headroom for unexpected delays<br>✓ Enables predictable resource allocation</em></p><p>By combining asynchronous processing with intelligent batching, we achieved faster data collection and reduced costs compared to synchronous methods. This approach enables handling more processes with fewer machines while maintaining system stability.</p><h3>Glue Process</h3><p>In this section, we’ll dive into the AWS Glue process, which serves as the backbone of our data transformation and loading pipeline. AWS Glue is a fully managed ETL (Extract, Transform, Load) service that simplifies the process of preparing and loading data for analytics.</p><p>Our Glue process is designed to handle two types of weather data: <strong>actual</strong> and <strong>forecast</strong>. The process involves reading raw JSON data from S3, transforming it into a structured format, and loading it into S3 using <a href="https://iceberg.apache.org/">Apache Iceberg</a> formats and integrate with the <a href="https://aws.amazon.com/pt/solutions/analytics/data-catalog/">Data Catalog</a>. At the end of the process, three tables are created:</p><ul><li><strong>weather_actual_data_timeseries</strong></li><li><strong>weather_actual_data_timeseries_agg</strong></li><li><strong>weather_forecast_data_timeseries</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sQi_Gz-KFnHDua1dGLE58Q.png" /></figure><h4>Overview of the Process</h4><p>The Glue process is divided into several key stages, each designed to handle specific tasks in the data pipeline:</p><p><strong>1. Schema Parsing and Table Creation:</strong></p><ul><li>The process begins by reading the schema of the data from a JSON file stored in S3. This schema defines the structure of the data, including column names, data types, and partitioning keys.</li><li>Using this schema, the Glue job creates Iceberg tables in the AWS Glue Data Catalog if they don’t already exist. These tables are partitioned to optimize query performance and storage efficiency.</li></ul><p><strong>2. Data Ingestion:</strong></p><ul><li>The Glue job reads raw JSON data from S3. Depending on the type of data (actual or forecast), it selects the appropriate S3 path where the data is stored.</li><li>The data is ingested into a <a href="https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-crawler-pyspark-extensions-dynamic-frame.html"><strong>DynamicFrame</strong></a>, a flexible data structure provided by AWS Glue that can handle semi-structured data like JSON.</li></ul><p><strong>3. Data Transformation</strong>:</p><ul><li>For <strong>actual weather data</strong>, the process involves:<br>- Flattening nested JSON structures to create a tabular format.<br>- Extracting relevant fields such as temperature, wind speed, humidity, and precipitation.<br>- Adding partition columns (e.g., year, month, day) to facilitate efficient querying and storage.<br>- Aggregating hourly data into year, month, hour summaries for analytical purposes.</li><li>For <strong>forecast weather data</strong>, the process includes:<br>- Flattening nested JSON structures to create a tabular format.<br>- Extracting forecasted weather metrics (e.g., temperature, wind speed, humidity) from the JSON data.<br>- Adding reference columns (e.g., ref_year, ref_month, ref_day) to track the date when the forecast was generated.</li></ul><p><strong>4. Data Loading</strong>:</p><ul><li>The transformed data is written to intermediate staging tables in the Iceberg format. These staging tables act as a temporary storage layer before the data is merged into the final tables.</li><li>A <a href="https://docs.aws.amazon.com/prescriptive-guidance/latest/apache-iceberg-on-aws/iceberg-spark.html#spark-upsert-data"><strong>merge operation</strong></a> is performed to update the main Iceberg tables with new data while avoiding duplicates. This ensures that the tables always contain the latest and most accurate information.</li></ul><h3>Snowflake connection</h3><p>Most of these connection steps are based on the <a href="https://quickstarts.snowflake.com/guide/data_lake_using_apache_iceberg_with_snowflake_and_aws_glue"><strong>Snowflake Quickstart Tutorial</strong></a> . Feel free to check it out if you need more details.</p><h4>1. Creating a Snowflake Trial Account</h4><p>Before setting up the integration, ensure you have a Snowflake trial account. You can sign up at <a href="https://www.snowflake.com/">Snowflake’s official website</a> and select the cloud provider as AWS.</p><h4>2. Creating the Warehouse and Database in Snowflake</h4><p>To store and process weather data, we first create a database and a warehouse in Snowflake:</p><pre>CREATE OR REPLACE DATABASE WAREHOUSE_WEATHER_DATA_DB;<br><br>CREATE OR REPLACE WAREHOUSE WAREHOUSE_WEATHER_DATA_DB<br>  WITH WAREHOUSE_SIZE = &#39;XSMALL&#39;<br>  INITIALLY_SUSPENDED = TRUE;</pre><p>This step ensures we have a designated compute resource and a database ready to handle our data processing tasks.</p><h4>3. Setting Up a Catalog Integration with AWS Glue</h4><p>To integrate Snowflake with AWS Glue, we need to create a catalog integration. This allows Snowflake to access metadata from the AWS Glue Data Catalog:</p><pre>CREATE OR REPLACE CATALOG INTEGRATION glueCatalog_WarehouseWeatherData<br>  CATALOG_SOURCE = GLUE<br>  CATALOG_NAMESPACE = &#39;warehouse_weather_data&#39;<br>  TABLE_FORMAT = ICEBERG<br>  GLUE_AWS_ROLE_ARN = &#39;arn:aws:iam::xxxxxxxxx:role/snowflake_service_role&#39;<br>  GLUE_CATALOG_ID = &#39;xxxxxxxxxxxxx&#39;<br>  GLUE_REGION = &#39;us-east-1&#39;<br>  ENABLED = TRUE<br>  REFRESH_INTERVAL_SECONDS = 600;</pre><h4>4. Creating an External Volume for S3</h4><p>Since the data is stored in Amazon S3, we define an external volume in Snowflake that references the S3 bucket:</p><pre>CREATE OR REPLACE EXTERNAL VOLUME warehouse_weather_data_vol<br>   STORAGE_LOCATIONS =<br>        (<br>            (<br>               NAME = &#39;s3_warehouse_weather_data.db&#39;<br>               STORAGE_PROVIDER= &#39;S3&#39;<br>               STORAGE_BASE_URL = &#39;s3://weather-datalake-projects/pipeline-weather-data/warehouse/warehouse_weather_data.db/&#39;<br>               STORAGE_AWS_ROLE_ARN=&#39;arn:aws:iam::xxxxxxxxxxx:role/snowflake_service_role&#39;<br>            )<br>        )<br>    ALLOW_WRITES=FALSE;</pre><h4>5. Setting up IAM permissions Snowflake and AWS</h4><p>After creating the resources (S3 and Catalog Integration), proper IAM permissions configuration is necessary. The following steps will guide you through setting up all required permissions to ensure secure access and operations.</p><blockquote><strong>Catalog Integration with AWS Glue</strong></blockquote><p>To enable Snowflake to interact with AWS Glue, we first create a <strong>catalog integration</strong>. This integration allows Snowflake to access metadata stored in Glue. After creating the integration, we need to retrieve two critical pieces of information:</p><ol><li><strong>GLUE_AWS_IAM_USER_ARN</strong>: The ARN of the IAM user created by Snowflake for Glue integration.</li><li><strong>GLUE_AWS_EXTERNAL_ID</strong>: A unique identifier used to secure the trust relationship between Snowflake and AWS.</li></ol><p>To retrieve these values, run the following command in Snowflake:</p><pre>DESCRIBE CATALOG INTEGRATION glueCatalog_WarehouseWeatherData;</pre><p>The output will include the GLUE_AWS_IAM_USER_ARN and GLUE_AWS_EXTERNAL_ID properties. Make a note of these values, as they will be used later in the Terraform configuration.</p><blockquote><strong>External Volume Integration with S3</strong></blockquote><p>Next, we need to configure an <strong>external volume</strong> in Snowflake to access data stored in an S3 bucket. This setup allows Snowflake to read and write data directly to S3. To retrieve the necessary details, describe the external volume:</p><pre>DESC EXTERNAL VOLUME warehouse_weather_data_vol;</pre><p>In the output, look for the STORAGE_LOCATIONS row. It will contain a JSON object similar to this:</p><pre>{<br>  &quot;NAME&quot;: &quot;my-s3-ice-ext-vol&quot;,<br>  &quot;STORAGE_PROVIDER&quot;: &quot;S3&quot;,<br>  &quot;STORAGE_BASE_URL&quot;: &quot;s3://glue-snowflake-devday-lab-6546xxxxxxxx/iceberg/&quot;,<br>  &quot;STORAGE_ALLOWED_LOCATIONS&quot;: [&quot;s3://glue-snowflake-lab-6546xxxxxxxx/iceberg/*&quot;],<br>  &quot;STORAGE_AWS_ROLE_ARN&quot;: &quot;arn:aws:iam::65465xxxxxxx:role/glue-snowflake-GluesnowflakedevdayLabRole-crOqCT36mDB4&quot;,<br>  &quot;STORAGE_AWS_IAM_USER_ARN&quot;: &quot;arn:aws:iam::90541xxxxxxxxxx:user/vvyk0000-s&quot;,<br>  &quot;STORAGE_AWS_EXTERNAL_ID&quot;: &quot;YJB50193_SFCRole=2_f1IsD5b8/DAFxxxxxxxxxxxx&quot;,<br>  &quot;ENCRYPTION_TYPE&quot;: &quot;NONE&quot;,<br>  &quot;ENCRYPTION_KMS_KEY_ID&quot;: &quot;&quot;<br>}</pre><p>From this JSON, extract the following values:</p><ul><li><strong>STORAGE_AWS_IAM_USER_ARN</strong>: The ARN of the IAM user created by Snowflake for S3 access.</li><li><strong>STORAGE_AWS_EXTERNAL_ID</strong>: A unique identifier used to secure the trust relationship between Snowflake and AWS.</li></ul><blockquote><strong>Configuring Terraform Variables</strong></blockquote><p>With the values retrieved from the previous steps, we can now define the necessary Terraform variables. These variables will be used to configure the IAM roles and permissions required for Snowflake to assume the role and access AWS resources.</p><p>Here’s an example of how to define these variables in your Terraform configuration variables.tf:</p><pre>variable &quot;aws_iam_user_arn_by_snowflake&quot; {<br>  type        = string<br>  description = &quot;IAM user ARN created by Snowflake for Glue or S3 integration&quot;<br>  default     = &quot;GLUE_AWS_IAM_USER_ARN or STORAGE_AWS_IAM_USER_ARN&quot;<br>}<br>variable &quot;glue_aws_external_id_by_snowflake&quot; {<br>  type        = string<br>  description = &quot;External ID for Glue integration&quot;<br>  default     = &quot;GLUE_AWS_EXTERNAL_ID&quot;<br>}<br>variable &quot;storage_aws_external_id_by_snowflake&quot; {<br>  type        = string<br>  description = &quot;External ID for S3 external volume integration&quot;<br>  default     = &quot;STORAGE_AWS_EXTERNAL_ID&quot;<br>}</pre><p>Replace the placeholders (GLUE_AWS_IAM_USER_ARN, GLUE_AWS_EXTERNAL_ID, etc.) with the actual values retrieved from Snowflake.</p><blockquote><strong>Assuming the Snowflake Role in AWS</strong></blockquote><p>Once the Terraform variables are configured, the IAM role specified in your Terraform configuration will be able to be assumed by the Snowflake user. This ensures that Snowflake has the necessary permissions to access Glue and S3 resources securely.</p><h4>6. Creating Iceberg Tables in Snowflake</h4><p>Using the Iceberg table format, we link Snowflake to the external tables stored in AWS Glue and S3:</p><p>First, we configure our Snowflake environment by selecting the appropriate database and warehouse:</p><pre>USE WAREHOUSE_WEATHER_DATA_DB;<br>USE WAREHOUSE_WEATHER_DATA_DB.PUBLIC;<br>USE WAREHOUSE WAREHOUSE_WEATHER_DATA_DB;</pre><p>Next, we create external Iceberg tables that map to our AWS Glue catalog tables. Each table is configured with:</p><ul><li>External volume reference</li><li>AWS Glue catalog connection</li><li>Auto-refresh for data consistency</li></ul><pre>CREATE OR REPLACE ICEBERG TABLE dim_locations<br>    EXTERNAL_VOLUME=&#39;warehouse_weather_data_vol&#39;<br>    CATALOG=&#39;glueCatalog_WarehouseWeatherData&#39;<br>    CATALOG_TABLE_NAME=&#39;dim_locations&#39;<br>    AUTO_REFRESH = TRUE;<br><br>CREATE OR REPLACE ICEBERG TABLE weather_actual_data_timeseries<br>    EXTERNAL_VOLUME=&#39;warehouse_weather_data_vol&#39;<br>    CATALOG=&#39;glueCatalog_WarehouseWeatherData&#39;<br>    CATALOG_TABLE_NAME=&#39;weather_actual_data_timeseries&#39;<br>    AUTO_REFRESH = TRUE;<br><br>CREATE OR REPLACE ICEBERG TABLE weather_actual_data_timeseries_agg<br>    EXTERNAL_VOLUME=&#39;warehouse_weather_data_vol&#39;<br>    CATALOG=&#39;glueCatalog_WarehouseWeatherData&#39;<br>    CATALOG_TABLE_NAME=&#39;weather_actual_data_timeseries_agg&#39;<br>    AUTO_REFRESH = TRUE;<br><br>CREATE OR REPLACE ICEBERG TABLE weather_forecast_data_timeseries<br>    EXTERNAL_VOLUME=&#39;warehouse_weather_data_vol&#39;<br>    CATALOG=&#39;glueCatalog_WarehouseWeatherData&#39;<br>    CATALOG_TABLE_NAME=&#39;weather_forecast_data_timeseries&#39;<br>    AUTO_REFRESH = TRUE;</pre><p>With these tables established, Snowflake users can now query weather data directly from S3 storage through the AWS Glue catalog, enabling unified data access across cloud platforms.</p><h4>7. Querying Data in Snowflake</h4><p>Once everything is set up, you can query the data directly from Snowflake:</p><pre>SELECT * FROM dim_locations LIMIT 10;<br>SELECT * FROM weather_actual_data_timeseries LIMIT 10;<br>SELECT * FROM weather_actual_data_timeseries_agg LIMIT 10;<br>SELECT * FROM weather_forecast_data_timeseries LIMIT 10;</pre><h3>Data Visualization Insights</h3><p>After connecting Snowflake to our Apache Iceberg Tables in S3, we integrated <a href="https://www.tableau.com/"><strong>Tableau</strong></a><strong> </strong>as our business intelligence visualization tool. This integration provides powerful analytical capabilities that transform the data into actionable insights. Let me walk you through the key visualization features we’ve implemented.</p><h4>Historical Data Analysis</h4><p>Our interactive visualization dashboard allows users to select specific geographic locations directly from the map interface. For example, when selecting Madrid, Spain, users can choose metrics such as temperature in Celsius to analyze. The system then displays comprehensive historical data through line charts, enabling trend identification and pattern recognition across different time periods.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yt4JBa-o0Emdh6wsCeQEgw.png" /></figure><h4>Forecast Visualization</h4><p>The forecast functionality provides a 14-day predictive view based on the reference date selection. This forward-looking capability helps users make informed decisions by anticipating future conditions for their selected metrics and locations.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LY6P1FmE-LRKhxd-xyBRWg.png" /></figure><h4>Pattern Analysis Using Aggregated Data</h4><p>The aggregated data visualization offers analysis based on metric selection, year of analysis, and location parameters. Users can visualize monthly and hourly time series views, revealing temporal patterns and regional variations in weather data.</p><p>The monthly analysis demonstrates clear seasonal trends — for instance, in the example below, temperatures in Fortaleza, Brazil are consistently lower in the early months of the year compared to later months, aligning with expected seasonal variations.</p><p>The hourly pattern analysis reveals daily temperature cycles with remarkable clarity. The visualization shows temperature drops during early morning and night hours, with peak temperatures occurring between 12:00 and 15:00 hours. This confirms expected diurnal temperature patterns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XvbjvSxPe-0San4ViLhYEQ.png" /></figure><h3>Conclusion</h3><p>This end-to-end data engineering solution demonstrates the power of combining AWS services, Apache Iceberg, and Snowflake in a modern data architecture. Our pipeline efficiently handles weather data from collection to visualization. The resulting architecture is scalable, cost-efficient, and adaptable to changing requirements.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=90b76e7a1082" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Criando um Agente SQL/BI usando LangGraph + Vertex AI Agent Builder + BigQuery]]></title>
            <link>https://medium.com/google-cloud-brasil/criando-um-agente-sql-bi-usando-langgraph-vertex-ai-agent-builder-bigquery-3a9fbd76f342?source=rss-af8e5bd722d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/3a9fbd76f342</guid>
            <category><![CDATA[langchain]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[vertex-ai]]></category>
            <category><![CDATA[langgraph]]></category>
            <dc:creator><![CDATA[Claudiocmm]]></dc:creator>
            <pubDate>Thu, 06 Mar 2025 17:32:42 GMT</pubDate>
            <atom:updated>2025-03-06T17:32:42.967Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xDGTxjpRstpyafvR1DjhZQ.png" /></figure><h3>Introdução</h3><p>Na era dos agentes de IA e da IA generativa, a automação de fluxos de trabalho de dados está transformando a Inteligência de Negócios (BI). Com o crescimento acelerado da tomada de decisões orientada por dados, as ferramentas de BI tornaram-se mais essenciais do que nunca, e a integração de agentes impulsionados por IA pode aumentar significativamente a eficiência, democratizando o acesso aos dados.</p><p>Neste artigo, exploraremos como construir um agente inteligente de SQL/BI usando <a href="https://langchain-ai.github.io/langgraph/tutorials/introduction/"><strong>LangGraph</strong></a><strong>, </strong><a href="https://cloud.google.com/products/agent-builder?hl=en"><strong>Vertex AI Agent Builder</strong></a><strong>, </strong><a href="https://python.langchain.com/docs/introduction/"><strong>LangChain </strong></a><strong>e </strong><a href="https://cloud.google.com/bigquery?hl=en"><strong>BigQuery</strong></a>. Este agente utiliza IA generativa para:</p><ul><li>Recuperar informações relevantes do esquema do banco de dados.</li><li>Gerar consultas SQL dinamicamente com base nos prompts dos usuários.</li><li>Validar e refinar consultas usando feedback iterativo.</li><li>Executar consultas no Google BigQuery.</li><li>Gerar visualizações informativas para apoiar a tomada de decisões.</li></ul><p>Ao aproveitar a automação impulsionada por IA, podemos simplificar todo o fluxo de trabalho de BI, tornando a análise de dados mais rápida, inteligente e acessível. Neste artigo vamos utilizar como base este <a href="https://github.com/claudiocmm/SqlBIAgent">repositório do Github</a> e explicar cada etapa do processo.</p><h3>RAG e Vertex AI Agent Builder</h3><p>Para aprimorar a geração de consultas, utilizamos o Retrieval-Augmented Generation (RAG) combinada com o Google Vertex AI Agent Builder. Em ambientes com milhares de tabelas, identificar as mais relevantes é crucial para a geração precisa de SQL.</p><p>Com o uso de RAG, o agente recupera apenas os documentos mais relevantes — garantindo que ele se concentre nas tabelas certas, em vez de escanear todo o banco de dados. O módulo <a href="https://python.langchain.com/docs/integrations/retrievers/google_vertex_ai_search/">VertexAISearchRetriever no LangChain</a> desempenha um papel fundamental nesse processo, ao buscar metadados de documentos relevantes, fornecendo ao agente uma consciência contextual essencial.</p><p>Neste projeto, cada “documento” corresponde a uma linha em uma tabela do BigQuery, contendo descrições das tabelas. Isso permite que o agente selecione com precisão a tabela mais relevante, recupere seu esquema e gere consultas SQL precisas.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1YHz_xFTOPpdakXI2cE__A.png" /><figcaption>Arquitetura projeto GCP</figcaption></figure><p>Agora, vamos configurar o projeto.</p><h3>Carregando Conjuntos de Dados no BigQuery</h3><p>Para preparar as tabelas necessárias no BigQuery, execute o script upload_datasets_in_bq.py.</p><pre>python upload_datasets_in_bq.py</pre><p>Este script automatiza a ingestão de múltiplos conjuntos de dados com esquemas fixos e descrições de colunas.</p><p>Certifique-se de que os conjuntos de dados estejam disponíveis no diretório datasets, com os arquivos CSV e JSON de esquema correspondentes. Execute o script para carregar conjuntos de dados como:</p><ul><li>tables_descriptions (metadados para busca estruturada)</li><li>hotel_bookings (dados de reservas de hotéis)</li><li>supermarket_sales (registros de transações no varejo)</li><li>netflix_movies_and_tv_shows (metadados de conteúdo de streaming)</li><li>video_games_sales (dados de vendas de jogos)</li></ul><h3>Configurando o Vertex AI Agent Builder no GCP</h3><p>Para integrar o Agent Builder ao BigQuery e habilitar a busca estruturada, siga os passos abaixo:</p><h4>1. Criar um Data Store para Descrições de Tabelas</h4><p>O Agent Builder depende de dados estruturados para recuperar tabelas relevantes do BigQuery. Para configurar este Data Store:</p><ul><li>Acesse o Google Cloud Console → Agent Builder → Habilite a Vertex API → Data Stores.</li><li>Clique em <strong>Create Data Store</strong> e escolha BigQuery como fonte.</li><li>Selecione <strong>Structured — BigQuery table with your own schema</strong>.</li><li>Escolha a opção <strong>One-Time Ingestion</strong> e selecione a tabela tables_descriptions.</li><li>Na pré-visualização do esquema (Schema Preview), verifique se a coluna description possui a propriedade-chave description e o esquema da tabela contém campos como project_id, dataset_id e table_id.</li><li>Selecione a localização global e nomeie seu Data Store como tables_descriptions. Finalize a configuração.</li></ul><h4>2. Criar um Aplicativo para Busca de Tabelas</h4><p>Agora, crie um aplicativo que permita ao Agent Builder pesquisar no Data Store estruturado:</p><ul><li>Navegue até <strong>Vertex AI Agent Builder → Apps</strong>.</li><li>Clique em <strong>Create New App</strong>.</li><li>Escolha a opção <strong>“Search for your website”</strong> para realizar buscas em dados estruturados.</li><li>Nomeie seu aplicativo como table_search_app.</li><li>Escolha um nome de empresa e defina a localização como global.</li><li>Selecione o Data Store criado anteriormente (tables_descriptions).</li><li>Crie o aplicativo (o processo pode levar alguns minutos) e teste-o fazendo consultas específicas para descrições de tabelas.</li></ul><p>O resultado final no Data store será semelhante ao mostrado na figura abaixo:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*3vVSGexjPoOLpOrA" /></figure><p>Após a conclusão, podemos obter o ID do Data Store e a região para configurar o retriever no script.</p><h3>Escolha da LLM (Large Language Model)</h3><p>Ao selecionar um provedor de API para este projeto, foi escolhido o Groq principalmente por oferecer acesso gratuito à API, tornando-o uma excelente opção para desenvolvimento e experimentação sem custos iniciais. Isso permite prototipagem flexível sem preocupações com taxas de uso.</p><p>Para o modelo, escolhe-se o LLaMA 3–70B-8192, que apresenta um bom desempenho na geração de SQL e no raciocínio contextual. No entanto, você pode facilmente substituí-lo por qualquer modelo que atenda às suas necessidades.</p><pre>llm = ChatGroq(model=&quot;llama3–70b-8192&quot;, temperature=0.3)</pre><h3>Configurando as Variáveis de Ambiente</h3><p>Para configurar corretamente o ambiente da aplicação, siga estes passos:</p><h4>1. Configurar o Arquivo .env</h4><p>Crie um arquivo .env no diretório raiz do projeto e adicione a seguinte linha:</p><pre>GROQ_API_KEY=your_groq_api_key_here</pre><p>Substitua your_groq_api_key_here pela sua chave de API real.</p><h4>2. Atualizar o arquivo settings.py</h4><p>Certifique-se de que o arquivo settings.py carregue as variáveis de ambiente corretamente:</p><pre>from dotenv import load_dotenv<br>import os<br>project_id = &quot;PROJECT IN GCP&quot;<br>dataset_id = &quot;DATASET IN BIGQUERY&quot;<br>vertex_agent_builder_data_store_location = &quot;VERTEX AGENT BUILDER DATA STORE<br>LOCATION&quot;<br>vertex_agent_builder_data_store_id = &quot;VERTEX AGENT BUILDER DATA STORE ID&quot;<br># Load environment variables from .env file<br>load_dotenv(&quot;PATH TO YOUR .ENV FILE&quot;)<br># Set the API key for Groq<br>os.environ[&quot;GROQ_API_KEY&quot;] = os.getenv(&quot;GROQ_API_KEY&quot;)</pre><p>Substitua “PATH TO YOUR .ENV FILE” pelo caminho correto para o arquivo .env no seu projeto.</p><h3>Explicação das Variáveis de Estado do Agente</h3><pre>class AgentState(TypedDict):<br>    question: str<br>    database_schemas: str<br>    query: str<br>    max_num_retries_debug: int<br>    num_retries_debug_sql: int<br>    result_debug_sql: str<br>    error_msg_debug_sql: str<br>    df: pd.DataFrame<br>    visualization_request: str<br>    python_code_data_visualization: str<br>    python_code_store_variables_dict: dict<br>    num_retries_debug_python_code_data_visualization: int<br>    result_debug_python_code_data_visualization: str<br>    error_msg_debug_python_code_data_visualization: str</pre><p>A classe AgentState define as variáveis que permanecem ao longo do fluxo de trabalho no LangGraph. Essas variáveis são usadas para preencher prompts do sistema e executar cada nó no fluxo. Os principais atributos incluem:</p><ul><li><strong>question</strong>: Consulta de entrada do usuário.</li><li><strong>database_schemas</strong>: Detalhes do esquema das tabelas relevantes.</li><li><strong>query</strong>: A consulta SQL gerada pelo agente.</li><li><strong>max_num_retries_debug</strong>: Máximo de tentativas de repetição para depuração de nós.</li><li><strong>num_retries_debug_sql</strong>: Rastreamento das tentativas de repetição para depuração de SQL.</li><li><strong>result_debug_sql</strong>: Indica se a validação do SQL foi aprovada.</li><li><strong>error_msg_debug_sql</strong>: Mensagem de erro da depuração do SQL.</li><li><strong>df</strong>: O dataframe contendo os resultados da consulta.</li><li><strong>visualization_request</strong>: Solicitação de visualização gerada pelo agente especialista em BI.</li><li><strong>python_code_data_visualization</strong>: Código Python gerado para visualização de dados.</li><li><strong>python_code_store_variables_dict</strong>: Todas as variáveis armazenadas na execução do código Python para visualização de dados.</li><li><strong>num_retries_debug_python_code_data_visualization</strong>: Rastreamento das tentativas de repetição para depuração do código de visualização de dados.</li><li><strong>result_debug_python_code_data_visualization</strong>: Indica se a validação do código de visualização de dados foi aprovada.</li><li><strong>error_msg_debug_python_code_data_visualization</strong>: Mensagem de erro da depuração do código de visualização.</li></ul><p>Essas variáveis ajudam a manter a consistência do estado enquanto o agente avança por diferentes etapas do fluxo.</p><h3>Fluxo de Trabalho no LangGraph</h3><p>O <strong>StateGraph</strong> no LangGraph gerencia o fluxo de trabalho utilizando nós definidos. Para cada nó de agente, você pode visualizar seus prompts no arquivo prompts.py e o fluxo de trabalho no workflow.py. Abaixo está um esboço de como cada nó contribui para a funcionalidade do agente:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/350/0*PCkwZjsTcKdd4Ghl" /><figcaption>LangGraph Workflow</figcaption></figure><h4><strong>1. Recuperando Tabelas Relevantes (Busca de tabelas e esquemas)</strong></h4><p>Este nó usa o <strong>Vertex AI Agent Builder</strong> para encontrar as tabelas relevantes para a pergunta do usuário e busca os detalhes do esquema usando o <strong>BigQuery</strong> Client. Ele realiza uma pesquisa estruturada para identificar as tabelas que correspondem ao contexto da consulta do usuário.</p><h4><strong>2. Gerando Consultas SQL (Agente escritor de SQL)</strong></h4><p>Usando uma LLM, o agente gera uma consulta SQL com base no esquema recuperado e na entrada do usuário. O esquema inclui descrições de colunas, fornecendo o contexto necessário para o agente interpretar corretamente a estrutura da tabela e gerar uma consulta SQL precisa.</p><h4><strong>3. Validando Consultas SQL (Agente validador de SQL)</strong></h4><p>A consulta gerada passa primeiro por uma execução de teste (“dry run”) no BigQuery para verificar erros de sintaxe. Em seguida, ela é executada de fato. Se algum dos dois processos falhar, o agente analisa as mensagens de erro e refina a consulta até que o número máximo de tentativas seja atingido.</p><h4><strong>4. Análise de BI (Agente especialista em BI)</strong></h4><p>O agente gera uma descrição dos dados e sugere técnicas apropriadas de visualização. Ele determina se é necessário um gráfico, uma tabela ou uma impressão de valor único, com base nas características dos dados e nas necessidades do usuário.</p><h4><strong>5. Gerando Código de Visualização (Agente gerador de código Python para visualização de dados)</strong></h4><p>O agente gera o código Python (por exemplo, usando Plotly para gráficos) com base na sugestão do Agente Especialista em BI para visualizar os resultados da consulta. Esse código pode gerar gráficos ou outras formas de visualização dos dados.</p><h4><strong>6. Validando o Código de Visualização (Agente validador de código Python para visualização de dados)</strong></h4><p>O código de visualização gerado é executado e validado. Se ele falhar, o agente tenta corrigi-lo com base no feedback dos erros até que o número máximo de tentativas seja atingido. Isso garante que as visualizações geradas sejam precisas e úteis para análise.</p><h3>Executando o Fluxo de Trabalho</h3><p>Para executar o agente, basta chamar a função <strong>run_workflow </strong>no arquivo run_workflow.py com a sua pergunta como entrada:</p><pre>state = run_workflow(question=&quot;How many movies were released in <br>2020 on Netflix?&quot;)</pre><p>Para aprimorar a interação do usuário, foi desenvolvida uma página no <strong>Streamlit </strong>que permite a comunicação com o agente. Nessa página, os usuários podem fazer perguntas e obter respostas dinâmicas, visualizações e insights com base nas consultas realizadas, tudo de forma interativa e intuitiva. Agora, vamos conferir alguns exemplos e seus resultados. As perguntas foram formuladas em inglês, pois a base de dados e os prompts estão todos documentados neste idioma.</p><p><em>“What are the released years with more released movies and tv shows added in netflix. Show me a top 10 by two categories (movie and tv show)”</em></p><p><strong>Tradução:</strong> “Quais são os anos de lançamento com mais filmes e séries adicionados à Netflix? Mostre-me um top 10 dividido em duas categorias (filmes e séries).”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*BHxvGFl5plAz6fNg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*bVlYDoHIsyEHQ_ea" /></figure><p><em>“What are the top 5 best-selling product categories for each supermarket?”</em></p><p><strong>Tradução: </strong>“Quais são as 5 categorias de produtos mais vendidas para cada supermercado?”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*QiuWU4pbS2YfW_h-" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*LuZyujERR12ceoAJ" /></figure><p><em>“I would like to see a comparison of resort hotel no-shows. Show me the numbers and the percentage difference between years.”</em></p><p><strong>Tradução: </strong>“Gostaria de ver uma comparação de no-shows em hotéis resort. Mostre-me os números e a diferença percentual entre os anos.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*SJVmO-6DKVuC-ffU" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*_ZmPTjk3N6WwY-gM" /></figure><p><em>“I would like a line chart showing the yearly sales from 1980 until 2013 for the top 3 best-selling video game genres of all time.”</em></p><p><strong>Tradução: </strong>“Gostaria de um gráfico de linha mostrando as vendas anuais de 1980 até 2013 para os 3 gêneros de videogames mais vendidos de todos os tempos.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*vYDNgxsICVJ_dqsJ" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/0*1Yuf0Lk-_WrknUq2" /></figure><p>O agente lidou com<strong> quatro conjuntos de dados diferentes, visualizações e consultas</strong>. Além disso, algumas consultas utilizaram conceitos avançados, como <a href="https://www.geeksforgeeks.org/window-functions-in-sql/">window functions</a>, mostrando o potencial desse projeto para realizar análises complexas de maneira eficiente e automatizada.</p><h3>Conclusão</h3><p>Este agente SQL/BI demonstra o poder do <strong>LangGraph</strong>, <strong>Vertex AI Agent Builder</strong> e <strong>LangChain</strong> na automação de fluxos de trabalho de BI. Ao utilizar <strong>RAG</strong> e execução baseada em estados com LangGraph, este projeto é capaz de se adaptar dinamicamente às consultas do usuário, garantindo a geração de SQL de alta qualidade e visualizações promissoras.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3a9fbd76f342" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud-brasil/criando-um-agente-sql-bi-usando-langgraph-vertex-ai-agent-builder-bigquery-3a9fbd76f342">Criando um Agente SQL/BI usando LangGraph + Vertex AI Agent Builder + BigQuery</a> was originally published in <a href="https://medium.com/google-cloud-brasil">google-cloud-brasil</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating a SQL/BI Agent using LangGraph + Vertex AI Agent Builder + BigQuery]]></title>
            <link>https://medium.com/@claudiofilho22/creating-a-sql-bi-agent-using-langgraph-vertex-ai-agent-builder-9c497d38de53?source=rss-af8e5bd722d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/9c497d38de53</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[vertex-ai]]></category>
            <category><![CDATA[langgraph]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[generative-ai-use-cases]]></category>
            <dc:creator><![CDATA[Claudiocmm]]></dc:creator>
            <pubDate>Sat, 01 Feb 2025 14:37:14 GMT</pubDate>
            <atom:updated>2025-02-05T15:23:13.823Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xDGTxjpRstpyafvR1DjhZQ.png" /></figure><h3>Introduction</h3><p>In the era of AI agents and generative AI, automating data workflows is transforming Business Intelligence (BI). With the rapid growth of data-driven decision-making, BI tools have become more essential than ever, and integrating AI-powered agents can significantly enhance efficiency, democratizing access to data.</p><p>In this article, we’ll explore how to build an intelligent SQL/BI agent using <strong>LangGraph, Vertex AI Agent Builder, and LangChain</strong>. This agent leverages generative AI to:</p><ul><li>Retrieve relevant database schema information.</li><li>Generate SQL queries dynamically based on user prompts.</li><li>Validate and refine queries using iterative feedback.</li><li>Execute queries in <strong>Google BigQuery</strong>.</li><li>Generate insightful visualizations to support decision-making.</li></ul><p>By harnessing AI-driven automation, we can streamline the entire BI workflow, making data analysis faster, smarter, and more accessible.</p><blockquote>The Repo: <a href="https://github.com/claudiocmm/SqlBIAgent"><strong>https://github.com/claudiocmm/SqlBIAgent</strong></a></blockquote><h3>RAG and Vertex AI Agent Builder</h3><p>To enhance query generation, we leverage <strong>Retrieval-Augmented Generation (RAG)</strong> combined with <strong>Google Vertex AI Agent Builder</strong>. In environments with thousands of tables, identifying the most relevant ones is critical for accurate SQL generation.</p><p>Using <strong>RAG</strong>, the agent retrieves only the most relevant documents — ensuring it focuses on the right tables instead of scanning an entire database. The <strong>VertexAISearchRetriever</strong> module in <strong>LangChain</strong> plays a key role in this process by fetching metadata from relevant documents, providing the agent with crucial contextual awareness.</p><p>In this project, each “document” corresponds to a row in a <strong>BigQuery table</strong>, containing table descriptions. This allows the agent to accurately select the most relevant table, retrieve its schema, and generate precise SQL queries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LQbX-yT4CunAqJhfJccaBA.png" /><figcaption>Project GCP Architecture</figcaption></figure><p>Now, let’s set up the project.</p><h3>Uploading Datasets to BigQuery</h3><p>To prepare the required tables in BigQuery, run the upload_datasets_in_bq.py script. This script automates the ingestion of multiple datasets with fixed schema and column descriptions.</p><ol><li>Ensure the datasets are available in the datasets/ directory with corresponding CSV and schema JSON files.</li><li>Run the script to upload datasets such as:</li></ol><ul><li>tables_descriptions (metadata for structured search)</li><li>hotel_bookings (hotel reservation data)</li><li>supermarket_sales (retail transaction records)</li><li>netflix_movies_and_tv_shows (streaming content metadata)</li><li>video_games_sales (game sales data)</li></ul><p>Executing the script will automatically ingest these datasets into BigQuery, making them available for analysis and search.</p><h3>Setting Up the Vertex AI Agent Builder in GCP</h3><p>To integrate the <strong>Agent Builder</strong> with <strong>BigQuery</strong> and enable structured search, follow these steps:</p><h4>1. Create a Data Store for Table Descriptions</h4><p>The Agent Builder relies on structured data to retrieve relevant BigQuery tables. To set up this data store:</p><ol><li><strong>Go to Google Cloud Console</strong> → <strong>Agent Builder</strong> → <strong>Enable the Vertex API </strong>→ <strong>Data Stores</strong>.</li><li>Click <strong>Create Data Store</strong> and choose <strong>BigQuery </strong>as source.</li><li>Select <strong>Structured — BigQuery table with your own schem</strong>a</li><li>Choose <strong>One-Time Ingestion </strong>and choose the tables_descriptions table.</li><li>In the <strong>Schema Preview</strong>, ensure the descriptioncolumn has the<strong> <em>key property description</em></strong> and table schema contains fields such as project_id, dataset_id, table_id.</li><li>Select <strong>global location</strong> and name your data store as <strong>tables_descriptions</strong> and finalize the setup.</li></ol><h4>2. Create an Application for Table Search</h4><p>Now, build an application that enables the Agent Builder to search the structured data store:</p><ol><li>Navigate to <strong>Vertex AI Agent Builder</strong> → <strong>Apps</strong>.</li><li>Click <strong>Create New App</strong>.</li><li>Choose <strong>Search for your website</strong> option to search accros structured data.</li><li>Name your app as <strong>table_search_app.</strong></li><li>Choose a company name and set global location.</li><li>Select the previously created data store (<strong>tables_descriptions</strong>).</li><li>Create the app (it can take some minutes) and test it by querying for specific table descriptions.</li></ol><p>The final result in Data Store will be:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/832/1*5OpRF_rJS7ooVX7sSACJpQ.png" /><figcaption>Data store in Vertex AI Agent Builder</figcaption></figure><p>Once completed, we can get the Data Store ID and region to setup the retriever in the script.</p><h3>LLM choose</h3><p>When selecting an API provider for this project, I chose <strong>Groq</strong> primarily for its <strong>free-tier API access</strong>, making it an excellent option for development and experimentation without upfront costs. This allows for flexible prototyping without worrying about usage fees.</p><p>For the model, I selected <strong>LLaMA 3–70B-8192</strong>, which offers strong performance for SQL generation and contextual reasoning. However, you can easily swap it out for any model that fits your needs.</p><pre>llm = ChatGroq(model=&quot;llama3-70b-8192&quot;, temperature=0.3)</pre><h3>Setting Up the Environment Variables</h3><p>To properly configure the environment for the application, follow these steps:</p><h4>1. Configure the .env File</h4><p>Create a .env file in the project root directory and add the following line:</p><pre>GROQ_API_KEY=your_groq_api_key_here</pre><p>Replace your_groq_api_key_here with your actual API key.</p><h4>2. Update settings.py</h4><p>Ensure the settings.py file loads the environment variables correctly:</p><pre>from dotenv import load_dotenv<br>import os<br><br>project_id = &quot;PROJECT IN GCP&quot;<br>dataset_id = &quot;DATASET IN BIGQUERY&quot;<br>vertex_agent_builder_data_store_location = &quot;VERTEX AGENT BUILDER DATA STORE <br>LOCATION&quot;<br>vertex_agent_builder_data_store_id = &quot;VERTEX AGENT BUILDER DATA STORE ID&quot;<br><br># Load environment variables from .env file<br>load_dotenv(&quot;PATH TO YOUR .ENV FILE&quot;)<br><br># Set the API key for Groq<br>os.environ[&quot;GROQ_API_KEY&quot;] = os.getenv(&quot;GROQ_API_KEY&quot;)</pre><h3>Agent State Variables Explained</h3><pre>class AgentState(TypedDict):<br>    question: str<br>    database_schemas: str<br>    query: str<br>    max_num_retries_debug: int<br>    num_retries_debug_sql: int<br>    result_debug_sql: str<br>    error_msg_debug_sql: str<br>    df: pd.DataFrame<br>    visualization_request: str<br>    python_code_data_visualization: str<br>    python_code_store_variables_dict: dict<br>    num_retries_debug_python_code_data_visualization: int<br>    result_debug_python_code_data_visualization: str<br>    error_msg_debug_python_code_data_visualization: str</pre><p>The AgentState class defines the variables that persist throughout the workflow in langGraph. These variables are used to fill system prompts to run each node in the workflow. Key attributes include:</p><ul><li>question: User&#39;s input query.</li><li>database_schemas: Schema details of relevant tables.</li><li>query: The SQL query generated by the agent.</li><li>max_num_retries_debug: Max retry attempts for debugging nodes.</li><li>num_retries_debug_sql: Tracks retry attempts for SQL debugging.</li><li>result_debug_sql: Indicates if SQL validation passed.</li><li>error_msg_debug_sql: Error message from the SQL debugging</li><li>df: The dataframe holding query results.</li><li>visualization_request: The generated visualization request made by the BI expert agent.</li><li>python_code_data_visualization: The Python code generate for data visualization.</li><li>python_code_store_variables_dict: All variables stored in the execution of the Python code for data visualization.</li><li>num_retries_debug_python_code_data_visualization: Tracks retries for data visualization code debugging.</li><li>result_debug_python_code_data_visualization: Indicates if the data visualization code validation passed.</li><li>error_msg_debug_python_code_data_visualization: Error message from the visualization code debugging</li></ul><p>These variables help maintain state consistency as the agent progresses through different steps.</p><h3>Workflow in LangGraph</h3><p>The <strong>StateGraph</strong> in LangGraph manages the workflow using defined nodes. For each agent node you can see your prompts in prompts.py file and the workflow in workflow.py. Below is an outline of how each node contributes to the agent’s functionality:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/350/1*37g3LkRMshMvyZeuUtxtKg.png" /><figcaption>LangGraph Workflow</figcaption></figure><h4>1. Retrieving Relevant Tables (Search tables and schemas)</h4><p>This node uses <strong>Vertex AI Agent Builder</strong> to find relevant tables to the user’s question and fetches schema details using <strong>BigQuery </strong>Client.</p><h4>2. Generating SQL Queries (Agent sql writer)</h4><p>Using an LLM, the agent generates an SQL query based on the retrieved schema and user input. The schema includes column descriptions, providing the agent with essential context to accurately interpret the table structure.</p><h4>3. Validating SQL Queries (Agents sql validator)</h4><p>The generated query first undergoes a <strong>dry run in BigQuery</strong> to check for syntax errors, followed by an <strong>actual execution</strong>. If either run fails, the agent analyzes the error messages and refines the query accordingly until the maximum number of retries is reached.</p><h4>4. BI Analysis (Agent BI expert)</h4><p>The agent generates a description of the data and suggests appropriate visualization techniques. It determines whether a chart, a table, or a single value print is needed.</p><h4>5. Generating Visualization Code (Agent python code data visualization generator)</h4><p>Agent to generate Python code (e.g., using <strong>Plotly </strong>for charts) based the suggestion of the BI Expert Agent to visualize the query results.</p><h4>6. Validating Visualization Code (Agent python code data visualization validator)</h4><p>The generated visualization code is executed and validated. If it fails, the agent attempts to fix it based on error feedback until the maximum number of retries is reached.</p><h3>Running the Workflow</h3><p>To execute the agent, simply call the run_workflow function in run_workflow.pywith your question as input:</p><pre>state = run_workflow(question=&quot;How many movies were released in <br>2020 on Netflix?&quot;)</pre><p>For enhance the user experience, I created a streamlit page to chat with this agent.</p><p><strong>Let’s walk through some examples and their results:</strong></p><p><em>“What are the released years with more released movies and tv shows added in netflix. Show me a top 10 by two categories (movie and tv show)”</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hYPD7zfVpYenflVtqUqX0w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/939/1*_F9sTIj05grCtBuyPFMphg.png" /></figure><p><em>“What are the top 5 best-selling product categories for each supermarket?”</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZWnhFhgweFybOauOaXNlvQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/939/1*jpMkjaTn4WJ2KDmvP59TLQ.png" /></figure><p><em>“I’d like to see a comparison of resort hotel no-shows. Show me the numbers and the percentage difference between years.&quot;</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9wttZ69yFMh_8FW8U5WCcQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8oWgAmJKdscBnrZjHRuG9Q.png" /></figure><p><em>“I’d like a line chart showing the yearly sales from 1980 until 2013 for the top 3 best-selling video game genres of all time.&quot;</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kwISHcN6MVcTCOTbAqXuZg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/939/1*YSm0rtD3vztVDbtr1F7zzQ.png" /></figure><p>The agent handled <strong>four different datasets, visualizations and queries</strong>. Furthermore, some queries utilized advanced concepts like window functions, showcasing the potencial of this project.</p><h3>Conclusion</h3><p>This <strong>SQL/BI agent</strong> showcases the power of <strong>LangGraph</strong>, <strong>Vertex AI Agent Builder</strong>, and <strong>LangChain</strong> in automating BI workflows. By leveraging RAG and state-based execution this project is capable of dynamically adapting to user queries, ensuring high-quality SQL generation and insightful visualizations.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9c497d38de53" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>