Orders in a Databricks table dropped by 40% for 3 days because an upstream feed half-failed, and nobody noticed until the weekly review. How would you set up an alert in Databricks SQL that catches this the next morning without crying wolf?

Write the alert query so it does the comparison itself: yesterday's orders against the average of the same weekday over the past 4 weeks, returning 1 when the ratio falls below a bound such as 0.7. Trigger the alert when the value is 1, schedule it after the nightly load (or run it as a task right after the load job), treat an empty result as a failure, and send it to a channel the team watches.

1. Why a fixed threshold fails

"Alert if orders < 5,000" fires every Sunday and misses a 40% drop on a busy Monday. Orders follow the week, so compare like with like.

2. The query

WITH daily AS (
  SELECT order_date, COUNT(*) AS orders
  FROM sales.gold.orders
  WHERE order_date >= current_date() - INTERVAL 29 DAYS
    AND order_date < current_date()
  GROUP BY order_date
),
yesterday AS (
  SELECT orders FROM daily WHERE order_date = current_date() - INTERVAL 1 DAY
),
baseline AS (
  SELECT AVG(orders) AS usual FROM daily
  WHERE dayofweek(order_date) = dayofweek(current_date() - INTERVAL 1 DAY)
    AND order_date < current_date() - INTERVAL 1 DAY
)
SELECT CASE
         WHEN y.orders IS NULL THEN 1                       -- no rows for yesterday at all
         WHEN y.orders < 0.7 * b.usual THEN 1
         ELSE 0
       END AS breach,
       y.orders AS yesterday_orders,
       ROUND(b.usual) AS usual_orders
FROM baseline b LEFT JOIN yesterday y ON true;

Alerts do not accept query parameters, so the dates come from current_date(), not from a parameter. Returning both counts lets the notification show them.

3. The alert settings

4. Tune it so people keep reading it

• Back-test: run the query for each of the past 90 days and count how often 0.7 would have fired. Adjust until it would have caught real incidents and little else.
• Expect public holidays to fire; a small calendar table to exclude them keeps trust.
• If many alerts run at 07:30, stagger them by a few minutes: a workspace runs 250 alert runs at once by default and skips the rest.

5. Beyond the count

The same pattern covers revenue per order, the share of orders with a null region, or the latest loaded_at older than 6 hours. For broad coverage, Unity Catalog data quality monitoring with anomaly detection can send alerts without one query per metric.

What interviewers want: a seasonal baseline instead of a fixed number, the comparison done in SQL with a 1 or 0 result, schedule tied to the load, an empty result treated as a failure, notifications that reach a team, and back-testing the threshold.