Skip to content

Find Broken Views in Snowflake

Supademo

In this example we create a DataMate called "Sprint Work Agent" that integrates Snowflake, dbt, and Jira into GitHub Copilot to find empty Snowflake views, their dbt models and filing a ticket to JIRA all within the VS Code IDE.

Walkthrough

The process begins by configuring DataMates to integrate with these tools. Once set up, a user can prompt GitHub Copilot to perform a series of actions. In the demo, the prompt asks Copilot to:

Query Snowflake tables in a specified schema (JAFFLE_CASTLE_20250521.DEV) to identify empty views in the mart layer. Find the associated dbt model if an empty view is found. Determine the root cause of the empty tables. Create a Jira ticket in the "AI" project, categorizing the issue as a "Task".

GitHub Copilot then outlines its steps to address the request:

  • List available Snowflake connections.
  • Query the mart layer views.
  • Check for empty tables.
  • Review corresponding dbt models.
  • Create Jira tickets for any issues discovered.

During this process, DataMate provides GitHub Copilot with the necessary tools to connect to Snowflake and execute queries. Copilot successfully identifies that the MART_STORE_LIFETIME_VALUE view in Snowflake is empty. It then investigates the corresponding dbt model (mart_store_lifetime_value.sql) and discovers that a WHERE store_id IS NULL condition in a CTE (store_orders) is filtering out all records, noting that this was a test condition left in the code.

Finally, Copilot utilizes DataMate's Jira "Create Issue Tool" to file a ticket for this issue. In essence, the demonstration highlights how AI agents, powered by DataMates, can automate complex data engineering tasks, from identifying issues in a data warehouse (Snowflake) and debugging code (dbt) to creating project management tickets (Jira), all within a single integrated development environment (VS Code).