New message text here

Yes, the results of different tests can be combined in Elementary. When you run your dbt tests (including dbt built-in tests, dbt-expectations, dbt-utils, and Elementary's own tests), Elementary collects all the test results and stores them in its schema. When you generate a report using the

edr report

command, it reads from this centralized table and visualizes the results together in a single report. This allows you to see the outcomes of all your tests—regardless of their source—in one unified interface. You do not need to do anything special; simply running your tests and then generating the report will combine the results automatically [Slack answer](https://slack.com/archives/C02CTC89LAX/p1693381395288719) [Slack answer](https://slack.com/archives/C02CTC89LAX/p1702587148681109). Additionally, if you run your tests in parallel or across multiple jobs, as long as they write to the same Elementary schema, the results will be aggregated and shown together in the report [Slack answer](https://slack.com/archives/C02CTC89LAX/p1693200603207449).

The knowledge sources do not explicitly state whether you can use an aggregate function like

count()

directly within the

where_expression

parameter of the

elementary.dimension_anomalies

test. The

where_expression

is described as a SQL expression used to filter rows before the anomaly detection logic is applied, and typical usage examples involve simple column filters (e.g.,

column1 = 'value1'

) rather than aggregates. In one Slack discussion, a user asked about using a condition like

column1 = value1 and (count(column1)) < x

in a

where_expression

, and the response suggested using

dimension_anomalies

for row counts per dimension, but did not confirm support for aggregate functions in the

where_expression

itself. This implies that aggregate functions like

count()

are not supported directly in the `where_expression`—it is intended for row-level filtering, not for post-aggregation logic [Slack thread](https://slack.com/archives/C02CTC89LAX/p1676536699097169). If you need to apply logic based on counts, you would typically need to pre-aggregate your data in a model or view, and then run the anomaly test on that result. Summary: No, you cannot use

count()

or other aggregate functions directly in the

where_expression

of

elementary.dimension_anomalies

. The

where_expression

is for row-level filtering only, not for aggregation logic.

The SQL equivalent of the provided

elementary.dimension_anomalies

test configuration would be a query that: 1. Filters the data using the

where_expression

. 2. Groups the data by the specified

dimensions

(

event_type

,

country_name

). 3. Buckets the data by the specified time period (

hour

, last 4 hours). 4. Counts the number of rows per group. Based on the configuration, the SQL might look like this:

sql SELECT   DATE_TRUNC(loaded_at, HOUR) AS bucket_start,   event_type,   country_name,   COUNT(*) AS event_count FROM   login_events WHERE   event_type IN ('login', 'logout')   AND country_name != 'unwanted country'   AND loaded_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 4 HOUR) GROUP BY   bucket_start,   event_type,   country_name ORDER BY   bucket_start DESC

Explanation: -

DATE_TRUNC(loaded_at, HOUR)

creates the hourly time buckets. - The

WHERE

clause applies the same filter as

where_expression

. - The

GROUP BY

clause groups by both dimensions and the time bucket. - The time window is limited to the last 4 hours, matching

time_bucket: { period: hour, count: 4 }

. This query structure aligns with how Elementary's

dimension_anomalies

test operates: it groups and counts rows by the specified dimensions and time buckets, after applying the row-level filter from

where_expression

 [dimension_anomalies documentation](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/dimension-anomalies#test-configuration).

Your configuration:

yaml - name: dim_efuse     description: "Unique identifier for the efuse"     tests:       - elementary.dimension_anomalies:           dimensions:             - efuse_id           where_expression: "COUNT(efuse_id) > 1"           config:             severity: warn

Explanation in SQL format: According to the knowledge sources, the

where_expression

in Elementary anomaly tests is intended for row-level filtering and does not support aggregate functions like

COUNT()

directly within it. The documentation and community discussions clarify that

where_expression

should be a valid SQL expression that can be applied to each row, not an aggregate over groups of rows [where_expression documentation](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/where-expression) [Slack discussion](https://slack.com/archives/C02CTC89LAX/p1676536699097169). If you attempt to use

COUNT(efuse_id) > 1

in the

where_expression

, it will not work as intended and will likely result in a SQL error. What actually works: A typical SQL equivalent of what you might want (finding

efuse_id

values that appear more than once) would look like this:

sql SELECT   efuse_id,   COUNT(*) AS efuse_count FROM   dim_efuse GROUP BY   efuse_id HAVING   COUNT(*) > 1

But this logic cannot be directly expressed in the

where_expression

parameter of

elementary.dimension_anomalies

. The

where_expression

is for row-level filters, such as:

yaml where_expression: "status = 'active'"

Summary: You cannot use

COUNT(efuse_id) > 1

in the

where_expression

of an Elementary test. If you want to monitor for duplicate

efuse_id

values, you would need to create a separate model or view that identifies duplicates, and then run an anomaly test on that result. The current configuration is not valid according to the knowledge sources.

A message from kapa.ai

In the context of the

elementary.dimension_anomalies

test, the equivalent of

COUNT(*) AS event_count

occurs because the test groups your data by the specified dimensions (e.g.,

event_type

,

country_name

) and time buckets (if a

timestamp_column

is provided), and then counts the number of rows in each group. This is how the test detects anomalies in the frequency of each dimension value over time. This behavior is described in the documentation: > "The test counts rows grouped by given

dimensions

(columns/expressions)." If a

timestamp_column

is configured, the distribution is collected per time bucket. If not, it counts the total rows per dimension [dimension_anomalies documentation](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/dimension-anomalies). So, the

COUNT(*)

is part of the internal logic of the test, used to aggregate the data for anomaly detection.