Home > Example - Nightly_DBT_Test_Schedule.yml

Example - Nightly_DBT_Test_Schedule.yml

#========================================================================================================================

# -- Description: This workflow is used to perform various operations such as

# 1- Clone prod and deploy all models

# 2- Test all the deployed models in the cloned databaase

#=========================================================================================================================

name: DBT_testing_workflow

# Controls when the action will run.

on:

# Triggers the workflow every day at 08:00 UTC (4PM Perth time)

schedule:

# * is a special character in YAML so you have to quote this string

- cron: '0 8 * * *'

# Environment variable values are retrived from Github secrets

env:

DBT_TARGET: PROD

DBT_SNOWFLAKE_ACCOUNT: ${{ secrets.DBT_SNOWFLAKE_ACCOUNT }}

DBT_USER: ${{ secrets.DBT_PROD_USER }}

DBT_PASSWORD: ${{ secrets.DBT_PROD_PASSWORD }}

DBT_ROLE: PROD_TRANSFORMER

DBT_DB: PROD_ONEDB

DBT_SCHEMA: WH_DW

DBT_WH: PROD_TRANSFORMER_WH

# A workflow run is made up of one or more jobs that can run sequentially or in parallel

jobs:

# This workflow contains two jobs 1. To clone the prod db and 2. to drop the test db.

test:

# The type of runner that the job will run on

runs-on: ubuntu-latest

# Steps represent a sequence of tasks that will be executed as part of the job

steps:

- name: Event info

run: |

echo This workflow on ${{ github.repository }} was started by ${{ github.actor }} from the event ${{ github.event_name }} and action ${{github.event.action}} and ${{github.event.pull_request}}

# Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it

- name: Checkout branch

uses: actions/checkout@v3

- name: Install Python 3.8

uses: actions/setup-python@v4

with:

python-version: 3.8

- name: Install dbt==1.6.0

run: |

python -m pip install --upgrade pip

pip install dbt-snowflake==1.6.0

- name: Install dbt dependency packages

run: |

dbt deps

- name: Create Zero copy clone of Production.

run: |

dbname=testdb_daily_test

dbt run-operation clone_prod_to_test --args '{createordrop : create, dbname: '${dbname}', testrole: PROD_TRANSFORMER}' --profiles-dir ./dbt_profiles

echo "DBT_DB=$(echo '"'${dbname^^}'"')" >> $GITHUB_ENV

- name: drop seed tables

run: |

dbname=testdb_daily_test

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: NON_FLEX_CRM_PERSON}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: NON_FLEX_CRM_PRODUCTS}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: FLEX_LOB}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: NON_FLEX_WC_POSITION}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: WC_ORG_LKP_CATEGORY}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: LENDERGOLIVE}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: THIRD_PARTY_PERMISSIONS}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: THIRD_PARTY_READER_ACCOUNTS}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: WC_ORD_D_SEED}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: MAJOR_LENDERS}' --profiles-dir ./dbt_profiles

dbt run-operation drop_seed_table --args '{dbname: '${dbname}', tablename: NON_FLEX_REV_DATA}' --profiles-dir ./dbt_profiles

- name: Deploy the seeds for this release

run: |

dbt seed --vars '{"batch_name":"github_batch"}' --profiles-dir ./dbt_profiles

- name: Compile-Deploy-test all models

run: |

dbt compile --profiles-dir ./dbt_profiles

dbt run --profiles-dir ./dbt_profiles

dbt test --profiles-dir ./dbt_profiles

- name: Drop the cloned database.

run: |

dbname=testdb_daily_test

dbt run-operation clone_prod_to_test --args '{createordrop : drop, dbname: '${dbname}', testrole: PROD_TRANSFORMER}' --profiles-dir ./dbt_profiles

- name: Run DBT Snapshots

run: |

dbt snapshot