The problem I’m having
I am trying to keep the Primary and Foreign key relationships in dbt, but by default dbt will drop and recreate objects in the database during each dbt run, thus the metadata is not preserved in this process.
Note: I am only trying to display the metadata and make sure that it is preserved after every run, I do not care about actually enforcing the primary key rules.
The context of why I’m trying to do this
We are using SqlDBM as our data modeling tool and it has a feature to directly upload our code from Snowflake. When that happens, if there are primary and foreign keys already in the code (specifically foreign and primary keys) those relationships are automatically established and displayed as such within SqlDBM. Right now, due to the models recreating on the nightly runs, we have to manually enter the PKs and FKs for each model every time we pull models into SqlDBM. We would like to permanently establish these relationships in dbt/Snowflake so that when we pull the code into SqlDBM the foreign key relationships between models are displayed, along with showing the Primary Key of each model.
What I’ve already tried
Manually setting primary and foreign key relationships in Snowflake, but they get removed when the tables get recreated on our nightly run.