In some cases, forcing execution of a branch not normally taken could cause runtime errors, parse errors, etc. The linter should ignore these, but in case of potential side effects (e.g. it's common in dbt to call out to the database, perhaps other external services), we should provide a way to suppress this behavior, e.g. a comment like:

{% if True %}
    SELECT 1
{% else %}  --ignore: coverage
    SELECT 2
{% endif %}

This is somewhat related to #4114.

This issue involves bookkeeping on whether each if statement is executed, then changing the trace template code to force it to be executed next time.
#4114 involves bookkeeping on how many times each for loop is executed, then discarding extra executions.

Hey all, is it possible to run sqlfluff for jinja (dbt) in a way it resolves all possible if-else nodes? For example, in my dbt project I have this:

<base code>
{% if is_incremental() %}
<code A>
{% else %}
<code B>
{% endif %}

and I would like to validate <base code> + <code A> and <base code> + <code B>. Currently it's only validating <base code> + <code A>.

The output of the parse command for else block is: [Type: 'compound', Raw: '{% else %}... [20 unused template characters] ...{% endif %}'].
I also tried to set ignore_templated_areas = False but it did not help.

Thanks for your reply.
I think I understand, within the dbt project you have a single dbt model.sql - this model contains a macro that iterates through a list.

1. The 'tables' that are created, are these dbt models / .sql files that are contained within the dbt project? - I don't quite understand how these tables are 'created' / where they reside.
2. Do you lose lineage?

For info, I've created a function in R, as more familiar with that than Python.
I provide a list of sink tables, in this case these are all {{ source }} objects and the function outputs a model / .sql file for each item in the list.

I've set incremental load within the project.yml for all models within models\raw folder where the function output to:

fn_create_dbt_raw_models_script <- function(alist, schema, folder_path) {
  
  for (table_name in alist) { 
    dbt_script <- glue('
                              with source as (
                              
                                  select *
                                  from {{{{ source(\'{schema}\', \'{table_name}\') }}}}
                              
                              )
                              
                              select * from source
                              
                              {{% if is_incremental() %}}
                              
                              -- this filter will only be applied on an incremental run
                              where SysStartTime > (select max(SysStartTime) from {{{{ this }}}})
                              
                              {{% endif %}}
                          ')
    
    print({table_name})    
    print(dbt_script)    
    print('start write')
    
    # Writes .sql file/model
    write.table(dbt_script,
                file = paste0(folder_path, table_name,'.sql', sep = ""),
                row.names=FALSE,
                col.names=FALSE,
                quote=FALSE)
    
    print('finish write')
    
  }
}

Hi folks!!,
I am trying to implement the incremental model in dbt. Below is the code snippet. The incremental feature is failing (the data consumption is same with/without the incremental feature added). Do let me know, if I am missing anything here?

{{config(
        materialized = 'incremental',
        unique_key = ['user_id','sessionId'],
        incremental_strategy = 'insert_overwrite',
        partition_by= {
           "field": "signup_dt",
           "data_type": "timestamp",
           "granularity": "day"
          }
        )

}}

select
current_timestamp() as row_update_dt,
signup_dt,
user_id,
sessionId
from {{ source('schema', 'table_source') }},
where 1=1
{% if is_incremental() %}
    and date(_PARTITIONTIME) > (select date(max(row_update_dt)) from {{ this }})
{% endif %}

Here is an example where we override a ref.

{%- macro incr_ref(model_name)-%}
  {%- set node = ref(model_name) -%}
  {%- set cols = adapter.get_columns_in_relation(this)|map(attribute='column')|list|lower -%}

  {%- if is_incremental() -%}
    (
      SELECT 
        * 
      FROM 
        {{node}} 
      WHERE 
      basic incremental logic goes here. I don't think this is the issue.
  )
  {%- else -%}
    (
      SELECT * FROM {{node}}
    )
  {%- endif -%}
{%- endmacro -%}

in the model its basically a select some_columns from {{incr_ref('some_model')}}
I have a feeling it errors in any macro where adapter.function is invoked.

the first time that the model run the model, the table will be created and the pre_combined key will be added, in this run the is_incremental section is not evaluated.
On the 2nd time that you run the is_incremental section will be evaluated, and should work.

Few notes:

• timestamp need to exist in your source table as you fetch based on that
• you are using the last timestamp added in the destination table to pick data from the source, most likely you will miss some data points.

I think that you are missing something in your model to de-duplicate the input data.
That’s how I will do:

{{ config(
    materialized='incremental',
    file_format='hudi',
    incremental_strategy='merge',
    unique_key='id',
    pre_combine_key='timestamp',
    partition_by=['date_partition'],
    on_schema_change='append_new_columns',
    custom_location='s3://bucket/analytics/table/table_name' --e.g
    )
}}

WITH SOURCE AS (
	SELECT
      id
      ,date
      ,date_partition
  FROM {{ source('db_raw_dev', 'raw_table_poc_dbt_hudi')}}
  {% if is_incremental() %}
    WHERE timestamp >= (SELECT max(timestamp) FROM {{ this }}) 
  {% endif %}
  ),
ordered as (
select *, 
row_number() over (partition by id order by timestamp desc) as rn
from SOURCE

SELECT * FROM SOURCE
where rn=1

This model will first deduplicate the data then rerun the only latest record:
2e419c8c2813905034b539fc0ab9c321 2022-10-26
NOTE: I used timestamp in the source to build the deduplication, that is better than using a date, as if you have records with the same date you want to be sure to pick the latest.

dbt merge/incremental is not de-deduplication for you.

I've already started merging in some of the logic for this, so I've assigned this issue to me for now.

@alanmcruickshank -- do you feel like you have a good path for fixing this in SQLFluff? If not, we've discussed potential ways to override/set state consistently for issues like linting conditional logic so conditionals always compile in a certain direction. The idea would be to give you some levers to pull to get more consistent results out of dbt compile. Wanted to check with you before I open a dbt-core issue though, as if you feel you've got a handle on this we probably wouldn't need this. Let me know, thanks!

@gwenwindflower - I think we've got a good solution for the Jinja parsing bit already 👍

The part that is causing me a headache is managing multiple versions of a file and getting them to all play nice with each other.

That being said - if you've got good suggestions for making this easier then I'm all ears!

@alanmcruickshank cool! i'm
a) really glad to hear yall found a way forward for the pesky jinja conditionals issues! that's amazing!
b) curious to hear more about the versioning problem (feel free to email me at winnie at dbtlabs if that's easier, and we could potentially set up some time between yall and our DX team, since we use SQLFluff in the product we have a solid mandate to make sure we support it 😸 )

re our ideas it was more or less this, basically allowing people to construct state in a very precise way, then figuring out what the best way for sqlfluff to use that in compile would be (for instance letting people customize the args to the compile command for the dbt templater via the .sqluff config)

@gwenwindflower - check out #5822 which supports a very minimal version of this.

It might be best to get on the phone to talk more though and work through some of the details. In particular, this feature is still very experimental so it might not be ready for the big time yet - totally worth starting to test out to find bugs though!