https://linen.dev logo
Join Slack
Powered by
<@U088WCP3B4P> how to store percentage for errors ...
# ask-elementary-ai
d
@kapa.ai how to store percentage for errors along with count of error or failed test records metrics into elementary tables.
k
A message from kapa.ai
Based on the knowledge sources, here's how you can work with error percentages and failed test record counts in Elementary: ## Current Capabilities Elementary stores the count of failures in the 
elementary_test_results
table in a column called 
failures
. Additionally, there's a 
failed_row_count
column that was added to help calculate data quality scores, as mentioned in the [Elementary 0.9.1 release notes](https://docs.elementary-data.com/oss/release-notes/releases/0.9.1). ## Calculating Percentages However, Elementary does not natively store percentage values for test failures. According to discussions in the community, there are some important considerations: 1. The number of failures is not always the same as the number of failed rows. As explained in [this Slack discussion](https://slack.com/archives/C02CTC89LAX/p1659034719181449), for some tests (like unique tests), the failures count represents distinct values that failed, not the total number of rows affected. 2. To calculate percentages, you would need both: - The number of failed rows - The total number of rows tested ## Workarounds Based on community solutions, here are approaches you can take: ### 1. Use 
fail_calc
Configuration You can use dbt's [
fail_calc
](https://slack.com/archives/C02CTC89LAX/p1659034719181449) parameter to customize what gets stored as failures. For example, for a unique test:
Copy code
yaml - unique:     config:       fail_calc: "sum(n_records)"
### 2. Query Elementary Tables Directly Create a custom dbt model that queries 
elementary_test_results
or 
dbt_run_results
tables and calculates percentages using regex to extract failure counts from messages, combined with total row counts from your tables. ### 3. Use Quality Dimension Fields For common tests, Elementary automatically populates the 
failed_row_count
field. You can use this along with your table's total row count to calculate percentages in downstream queries or dashboards. Note: As mentioned in [this recent discussion](https://slack.com/archives/C02CTC89LAX/p1738682683591499), Elementary doesn't currently provide percentage calculations directly, and you would need to implement this manually by querying both failed rows and total row counts.
d
@kapa.ai I have elementary_test_results table populated with data but test_result_rows doesn't have any data I have set test_sample_row_count: 10000
k
A message from kapa.ai
Based on the knowledge sources, there are several common reasons why 
test_result_rows
might be empty even though 
elementary_test_results
is populated. Here are the solutions: ## 1. dbt 1.8+ Materialization Issue (Most Common) If you're using dbt 1.8 or above, there was a change in materializations that affects 
test_result_rows
. Add this flag to your `dbt_project.yml`:
Copy code
yaml flags:   require_explicit_package_overrides_for_builtin_materializations: false
This is documented in the [Elementary quickstart guide](https://docs.elementary-data.com/oss/quickstart/quickstart-cli-package#important-allowing-elementary-to-override-dbts-default-materializations-relevant-from-dbt-1-8) and has resolved the issue for many users, as mentioned in [multiple](https://slack.com/archives/C02CTC89LAX/p1741861180336069) [Slack discussions](https://slack.com/archives/C02CTC89LAX/p1734111312018389). ## 2. Large Row Count Issue Since you've set 
test_sample_row_count: 10000
, you might be hitting a Snowflake Connector limit. According to [this Slack discussion](https://slack.com/archives/C02CTC89LAX/p1739372274940839), Elementary can process 1,000 rows without issues, but at 10,000 rows, errors occur with the Snowflake Connector. Workaround: Add these variables to your `dbt_project.yml`:
Copy code
yaml vars:   insert_rows_method: chunk   dbt_artifacts_chunk_size: <number lower than 10,000>
See [GitHub issue #1419](https://github.com/elementary-data/elementary/issues/1419) for more details. ## 3. Correct Variable Placement Ensure 
test_sample_row_count
is placed directly under 
vars
, not under 
models.elementary
. The correct configuration is:
Copy code
yaml vars:   test_sample_row_count: 10000
As clarified in [this Slack thread](https://slack.com/archives/C02CTC89LAX/p1704277382272089). ## Next Steps 1. First, try adding the materialization flag if you're on dbt 1.8+ 2. If that doesn't work, reduce your 
test_sample_row_count
and add the chunking configuration 3. Verify your variable placement in 
dbt_project.yml
Let me know which dbt version and data warehouse you're using if these solutions don't resolve the issue!
d
@kapa.ai how is Fail Rate calculated in UI?
k
A message from kapa.ai
Based on the knowledge sources, I can provide information about how fail rate is calculated and displayed in Elementary's UI: ## Fail Rate Calculation According to [this Slack discussion](https://slack.com/archives/C02CTC89LAX/p1682954226118479), Elementary calculates fail rate to help you detect which tests have the highest failure rate. The fail rate is designed to show you the overall failure pattern of a test over time. ## Important Considerations The Elementary team acknowledged some nuances with the fail rate calculation: - The fail rate shows the overall failure percentage across all test runs - There was discussion about potentially excluding the last result from the fail rate calculation and showing it separately, so you could see both: - The current/last test status - The historical fail rate This would help distinguish between: - Tests that are currently failing - Tests that are historically flaky (fail intermittently) ## Context The fail rate metric is particularly useful for identifying flaky tests - tests that fail inconsistently over time. By sorting tests by fail rate, you can prioritize which tests need attention based on their reliability patterns. However, the knowledge sources don't provide the exact formula used for the calculation. If you need more specific details about the calculation methodology, I'd recommend checking the Elementary documentation or reaching out to the Elementary team directly.
d
@kapa.ai can we use fail_calc data_test calculation to return percentage of rows failed along with number of rows failed?
k
A message from kapa.ai
Based on the knowledge sources, 
fail_calc
has limitations that prevent it from returning both percentage and count simultaneously. Here's what you need to know: ## Limitations of 
fail_calc
According to [this GitHub issue](https://github.com/elementary-data/elementary/issues/1453), 
fail_calc
must return a single value that dbt uses to determine test pass/fail status. The value returned by 
fail_calc
becomes the 
failures
field, which: 1. Must be an integer (or at least dbt's base result class requires it to be an integer) 2. Is used for the 
should_warn
and 
should_error
comparisons 3. Cannot return multiple values simultaneously ## What You Can Do ### Option 1: Use 
fail_calc
for Percentage Only You can configure 
fail_calc
to return a percentage (as a decimal), as shown in [this example](https://github.com/elementary-data/elementary/issues/1453):
Copy code
yaml - name: test_name   test_name: dbt_utils.not_null_proportion   column_name: "some_column"   config:     fail_calc: 'coalesce(not_null_proportion, 0)'     severity: warn     warn_if: <= 0.98     error_if: <= 0.95
However, this approach has issues with Elementary's schema validation expecting integers. ### Option 2: Use 
fail_calc
for Row Count For tests like 
unique
, you can use 
fail_calc
to get the actual failed row count instead of distinct values:
Copy code
yaml - unique:     config:       fail_calc: "sum(n_records)"
As discussed in [this Slack thread](https://slack.com/archives/C02CTC89LAX/p1659034719181449). ## Recommended Approach You cannot return both values through 
fail_calc
. Instead, as mentioned in the [previous discussion](https://slack.com/archives/C02CTC89LAX/p1659034719181449), the recommended approach is to: 1. Use 
fail_calc
to store the failed row count 2. Create a custom dbt model that queries 
elementary_test_results
and calculates percentages by combining: - The 
failures
or 
failed_row_count
from Elementary tables - Total row counts from your source tables This allows you to have both metrics available for downstream analysis and dashboards.
d
@kapa.ai is results from https://github.com/calogica/dbt-expectations is stored in elementary tables?
k
A message from kapa.ai
Yes, results from dbt-expectations tests are stored in Elementary tables. Here's what you need to know: ## Storage in Elementary Tables According to [this Slack discussion](https://slack.com/archives/C02CTC89LAX/p1678359915920049), if you import dbt-expectations and use it, the results will show in the report and alerts. Elementary has integrated support for dbt-expectations, including: - Default descriptions - Test names - The icon that the maintainer uses for the project ## Which Tables Store the Results The test results from dbt-expectations are stored in the same Elementary tables as all other dbt tests: 1. 
elementary_test_results
 - Contains all test results from dbt tests, including dbt-expectations, Elementary tests, and other packages like dbt_utils, as explained in [this Slack thread](https://slack.com/archives/C02CTC89LAX/p1662059505282089). 2. 
test_result_rows
 - Stores failed row samples from dbt-expectations tests. As clarified in [this discussion](https://slack.com/archives/C02CTC89LAX/p1687443933741339), dbt-expectations tests are stored in 
test_result_rows
with a default sample size of 5 rows (configurable via 
test_sample_row_count
). ## Important Notes - You need to import dbt-expectations separately to your dbt project - it's not included as part of Elementary's deployment - The [dbt Test Hub](https://www.elementary-data.com/dbt-test-hub) includes dbt-expectations as one of the supported packages - Elementary treats dbt-expectations tests as first-class citizens, just like native dbt tests So yes, once you have dbt-expectations installed in your dbt project, all test results will automatically be captured and stored in Elementary's tables through the on-run-end hooks.
d
@kapa.ai how to calculate unexpected_percent does the dbt-expectations generate this metrics as test results output
k
A message from kapa.ai
Based on the knowledge sources provided, I don't have specific information about how 
unexpected_percent
is calculated or whether dbt-expectations generates this metric as part of its test results output. ## What I Can Confirm The knowledge sources confirm that: 1. dbt-expectations test results are stored in Elementary tables - specifically in 
elementary_test_results
and 
test_result_rows
, as mentioned in [this Slack discussion](https://slack.com/archives/C02CTC89LAX/p1687443933741339). 2. Elementary stores test results in a specific format - The 
test_result_rows
table contains raw output from tests as a JSON string in the 
result_row
column, as explained in [this thread](https://slack.com/archives/C02CTC89LAX/p1687443933741339). ## What's Not Clear The knowledge sources do not explicitly mention: - Whether 
unexpected_percent
is a standard field in dbt-expectations test output - How to calculate or extract 
unexpected_percent
from test results - The specific structure of dbt-expectations test result JSON ## Recommendation To find out if 
unexpected_percent
is included in dbt-expectations test results: 1. Query the 
test_result_rows
table for your dbt-expectations tests 2. Examine the JSON structure in the 
result_row
column 3. You can join with 
elementary_test_results
using 
elementary_test_results_id
to identify specific tests For detailed information about dbt-expectations output structure and available metrics, I'd recommend consulting the [dbt-expectations documentation](https://github.com/calogica/dbt-expectations) directly, as the Elementary knowledge sources don't contain this level of detail about dbt-expectations' internal metrics.
d
@kapa.ai can you help me with how to calculate percentage of errors or failuers base on elementary tables.
k
A message from kapa.ai
Based on the knowledge sources, here's how you can calculate the percentage of errors or failures using Elementary tables: ## Available Data in Elementary Tables Elementary stores test failure information in the 
elementary_test_results
table, which includes: - 
failures
 - The count of failures for each test - 
failed_row_count
 - The number of failed rows (added in later versions) However, as explained in [this Slack discussion](https://slack.com/archives/C02CTC89LAX/p1686216808788369), Elementary does not store the total number of rows processed by default, which you need to calculate percentages. ## Calculating Failure Percentages To calculate the percentage of failures, you need both: 1. Number of failed rows (from Elementary tables) 2. Total number of rows (from your source tables) ### Approach 1: Custom dbt Model Create a custom dbt model that joins Elementary test results with your source tables. As shared by a community member in [this thread](https://slack.com/archives/C02CTC89LAX/p1659034719181449):
Copy code
sql -- Example approach: Join test results with table row counts SELECT      etr.table_name,     etr.column_name,     etr.test_name,     etr.failures,     etr.failed_row_count,     -- Add your table's total row count here     -- Calculate percentage     (etr.failed_row_count * 100.0 / total_rows) as failure_percentage FROM elementary.elementary_test_results etr -- Join with your source tables to get total row counts
### Approach 2: Use Elementary's Column Anomalies Test For null rate percentages specifically, you can use Elementary's built-in test, as mentioned in [this discussion](https://slack.com/archives/C02CTC89LAX/p1686216808788369):
Copy code
yaml tests:   - elementary.column_anomalies:       column_anomalies:          - null_rate
This calculates the percentage of null entries in a column automatically. ## Important Considerations As explained in [this Slack thread](https://slack.com/archives/C02CTC89LAX/p1659034719181449): - The number of failures is not always the same as the number of failed rows. For example: - A 
unique
test returns the number of distinct IDs that have duplicates, not the total number of duplicate rows - To get actual row counts for unique tests, use 
fail_calc: "sum(n_records)"
## Recommended Solution 1. Query 
elementary_test_results
for failure counts 2. Join with your source tables to get total row counts at the time of testing 3. Calculate percentages in a downstream dbt model or BI tool 4. Store these calculated percentages in a custom table for tracking over time As noted in [this recent discussion](https://slack.com/archives/C02CTC89LAX/p1738682683591499), Elementary doesn't provide percentage calculations directly, so you'll need to implement this manually by combining Elementary's failure data with your table metrics.
36 Views
Open in Slack
PreviousNext
Powered by