SQL Server data freshness check and alerts

Connect SQL Server

In arcodash, add a connector of type SQL Server and fill in the host, port (1433 by default), database, login and password. The field-by-field list is in the SQL Server connector docs. Two options matter more than they look:

• Encrypt takes off, optional or required, and defaults to required. Leave it there. If your server presents a self-signed certificate the connection will fail until you tick trust server certificate, which skips certificate validation. Use that for a known internal server, not as a habit.
• Authentication is a SQL login and password. Windows authentication isn’t offered.

arcodash is hosted and doesn’t publish a fixed set of outbound IP addresses, so the server has to be reachable from the internet with TLS on. If your firewall only admits listed addresses, the troubleshooting page covers the options. Connecting has a 5 second timeout for the network hop and another 5 seconds for the login, so an unreachable host fails fast instead of hanging the form.

arcodash runs the SQL you give it as written. The login’s permissions are the only guard, so make it read-only:

CREATE LOGIN arcodash_ro WITH PASSWORD = '<a long random password>';
GO
USE appdb;
GO
CREATE USER arcodash_ro FOR LOGIN arcodash_ro;
ALTER ROLE db_datareader ADD MEMBER arcodash_ro;
ALTER ROLE db_denydatawriter ADD MEMBER arcodash_ro;
GRANT VIEW DATABASE STATE TO arcodash_ro;

db_datareader reads every user table and view. db_denydatawriter is a second lock in case someone later widens the first. VIEW DATABASE STATE is only for the metadata check further down: sys.dm_db_partition_stats requires it plus VIEW DEFINITION, and SQL Server 2022 and later ask for VIEW DATABASE PERFORMANCE STATE and VIEW SECURITY DEFINITION instead (docs). Skip the grant if you only run queries against your own tables.

A freshness check in T-SQL

A check in arcodash is a scheduled query that returns one numeric row. The monitor reads the first row, takes the column you name, and compares it with an operator and a value; see alerts overview for the exact semantics. So a freshness check should always return a row, even when the table is empty:

SELECT ISNULL(
         DATEDIFF(second, MAX(loaded_at), SYSUTCDATETIME()),
         2147483647) AS seconds_since_last_row
FROM dbo.orders;

Point a monitor at seconds_since_last_row with > and 7200 for “nothing in two hours”. The ISNULL is the empty-table guard: MAX of no rows is NULL, DATEDIFF of NULL is NULL, and a comparison against NULL never fires. The big integer is the largest value DATEDIFF returns, so an empty table looks as stale as it can.

DATEDIFF counts boundaries crossed, not elapsed time. Two timestamps a hundredth of a second apart can differ by one second, minute or hour if they straddle the line. At second granularity that’s noise, so measure in seconds and set the threshold in seconds. The return type is int, which holds about 68 years of seconds; use DATEDIFF_BIG only if you ever measure in finer units.

A volume check

Freshness says something arrived. Volume says enough arrived:

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

Monitor rows_last_hour with < and a floor that matches your quietest hour, not your average. If volume has a daily shape, a threshold-free baseline monitor is easier than picking a floor; the docs explain how it learns in alerts overview.

On a very large table you can read the engine’s own row counts instead of counting:

SELECT SUM(row_count) AS approx_rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.orders')
  AND index_id IN (0, 1);

Microsoft documents row_count as approximate, so use this for “did the table suddenly lose half its rows”, not for reconciliation.

One metric alert

Alert on a rate, because a count rises with traffic and a rate doesn’t:

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

Monitor failed_pct with > and 5. An empty hour makes AVG return NULL, which stays quiet. That’s the right outcome for a payment-failure rate, and the volume check above is what catches the empty hour.

Put each query on a schedule from the scheduled refresh panel: every 5, 15 or 30 minutes, hourly, daily, or a cron. A schedule runs the published version of the query, so publish it first. After 10 consecutive failures arcodash pauses the schedule instead of retrying forever, which is what you want if a password rotation breaks the login.

Gotchas that bite SQL Server checks

• Local time. GETDATE() and SYSDATETIME() are server-local. SYSUTCDATETIME() and GETUTCDATE() are UTC. Match the function to how the column is written.
• No LIMIT. T-SQL uses TOP. If a query can return a long list, write SELECT TOP (20) ... yourself, because arcodash doesn’t append a limit to SQL Server queries. The connector fails a query that returns more than 10,000 rows rather than truncating it, so narrow it. A multi-statement batch returns the result of its last statement.
• Locks. A check that scans a table another job is rewriting can wait on that job. When the connector’s query timeout hits, the run fails, and ten failures in a row pause the schedule. Check the lock before you blame the monitor.
• Collation. String comparisons follow the column’s collation, so status = 'failed' may or may not match 'Failed'.
• Date types. datetimeoffset values reach arcodash as UTC timestamps. datetime has no zone at all, so the zone is whatever your pipeline wrote.

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

Dashboard tiles on SQL Server

A dashboard is saved queries laid out as tiles; the building dashboards page covers layout. Three that work well on a T-SQL database:

Orders per hour, last day, for a line chart:

SELECT DATEADD(hour, DATEDIFF(hour, 0, created_at), 0) AS hour_start,
       COUNT(*) AS orders
FROM dbo.orders
WHERE created_at >= DATEADD(day, -1, SYSUTCDATETIME())
GROUP BY DATEADD(hour, DATEDIFF(hour, 0, created_at), 0)
ORDER BY hour_start;

Revenue by day for the last 30 days, for a bar chart:

SELECT CAST(created_at AS date) AS day, SUM(amount) AS revenue
FROM dbo.orders
WHERE created_at >= DATEADD(day, -30, SYSUTCDATETIME())
GROUP BY CAST(created_at AS date)
ORDER BY day;

Top error codes today, for a table tile:

SELECT TOP (10) error_code, COUNT(*) AS occurrences
FROM dbo.job_errors
WHERE logged_at >= DATEADD(day, -1, SYSUTCDATETIME())
GROUP BY error_code
ORDER BY occurrences DESC;

If you’d rather not hand-write checks for every table, the connector page has a Data quality checks card that drafts them from your schema with your own AI key. Read data quality monitors for what it sends first. Either way, the end result is the same plain query and monitor described above.

docs

SQL Server setup in the docs

Connection fields, an example configuration and caveats.