tuva_internal_dbt_v1.8.6.yml

name: tuva_internal_dbt_v1.8.6
on:
  workflow_dispatch:
  pull_request:
    branches:
      - main

concurrency:
  # Group by workflow and ref, cancel ongoing runs for the same PR/branch
  group: ${{ github.workflow }}-${{ github.ref }}
  cancel-in-progress: true

env:
  PYTHON_VERSION: '3.9'
  LINT_SCRIPT_PATH: ./lint_tuva_project.sh

#######  Secrets #######
#######  BigQuery
  TUVA_BIGQUERY_TOKEN: ${{ secrets.TUVA_BIGQUERY_TOKEN }}
  TUVA_BIGQUERY_PROJECT: ${{ secrets.TUVA_BIGQUERY_PROJECT }}
#######  Fabric
  DBT_FABRIC_SERVICE_PRINCIPAL_ID: ${{ secrets.DBT_FABRIC_SERVICE_PRINCIPAL_ID }}
  DBT_FABRIC_SERVICE_PRINCIPAL_SECRET: ${{ secrets.DBT_FABRIC_SERVICE_PRINCIPAL_SECRET }}
  DBT_FABRIC_TENANT_ID: ${{ secrets.DBT_FABRIC_TENANT_ID }}
  DBT_FABRIC_CI_DATABASE: ${{ secrets.DBT_FABRIC_CI_DATABASE }}
  DBT_FABRIC_CI_SCHEMA: ${{ secrets.DBT_FABRIC_CI_SCHEMA }}
#######  Redshift
  DBT_REDSHIFT_CI_HOST: ${{ secrets.DBT_REDSHIFT_CI_HOST }}
  DBT_REDSHIFT_CI_HOST_MIGRATION_TESTING: ${{ secrets.DBT_REDSHIFT_CI_HOST_MIGRATION_TESTING }}
  DBT_REDSHIFT_CI_USER: ${{ secrets.DBT_REDSHIFT_CI_USER }}
  DBT_REDSHIFT_CI_PASSWORD: ${{ secrets.DBT_REDSHIFT_CI_PASSWORD }}
  DBT_REDSHIFT_2_CI_PASSWORD: ${{ secrets.DBT_REDSHIFT_2_CI_PASSWORD }}
  DBT_REDSHIFT_CI_PORT: ${{ secrets.DBT_REDSHIFT_CI_PORT }}
  #######  Snowflake
  DBT_TUVA_SNOWFLAKE_ACCOUNT: ${{ secrets.DBT_TUVA_SNOWFLAKE_ACCOUNT }}
  DBT_TUVA_CI_DATABASE: ${{ secrets.DBT_TUVA_CI_DATABASE }}
  DBT_SNOWFLAKE_CI_PASSWORD: ${{ secrets.DBT_SNOWFLAKE_CI_PASSWORD }}
  DBT_SNOWFLAKE_CI_ROLE: ${{ secrets.DBT_SNOWFLAKE_CI_ROLE }}
  DBT_SNOWFLAKE_CI_SCHEMA: ${{ secrets.DBT_SNOWFLAKE_CI_SCHEMA }}
  DBT_SNOWFLAKE_CI_USER: ${{ secrets.DBT_SNOWFLAKE_CI_USER }}
  DBT_SNOWFLAKE_CI_WAREHOUSE: ${{ secrets.DBT_SNOWFLAKE_CI_WAREHOUSE }}

# Permissions needed for the comment action
permissions:
  contents: read
  pull-requests: write

