Hey folks, those days a question pop up in the Airflow workspace about elementary and astronomer-cosmos. Pretty much astronomer cosmos is a library that allow to translate dbt Dags in native Airflow dag. As such, each model will get an airflow task, and each model run will invoke "elementary" on run-end hook. This can create some issues on some systems (e.g. Redshift, but also Athena) that don't deal well with concurrent transactions. See original message:

Hi team, actually I'm using

astronomer-cosmos[dbt.all]==1.5.0

to orchestrate dbt models in airflow, also I'm using the package

elementary-data/elementary=0.14.0

but I'm experiencing some performance issue because elementary.on-run-end is running on every node/table and this sometimes generate some bottlenecks (delay) in redshift due locked transactions related to elementary inserts/updates. is there any better way to work with elementary+cosmos?

One idea that came in the discussion is to upload the artifacts from each model/test run in S3 (or object storage), and then "parse" those artifacts to then upload/add them to Elementary tables. This will change pratically how the current on-run-end hooks works, which will not be necessary anymore, but will introduce another process to deal with artifacts. I'm wondering if have something in place to achieve that, and if not I would like to connect to discuss about solutions. cc @Itamar Hartstein

Hi @Nicola Corda, thanks for sharing! I wasn't familiar with with this library, very interesting. I think this can be divided into two issues: 1. Dbt metadata tables (e.g.

dbt_models

,

dbt_sources

and so on) 2. Various result objects (e.g.

dbt_run_results

,

elementary_test_results

) Regarding the first, actually a straightforward solution I can suggest is to disable uploading artifacts during on-run-end hook using this config:

vars:
  disable_dbt_artifacts_autoupload: true

And then in a separate job run

dbt run -s elementary

which will load the artifacts in a single job. (2) is trickier, though in some cases since it involves append-only tables rather than deletes+inserts it may be fine. If it is not, then I guess a mechanism like the one you described may be necessary. Anyway, will be happy to discuss further.

I think that we might need another discussion. The dbt_models/dbt_sources part IMHO is not really problematic, because that can be even "uploaded" as part of the CI process, that's the part that deploy dbt - so running something like

dbt run -s elementary

might work The complicated part is to upload the "dbt_run_results"/elementary_test_results - what I'm seeking it's a mechanism that allows given a set of artifacts in "s3" (which is a common medium for artifacts) to insert them in elementary tables - that issue pop up few times as well when I was working with dbt-athena and iceberg table. Let me know if you want to chat in details about that @Itamar Hartstein - we can consider to schedule a call.