Case sensitivity for databases, schemas and identifiers

[sdebruyn]

This issue is meant to centralize thoughts around quoting and case sensitivity.

In dbt 1.5 new tests for the caching mechanism were added. These, once again, failed on issues related to quoting and case sensitivity. There have been numerous issues related to these topics in the past.

dbt-core is built around Postgres and in Postgres an identifier is case sensitive if it is surrounded with quotes. So the quoting defines the case sensitivity.

In T-SQL, an identifier is case sensitive if it's in a case sensitive collation. The collation can be set on multiple levels: database level and column level.

it seems that the Postgres implementation just ignores any casing in identifiers depending on the quoting policy. The question is how does this translate to T-SQL?

E.g. in Postgres when the adapter looks for a relation identified by MODEL, it will also give you model and Model and treat those as an exact match. While in SQL Server with a CS collation, those are 3 different models. But the quoting policy can still be false since they don't contain any special characters which would require quoting.

Should always require users to enable quoting for case sensitive collations? And should we use that same policy to determine if we should just lowercase all models and treat them as equal?

[sdebruyn]

Any thoughts?

Pinging @dataders @mikaelene for visibility, but anyone is more than welcome to chime in!

[flunardelli]

Hi, I just like to say that I'm facing a problem related with that (at least I think it is). Using dbt-1.3.x version with dbt-fal, works great but upgrading to ^1.4.0, I'm getting this problem:
11:51:30.034105 [info ] [MainThread]: Completed with 1 error and 0 warnings:
11:51:30.034244 [info ] [MainThread]:
11:51:30.034381 [error] [MainThread]: Compilation Error in model Int_Base_FalTest (models/intermediate/Base/Int_Base_FalTest.py)
11:51:30.034509 [error] [MainThread]: When searching for a relation, dbt found an approximate match. Instead of guessing
11:51:30.034629 [error] [MainThread]: which relation to use, dbt will move on. Please delete "AdventureWorksDW2019"."Staging"."Int_Base_FalTest", or rename it to be less ambiguous.
11:51:30.034745 [error] [MainThread]: Searched for: "adventureworksdw2019"."staging"."int_base_faltest"
11:51:30.034860 [error] [MainThread]: Found: "AdventureWorksDW2019"."Staging"."Int_Base_FalTest"
11:51:30.034975 [error] [MainThread]:
11:51:30.035089 [error] [MainThread]: > in macro is_incremental (macros/materializations/models/incremental/is_incremental.sql)
11:51:30.035217 [error] [MainThread]: > called by macro py_script_postfix (macros/python_model/python.sql)
11:51:30.035406 [error] [MainThread]: > called by model Int_Base_FalTest (models/intermediate/Base/Int_Base_FalTest.py)

[costructuralist]

@flunardelli I'm getting the same error, with dbt-core 1.4.6 and dbt-sqlserver 1.4.3 after running dbt build twice:

When searching for a relation, dbt found an approximate match. Instead of guessing
09:56:39    which relation to use, dbt will move on. Please delete "STAGING"."test"."test-table", or rename it to be less ambiguous.
09:56:39    Searched for: "Staging"."test"."test-table"
09:56:39    Found: "STAGING"."test"."test-table"

profiles.yml:

test_db:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 18 for SQL Server'
      server: # snip
      database: Staging
      schema: dbt
      trusted_connection: true
      trust_cert: true
      threads: 2

[flunardelli]

Hi, @costructuralist. Actually I solved that problem with help from dbt-fal team. I submitted that issue to them and they were able to create a workaround for that sqlserver issue. Do you have a custom section "models" inside dbt_project.yml pointing to different schema/database name case (sorry for that obvious question, but I made that mistake)?
My environment is:
dbt-core==1.4.6
dbt-sqlserver==1.4.3
dbt-fal==1.4.9

[costructuralist]

Hi @flunardelli, thanks for the quick reply. My dbt_project.yml does not contain a custom "models" section.

The issue was more innocent: the name of the database is actually STAGING, not Staging as specified in the profiles.yml (it differs between Dev and Prod...).

This comment alerted me to the possibility: #321 (comment)