Monitor metric tables

Preview

This page describes the metric tables created by Databricks Lakehouse Monitoring. For information about the dashboard created by a monitor, see Use the generated SQL dashboard.

When a monitor runs on a Databricks table, it creates or updates two metric tables: a profile metrics table and a drift metrics table.

The profile metrics table contains summary statistics for each column and for each combination of time window, slice, and grouping columns. For InferenceLog analysis, the analysis table also contains model accuracy metrics.
The drift metrics table contains statistics that track changes in distribution for a metric. Drift tables can be used to visualize or alert on changes in the data instead of specific values. The following types of drift are computed:
- Consecutive drift compares a window to the previous time window. Consecutive drift is only calculated if a consecutive time window exists after aggregation according to the specified granularities.
- Baseline drift compares a window to the baseline distribution determined by the baseline table. Baseline drift is only calculated if a baseline table is provided.

Where metric tables are located

Monitor metric tables are saved to {output_schema}.{table_name}_profile_metrics and {output_schema}.{table_name}_drift_metrics, where:

{output_schema} is the catalog and schema specified by output_schema_name.
{table_name} is the name of the table being monitored.

How monitor statistics are computed

Each statistic and metric in the metric tables is computed for a specified time interval (called a “window”). For Snapshot analysis, the time window is a single point in time corresponding to the time the metric was refreshed. For TimeSeries and InferenceLog analysis, the time window is based on the granularities specified in create_monitor and the values in the timestamp_col specified in the profile_type argument.

Metrics are always computed for the entire table. In addition, if you provide a slicing expression, metrics are computed for each data slice defined by a value of the expression.

For example:

slicing_exprs=["col_1", "col_2 > 10"]

generates the following slices: one for col_2 > 10, one for col_2 <= 10, and one for each unique value in col1.

Slices are identified in the metrics tables by the column names slice_key and slice_value. In this example, one slice key would be “col_2 > 10” and the corresponding values would be “true” and “false”. The entire table is equivalent to slice_key = NULL and slice_value = NULL. Slices are defined by a single slice key.

Metrics are computed for all possible groups defined by the time windows and slice keys and values. In addition, for InferenceLog analysis, metrics are computed for each model id. For details, see Column schemas for generated tables.

Additional statistics for model accuracy monitoring (`InferenceLog` analysis only)

Additional statistics are calculated for InferenceLog analysis.

Model quality is calculated if both label_col and prediction_col are provided.
Slices are automatically created based on the distinct values of model_id_col.
For classification models, fairness and bias statistics are calculated for slices that have a Boolean value.

Query analysis and drift metrics tables

You can query the metrics tables directly. The following example is based on InferenceLog analysis:

SELECT
  window.start, column_name, count, num_nulls, distinct_count, frequent_items
FROM census_monitor_db.adult_census_profile_metrics
WHERE model_id = 1    — Constrain to version 1
  AND slice_key IS NULL    — look at aggregate metrics over the whole data
  AND column_name = "income_predicted"
ORDER BY window.start

Column schemas for generated tables

For each column in the primary table, the metrics tables contain one row for each combination of grouping columns. The column associated with each row is shown in the column column_name.

For metrics based on more than one column such as model accuracy metrics, column_name is set to :table.

For profile metrics, the following grouping columns are used:

time window
granularity (TimeSeries and InferenceLog analysis only)
log type - input table or baseline table
slice key and value
model id (InferenceLog analysis only)

For drift metrics, the following additional grouping columns are used:

comparison time window
drift type (comparison to previous window or comparison to baseline table)

The schemas of the metric tables are shown below, and are also shown in the Databricks Lakehouse Monitoring API reference documentation.

Profile metrics table schema

The following table shows the schema of the profile metrics table. Where a metric is not applicable to a row, the corresponding cell is null.

Column name	Type	Description
Grouping columns
window	Struct. See [1] below.	Time window.
granularity	string	Window duration, set by `granularities` parameter. [2]
model_id_col	string	Optional. Only used for `InferenceLog` analysis type.
log_type	string	Table used to calculate metrics. BASELINE or INPUT.
slice_key	string	Slice expression. NULL for default, which is all data.
slice_value	string	Value of the slicing expression.
column_name	string	Name of column in primary table. `:table` is a special name for metrics that apply to the whole table, such as model accuracy.
data_type	string	Spark data type of `column_name`.
logging_table_commit_version	int	Ignore.
monitor_version	bigint	Version of the monitor configuration used to calculate the metrics in the row. See [3] below for details.
Metrics columns - summary statistics
count	bigint	Number of non-null values.
num_nulls	bigint	Number of null values in `column_name`.
avg	double	Arithmetic mean of the column, ingoring nulls.
quantiles	`array<double>`	Array of 1000 quantiles. See [4] below.
distinct_count	bigint	Number of distinct values in `column_name`.
min	double	Minimum value in `column_name`.
max	double	Maximum value in `column_name`.
stddev	double	Standard deviation of `column_name`.
num_zeros	bigint	Number of zeros in `column_name`.
num_nan	bigint	Number of NaN values in `column_name`.
min_size	double	Minimum size of arrays or structures in `column_name`.
max_size	double	Maximum size of arrays or structures in `column_name`.
avg_size	double	Average size of arrays or structures in `column_name`.
min_len	double	Minimum length of string and binary values in `column_name`.
max_len	double	Maximum length of string and binary values in `column_name`.
avg_len	double	Average length of string and binary values in `column_name`.
frequent_items	Struct. See [1] below.	Top 100 most frequently occurring items.
non_null_columns	`array<string>`	List of columns with at least one non-null value.
median	double	Median value of `column_name`.
percent_null	double	Percent of null values in `column_name`.
percent_zeros	double	Percent of values that are zero in `column_name`.
percent_distinct	double	Percent of values that are distinct in `column_name`.
Metrics columns - classification model accuracy [5]
accuracy_score	double	Accuracy of model, calculated as (number of correct predictions / total number of predictions), ignoring null values.
confusion_matrix	Struct. See [1] below.
precision	Struct. See [1] below.
recall	Struct. See [1] below.
f1_score	Struct. See [1] below.
Metrics columns - regression model accuracy [5]
mean_squared_error	double	Mean squared error between `prediction_col` and `label_col`.
root_mean_squared_error	double	Root mean squared error between `prediction_col` and `label_col`.
mean_average_error	double	Mean average error between `prediction_col` and `label_col`.
mean_absolute_percentage_error	double	Mean absolute percentage error between `prediction_col` and `label_col`.
r2_score	double	R-squared score between `prediction_col` and `label_col`.
Metrics columns - fairness and bias [6]
predictive_parity	double	Measures whether the two groups have equal precision across all predicted classes. `label_col` is required.
predictive_equality	double	Measures whether the two groups have equal false positive rate across all predicted classes. `label_col` is required.
equal_opportunity	double	Measures whether the two groups have equal recall across all predicted classes. `label_col` is required.
statistical_parity	double	Measures whether the two groups have equal acceptance rate. Acceptance rate here is defined as the empirical probability to be predicted as a certain class, across all predicted classes.

