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

Help
1

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?

2

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

vars:

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

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

then in your sources.yml

define as follows:

version: 2

sources:

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

then in your model refer to the source as follows:

xyz_view.sql:

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

hope this helps.