https://linen.dev logo
Join Slack
Powered by
<@U088WCP3B4P> is there a way to chunk the metrics...
# ask-elementary-ai
a
@kapa.ai is there a way to chunk the metrics uploading to the temp tables so that I don't run into the database errors?
k
A message from kapa.ai
Yes, there is a way to chunk the metrics uploading in Elementary to help avoid database errors, especially with BigQuery. You can control the size of the insert queries by setting the following variables in your `dbt_project.yml`:
Copy code
yaml vars:   insert_rows_method: chunk   dbt_artifacts_chunk_size: 500  # or a lower value if you still encounter errors
This configuration tells Elementary to upload artifacts in smaller batches (chunks), which can help prevent errors related to query complexity or resource limits. If you continue to see errors, try reducing the 
dbt_artifacts_chunk_size
further (e.g., 250, 100, etc.) until the errors stop occurring. This approach is specifically designed to address issues like "Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex" in BigQuery and similar platforms. The chunking only affects Elementary's uploads and not all dbt insertions, so it is safe to use for this purpose [see example discussion](https://slack.com/archives/C02CTC89LAX/p1668682659957299). If you are still experiencing issues after chunking, you may also want to adjust the 
query_max_size
variable or experiment with different insert methods, as discussed in several support threads [see here](https://github.com/elementary-data/elementary/issues/1689).
a
@kapa.ai can I add it in as a command line vars option?
k
A message from kapa.ai
You can technically pass variables like 
dbt_artifacts_chunk_size
and 
insert_rows_method
as command line variables using the 
--vars
option with dbt commands. However, according to Elementary support, changing the sample size (and by extension, similar variables) from the CLI does not work for all use cases because some sampling and artifact collection happens as part of the 
dbt test
runs, not when you run the CLI directly. For chunking and artifact upload settings, the recommended and supported way is to set these variables in your 
dbt_project.yml
file, not via the command line. Here’s a relevant quote: > No, this can only be changed in the dbt package. The reason is that the samples are collected as part of your 
dbt test
runs and not when you run the CLI. > Hope that makes sense 🙏 [Slack thread](https://slack.com/archives/C02CTC89LAX/p1682189527028859) So, while you can try passing them via 
--vars
, for reliable and supported behavior, you should set them in your 
dbt_project.yml
.
a
@kapa.ai do I add it into the vars configuration for all of dbt or under the elementary configurations?
k
A message from kapa.ai
You should add these variables under the top-level vars section in your dbt_project.yml, not under an elementary-specific configuration block. Several users have reported issues when placing these variables under an elementary or models section, and Elementary maintainers have clarified that they must be set under vars for them to take effect. Here’s an example of the correct placement:
Copy code
yaml vars:   insert_rows_method: chunk   dbt_artifacts_chunk_size: 500   query_max_size: 100000
Placing them elsewhere (such as under an elementary: block) will not work as intended. This has been confirmed in support threads and GitHub issues, where users resolved their issues by moving these settings to the top-level vars section of dbt_project.yml [see discussion](https://github.com/elementary-data/elementary/issues/1689).
a
@kapa.ai even if I disable the artifacts upload: ”disable_dbt_artifacts_autoupload”: true, this should still work right?
k
A message from kapa.ai
If you set 
disable_dbt_artifacts_autoupload: true
, this will disable the automatic upload of dbt artifacts at the end of your dbt runs (i.e., via the on-run-end hook). However, you can still upload artifacts manually by running the relevant Elementary models, for example with:
Copy code
dbt run --select elementary.edr.dbt_artifacts
This approach allows you to control when the artifacts are uploaded, such as only during deployments, rather than after every dbt run. The chunking configuration (e.g., 
dbt_artifacts_chunk_size
, 
insert_rows_method: chunk
) will still apply when you manually run the artifact upload models, so chunking will work as expected even if automatic uploads are disabled [see discussion](https://slack.com/archives/C02CTC89LAX/p1689186975724739) [see also](https://docs.elementary-data.com/dbt/on-run-end_hooks#can-i-disable-the-on-run-end-hooks%3F). In summary: - Disabling autoupload prevents automatic artifact uploads. - Manual uploads (via 
dbt run --select elementary.edr.dbt_artifacts
) will still work and respect your chunking settings.
a
@kapa.ai Why do I get the database error even when I disable the artifacts upload?
k
A message from kapa.ai
If you are still getting a database error even after setting 
disable_dbt_artifacts_autoupload: true
, it is likely because other parts of Elementary's on-run-end hook are still running and attempting to upload data. Disabling artifact autoupload only prevents the metadata models (like 
dbt_models
, 
dbt_tests
, etc.) from being updated automatically, but it does not disable all uploads or all on-run-end hook activity by default. To fully disable all uploads (including run results, test results, and invocation uploads), you need to set additional variables:
Copy code
yaml vars:   disable_dbt_artifacts_autoupload: true   disable_run_results: true   disable_tests_results: true   disable_dbt_invocation_autoupload: true
This is confirmed in several support threads, where users found that setting only 
disable_dbt_artifacts_autoupload: true
did not stop all database activity, and additional variables were required to fully prevent uploads and avoid related errors [see example](https://slack.com/archives/C02CTC89LAX/p1711530856757159) [see also](https://slack.com/archives/C02CTC89LAX/p1697606690660449). In summary: - Disabling only artifact autoupload does not stop all database writes. - You must disable all relevant result and invocation uploads to fully prevent database activity from Elementary’s hooks.
a
I have all of those disabled and I still get the databse error
@kapa.ai I have all of those disabled and I still get the databse error
k
A message from kapa.ai
If you have set all of the following variables to disable uploads:
Copy code
yaml vars:   disable_dbt_artifacts_autoupload: true   disable_run_results: true   disable_tests_results: true   disable_dbt_invocation_autoupload: true
and you are still experiencing database errors related to Elementary, this is a known issue reported by several users. The on-run-end hook itself will still execute, but with all these variables set, it should not perform any uploads or database writes. However, some users have observed that the hook still runs and may attempt actions that result in errors, especially if there are Elementary tests or models present in your project or if there are cached/corrupted states from previous runs. From the knowledge sources, there is no explicit solution provided for this scenario. Support staff have confirmed that with all disable variables set, the hook should not perform any actions: > The hook runs anyway, and check your vars. > If all are set to disabled, nothing happens: > — [Slack thread](https://slack.com/archives/C02CTC89LAX/p1669625539486519) If you are still seeing errors, it may be due to: - Elementary-specific tests or models still being referenced in your project files. - Cached or residual state from previous runs. - A bug or edge case not covered in the documentation or support threads. Unfortunately, the knowledge sources do not provide a definitive answer or workaround for this situation beyond what you have already tried. If the issue persists, it may be necessary to remove Elementary-specific tests/models from your project or seek direct support from the Elementary team for further troubleshooting. If you have already removed all Elementary references and still see errors, please provide more details or error messages for further investigation.
a
if I disable these will the elementary metrics still run ? @kapa.ai
k
A message from kapa.ai
a
if I disable these will the tests still run? @kapa.ai and just not save the results that's what I want
k
If you disable all of the following variables:
Copy code
yaml vars:   disable_dbt_artifacts_autoupload: true   disable_run_results: true   disable_tests_results: true   disable_dbt_invocation_autoupload: true
the Elementary on-run-end hooks will not upload metadata, run results, test results, or invocation information. However, this does not disable the execution of Elementary tests themselves (such as anomaly or schema change tests) if you run them directly. The metrics tables (like 
data_monitoring_metrics
) that support anomaly detection tests are populated as part of running those tests, not as part of the on-run-end hook uploads. So, if you run Elementary anomaly tests, the metrics will still be collected and written to the relevant tables, regardless of the on-run-end upload settings. Disabling the uploads only affects the automatic population of metadata and results, not the core test execution and metrics collection. This is confirmed by support staff who note that disabling uploads does not prevent the anomaly tests from writing to their required tables, and you will still see database activity related to those tests if you run them [see discussion](https://slack.com/archives/C02CTC89LAX/p1701710053524279). Summary: - Disabling the upload variables stops automatic uploads of artifacts and results. - Running Elementary tests (like anomaly detection) will still collect and upload metrics to their tables. - To fully prevent all database activity, you would need to avoid running Elementary tests/models entirely.
A message from kapa.ai
Yes, if you disable the following variables in your `dbt_project.yml`:
Copy code
yaml vars:   disable_dbt_artifacts_autoupload: true   disable_run_results: true   disable_tests_results: true   disable_dbt_invocation_autoupload: true
the Elementary tests will still run, but the results and artifacts will not be uploaded or saved by the on-run-end hooks. This means you can execute the tests and see their output in your dbt run logs, but the results will not be stored in the Elementary tables for reporting or further analysis. This approach is specifically recommended for scenarios where you want to run tests but avoid database writes from Elementary’s reporting and artifact upload features [see discussion](https://slack.com/archives/C02CTC89LAX/p1708674780847219) [see also](https://docs.elementary-data.com/dbt/on-run-end_hooks#can-i-disable-the-on-run-end-hooks%3F). Summary: - Tests will run. - Results/artifacts will not be saved to Elementary tables.
a
@kapa.ai but then why am I seeing database errors for the data_monitoring_metrics evne when I disable all the uploads
k
A message from kapa.ai
You are still seeing database errors for 
data_monitoring_metrics
even after disabling all the upload variables because running Elementary anomaly detection tests (and some other Elementary tests) will still attempt to write to the 
data_monitoring_metrics
table, regardless of the upload settings. The variables like 
disable_dbt_artifacts_autoupload
, 
disable_run_results
, 
disable_tests_results
, and 
disable_dbt_invocation_autoupload
only control the on-run-end hook uploads and do not prevent the anomaly tests themselves from writing their results to the database. This is confirmed by Elementary support: > "Inserts to 
data_monitoring_metrics
are a part of the anomaly tests - they cannot be disabled if you wish to use elementary anomaly tests... you will still see the post-run-end hook running, as its all running in the same hook, but the dbt artifacts will not be uploaded" [Slack thread](https://slack.com/archives/C02CTC89LAX/p1701710053524279) So, if you are running Elementary tests (like anomaly detection), they will still try to write to the 
data_monitoring_metrics
table, which can cause database errors if you do not have the necessary permissions or if the table does not exist in your environment. The only way to fully prevent these database writes is to avoid running the Elementary tests themselves in that environment.
a
how can I not get those errors? @kapa.ai
k
A message from kapa.ai
To avoid database errors related to 
data_monitoring_metrics
when running dbt with Elementary, you have two main options based on the knowledge sources: 1. Disable Elementary tests/models entirely in the environment where you don't want database writes. The upload-disabling variables (
disable_dbt_artifacts_autoupload
, 
disable_run_results
, 
disable_tests_results
, 
disable_dbt_invocation_autoupload
) only prevent artifact/result uploads, but do not stop the anomaly tests themselves from writing to 
data_monitoring_metrics
. If you want to prevent any database writes (and thus avoid errors), you need to disable the Elementary package or exclude its tests/models from running in that environment. You can do this by adding the following to your `dbt_project.yml`:
Copy code
yaml    models:      elementary:        enabled: "{{ target.name in ['prod', 'stage'] }}"
Or, for a more restrictive approach, use 
--exclude elementary.*
in your dbt command to skip all Elementary models and tests in non-prod environments [see discussion](https://slack.com/archives/C02CTC89LAX/p1702031013575389) [see also](https://slack.com/archives/C02CTC89LAX/p1717488789811829). 2. Ensure the required tables and permissions exist. If you do want to run Elementary tests but are getting errors because the 
data_monitoring_metrics
table does not exist or you lack permissions, you need to either: - Run 
dbt run -s elementary
at least once to create the necessary tables in your environment, and - Make sure your user/role has the required permissions to write to those tables [see discussion](https://slack.com/archives/C02CTC89LAX/p1701710053524279). Summary: - To fully avoid database errors, do not run Elementary tests/models in environments where you don't want database writes. - Disabling only the upload variables is not enough, as the tests themselves still write to the database. If you need to run the tests but want to avoid errors, ensure the tables exist and you have the right permissions. If you want to avoid all writes, disable or exclude Elementary models/tests in those environments.
a
will the chunking get rid of this? @kapa.ai when I have lots of tests?
k
A message from kapa.ai
Chunking (using 
insert_rows_method: chunk
and setting 
dbt_artifacts_chunk_size
) can help mitigate some database errors that occur when uploading large numbers of test results or metrics, especially errors related to query size, complexity, or database limits (such as "too many subqueries" or "maximum number of expressions in a list exceeded") when you have lots of tests. This approach is specifically recommended for platforms like BigQuery and Snowflake, where such limits are common. Users have reported that chunking resolved issues with large uploads and allowed their pipelines to complete successfully when running many tests [see example](https://slack.com/archives/C02CTC89LAX/p1677869503258149) [see also](https://slack.com/archives/C02CTC89LAX/p1706626800346159). However, chunking will not resolve errors related to missing tables or insufficient permissions. If the error is due to the 
data_monitoring_metrics
table not existing or your user lacking write permissions, chunking will not help—you must ensure the table exists and that you have the necessary permissions. Summary: - Chunking helps with errors caused by large uploads (query size/complexity limits). - Chunking does not fix errors caused by missing tables or insufficient permissions. - If you have lots of tests and are hitting database limits, chunking is recommended and often effective.
a
@kapa.ai what about this error: The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters.
k
A message from kapa.ai
The error:
Copy code
The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters.
is a common issue when using Elementary with BigQuery, especially in large dbt projects with many tests or models. This error occurs when the SQL query generated (often for uploading test results or metrics) exceeds BigQuery's maximum allowed query length. How to address this: - Chunking: You can mitigate this error by configuring Elementary to split (chunk) the uploads into smaller queries. Set the following in your 
dbt_project.yml
under the top-level 
vars
section:
Copy code
yaml   vars:     insert_rows_method: chunk     dbt_artifacts_chunk_size: 500  # Try lowering this value (e.g., 250, 100) if the error persists     query_max_size: 100000         # You may need to adjust this as well
Users have reported that reducing 
dbt_artifacts_chunk_size
and 
query_max_size
helps avoid this error. Sometimes, you may need to set 
dbt_artifacts_chunk_size
as low as 100 to resolve the issue in very large projects [see discussion](https://github.com/elementary-data/elementary/issues/1689), [Slack thread](https://slack.com/archives/C02CTC89LAX/p1730388625281789). - Correct placement: Make sure these variables are set under the top-level 
vars
in your 
dbt_project.yml
, not under an 
elementary
block or elsewhere, for them to take effect [see discussion](https://github.com/elementary-data/elementary/issues/1689). - Iterative tuning: If you still see the error, continue lowering 
dbt_artifacts_chunk_size
until the error stops. The optimal value depends on your project's size and the number of tests/models. Summary: Chunking and reducing the size of each upload query is the recommended way to resolve the "query is too large" error in BigQuery when using Elementary. If you continue to see the error after making these changes, double-check your variable placement and try even smaller chunk sizes.
a
even when I set the chunk size to 100 it fails @kapa.ai
k
A message from kapa.ai
If you are still getting the "The query is too large" error even after setting 
dbt_artifacts_chunk_size
to 100, you are not alone—several users have reported this issue. According to the knowledge sources, chunking and reducing the chunk size is the primary recommended solution, but in some very large projects, even very small chunk sizes (like 100) may not be enough to avoid BigQuery's query length limits. From the GitHub issue and Slack discussions, users have sometimes had to reduce the chunk size even further (e.g., to 50 or lower), but there are cases where, despite these changes, the error persists. In these situations, the Elementary team has acknowledged that the chunking logic may not always fully resolve the problem, especially in extremely large projects or with certain types of data. There have been ongoing discussions and code changes to improve this, but no universal fix is guaranteed in the current versions if chunking at 100 still fails [see discussion](https://github.com/elementary-data/elementary/issues/1689), [Slack thread](https://slack.com/archives/C02CTC89LAX/p1730388625281789). If you have already tried the latest version of the Elementary dbt package (0.16.2 or newer), set the chunk size as low as possible, and still encounter this error, the knowledge sources do not provide a further workaround or solution at this time. The issue may require additional improvements from the Elementary team. Summary: - Chunking and reducing chunk size is the recommended approach. - If it still fails at chunk size 100, this is a known limitation and may not be fully resolvable with current settings. - There is no additional workaround documented in the knowledge sources if chunking at 100 still fails.
a
can I dsiable the posthook that runs the data monitoring at the end to avoid the database error?
@kapa.ai can I dsiable the posthook that runs the data monitoring at the end to avoid the database error?
k
A message from kapa.ai
You can disable the Elementary on-run-end posthook (which uploads artifacts and results) by setting the following variables in your `dbt_project.yml`:
Copy code
yaml vars:   disable_dbt_artifacts_autoupload: true   disable_run_results: true   disable_tests_results: true   disable_dbt_invocation_autoupload: true
However, this does not fully disable all database writes. If you are running Elementary anomaly or data monitoring tests, those tests themselves will still attempt to write to tables like 
data_monitoring_metrics
as part of their execution, regardless of the posthook settings. The posthook variables only control the upload of artifacts and results, not the core test logic. To completely avoid database errors related to data monitoring metrics, you need to ensure that Elementary tests/models are not executed at all in the environment where you want to avoid writes. This can be done by disabling the Elementary package for that environment:
Copy code
yaml models:   elementary:     enabled: "{{ target.name in ['prod', 'stage'] }}"
Or by excluding Elementary models/tests in your dbt command (e.g., 
--exclude elementary.*
) [see discussion](https://slack.com/archives/C02CTC89LAX/p1702031013575389), [see also](https://slack.com/archives/C02CTC89LAX/p1701710053524279). Summary: - Disabling the posthook prevents artifact/result uploads, but not test execution writes. - To fully avoid database errors, do not run Elementary tests/models in that environment.
a
@kapa.ai should adding those configurations stop the database error ?
k
A message from kapa.ai
Adding the following configurations in your `dbt_project.yml`:
Copy code
yaml vars:   disable_dbt_artifacts_autoupload: true   disable_run_results: true   disable_tests_results: true   disable_dbt_invocation_autoupload: true
should stop the Elementary on-run-end posthook from uploading artifacts and results. However, according to the knowledge sources, this does not prevent all database writes. If you are still running Elementary anomaly or data monitoring tests, those tests will still attempt to write to tables like 
data_monitoring_metrics
, regardless of these settings. This is a known behavior and has been confirmed by Elementary support: > "Inserts to 
data_monitoring_metrics
are a part of the anomaly tests - they cannot be disabled if you wish to use elementary anomaly tests... you will still see the post-run-end hook running, as its all running in the same hook, but the dbt artifacts will not be uploaded" [Slack thread](https://slack.com/archives/C02CTC89LAX/p1701710053524279) In summary: - These configurations will stop artifact and result uploads from the posthook. - They will not stop database errors if you are running Elementary tests that write to metrics tables. - To fully avoid such errors, you must also exclude or disable Elementary tests/models in your environment. If you need to completely prevent database writes and errors, you should disable or exclude Elementary models/tests, not just the uploads.
a
@kapa.ai how can I still run elementary anomaly tests but avoid this database error when I have an extremely large number of tests on tables (over 1k)
k
A message from kapa.ai
If you want to run Elementary anomaly tests on a very large number of tables (over 1,000) and are encountering database errors—especially errors like "The query is too large" in BigQuery—the main mitigation strategy recommended in the knowledge sources is to use chunking by setting:
Copy code
yaml vars:   insert_rows_method: chunk   dbt_artifacts_chunk_size: 100  # or lower if needed   query_max_size: 100000         # adjust as necessary
and ensure these are set at the top-level 
vars
in your 
dbt_project.yml
 [see discussion](https://github.com/elementary-data/elementary/issues/1689). However, the knowledge sources also make it clear that even with chunking set as low as 100, some users still encounter the query size error in extremely large projects. In these cases, there is no documented workaround or configuration in the current version of Elementary that guarantees the error will be avoided. The Elementary team is aware of this limitation, and users have reported that chunking sometimes does not fully resolve the issue for very large test suites [see Slack thread](https://slack.com/archives/C02CTC89LAX/p1730388625281789). Summary: - Chunking is the only documented mitigation for this error. - If chunking (even at very low sizes) does not resolve the error, there is no further workaround in the knowledge sources. - The only way to guarantee avoidance of the error is to reduce the number of tests/models per run, or split your runs into smaller batches. If you continue to encounter the error after applying chunking, you may need to split your dbt runs or contact the Elementary team for further support, as the current knowledge sources do not provide a complete solution for this scenario.
a
does chunk have to be in quotes: insert_rows_method: chunk dbt_artifacts_chunk_size: 10 query_max_size: 100000 disable_dbt_artifacts_autoupload: true disable_run_results: true disable_tests_results: true disable_dbt_invocation_autoupload: true
insert_rows_method: chunk dbt_artifacts_chunk_size: 10 query_max_size: 100000 disable_dbt_artifacts_autoupload: true disable_run_results: true disable_tests_results: true disable_dbt_invocation_autoupload: true does chunk have to be in quotes @kapa.ai
k
A message from kapa.ai
Based on the knowledge sources, the value for 
insert_rows_method
can be set as either 
chunk
or 
"chunk"
(with or without quotes) in your 
dbt_project.yml
. Both forms are shown in user examples and support responses, and there is no indication that quotes are strictly required or that omitting them causes issues. For example:
Copy code
yaml vars:   insert_rows_method: chunk   dbt_artifacts_chunk_size: 500
and
Copy code
yaml vars:   insert_rows_method: "chunk"   dbt_artifacts_chunk_size: 500
Both are accepted and function the same way according to the support threads and GitHub issues [see example](https://slack.com/archives/C02CTC89LAX/p1741383537698769). Summary: No, 
chunk
does not have to be in quotes, but it is also fine if you use quotes. Both are valid YAML and recognized by Elementary.
38 Views
Open in Slack
PreviousNext
Powered by