https://linen.dev logo
Join Slack
Powered by
<@U088WCP3B4P> I have a dimension anomalies test f...
# ask-elementary-ai
n
@kapa.ai I have a dimension anomalies test for a column. It has 3 values say a,b,c. Now A has 2.5 mil records and b has 1.5 mil records. C has 500k records. Now we had a mistake in our code and C dropped to 0 records. The dimension anomaly saw a deviation of -0.8 and didnt trigger a warning because I had the default values. How can I make it sure that it will see these drops in number?
k
A message from kapa.ai
There are a few levers you can use to make sure a big drop like C → 0 is caught by `dimension_anomalies`: 1. Increase anomaly sensitivity (make it more sensitive) The expected range is based on standard deviations; a lower 
anomaly_sensitivity
makes the band tighter and flags more anomalies. Default is 3; you can try values like 1.5–2 so large drops are more likely to be flagged. [[anomaly sensitivity](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/anomaly-sensitivity#page-title)]
Copy code
yml    models:      - name: your_model        data_tests:          - elementary.dimension_anomalies:              arguments:                dimensions:                  - your_dimension_column                anomaly_sensitivity: 1.5
2. Use 
fail_on_zero
to always fail on zero volume If a dimension value going to 0 should always be considered an incident, you can add 
fail_on_zero: true
. Any zero metric value in the detection period will fail the test, regardless of z‑score. [[fail on zero](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/fail_on_zero#page-title)]
Copy code
yml    models:      - name: your_model        data_tests:          - elementary.dimension_anomalies:              arguments:                dimensions:                  - your_dimension_column                fail_on_zero: true
3. Optionally tune training/detection windows In similar “drop not detected” cases, support has suggested adjusting: - 
days_back
(training window) - 
backfill_days
(how many recent days are checked) - 
anomaly_sensitivity
(as above) [[dim anomaly tuning](https://elementary-community.slack.com/archives/C02CTC89LAX/p1691435821870519?thread_ts=1691435821.870519&amp;cid=C02CTC89LAX)] This can help if a previous drop was “learned” as normal and is masking the new one. In your situation, the most direct way to guarantee that “C dropped to 0” is treated as a problem is to keep a reasonable 
anomaly_sensitivity
(e.g. 2) and add 
fail_on_zero: true
to that 
dimension_anomalies
test.
n
@kapa.ai I see that big number have a bigger score then the lower number. However In want to have a deviation on the separte values instead of combining them how do I do that?
k
A message from kapa.ai
You can get separate deviation/thresholds per value by using dimensions on the anomaly test, so each dimension value is modeled independently instead of all values being combined. For 
dimension_anomalies
, this is already how it works: the test groups by the configured 
dimensions
, and each dimension is monitored separately. [[dimensions config](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)] Example:
Copy code
yml models:   - name: your_model     config:       elementary:         timestamp_column: event_date     data_tests:       - elementary.dimension_anomalies:           arguments:             dimensions:               - your_dimension_column   # values: a, b, c
In this setup: - A, B, and C each get their own history, thresholds, and anomaly score. - A large volume on A does not “dilute” the anomaly score for C; C→0 is evaluated only against C’s own past. [[dimensions config](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)] If you instead meant 
column_anomalies
or 
all_columns_anomalies
, you can also add 
dimensions
there so the metric (e.g. null_rate, max, etc.) is computed and modeled per dimension value:
Copy code
yml columns:   - name: some_metric_column     tests:       - elementary.column_anomalies:           column_anomalies:             - max           dimensions:             - your_dimension_column
This makes the anomaly detection run separately for each value of 
your_dimension_column
, not on the combined distribution. [[dimensions config](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)]
n
@kapa.ai how is this score calculated? I have for example the following: The first one is the anomaly score and the second one is the latest metric value
k
A message from kapa.ai
The knowledge sources say that Elementary uses a z‑score (standard score) for anomaly detection. [[Data anomaly](https://docs.elementary-data.com/data-tests/data-anomaly-detection#page-title)] The core formula is:
Copy code
sql anomaly_score =    case     when training_stddev is null then null     when training_stddev = 0 then 0     else (metric_value - training_avg) / training_stddev   end
Where (naming from the models): - 
metric_value
= the latest metric value (your “second number”) - 
training_avg
= rolling average of past metric values - 
training_stddev
= rolling standard deviation of past metric values - 
anomaly_score
= z‑score (your “first number”) [[Slack snippet](https://elementary-community.slack.com/archives/C08HW57FGN9/p1762612914689309?thread_ts=1762612914.689309&amp;cid=C08HW57FGN9); [metrics_anomaly_score](https://elementary-community.slack.com/archives/C02CTC89LAX/p1720623688508099?thread_ts=1720623688.508099&amp;cid=C02CTC89LAX)] So, for a given row: - “latest metric value” = 
metric_value
- “anomaly score” = 
(metric_value - training_avg) / training_stddev
(or 0 / null in the edge cases above). [[Anomaly SQL](https://elementary-community.slack.com/archives/C08HW57FGN9/p1762612914689309?thread_ts=1762612914.689309&amp;cid=C08HW57FGN9); [Data anomaly](https://docs.elementary-data.com/data-tests/data-anomaly-detection#page-title)] If you share a concrete pair like 
anomaly_score = -0.8
and 
latest_metric_value = 500000
, you’d also have corresponding 
training_avg
and 
training_stddev
in the same row that make that equation hold.
n
If I want to add more trainings days how do I do this?
@kapa.ai If I want to add more training days How do I do this?
k
A message from kapa.ai
To add more training days, configure the 
training_period
on your anomaly test (this replaced the old 
days_back
parameter). [[training period](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/training-period#page-title); [anomaly params](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/anomaly-params#page-title)] Example for a 
dimension_anomalies
test with 30 training days:
Copy code
yml models:   - name: your_model     data_tests:       - elementary.dimension_anomalies:           arguments:             dimensions:               - your_dimension_column             training_period:               period: day               count: 30
Key points: - 
period
can be 
day
, 
week
, or 
month
. - 
count
is how many of those periods are used for training. - Default is 14 days if you don’t set it. [[training period](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/training-period#page-title)] Remember that if you also configure seasonality (e.g. 
day_of_week
), Elementary may automatically extend the effective training window beyond what you set to ensure enough history per season. [[training period](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/training-period#page-title)]
n
@kapa.ai How iss training_avg and traing_stddev calculated?
k
A message from kapa.ai
training_avg
and 
training_stddev
are calculated as rolling window statistics over the historical metric values, using SQL window functions. From the compiled models and snippets: ```sql avg(metric_value) over ( partition by metric_name, full_table_name, column_name, dimension, dimension_value, bucket_seasonality order by bucket_end asc rows between unbounded preceding and current row ) as training_avg, stddev(metric_value) over ( partition by metric_name, full_table_name, column_name, dimension, dimension_value, bucket_seasonality order by bucket_end asc rows between unbounded preceding and current row ) as training_stddev ```[[BigQuery window](https://elementary-community.slack.com/archives/C02CTC89LAX/p1687939167177509?thread_ts=1687939167.177509&amp;cid=C02CTC89LAX)] Concretely: - Partition: metrics are grouped by 
metric_name, full_table_name, column_name, dimension, dimension_value, bucket_seasonality
so each metric × table × column × dimension value × seasonality has its own series. [[BigQuery window](https://elementary-community.slack.com/archives/C02CTC89LAX/p1687939167177509?thread_ts=1687939167.177509&amp;cid=C02CTC89LAX)] - Order: within each partition, rows are ordered by 
bucket_end
(time). - Window: 
rows between unbounded preceding and current row
means: - For each time bucket, 
training_avg
is the average of all metric_value values from the first bucket up to that bucket. - 
training_stddev
is the standard deviation of those same values. These 
training_avg
and 
training_stddev
are then used to compute the anomaly score: ```sql case when training_stddev is null then null when training_stddev = 0 then 0 else (metric_value - training_avg) / training_stddev end as anomaly_score ```[[metrics_anomaly_score](https://elementary-community.slack.com/archives/C02CTC89LAX/p1720623688508099?thread_ts=1720623688.508099&amp;cid=C02CTC89LAX)]
n
@kapa.ai the training avg that is calculated over all the values in a column. Is there a way that this is only done on the dimension_value instead?
k
A message from kapa.ai
Yes. When you use the 
dimensions
parameter, Elementary is designed to calculate the training statistics per dimension value, not across all values together. From the implementation, 
training_avg
and 
training_stddev
are computed with a window that partitions by 
dimension
and 
dimension_value
(along with metric/table/column and seasonality), so each dimension value has its own rolling average and stddev:
Copy code
sql avg(metric_value) over (   partition by metric_name, full_table_name, column_name, dimension, dimension_value, bucket_seasonality   order by bucket_end asc   rows between unbounded preceding and current row ) as training_avg, stddev(metric_value) over (   partition by metric_name, full_table_name, column_name, dimension, dimension_value, bucket_seasonality   order by bucket_end asc   rows between unbounded preceding and current row ) as training_stddev ```[[BigQuery window](<https://elementary-community.slack.com/archives/C02CTC89LAX/p1687939167177509?thread_ts=1687939167.177509&cid=C02CTC89LAX>)]  And the docs for `dimensions` explicitly say that using this parameter “segments the tested data per dimension, and each dimension is monitored separately.” [[dimensions config](<https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title>)]  So to ensure training is done only per `dimension_value`, make sure your test is configured with `dimensions`:  ```yml models:   - name: your_model     config:       elementary:         timestamp_column: event_date     data_tests:       - elementary.dimension_anomalies:           arguments:             dimensions:               - your_dimension_column
With this setup, the training average/stddev and anomaly score are calculated independently for each value of 
your_dimension_column
.
n
@kapa.ai is this also the case in the free version of elementary? Because it seems that my training avg is calculated over alle the values in a column with the elementary.dimension_anomalies
k
A message from kapa.ai
The knowledge sources don’t explicitly distinguish between a “free” and “paid” version for how 
dimension_anomalies
is calculated, so I can’t say there is any difference in the partitioning logic between editions. What is documented/visible in the shared SQL is: - The anomaly training stats (
training_avg
, 
training_stddev
) are computed with a window like:
Copy code
sql   avg(metric_value) over (     partition by metric_name, full_table_name, column_name, dimension, dimension_value, bucket_seasonality     order by bucket_end asc     rows between unbounded preceding and current row   ) as training_avg
so they are intended to be per dimension + dimension_value, not over all values together, when dimensions are used. [[BigQuery window](https://elementary-community.slack.com/archives/C02CTC89LAX/p1687939167177509?thread_ts=1687939167.177509&amp;cid=C02CTC89LAX); [metrics partition issue](https://github.com/elementary-data/elementary/issues/1729)] - The docs for 
dimensions
say: “Using this param segments the tested data per dimension, and each dimension is monitored separately.” [[dimensions config](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)] So, based on the available information, **yes, when 
dimensions
is configured, training should be per 
dimension_value
even in the open‑source / free setup**; there’s no indication of a different behavior by edition. If in your environment 
training_avg
clearly looks like it’s computed over all values in the column: 1. Double‑check that your test actually has 
dimensions:
set under 
arguments
for 
elementary.dimension_anomalies
. [[dimensions config](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)] 2. It might be an issue similar to the open bug where metrics weren’t partitioned by dimension in one of the models. [[metrics partition issue](https://github.com/elementary-data/elementary/issues/1729)] Given the uncertainty and that you’re seeing different behavior, I’d recommend: - Sharing a snippet of the compiled SQL for your test (from 
target/compiled
) in the Elementary Slack, or - Commenting on / referencing the open GitHub issue above with your example. That will let the team confirm whether you’re hitting that partitioning bug or something configuration‑related.
n
@kapa.ai What is the latest free elementary?
k
A message from kapa.ai
Based on the release notes in the knowledge sources, the latest documented open‑source (free) Elementary version is 0.20.0. [[Elementary 0.20.0](https://docs.elementary-data.com/oss/release-notes/releases/0.20.0#page-title)] For a complete and always up‑to‑date list of OSS releases, the team also points users to the GitHub releases page. [[Slack answer](https://elementary-community.slack.com/archives/C02CTC89LAX/p1730991716197739?thread_ts=1730991716.197739&amp;cid=C02CTC89LAX)]
n
@kapa.ai I have updated 0.19.4 to 0.21.0 however I m getting this error message: 132747 Encountered an error: Compilation Error in operation elementary-on-run-end-0 (./dbt_project.yml) 'dbt.contracts.graph.nodes.ModelNode object' has no attribute 'get' > in macro flatten_run_result (macros/edr/dbt_artifacts/upload_run_results.sql) > called by macro upload_artifacts_to_table (macros/edr/dbt_artifacts/upload_artifacts_to_table.sql) > called by macro upload_run_results (macros/edr/dbt_artifacts/upload_run_results.sql) > called by macro on_run_end (macros/edr/system/hooks/on_run_end.sql) > called by operation elementary-on-run-end-0 (./dbt_project.yml)
k
A message from kapa.ai
This specific error has been seen before and was fixed by upgrading to a newer Elementary dbt package version. In Slack, a user on 0.20.0 hit the same error: > 
'dbt.contracts.graph.nodes.ModelNode object' has no attribute 'get'
> in 
flatten_run_result
→ 
upload_run_results
→ 
on_run_end
[[Slack thread](https://elementary-community.slack.com/archives/C02CTC89LAX/p1761598307807089?thread_ts=1761598307.807089&amp;cid=C02CTC89LAX)] They resolved it by upgrading from 0.20.0 to 0.20.1: > “If anyone hits this, I was package-locked on v20.0, 20.1 fixes it.” [[Slack thread](https://elementary-community.slack.com/archives/C02CTC89LAX/p1761598307807089?thread_ts=1761598307.807089&amp;cid=C02CTC89LAX)] Given that: 1. You’re seeing the same stack trace in 
flatten_run_result
. 2. A nearly identical bug was fixed by a patch release. The most reliable next steps are: 1. Upgrade to the latest 0.21.x patch in 
packages.yml
(e.g. 
">=0.21.1,<0.22.0"
if available) and run:
Copy code
bash    dbt deps    dbt run --select elementary
2. Re‑run your job that triggers the 
on-run-end
hook. If after upgrading to the latest 0.21.x you still see the same error, then it’s likely a new regression not covered in the current knowledge sources; in that case I’d recommend posting the full error and your dbt/elementary versions in the Elementary Slack so the team can confirm whether a new patch is needed.
n
@kapa.ai this is the sql of metrics_anomaly_score.sql in 0.21.0 {{ config( materialized = 'view', bind=False ) }} with data_monitoring_metrics as ( select * from {{ ref('data_monitoring_metrics') }} ), time_window_aggregation as ( select id, full_table_name, column_name, dimension, dimension_value, metric_name, metric_value, source_value, bucket_start, bucket_end, bucket_duration_hours, updated_at, avg(metric_value) over (partition by metric_name, full_table_name, column_name order by bucket_start asc rows between unbounded preceding and current row) as training_avg, {{ elementary.standard_deviation('metric_value') }} over (partition by metric_name, full_table_name, column_name order by bucket_start asc rows between unbounded preceding and current row) as training_stddev, count(metric_value) over (partition by metric_name, full_table_name, column_name order by bucket_start asc rows between unbounded preceding and current row) as training_set_size, last_value(bucket_end) over (partition by metric_name, full_table_name, column_name order by bucket_start asc rows between unbounded preceding and current row) training_end, first_value(bucket_end) over (partition by metric_name, full_table_name, column_name order by bucket_start asc rows between unbounded preceding and current row) as training_start from data_monitoring_metrics {{ dbt_utils.group_by(12) }} ), metrics_anomaly_score as ( select id, full_table_name, column_name, dimension, dimension_value, metric_name, case when training_stddev is null then null when training_set_size = 1 then null -- Single value case - no historical context for anomaly detection when training_stddev = 0 then 0 -- Stationary data case - valid, all values are identical else (metric_value - training_avg) / (training_stddev) end as anomaly_score, metric_value as latest_metric_value, bucket_start, bucket_end, training_avg, training_stddev, training_start, training_end, training_set_size, max(updated_at) as updated_at from time_window_aggregation where metric_value is not null and training_avg is not null and bucket_end >= {{ elementary.edr_timeadd('day', '-7', elementary.edr_date_trunc('day', elementary.edr_current_timestamp())) }} {{ dbt_utils.group_by(15) }} order by bucket_end desc ), final as ( select id, full_table_name, column_name, dimension, dimension_value, metric_name, anomaly_score, latest_metric_value, bucket_start, bucket_end, training_avg, training_stddev, training_start, training_end, training_set_size, updated_at, case when abs(anomaly_score) > {{ elementary.get_config_var('anomaly_sensitivity') }} then true else false end as is_anomaly from metrics_anomaly_score ) select * from final In here you can see that the training avg is not on the value but on the column level. In which version is this fixed?
k
A message from kapa.ai
You’re right: in the 
metrics_anomaly_score.sql
you pasted, the training stats are partitioned only by:
Copy code
sql partition by metric_name, full_table_name, column_name
so 
training_avg
/ 
training_stddev
are computed at the column level, not per 
dimension_value
. That’s exactly the behavior described in the open GitHub issue about 
metrics_anomaly_score
not partitioning by dimensions. [[Partition bug](https://github.com/elementary-data/elementary/issues/1729)] In that issue, the expected partition is: ```sql avg(metric_value) over ( partition by metric_name, full_table_name, column_name, dimension, dimension_value order by bucket_start asc rows between unbounded preceding and current row ) as training_avg ```[[Partition bug](https://github.com/elementary-data/elementary/issues/1729); [user comment](https://github.com/elementary-data/elementary/issues/1729)] However, the knowledge sources do not state in which Elementary version this is fixed, and the issue is still marked as open in the referenced repo. [[Partition bug](https://github.com/elementary-data/elementary/issues/1729)] Because of that, I can’t confidently tell you a version where this is resolved. Given what we have: - Your 0.21.0 SQL matches the problematic pattern from the open issue. - The latest docs/snippets we see still show the same partitioning. - There is no release note or comment in the provided sources that says “fixed in X.Y.Z”. So, based on the available information: - This does not appear fixed yet in the code you’re running (0.21.0). - The knowledge sources do not specify a version where it is fixed. To get per‑dimension training stats today, you’d either need: 1. A version where that GitHub issue is explicitly closed with a code change (not visible in the provided sources), or 2. A local override of 
metrics_anomaly_score.sql
in your own project that adds 
dimension, dimension_value
to the window 
partition by
, as suggested in the issue. [[Partition bug](https://github.com/elementary-data/elementary/issues/1729)] Since I can’t see a documented fix version, the safest next step is to watch or comment on that GitHub issue and/or ask in the Elementary Slack which release includes the corrected partitioning.
n
How can I override this locally?
@kapa.ai how can I override this locally?
k
A message from kapa.ai
You can override Elementary’s model or macro in your own dbt project by redefining it with the same name and path/namespace, and using dbt’s normal override/dispatch rules. From the knowledge sources, there’s a concrete example of overriding an Elementary macro (
query_table_metrics
) by: 1. Adding a dispatch rule in 
dbt_project.yml
so dbt looks in your project before the 
elementary
package: ```yml dispatch: - macro_namespace: elementary search_order: ["<your_project_name>", "elementary"] ```[[Macro override example](https://elementary-community.slack.com/archives/C02CTC89LAX/p1709420538077629?thread_ts=1709420538.077629&amp;cid=C02CTC89LAX)] 2. Defining a macro with the same name in your project, which dbt will then use instead of the package’s version. [[Macro override example](https://elementary-community.slack.com/archives/C02CTC89LAX/p1709420538077629?thread_ts=1709420538.077629&amp;cid=C02CTC89LAX)] For your specific case (
metrics_anomaly_score.sql
): The knowledge sources don’t show an explicit, documented pattern for overriding this model file, but you can follow the same principle dbt uses for package model overrides: - Create a model in your project with the same name (
metrics_anomaly_score
) and configure it so it replaces the package model (e.g., by using the same schema and relation name). - Copy the current SQL from the package, then adjust the window functions to partition by 
dimension
and 
dimension_value
as you want. Because the docs we have only show macro override patterns (not a step‑by‑step for overriding this specific model), I can’t give you a fully guaranteed, version‑specific recipe. The general approach is: 1. Add the 
dispatch
block (for macros) if you also need to override any Elementary macros. [[Macro override example](https://elementary-community.slack.com/archives/C02CTC89LAX/p1709420538077629?thread_ts=1709420538.077629&amp;cid=C02CTC89LAX)] 2. Create a local 
models/elementary_overrides/metrics_anomaly_score.sql
with your adjusted SQL and ensure it’s selected instead of the package model (e.g., by excluding the package model in 
dbt_project.yml
and including your override). Since the knowledge sources don’t contain a precise, documented override snippet for 
metrics_anomaly_score
, I’d also recommend posting your current setup in the Elementary Slack or on the GitHub issue about partitioning so the team can confirm the exact override pattern for your version.
n
@kapa.ai could you show me the page of the dimension values and how there calculated on column or value levels for the paid version?
k
A message from kapa.ai
I’m sorry, but the knowledge sources don’t include a “page of the dimension values” for the paid (SaaS) version UI, nor do they describe any SaaS‑only behavior that differs from OSS in how dimension values are calculated. What we do have is how dimension logic works in the underlying tests, which is the same core engine the paid product uses: - 
dimension_anomalies
“counts rows grouped by given dimensions” and monitors the frequency of each dimension value over time. [[dimension anomalies](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/dimension-anomalies#page-title)] - The 
dimensions
parameter “segments the tested data per dimension, and each dimension is monitored separately.” [[dimensions config](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)] - In the anomaly engine SQL, when dimensions are wired correctly, the rolling 
training_avg
/ 
training_stddev
are computed with a window that partitions by 
metric_name, full_table_name, column_name, dimension, dimension_value, bucket_seasonality
, i.e., per dimension value, not across all values together. [[window example](https://elementary-community.slack.com/archives/C02CTC89LAX/p1687939167177509?thread_ts=1687939167.177509&amp;cid=C02CTC89LAX)] However, there is also an open issue showing that in 
metrics_anomaly_score.sql
the partition is currently only by 
metric_name, full_table_name, column_name
, which means those training stats are at the column level there, not per dimension value. [[partition bug](https://github.com/elementary-data/elementary/issues/1729)] Because the sources don’t show: - Any SaaS‑only UI screenshots or “dimension values page”, or - Any documentation that distinguishes paid vs free behavior for dimension calculations, I can’t reliably show you the exact paid‑version page or confirm any UI‑level differences. If you want to verify behavior in your paid workspace, the best next steps are: - Check the docs pages on anomaly detection and dimensions (these apply to both OSS and SaaS): [[data anomaly method](https://docs.elementary-data.com/data-tests/data-anomaly-detection#page-title); [dimensions config](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)] - Ask in the Elementary Slack or support channel for a screenshot / walkthrough of the specific SaaS “dimension values” view, since that information isn’t present in the provided sources.
n
@kapa.ai this is the test_dimension_anomalies.sql does this refer to the metrics_anomaly_score.sql?
k
A message from kapa.ai
I’m not seeing 
test_dimension_anomalies.sql
itself in the provided knowledge sources, so I can’t confirm its exact contents or show the reference directly. From the docs and snippets we do have: - Anomaly tests (including 
dimension_anomalies
) store their metrics in 
data_monitoring_metrics
. [[Anomaly detection models](https://docs.elementary-data.com/data-tests/dbt/package-models#anomaly-detection)] - 
metrics_anomaly_score
is a view on 
data_monitoring_metrics
 that “runs the same query the anomaly detection tests run to calculate anomaly scores.” [[Anomaly detection models](https://docs.elementary-data.com/data-tests/dbt/package-models#anomaly-detection)] - There is also a note that anomaly tests use a query like 
elementary.get_read_anomaly_scores_query()
and then call 
store_anomaly_test_results
, which relies on the anomaly scores query. [[GitHub issue 1483](https://github.com/elementary-data/elementary/issues/1483)] So, even though I don’t have the exact 
test_dimension_anomalies.sql
file here, the architecture described in the docs implies: - 
dimension_anomalies
→ writes metrics to 
data_monitoring_metrics
- 
metrics_anomaly_score
→ reads from 
data_monitoring_metrics
to compute anomaly scores - The test implementation uses that same anomaly-scores logic (or a very similar query) when deciding if the test passes/fails. In other words: 
dimension_anomalies
is not a wrapper around 
metrics_anomaly_score
, but both rely on the same underlying metrics and anomaly-score logic; 
metrics_anomaly_score
is effectively exposing the same calculations the tests use. [[Anomaly detection models](https://docs.elementary-data.com/data-tests/dbt/package-models#anomaly-detection); [GitHub issue 1483](https://github.com/elementary-data/elementary/issues/1483)]
n
@kapa.ai If I want to test only certain values in a column. How can I do this with dimension?
k
A message from kapa.ai
You can limit a 
dimension_anomalies
test to only some values of a column by combining: 1. 
dimensions
 – which column you group by 2. 
where_expression
 – which rows (values) to include ### Example: only test values 
a
and 
b
of 
status
Copy code
yml models:   - name: your_model     config:       elementary:         timestamp_column: event_time     data_tests:       - elementary.dimension_anomalies:           arguments:             dimensions:               - status             where_expression: "status in ('a', 'b')"   # only test these values             time_bucket:               period: day               count: 1
- 
dimensions: - status
tells Elementary to monitor row counts per status value. [[dimensions](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)] - 
where_expression
filters the data so only rows with 
status in ('a','b')
are considered; other values (like 
c
) are ignored by this test. [[where_expression](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/where-expression#page-title); [dimension_anomalies config](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/dimension-anomalies#test-configuration)] You can adapt the 
where_expression
to any SQL condition you need (e.g. multiple columns, ranges, etc.).
n
@kapa.ai if I want to check and track the values of columns and see the deviation for each value what kind of test do I need to use?
k
A message from kapa.ai
To track each value of a column over time and see its own deviation, you have two main options, depending on what exactly you want to measure: --- ### 1. Track row counts per value (distribution of a dimension) Use 
elementary.dimension_anomalies
. This test: - Groups rows by the configured 
dimensions
(e.g. a status or category column). - Monitors the row count per value over time. - Flags anomalies when a specific value’s frequency changes unexpectedly. [[dimension anomalies](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/dimension-anomalies#page-title); [dimensions](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)] Example:
Copy code
yml models:   - name: your_model     config:       elementary:         timestamp_column: event_time     data_tests:       - elementary.dimension_anomalies:           arguments:             dimensions:               - your_dimension_column   # each value is tracked separately
Use this if you want: “for each value X in this column, alert when its volume changes abnormally.” --- ### 2. Track a column metric per value (e.g. avg, null rate per value) Use 
elementary.column_anomalies
 (or 
all_columns_anomalies
) with 
dimensions
. Here: - 
column_anomalies
computes metrics like 
average
, 
null_percent
, etc. for a column. [[column anomalies](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/column-anomalies#page-title); [column_anomalies config](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/column-anomalies#page-title)] - Adding 
dimensions
makes those metrics calculated and monitored per dimension value. [[dimensions](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/dimensions#page-title)] Example:
Copy code
yml models:   - name: your_model     config:       elementary:         timestamp_column: event_time     columns:       - name: metric_column         data_tests:           - elementary.column_anomalies:               arguments:                 column_anomalies:                   - average                 dimensions:                   - your_dimension_column   # deviation per value of this column
Use this if you want: “for each value X in 
your_dimension_column
, track how the metric_column’s average (or other metric) deviates over time.” --- So: - Row count per value → 
elementary.dimension_anomalies
- Other metrics per value (avg, null %, etc.) → 
elementary.column_anomalies
(or 
all_columns_anomalies
) with 
dimensions
.
n
@kapa.ai How can I override the metrics_anomaly_score.sql in the package again? Could you give me a step by step description?
k
A message from kapa.ai
The knowledge sources don’t contain an official, documented “override `metrics_anomaly_score.sql`” guide, so I can only outline the approach based on how dbt and Elementary handle overrides in general, and you may need to validate details in your own project. ### 1. Identify and exclude the original model 
metrics_anomaly_score
is a model in the Elementary package that reads from 
data_monitoring_metrics
and computes anomaly scores. [[metrics_anomaly_score SQL](https://elementary-community.slack.com/archives/C08HW57FGN9/p1766406646707399?thread_ts=1766406646.707399&amp;cid=C08HW57FGN9)] To replace it, you should ensure the package’s version is not built: In 
dbt_project.yml
, under the 
elementary
package, exclude that model, for example:
Copy code
yml models:   elementary:     +schema: "elementary"     metrics_anomaly_score:       +enabled: false
(Exact syntax may vary by your existing config; the key idea is to disable that model.) The docs do show how to configure the 
elementary
models block, so you can add the 
metrics_anomaly_score
sub‑config there. [[dbt package quickstart](https://docs.elementary-data.com/data-tests/dbt/quickstart-package#page-title)] ### 2. Create your own 
metrics_anomaly_score
model In your project (not in 
dbt_packages
), create a model file, e.g.: 
models/elementary_overrides/metrics_anomaly_score.sql
Copy the SQL from the package version you pasted (0.21.0) and then adjust it. For example, to partition by dimension and dimension_value in the training stats (as suggested in the open issue): [[partition bug](https://github.com/elementary-data/elementary/issues/1729)]
Copy code
sql with data_monitoring_metrics as (      select * from {{ ref('data_monitoring_metrics') }}  ),  time_window_aggregation as (      select         id,         full_table_name,         column_name,         dimension,         dimension_value,         metric_name,         metric_value,         source_value,         bucket_start,         bucket_end,         bucket_duration_hours,         updated_at,         avg(metric_value) over (           partition by metric_name, full_table_name, column_name, dimension, dimension_value           order by bucket_start asc           rows between unbounded preceding and current row         ) as training_avg,         {{ elementary.standard_deviation('metric_value') }} over (           partition by metric_name, full_table_name, column_name, dimension, dimension_value           order by bucket_start asc           rows between unbounded preceding and current row         ) as training_stddev,         count(metric_value) over (           partition by metric_name, full_table_name, column_name, dimension, dimension_value           order by bucket_start asc           rows between unbounded preceding and current row         ) as training_set_size,         last_value(bucket_end) over (           partition by metric_name, full_table_name, column_name, dimension, dimension_value           order by bucket_start asc           rows between unbounded preceding and current row         ) training_end,         first_value(bucket_end) over (           partition by metric_name, full_table_name, column_name, dimension, dimension_value           order by bucket_start asc           rows between unbounded preceding and current row         ) as training_start     from data_monitoring_metrics     {{ dbt_utils.group_by(12) }} ),  -- keep the rest of the model as in the package version
The rest of the model (the 
metrics_anomaly_score
CTE and 
final
CTE) can stay as in your 0.21.0 SQL. [[metrics_anomaly_score SQL](https://elementary-community.slack.com/archives/C08HW57FGN9/p1766406646707399?thread_ts=1766406646.707399&amp;cid=C08HW57FGN9)] ### 3. Ensure your override is selected Because the knowledge sources don’t show a documented pattern for overriding this specific model, I can’t guarantee the exact selection mechanics, but in practice: - With the package model disabled (
+enabled: false
), only your 
metrics_anomaly_score
should be built. - Make sure your model is included in your run selection (e.g. 
dbt run --select elementary
will pick it if it’s in the same schema / dependency graph). You can verify by: - Running 
dbt ls --select metrics_anomaly_score
to see which node is active. - Inspecting the compiled SQL in 
target/compiled
to confirm it’s your version. ### 4. Notes and limitations - Elementary’s own macros are often called with the 
elementary.
namespace to prevent accidental overrides. [[macro override explanation](https://elementary-community.slack.com/archives/C02CTC89LAX/p1701990486227199?thread_ts=1701990486.227199&amp;cid=C02CTC89LAX); [macro call pattern](https://elementary-community.slack.com/archives/C02CTC89LAX/p1709420538077629?thread_ts=1709420538.077629&amp;cid=C02CTC89LAX)] That affects macro overrides, but not model overrides like this one. - The docs and Slack threads don’t provide a fully worked example of overriding 
metrics_anomaly_score
specifically, so some details (like your exact 
dbt_project.yml
structure) you’ll need to adapt. If you want confirmation that this override pattern is correct for your exact version, the safest follow‑up is to share your 
dbt_project.yml
and the new model path in the Elementary Slack and ask them to confirm the model‑override setup.
n
@kapa.ai could you show me the metrics_anomaly_score.sql from the paid version?
k
A message from kapa.ai
I’m sorry, but the knowledge sources do not include the 
metrics_anomaly_score.sql
definition for the paid (Cloud) version, only for the open‑source dbt package. What we do have is: - A description that, in OSS, 
metrics_anomaly_score
is a view on 
data_monitoring_metrics
 that “runs the same query the anomaly detection tests run to calculate anomaly scores.” [[Dbt models](https://docs.elementary-data.com/data-tests/dbt/package-models#anomaly-detection)] - The full OSS SQL for 
metrics_anomaly_score
(v0.21.0 style), which you already pasted, where 
training_avg
/ 
training_stddev
are calculated with window functions over 
data_monitoring_metrics
. [[Slack snippet](https://elementary-community.slack.com/archives/C08HW57FGN9/p1766406646707399?thread_ts=1766406646.707399&amp;cid=C08HW57FGN9)] There is no separate, Cloud‑only 
metrics_anomaly_score.sql
shown, and the Cloud anomaly detection is documented as using a different implementation (ML models in the platform rather than dbt‑run SQL). [[OSS vs Cloud](https://docs.elementary-data.com/data-tests/anomaly-detection-tests-oss-vs-cloud#page-title)] Because of that, I can’t show you the paid version’s `metrics_anomaly_score.sql`—it’s simply not present in the provided sources.
38 Views
Open in Slack
PreviousNext
Powered by