Multiple models in Database from a Single DBT model

user_17234713 0
Viewed 149

I am thinking to publish two models in the database from one model in DBT. One model is going to be with filter and the other one is going to be without filter. Have you ever created such a model like this with some jinjas? It would be so helpful if you can share with me some examples or resources please. I would not create two models as they are the same models only the filter is applied as a difference. So the idea is to keep one sql file and publish two models with different names into the database.

I look forward to hear some suggestions from you.

Many thanks!

dbt snowflake-cloud-data-platform jinja2
2 Answers

Short answer is you cannot do this with a single model, but there are lots of good alternatives. See this question for a discussion about why you can't produce multiple assets from a single dbt model.

In this instance, since you're just applying a filter, I would just create two models, with one selecting from the other. This is a bread-and-butter use case of dbt's DAG.

unfiltered.sql

select
    -- your logic goes here
    ...

filtered.sql

select *
from {{ ref('unfiltered') }}
where
    -- your filter goes here
    ...

If that doesn't work, and the models share a lot of logic, but not all of it, I would wrap the common bits in a macro, and then invoke the macro in both unfiltered.sql and filtered.sql (as in the other answer I linked to).

user_108130820

Thank you very much for giving the idea.
I have also another solution actually and kindly would like to share it with you.

  1. Create the table without filter

  2. Post hook clone the created table

  3. Delete from cloned table where the filter applied.

post_hook helps a lot to make it happen.
So basically the post_hook is going to be like this:

{{ config(
    materialized='table',
    post_hook= [ "create or replace table table_filtered clone {{this}}",
                 "delete from table_filtered where 1=1 and filter=true" ]
) }}

By cloning the table, we can keep the descriptions at both column and table level.

user_172347130
Related