Microsoft SQL Server configurations
Materializations
Ephemeral materialization is not supported due to T-SQL not supporting nested CTEs. It may work in some cases when you're working with very simple ephemeral models.
Tables
Tables will, by default, be materialized as a columnstore tables. This requires SQL Server 2017 or newer for on-premise instances or service tier S2 or higher for Azure.
This behaviour can be disabled by setting the as_columnstore
configuration option to False
.
- Model config
- Project config
{{
config(
as_columnstore='False'
)
}}
select *
from ...
models:
your_project_name:
materialized: view
staging:
materialized: table
as_columnstore: False
Seeds
By default, dbt-sqlserver
will attempt to insert seed files in batches of 400 rows.
If this exceeds SQL Server's 2100 parameter limit, the adapter will automatically limit to the highest safe value possible.
To set a different default seed value, you can set the variable max_batch_size
in your project configuration.
vars:
max_batch_size: 200 # Any integer less than or equal to 2100 will do.
Snapshots
Columns in source tables can not have any constraints.
If, for example, any column has a NOT NULL
constraint, an error will be thrown.
Indices
You can specify indices to be created for your table by specifying post-hooks calling purpose-built macros.
The following macros are available:
create_clustered_index(columns, unique=False)
: columns is a list of columns, unique is an optional boolean (defaults to False).create_nonclustered_index(columns, includes=columns)
: columns is a list of columns, includes is an optional list of columns to include in the index.drop_all_indexes_on_table()
: drops current indices on a table. Only meaningful if the model is incremental.`
Some examples:
{{
config({
"as_columnstore": false,
"materialized": 'table',
"post-hook": [
"{{ create_clustered_index(columns = ['row_id', 'row_id_complement'], unique=True) }}",
"{{ create_nonclustered_index(columns = ['modified_date']) }}",
"{{ create_nonclustered_index(columns = ['row_id'], includes = ['modified_date']) }}",
]
})
}}
select *
from ...
Grants with auto provisioning
dbt 1.2 introduced the capability to grant/revoke access using the grants
configuration option.
In dbt-sqlserver, you can additionally set auto_provision_aad_principals
to true
in your model configuration if you are using Azure Active Directory authentication with an Azure SQL Database or Azure Synapse Dedicated SQL Pool.
This will automatically create the Azure Active Directory principal inside your database if it does not exist yet. Note that the principals need to exist in your Azure Active Directory, this just makes them available to use in your database.
Principals are not removed again when they are removed from the grants configuration.
models:
your_project_name:
auto_provision_aad_principals: true
cross-database macros
The following macros are currently not supported:
bool_or
array_construct
array_concat
array_append
dbt-utils
Many dbt-utils
are supported,
but require the installation of the tsql_utils
dbt package.