Implementing CICD on Azure Devops

[venkata-nikhit-mulkala]

Hi,

I have used schemachange on azure devops pipeline, below are some of the questions

1. I am deploying scripts based on V1.#.# naming convention,

For Example: V1.1.1__Create_schema.sql

use database SANDBOX_CICD; --- Create schema in the database and access privileges CREATE SCHEMA IF NOT EXISTS "SANDBOX_CICD"."SANDBOX_SCHEMA_DEMO" WITH MANAGED ACCESS;

GRANT SELECT ON ALL tables IN SCHEMA "SANDBOX_CICD"."SANDBOX_SCHEMA_DEMO" TO role CICD_ROLE;

GRANT SELECT,INSERT ON future tables IN SCHEMA "SANDBOX_CICD"."SANDBOX_SCHEMA_DEMO" TO role CICD_ROLE;

V1.1.2__New_schema.sql

use database SANDBOX_CICD;

--- Create schema in the database and access privileges CREATE SCHEMA IF NOT EXISTS "SANDBOX_CICD"."SANDBOX_SCHEMA_DEMO2" WITH MANAGED ACCESS;

GRANT SELECT ON ALL tables IN SCHEMA "SANDBOX_CICD"."SANDBOX_SCHEMA_DEMO2" TO role CICD_ROLE;

GRANT SELECT,INSERT ON future tables IN SCHEMA "SANDBOX_CICD"."SANDBOX_SCHEMA_DEMO2" TO role CICD_ROLE;

2. If we need to add any new schema/database or changes to existing databases/schema, do we always need to add them with new script V1.1.3__New_change.sql or we have add it to the existing scripts V1.1.1__Create_schema.sql, V1.1.2__New_schema.sql.

3. If not, every time we ran a build which runs below command, will it run all the files within $(PROJECT_FOLDER)/Migrations folder which were previous deployed to the console or only the new sql files(V1.1.3__New_change.sql) which are newly added to folder.

4. We have followed similar approach mentioned in below URL to implement the CICD pipeline on Azure Devops, in the step 6, we have yaml script, in which we have used command for the scripts to be deployed to console.

a. schemachange -f $(PROJECT_FOLDER)/migrations -a $(SF_ACCOUNT) -u $(SF_USERNAME) -r $(SF_ROLE) -w $(SF_WAREHOUSE) -d $(SF_DATABASE) -c $(SF_DATABASE).SCHEMACHANGE.CHANGE_HISTORY --create-change-history-table

b. Using above command, we will be able to deploy to only one database, is there any way we will be able to deploy to multiple databases, I have created multiple variable groups, jobs. I want to check whether we have any other approach.

DEVOPS CICD pipeline implementation

https://quickstarts.snowflake.com/guide/devops_dcm_schemachange_azure_devops/index.html?index=..%2F..index#2

[sfc-gh-jhansen]

Hey there @VenkataNikhit-Integrity, thanks for reaching out. Please check out the Change Scripts section of the schemachange documentation for a description of each of the three different change script types (versioned, repeatable, always). That should help with the first 3 questions.

As for question 4, you can deploy to any database you want as long as you write the scripts accordingly. The parameters like role, warehouse and database can all be overriden in your scripts by using 3 part names or the USE command.

Hope that helps!