https://linen.dev logo
Join Slack
Powered by
<@U088WCP3B4P> dbt test throw an error on on-run-e...
# ask-elementary-ai
c
@kapa.ai dbt test throw an error on on-run-end hook :
Copy code
TABLE_NOT_FOUND: line 11:6: Table 'awsdatacatalog."dfn-sales-dev-dq_elementary".test_b260cb04bb_elementary_column_anomalies_allocations_null_percent__average__max__zero_percent__impressions__day__3__country_id__source_id__day__1__day__30__metrics__tmp_20250911154022144953' does not exist. You may need to manually clean the data at location '<s3://aws-athena-query-results-549171697840-eu-west-1/tables/a545cc60-229f-4d15-9a67-160abdc9f40d>' before retrying. Athena will not delete data in your account.
[0m17:45:18.058292 [warn ] [MainThread]: [[33mWARNING[0m][DeprecationsSummary]: Deprecated functionality
because dbt tries to create a view from a table that has been deleted previously by elementary here is the log of the sql query that fail :
Copy code
[0m17:45:16.901028 [debug] [MainThread]: dbt.adapters.athena.constants adapter: Athena query ID a545cc60-229f-4d15-9a67-160abdc9f40d
[0m17:45:18.031193 [debug] [MainThread]: dbt.adapters.athena.constants adapter: Error running SQL: -- /* {"app": "dbt", "dbt_version": "1.10.9", "profile_name": "dfn", "target_name": "sales", "connection_name": "master"} */
/* --ELEMENTARY-METADATA-- {"command": "test", "invocation_id": "ea653a13-19cb-4dc2-8e92-4c8673a3d1b8", "package_name": "elementary", "resource_name": "elementary-on-run-end-0", "resource_type": "operation"} --END-ELEMENTARY-METADATA-- */
    

    create table "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."data_monitoring_metrics__tmp_20250911154306183175"
    with (
      table_type='hive',
      is_external=true,
      format='parquet'
    )
    as
      with union_temps_metrics as (select * from "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_b260cb04bb_elementary_column_anomalies_allocations_null_percent__average__max__zero_percent__impressions__day__3__country_id__source_id__day__1__day__30__metrics__tmp_20250911154022144953" union all select * from "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_10f417179d_elementary_column_anomalies_allocations_null_percent__average__max__zero_percent__sot__day__3__country_id__source_id__day__1__day__30__metrics__tmp_20250911154045827474" union all select * from "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_808bfd72c7_elementary_column_anomalies_bookings_3__null_percent__average__max__zero_percent__price__day__3__country_id__source_id__day__1__day__14__metrics__tmp_20250911154109775398" union all select * from "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_0095b83a15_elementary_volume_anomalies_programmatic_campaigns_day__3__day__1__published_at__day__30__metrics__tmp_20250911154129719020"
            ),
            metrics_with_duplicates as (
                select *,
                    row_number() over (partition by id order by updated_at desc) as row_num
                from union_temps_metrics
            )
            select
                id,
                full_table_name,
                column_name,
                metric_name,
                metric_type,
                metric_value,
                source_value,
                bucket_start,
                bucket_end,
                bucket_duration_hours,
                updated_at,
                dimension,
                dimension_value,
                metric_properties
            from metrics_with_duplicates
            where row_num = 1

  
[0m17:45:18.037585 [debug] [MainThread]: On master: Close
[0m17:45:18.047255 [info ] [MainThread]: 
[0m17:45:18.048947 [info ] [MainThread]: [33mExited because of keyboard interrupt[0m
[0m17:45:18.050068 [info ] [MainThread]: 
[0m17:45:18.051310 [info ] [MainThread]: Done. PASS=14 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=14
[0m17:45:18.052585 [debug] [MainThread]: Connection 'master' was properly closed.
[0m17:45:18.053245 [debug] [MainThread]: Connection 'list_AwsDataCatalog_dfn-sales-dev-dq_elementary' was properly closed.
[0m17:45:18.053681 [debug] [MainThread]: Connection 'test.dfn.relationships_sold_items_country_id__alpha_2__source_dfn_shared_countries_.82a08b9c23' was properly closed.
[0m17:45:18.055240 [info ] [MainThread]: 
[0m17:45:18.056199 [info ] [MainThread]: Finished running 3 project hooks, 11 data tests in 0 hours 5 minutes and 5.77 seconds (305.77s).
[0m17:45:18.057276 [error] [MainThread]: Encountered an error:
Runtime Error
  TABLE_NOT_FOUND: line 11:6: Table 'awsdatacatalog."dfn-sales-dev-dq_elementary".test_b260cb04bb_elementary_column_anomalies_allocations_null_percent__average__max__zero_percent__impressions__day__3__country_id__source_id__day__1__day__30__metrics__tmp_20250911154022144953' does not exist. You may need to manually clean the data at location '<s3://aws-athena-query-results-549171697840-eu-west-1/tables/a545cc60-229f-4d15-9a67-160abdc9f40d>' before retrying. Athena will not delete data in your account.
[0m17:45:18.058292 [warn ] [MainThread]: [[33mWARNING[0m][DeprecationsSummary]: Deprecated functionality
Summary of encountered deprecations:
and here the log that show that the table referenced in the above query has been deleted earlier
Copy code
[0m17:42:50.952748 [debug] [MainThread]: dbt.adapters.athena.constants adapter: "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_0095b83a15_elementary_volume_anomalies_programmatic_campaigns_day__3__day__1__published_at__day__30__anomaly_scores__tmp_20250911154136612445" is stored in <s3://aws-athena-query-results-549171697840-eu-west-1/tables/2438d424-ecb1-4da9-ae09-cfe39c27e805>
[0m17:42:51.095106 [debug] [MainThread]: dbt.adapters.athena.constants adapter: Deleting table data: path='<s3://aws-athena-query-results-549171697840-eu-west-1/tables/2438d424-ecb1-4da9-ae09-cfe39c27e805>', bucket='aws-athena-query-results-549171697840-eu-west-1', prefix='tables/2438d424-ecb1-4da9-ae09-cfe39c27e805/'
[0m17:42:51.351995 [debug] [MainThread]: Elementary: Deleting temporary Elementary test tables: [<AthenaRelation "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_b260cb04bb_elementary_column_anomalies_allocations_null_percent__average__max__zero_percent__impressions__day__3__country_id__source_id__day__1__day__30__metrics__tmp_20250911154022144953">, <AthenaRelation "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_b260cb04bb_elementary_column_anomalies_allocations_null_percent__average__max__zero_percent__impressions__day__3__country_id__source_id__day__1__day__30__anomaly_scores__tmp_20250911154033306950">, <AthenaRelation "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_10f417179d_elementary_column_anomalies_allocations_null_percent__average__max__zero_percent__sot__day__3__country_id__source_id__day__1__day__30__metrics__tmp_20250911154045827474">, <AthenaRelation "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_10f417179d_elementary_column_anomalies_allocations_null_percent__average__max__zero_percent__sot__day__3__country_id__source_id__day__1__day__30__anomaly_scores__tmp_20250911154058781979">, <AthenaRelation "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_808bfd72c7_elementary_column_anomalies_bookings_3__null_percent__average__max__zero_percent__price__day__3__country_id__source_id__day__1__day__14__metrics__tmp_20250911154109775398">, <AthenaRelation "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_808bfd72c7_elementary_column_anomalies_bookings_3__null_percent__average__max__zero_percent__price__day__3__country_id__source_id__day__1__day__14__anomaly_scores__tmp_20250911154119537134">, <AthenaRelation "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_0095b83a15_elementary_volume_anomalies_programmatic_campaigns_day__3__day__1__published_at__day__30__metrics__tmp_20250911154129719020">, <AthenaRelation "AwsDataCatalog"."dfn-sales-dev-dq_elementary"."test_0095b83a15_elementary_volume_anomalies_programmatic_campaigns_day__3__day__1__published_at__day__30__anomaly_scores__tmp_20250911154136612445">]
[0m17:42:51.364291 [debug] [MainThread]: Using athena connection "master"
[0m17:42:51.365059 [debug] [MainThread]: On master: -- /* {"app": "dbt", "dbt_version": "1.10.9", "profile_name": "dfn", "target_name": "sales", "connection_name": "master"} */
/* --ELEMENTARY-METADATA-- {"command": "test", "invocation_id": "ea653a13-19cb-4dc2-8e92-4c8673a3d1b8", "package_name": "dfn", "resource_name": "dfn-on-run-end-0", "resource_type": "operation"} --END-ELEMENTARY-METADATA-- */
    DROP TABLE IF EXISTS `AwsDataCatalog`.`dfn-sales-dev-dq_elementary`.`test_b260cb04bb_elementary_column_anomalies_allocations_null_percent__average__max__zero_percent__impressions__day__3__country_id__source_id__day__1__day__30__metrics__tmp_20250911154022144953`
k
A message from kapa.ai
Thanks for the detailed logs — this matches a known Athena-specific issue where Elementary cleans up temporary test tables before they’re reused in the on-run-end merge, causing TABLE_NOT_FOUND during the union into data_monitoring_metrics__tmp. This was addressed by adding Athena-specific cleanup behavior and avoiding premature deletion of temp resources. [[Temp tables Athena](https://github.com/elementary-data/elementary/issues/1514)] What to do: - Upgrade to the latest Elementary dbt package (the fix was merged) and rerun deps, then initialize Elementary on the same target. Commands: - dbt deps - dbt run -s elementary --target <your target> Then run dbt test again. [[Temp tables Athena](https://github.com/elementary-data/elementary/issues/1514)] Why this helps: - The fix introduces adapter-aware dropping (fully_drop_relation/clean_up_table) so Athena S3 data cleanup happens at the right time, and temp tables used in the final union aren’t dropped before they’re read. [[Temp tables Athena](https://github.com/elementary-data/elementary/issues/1514)] If you still hit it after upgrading: - Confirm the Elementary models exist in the same target you’re testing (run dbt run -s elementary for that target). [[Initial run target](https://github.com/elementary-data/elementary/issues/830)] - As a temporary workaround, disable automatic test table cleanup at on-run-end and run a separate cleanup (run-operation) after tests finish, so union_temps_metrics can still read those tables. The project history shows the cleanup is normally invoked from handle_tests_results in on-run-end. [[On-run-end cleanup exists](https://github.com/elementary-data/elementary/issues/1313)] If you can share a dbt.log from a failing run, we can verify whether cleanup is triggered before the merge and confirm versions in use. [[Databricks/Athena temp view timing](https://slack.com/archives/C02CTC89LAX/p1692603763973949)]
c
@kapa.ai elementary.clean_elementary_temp_tables() is indeed call at the end of the on-run-end, after the macro that generate union_temps_metrics table
k
A message from kapa.ai
Thanks for confirming. Yes—Elementary’s on-run-end calls the cleanup macro after handling test results (i.e., after building/reading union_temps_metrics), and past issues came from adapter-specific behavior (Athena) where files/catalog entries needed careful, delayed cleanup to avoid TABLE_NOT_FOUND. Recent fixes introduced adapter-aware “fully drop” behavior so Athena temp resources aren’t removed before they’re read and are cleaned correctly afterward. [[on-run-end cleanup](https://github.com/elementary-data/elementary/issues/1313); [Temp tables Athena](https://github.com/elementary-data/elementary/issues/1514)] If you still see TABLE_NOT_FOUND on Athena: - Upgrade to the latest Elementary dbt package and rerun dbt deps, then dbt run -s elementary before dbt test. [[Temp tables Athena](https://github.com/elementary-data/elementary/issues/1514)] - Ensure you’re on the version that includes fully_drop_relation/clean_up_table for Athena. [[Temp tables Athena](https://github.com/elementary-data/elementary/issues/1514)]
c
@kapa.ai could it be a issue with multi-threading ?
k
A message from kapa.ai
Short answer: yes, concurrency/multi-threading can trigger these kinds of failures with Elementary’s temp/artifacts handling—especially on Athena/Databricks/BigQuery—if multiple dbt runs or threads touch the same Elementary tables at once. We’ve seen: - Databricks “conflicting commit” and TABLE_OR_VIEW_NOT_FOUND when parallel runs truncate/update the same artifacts tables. [[Databricks conflicts](https://slack.com/archives/C02CTC89LAX/p1694431548484389)] - BigQuery transaction aborts when two on-run-end hooks run concurrently against dbt_models. [[BQ concurrency](https://slack.com/archives/C02CTC89LAX/p1689186975724739); [BQ issue](https://github.com/elementary-data/elementary/issues/1766)] - General guidance to avoid parallel writes to dbt_artifacts and, if needed, disable on-run-end or exclude Elementary models when running jobs in parallel. [[Troubleshooting](https://docs.elementary-data.com/oss/general/troubleshooting)] For Athena specifically, there were fixes to properly sequence and clean temp tables/files; upgrade to the latest Elementary package to get the adapter-aware cleanup. [[Temp tables Athena](https://github.com/elementary-data/elementary/issues/1514)] If parallelism is required, consider serializing runs that execute on-run-end or write to Elementary tables, or schedule a single job to update artifacts separately. [[Troubleshooting](https://docs.elementary-data.com/oss/general/troubleshooting)]
29 Views
Open in Slack
PreviousNext
Powered by