Concurrent transactions from get_delete_and_insert_queries fail in BigQuery

[rloredo]

I have the same issue than this one and it's blocking our Elementary deployment.
We use one run per model in our scheduler and sometimes two models end at the same time and we get the queries cancelled.

Describe the bug The concept of transaction for get_delete_and_insert_queries was introduced in this commit.

However as visible in the documentation:

If a transaction mutates (update or deletes) rows in a table, then other transactions or DML statements that mutate rows in the same table cannot run concurrently. Conflicting transactions are cancelled. Conflicting DML statements that run outside of a transaction are queued to run later, subject to queuing limits.

Therefore if we want to have the delete part we either have:

• have an outside system to limit concurrency so that only one dbt run can concurrently with that transaction
• remove the transaction

To Reproduce Steps to reproduce the behavior:

1. set up a dbt project using Elementary
2. Create a simple model
3. Run concurrently twice the model and hope that they finish around the same time
4. See error generated by the concurrent transaction on dbt_models that would look like:

/* {"app": "dbt", "dbt_version": "1.6.0", "profile_name": "dbt", "target_name": "prod", "connection_name": "master"} */

    
        begin transaction;
        
            delete from `gcp_project`.`elementary`.`dbt_models`
            where
            metadata_hash is null
            or metadata_hash in (select metadata_hash from `gcp_project`.`elementary`.`dbt_models__tmp_table_XXXX`);
        
        
            insert into `gcp_project`.`elementary`.`dbt_models` select * from `gcp_project`.`elementary`.`dbt_models__tmp_table_YYY`;
        
        commit;

Expected behavior I would expect to be able to run concurrently 2 dbt run commands without them failing when they happen to start the Elementary data post hook executing get_delete_and_insert_queries macro query concurrently.

Environment (please complete the following information):

• edr Version: 0.9.2
• dbt package Version: 0.9.0

Additional context Slack thread regarding other people affected: https://elementary-community.slack.com/archives/C02CTC89LAX/p1689186975724739

Potential workaround Override the macro default__get_delete_and_insert_queries by a version without a transaction.

{% macro default__get_delete_and_insert_queries(relation, insert_relation, delete_relation, delete_column_key) %}
    {% set query %}
        {% if delete_relation %}
            delete from {{ relation }}
            where
            {{ delete_column_key }} is null
            or {{ delete_column_key }} in (select {{ delete_column_key }} from {{ delete_relation }});
        {% endif %}
        {% if insert_relation %}
            insert into {{ relation }} select * from {{ insert_relation }};
        {% endif %}
    {% endset %}
    {% do return([query]) %}
{% endmacro %}

However I'm not sure of all the consequences of removing the transaction but since Spark doesn't have transactions, I assume mostly works but it's not "safe"?

[ramon-prieto]

Were you able to solve this?

[rloredo]

@ramon-prieto I've opened a PR here but it's being ignored.
For now I'm overriding the delete_and_insert macro with the code I put in that PR.

[macklin-fluehr]

Upvoting this for the elementary folks?

[elazarlachkar]

Hi @rloredo!

I'm sorry for the delayed response. I'm not sure if your issue is still relevant, but I've looked into it anyway to give you a full reply.

From the issue's description, I can see that your goal is to be able to run concurrently 2 dbt run commands without them failing.

Removing the transaction for the delete_and_insert macro, so that 2 different runs may write to the table at the same time, will make the error disappear. However, the table's data would still be changed by 2 different processes.
As each process deletes and inserts data, the 2 processes might conflict and override each other. The table's data might not be what you expect in a race condition.

This is not an issue related specifically to Elementary, but to dbt in general.
The official dbt docs states that write commands (like dbt run) are:

Limited to one invocation at any given time, which prevents any potential conflicts, such as overwriting the same table in your data platform at the same time.

I understand that your individual runs are selecting a single unique model, so you won't face these issues, but the Elementary artifact is the same model in the different runs.

The Elementary package does include a flag to disable the artifacts upload. If you're not interested in writing them on every run, you may use it. Will it help your case?