Skip to content
A custom dbt adapter for Microsoft SQL Server using pyodbc as the connection library.
TSQL Python
Branch: master
Clone or download
jacobm001 Update README.md
Removed note about beta release.
Latest commit b9c046b Sep 27, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
dbt Converted Drop If Exists keywords Sep 26, 2019
.gitignore
LICENSE.txt Added a license Aug 1, 2019
README.md Update README.md Sep 27, 2019
setup.py incremented version Aug 21, 2019

README.md

dbt-mssql

dbt-mssql is a custom adapter for dbt that adds support for Microsoft SQL Server versions 2008 R2 and later. pyodbc is used as the connection driver as that is what is suggested by Microsoft. The adapter supports both windows auth, and specified user accounts.

Connecting to SQL Server

Your user profile (located in ~/.dbt/profile) will need an appropriate entry for your package.

Required parameters are:

  • driver
  • host
  • database
  • schema
  • one of the login options:
    • SQL Server authentication
      • username
      • password
    • Windows Login
      • windows_login: true

Example profile:

The example below configures a seperate dev and prod environment for the package, foo. You will likely need to alter the driver variable to match whatever is installed on your system. In this example, I'm using version 17, which is the newest on my system. If you have something else on your system, it should work as well.

foo:
  target: dev
  outputs:
    dev:
      type: mssql
      driver: 'ODBC Driver 17 for SQL Server'
      host: sqlserver.mydomain.com
      database: dbt_test
      schema: foo_dev
      windows_login: True
    prod:
      type: mssql
      driver: 'ODBC Driver 17 for SQL Server'
      host: sqlserver.mydomain.com
      database: dbt_test
      schema: foo
      username: dbt_user
      password: super_secret_dbt_password

Jaffle Shop

Fishtown Analytic's jaffle shop package is currently unsupported by this adapter. At the time of this writing, jaffle shop uses the using() join, and group by [ordinal] notation which is not supported in T-SQL. An alternative version has been forked by the author of dbt-mssql here.

Creating indexes on post-hook

  • To create a nonclustered index for a specific model, go to that model's SQL and add a config macro with a pre-hook and post-hook key/value pair.
  • Whenever you create_nonclustered_index on a post-hook, we recommend you drop_all_indexes_on_table on a pre-hook.
  • You can create more than one index on a model in the post-hook by submitting a bracketed list of create_nonclustered_index macros.
  • See examples below.

Macro Syntax

  • create_clustered_index takes one argument:
    • column - a quoted string that refers to the column you want to create a clustered index on
  • drop_all_indexes_on_table needs no arguments.
  • create_nonclustered_index takes two arguments:
    • columns - a list of quoted strings that refer to the column names you want to create the index on
    • includes - a list of quotes strings that refer to the column names that you want to include in the index look-ups.

Create one index on a model

{{ 
    config({
      "pre-hook": "{{ drop_all_indexes_on_table() }}",
      "post-hook": "{{ create_nonclustered_index(columns = ['some_column'], includes = ['another_column']) }}"
    }) 
}}

Create many indexes on a model

{{ 
    config({
      "pre-hook": "{{ drop_all_indexes_on_table() }}",
      "post-hook": [
         "{{ create_clustered_index(column = 'some_table_key'}}",
         "{{ create_nonclustered_index(columns = ['some_column']) }}",
         "{{ create_nonclustered_index(columns = ['a_colmumn', 'the_column']) }}",
         "{{ create_nonclustered_index(columns = ['this_column', 'that_column'], includes = ['my_column', 'your_column']) }}"
	 ]
    }) 
}}
You can’t perform that action at this time.