jobs:
  # ============================================================================
  # Linting Job (Runs First)
  # ============================================================================
  sqlfluff-lint:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Set up Python ${{ env.PYTHON_VERSION }}
        uses: actions/setup-python@v4
        with:
          python-version: ${{ env.PYTHON_VERSION }}

      - name: Install Dependencies
        run: |
          python -m pip install --upgrade pip
          # Install dbt adapter needed for compile/lint (using Snowflake here)
          # Also install sqlfluff
          pip install dbt-core==1.8.6 dbt-snowflake sqlfluff==3.3.1 sqlfluff-templater-dbt

      # --- Setup dbt Profile for Linting ---
      - name: Create dbt profiles directory
        run: mkdir -p ~/.dbt

      - name: Create dbt profiles.yml for Snowflake (for linting context)
        run: |
          echo "default:
            outputs:
              dev:
                account: \"{{ env_var('DBT_TUVA_SNOWFLAKE_ACCOUNT') }}\"
                database: dev_ci_testing
                password: \"{{ env_var('DBT_SNOWFLAKE_CI_PASSWORD') }}\"
                role: \"{{ env_var('DBT_SNOWFLAKE_CI_ROLE') }}\"
                schema: \"{{ env_var('DBT_SNOWFLAKE_CI_SCHEMA') }}\"
                threads: 8
                type: snowflake
                user: \"{{ env_var('DBT_SNOWFLAKE_CI_USER') }}\"
                warehouse: \"{{ env_var('DBT_SNOWFLAKE_CI_WAREHOUSE') }}\"
            target: dev" > ~/.dbt/profiles.yml

      - name: Check if lint script exists
        run: |
          if [ ! -f "${{ env.LINT_SCRIPT_PATH }}" ]; then
            echo "Error: Lint script not found at ${{ env.LINT_SCRIPT_PATH }}"
            exit 1
          fi

      - name: Make lint script executable
        run: chmod +x ${{ env.LINT_SCRIPT_PATH }}

      - name: Run SQLFluff Lint Check Script
        id: lint_script
        run: bash ${{ env.LINT_SCRIPT_PATH }} --ci

      # --- Failure Handling ---
      # Intermediate step to read file content reliably on failure
      - name: Read Lint Output File on Failure
        id: read-lint-output
        # Run only if the lint_script step failed
        if: failure() && steps.lint_script.outcome == 'failure'
        run: |
          output_file="SQLFLUFF_LINTER_OUTPUT.TXT" # Matches script variable
          if [ -f "$output_file" ]; then
            # Read the file content, preserving newlines
            content=$(cat "$output_file")
          else
            content="Lint output file ($output_file) not found."
          fi
          content="${content//'%'/'%25'}"
          EOF_MARKER=$(uuidgen)
          echo "content<<${EOF_MARKER}" >> $GITHUB_OUTPUT
          echo "$content" >> $GITHUB_OUTPUT
          echo "${EOF_MARKER}" >> $GITHUB_OUTPUT
        shell: bash

      - name: Create Comment on PR Failure
        # Run only if lint_script failed and it's a pull request event
        if: failure() && steps.lint_script.outcome == 'failure' && github.event_name == 'pull_request'
        uses: peter-evans/create-or-update-comment@v4
        with:
          issue-number: ${{ github.event.pull_request.number }}
          token: ${{ secrets.GITHUB_TOKEN }}
          body: |
            ❌ **SQLFluff Linting Failed**

            Issues were found that require manual correction or are unfixable by `sqlfluff fix`.
            Please review the output below (or the full logs), fix the issues locally, and commit the changes.

            ```text
            ${{ steps.read-lint-output.outputs.content }}
            ```

  # ============================================================================
  # Build Jobs (Run only if Linting Passes)
  # ============================================================================

  #######  BigQuery
  bigquery_clinical_and_claims_enabled:
    needs: sqlfluff-lint
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v2



      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: ${{ env.PYTHON_VERSION }}

      - name: Install dbt-core and BigQuery adapter
        run: |
          python -m pip install --upgrade pip
          pip install dbt-core==1.8.6 dbt-bigquery

      - name: Create dbt profiles.yml for BigQuery
        run: |
          mkdir -p ./profiles/bigquery
          echo "default:
            outputs:
              dev:
                type: bigquery
                method: service-account
                project: dev-ci-testing
                keyfile: ./creds.json
                dataset: connector
                threads: 8
                timeout_seconds: 300
                priority: interactive
            target: dev" > ./profiles/bigquery/profiles.yml
        working-directory: ci_testing

      - name: Create BigQuery credentials file
        run: |
          echo "${{ secrets.TUVA_BIGQUERY_TOKEN }}" | base64 --decode > ./creds.json
        working-directory: ci_testing

      - name: dbt-deps
        run: dbt deps --profiles-dir ./profiles/bigquery
        working-directory: ci_testing

      - name: dbt-debug
        run: dbt debug --profiles-dir ./profiles/bigquery
        working-directory: ci_testing

      - name: dbt-build
        run: |
          dbt build --full-refresh --profiles-dir ./profiles/bigquery --vars '{"input_database": "dev-ci-testing","input_schema": "input_layer","clinical_enabled": true,"claims_enabled": true,"provider_attribution_enabled":true}'
        working-directory: ci_testing

      - name: Get the result
        if: ${{ always() }}
        run: echo "${{ steps.dbt-build.outputs.result }}"
        shell: bash


