Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

dbt quoting does not work as expected #497

Closed
FlorianVc opened this issue Apr 25, 2024 · 2 comments
Closed

dbt quoting does not work as expected #497

FlorianVc opened this issue Apr 25, 2024 · 2 comments

Comments

@FlorianVc
Copy link

The quoting for msft sql not working. Dbt use double quotes when the config quoting is turned on but ms sql or tsql use square brackets.

I need quoting for databases and table. Our table or database prefix is sometimes a number like 101_Customers .

Example:
dbt config:

quoting:
  database: true
  schema: true
  identifier: true

The dbt compilation result:
SELECT * FROM "101_supplier"."core"."supplier"

What I expected:
SELECT * FROM [101_supplier].[core].[supplier]

Question:
Is there a way to configure the quoting for database, schema and identifier with square brackets. Or this issue a topic for the dbt core team?

Additional context
I found a core issue with similar problems: link

@hernanparra
Copy link

The double quotes work fine in MS SQL. Did you try running the query? It works fine with tables that start with numbers. I don't have servers named like that to try.

@bsarge88
Copy link

I think this issue can be closed as 'by design'.
DBT handles database and table names beginning with numbers or contains spaces (ack!).

Sql Server databases have Quoted Identifiers set to False by default but most drivers, including the Sql Server ODBC driver, set Quoted Identifiers are on by default, which is the ANSI standard. If OP is having issues with quoted identifiers it might be the driver he is using.

Here is a good article describing Sql Server's quoted identifiers settings:
https://www.sqlshack.com/set-quoted_identifier-settings-in-sql-server/

Issue 2986 referenced by OP is simply cleaning up terminology that probably evolved during the organic development of the project.

This assumes the DBT configuration has quoting set to true (default).
If it is set to false then tables beginning with numbers or contains spaces will fail.

quoting:
  database: true
  schema: true
  identifier: true

I tested this using the following database and tables:

create database "123 testdb";
use "123 testdb";
create table "123 test" (pk int primary key, one int null, two varchar(100) null);
insert "123 test" (pk, one, two) values (1, 1, 'one')

drop table "123 test";
drop database "123 testdb";

And the following DBT model:

{{ config(materialized='table') }}

with source_data as (
    select *
    from {{ source('123 testdb', '123 test') }}
)
select *
from source_data

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants