Open cshlin opened 5 months ago
Hi @cshlin ! Thanks for posting this issue and sorry for the delayed response. You're right - there is a temp table here that is available during the test run, but is not available afterwards. We need to save instead a query that will not require this temp table.
Any chance you'd like to contribute a fix to this? I'd be happy to provide guidance with a possible approach to fix this.
Absolutely, point me in the right direction and I can implement CharlesOn May 19, 2024, at 15:45, Itamar Hartstein @.***> wrote: Hi @cshlin ! Thanks for posting this issue and sorry for the delayed response. You're right - there is a temp table here that is available during the test run, but is not available afterwards. We need to save instead a query that will not require this temp table. Any chance you'd like to contribute a fix to this? I'd be happy to provide guidance with a possible approach to fix this.
—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: @.***>
Thanks @cshlin !
It's admittedly not a easy fix - but I'll write what I think can be a way to solve it.
Essentially I think we need to save a different query in the test_result_query
than what is being actually run - a version of the query that does not rely on temp tables.
(This is needed, because the metrics of all tests are only saved to the permanent data_monitoring_metrics
table in the on_run_end
hook of dbt, which is after the tests are actually being run)
These areas likely need to be changed:
get_read_anomaly_scores_query
should get a parameter that allows it to compute the anomaly scores directly, and not through a temp table that is previously generated.get_anomaly_scores_query
pulls only from data_monitoring_metrics
and not from a union of it with a temporary table.store_anomaly_test_results
should get an additional saved_anomaly_scores_sql
parameter which will contain a query that does not rely on a temp table, and the test_results_query
should be computed by using it.The full flow where the anomaly query is generated and the result is saved exists in each of the anomaly test implementations.
For example in test_table_anomalies.sql
it would be these rows:
{% set flattened_test = elementary.flatten_test(context["model"]) %}
{% set anomaly_scores_sql = elementary.get_read_anomaly_scores_query() %}
{% do elementary.store_metrics_table_in_cache() %}
{% do elementary.store_anomaly_test_results(flattened_test, anomaly_scores_sql) %}
Please let me know if you plan to look into it, and feel free to ask additional questions.
Describe the bug When trying to copy the result query to view the failed rows, I get a query that contains an invalid tmp table
To Reproduce Steps to reproduce the behavior:
edr.txt
Expected behavior A query that provides me with the failed rows
Screenshots
Environment (please complete the following information):
Additional context Add any other context about the problem here.