#######  Fabric
  fabric_clinical_and_claims_enabled:
    needs: sqlfluff-lint
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: ${{ env.PYTHON_VERSION }}

      - name: Install ODBC Driver 18 for SQL Server
        run: |
          curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
          curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
          sudo apt-get update
          sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18

      - name: Install dbt-core and Fabric adapter
        run: |
          python -m pip install --upgrade pip
          pip install dbt-core==1.8.6 dbt-fabric

      - name: dbt-deps
        run: dbt deps --profiles-dir ./profiles/fabric
        working-directory: ci_testing

      - name: dbt-debug
        run: dbt debug --profiles-dir ./profiles/fabric
        working-directory: ci_testing

      - name: dbt-build
        run: |
          dbt build --full-refresh --profiles-dir ./profiles/fabric --vars '{"input_database":"dev_ci_testing","input_schema":"input_layer","clinical_enabled":true,"claims_enabled":true,"provider_attribution_enabled":true}'
        working-directory: ci_testing

      - name: Get the result
        if: ${{ always() }}
        run: echo "${{ steps.dbt-build.outputs.result }}"
        shell: bash

#######  Redshift
  redshift_clinical_and_claims_enabled:
    needs: sqlfluff-lint
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: ${{ env.PYTHON_VERSION }}

      - name: Install dbt-core and Redshift adapter
        run: |
          python -m pip install --upgrade pip
          pip install dbt-core==1.8.6 dbt-redshift

      - name: dbt-deps
        run: dbt deps --profiles-dir ./profiles/redshift
        working-directory: ci_testing

      - name: dbt-debug
        run: dbt debug --profiles-dir ./profiles/redshift
        working-directory: ci_testing

      # Breaking up dbt build into seed then compile to try and speed up processing
      - name: dbt-seed
        run: |
          dbt seed --full-refresh --profiles-dir ./profiles/redshift --vars '{"input_database":"dev_ci_testing","input_schema":"input_layer","clinical_enabled":true,"claims_enabled":true}'
        working-directory: ci_testing

      - name: dbt-compile
        run: |
          dbt compile --profiles-dir ./profiles/redshift --vars '{"input_database":"dev_ci_testing","input_schema":"input_layer","clinical_enabled":true,"claims_enabled":true}'
        working-directory: ci_testing

      - name: Get the result
        if: ${{ always() }}
        run: echo "${{ steps.dbt-build.outputs.result }}"
        shell: bash

