Github Action pipeline to run CI job on dbt Cloud when PR is approved

run_dbt_cloud_ci_job.yaml

name: Run CI job on dbt Cloud
# After PR is approved, validate the modifications in dbt Cloud

on:
  pull_request_review: # triggers on PR approval
    types:
      - submitted
  pull_request: # triggers on pull requests, so we make sure this won't be skipped
    types:
      - opened
      - reopened
      - synchronize
      - labeled
      - unlabeled

jobs:
  check-approvals:
    name: Check if it's the first approval # this will block the PR before it get it's first approval
    runs-on: ubuntu-latest
    outputs:
      run_ci: ${{ steps.check_approvals_condition.outputs.run_ci }} # this controls the downstream job
    steps:
      - name: Get approvals number and block PR if not approved
        id: check_approvals_condition
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
        run: |
          # Get the number of previous approvals
          previous_approvals=$(curl -s -H "Authorization: Bearer ${{ secrets.GITHUB_TOKEN }}" \
            "https://api.github.com/repos/${{ github.repository }}/pulls/${{ github.event.pull_request.number }}/reviews" | \
            jq '[.[] | select(.state == "APPROVED") ] | length')

          echo "Approvals number: ${previous_approvals}"

          # Check approvals: none -> fails ; 1 -> run CI ; 2+ -> do nothing
          if [ "$previous_approvals" -eq 0 ]; then
            echo "Not approved yet, blocking PR"
            exit 1
          elif [ "$previous_approvals" -eq 1 ]; then
            echo "First approve, will trigger the dbt CI run"
            echo "run_ci=1" >> $GITHUB_OUTPUT
          else
            echo "Second approve or more, won't trigger the dbt CI run again"
            echo "run_ci=0" >> $GITHUB_OUTPUT
          fi

  run-ci-job-in-dbt:
    name: Filter approved Pull Requests only
    needs: check-approvals
    runs-on: ubuntu-latest
    if: ${{ needs.check-approvals.outputs.run_ci == 1 }} # run only on first approval
    env:
      DBT_ACCOUNT_ID: <ACC_ID>
      DBT_PROJECT_ID: <PROJ_ID>
      DBT_PR_JOB_ID: <JOB_ID> # CI job
      DBT_API_KEY: ${{ secrets.DBT_CLOUD_TOKEN }}
      DBT_JOB_CAUSE: 'Pull Request validation'

    steps:
      - name: Checkout
        uses: "actions/checkout@v3"
        with:
          ref: ${{ github.event.pull_request.head.sha }}

      - name: Python set up
        uses: "actions/setup-python@v4"
        with:
          python-version: "3.9"

      - name: Get last commit SHA
        id: get_last_commit
        run: |
          commit_hash=$(git rev-parse HEAD)
          echo "git_sha=$commit_hash" >> $GITHUB_OUTPUT

      - name: Mount schema override
        id: schema_override
        run: |
          schema_override="DBT_CLOUD_PR_<JOB_ID>_${{ github.event.pull_request.number }}"
          echo $schema_override
          echo "schema_override=$schema_override" >> $GITHUB_OUTPUT

      - name: Trigger job in dbt cloud
        id: trigger_job
        env:
          GIT_SHA: ${{ steps.get_last_commit.outputs.git_sha }} # dbt cloud job will use it to checkout at commit
          DBT_JOB_SCHEMA_OVERRIDE: ${{ steps.schema_override.outputs.schema_override }}
        run: "python .github/trigger_dbt_cloud_api.py"

      - name: Comment link in PR
        uses: "actions/github-script@v6"
        with:
          script: |
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: ':link: **Job run in dbt cloud:** ${{ steps.trigger_job.outputs.run_status_link }}'
            })

      - name: Monitor job in dbt cloud
        id: monitor_job
        env:
          TRIGGERED_JOB_RUN_ID: ${{ steps.trigger_job.outputs.triggered_job_run_id }}
        run: "python .github/monitor_dbt_cloud_api.py"

      - name: Dismiss previous approvals on failure
        if: steps.monitor_job.outputs.run_succeeded != 'true'
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
          REPO_OWNER_AND_NAME: ${{ github.repository }}
          PR_NUMBER: ${{ github.event.pull_request.number }}
        run: |
          pip install requests
          python .github/dismiss_reviews.py

      - name: Comment failure in PR
        if: steps.monitor_job.outputs.run_succeeded != 'true'
        uses: "actions/github-script@v6"
        with:
          script: |
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: ':x: **Job run failed**. Approvals dismissed. Please check: ${{ steps.trigger_job.outputs.run_status_link }}'
            })