[SNOW-90] Introduce CI job to test schemachange updates against a clone
#78
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --- | |
| name: Test Changes with Cloned DB | |
| on: | |
| pull_request: | |
| types: [ labeled, synchronize, closed ] | |
| push: | |
| permissions: | |
| contents: read | |
| jobs: | |
| create_clone_and_run_schemachange: | |
| runs-on: ubuntu-latest | |
| if: contains(github.event.pull_request.labels.*.name, 'create_clone_and_run_schemachange') && github.event.pull_request.state == 'open' | |
| environment: dev | |
| env: | |
| SNOWFLAKE_PASSWORD: ${{ secrets.SNOWSQL_PWD }} | |
| SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }} | |
| SNOWFLAKE_USER: ${{ secrets.SNOWSQL_USER }} | |
| SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }} | |
| SNOWFLAKE_CLONE_ROLE: DATA_ENGINEER | |
| SNOWFLAKE_SCHEMACHANGE_ROLE: SYSADMIN | |
| SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE_ORIG: ${{ 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 }} | |
| CLONE_NAME: "${{ vars.SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE }}_${{ github.head_ref }}" | |
| STACK: ${{ vars.STACK }} | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - uses: actions/setup-python@v4 | |
| with: | |
| python-version: '3.10' | |
| - name: Install python libraries | |
| shell: bash | |
| run: | | |
| pip install schemachange==3.6.1 | |
| pip install numpy==1.26.4 | |
| pip install pandas==1.5.3 | |
| - name: Configure Snowflake connections | |
| run: | | |
| # Config file for DPE_ENGINEER | |
| config_file_dpe=$(mktemp) | |
| echo 'default_connection_name = "dpe"' >> $config_file_dpe | |
| echo '[connections.dpe]' >> $config_file_dpe | |
| echo "account = \"${SNOWFLAKE_ACCOUNT}\"" >> $config_file_dpe | |
| echo "user = \"${SNOWFLAKE_USER}\"" >> $config_file_dpe | |
| echo "role = \"${SNOWFLAKE_CLONE_ROLE}\"" >> $config_file_dpe | |
| echo "password = \"${SNOWFLAKE_PASSWORD}\"" >> $config_file_dpe | |
| echo "warehouse = \"${SNOWFLAKE_WAREHOUSE}\"" >> $config_file_dpe | |
| echo 'authenticator = "SNOWFLAKE"' >> $config_file_dpe | |
| # Config file for SYSADMIN | |
| config_file_sysadmin=$(mktemp) | |
| echo 'default_connection_name = "sysadmin"' >> $config_file_sysadmin | |
| echo '[connections.sysadmin]' >> $config_file_sysadmin | |
| echo "account = \"${SNOWFLAKE_ACCOUNT}\"" >> $config_file_sysadmin | |
| echo "user = \"${SNOWFLAKE_USER}\"" >> $config_file_sysadmin | |
| echo "role = \"${SNOWFLAKE_SCHEMACHANGE_ROLE}\"" >> $config_file_sysadmin | |
| echo "password = \"${SNOWFLAKE_PASSWORD}\"" >> $config_file_sysadmin | |
| echo "warehouse = \"${SNOWFLAKE_WAREHOUSE}\"" >> $config_file_sysadmin | |
| echo 'authenticator = "SNOWFLAKE"' >> $config_file_sysadmin | |
| # Write config paths to environment | |
| echo "SNOWFLAKE_CONFIG_PATH_DPE=$config_file_dpe" >> $GITHUB_ENV | |
| echo "SNOWFLAKE_CONFIG_PATH_SYSADMIN=$config_file_sysadmin" >> $GITHUB_ENV | |
| - name: Install Snowflake CLI with DPE_ENGINEER config | |
| uses: Snowflake-Labs/[email protected] | |
| with: | |
| default-config-file-path: ${{ env.SNOWFLAKE_CONFIG_PATH_DPE }} | |
| - name: Verify Snowflake CLI installation and connections | |
| run: | | |
| snow --version | |
| snow connection test -c sysadmin | |
| snow connection test -c dpe | |
| - name: Sanitize Clone Name | |
| run: | | |
| CLONE_NAME_SANITIZED="${CLONE_NAME//[^a-zA-Z0-9_]/_}" | |
| echo "Clone name has been updated! The clone name will be: ${CLONE_NAME_SANITIZED}" | |
| echo "SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE=${CLONE_NAME_SANITIZED}" >> $GITHUB_ENV | |
| - name: Zero-copy clone the database | |
| shell: bash | |
| run: | | |
| snow sql -q "CREATE OR REPLACE DATABASE $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE CLONE $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE_ORIG;" | |
| - name: Grant permissions to DPE_ENGINEER on cloned database | |
| shell: bash | |
| run: | | |
| snow connection set-default sysadmin | |
| snow sql -q "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE TO ROLE DPE_ENGINEER;" | |
| snow sql -q "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE_RAW TO ROLE DPE_ENGINEER;" | |
| - name: Run schemachange on the clone as DPE_ENGINEER | |
| shell: bash | |
| run: | | |
| schemachange \ | |
| -f synapse_data_warehouse \ | |
| -a $SNOWFLAKE_ACCOUNT \ | |
| -u $SNOWFLAKE_USER \ | |
| -r $SNOWFLAKE_CLONE_ROLE \ | |
| -w $SNOWFLAKE_WAREHOUSE \ | |
| --config-folder synapse_data_warehouse | |
| drop_clone: | |
| runs-on: ubuntu-latest | |
| if: github.event.pull_request.merged == true || github.event.action == 'closed' | |
| environment: dev | |
| env: | |
| SNOWFLAKE_PASSWORD: ${{ secrets.SNOWSQL_PWD }} | |
| SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }} | |
| SNOWFLAKE_USER: ${{ secrets.SNOWSQL_USER }} | |
| SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }} | |
| SNOWFLAKE_CLONE_ROLE: DATA_ENGINEER | |
| CLONE_NAME: "${{ vars.SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE }}_${{ github.head_ref }}" | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - uses: actions/setup-python@v4 | |
| with: | |
| python-version: '3.10' | |
| - name: Configure Snowflake connection | |
| run: | | |
| # Create temporary files for config.toml and our private key | |
| config_file=$(mktemp) | |
| # Write to config.toml file | |
| echo 'default_connection_name = "dpe"' >> $config_file | |
| echo '[connections.dpe]' >> $config_file | |
| echo "account = \"${SNOWFLAKE_ACCOUNT}\"" >> $config_file | |
| echo "user = \"${SNOWFLAKE_USER}\"" >> $config_file | |
| echo "role = \"${SNOWFLAKE_CLONE_ROLE}\"" >> $config_file | |
| echo "password = \"${SNOWFLAKE_PASSWORD}\"" >> $config_file | |
| echo "warehouse = \"${SNOWFLAKE_WAREHOUSE}\"" >> $config_file | |
| echo 'authenticator = "SNOWFLAKE"' >> $config_file | |
| # Write config.toml path to global environment | |
| echo "SNOWFLAKE_CONFIG_PATH=$config_file" >> $GITHUB_ENV | |
| - name: Install Snowflake CLI | |
| uses: Snowflake-Labs/[email protected] | |
| with: | |
| default-config-file-path: ${{ env.SNOWFLAKE_CONFIG_PATH }} | |
| - name: Verify Snowflake CLI installation and connection | |
| run: | | |
| snow --version | |
| snow connection test | |
| - name: Sanitize Clone Name | |
| run: | | |
| CLONE_NAME_SANITIZED="${CLONE_NAME//[^a-zA-Z0-9_]/_}" | |
| echo "SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE=${CLONE_NAME_SANITIZED}" >> $GITHUB_ENV | |
| echo "Clone name has been updated! The clone name will be: ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}" | |
| echo $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE | |
| - name: Drop the clone | |
| shell: bash | |
| run: | | |
| snow sql -r $SNOWFLAKE_CLONE_ROLE -q "DROP DATABASE IF EXISTS $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE;" |