Major

Snowflake Automation: Build Stateful, Governed Agent Workflows

Snowflake automation goes beyond scheduled SQL. See how to build a governed agent workflow that stores durable results and reserves the model for judgment.

Jose GironUpdated
Snowflake Automation: Build Stateful, Governed Agent Workflows

Snowflake automation covers three jobs: scheduled SQL, data movement, and the work that happens after a query returns. Snowflake Tasks handle the first, Openflow handles the second, and neither decides which accounts need a call or remembers what was flagged last week. That third job is where an agent helps, and where most teams end up re-running a model on work that should be code.

Major is the enterprise platform where agents build the software they run on. For Snowflake, that means an agent works out the query, the snapshot schema, and the routing once, builds them as a governed app, and runs that app every week. The model comes back only for judgment calls. This guide is for ops, RevOps, and data teams who already have a warehouse report someone reads and acts on by hand.

What Snowflake automates natively, and where it stops

Tasks run SQL or procedures on a cadence. Openflow moves data in from outside systems and handles staging and transformation. Cortex Code and CoWork run saved prompts on a schedule against your data.

Their limits are specific. Cortex Code Desktop automations are stored on the current machine and OS user, and they need the app open and the computer awake. CoWork automations run no more often than hourly and deliver by email. Every run starts from a saved prompt, so the history an operator needs has to live somewhere you control.

Use a Task when the job is stable SQL on a schedule. Add an agent when the output needs interpretation or the next step lives in another system.

Four Snowflake workflows worth an agent

  • Executive briefing. A weekly query reads one metrics view. Code stores the snapshot and delivers it. The model writes two paragraphs on what moved.
  • Churn-risk review. A cohort query scores accounts on usage and support signals. The agent triages the accounts whose signals changed most and hands each to an owner. This is the worked example below.
  • Data quality escalation. A failed task becomes an incident record with an owner, a timestamp, and the failing object. The agent sets severity and drafts the note. Code writes the record.
  • Cohort analysis requests. A person asks a question in plain language. The agent writes a bounded, read-only query against approved views, and a human checks the result. This is the least repeatable pattern, so the model does the most work here.

The Snowflake SQL API in brief

Submit a statement with POST /api/v2/statements. Add async=true for asynchronous execution. The body carries statement plus optional warehouse, database, schema, role, timeout, and bindings. An unfinished statement returns a 202 and a statementHandle. Poll GET /api/v2/statements/{statementHandle}: 200 means results, 422 means the statement failed, and 202 or 429 means keep waiting. Large results arrive in partitions via ?partition=N, and /api/v2/statements/{statementHandle}/cancel stops a run.

Authentication options include key-pair JWT, OAuth, programmatic access tokens, and workload identity federation, signaled with the X-Snowflake-Authorization-Token-Type header.

The upside: full SQL, every statement under a role and warehouse you already govern, and bindings keep inputs out of the statement text. The downsides are concrete. You mint and rotate tokens, and key-pair JWTs are short-lived. Async means you own the polling loop. A running warehouse consumes compute, so a careless schedule costs money. And a query returns rows with no memory. Nothing in the API remembers what you saw last week.

Build the Snowflake churn-risk agent on Major

This is an illustrative design, not a customer deployment, and it carries no measured results.

  • Connectors. Snowflake for the read-only query, Salesforce for the review task, Slack for the alert. Each is optional until access is approved.
  • The app. A Major app holds a managed database with the statementHandle, run timestamp, input cohort, and a result snapshot for every run. State lives in rows, not in a context window.
  • The deterministic part. The query, the snapshot write, the freshness check, and the routing run as code on every run.
  • The agent. It reads only the outliers, accounts whose signals moved unusually, and decides which need an owner's attention. Ambiguous movement goes to human review instead of becoming a task.
  • The trigger. A weekly workflow fires after the warehouse refresh, with a manual rerun for the workflow owner.
  • Governance. The Snowflake credential gets SELECT on one view and one warehouse, with no writes to source data. The Salesforce credential can create tasks and nothing else. Slack is limited to one channel. Every action is logged, and the stored snapshot plus statementHandle trace any alert to the exact query behind it. A person approves any account action.

The app handles four failure modes by name. A stale result is labeled stale instead of posted as current. A 422 is logged with its handle and the owner is notified, and the agent never summarizes a failed query. A missing grant fails loudly and the app records which one. Sensitive columns are masked in the view before data reaches anyone.

The agent builds this app once. Next week's run executes the same code and spends model tokens only on interpretation, which is why token cost is front-loaded, then flatter. The saved snapshots are durable state for agents, and the app layer is what makes the work repeatable. Build this on Major.

Does this replace Snowflake Tasks or Openflow?

No. A Task is a good home for stable SQL and Openflow is a good home for ingestion. The Major app sits beside them and holds the state, handoffs, and audit trail they were never designed to carry. This guide skips Tasks and Streams syntax, Openflow setup, and warehouse sizing.

Start building on Major

Take the weekly warehouse report someone still reads and acts on by hand, and let an agent build the Snowflake churn-risk briefing app once. Reason once, run forever.

Related articles

Frequently asked questions

is snowflake hard to learn?
Snowflake is approachable if you already know SQL. The harder parts are warehouse sizing, roles and grants, and choosing between Tasks, pipelines, and API-driven automation. Start with SQL basics, then learn roles and warehouses, then schedule one simple Task before touching the SQL API.
how do you automate queries in snowflake?
Use a Snowflake Task for stable SQL on a schedule, with a dedicated role and warehouse. For orchestration across other systems, submit statements through the SQL API and poll by statement handle. Either way, monitor failures and store each result with its run time and query ID.
can ai agents query snowflake data?
Yes, through scoped credentials tied to a role that can read only approved views. Default to read-only access, mask sensitive columns, and save each query ID with its result. Keep a human reviewer in the loop for any consequential action the agent proposes from the data.
what is the difference between snowflake tasks and an ai agent?
A Task runs a defined SQL statement or procedure on a schedule and does the same thing every time. An agent makes contextual decisions and calls tools in other systems. Repeatable steps belong in code or an app, with the agent reserved for judgment.