https://linen.dev logo
Join Slack
Powered by
How do i stop Elementary making 100's of tables in...
# ask-elementary-ai
n
How do i stop Elementary making 100's of tables in bigquery (e.g. 1 for every column test it runs). Shouldnt these be temp tables or there be some kind of cleanup process? table name: test_146ea26f29_elementary_collect_metrics_dim_commune__name_row_count_type_row_count____name_null_percent_type_null_percent_columns____name_null_count_type_null_count_columns____name_min_length_type_min_length_columns_id_nom_departement____name_max_length_type_max_length_columns_id_nom_departement____name_average_length_type_average_length_columns_id_nom_departement____name_missing_count_type_missing_count_columns_id_nom_departement____name_missing_percent_type_missing_percent_columns_id_nom_departement____name_freshness_type_freshness___metrics__tmp_20251112194951681698
@kapa.ai
k
Sorry @Nic, I'm not sure what you mean. Please try again. 👋
n
@kapa.ai How do i stop Elementary making 100's of tables in bigquery (e.g. 1 for every column test it runs). Shouldnt these be temp tables or there be some kind of cleanup process? table name: test_146ea26f29_elementary_collect_metrics_dim_commune__name_row_count_type_row_count____name_null_percent_type_null_percent_columns____name_null_count_type_null_count_columns____name_min_length_type_min_length_columns_id_nom_departement____name_max_length_type_max_length_columns_id_nom_departement____name_average_length_type_average_length_columns_id_nom_departement____name_missing_count_type_missing_count_columns_id_nom_departement____name_missing_percent_type_missing_percent_columns_id_nom_departement____name_freshness_type_freshness___metrics__tmp_20251112194951681698
k
A message from kapa.ai
Based on the knowledge sources, I can help explain what's happening with these Elementary test tables in BigQuery and how to address them. ## What are these tables? The tables you're seeing (like 
test_146ea26f29_elementary_collect_metrics...
) are created by Elementary during test execution. These tables store intermediate test results and metrics before they're inserted into Elementary's main tables. ## Current behavior and cleanup According to the [GitHub issue #1313](https://github.com/elementary-data/elementary/issues/1313), Elementary does have an automatic cleanup process for these test tables. The cleanup is implemented in the 
clean_elementary_test_tables
macro, which is called during the 
on-run-end
hook. However, based on multiple [Slack discussions](https://slack.com/archives/C02CTC89LAX/p1684349983650899), there was a specific issue with BigQuery where temporary tables with names ending in 
__dbt_tmp
were not being properly cleaned up. This was fixed and merged, though the exact version isn't specified in the knowledge sources. ## Solutions 1. Upgrade Elementary: Make sure you're using the latest version of Elementary, as the automatic cleanup functionality has been implemented and improved over time. 2. Manual cleanup is safe: If tables persist, you can safely delete them after your dbt run completes. As mentioned in [this discussion](https://slack.com/archives/C02CTC89LAX/p1685097367889679), "You can drop these tests tables as soon as the 
dbt test
run is completed, there will be no impact on the report." 3. Custom cleanup hook: If the issue persists, you can implement your own cleanup using an 
on_run_end
hook in your 
dbt_project.yml
to drop tables with 
__tmp
in their names, as suggested in [this thread](https://slack.com/archives/C02CTC89LAX/p1702682903247439). ## Why aren't they temp tables? According to [this explanation](https://slack.com/archives/C02CTC89LAX/p1685097367889679), Elementary needs to create these tables (rather than using INSERT statements directly) to avoid conflicts when multiple tests run in parallel. The tables are created, then a single INSERT query is executed during the 
on-run-end
hook. If you're still experiencing this issue with the latest version, I'd recommend checking if your dbt process is completing successfully, as interrupted runs may prevent the cleanup from executing.
n
how do i clean these tables up and delete them
how do i clean these tables up and delete them @kapa.ai
k
A message from kapa.ai
Based on the knowledge sources, here are the methods to clean up and delete these Elementary test tables: ## Manual Deletion You can safely delete these tables manually at any time after your dbt run completes. As confirmed in [this discussion](https://slack.com/archives/C02CTC89LAX/p1674189580227079), "it's safe to delete them. These tmp tables are probably leftovers from an unsuccessful run." ## Automated Cleanup Options ### 1. Using an on-run-end hook You can add a custom cleanup to your 
dbt_project.yml
file. Here's an example approach mentioned in [this thread](https://slack.com/archives/C02CTC89LAX/p1706702598567759): Add a 
drop
statement to your own 
on_run_end
hook to remove the temporary tables after each run. ### 2. PySpark script (for Databricks/Spark) If you're using Databricks or a Spark cluster, you can use this script from [this discussion](https://slack.com/archives/C02CTC89LAX/p1702682903247439):
Copy code
python catalog = 'INSERT_HERE' elementary_schema = 'INSERT_HERE'  spark.sql("use catalog " + catalog)  table_df = spark.sql("show tables in " + elementary_schema)  table_list = [row.tableName for row in table_df.select("tableName").collect()]  tables_to_keep = [table for table in table_list if '__tmp' not in table]  tables_to_drop =  list(set(table_list) - set(tables_to_keep))  print("Tables to drop: \n\n" + '\n'.join(tables_to_drop) + '\n') print("Tables to keep: \n\n" + '\n'.join(tables_to_keep))  for table in tables_to_drop:     spark.sql("drop table " + catalog + "." + elementary_schema + "." + table + ";")
## Important Notes - According to [this explanation](https://slack.com/archives/C02CTC89LAX/p1685097367889679), you can delete these test tables "as soon as the 
dbt test
command completes (including the on run end), as there is no use to them after the on-run-end hook completes." - Deleting these tables will have no impact on the Elementary report or stored test results, as the data has already been inserted into Elementary's main tables. The knowledge sources indicate that Elementary should be cleaning these up automatically in recent versions, so if you're experiencing persistent accumulation, ensure you're on the latest version and that your dbt runs are completing successfully.
n
<!subteam^S08HYBWCF6E> Is there no elementary feature that deletes these tables for me? Do i have to build a macro or script to remove 100's of these tables?
👀 1
n
@Nic It should be automatically deleted. What dbt package version are you using?
n
- name: elementary package: elementary-data/elementary version: 0.20.1
even in bigquery? I believe there is no temp table so maybe thats the issue
Any idea on the above @Noya Offer? Thanks 🙂
n
Hey @Nic! We're looking into it.
y
The tables are really created as non-temporary. We had issues with these, as dbt doesn't always maintain the same DB session. These should be deleted at the end of the run. If runs are killed or our on-run-end is disabled, then they won't get deleted.
n
Hey @Yosef Arbiv, the tables are created and the hooks seem to run ok, yet the tables still remain. Below is a screenshot of the tables still existing in bigquery, and also a log of the test run where the test succeeds, table is created, hook runs successfully yet no table is deleted. Any ideas? Cheers for your help
Copy code
(dbt-models) (tgm-975-…) ~/D/dbt_models > mise run dbt test --select dim_backoffice_organizations
[dbt] $ cd corporate_suite && uv run dbt test --select dim_backoffice_organizations
14:32:14  Running with dbt=1.10.13
14:32:16  Registered adapter: bigquery=1.10.2
14:32:18  Found 842 models, 23 snapshots, 457 data tests, 4 seeds, 4 operations, 132 sources, 2293 exposures, 3 metrics, 2131 macros, 1 semantic model
14:32:18  
14:32:18  Concurrency: 30 threads (target='dev')
14:32:18  
/Users/nic.tay/Documents/dbt_models/.venv/lib/python3.13/site-packages/google/auth/_default.py:76: UserWarning: Your application has authenticated using end user credentials from Google Cloud SDK without a quota project. You might receive a "quota exceeded" or "API not enabled" error. See the following page for troubleshooting: <https://cloud.google.com/docs/authentication/adc-troubleshooting/user-creds>. 
  warnings.warn(_CLOUD_SDK_CREDENTIALS_WARNING)
/Users/nic.tay/Documents/dbt_models/.venv/lib/python3.13/site-packages/google/auth/_default.py:76: UserWarning: Your application has authenticated using end user credentials from Google Cloud SDK without a quota project. You might receive a "quota exceeded" or "API not enabled" error. See the following page for troubleshooting: <https://cloud.google.com/docs/authentication/adc-troubleshooting/user-creds>. 
  warnings.warn(_CLOUD_SDK_CREDENTIALS_WARNING)
/Users/nic.tay/Documents/dbt_models/.venv/lib/python3.13/site-packages/google/auth/_default.py:76: UserWarning: Your application has authenticated using end user credentials from Google Cloud SDK without a quota project. You might receive a "quota exceeded" or "API not enabled" error. See the following page for troubleshooting: <https://cloud.google.com/docs/authentication/adc-troubleshooting/user-creds>. 
  warnings.warn(_CLOUD_SDK_CREDENTIALS_WARNING)
/Users/nic.tay/Documents/dbt_models/.venv/lib/python3.13/site-packages/google/auth/_default.py:76: UserWarning: Your application has authenticated using end user credentials from Google Cloud SDK without a quota project. You might receive a "quota exceeded" or "API not enabled" error. See the following page for troubleshooting: <https://cloud.google.com/docs/authentication/adc-troubleshooting/user-creds>. 
  warnings.warn(_CLOUD_SDK_CREDENTIALS_WARNING)
14:32:19  Elementary: Runtime data: {"config": {}, "dbt_version": "1.10.13", "elementary_version": "0.20.1", "database": "prj-bi-data-7692", "schema": "dbt_ntay_elementary"}
14:32:20  1 of 2 START hook: elementary.on-run-start.0 ................................... [RUN]
14:32:20  1 of 2 OK hook: elementary.on-run-start.0 ...................................... [OK in 0.54s]
14:32:20  2 of 2 START hook: zendesk.on-run-start.1 ...................................... [RUN]
14:32:20  2 of 2 OK hook: zendesk.on-run-start.1 ......................................... [OK in 0.00s]
14:32:20  
14:32:20  1 of 1 START test elementary_collect_metrics_dim_backoffice_organizations__name_row_count_type_row_count_  [RUN]
14:32:22  1 of 1 PASS elementary_collect_metrics_dim_backoffice_organizations__name_row_count_type_row_count_  [PASS in 2.41s]
14:32:22  
14:32:22  1 of 2 START hook: pricehubble.on-run-end.0 .................................... [RUN]
14:32:22  1 of 2 OK hook: pricehubble.on-run-end.0 ....................................... [OK in 0.01s]
14:32:35  2 of 2 START hook: elementary.on-run-end.1 ..................................... [RUN]
14:32:35  2 of 2 OK hook: elementary.on-run-end.1 ........................................ [OK in 13.13s]
14:32:35  
14:32:35  Finished running 4 project hooks, 1 test in 0 hours 0 minutes and 17.86 seconds (17.86s).
14:32:36  
14:32:36  Completed successfully
14:32:36  
14:32:36  Done. PASS=5 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=5
y
Thanks @Nic that's strange. Can you share the full dbt logs?
32 Views
Open in Slack
PreviousNext
Powered by