Introducing BigQuery data engineering agents

Key Concepts

• Data Engineering Agent in BigQuery
• SQL code generation
• Metadata creation
• Data pipeline creation and modification
• Schema generation and modification
• Troubleshooting assistance
• AI-powered applications
• Data quality checks
• Vertex AI integration
• Gemini model
• SQLX definitions
• Data sets
• Physical tables
• Time dimension
• AI functions
• Data quality assertions
• Conversational analytics

Data Engineering Agent Overview

The Data Engineering Agent in BigQuery is an AI-powered tool designed to enhance the productivity of data analysts and data engineers. It uses prompts to automate tasks such as creating and modifying data pipelines, generating schemas, assisting with troubleshooting, and creating metadata for AI-powered applications.

Use Case: Salesforce Data Integration

The speaker demonstrates the agent's capabilities using a real-world scenario involving Salesforce data integration. The goal is to make Salesforce support case data more accessible and insightful for business users, while also ensuring metadata is available for AI agents.

Step-by-Step Pipeline Creation

The speaker walks through the creation of an initial load pipeline in under 10 minutes, highlighting the agent's efficiency.

1. Dataset Creation: A dataset named sfdc_dataset is created in the US location using the prompt: "Create a data set called sfdc_dataset in location US." The agent generates the SQLX definition for the dataset.
2. Data Loading from GCS: The agent is instructed to load data from Parquet files in a Google Cloud Storage (GCS) bucket into physical tables. The prompt specifies the files and requests that the table names match the file names.
3. Schema Generation: The agent scans the Parquet files, creates SQLX definitions for each table, and validates them. The generated tables include all fields from the Parquet files.
4. Time Dimension Creation: A time dimension is added to facilitate reporting by quarter. The agent generates the necessary definitions.
5. Data Enrichment with AI Function: The cases table is enhanced with an AI function to filter out cases containing profanities. This is achieved using a connection to Vertex AI and a Gemini model. The agent creates a new Boolean field to indicate the presence of inappropriate terms.

Data Quality and Metadata Generation

1. Data Quality Checks: The agent is used to implement data quality checks on the user table. It detects and reverts incorrect changes made during validation. It also suggests checks for non-null fields and email address format validation.
2. Metadata Generation: The agent generates metadata for the tables to make the data available for AI agents, such as those used in conversational analytics. The generated metadata can be further enriched with business terminology and synonyms.

Data Quality Assertions

The agent generates data quality assertions, which are later converted into SQL assertion tests. The demonstration shows a data quality validation failing, indicating issues in the data.

Technical Details and Examples

• SQLX Definitions: The agent generates SQLX definitions for datasets, tables, and other data objects. These definitions are used to create and manage the data infrastructure.
• Vertex AI and Gemini: The integration with Vertex AI and the Gemini model allows for advanced data enrichment using AI functions, such as profanity detection.
• GCS Bucket: The data is initially loaded into a GCS bucket before being loaded into BigQuery tables.
• Parquet Files: The data is stored in Parquet files, which are a columnar storage format optimized for analytics.

Key Arguments and Perspectives

The speaker emphasizes the following benefits of using the Data Engineering Agent:

• Increased Productivity: The agent automates many manual tasks, allowing data engineers and analysts to do more in less time.
• Improved Data Quality: The agent helps to identify and prevent data quality issues.
• Enhanced Data Discovery: The agent generates metadata that makes data easier to find and understand.
• AI-Powered Data Enrichment: The agent integrates with AI models to enrich data with insights and intelligence.

Notable Quotes

• "The data engineering agent in BigQuery is here to help data analysts and data engineers do more in less time."
• "One thing I really like about this agent is that it gives an explanation of what it's doing and why after it finishes with each prompt."
• "This is super smart." (referring to the agent autonomously reverting incorrect changes)

Synthesis/Conclusion

The Data Engineering Agent in BigQuery is a powerful tool that can significantly improve the efficiency and effectiveness of data engineering and analytics workflows. By automating tasks such as data pipeline creation, schema generation, metadata creation, and data quality checks, the agent frees up data professionals to focus on more strategic initiatives. The integration with AI models further enhances the value of the data by enabling advanced data enrichment and insights. The demonstration shows how the agent can be used to quickly create a data pipeline for Salesforce data, enrich the data with AI functions, and ensure data quality. The agent empowers users to prepare data for both traditional analytics and AI-powered applications.