Using Jina in a config block

Help
,
1

Hi all,

I am using dbt-core 1.6.9 on an Exasol .

I am trying to use jina in a config block like this:
{% set is_delta_run = run_query(“SELECT count(*) < 100000000 FROM DW_TEMP.FACT_TICKET_FULL_DELTA;”) %}

{{ config(
enabled=true,
pre_hook = [“{{pre_t__rep__agg__ticket(this.name)}}”],
post_hook = [“{{post_t__rep__agg__ticket(this.name)}}”],
{% if is_delta_run %}
materialized= ‘incremental’
{% else %}
materialized= ‘table’
{% endif %}
)
}}

The goal is to let my model be incremental or table depending on the outcome of a an sql i run first and save that result to a variable, that is used in the if-clause.

The error im getting is this:
invalid syntax for function call expression
line 18
{{ config(

So it complains about a line he wouldnt complain about, when i just put in materialized = incremental without the jinja. Is it possible to use jinja there or how would you solve my problem? Thanks a lot for any help or guidance!

Greetings

2

Your configuration is one block so cannot contain if statements.
Create a seperate macro called something like delta_materialiization and set materialized=delta_matwrialization()

So.ething like that should work

Note: @NickP originally posted this reply in Slack. It might not have transferred perfectly.

3

The correct syntax would be

materialized= 'incremental' if is_delta_run else 'table'

Also, your query will return a table, not a boolean, if you want to return the value, use the macro dbt_utils.get_single_value()

Anyway, I don’t think it will work because dbt has two phases:

  1. Reads all of the files in your project and generates a “manifest” comprised of models, tests, and other graph nodes present in your project. During this phase, dbt uses the ref statements it finds to generate the DAG for your project. No SQL is run during this phase, and execute == False.
  2. Compiles (and runs) each node (eg. building models, or running tests). SQL is run during this phase, and execute == True.

In the first phase it will define the configurations

In the second phase it will run the query you are trying to run

So, it will not follow the order you want

4

Hey,

thanks for the input! From the correct syntax you provided I am deriving then it is somehow possible to let a model be either incremental or table, but the variable has to be defined without running an SQL. Do you have an idea on how to approach that?

Thanks and greetings

5

What is your business problem
Based on what you posted you want table to switch to delta o ce it reaches a certain amount of rows without having to go in and change model ?

Note: @NickP originally posted this reply in Slack. It might not have transferred perfectly.

6

The business problem is this: We are loading data into a large table and we want to do it in delta (incrementally), if the delta is lower than 100 million or in full, if it is more than 100 mio. We are calculating that delta with SQLs. Migrating from plain SQL to dbt. There are a lot of post calculations which differ depending on delta or full, but many things are the same, which is why I would love to keep it in one model, so if I add a column to that table I only have to do it in one place.

7

I agree you should only have one Path for your data. Moving to dbt from another platform usually required you to break ip the code with more intermediate models to ensure the data flows efficiently.

Note: @NickP originally posted this reply in Slack. It might not have transferred perfectly.