https://linen.dev logo
Join Slack
Powered by
Hi team is it possible to have Elementary queries ...
# community-support
j
Hi team is it possible to have Elementary queries push to a PostgresDB while our dbt model results continue to push to BigQuery? Somehow the post hook would use a different connection profile from the actual dbt query.
👀 1
The reason to do this is to avoid concurrent write issues to BQ for Elementary queries like inserting to data_monitoring_metrics. It doesn't look like you can avoid writing to data_monitoring_metrics even if the dbt model has no Elementary tests, so all dbt jobs/models perform an insert to this table. That is not a good idea on BigQuery.
o
Hi @Jordan Williford, sorry for the late reply drop sad elementary runs as a part of your dbt project, and since it is not supported in dbt, it is not supported in elementary as well... 🙃 what version of 
dbt
and 
elementary
are you using? we may have resolved this issue in a newer release
j
dbt v1.7, Elementary v0.13.0
o
are you sure there are concurrent writes to BQ in these versions? what makes you think that? (not trying to imply that there aren't, just attempting to find the root of the issue)
j
I am not suggesting that the dbt/Elementary versions are related. We multiple dbt jobs calling insert_data_monitoring_metrics(), for instance.
o
alright, i understand now. at any rate, as elementary runs as a part of the 
dbt
command, and dbt does not allow mulit connection profiles, it is not possible to for elementary to write its results to a different database
j
Yeah that makes sense. My current path is to overload two key functions to push the results to a GCP cloud function -> Pub/Sub -> BQ table to increase the concurrent write throughput. It works great, but I am having trouble dispatching the two macros so mine get used instead of Elementary's.
@Ofek Weiss should it be possible to dispatch Elementary macros in order to do what I describe ^ above? I am trying to dispatch elementary.insert_data_monitoring_metrics with a local version and dbt does not seem to see it. I have contacted dbt help and they say because Elementary is calling it like 'elementary._macro_name_' the dispatch does not work. This is not consistent with dbt documentation of dispatching package macros. Do you have any experience here?
o
thats weird, according to dbt documentation it should work... how is your dispatch config set up?
j
I am basically doing this below, and putting it in my /macros/elementary_overrides/handle_tests_results.sql file. If I create a temp.sql model that runs {{ elementary.insert_data_monitoring_metrics('a','b', false) }}, it does not get to my macro and instead runs the original macro in 
dbt_packages/elementary/macros/edr/tests/on_run_end/handle_tests_results.sql
Can you try what I am doing to see if it works for you? 
{# This overrides projects/xxxx/dbt_packages/elementary/macros/edr/tests/on_run_end/handle_tests_results.sql's insert_data_monitoring_metrics() macro #}
{%- macro insert_data_monitoring_metrics(database_name, schema_name, test_metrics_tables) -%}
{{ return(adapter.dispatch('insert_data_monitoring_metrics','elementary')(database_name, schema_name, test_metrics_tables)) }}
{%- endmacro -%}
{% macro default__insert_data_monitoring_metrics(database_name, schema_name, test_metrics_tables) %}
{{ elementary.file_log("OVERRIDING Elementary insert_data_monitoring_metrics") }}
{%endmacro %}
BTW this is the code change I am trying to make with my dispatch. It adds the capability to push small inserts using a gcp cloud function (and pub/sub -> BigQuery table) instead of doing a bunch of small inserts to a BigQuery table directly. Because of BigQuery's concurrent write limitations, Elementary is broken for folks with enough dbt jobs. If this PR's code change could be added to the Elementary release, I wouldn't need to dispatch or anything else. https://github.com/elementary-data/dbt-data-reliability/pull/619
31 Views
Open in Slack
PreviousNext
Powered by