Based on the knowledge sources, there are a few important points to consider when using the

elementary.freshness_anomalies

test, especially for cases where your table has not received fresh data for an extended period: 1. How Freshness Anomalies Work: The test splits your data into time buckets (e.g., by hour or day) based on the

timestamp_column

. It then compares the "freshness" (i.e., the time since the last update) of each bucket in the detection period to the previous buckets in the training period. If all buckets in both periods have similarly stale data (e.g., no updates for two weeks), the test may not flag this as an anomaly because, statistically, nothing has changed—the "normal" state is no updates. This is a common source of confusion and is confirmed in several support threads and the documentation [[source]](https://slack.com/archives/C02CTC89LAX/p1684401442974139). 2. Why Your Test Passes: If your table has not been updated for two weeks, and your training and detection periods are both set to 1 hour, the test is only comparing very recent (and equally stale) buckets. Since there is no "change" or "anomaly" compared to the recent past, the test passes. The test is designed to detect deviations from recent patterns, not absolute staleness [[source]](https://slack.com/archives/C02CTC89LAX/p1684401442974139). 3. Recommended Solutions: - If you want to detect absolute staleness (e.g., "fail if no data for X days"), the anomaly test is not the right tool. Instead, you should use a threshold-based test, such as

dbt source freshness

for sources, or create a custom test that fails if the latest timestamp is older than your threshold [[source]](https://slack.com/archives/C02CTC89LAX/p1683294589948059). - If you want to use the anomaly test, ensure your training and detection periods cover enough history to capture the "normal" update frequency. For example, if your table is usually updated daily, set

training_period

and

detection_period

to several days, not just one hour. However, if the table has been stale for the entire training period, the test will still pass. 4. Example of a Custom Staleness Test: You can write a custom dbt test that checks if the latest timestamp is within your freshness threshold. This is suggested in the support threads as a workaround for absolute staleness detection [[source]](https://slack.com/archives/C02CTC89LAX/p1684401442974139). Summary: The

elementary.freshness_anomalies

test is not designed to fail when a table is always stale; it detects changes in freshness patterns. For your use case ("fail if no data for almost 2 weeks"), use a threshold-based test or a custom test, not the anomaly test. If you need help writing such a custom test, let me know!

To trigger a warning or alert if your data is more than 2 weeks stale, the recommended approach is to use a threshold-based freshness check, not the

elementary.freshness_anomalies

anomaly test. The anomaly test is designed to detect changes in update patterns, not absolute staleness. This is confirmed in multiple support threads, where users are advised to use a custom test or dbt's built-in source freshness for this use case [[Slack thread: staleness detection](https://slack.com/archives/C02CTC89LAX/p1684401442974139)]. How to set up a threshold-based freshness check: 1. For dbt sources: Use dbt's built-in source freshness feature, which allows you to specify a freshness threshold (e.g., 14 days). Elementary can collect and alert on these results if configured properly. Example in your

sources

YAML:

yaml    sources:      - name: spectrum        tables:          - name: ld_demand_regulation_action_events            freshness:              warn_after: {count: 14, period: day}              error_after: {count: 21, period: day}

2. Elementary Alerting on Source Freshness: - For dbt Core: After running

dbt source freshness

, run:

edr run-operation upload-source-freshness --project-dir <path to your project>

Then run

edr monitor

to send alerts [[Elementary docs: Collect dbt source freshness](https://docs.elementary-data.com/oss/guides/collect-dbt-source-freshness)]. - For dbt Cloud: Run

dbt run-operation elementary.upload_source_freshness

after

dbt source freshness

in your job [[Slack thread: source freshness alerting](https://slack.com/archives/C02CTC89LAX/p1667567840168569)]. Summary: Set your

warn_after

or

error_after

threshold to 14 days in your source freshness config. Elementary will alert you if the data is more than 2 weeks stale, provided you upload the source freshness results as described above. If you need to do this for a model (not a source), you would need to write a custom dbt test that fails if the latest timestamp is older than 14 days, as the anomaly test will not catch absolute staleness [[Slack thread: staleness detection](https://slack.com/archives/C02CTC89LAX/p1684401442974139)].

Certainly! If you want to force the

elementary.freshness_anomalies

test to fail in your current scenario (where the table has not been updated for almost 2 weeks), you need to configure the test so that the detection period covers the recent stale period, and the training period covers a time when the table was being updated regularly. This way, the test will see the recent lack of updates as an anomaly compared to the previous pattern. Key points from the knowledge sources: - The test compares the freshness in the detection period to the training period. If both periods are equally stale, the test will pass. If the training period has frequent updates and the detection period is stale, the test will fail [[Slack thread: staleness detection](https://slack.com/archives/C02CTC89LAX/p1684401442974139)]. - You can increase the test’s sensitivity to make it more likely to fail when there is a deviation [[Slack thread: sensitivity](https://slack.com/archives/C02CTC89LAX/p1703157711512959)]. Example configuration to force a failure: Suppose your table was updated daily until 2 weeks ago, and then stopped. You could set:

yaml tests:   - elementary.freshness_anomalies:       timestamp_column: 'timestamp'       time_bucket:         period: day         count: 1       detection_period:         period: day         count: 2      # Looks at the last 2 days (which are stale)       training_period:         period: day         count: 14     # Looks at the 14 days before the detection period (which had updates)       anomaly_sensitivity: 3  # Highest sensitivity       config:         severity: warn

How this works: - The test will compare the last 2 days (no updates) to the previous 14 days (with updates). - With high sensitivity, the test is more likely to flag the recent staleness as an anomaly. Important caveat: If your table has been stale for the entire training and detection period, the test will still pass, as this is now the "normal" pattern. The anomaly test only detects changes from the recent pattern, not absolute staleness [[Slack thread: staleness detection](https://slack.com/archives/C02CTC89LAX/p1684401442974139)]. If you want to experiment, try updating the training and detection periods to match your data’s update history as described above. This will help you see how the test responds to changes in freshness patterns. Let me know if you need help adjusting these settings further!

The SQL you’re seeing:

sql select   count(*) as failures,   count(*) != 0 as should_warn,   count(*) != 0 as should_error from (   with nothing as (select 1 as num)   select * from nothing where num = 2 ) dbt_internal_test

indicates that the test is not actually running the freshness anomaly logic, but instead is running a placeholder query that always returns zero failures. This typically happens when the test is skipped due to insufficient data or configuration issues. From the knowledge sources, this can occur if: - There is not enough data in the training or detection period for the test to run (for example, if the table is empty or all data is outside the configured periods). - The test is not able to find any rows matching the

timestamp_column

or the time buckets you’ve defined. - The test is in a "training" phase and not yet ready to detect anomalies (for example, if the minimum training set size is not met) [[Slack thread: training set size](https://slack.com/archives/C02CTC89LAX/p1682967620411319)]. What you can do: - Double-check that your

timestamp_column

exists and contains data within the periods defined by your

training_period

and

detection_period

. - Make sure your table actually has rows with timestamps in the expected range. - If your table has been stale for the entire training and detection period, the test will not fail, as this is now considered "normal" [[Slack thread: staleness detection](https://slack.com/archives/C02CTC89LAX/p1684401442974139)]. - If you see messages like "not enough data to calculate anomaly," it means the test is being skipped due to insufficient data [[Slack thread: training set size](https://slack.com/archives/C02CTC89LAX/p1682967620411319)]. Summary: The placeholder SQL means the test is not running its anomaly logic, likely due to insufficient or missing data in the relevant periods. This is expected behavior in such cases, as confirmed in the knowledge sources. If you want the test to run, ensure your table has enough recent data and that your configuration matches the data’s actual update pattern.