Make BigQuery cache lookups case-insensitive

[haukeduden]

UPDATE: added step 4 to reproduction recipe

Describe the bug

The is_incremental() macro always returns false when the target is BigQuery, effectively disabling the incremental build feature.

Steps To Reproduce

1. Create a fresh DBT project with dbt init
2. Configure the profile to use BigQuery
3. Change the "my_first_dbt_model.sql" file to this:

{{ config(materialized='incremental') }}
select 1 as id
{% if is_incremental() %}
THIS SHOULD CAUSE AN ERROR
{% endif %}

4. execute "dbt run" to initially create the table
5. execute "dbt run" again
   => no error is displayed (bug)
6. execute "dbt run --bypass-cache"
   => error is displayed (expected behaviour)

Expected behavior

An error message should be displayed when calling "dbt run" because the SQL inside the is_incremental condition is invalid.
When running "dbt run --bypass-cache" the result is as expected.

Additional information

I did a little debugging and verified that is_incremental is indeed returning false. The cause is that adapter.get_relation apparently returns None. Since the problem vanishes when --bypass-cache is specified, I suspect that the problem is caused by a bug in the relation cache.

System information

Which database are you using dbt with?

• postgres
• redshift
• [X ] bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

installed version: 0.14.2
   latest version: 0.14.2

Up to date!

The operating system you're using:
macOS Mojave

The output of python --version:
Python 3.7.4

[drewbanin]

hey @haukeduden - I think this is the normal and expected behavior of the is_incremental() macro -- this macro should return True when:

1. a model is materialized as incremental AND
2. the --full-refresh flag was not provided AND
3. the target table exists in the database

I think the issue you're seeing is that for the first run of a model, the destination table does not yet exist in the database.

The is_incremental() block is frequently useful for self-queries, eg.

-- models/my_model.sql
select *
from source_table
{% if is_incremental() %}
  where event_time > (select max(event_time) from {{ this }})
{% endif %}

Here, {{ this }} refers to the model itself, eg analytics.my_model. The is_incremental() logic prevents this self-query from running if analytics.my_model does not yet exist in the database, that is: when adapter.get_relation returns None.

It's certainly possible that there is some other cache-related bug at play here, but I'm not sure that the example code you sent through exercises that bug if it does exist. I'm going to close this issue out, but please feel free to follow up if I have the wrong idea - happy to discuss this further.

[haukeduden]

Hi Drewbanin,
thank you for the quick response. I realize now that my reproduction recipe was oversimplified. I have modified the original problem description and added an additional run step.
The problem occurs even if the target table already exists, so it would seem that this is an actual bug.

[drewbanin]

@haukeduden thanks for the additional info. When I try to reproduce this, I do see dbt failing on the second invocation of dbt run.

If this is a cache-related issue, then it probably has something to do with the state of your particular BQ project. Does your project name or dataset name contain any interesting/special characters? When we've seen caching issues like this in the past, they usually had to do with case-sensitive comparisons in the cache lookup. Any chance your dataset is named something like MydbtProject or something like that?

[haukeduden]

Yes, the dataset has some camel-casing. It is called "AshData".

[haukeduden]

I just tried again with a lower-case dataset name. Then I also get the expected error message on the second call. So it seems that this might indeed be caused by upper characters in the original dataset name.

[drewbanin]

Thanks @haukeduden - that's super helpful. I appreciate you taking the time to report this one -- we'll take a look at fixing it!