dbt-sqlserver: Overriding the database in the dbt_project file does not work

When I override the database to something different than the database configured in the profile, the CLI says it’s going to built into the override database, but it’s executed on the profile database.

Example profiles.xml:

datawarehouse:
  target: development
  outputs:
    development:
      type: sqlserver
      driver: 'ODBC Driver 17 for SQL Server'
      server: bisql
      port: 1433
      windows_login: True
      database: Test
      schema: test
      threads: 4

example dbt_project.yml:

...
models:
  my_datawarehouse:
    materialized: view
    products:
      +database: ProductsModels
      +schema: ProductsModels

When ran, the CLI says “created view model ProductsModels.ProductsModels.Metrics…” but in reality it’s created in Test.ProductsModels.Metrics

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 1
Comments: 21

Most upvoted comments

Ah, sorry… I changed the materialization to table so it’s obviously using sqlserver__create_table_as now.

CMRobbie on Nov 19, 2020

I’m all for an adventure! 😃 And I’m also happy to wait to next week.

I added a file called sqlserver_create_view_as.sql in my macros folder with this as content:

{% macro sqlserver__create_view_as(relation, sql) -%}
  create view {{ relation.schema }}.{{ relation.identifier }} as
    --THIS IS A TEST
    {{ sql }}
{% endmacro %}

But I have the impression it’s not using it at all, because it’s not logging the “THIS IS A TEST” anywhere. Or maybe I did not understand you?

CMRobbie on Nov 19, 2020

I think the problem is in this part, rather than the generation of the queries: 2020-11-19 19:16:14.545496 (Thread-1): Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=of-bisql-01,1433;Database=Test;trusted_connection=yes 2020-11-19 19:16:14.545496 (Thread-1): Connected to db: Test

It should have connected to the ProductsModels database instead.

CMRobbie on Nov 19, 2020