https://linen.dev logo
Join Slack
Powered by
<@U088WCP3B4P> name: sales_backlog__parquet_common...
# ask-elementary-ai
s
@kapa.ai name: sales_backlog__parquet_common_refined4 data_tests: - elementary.volume_anomalies: anomaly_direction: both ignore_small_changes: spike_failure_percent_threshold: 6 drop_failure_percent_threshold: 6 tags: ["elementary", "daily_elementary", "refined4", "common_refined4"] columns: - name: "salesbacklogskey" quote: true data_tests: - unique: config: severity: error error_if: ">1" warn_if: ">1" - not_null - name: "%companynumber" quote: true data_tests: - not_null - name: "%businessunitskey" quote: true data_tests: - not_null - name: "%part number" quote: true data_tests: - not_null - name: "%customernumber" quote: true data_tests: - not_null - name: "%datekey" quote: true data_tests: - not_null - name: "%itembranchskey" quote: true data_tests: - not_null - name: "amount extended price base currency" quote: true tests: - elementary.column_anomalies: column_anomalies: [null_count, null_percent] #timestamp_column: to_date("%datekey",'yyyymmdd') anomaly_sensitivity: 2 anomaly_direction: both ignore_small_changes: spike_failure_percent_threshold: 5 drop_failure_percent_threshold: 5 tags: ["elementary", "daily_elementary", "refined4", "common_refined4","shobia"] im getting the error without timestamp Database Error in test elementary_column_anomalies_sales_backlog__parquet_common_refined4_both__2__null_count__null_percent___amount_extended_price_base_currency___5__5 (models\refined4\common\quality_check\_source_quality_check_common_refined4.yml) syntax error at or near "from" in context "as start_bucket_in_data, from", at line 26, column 13 with tiemstamp it is passing but saying not found enough data to calculate column anomolies
k
A message from kapa.ai
The error you are seeing:
Copy code
syntax error at or near "from" in context "as start_bucket_in_data,               from", at line 26, column 13
is a known issue in Elementary when running 
elementary.column_anomalies
tests without a 
timestamp_column
. The root cause is an extra comma in the generated SQL when no timestamp is provided, which leads to invalid syntax in some warehouses (notably Databricks and Trino, but it can affect others as well). This has been reported and is being addressed by the Elementary team. The current workaround is to always provide a 
timestamp_column
if possible, which avoids the problematic code path and resolves the syntax error. See the discussion and confirmation of the bug here: [Github Issue: Failure in column_anomalies test when column_timestamp isn't provided](https://github.com/elementary-data/elementary/issues/1554) and [Slack thread](https://slack.com/archives/C02CTC89LAX/p1716306300184349). When you add a 
timestamp_column
and the test passes but reports "not enough data to calculate column anomalies," this means Elementary does not have enough historical data in the time buckets defined by your timestamp column to perform anomaly detection. By default, Elementary requires at least 7 training data points (e.g., 7 days if your bucket is daily) to start calculating anomalies. If your data is recent or the table doesn't have enough history, you'll see this message until enough data accumulates. You can either wait for more data to be collected or reduce the training period for testing purposes, but be aware that this may reduce the accuracy of anomaly detection. More details are available in the [Anomaly Tests Troubleshooting Guide](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/Anomaly-troubleshooting-guide) and [timestamp_column documentation](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/timestamp-column). Summary of what you should do: - Always provide a 
timestamp_column
to avoid the SQL syntax error. - Ensure your timestamp column is of the correct type (date or timestamp, not string). - Make sure you have enough historical data in your table for the anomaly detection to work, or reduce the training period for demonstration/testing. If you cannot provide a 
timestamp_column
due to data limitations, you will encounter the SQL error until the bug is fixed in a future Elementary release. Let me know if you need help with configuring the timestamp or adjusting the training period!
s
@kapa.ai we have more than 3 million records from 2019 data the n why reporting not enough data, it is a date field not a time stamp field
k
A message from kapa.ai
If you have more than 3 million records from 2019 and are still seeing "not enough data to calculate anomaly score," the most likely reasons are: 1. Time Buckets and Data Distribution: Even if you have a large volume of data, Elementary's anomaly detection relies on how your data is distributed across time buckets (e.g., days or months). If your 
date
field is used as the 
timestamp_column
, Elementary will group data by this field. If there are gaps in the dates, or if most records are concentrated in a few dates, you may not have enough distinct time buckets for the training period. For example, if your training period is 14 days, you need at least 14 unique date values with data in your table. If some days are missing, you won't have enough buckets for training, and the test will not run as expected [Anomaly Tests Troubleshooting Guide](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/Anomaly-troubleshooting-guide). 2. Column Type: A 
date
type column is supported and should work as a 
timestamp_column
. However, if the column contains nulls or is not consistently formatted, this can cause issues. Make sure there are no nulls and that the column is truly of type 
date
(not string) [Slack forum](https://slack.com/archives/C02CTC89LAX/p1737832587221769). 3. Training Period Configuration: By default, Elementary requires at least 7 (sometimes 14) time buckets for training. If your configuration uses a longer training period (e.g., 30 days), you need at least 30 unique date buckets with data. You can try reducing the 
min_training_set_size
to a lower value for testing purposes, but be aware this may affect anomaly detection accuracy [Anomaly Tests Troubleshooting Guide](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/Anomaly-troubleshooting-guide). 4. Data Collection Verification: You can check how metrics are being collected by running a query like:
Copy code
sql    SELECT        metric_timestamp,        COUNT(*) as metrics_per_bucket    FROM your_schema.data_monitoring_metrics    WHERE table_name = 'your_table'    GROUP BY metric_timestamp    ORDER BY metric_timestamp DESC;
This will show you how many time buckets are being created and if there are gaps in your data [Anomaly Tests Troubleshooting Guide](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/Anomaly-troubleshooting-guide). In summary: Having millions of records is not enough if they are not distributed across enough unique time buckets. Check your 
date
column for gaps, nulls, or formatting issues, and verify the number of unique buckets in 
data_monitoring_metrics
. Adjust your training period if needed. If you need more help, please share how your data is distributed across the 
date
field and your test configuration.
s
@kapa.ai how to adjust the training period and how to do it, so u r saying i sound run the dbt test for 7 days or the data should old for 7 days
k
A message from kapa.ai
To adjust the training period in Elementary, you need to set the 
training_period
parameter in your test configuration. This parameter controls how much historical data (in terms of time buckets, like days or weeks) is used to train the anomaly detection model. How to adjust the training period: Add or modify the 
training_period
section in your test YAML, for example:
Copy code
yaml models:   - name: your_model_name     tests:       - elementary.volume_anomalies:           timestamp_column: your_date_column           training_period:             period: day             count: 7
- 
period
can be 
day
, 
week
, or 
month
. - 
count
is the number of periods (e.g., 7 days). What does this mean? - You need at least 7 unique days of data in your table for the test to start detecting anomalies if you set 
count: 7
. - The data itself does not need to be "old" for 7 days, but you must have data that covers at least 7 different days in your 
timestamp_column
. - You do not need to run the dbt test for 7 days in a row if your data already covers 7 unique days. If your data is static and already contains enough history, the test can use it immediately. If you change the training period after running tests previously: You should do a full refresh of the metrics by running:
Copy code
bash dbt run --select data_monitoring_metrics --full-refresh
Then run your Elementary tests again to collect data for the new training period [Anomaly Tests Troubleshooting Guide](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/Anomaly-troubleshooting-guide). For more details, see the [training_period documentation](https://docs.elementary-data.com/data-tests/anomaly-detection-configuration/training-period). Summary: - Set 
training_period
in your test config to control how much history is used. - You need data covering at least as many unique time buckets as your training period. - You do not need to run the test for 7 days if your data already covers 7 days. Let me know if you need an example for your specific test!
26 Views
Open in Slack
PreviousNext
Powered by