dbt quoting does not work as expected

[FlorianVc]

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]

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]

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