[SNOW-90] Introduce CI job to test schemachange updates against a clone
#103
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 DATA_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 | |
| echo '[connections.sysadmin]' >> $config_file_dpe | |
| echo "account = \"${SNOWFLAKE_ACCOUNT}\"" >> $config_file_dpe | |
| echo "user = \"${SNOWFLAKE_USER}\"" >> $config_file_dpe | |
| echo "role = \"${SNOWFLAKE_SCHEMACHANGE_ROLE}\"" >> $config_file_dpe | |
| echo "password = \"${SNOWFLAKE_PASSWORD}\"" >> $config_file_dpe | |
| echo "warehouse = \"${SNOWFLAKE_WAREHOUSE}\"" >> $config_file_dpe | |
| echo 'authenticator = "SNOWFLAKE"' >> $config_file_dpe | |
| # Write config paths to environment | |
| echo "SNOWFLAKE_CONFIG_PATH_DPE=$config_file_dpe" >> $GITHUB_ENV | |
| - name: Install Snowflake CLI with DATA_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 DATA_ENGINEER on cloned database | |
| shell: bash | |
| run: | | |
| snow connection set-default sysadmin | |
| # Transfer ownership of: database | |
| snow sql -q "GRANT OWNERSHIP ON DATABASE ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE} TO ROLE DATA_ENGINEER REVOKE CURRENT GRANTS;" | |
| # Transfer ownership of: schemas | |
| snow sql -q "GRANT OWNERSHIP ON SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE_RAW TO ROLE DATA_ENGINEER REVOKE CURRENT GRANTS;" | |
| snow sql -q "GRANT OWNERSHIP ON SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE TO ROLE DATA_ENGINEER REVOKE CURRENT GRANTS;" | |
| # Transfer ownership of: tables | |
| snow sql -q "GRANT OWNERSHIP ON ALL TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE_RAW TO ROLE DATA_ENGINEER REVOKE CURRENT GRANTS;" | |
| snow sql -q "GRANT OWNERSHIP ON ALL TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE TO ROLE DATA_ENGINEER REVOKE CURRENT GRANTS;" | |
| # Transfer ownership of: dynamic tables | |
| snow sql -q "GRANT OWNERSHIP ON ALL DYNAMIC TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE TO ROLE DATA_ENGINEER REVOKE CURRENT GRANTS;" | |
| - name: Run schemachange on the clone as DATA_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;" |