pr_workflow.yml

name: Pull Request testing
on: [pull_request, workflow_dispatch]

# GitHub secrets
env:
  DBT_PROFILES_DIR: ./integration_tests
  GITHUB_SHA_OVERRIDE: ${{ github.event.pull_request.head.sha }} # We need the commit hash of the pull request branch's head, the GITHUB_SHA env var is always the base branch in a pull_request_target trigger
  DBT_ENV_SECRET_BIGQUERY_TEST_SERVICE_ACCOUNT: ${{ secrets.DBT_ENV_SECRET_BIGQUERY_TEST_SERVICE_ACCOUNT }}
  DBT_ENV_SECRET_BIGQUERY_TEST_STORAGE_PROJECT: ${{ secrets.DBT_ENV_SECRET_BIGQUERY_TEST_STORAGE_PROJECT }}
  DBT_ENV_SECRET_BIGQUERY_TEST_EXECUTION_PROJECT: ${{ secrets.DBT_ENV_SECRET_BIGQUERY_TEST_EXECUTION_PROJECT }}
  DBT_ENV_IGQUERY_TEST_LOCATION: ${{ secrets.DBT_ENV_SECRET_BIGQUERY_TEST_LOCATION }}
  DBT_BQ_MONITORING_GCP_PROJECTS: ${{ secrets.DBT_BQ_MONITORING_GCP_PROJECTS }}

concurrency:
  cancel-in-progress: true
  group: ${{ github.workflow }}-${{ github.ref }}

jobs:
  integration-bigquery:
    runs-on: ubuntu-latest
    environment:
      name: Approve Integration Tests

    steps:
      - name: Checkout
        uses: actions/checkout@v4
        with:
          ref: ${{ github.event.pull_request.head.sha }} # Check out the code of the PR
      
      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Install tox
        run: python3 -m pip install tox

      - name: Write keyfile if secret is defined
        run: |
          if [ -z "${{ secrets.DBT_ENV_SECRET_BIGQUERY_TEST_SERVICE_ACCOUNT }}" ]; then
            echo "Error: DBT_ENV_SECRET_BIGQUERY_TEST_SERVICE_ACCOUNT is not defined."
          else
            echo ${{ secrets.DBT_ENV_SECRET_BIGQUERY_TEST_SERVICE_ACCOUNT }} | base64 -d > ./integration_tests/keyfile.json  
          fi

      - name: Run All models on BigQuery
        run: tox -e bigquery

  sqlfluff-lint-models:
    name: Lint dbt models using SQLFluff
    runs-on: ubuntu-latest
    environment:
      name: Approve Integration Tests

    steps:
      - name: Checkout branch
        uses: actions/checkout@v4
        with:
          ref: ${{ github.event.pull_request.head.sha }} # Check out the code of the PR

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'    

      - name: Install Python packages
        run: python -m pip install dbt-bigquery~=1.8.2 sqlfluff-templater-dbt

      - name: Write keyfile if secret is defined
        run: |
          if [ -z "${{ secrets.DBT_ENV_SECRET_BIGQUERY_TEST_SERVICE_ACCOUNT }}" ]; then
            echo "Error: DBT_ENV_SECRET_BIGQUERY_TEST_SERVICE_ACCOUNT is not defined."
          else
            echo ${{ secrets.DBT_ENV_SECRET_BIGQUERY_TEST_SERVICE_ACCOUNT }} | base64 -d > keyfile.json  
          fi

      - name: Test database connection
        run: dbt debug

      - name: Install dbt packages
        run: dbt deps

      - name: Get changed files
        id: get_file_changes
        uses: trilom/file-changes-action@v1.2.4
        with:
          output: ' '

      - name: Get new and changed .sql files in /models to lint
        id: get_files_to_lint
        shell: bash -l {0}
        run: |
          # Set the command in the $() brackets as an output to use in later steps
          echo "::set-output name=lintees::$(
          # Issue where grep regular expressions don't work as expected on the
          # Github Actions shell, check dbt/models/ folder
          echo \
          $(echo ${{ steps.get_file_changes.outputs.files_modified }} |
          tr -s ' ' '\n' |
          grep -E '^models.*[.]sql$' |
          tr -s '\n' ' ') \
          $(echo ${{ steps.get_file_changes.outputs.files_added }} |
          tr -s ' ' '\n' |
          grep -E '^models.*[.]sql$' |
          tr -s '\n' ' ')
          )"

      - name: Lint dbt models
        if: steps.get_files_to_lint.outputs.lintees != ''
        shell: bash -l {0}
        run: |
          sqlfluff lint --format github-annotation --annotation-level failure --nofail ${{ steps.get_files_to_lint.outputs.lintees }} > annotations.json
          sed -i '/^\[/!d' annotations.json # see https://github.com/sqlfluff/sqlfluff/issues/2244

      - name: Annotate
        uses: yuzutech/annotations-action@v0.4.0
        with:
          repo-token: "${{ secrets.GITHUB_TOKEN }}"
          title: "SQLFluff Lint"
          input: "./annotations.json"