If you are using SQL Server with dbt, odds are that you probably have some stored procedures lurking in your database. And of course, the sql job agent is probably running some of those on a cron. I want to show another way to approach these, using dbt run-operations and GitHub actions. This will allow you to have a path towards moving your codebase into a VCS like git.
Unwrapping your wrapper with jinja
The pattern I am most familiar with is using the sql agent to run a “wrapper”, which servers to initialize the set of variables to pass into your stored procedure. The way I have done this with dbt is a bit different, and split into two steps: 1) writing the variables into a dbt model and 2) passing that query into a table that dbt can iterate on.
Since your model to stuff the variables into a table (step 1) is highly contextual, I’m not going to provide an example, but I will show how to pass an arbitrary sql query into a table. Example below:
{% set sql_statement %}
SELECT * FROM {{ ref( 'my_model' ) }}
{% endset %}
{% do log(sql_statement, info=True) %}
{%- set table = run_query(sql_statement) -%}For those of you from the SQL Server world – the metaphor here is a temporary table. You can find more about run_query here.
Agate & for loops
What we have created with the run_query macro is an Agate table. This means we can perform any of the Agate operations on this data set, which is pretty neat! In our case, we are going to use a python for loop and pass in the rows of our table.
{% for i in table.rows -%}
{% set stored_procs %}
EXECUTE dbo.your_procedure
@parameter_1 = {{ i[0] }}
, @parameter_2 = {{ i[1] }}
{% endset %}
{%- do log("running query below...", info=True) -%}
{% do log(stored_procs, info=True) %}
{% do run_query(stored_procs) %}
{% set stored_procs = true %}
{% endfor %} The clever thing to do here with python is that we can pass multiple columns into our stored procedure, which differs from something like dbt_utils.get_column_values that can also be used as part of a for loop, but only for a single column. In this case we can reference which column to return from our table with variable[n], so i[0] returns the value in the first column in the current row, i[1] returns the second column and so on.
Building the entire macro
Now that we have the guts of this worked out, we can pull it together in an entire macro. I’m adding ‘dry_run’ flag so we can see what the generate SQL is for debugging purposes, without having to execute our procedure. As a side note, you could also build this as a macro that you run as pre or post hook, but in that case you would need to include an ‘if execute‘ block to make sure you don’t run the proc when project is compiled and so on.
-- Execute with: dbt run-operation my_macro --args '{"dry_run": True}'
-- to run the job, run w/o the args
{% macro my_macro(dry_run='false') %}
{% set sql_statement %}
SELECT * FROM {{ ref( 'my_model' ) }}
{% endset %}
{% do log(sql_statement, info=True) %}
{%- set table = run_query(sql_statement) -%}
{% for i in table.rows -%}
{% set stored_procs %}
EXECUTE dbo.your_procedure
@parameter_1 = {{ i[0] }}
, @parameter_2 = {{ i[1] }}
{% endset %}
{%- do log("running query below...", info=True) -%}
{% do log(stored_procs, info=True) %}
{% if dry_run == 'false' %}
{% do run_query(stored_procs) %}
{% endif %}
{% set stored_procs = true %}
{% endfor %}
{% do log("my_macro completed.", info=True) %}
{% endmacro %}Running in a Github action
Now that we have the macro, we can execute in dbt with ‘dbt run-operation my_macro’. Of course, this is great when testing but so no great if you want this in production. There are lots of ways you run this: on-run-start, on-run-end, as a pre or post-hook. I am not going to do that in this example, but instead share how you can run this a stand alone operation in github actions. I’ll start with the sample code.
name: run_my_proc
on:
workflow_dispatch:
# Inputs the workflow accepts.
inputs:
name:
# Friendly description to be shown in the UI instead of 'name'
description: 'What is the reason to trigger this manually?'
# Default value if no value is explicitly provided
default: 'manual run for my stored procedure'
# Input has to be provided for the workflow to run
required: true
env:
DBT_PROFILES_DIR: ./
MSSQL_USER: ${{ secrets.MSSQL_USER }}
MSSQL_PROD: ${{ secrets.MSSQL_PROD }}
MSSQL_LOGIN: ${{ secrets.MSSQL_LOGIN }}
jobs:
run_my_proc:
name: run_my_proc
runs-on: self-hosted
steps:
- name: Check out
uses: actions/checkout@master
- name: Get dependencies # ok guess I need this anyway
run: dbt deps --target prod
- name: Run dbt run-operation
run: dbt run-operation my_macroAs you can see – we are using ‘workflow_dispatch’ as our hook for the job. You can find out more about this in the github actions documentation. So now what we have in github is the ability to run this macro on demand with a button press. Neat!
Closing thoughts
One of the challenges I have experienced with existing analytics projects on SQL Server and dbt is “what do I do about my stored procedures”. They can be very hard to fit into the dbt model in my experience. So this is my attempt at a happy medium where you can continue to use those battle tested stored procedures while continuing build out and migrate towards dbt. Github actions is a simple, nicely documented way to start moving logic away from the sql job agent, and you can run it “on-prem” if you have that requirement. Of course, you can always find me on twitter @matsonj if you have questions or comments!