[Bug]: Dim Models grows indefinitely, dbt models reinserted on every execution

[miguermv]

Overview

We're seeing an issue with the dbt_artifacts package, specifically with the dim_models table. Every time we run a dbt command, all models in the project are being reinserted into this table instead of being updated or deduplicated. As a result, it grows extremely fast. After just a few days, we end up with around 16 million rows, even though we only have about 1,500 models in our dbt project.

How to reproduce

1. Use dbt_artifacts in a dbt Core project (we're using Snowflake).
2. Run any dbt command (dbt run, dbt build, etc.).
3. Check the dim_models table.
4. Notice that instead of updating existing rows, all models are added again.

Expected behaviour

dim_models should have one row per model, updated when necessary, not duplicated on every run. The row count shouldn't grow if the models haven't changed.

Environment

dbt --version:

Core:
  - installed: 1.10.10

packages.yml:

packages:
  - package: metaplane/dbt_expectations
    version: 0.10.9

  - package: dbt-labs/dbt_utils
    version: 1.1.1

  - package: dbt-labs/codegen
    version: 0.12.1

  - package: elementary-data/elementary
    version: 0.15.2

  - package: get-select/dbt_snowflake_query_tags
    version: [">=2.0.0", "<3.0.0"]

  - package: brooklyn-data/dbt_artifacts
    version: 2.9.3

[LuizAstorga]

Hi @miguermv,

Are you working on this? Can we schedule a call to discuss? I'm confused about what you mean by 'instead of being updated or deduplicated.'

Thanks!

[vergenzt]

@miguermv from my reading of the code I think that's deliberate. It appears to be only ever doing inserts.

If you want to only keep the latest you should probably add an on-run-end hook to your project to delete non-current model records from the table.

Fortunately it looks like the authors of this package did most of the work to do this for you by creating dim_dbt__current_models !

Something like this should work to delete any non-current model invocation records from the raw models table (and thus should propagate up to dim_dbt__models once that's refreshed):

# in dbt_project.yml
# (pseudocode, I don't think you can do joins in delete clauses and also `anti join` and `using`
#  aren't always available across dialects... but you get the idea)
on-run-end:
- >-
    delete from {{ ref('models') }}
    anti join {{ ref('dim_dbt__current_models') }}
      using (command_invocation_id, node_id)

[pltrant]

We had the same problem. While getting the current state of all the artifacts is useful, we don't need it for every dbt invocation. However, we are interested in the execution results from every run. To work around this, we created a new macro that is mostly a copy/paste of the package's dbt_artifacts.upload_results, but it configured so it only uploads the executions. Then once a day, we have a job that uploads all the artifacts.

Our on-run-end hook uses {{ dbt_artifacts_upload(results) }} and our daily job uses dbt run-operation dbt_artifacts_upload --args '{data: all}'

{# This macro is mostly a copy/paste of the dbt_artifacts.upload_results macro, but it has been modified to accept
a parameter to conditionally only upload a subset of the data. #}

{% macro dbt_artifacts_upload(results, data) -%}

    {% if execute %}

        {# By default, only upload executions #}
        {% set datasets_to_load = [] %}
        {% if data == "all" %}
            {% set datasets_to_load = ['exposures', 'seeds', 'snapshots', 'invocations', 'sources', 'tests', 'models'] %}
        {% endif %}
        {% 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 %}

            {% 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 %}