# delphi-exploration-snowflake
Definition of objects in snowflake and Snowchange deployment scripts.
Snowchange is imperative tool - for example, if you want to change table, you need to add new deployemnt script with ALTER statement. All snowchange related stuff is located in ```snowchange``` folder. Latest versions of the tool changed name to schemachange instead of snowchange. [Schemachange documentation](https://github.com/Snowflake-Labs/schemachange)
To keep track of current state of database objects we use *definitions* folder. If you changed an object via snowchange script, you should find definition of this object in the *definitions* folder and update CREATE statement accordingly to reflect latest changes.

### Deployment scripts requirements
1) Deployment script in snowchange should contain *V* followed by SemVer followed by double underscore in their name.
Example:
```V1.0.1__views_for_schema```
Version should be bigger than the current maximum, otherwise changes won't be applied.
2) Deployment script **MUST NOT** contain any environment specific names in it. If you need environment specific name, please set it as #{variableName}. Afterwards go in [Octopus project](https://octopus.delphi.zone/app#/Spaces-1/projects/delphi-exploration-snowflake/variables) and set corresponding variable with values appropriate for each environment. During the deployment Octopus will substitute all variables defined in the project.
3) Deployment script should include explicite *use* statements for database and schema, e.g.:
```
use database #{database};
use schema #{database}.SYS;
```

### How to make changes and deploy them in Dev/QA environments:
1) Add change script in snowflake folder
2) Update existing object in definitions folder or create a new one.
3) Create PR to develop branch, merge it.
4) After the merge [Jenkins](https://jenkins.apollo.stream/job/delphi-repos/job/delphi-exploration-snowflake/job/develop/) will trigger a build in develop subproject, which will also create a release in [Channel: develop in Octopus](https://octopus.delphi.zone/app#/Spaces-1/projects/delphi-exploration-snowflake/).
5) Go to Octopus and deploy newly created release to Dev/QA environment to apply changes

### How to deploy changes in Staging/Production environments:
1) Create a PR from develop to master branch
2) After PR is merged, [Jenkins](https://jenkins.apollo.stream/job/delphi-repos/job/delphi-exploration-snowflake/job/master/) will trigger a build which will create a release in [Channel: release in Octopus](https://octopus.delphi.zone/app#/Spaces-1/projects/delphi-exploration-snowflake/).
3) Go to Octopus and deploy newly created release to Dev/QA/Staging environment to apply changes. To unlock deployment to production, you must first successfully deploy release to Staging environment