All model objects inserted every time a new invocation is executed.

[AlbertEksteen]

Hi,
Trust you are all well.

I use the dbt_artifacts package to monitor model execution failures and display the failures on a dashboard.

I picked something up and I'm not sure if it's a problem or if it's by design and I just need someone to explain it to me.

I took a random invocation just as a test, and to indicate it exists, I did a select and it returns 1 row.
select count(*)
from fct_dbt__invocations
where command_invocation_id = '75c153c8-36cb-4c21-b275-f6ff40cfdae1';
--rows returned = 1

I then use that invocation id and lookup the model execution, and it returns 2 rows, and I can confirm this is correct because I can validate it from dbt_cloud and the model has only 2 dbt build commands.
select *
from fct_dbt__model_executions
where command_invocation_id = '75c153c8-36cb-4c21-b275-f6ff40cfdae1';
--rows returned 2

I can also find the correct model data by using the model execution id, and this also only returns 2 rows.
select count(*)
from dim_dbt__models
where model_execution_id in ('e94a6dbd858b682fe1707bbb930b9c4f','b2ef92dac3433be8cf4b58415a6a2a9a');
--rows returned 2

but when I use the command_invocation_id on the model object as a lookup, it returns a list of all my models and not only the models affected.
select count(*)
from dim_dbt__models
where command_invocation_id = '75c153c8-36cb-4c21-b275-f6ff40cfdae1';
--rows returned 2,175

Will the dbt_artifacts package insert all the model objects every time a new invocation is executed, and why if it's by design?

Thanks in advance.

Regards
A

[asifzbaig]

Hi @AlbertEksteen , Did you got a work-around for this problem yet?
We are also trying to integrate dbt Artifacts package but are seeing static objects like exposures, seeds and models among others getting uploaded with every invocation. We would only want to upload this information in case of CI job execution or manually and upload only the execution data every time.

[AlbertEksteen]

Hi @asifzbaig, I created a script to clean up the unwanted data periodically.

[jschuba]

I also wanted to keep a single copy of the project-level datasets. On each dbt run, I wanted the project-level datasets to be cleared and re-written.

If anyone else feels the same way, here is how I solved it in my project:

Define a new macro in your project, which modifies/overrides the dbt_artifacts.upload_results macro. In this macro, we will TRUNCATE each of the project-level datasets ['exposures', 'seeds', 'snapshots', 'sources', 'tests', 'models']. The key code is:

            {% if dataset in datasets_to_truncate %}
                {% set dataset_relation = dbt_artifacts.get_relation(dataset) %}
                {{ log("Truncating table: " ~ dataset_relation, info=True) }}
                {% do adapter.truncate_relation(dataset_relation) %}
            {% endif %}

Here is the whole macro:

{% macro upload_results(results) -%}

    {% if execute %}

        {% set datasets_to_load = ['exposures', 'seeds', 'snapshots', 'invocations', 'sources', 'tests', 'models'] %}
        {% set datasets_to_truncate = ['exposures', 'seeds', 'snapshots', 'sources', 'tests', 'models'] %}
        {% if results != [] %}
            {# When executing, and results are available, then upload the results #}
            {% set datasets_to_load = ['model_executions', 'seed_executions', 'test_executions', 'snapshot_executions'] + datasets_to_load %}
        {% endif %}

        {# Upload each data set in turn #}
        {% for dataset in datasets_to_load %}
            {% if dataset in datasets_to_truncate %}
                {% set dataset_relation = dbt_artifacts.get_relation(dataset) %}
                {{ log("Truncating table: " ~ dataset_relation, info=True) }}
                {% do adapter.truncate_relation(dataset_relation) %}
            {% endif %}

            {% do log("Uploading " ~ dataset.replace("_", " "), true) %}

            {# Get the results that need to be uploaded #}
            {% set objects = dbt_artifacts.get_dataset_content(dataset) %}

            {# Upload in chunks to reduce the query size #}
            {% if dataset == 'models' %}
                {% set upload_limit = 50 if target.type == 'bigquery' else 100 %}
            {% else %}
                {% set upload_limit = 300 if target.type == 'bigquery' else 5000 %}
            {% endif %}

            {# Loop through each chunk in turn #}
            {% for i in range(0, objects | length, upload_limit) -%}

                {# Get just the objects to load on this loop #}
                {% set content = dbt_artifacts.get_table_content_values(dataset, objects[i: i + upload_limit]) %}

                {# Insert the content into the metadata table #}
                {{ dbt_artifacts.insert_into_metadata_table(
                    dataset=dataset,
                    fields=dbt_artifacts.get_column_name_list(dataset),
                    content=content
                    )
                }}

            {# Loop the next 'chunk' #}
            {% endfor %}

        {# Loop the next 'dataset' #}
        {% endfor %}

    {% endif %}

{%- endmacro %}

Then, in your dbt_project.yml, refer to your version of the macro:

on-run-end:
  - "{% if target.name == 'prod' %}{{ upload_results(results) }}{% endif %}"