Good practices & incremental models for SQL Server

Help
, , , ,
1

Hi there!
I am a little bit blocked regarding the use, in a SQL server of:

  • Jinja templating
  • dbt incremental models
  • dbt Snapshots

The problem I’m having:

1. How to compile a query to run it in a SQL server

When declaring variables (sources, references…) I’m not able to compile models as the SQL Server wants to be fed. If I want to declare a dbt FROM clause like:

{{ source("<my_schema>", "my_table")}}

I don’t know how to do it, taking into account that my compiled query should looks like

FROM [<my_sql_db>].[<my_schema>].[<my_table>]

2. Migrate the BQ work to the SQL server

I do need to:

  • Fix the compiling
  • Create incremental models in the SQL Server
  • Use dbt snapshots to set validity/expiration dates of rows for historical tables

The context of why I’m trying to do this

I’m used to work with dbt in BigQuery. I have a full pipeline working with snapshots to create historical tables. Now I have to replicate the same in a SQL Server.

What I’ve already tried

  • Install dbt-sqlserver library. It works when running a model but all of them have the path hardcoded, which I am trying to avoid . Also, the dbt-sqlserver library conflicts with my sqlfluff one. I will need to run the linter eventually.

To compile the sql file properly I tried:

  • To use an identificer but it does not compile it properly, as it treats the identifier as an alias for the table, not for the whole path

    image
    image1406×463 33.6 KB

  • To set the full path as a global variable from the project, and call it in the FROM clause, but that means not establishing that path as a source, and seems a patchy/unefficient workaround I would like to avoid

Any feedback regarding this questions are more than welcome
Thanks!