https://linen.dev logo
Join Slack
Powered by
<@U088WCP3B4P> I am working on creating a referent...
# ask-elementary-ai
j
@kapa.ai I am working on creating a referential integrity test that joins order IDs from TableA to TableB. The goal is to calculate the daily match rate, which is the percentage of order IDs in TableA that have corresponding matches in TableB. I also want to raise a warning if the match rate falls outside of a defined threshold.
k
A message from kapa.ai
New message text here
j
How do you suggest I make create the materialized view to track anamolies over time?
@kapa.ai How do you suggest I make create the materialized view to track anamolies over time?
k
A message from kapa.ai
To track anomalies in the daily match rate over time, the recommended approach is to create a dbt model (table or view) that materializes the daily match rate metric. Then, you can use Elementary’s anomaly detection tests (such as column_anomalies) on that metric. Steps: 1. Create a dbt Model for Daily Match Rate Write a dbt model (e.g., 
order_match_rate_daily.sql
) that calculates, for each day, the percentage of order IDs in TableA that have a match in TableB. For example:
Copy code
sql    with daily_matches as (      select        a.order_date,        count(a.order_id) as total_orders,        count(b.order_id) as matched_orders      from {{ ref('TableA') }} a      left join {{ ref('TableB') }} b        on a.order_id = b.order_id      group by a.order_date    )    select      order_date,      matched_orders * 1.0 / total_orders as match_rate    from daily_matches
Materialize this as a table or view in your dbt project. 2. Add an Elementary Anomaly Detection Test Use the 
elementary.column_anomalies
test on the 
match_rate
column in your new model. This will allow Elementary to monitor the match rate over time and alert you to anomalies. Example in your `schema.yml`:
Copy code
yaml    models:      - name: order_match_rate_daily        columns:          - name: match_rate            tests:              - elementary.column_anomalies:                  column_anomalies: [mean]  # or use default to monitor for spikes/drops                  timestamp_column: order_date
This setup will let Elementary track the match rate daily and detect unexpected changes or drops, alerting you when anomalies occur. You can further tune the anomaly detection parameters as needed. References: This approach is based on guidance for monitoring custom metrics and using anomaly detection on them in Elementary. You can find more details and configuration options in the documentation for [column_anomalies](https://docs.elementary-data.com/data-tests/anomaly-detection-tests/column-anomalies#test-configuration) and in the discussion about monitoring custom metrics and ratios [here](https://slack.com/archives/C02CTC89LAX/p1677193288425909). If you need to monitor a more complex metric, you can use the 
elementary.collect_metrics
test, which is designed for custom metric collection and anomaly detection as well [Metrics collection and anomaly detection](https://docs.elementary-data.com/features/anomaly-detection/metrics).
j
@kapa.ai Is this YAML correctly formatted? Whenever I run 'edr report' it doesn't recongize it as the timestamp column version: 2 models: - name: onsite_transaction_match_rate description: > Calculates the match rate between f_orders and br_session_orders for US-Ecom orders. config: elementary: timestamp_column: "est_order_completed_at" columns: - name: match_rate_percent tests: - "elementary.column_anomalies": column_anomalies: - average time_bucket: period: day count: 1 tags: ["elementary", "onsite"]
k
A message from kapa.ai
Your YAML is mostly correct, but there is a known issue where the 
timestamp_column
set at the model level under 
config: elementary:
does not always propagate down to the 
elementary.column_anomalies
test. This means that Elementary may not recognize it as the timestamp column when running anomaly detection, which matches the behavior you're seeing. Recommended Solution: Explicitly set the 
timestamp_column
inside the test configuration itself, not just at the model level. For example:
Copy code
yaml version: 2  models:   - name: onsite_transaction_match_rate     description: >       Calculates the match rate between f_orders and br_session_orders for US-Ecom orders.     columns:       - name: match_rate_percent         tests:           - elementary.column_anomalies:               column_anomalies:                 - average               timestamp_column: est_order_completed_at               time_bucket:                 period: day                 count: 1               tags: ["elementary", "onsite"]
Why? This is a documented behavior: > The 
timestamp_column
does not propagate down to the 
column_anomalies
test. If you move the 
timestamp_column
under the test config, then the 
timestamp_column
config variable is passed into the test [Slack forum](https://slack.com/archives/C02CTC89LAX/p1706795086121929). Summary: - Move 
timestamp_column
into the test block for 
elementary.column_anomalies
. - This should resolve the issue with Elementary not recognizing the timestamp column. Let me know if you need further help!
j
@kapa.ai So for any referential integrity testing, I would need to materialize a model? There is no native elementary test I can use for match rates?
k
A message from kapa.ai
Based on the available knowledge sources, Elementary does not provide a native, out-of-the-box test specifically for calculating match rates or referential integrity (i.e., the percentage of keys in one table that exist in another) as a built-in test type. The recommended approach is to create a dbt model that materializes the metric you want to track (such as daily match rate), and then apply Elementary’s anomaly detection tests (like 
column_anomalies
) to that metric in the materialized model. This approach is confirmed in community discussions and support responses, where users are advised to create custom models for such metrics and then use Elementary’s anomaly detection on those models [Anomaly detection on custom metrics](https://slack.com/archives/C02CTC89LAX/p1677193288425909). Elementary does support all dbt tests, so you can write a custom dbt test for referential integrity, and Elementary will capture and report on its results. However, for ongoing anomaly detection or match rate monitoring, materializing the metric in a model and then testing it is the suggested workflow. In summary: - There is no native Elementary test specifically for match rates or referential integrity. - You should materialize a model with your metric and then use Elementary’s anomaly detection tests on it. If you need to monitor referential integrity as a pass/fail test (not as a metric), you can use a custom dbt test, and Elementary will report on its results, but for match rate trends and anomaly detection, materializing a model is required.
j
@kapa.ai if I want to clear results for a specific test from my elementary tables, how would I go about doing that?
k
A message from kapa.ai
To clear results for a specific test from your Elementary tables, you should manually delete the relevant rows from the 
elementary_test_results
table (and potentially other related tables, depending on your use case). You can identify the rows to delete by filtering on columns such as the test name, test unique ID, or other identifying fields. For example, you might run a SQL statement like:
Copy code
sql DELETE FROM elementary_test_results WHERE test_name = 'your_test_name';
Or, if you have the test unique ID:
Copy code
sql DELETE FROM elementary_test_results WHERE test_unique_id = 'your_test_unique_id';
This approach is confirmed in multiple community discussions, where users are advised to delete rows directly from 
elementary_test_results
to remove unwanted or outdated test results. This is the standard method for clearing specific test results, as there is no built-in CLI or automated feature for this in Elementary at this time [see example](https://slack.com/archives/C02CTC89LAX/p1687167849673609), [another example](https://slack.com/archives/C02CTC89LAX/p1665667454203099). Note: - Be careful when deleting data directly from Elementary tables, especially in production environments. - If your test results are also present in other tables (such as 
data_monitoring_metrics
for metrics/anomalies), you may want to clear those as well using similar filters. Let me know if you need help crafting a specific query!
j
@kapa.ai - Whats the difference betweeen null_percent and missing_percent?
k
A message from kapa.ai
The difference between 
null_percent
and 
missing_percent
is: - 
null_percent
 monitors the percentage of only 
null
values in a column out of the total number of rows. - 
missing_percent
 monitors the percentage of empty values (such as 
''
, strings with only spaces, or 
nulls
) in a column out of the total number of rows. So, 
missing_percent
is broader and includes both empty strings and nulls, while 
null_percent
only counts nulls. This distinction is especially relevant for string columns, where empty strings and nulls are both possible but have different meanings in your data quality context [Slack forum explanation](https://slack.com/archives/C02CTC89LAX/p1656075987045489).
j
@kapa.ai How can I look create a test where it fails if null_percent is above 10%?
k
A message from kapa.ai
To create a test that fails if 
null_percent
is above 10%, you should use a standard dbt test (such as a custom SQL test or a dbt-utils test) with a threshold condition, since Elementary’s anomaly detection tests are designed to detect significant changes over time rather than enforce static thresholds. Elementary’s anomaly detection (
column_anomalies
or 
all_columns_anomalies
) will alert you if the 
null_percent
is anomalous compared to historical values, but it does not natively support static threshold-based failure (e.g., "fail if above 10%") [see discussion](https://slack.com/archives/C02CTC89LAX/p1693358780626649). Recommended approach: - Use a custom dbt test or dbt-utils test to enforce a static threshold. - Use Elementary’s anomaly detection to catch unexpected changes or spikes in 
null_percent
over time. Example of a custom dbt test for a static threshold:
Copy code
sql -- tests/null_percent_below_10.sql SELECT * FROM {{ ref('your_model') }} WHERE (your_column IS NULL)::int GROUP BY 1 HAVING COUNT(*) * 1.0 / (SELECT COUNT(*) FROM {{ ref('your_model') }}) > 0.10
Then, add this test in your 
schema.yml
for the relevant column. Summary: - Use a custom dbt test for static thresholds. - Use Elementary anomaly detection for trend-based alerts. If you need help with the exact SQL or YAML, let me know!
j
@kapa.ai If I use dbt cloud, do I need to use Elementary Cloud to get EDR reports?
k
A message from kapa.ai
No, you do not need to use Elementary Cloud to get EDR (Elementary Data Report) reports if you use dbt Cloud. You can use the open-source Elementary CLI to generate reports with dbt Cloud. However, since dbt Cloud does not allow running external tools like the Elementary CLI directly, you need to orchestrate the CLI separately—commonly on your local machine, with Airflow, GitHub Actions, or another orchestrator. The typical workflow is: 1. Deploy the Elementary dbt package in your dbt Cloud project. 2. Use dbt Cloud to generate a connection profile (using 
dbt run-operation elementary.generate_elementary_cli_profile
). 3. Copy the generated profile to the machine where you will run the Elementary CLI. 4. Run the 
edr
commands from that machine to generate reports. Elementary Cloud is an option if you want a managed solution, but it is not required for generating EDR reports with dbt Cloud [dbt cloud integration guide](https://docs.elementary-data.com/oss/integrations/dbt), [Slack discussion](https://slack.com/archives/C02CTC89LAX/p1672946793881909), [another Slack discussion](https://slack.com/archives/C02CTC89LAX/p1678066213782359).
30 Views
Open in Slack
PreviousNext
Powered by