Snowflake data freshness check with SQL, plus alerts

Connect Snowflake

Add a connector of type Snowflake. The fields are account, user, private key, warehouse, database, schema and role; the Snowflake connector docs list them one by one. Two things differ from a typical database connection.

First, authentication is key-pair only. arcodash calls Snowflake’s SQL API, and that API doesn’t take a plain password, so the password field on the form does nothing. Generate a PKCS#8 key and register the public half on the user, as described in Snowflake’s key-pair guide:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Paste the contents of rsa_key.p8 into the private key field. The field expects an unencrypted PEM, so don’t add a passphrase.

Second, the warehouse is yours to choose, and it’s the line item that matters. Make a role and user that can do nothing but read, and give them their own warehouse:

CREATE WAREHOUSE arcodash_wh WITH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE ROLE arcodash_ro;
GRANT USAGE ON WAREHOUSE arcodash_wh TO ROLE arcodash_ro;
GRANT USAGE ON DATABASE analytics TO ROLE arcodash_ro;
GRANT USAGE ON SCHEMA analytics.public TO ROLE arcodash_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.public TO ROLE arcodash_ro;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.public TO ROLE arcodash_ro;

CREATE USER arcodash_svc
  TYPE = SERVICE
  DEFAULT_ROLE = arcodash_ro
  DEFAULT_WAREHOUSE = arcodash_wh
  RSA_PUBLIC_KEY = '<contents of rsa_key.pub, without the BEGIN/END lines>';
GRANT ROLE arcodash_ro TO USER arcodash_svc;

Set the same names in the arcodash form: warehouse arcodash_wh, role arcodash_ro, plus the database and schema. The database matters beyond queries, because schema browsing in arcodash needs one set. For the account field, use the account identifier from your Snowflake URL. A value like xy12345.us-east-1 works; arcodash uses the part before the first dot for the key-pair token.

Network access is the usual snag. arcodash Cloud doesn’t publish fixed outbound IP addresses, so an account-level network policy with an ALLOWED_IP_LIST (docs) will block it. Snowflake blocks everything not on the list, and there’s no single address of ours to add. Decide that with your security team before you start.

A snowflake data freshness check

A check is a scheduled query that returns one numeric row; the monitor reads that row’s column and compares it with your threshold (alerts overview). For freshness, return the lag in seconds:

SELECT COALESCE(
         DATEDIFF(second, MAX(loaded_at), SYSDATE()),
         999999999) AS seconds_since_last_row
FROM analytics.public.orders;

Monitor seconds_since_last_row with > and 7200 for a two hour limit. The COALESCE makes an empty table read as very stale instead of NULL, which no comparison ever matches.

The function choice is about time zones. CURRENT_TIMESTAMP() returns TIMESTAMP_LTZ in the session’s time zone, while SYSDATE() returns a UTC TIMESTAMP_NTZ (docs). If your loaded_at column is TIMESTAMP_NTZ written in UTC, as most pipelines do, SYSDATE() is the match. If it’s TIMESTAMP_LTZ, use CURRENT_TIMESTAMP(). Get this wrong and the lag is off by your UTC offset, which looks like a pipeline problem and isn’t.

DATEDIFF counts the boundaries it crosses, so measure in seconds and compare in seconds; a minute-level difference of 1 can be one second apart.

Keep scheduled checks cheap

Snowflake bills compute by warehouse uptime, not by the rows a query touches, and a check is mostly a tiny query on a warehouse that has to be awake. Three facts from Snowflake’s docs drive the setup.

1. Auto-suspend defaults to 600 seconds (CREATE WAREHOUSE). A check every 5 minutes arrives before the warehouse can suspend, so it never does. An X-Small warehouse uses 1 credit per hour (warehouses overview), which is 24 credits a day for a query that runs in a fraction of a second.
2. Each resume bills at least 60 seconds, and a background process checks suspension about every 30 seconds, so settings under 30 don’t behave precisely. AUTO_SUSPEND = 60 is the practical floor.
3. Size doesn’t help. Credits double with each size. A freshness query doesn’t need more than X-Small.

So give the checks their own X-Small warehouse with AUTO_SUSPEND = 60, as in the setup above, and schedule them every 15 to 60 minutes. Back of the envelope for a 15 minute cadence: 96 wake-ups a day, each billed for at least a minute, is at least 96 minutes of X-Small, about 1.6 credits. Several checks scheduled in the same slot share one wake-up, so line them up. Don’t point checks at your BI or transformation warehouse; they’ll keep it awake, and you’ll lose the ability to see what they cost.

Reading metadata instead of scanning a table is the other lever, with a catch. INFORMATION_SCHEMA.TABLES has ROW_COUNT and LAST_ALTERED:

SELECT DATEDIFF(second, last_altered, CURRENT_TIMESTAMP()) AS seconds_since_alter
FROM analytics.information_schema.tables
WHERE table_schema = 'PUBLIC' AND table_name = 'ORDERS';

Snowflake documents LAST_ALTERED as updated by DML, DDL and background metadata maintenance, and “even when no rows are affected by the DML statement.” An empty INSERT or an internal reorganisation moves it. Treat it as a hint that something touched the table, not proof that new data landed; a MAX(loaded_at) is the honest answer.

The ACCOUNT_USAGE views in the SNOWFLAKE database are the wrong place for freshness. Snowflake documents their latency at 45 minutes to 3 hours, “for most of the views” 2 hours, and INFORMATION_SCHEMA has none (docs). Use ACCOUNT_USAGE for a daily cost or login report, where a two hour lag is fine.

A volume check and a metric alert

SELECT COUNT(*) AS rows_last_hour
FROM analytics.public.orders
WHERE created_at >= DATEADD(hour, -1, SYSDATE());

Monitor rows_last_hour with < and a floor set from your quietest hour. The row count anomaly post covers picking thresholds.

For a rate, such as failed payments:

SELECT 100.0 * AVG(CASE WHEN status = 'failed' THEN 1.0 ELSE 0.0 END) AS failed_pct
FROM analytics.public.payments
WHERE created_at >= DATEADD(hour, -1, SYSDATE());

Monitor failed_pct with > and 5. Schedule each query from scheduled refresh; a schedule runs the published version, and pauses itself after 10 consecutive failures, which will happen if the key is rotated and nobody tells arcodash.

To generate freshness, row count, null and duplicate checks for a Snowflake table, use the Snowflake data check generator.

Dashboards on Snowflake

A dashboard is saved queries as tiles (building dashboards). Dashboard queries run on the same warehouse, so every open dashboard costs compute; aggregate in SQL and keep the result small.

Daily active accounts, last 30 days:

SELECT DATE_TRUNC('day', event_at) AS day, COUNT(DISTINCT account_id) AS active_accounts
FROM analytics.public.events
WHERE event_at >= DATEADD(day, -30, SYSDATE())
GROUP BY DATE_TRUNC('day', event_at)
ORDER BY day;

Revenue by plan, this month, as a bar chart:

SELECT plan, SUM(amount) AS revenue
FROM analytics.public.invoices
WHERE paid_at >= DATE_TRUNC('month', SYSDATE())
GROUP BY plan
ORDER BY revenue DESC;

A freshness tile is the check query itself, shown as a counter next to the dashboard it protects, so a stale chart comes with an explanation. The data quality monitors page has the other recipes: null rate and duplicates.

Databricks and BigQuery have similar cost trade-offs for scheduled checks.

docs

Snowflake setup in the docs

Connection fields, an example configuration and caveats.