Diagnostic Analysis: Collecting Related Data, Trends and Correlation

1.What you are diagnosing: anomalies and patterns in historical data

Descriptive analysis reports what happened. Diagnostic analysis explains why it happened. Most diagnostic work in Snowflake starts from historical time-series data, meaning sequential observations that record how a system, process or behaviour changed over time. Snowflake's documentation breaks a single time-series record into three parts: a date, time or timestamp at a consistent granularity; one or more measurements, usually numeric; and the dimensions linked to each measurement, such as the location of a temperature reading or the stock symbol of a trade.

Each part does a different job. The measurement holds the signal, which the documentation describes as facts that might reveal trends or anomalies in the data. The timestamp tells you when the signal changed. The dimensions tell you where to look for the cause. The documentation's own example is a run of temperature readings taken every 15 seconds. They sat around 35°C for a day and then peaked above 40°C. Seeing the peak is the descriptive step. Asking which device, which line and what else changed at that moment is the diagnostic step.

Sources1

2.Collecting the related data a diagnosis needs

A diagnosis is only as good as the data in front of you. Three kinds of related data come up again and again: the earlier state of the same table, outside factors that may have pushed the metric, and a curated input set built for the question you are asking.

The earlier state of the same table. If a total changed and you suspect a job modified the data, you need to compare the table now with the table as it was before. Time Travel provides this. You add an AT or BEFORE clause in the FROM clause, straight after the table name, and choose a point in the past by TIMESTAMP, OFFSET, STATEMENT or STREAM. With AT, the request is inclusive of any changes made by a statement or transaction with a timestamp equal to the specified parameter. BEFORE refers to the point just before the specified parameter, so with STATEMENT it gives you the table as it stood just before a given query finished.

SELECT ...
FROM ...
  { AT | BEFORE }
  (
    { TIMESTAMP => <timestamp> |
      OFFSET => <time_difference> |
      STATEMENT => <id> |
      STREAM => '<name>' }
  )
[ ... ]

Outside factors. Sometimes the cause isn't in your data at all. Snowflake's ML functions accept exogenous variables, meaning data that may have influenced the target value. The documentation lists weather, holidays, advertisement campaigns and event schedules as typical examples. These can be numeric or categorical, and they can contain NULLs. Collecting them alongside the metric turns guesses such as 'it was the heatwave' into something you can test.

A curated input set. Wrapping the data you collected in a view is common practice. The documentation notes that a view, compared with a table, lets you train models iteratively with different row counts without updating the source data. A view is also where you filter down to the columns your analysis needs.

Sources231

3.Analysing trends: downsampling to a useful granularity

Raw time series are often too fine-grained to show a trend. A sensor that reports every second produces a lot of noise and little signal at the scale of days. Downsampling rolls records up to a coarser granularity. You group them into time buckets and summarise each bucket with standard aggregates such as SUM and AVG. Snowflake offers two functions for this. TIME_SLICE works out fixed-width buckets and returns the start of each one. DATE_TRUNC cuts a date or timestamp down to a unit such as day.

Downsampling also matters for collecting related data. When two datasets have different time granularities, they can't be compared row for row. The documentation's example is Sensor A reporting every 15 seconds and Sensor B every 30 seconds. Rolling both up to 1-minute buckets puts them on a shared time axis. IDs and dimensions stay as they are, while the measurements are summed or averaged per interval. It is also a prerequisite for ML-based diagnosis, because the timestamps in your time series must represent fixed time intervals.

SELECT DATE_TRUNC('day', order_ts)::date sliced_ts, truck_id, location_id, AVG(order_amount)::NUMBER(4,2) as avg_amount
  FROM order_header
  WHERE EXTRACT(YEAR FROM order_ts)='2022'
  GROUP BY date_trunc('day', order_ts), truck_id, location_id
  ORDER BY 1, 2, 3 LIMIT 25;

The query above keeps truck_id and location_id in the GROUP BY. That choice is what makes it diagnostic and not just descriptive. If the daily trend dips, the dip can be traced to particular trucks or locations, because the dimensions survived the roll-up.

SELECT
     ? (TO_TIMESTAMP_NTZ(timestamp), 1, 'MINUTE') minute_slice,
    device_id,
    COUNT(*),
    AVG(temperature) avg_temp
  FROM sensor_data_ts
  WHERE TIMESTAMP >= ('2024-03-01 00:01:00')
    AND TIMESTAMP < ('2024-03-01 00:02:00')
  GROUP BY 1,2
  ORDER BY 1,2;

Sources1

4.Measuring relationships between metrics with CORR

After you collect a candidate cause next to the metric, such as temperature next to sales, the next question is whether the two actually move together. CORR returns the correlation coefficient of a dependent variable y and an independent variable x, calculated as COVAR_POP(y, x) / (STDDEV_POP(x) * STDDEV_POP(y)). It works as an aggregate with GROUP BY, which gives one coefficient per segment. It also works as a window function with OVER (PARTITION BY ...), but in that form it does not allow ORDER BY inside OVER or explicit window frames. DISTINCT is not supported.

CREATE OR REPLACE TABLE aggr(k int, v decimal(10,2), v2 decimal(10, 2));
INSERT INTO aggr VALUES(1, 10, NULL);
INSERT INTO aggr VALUES(2, 10, 11), (2, 20, 22), (2, 25, NULL), (2, 30, 35);

SELECT k, CORR(v, v2) FROM aggr GROUP BY k;

+---+--------------+
| K |  CORR(V, V2) |
|---+--------------|
| 1 |         NULL |
| 2 | 0.9988445981 |
+---+--------------+

Look at group 2. It has four rows, but the row (25, NULL) is quietly left out, so 0.9988 is based on three pairs. This matters when you compare segments such as regions or customer groups. A segment with a strong coefficient may rest on only a few complete pairs. Before you read a strong coefficient as a strong relationship, count the non-null pairs behind it.

Sources4

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.CORR uses every row in the group, so a high coefficient is backed by all the data in that segment.Why is that wrong?

   CORR only uses rows where both x and y are non-null. Rows with a NULL on either side are dropped silently, and a group with no complete pair returns NULL.

   Covered in Measuring relationships between metrics with CORR

2. 2.AT and BEFORE with the same STATEMENT ID return the same data.Why is that wrong?

   AT includes changes made by the statement or transaction at the specified point, while BEFORE returns the state just before it, without that statement's changes.

   Covered in Collecting the related data a diagnosis needs