Data quality assurance is essential to enterprise data engineering, but it can also be one of the most time-consuming parts of the development process. Every new table, transformation, and business rule must be validated before data can be trusted for reporting, analytics, and AI.
For our engineering team, an opportunity to improve this process emerged while building a modern data platform for a large wealth management organization. BBI had developed a centralized Snowflake environment to integrate data from disparate systems and support enterprise reporting, analytics, and AI. The platform incorporated approximately 400 tables across raw, curated, and business data layers, each requiring quality assurance as new development work progressed.
Engineers were spending between one and five hours validating individual tables by reviewing mapping documents, writing SQL queries, executing tests, documenting results, and preparing evidence for production approval.
When the client enabled access to Snowflake Cortex AI, BBI saw an opportunity to automate much of this repetitive work. So we developed an AI-powered QA automation agent that could streamline testing within the client’s existing Snowflake environment.
Our goal was to automate the repetitive work surrounding quality assurance while preserving the engineering judgment required to validate enterprise data.
We designed the solution using Snowflake Cortex AI and the structured engineering artifacts already available within the client’s data development process.
The agent uses metadata, source-to-target mappings, transformation rules, and business requirements to generate test cases and executable SQL, run validations against Snowflake data, and document the results. The workflow is integrated with Azure DevOps, where the team manages its testing and sprint activities.
At a high level, the workflow looked like this:
Read metadata and mapping documents
Interpret transformation logic and business requirements
Generate QA test cases
Generate SQL validation queries
Execute validation queries in Snowflake
Produce QA documentation and categorize validation results
Publish results to Azure DevOps for engineer review
Unlike a traditional chatbot interaction, every step depended on structured engineering artifacts rather than free-form prompts. The agent used information from mapping documents, metadata, transformation rules, and business requirements to guide its work.
The first challenge was providing the AI with sufficient context to understand what it was validating.
The agent ingested information from multiple sources, including:
This information established the relationships between source data, target data, and the business expectations governing each transformation.
The agent was designed to validate data across three layers of the Snowflake architecture: raw data ingested from source systems, curated data that had been cleaned and transformed, and business-level data organized for reporting and analytics. Each layer required different types of validation, from structural and completeness checks to more complex business calculations and relationships across tables.
Once the transformation logic was understood, the agent generated a comprehensive set of validation scenarios.
These included:
Engineers reviewed and refined the generated test cases as part of the validation process.
As time went on, we saw that the AI frequently proposed validation scenarios beyond those initially identified through manual planning, which increased overall test coverage.
The increase in test coverage was substantial. For some tables, the agent expanded an initial set of approximately 20 manually identified test cases to 40 or 50. For more complex curated tables, it generated approximately 60 to 70 scenarios, including checks engineers might otherwise have overlooked.
Writing validation SQL consumed a significant portion of the original QA effort.
The AI agent generated SQL queries directly from the transformation logic and expected outcomes, producing executable statements designed to validate data within Snowflake.
Instead of manually creating dozens of validation statements, engineers reviewed generated SQL, verified the logic, and focused our attention on exceptions.
This shifted the engineer’s role from manually writing every validation query to reviewing AI-generated logic and investigating potential discrepancies.
The validation queries were executed against the target Snowflake environment.
The agent compared expected and actual results, identified discrepancies, and organized the findings into a consistent QA package.
Results were categorized as pass, fail, warning, or informational. This distinction was important because some findings, such as certain relationship or threshold conditions, required additional investigation without necessarily indicating a data quality failure.
Rather than requiring engineers to manually consolidate evidence, the workflow produced structured output ready for review.
The agent was designed for use across development, QA, user acceptance testing, and production environments, supporting a consistent validation process throughout the data engineering lifecycle.
Documentation is one of the least technical but most time-consuming aspects of enterprise QA.
The agent assembled:
The information was then published into the team’s existing workflow in Azure DevOps for engineering review.
The standardized output reduced the manual effort required to prepare testing evidence and provided a consistent record of validation activity across the team’s development sprints.
Throughout the project, the team counted on humans, not AI, to approve all production releases.
Engineers reviewed generated SQL, evaluated test results, investigated unexpected findings, and determined whether each transformation satisfied the business requirements.
This proved especially important when evaluating complex business logic or identifying AI hallucinations. The agent accelerated preparation, but engineering judgment was essential throughout the review process.
Engineers also verified results against the underlying Snowflake data before providing final sign-off. This helped identify potential false positives, inaccurate test logic, or other errors introduced by the AI-generated output.
The agent is now incorporated into the team’s regular sprint workflow, delivering measurable improvements in QA productivity and testing coverage.
Broader test coverage: AI-generated validation scenarios increased the number of checks performed, including additional edge cases, business rules, and transformation validations.
Several engineering lessons emerged during the project.
The quality of generated SQL and test cases depended on the quality of metadata, mapping documents, and business requirements provided to the agent.
Quality assurance follows a repeatable workflow with well-defined inputs and outputs. Those characteristics made it an excellent candidate for AI-assisted automation.
The combination of AI-generated artifacts and engineering review produced stronger outcomes than either approach independently.
Though the initial objective was to reduce manual effort, the AI expanded the range of validation scenarios considered during testing, strengthening overall QA integrity.
By leveraging Snowflake Cortex AI within the client’s established data environment, BBI was able to extend the capabilities of the platform and integrate AI automation into an existing engineering workflow. The solution built on the team’s knowledge of Snowflake architecture, data transformations, and quality assurance requirements.
Although this implementation focused on data quality assurance, the architectural pattern extends to other areas of enterprise data engineering.
Processes built around structured metadata, defined business rules, and repeatable workflows present similar opportunities for AI-assisted automation, including:
This project demonstrates how Snowflake Cortex AI can be applied to an established engineering process to improve productivity, strengthen data quality, and increase development capacity. It also provides a model for how BBI can apply AI-driven automation to other data engineering activities, helping clients gain greater value from their existing technology investments.