# delphi-ad-sources-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

```
LIST OF STORED PROCEDURES ->
1. EXP.UPDATE_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_SP
2. EXP.UPDATE_GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT_SP
3. EXP.UPDATE_GOOGLE_GENDER_PERFORMANCE_REPORT_SP
4. EXP.UPDATE_FACEBOOK_AGE_GENDER_DAILY_REPORT_SP
5. EXP.UPDATE_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS_SP
6. EXP.UPDATE_FACEBOOK_COUNTRY_DAILY_REPORT_SP
7. EXP.UPDATE_FACEBOOK_COUNTRY_DAILY_REPORT_ACTIONS_SP
8. EXP.UPDATE_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS_SP
9. EXP.UPDATE_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS_SP
10. EXP.UPDATE_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS_SP
11. EXP.UPDATE_FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS_SP
12. EXP.UPDATE_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS_SP
13. EXP.UPDATE_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_CONVERSIONS_SP
14. EXP.UPDATE_FACEBOOK_COUNTRY_DAILY_REPORT_CONVERSIONS_SP
15. EXP.UPDATE_GOOGLE_BUDGET_HISTORY_SP
16. EXP.UPDATE_FACEBOOK_AD_SET_HISTORY_SP
17. EXP.UPDATE_GOOGLE_GEO_PERFORMANCE_REPORT_SP
18. EXP.UPDATE_GOOGLE_ADGROUP_PERFORMANCE_REPORT_SP
19. SYS.UPDATE_FACEBOOK_ADS_CAMPAIGN_SP
20. SYS.UPDATE_FACEBOOK_ADS_AD_SET_SP
21. SYS.GOOGLE_UPDATE_CAMPAIGN_ARTIST_SP
22. SYS.FACEBOOK_UPDATE_CAMPAIGN_ARTIST_SP
23. SYS.UPDATE_GOOGLE_ADS_AD_SET_SP
24. SYS.UPDATE_GOOGLE_ADS_CAMPAIGN_SP
25. SYS.GOOGLE_CAMPAIGN_PROJECT_SP
26. SYS.ADS_DATA_EXPORT_SP
27. SYS.FACEBOOK_CAMPAIGN_PROJECT_SP
28. SYS.UPDATE_GOOGLE_ADS_ACCOUNT_SP
29. SYS.EXPORT_EXECUTION_SP(SRC_QUERY STRING, PATH STRING, FILE_NAME STRING)
```
```
LIST OF TASKS ->
1. EXP.UPDATE_FACEBOOK_AD_SET_HISTORY_TASK
2. EXP.UPDATE_GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT_TASK
3. EXP.UPDATE_GOOGLE_AGE_RANGE_PERFORMANCE_REPORT_TASK
4. EXP.UPDATE_GOOGLE_GENDER_PERFORMANCE_REPORT_TASK
5. EXP.UPDATE_FACEBOOK_AGE_GENDER_DAILY_REPORT_TASK
6. EXP.UPDATE_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS_TASK
7. EXP.UPDATE_FACEBOOK_COUNTRY_DAILY_REPORT_TASK
8. EXP.UPDATE_FACEBOOK_COUNTRY_DAILY_REPORT_ACTIONS_TASK
9. EXP.UPDATE_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_TASK
10. EXP.UPDATE_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS_TASK
11. EXP.UPDATE_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS_TASK
12. EXP.UPDATE_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS_TASK
13. EXP.UPDATE_FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS_TASK
14. EXP.UPDATE_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS_TASK
15. EXP.UPDATE_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_CONVERSIONS_TASK
16. EXP.UPDATE_FACEBOOK_COUNTRY_DAILY_REPORT_CONVERSIONS_TASK
17. EXP.UPDATE_GOOGLE_BUDGET_HISTORY_TASK
18. EXP.UPDATE_GOOGLE_GEO_PERFORMANCE_REPORT_TASK
19. EXP.UPDATE_GOOGLE_ADGROUP_PERFORMANCE_REPORT_TASK
20. SYS.UPDATE_FACEBOOK_ADS_CAMPAIGN_TASK
21. SYS.UPDATE_FACEBOOK_ADS_AD_SET_TASK
22. SYS.GOOGLE_UPDATE_CAMPAIGN_ARTIST_TASK
23. SYS.FACEBOOK_UPDATE_CAMPAIGN_ARTIST_TASK
24. SYS.UPDATE_GOOGLE_ADS_AD_SET_TASK
25. SYS.UPDATE_GOOGLE_ADS_CAMPAIGN_TASK
26. SYS.GOOGLE_CAMPAIGN_PROJECT_TASK
27. SYS.ADS_DATA_EXPORT
28. SYS.FACEBOOK_CAMPAIGN_PROJECT_TASK
29. SYS.UPDATE_GOOGLE_ADS_ACCOUNT


```