The problem I’m having
I have a model, let’s call it MYTABLE. Due to circumstances this model fully replaces itself every time it runs rather than using an incremental materialization. I need to run a test to validate keys from the new version of MYTABLE against the keys from the old version to check if the replacement data is missing any rows.
My current idea is to create a separate ephemeral table, let’s call it base__MYTABLE, to save a copy of MYTABLE before the ingestion. Then, I have a test to compare the primary keys of this table to the staging table stg__MYTABLE.
The issue is that since base__MYTABLE needs to reference MYTABLE (as it is a temporary copy of it) it confuses DAG and returns a “Found a cycle” error. I basically need dbt to do this in order:
- Create base__MYTABLE as a copy of MYTABLE
- Create the view stg__MYTABLE from the incoming dataset.
- Have a test to compare the primary keys of base__MYTABLE with stg__MYTABLE
- If the test succeeds, move the data from stg__MYTABLE to MYTABLE.
The context of why I’m trying to do this
Due to circumstances, the MYTABLE model uses a full refresh method to update itself. However, the incoming data from vendor is sometimes erroneously missing prior rows that we need to catch and be notified of. Thus, we need some way to compare the staging version of MYTABLE with the pre-update (current) version of MYTABLE to insure that no primary keys are missing in the updated version.
What I’ve already tried
My current code for base__MYTABLE is something like this:
SELECT [columns] FROM {{ref(‘MYTABLE’)}}
However, this causes the base table to run after MYTABLE is updated which would defeat the purpose of the test. If I include references to base_MYTABLE in either the staging or final tables it runs into a ‘Found a Cycle’ error since base__MYTABLE references MYTABLE and MYTABLE references base__MYTABLE.
Some example code or error messages
RunTimeError: Found a Cycle: model.[redacted].stg__MYTABLE → model.[redacted].base__MYTABLE
Thanks :).