Snowflake Basic integrations and automation

Snowflake holds the numbers other teams want but rarely open the warehouse to get: product usage per account, health scores, billing totals. Automations here are usually a query on a schedule whose results are pushed to the tool people already use. The Snowflake SQL API accepts SQL statements over REST and returns result sets, with large results split into partitions you fetch in turn. Statements run on a virtual warehouse, under a role, against a database and schema, all of which can be set on the request or defaulted from the user.

The catalogue template here runs every morning: it queries Snowflake for accounts whose usage or health score moved more than 10 percent in a day, updates the matching Apollo account with the new score, adds a note describing the change unless an existing note already covers it, and posts the five biggest movers to a sales Slack channel. The threshold logic lives in SQL, so the same data produces the same list, and the note check keeps reruns from stacking duplicate notes.

Cost is the gotcha people meet first. Running a query resumes the warehouse if it is suspended, and Snowflake bills compute while it runs, plus its minimum billing time on resume. A small query every five minutes can keep a warehouse awake all day. Use a small warehouse with a short auto-suspend for automation, aggregate in SQL rather than pulling raw rows, and schedule only as often as the data actually changes; a table loaded nightly does not need hourly checks.

Create a dedicated user and role that can read only the views the workflow needs. Snowflake documents key-pair authentication and OAuth for the SQL API, which avoids storing a person’s password. If the query writes back, for example logging what was sent, grant insert on that one table only. Snowflake must be reachable from the public internet for a hosted runtime to connect; accounts restricted by network policies need the runtime’s access allowed.