Orchestrator using DBT

Help
1

The problem I’m having

  • Execute group of models based on output of a SQL query.
  • It should be dynamically executed at specific recurring times.
  • ERROR: (‘42000’, “[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword ‘select’. (156) (SQLMoreResults)”)
  • The executing model file my_run.sql in run\project_name\models shows the following:
    USE dataset;
    EXEC('create view “schema”.“my_run” as
    – depends_on: “db”.“schema”.“model1”, “db”.“schema”.“model2”, “db”.“schema”.“model3”
    select * from “db”.“schema”.“model1”

select * from “db”.“schema”.“model2”

select * from “db”.“schema”.“model3”;');

  1. There should be the actual model query in the create view EXEC. But it shows multiple selects which is not valid in SQL Server
  2. EXEC(‘select * INTO …’) is not there
  3. EXEC(‘DROP view …’) is also not there
  4. Each of the models should be executed separately

The context of why I’m trying to do this

  • I have multiple Data Quality rule (queries) models (Completeness, Integrity, Validity etc.)
  • Some are daily, some weekly categorized in phases.
  • The output is available from an SQL query from SQL Server e.g. get_phase_and_frequency table
  • Only those models should execute which has a corresponding frequency in the output of the query

What I’ve already tried

  1. Created models with {{ config(materialized = “table”, tags=[‘weekly_Phase1’], sort = ‘id’, alias=“SQL_Server_specific_name”) }}
  2. Created macro to get the list of frequencies available in the SQL output (successfully executes)
  3. Get list of the models that has the tags corresponding to the output of the SQL Output using graph.nodes.value() (executes successfully)
  4. execute the models one by one using {{ ref(model_variable) }} in a second macro.
  • I do not want to leave the dbt framework for any of the tasks mentioned above
  • SQL Server Stored Procedures will be converted to dbt .sql
  • Orchestration should be done within dbt etc.

Thanking you in anticipation.

Regards,
Debashish

2

Got the resolution for the above issue:
called the generic macro in each model (DQ rules) sending the this.name to the macro and executed all the models for the corresponding frequency to the tags of the models.