Can i use Linked Servers MS SQL Server as dbt sources?

I am using dbt with Microsoft SQL Server. I know that dbt is not designed for data movement, only for transformation. However, in the core of MS SQL there is a mechanism for linked servers to which you can send queries. Is there any way to configure dbt to use a linked server as a source?

Yes you can, first define the linked servers in your dbt_project.yml based on the target environment. for example:


The linked variable will be used as a linked_server name for LN tables

linked_server: |
{%- if == “rdev” -%} “LNDB-T\LN”
{%- elif == “dev” -%} “LNDB-T\LN”
{%- elif == “test” -%} “LNDB-T\LN”
{%- elif == “qa” -%} “LNDB-S1-P1\LN”
{%- elif == “prod” -%} “LNDB-S1-P1\LN”
{%- else -%} invalid_linked_server
{%- endif -%}

then in your sources.yml

define as follows:

version: 2


  • name: LN_Tables
    database: lndb
    schema: dbo
    • name: tasint000400

then in your model refer to the source as follows:


select A.t_cprj as project, A.t_dsca as project_desc from
{{ var(‘linked_server’) }}.{{ source(‘LN_Tables’, ‘ttppdm600510’) }} A

hope this helps.