[1] Format of struct for confusion_matrix, precision, recall, and f1_score:

Column name	Type
window	`struct<start: timestamp, end: timestamp>`
frequent_items	`array<struct<item: string, count: bigint>>`
confusion_matrix	`struct<prediction: string, label: string, count: bigint>`
precision	`struct<one_vs_all: map<string,double>, macro: double, weighted: double>`
recall	`struct<one_vs_all: map<string,double>, macro: double, weighted: double>`
f1_score	`struct<one_vs_all: map<string,double>, macro: double, weighted: double>`

[2] For time series or inference profiles, the monitor looks back 30 days from the time the monitor is created. Due to this cutoff, the first analysis might include a partial window. For example, the 30 day limit might fall in the middle of a week or month, in which case the full week or month is not included in the calculation. This issue affects only the first window.

[3] The version shown in this column is the version that was used to calculate the statistics in the row and might not be the current version of the monitor. Each time you refresh the metrics, the monitor attempts to recompute previously calculated metrics using the current monitor configuration. The current monitor version appears in the monitor information returned by the API and Python Client.

[4] Sample code to retrieve the 50th percentile: SELECT element_at(quantiles, int((size(quantiles)+1)/2)) AS p50 ... or SELECT quantiles[500] ... .

[5] Only shown if the monitor has InferenceLog analysis type and both label_col and prediction_col are provided.

[6] Only shown if the monitor has InferenceLog analysis type and problem_type is classification.

Drift metrics table schema

The following table shows the schema of the drift metrics table. The drift table is only generated if a baseline table is provided, or if a consecutive time window exists after aggregation according to the specified granularities.

Column name	Type	Description
Grouping columns
window	`struct<start: timestamp, end: timestamp>`	Time window.
window_cmp	`struct<start: timestamp, end: timestamp>`	Comparison window for drift_type `CONSECUTIVE`.
drift_type	string	BASELINE or CONSECUTIVE. Whether the drift metrics compare to the previous time window or to the baseline table.
granularity	string	Window duration, set by `granularities` parameter. [7]
model_id_col	string	Optional. Only used for `InferenceLog` analysis type.
slice_key	string	Slice expression. NULL for default, which is all data.
slice_value	string	Value of the slicing expression.
column_name	string	Name of column in primary table. `:table` is a special name for metrics that apply to the whole table, such as model accuracy.
data_type	string	Spark data type of `column_name`.
monitor_version	bigint	Version of the monitor configuration used to calculate the metrics in the row. See [8] below for details.
Metrics columns - drift		Differences are calculated as current window - comparison window.
count_delta	double	Difference in `count`.
avg_delta	double	Difference in `avg`.
percent_null_delta	double	Difference in `percent_null`.
percent_zeros_delta	double	Difference in `percent_zeros`.
percent_distinct_delta	double	Difference in `percent_distinct`.
non_null_columns_delta	`struct<added: int, missing: int>`	Number of columns with any increase or decrease in non-null values.
chi_squared_test	`struct<statistic: double, pvalue: double>`	Chi-square test for drift in distribution.
ks_test	`struct<statistic: double, pvalue: double>`	KS test for drift in distribution. Calculated for numeric columns only.
tv_distance	double	Total variation distance for drift in distribution.
l_infinity_distance	double	L-infinity distance for drift in distribution.
js_distance	double	Jensen–Shannon distance for drift in distribution. Calculated for categorical columns only.
wasserstein_distance	double	Drift between two numeric distributions using the Wasserstein distance metric.
population_stability_index	double	Metric for comparing the drift between two numeric distributions using the population stability index metric. See [9] below for details.

[7] For time series or inference profiles, the monitor looks back 30 days from the time the monitor is created. Due to this cutoff, the first analysis might include a partial window. For example, the 30 day limit might fall in the middle of a week or month, in which case the full week or month is not included in the calculation. This issue affects only the first window.

[8] The version shown in this column is the version that was used to calculate the statistics in the row and might not be the current version of the monitor. Each time you refresh the metrics, the monitor attempts to recompute previously calculated metrics using the current monitor configuration. The current monitor version appears in the monitor information returned by the API and Python Client.

[9] The output of the population stability index is a numeric value that represents how different two distributions are. The range is [0, inf). PSI < 0.1 means no significant population change. PSI < 0.2 indicates moderate population change. PSI >= 0.2 indicates significant population change.