Major

Snowflake Data Quality Alert Agent: Automated Anomaly Detection and Escalation

Learn how a Snowflake data quality alert agent runs scheduled checks, explains anomalies using dbt or GitHub changes, and alerts owners in Slack.

Ritvij Gautam
Major x Snowflake logo lockup

A Snowflake data quality alert agent helps teams stay on top of data freshness, row count, and null anomalies by automating SQL-based monitoring and intelligent alerting. It connects the results of data quality checks with context from recent dbt or GitHub changes, then sends actionable alerts to Slack with the right owner tagged. This guide explains how to design, build, and operate a reliable process for automated data quality monitoring using Major, Snowflake, GitHub, Slack, and PagerDuty.

This guide covers one Snowflake process end to end. For the Snowflake API guide, connection setup and the full list of Snowflake use cases, see More Snowflake use cases.

Why This Process Breaks Today

Even teams who invest in data quality often find themselves facing four recurring issues:

  • Delayed detection: Manual monitoring or traditional dashboards catch data issues well after they cause real business problems.
  • No root cause context: Alerts rarely explain why a metric changed, forcing time-consuming investigation across dbt models or code repositories.
  • No clear owner: Alerts come with no responsible person or point of escalation, leading to finger-pointing or inaction.
  • Too much noise: Frequent redundant alerts create noise, increasing alert fatigue and slow responses to real incidents.

A Snowflake data quality alert agent, coordinated with dbt and version control metadata, tackles all these problems by joining scheduled checks, explanation, and owner routing in one workflow.

The Systems and Data Involved

Snowflake

  • Objects: Tables (TABLES), views (VIEWS), schemas, information schema queries.
  • API: Snowflake REST API, specifically the POST /queries/v1/query-request endpoint for running SQL.
  • Data points checked:
  • Row count per table
  • Null rate per critical column
  • Freshness via MAX(updated_at) or similar timestamp checks

dbt (Data Build Tool)

  • Artifacts: Manifest (manifest.json), run results (run_results.json).
  • Typical changes: Model code, seed files, schema tests, configuration.

GitHub

Slack

  • Events: Message posting to a channel (chat.postMessage API) with owner mentions.

PagerDuty (for incidents that escalate)

What is Code vs Agent Judgment vs Human Approval

