# crm-ecommerce-data-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 ```schemachange``` folder. [Snowchange 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/crm-ecommerce-data-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/CRM/job/crm-ecommerce-data-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/crm-ecommerce-data-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/CRM/job/crm-ecommerce-data-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/crm-ecommerce-data-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