ci.yaml

# Very basic GitHub Action workflow, goes in ~/.github/workflows/deploy.yaml

name: CI

# Controls when the workflow will run
on:
  # Triggers the workflow on any branch or tag commit
  push:
    paths-ignore:
      - '*.tf'
      - 'transforms/*'
      - 'analytics/*'
      - 'README.md'
      - 'recover/*'
      - 'sage/*'
    branches:
      - "main"
      - "dev"

  # Allows you to run this workflow manually from the Actions tab
  workflow_dispatch:

jobs:

  schemachange_synapse_data_warehouse_dev:
    runs-on: ubuntu-latest
    if: github.ref == 'refs/heads/dev'
    environment: dev
    env:
      SNOWFLAKE_PASSWORD: ${{ secrets.SNOWSQL_PWD }}
      SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }}
      SNOWFLAKE_USER: ${{ secrets.SNOWSQL_USER }}
      # SNOWSQL_DATABASE: ${{ secrets.SNOWFLAKE_DATABASE }}
      # SNOWSQL_SCHEMA: ${{ secrets.SNOWFLAKE_SCHEMA }}
      SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE: ${{ vars.SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE }}
      SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }}
      SNOWFLAKE_SYNAPSE_STAGE_STORAGE_INTEGRATION: ${{ vars.SNOWFLAKE_SYNAPSE_STAGE_STORAGE_INTEGRATION }}
      SNOWFLAKE_SYNAPSE_STAGE_URL: ${{ vars.SNOWFLAKE_SYNAPSE_STAGE_URL }}

    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v4
        with:
          python-version: '3.10'

      - name: install-py-dependencies
        shell: bash
        run: |
          pip install schemachange

      - name: deploy synapse_data_warehouse
        shell: bash
        run: |
          schemachange \
            -f synapse_data_warehouse \
            -a $SNOWFLAKE_ACCOUNT \
            -u $SNOWFLAKE_USER \
            -r SYSADMIN \
            -w compute_xsmall \
            --config-folder synapse_data_warehouse

  snowsql_admin:
    runs-on: ubuntu-latest
    if: github.ref == 'refs/heads/main'

    env:
      SNOWSQL_PWD: ${{ secrets.SNOWSQL_PWD }}
      SNOWSQL_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }}
      SNOWSQL_USER: ${{ secrets.SNOWSQL_USER }}
      # SNOWSQL_DATABASE: ${{ secrets.SNOWFLAKE_DATABASE }}
      # SNOWSQL_SCHEMA: ${{ secrets.SNOWFLAKE_SCHEMA }}
      # SNOWSQL_ROLE: ${{ secrets.SNOWFLAKE_ROLE }}
      SNOWSQL_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }}
      saml2_x509_cert: ${{ secrets.SAML2_X509_CERT }}
      saml2_sso_url: ${{ secrets.SAML2_SSO_URL }}
      saml2_issuer: ${{ secrets.SAML2_ISSUER }}

    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
      # Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it
      - uses: actions/checkout@v4

      - name: Install SnowSQL
        run: |
          curl -O https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap/1.2/linux_x86_64/snowsql-1.2.9-linux_x86_64.bash
          SNOWSQL_DEST=~/bin SNOWSQL_LOGIN_SHELL=~/.profile bash snowsql-1.2.9-linux_x86_64.bash

      - name: Create users
        run: |
          ~/bin/snowsql -f admin/users.sql

      - name: Create roles
        run: |
          ~/bin/snowsql -f admin/roles.sql

      - name: Create databases
        run: |
          ~/bin/snowsql -f admin/databases.sql

      - name: Create integration
        run: |
          ~/bin/snowsql -f admin/integrations.sql --variable saml2_issuer=$saml2_issuer --variable saml2_sso_url=$saml2_sso_url --variable saml2_x509_cert=$saml2_x509_cert

      # - name: Governance
      #   run: |
      #     ~/bin/snowsql -f admin/policies.sql

      - name: Grant privileges
        run: |
          ~/bin/snowsql -f admin/grants.sql

  schemachange_admin:
    runs-on: ubuntu-latest
    if: github.ref == 'refs/heads/main'
    env:
      SNOWFLAKE_PASSWORD: ${{ secrets.SNOWSQL_PWD }}
      SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }}
      SNOWFLAKE_USER: ${{ secrets.SNOWSQL_USER }}
      # SNOWSQL_DATABASE: ${{ secrets.SNOWFLAKE_DATABASE }}
      # SNOWSQL_SCHEMA: ${{ secrets.SNOWFLAKE_SCHEMA }}
      SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }}

    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v4
        with:
          python-version: '3.10'

      - name: install-py-dependencies
        shell: bash
        run: |
          pip install schemachange

      - name: deploy warehouses
        shell: bash
        run: |
          schemachange \
            -f admin/warehouses \
            -a $SNOWFLAKE_ACCOUNT \
            -u $SNOWFLAKE_USER \
            -r SYSADMIN \
            -w compute_xsmall \
            -d METADATA

  schemachange_synapse_data_warehouse_prod:
    runs-on: ubuntu-latest
    if: github.ref == 'refs/heads/main'
    environment: prod
    env:
      SNOWFLAKE_PASSWORD: ${{ secrets.SNOWSQL_PWD }}
      SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }}
      SNOWFLAKE_USER: ${{ secrets.SNOWSQL_USER }}
      # SNOWSQL_DATABASE: ${{ secrets.SNOWFLAKE_DATABASE }}
      # SNOWSQL_SCHEMA: ${{ secrets.SNOWFLAKE_SCHEMA }}
      SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }}
      SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE: ${{ vars.SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE }}
      SNOWFLAKE_SYNAPSE_STAGE_STORAGE_INTEGRATION: ${{ vars.SNOWFLAKE_SYNAPSE_STAGE_STORAGE_INTEGRATION }}
      SNOWFLAKE_SYNAPSE_STAGE_URL: ${{ vars.SNOWFLAKE_SYNAPSE_STAGE_URL }}
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v4
        with:
          python-version: '3.10'

      - name: install-py-dependencies
        shell: bash
        run: |
          pip install schemachange

      - name: deploy synapse_data_warehouse
        shell: bash
        run: |
          schemachange \
            -f synapse_data_warehouse \
            -a $SNOWFLAKE_ACCOUNT \
            -u $SNOWFLAKE_USER \
            -r SYSADMIN \
            -w compute_xsmall \
            --config-folder synapse_data_warehouse