Code (Deterministic)

  • Scheduled SQL checks (run via Snowflake's API): row counts, null rates, freshness age.
  • Mapping model to owner using metadata in dbt or a config file.
  • Record parsing from version control (last commit, PR meta).
  • Formatting Slack and PagerDuty payloads.

Agent Judgment (Subjective or Contextual)

  • Correlating anomalies to likely root-causes:
  • Did a dbt model or transformation change between last-pass and failure?
  • Was there a recent merge to the relevant GitHub directory?
  • Suppressing duplicate alerts for a single root-cause.
  • Deciding whether to escalate to PagerDuty based on priority and recurrence.
  • Choosing helpful context for the Slack alert (e.g., PR titles, commit links).

Human Approval

  • Explicit escalation of false positives (quick dismiss or snooze functions).
  • Confirming proposed remediations or rollback actions.

Worked Example: A Row Count Drop in the "sales" Table

Let’s walk through a real-looking incident:

Trigger: The scheduled process kicks off hourly.

  • Step 1: The app runs a SQL check against sales in the ANALYTICS schema:

SELECT COUNT(*) FROM ANALYTICS.sales WHERE sale_date >= CURRENT_DATE-1;

Result: 0 rows (previous days typically have 15,000).

  • Step 2: The app checks null rate on customer_id in the same timeframe. Null rate is 0% (as usual).
  • Step 3: The app records the failed freshness check: MAX(updated_at) was 14 hours old, but the pipeline runs every hour.

The agent now determines this is an anomaly:

  • Step 4: The agent looks into dbt and GitHub:
  • Scans dbt manifest, finds the sales model was modified 3 hours ago.
  • Cross-references with GitHub commits, matching the PR owner to @jamie via the commit's author field.
  • Reads the PR title: “Refactor joins in sales model to optimize query.”
  • Step 5: The agent generates a root-cause summary:
  • “Row count in sales dropped from 15,634 to 0. Last change: PR #1421 by @jamie, refactored joins in sales model. See commit 3e7d1c8 on GitHub.”
  • Step 6: The agent posts this in the #data-quality-alerts Slack channel, tagging @jamie and the default data lead. If no one acknowledges in Slack in 15 minutes, it escalates to PagerDuty using the POST /incidents endpoint.

How to Build It on Major

App: Scheduled SQL Checks and Logging

  • Build a Major app to run all code-driven SQL checks on a schedule.
  • Use the Snowflake connector with least-privilege credentials to only run read queries.
  • Store check run logs and results in Major’s state (and optionally a history table in Snowflake).
  • Include a process for updating and reading owner mappings and thresholds (from dbt, a YAML file, or owners.json in GitHub).

Agent: Judging Anomalies and Explaining Them

  • Build a Major agent to:
  • Analyze check results for actual anomalies using thresholds (not just any change, but significant deviations).
  • Suppress duplicate alerts for a given incident window.
  • Attribute probable root-cause by querying dbt and GitHub for recent model and code changes (using their APIs and artifacts).
  • Choose the best context (PR, commit, file, and code line) to include in the alert.
  • The agent can also recommend next steps (re-run dbt, roll back a PR) but cannot take these actions without human approval.

Workflow: Orchestration, Notification, and Human-in-the-Loop

  • Build a workflow to:
  • Orchestrate scheduled runs, agent invocation, and Slack posting.
  • Interrupt cycles to prevent multiple alerts about the same root-cause.
  • Wait for Slack message acknowledgment; if unacknowledged after X minutes, then call PagerDuty’s incident API for escalation.
  • Provide a UI for human triage, dismiss, and comment workflows.

What to Measure

  • Alert precision: % of alerts that are actionable vs noise.
  • Mean time to detection: Hours/minutes from data incident to alert.
  • Resolution time: From alert to owner acknowledgment.
  • False positive/negative rate: Use workflow feedback as labeling.
  • Incident deduplication efficiency: How often the agent prevents re-alerting for the same root-cause.

Guardrails

A quality agent needs clear limits:

  • Never re-run writes on Snowflake: All queries must be read-only. No DDL or DML.
  • Never auto-fix or roll back PRs: The agent can recommend but not execute code or model reversions.
  • Never escalate PagerDuty for non-actionable or low-priority alerts: Only escalate if thresholds and lack of acknowledgment are met.
  • Never share sensitive information in Slack: Only include minimal context, such as table name, PR number, and owner tag.
  • Always log all actions with traceable metadata: For accountability and future audits.

Frequently asked questions

How does the Snowflake data quality alert agent identify the owner to notify?

The agent maps failing models or tables to owners by checking metadata in dbt files or an owners.json/YAML file in GitHub, then attributes the alert to the owner who made the most recent relevant change.

What queries are safe for the agent to run in Snowflake?

Only read-only SQL for monitoring is allowed, such as SELECT COUNT(*), MAX(timestamp_field), and null rate checks. The agent must never perform DDL or DML operations.

How does the agent suppress duplicate alerts for the same incident?

The agent tracks root-cause signatures, such as PR, commit hash, or dbt model modification, and ensures only a single alert is posted for a given anomaly and cause within a defined window.

How does the agent pull context from dbt or GitHub about recent changes?

It reads dbt artifacts for recent model builds, then uses the GitHub API to fetch related commits or PRs, extracting authorship and message details for inclusion in alert context.

Start building on Major

Connect Snowflake, describe the process, and Major builds the app that holds its state, the agent that makes the judgment call and the workflow that runs it, with scoped access and an audit log from the first deploy.

Frequently asked questions

How do AI agents securely access Snowflake data through Major?
AI agents in Major use authenticated API calls to Snowflake, either with a signed JWT from a user key pair or OAuth. Access is scoped and audited within your enterprise controls.
Can I automate file ingestion into Snowflake with Major?
Yes, you can use deterministic apps or workflows to automate calls to Snowpipe’s insertFiles endpoint. This lets you keep your warehouse up-to-date with new data files on any event or schedule.
Does Major replace Snowflake’s built-in automation or AI features?
No, Major orchestrates Snowflake but does not replace any of its automation or built-in AI features. Major adds judgment, workflow and integrations around your warehouse.
Are there any rate limits or restrictions when using the Snowflake API in Major?
The Snowflake API enforces request frequency and concurrency limits, returning HTTP 429 errors if exceeded. Snowpipe’s insertFiles endpoint is limited to 5,000 files per request.
Who typically approves actions taken by AI agents on Snowflake data?
Approvals are handled by people in your organization, who review outputs or summaries presented by agents before any action is finalized, Major keeps humans in control.