02.step_by_step_implementation.md

Step-by-Step Implementation

Setup Snowflake

Use the script below to create databases and a user for this demo.

-- Create Databases
CREATE DATABASE FLYWAY_DEMO COMMENT = 'Azure DevOps deployment test';
CREATE DATABASE FLYWAY_DEMO_DEV COMMENT = 'Azure DevOps deployment test';
CREATE DATABASE FLYWAY_DEMO_QA COMMENT = 'Azure DevOps deployment test';

-- Create a Deploy User
-- I'm granting sysadmin role to make it simple but you'd better to create a role with necessary permissions.
create user devopsuser password='<mypassword>' default_role = sysadmin;

Project Creation

Create your demo project - Snowflake_Flyway.

Step	Detail
Create a Project

Project Setup

There are some manual tasks that you need to do to set up the demo environment using the resources from kulmam92/snowflake_flyway.

Environment

You need to have an environment to be able to add the Approval step. Let's create the necessary Environments and Approvals.

Step	Detail
Create a PROD environment
Create Approval for the PROD environment
Create a QA environment	Follow instructions for the PROD environment.
Create Approval for the QA environment	Follow instructions for the PROD environment.
Create a DEV environment	Follow instructions for the DEV environment. Don't create Approval since the Push will trigger a deployment to the DEV environment without manual intervention.

Variable Group

When you have a common set of variables that will be used in multiple pipelines, you create a variable group once and reference them in many groups.

Step	Detail
Create Variable Group	Snowflake.Database.env of the kulmam92/snowflake_flyway has the list of variables needed for this demo

Set up a Template Repo

The repository with the same name as the project will be created by default. This will be our snowflake database project repository. I will create another repository to keep templates since it's a common module. Your team may have a repository for this purpose already.

Step	Detail
Create a Repo	Repository name: PipelineCommon
Add Pipeline Template to PipelineCommon	Add Pipeline Template to PipelineCommon Copy all the files in the templates folder of the kulmam92/snowflake_flyway to your repo and push changes to PipelineCommon. Your repo will look like this after that.

Create a Pipeline for the Database(Snowflake_flyway) Repo

Step	Detail
Create a Pipeline	Click "Create Pipeline"
Connect Tab	Choose "Azure Repos Git"
Select Tab	Choose "Snowflake_flyway"
Configure Tab	Choose "Starter pipeline"
Review Tab	Paste the contents of templates\YAMLpipelines\azure-pipelines.yml file of the kulmam92/snowflake_flyway to the editor. Add the template repository resource at the beginning of the YAML script. This will let you reference templates in the PipelineCommon Repo. resources: repositories: # template repository - repository: PipelineCommon type: git name: PipelineCommon Modify template reference. # For build Stage jobs: - template: YAMLpipelines/templates/snowflakeFlywayBuild.yml@PipelineCommon # For deploy Stages jobs: - template: YAMLpipelines/templates/snowflakeFlywayDeploy.yml@PipelineCommon
Save	Save

Add Demo Scripts to the Database(Snowflake_flyway) Repo

Step	Detail
Clone Snowflake_flyway Repo	I will use the VS code.
Copy migration scripts to your database Repo	Copy databases folder of the helper resource repository(kulmam92/snowflake_flyway) and paste to your database repo
Commit and Push changes
Your changes will be in the Repo

Check Pipeline(Snowflake_Flyway) Status

Your push to master will trigger the pipeline (Snowflake_Flyway) that you created.

Step	Detail
Pipeline Run History	Navigate to Pipelines → Pipelines → Snowflake_Flyway and click a Run You will see below if you follow everything correctly
Approve to deploy to QA	Deployment to QA is waiting for the approval. Click review and approve.
Approve to deploy to PROD	Do the same as QA for PROD deployment.

This is the end of the Step-by-Step implementation guide. Now you have a working demo pipeline.

<< Prev | Next >>