Redshift data freshness check and dashboards

Connect Redshift

Add a Redshift connector with host (the cluster or workgroup endpoint), port (5439 by default), user, password, database name and SSL mode. Connectors overview covers the form and the connection test. Underneath, arcodash reuses its PostgreSQL driver for Redshift, which is why the fields match.

Create a dedicated user and grant it only reads. Statements from the Redshift GRANT and ALTER DEFAULT PRIVILEGES docs:

CREATE USER arcodash_ro PASSWORD 'Change-me-1234';
GRANT USAGE ON SCHEMA analytics TO arcodash_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO arcodash_ro;
ALTER DEFAULT PRIVILEGES FOR USER etl_user IN SCHEMA analytics
  GRANT SELECT ON TABLES TO arcodash_ro;

The ALTER DEFAULT PRIVILEGES line is the one people skip. It applies only to tables that etl_user creates later, so name the user your loader runs as. Existing tables need the GRANT SELECT above.

Network: arcodash Cloud does not publish fixed outbound IP addresses, and your cluster must be reachable from the internet. In practice that means a publicly accessible cluster or workgroup with a security group rule for port 5439 that is broader than one address. Weigh that before you connect a production warehouse.

SSL: the connector offers disable, prefer (default) and require. Redshift clusters accept non-SSL connections unless the require_SSL parameter is true in the cluster’s parameter group, per the AWS SSL docs, so set require on the arcodash side too.

A Redshift data freshness check

For the idea behind this and the Postgres version, see data freshness and the Postgres freshness post. The Redshift version swaps in GETDATE(), which returns the current time as a TIMESTAMP in the session time zone (UTC by default), and DATEDIFF:

SELECT COALESCE(DATEDIFF(second, MAX(loaded_at), GETDATE()), 31536000)
       AS lag_seconds
FROM analytics.events;

Point a monitor at lag_seconds with > and a threshold such as 7200. A monitor reads only the first row and needs a number, so the COALESCE matters: an empty table gives NULL, and the monitor would sit in the unknown state instead of firing. Schedule the query to match your load cadence (see scheduled refresh).

One trap: DATEDIFF counts date-part boundaries crossed, not elapsed time. Between 10:59:59 and 11:00:01 it returns 1 for hour. Using second keeps the error to a second; do not use hour for a threshold you care about.

A load volume check

A freshness check passes if one stray row arrives. Check how many rows the last hour brought:

SELECT COUNT(*) AS rows_last_hour
FROM analytics.events
WHERE loaded_at >= DATEADD(hour, -1, GETDATE());

DATEADD takes a negative integer to subtract. Alert on rows_last_hour with < and a floor. For a table whose volume swings, use a baseline monitor (alerts overview) that learns the level instead; pattern in row count anomaly SQL.

A metric alert

A failure rate over the last hour, with the conditional count written as SUM(CASE ...):

SELECT COALESCE(
  ROUND(100.0 * SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0), 1), 0) AS failed_pct
FROM analytics.payments
WHERE created_at >= DATEADD(hour, -1, GETDATE());

Alert on failed_pct > 5. Routing options are in alert to Slack with SQL.

Gotchas when monitoring Redshift

• Loads are batches. COPY from S3 lands in chunks, so a freshness threshold below your batch interval will fire between healthy loads. Set it to the interval plus slack.
• Wide scans cost cluster time. Filter on the sort key or a timestamp column the table is sorted by. A check that scans the whole table every five minutes competes with your loads and reports.
• tbl_rows includes deleted rows. In SVV_TABLE_INFO, the row count includes rows marked for deletion but not yet vacuumed. For a trustworthy volume check, count with a query; use the view for table-health tiles.
• Timestamps have no zone. GETDATE() is UTC by default and TIMESTAMP columns carry no zone. If the loader writes local time, your lag is off by the offset.
• System views need privileges. SVV_TABLE_INFO is visible only to superusers unless you grant SELECT on it to the user.

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

What a dashboard on Redshift looks like

A daily revenue line, using TRUNC to turn a timestamp into a date (the GETDATE docs show it on a timestamp):

SELECT TRUNC(created_at) AS day, SUM(amount) AS paid
FROM analytics.payments
WHERE status = 'paid' AND created_at >= DATEADD(day, -30, GETDATE())
GROUP BY 1 ORDER BY 1;

Then a table-health table, once you have granted the view:

SELECT "schema", "table", tbl_rows, unsorted, stats_off
FROM svv_table_info
ORDER BY tbl_rows DESC
LIMIT 10;

table is a reserved word, so it is double-quoted. A high unsorted or stats_off means a VACUUM or ANALYZE is due, and either can become a monitor on its own column.

The connector page can also suggest checks with AI from the schema, using your own provider key. Free for 14 days, no card, then from $59 a month with unlimited seats.

docs

Redshift setup in the docs

Connection fields, an example configuration and caveats.