#######  Snowflake
  snowflake_clinical_and_claims_enabled:
    needs: sqlfluff-lint
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: ${{ env.PYTHON_VERSION }}

      - name: Install dbt-core and Snowflake adapter
        run: |
          python -m pip install --upgrade pip
          pip install dbt-core==1.8.6 dbt-snowflake

      - name: dbt-deps
        run: dbt deps --profiles-dir ./profiles/snowflake
        working-directory: ci_testing

      - name: dbt-debug
        run: dbt debug --profiles-dir ./profiles/snowflake
        working-directory: ci_testing

      - name: dbt-build
        run: |
          dbt build --full-refresh --profiles-dir ./profiles/snowflake --vars '{"input_database":"dev_ci_testing","input_schema":"input_layer","clinical_enabled":true,"claims_enabled":true}'
        working-directory: ci_testing

      - name: Get the result
        if: ${{ always() }}
        run: echo "${{ steps.dbt-build.outputs.result }}"
        shell: bash

  snowflake_claims_enabled:
    runs-on: ubuntu-latest
    needs: snowflake_clinical_and_claims_enabled

    steps:
      - uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: ${{ env.PYTHON_VERSION }}

      - name: Install dbt-core and Snowflake adapter
        run: |
          python -m pip install --upgrade pip
          pip install dbt-core==1.8.6 dbt-snowflake

      - name: dbt-deps
        run: dbt deps --profiles-dir ./profiles/snowflake
        working-directory: ci_testing

      - name: dbt-debug
        run: dbt debug --profiles-dir ./profiles/snowflake
        working-directory: ci_testing
      # Changed to compile to save processing time
      - name: dbt-compile
        run: |
          dbt compile --profiles-dir ./profiles/snowflake --vars '{"input_database":"dev_ci_testing","input_schema":"input_layer","claims_enabled":true}'
        working-directory: ci_testing

      - name: Get the result
        if: ${{ always() }}
        run: echo "${{ steps.dbt-build.outputs.result }}"
        shell: bash

  snowflake_claims_prov_attribution_enabled:
    runs-on: ubuntu-latest
    needs: snowflake_claims_enabled

    steps:
      - uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.9'

      - name: Install dbt-core and Snowflake adapter
        run: |
          python -m pip install --upgrade pip
          pip install dbt-core==1.8.6 dbt-snowflake

      - name: dbt-deps
        run: dbt deps --profiles-dir ./profiles/snowflake
        working-directory: ci_testing

      - name: dbt-debug
        run: dbt debug --profiles-dir ./profiles/snowflake
        working-directory: ci_testing

      # Changed to compile to save processing time
      - name: dbt-compile
        run: |
          dbt compile --profiles-dir ./profiles/snowflake --vars '{"input_database":"dev_ci_testing","input_schema":"input_layer","claims_enabled":true,"provider_attribution_enabled":true}'
        working-directory: ci_testing

      - name: Get the result
        if: ${{ always() }}
        run: echo "${{ steps.dbt-build.outputs.result }}"
        shell: bash

  snowflake_clinical_enabled:
    runs-on: ubuntu-latest
    needs: snowflake_claims_prov_attribution_enabled

    steps:
      - uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.9'

      - name: Install dbt-core and Snowflake adapter
        run: |
          python -m pip install --upgrade pip
          pip install dbt-core==1.8.6 dbt-snowflake

      - name: dbt-deps
        run: dbt deps --profiles-dir ./profiles/snowflake
        working-directory: ci_testing

      - name: dbt-debug
        run: dbt debug --profiles-dir ./profiles/snowflake
        working-directory: ci_testing

      # Changed to compile to save processing time
      - name: dbt-compile
        run: |
          dbt build --full-refresh --profiles-dir ./profiles/snowflake --vars '{"input_database":"dev_ci_testing","input_schema":"input_layer","clinical_enabled":true}'
        working-directory: ci_testing

      - name: Get the result
        if: ${{ always() }}
        run: echo "${{ steps.dbt-build.outputs.result }}"
        shell: bash

  snowflake_prov_attribution_enabled:
    runs-on: ubuntu-latest
    needs: snowflake_clinical_enabled

    steps:
      - uses: actions/checkout@v2

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: ${{ env.PYTHON_VERSION }}

      - name: Install dbt-core and Snowflake adapter
        run: |
          python -m pip install --upgrade pip
          pip install dbt-core==1.8.6 dbt-snowflake

      - name: dbt-deps
        run: dbt deps --profiles-dir ./profiles/snowflake
        working-directory: ci_testing

      - name: dbt-debug
        run: dbt debug --profiles-dir ./profiles/snowflake
        working-directory: ci_testing
      # Changed to compile to save processing time
      - name: dbt-compile
        run: |
          dbt compile --profiles-dir ./profiles/snowflake --vars '{"input_database":"dev_ci_testing","input_schema":"input_layer","provider_attribution_enabled":true}'
        working-directory: ci_testing

      - name: Get the result
        if: ${{ always() }}
        run: echo "${{ steps.dbt-build.outputs.result }}"
        shell: bash