What is data observability? A SQL-first definition

What it covers

The word comes from software, where observability means you can tell what a system is doing from its outputs. Applied to data, the system is your tables and the outputs are measurements about them. Three measurements cover most real incidents:

• Freshness. How old is the newest row? Catches pipelines that stopped.
• Volume. Did roughly the usual number of rows arrive? Catches partial loads, dropped partitions, and upstream outages that still “succeed”.
• Validity. Are the values what you expect: null rates, ranges, accepted values, duplicate keys? Catches schema changes and bad joins.

Anything more advanced, such as lineage or anomaly models, builds on these. If these three are not covered, the advanced parts are not the bottleneck.

Why dashboards need it

A dashboard cannot tell you its own inputs are wrong. It renders whatever the table holds, with the same confident axis labels, whether the data is fresh or three days old. The first person to notice is usually a stakeholder reading a number in a meeting, which is the most expensive place to find out.

Checks move that moment earlier. The failure still happens, but the owner hears about it from an alert instead of from a screenshot with a question mark on it.

A volume check in SQL

Compare yesterday’s row count with the average of the days before it, and return a row only when it drops below half.

WITH daily AS (
  SELECT created_at::date AS day, count(*) AS n
  FROM payments
  WHERE created_at >= current_date - 8
    AND created_at <  current_date
  GROUP BY 1
)
SELECT day, n,
       round((SELECT avg(n) FROM daily
              WHERE day < current_date - 1)) AS typical
FROM daily
WHERE day = current_date - 1
  AND n < 0.5 * (SELECT avg(n) FROM daily
                  WHERE day < current_date - 1);

Half is a starting point. A table with a strong weekly pattern needs the comparison to use the same weekday, or the Monday after a quiet Sunday will fire every week. The row count anomaly post covers that.

A validity check in SQL

Alert when more than a fifth of recent rows have a null in a column that should always be filled.

SELECT 'payments' AS tbl,
       round(100.0 * count(*) FILTER (WHERE amount IS NULL)
             / count(*), 1) AS null_pct
FROM payments
WHERE created_at >= current_date - 1
HAVING count(*) FILTER (WHERE amount IS NULL) > 0.2 * count(*);

Running the checks

Each check above returns zero rows when things are fine and one or more rows when they are not. With a generic scheduler, that shape is simple: run the query every few minutes or hours and alert whenever the result is not empty. arcodash works differently: a monitor reads one numeric column from the first row and compares it with a condition, and an empty result leaves it unknown. So drop the final WHERE or HAVING (or wrap the query in SELECT count(*) AS bad FROM (...) t), monitor the number with > and 0, and send the alert to email or Slack.

Start with freshness on a few important tables, add volume where partial loads have hurt you, and add validity checks for the columns that have broken before. Write a check each time something breaks, and the set grows to match your real failure modes.

For a small-team version of this, see data observability for small teams.