loggie
Start free

Snowflake · Workflow guide

Snowflake data quality checks with an AI agent

Have your AI agent run reviewed Snowflake quality checks through Loggie and report failures with reproducible queries, under a SELECT-only role.

Who this is for: Data teams that want an agent to explain a small set of quality checks without granting unrestricted warehouse access.

Before you start

  • An active Snowflake account and SQL API connection in Loggie, with a usable warehouse and an appropriately restricted Snowflake role.
  • A Snowflake role limited to SELECT on approved views, plus a Loggie grant that allows the statements endpoint. Loggie classifies statement submission as a write, so the role is what keeps the workflow read-only.
  • Reviewed check queries, explicit row limits, statement timeouts, cost controls, and data-owner-approved report fields.

In your Loggie workspace, connect Snowflake, create an agent identity, and review its access. On the agent’s machine, use Node.js 20 or later and initialize the CLI:

npm install -g @loggie-ai/cli
loggie init
loggie discover

Enter the agent’s Loggie key through the initialization prompt. Keep provider credentials in the connection setup, not in the agent prompt. Use the connection slug returned by discovery in the examples below.

Step-by-step workflow

  1. Start with reviewed checks over approved views

    Give the agent a few named checks rather than an invitation to explore the warehouse. Useful checks include a missing daily partition, an unexpected null rate, or a duplicate key in a curated view. Record the expected freshness and the business owner for each check.

    Prefer aggregate results: counts, dates, and check outcomes. There is usually no need to return individual user records just to show that a daily table is late. Review the view definitions and permitted output before enabling the workflow.

  2. Prove access and constrain statement execution

    Connect Snowflake in Loggie and initialize the agent’s CLI. Discover the connection slug and SQL statement endpoint. The example uses snowflake; replace it if your workspace uses another name.

    First run a small SELECT to verify authentication, warehouse availability, and role context. Statement submission is a POST that can execute SQL, including writes if the provider role permits them. Enforce read-only data access in Snowflake itself, then apply compatible Loggie restrictions and reviewed queries.

    loggie discover snowflake --endpoint /api/v2/statements --method POST
loggie call snowflake POST /api/v2/statements --body '{"statement":"SELECT 1 AS connection_ok","timeout":10}' --pretty

  3. Run bounded checks and inspect completion

    Use the reviewed queries against the approved database, schema, views, role, and warehouse. Set these contexts explicitly where defaults are not guaranteed. A typical check returns a date and a count, not a large raw export.

    An accepted asynchronous statement is not a completed query. Inspect the response status and statement handle, poll the status endpoint until completion or a bounded timeout, and retrieve every required result partition. Failed or partial execution means the check is unknown, not passed.

  4. Return exceptions with reproducible evidence

    For each failed expectation, include the check name, observed value, expected threshold, query or approved query identifier, execution time, and owner. Keep a freshness failure separate from a query failure so someone can fix the right system.

    Run the checks through your agent’s scheduler within the agreed warehouse budget. Store the previous results to highlight new failures and recoveries. Changes to tables, grants, roles, or pipelines require a separate reviewed action; the reporting workflow should never repair data by guessing.

Snowflake agent prompt

Adapt this prompt to your connected account and the scope you have approved.

Execute only the approved Snowflake quality checks with the restricted provider role and agreed query context. Apply row limits and timeouts. Wait for statement completion and retrieve required partitions. Return failed expectations with observed and expected values and a reproducible query reference. Mark failed or incomplete queries unknown. Do not alter data, schemas, roles, grants, or pipelines.

Example Snowflake report

Example output with invented data.

Data quality: 6 checks, 2 failed, 1 unknown

Failed   orders_daily freshness
  Latest order_date is 2 days old, expected yesterday
  Query: dq_freshness_orders, ran 07:05 UTC
  Next: data owner checks the overnight load
Failed   customers_v unique key
  14 duplicate customer_id values
  Next: owner inspects the dedupe step
Unknown  events null rate
  Query timed out after 60 s. Not counted as passed.

No data, schemas, or grants changed.

Success checks

  • Verify the first SELECT succeeds with the intended role and warehouse before scheduling any checks.
  • Confirm the Snowflake role cannot modify data or privileges, even though the SQL API submission route is enabled.
  • Test an asynchronous completion, a query failure, and a multi-part result so none is mistaken for a passing check.

Pitfalls

  • A Loggie endpoint grant is not a SQL parser or a substitute for Snowflake role restrictions.
  • A suspended account or unavailable warehouse is an access failure, not evidence that the dataset is empty.
  • Warehouse execution can incur cost. Keep query scope and scheduling within explicit operational limits.

Sources