As of 07/01/2017, The Orchard has two 'live' Data Warehouses. Redshift is our historic Data Warehouse and Snowflake is its newer, cheaper, more performant sibling. Unfortunately these family rivals will have to learn to live with one another for a period of time. This README will focus on Data Warehouse related databases in Snowflake, and not those created by the Data Analytics teams or microservice/monolith databases synced by [snowflake-etl](https://github.com/theorchard/snowflake-etl#how-to-add-a-new-source-table-to-sync-to-snowflake). Examples: **Data Warehouse Tables** Examples of tables whose db changes should be checked into this repo: * fact_analytics * fact_analytics_error * dim_releases * staging_raw_spotify_v2 **Data Analytics Tables / Miscroservice Tables** Changes to these tables should *NOT* be checked into this repo from the follow . locations * Snowflake tables in PROD.BI (Data Analytics) * Snowflake tables in ART_RELATIONS.PROD ([Microservice sync tables](https://github.com/theorchard/snowflake-etl/blob/master/snowflake_etl/conf/sql2sf_sources.yml#L28)) * Snowflake tables in PRICING.PROD ([Microservice sync tables](https://github.com/theorchard/snowflake-etl/blob/master/snowflake_etl/conf/sql2sf_sources.yml#L98)) ## Redshift Data Warehouse Resources ### Production * db04.redshift.theorchard.com Legacy Data Warehouse ETLs continue to run on Redshift Database 04 (db04.redshift.theorchard.com), in the DEV database using the PRODUCTION schema. ### Dev * dev-db02.redshift.theorchard.com dev-db02.redshift.theorchard.com continues to be the shared development Redshift database. Note that tables and data in these database are not automatically synced and should be checked against production before being worked on. ## Snowflake Data Warehouse Resources All user facing applications (e.g. VAPI) read/write from/to Snowflake. Eventually legacy ETLs will be ported from Redshift to Snowflake. Data from Redshift is synced to Snowflake either on a semi-hourly or daily basis by various Jenkins jobs running the [snowflake-etl](https://github.com/theorchard/snowflake-etl) application. These syncs come in two flavors: * Snapshot: Data for a table in Snowflake is replaced completely by that data for that table that is currently in Redshift * Incremental: Only new data that has been inserted into that table in Redshift and is not yet in Snowflake is loaded into Snowflake (note this sync does not handle DELETEs or UPDATEs) For a list of what tables are synced and what methodology they use for syncing, see the [Snowflake 2 Redshift Syncs/Refreshes List](https://docs.google.com/spreadsheets/d/18jClaNgGckaazb8bVy3OrPxb-kljLYdSADuV5DLLg34/edit#gid=0) ### Production * FACTS database / PROD schema The source of truth for our production Data Warehouse data. All applications consuming data from our Data Warehouse should read from here. * PROD database / PRODUCTION schema Legacy copy of our Data Warehouse used by the Data Analytics Team. This schema will be deprecated when the Data Analytics Team updates its ETLs and Looker Reports to use the FACTS db / PROD schema. ### Dev * FACTS database / DEV schema Dimension table schemas & data are synced nightly from Redshift. Fact table schemas (ie fact_analytics) are not touched and must be managed by the Dev team. ## DW DB Deploy Process All DW PRs are applied manually by @orchardit (Artur, Russell, & Myron) Dependent on the type of Data Warehouse changeset be running, some or all of the following list of `host` - `database` - `schema`: Redshift * db04.redshift.theorchard.com - DEV database - PRODUCTION schema Snowflake * [orchard snowflake account](https://orchard.snowflakecomputing.com/) - FACTS database - PROD schema * [orchard snowflake account](https://orchard.snowflakecomputing.com/) - PROD database - PRODUCTION schema Specifics are broken out in the [DDL Deploy Process](#DDL-Deploy-Process) & [DDL Deploy Process](#DML-Deploy-Process) sections below ### DDL Deploy Process 1) New Tables - Synced From Redshift to Snowflake via snapshot If the newly created table is synced from Redshift to Snowflake snapshot style (i.e. full replace), the sql only needs to be applied to Redshift: db04.redshift.theorchard.com - DEV database - PRODUCTION schema 2) New Tables - Synced From Redshift to Snowflake incrementally If the newly created table is synced from Redshift to Snowflake, the sql needs to be applied to both Redshift & Snowflake: * db04.redshift.theorchard.com - DEV database - PRODUCTION schema * [orchard snowflake account](https://orchard.snowflakecomputing.com/) - FACTS database - PROD schema * [orchard snowflake account](https://orchard.snowflakecomputing.com/) - PROD database - PRODUCTION schema 3) Altered Table All ALTER table sql statements need to be run on both Redshift & Snowflake. Additionally, the change needs to be applied to the Snowflake `stg_` table that is used by snowflake-etl for syncing from redshift. Example * Adding a column to `dim_release` would require the following sql to be executed For Redshift (on DEV.PRODUCTION) ``` ALTER TABLE dim_release ADD COLUMN COMPILATION VARCHAR(2); ``` For Snowflake (on both FACTS.PROD & PROD.PRODUCTION) ``` ALTER TABLE dim_release ADD COLUMN COMPILATION VARCHAR(2); ALTER TABLE stg_dim_release ADD COLUMN COMPILATION VARCHAR(2); ``` ### DML Deploy Process 1) Tables that are synced snapshot style from Redshift to Snowflake For these tables DML only needs to be applied to Redshift. The table's changed contents will then be synced to Snowflake the next time a snapshot refresh is run. 2) Tables that are synced incrementally from Redshift to Snowflake For these tables DML needs to be applied to both Redshift & Snowflake for fact_* & staging_raw tables that are in both Redshift & Snowflake. ### Gotchas * Snowflake DML changes can take much longer when running on Snowflake than when running on Redshift. * While a DML change is being applied, if the table is synced incrementally additional data may be synced from Redshift to Snowflake. If this is not desired (e.g. only old fact_analytics data should be modified), a data range should be applied to the query or the corresponding Jenkins job syncing the table should be disabled. * DML Inserts into a table that is synced incrementally will only be applied if they fall into a date range in the future for that table's `date_col` (ex. [processeddaytime for fact_analytics](https://github.com/theorchard/snowflake-etl/blob/master/snowflake_etl/conf/sources.yml#L214)). When inserting test data for such tables avoid inserting dates far into the future, as it will result in unpredicatable syncing for data between now and that date. ## Data Warehouse DB PR process All dml/ddl database changes should continue to be checked in to the appropriate database/dw/changelog directory. ### Verification Liquibase can be run against Redshift to verify SQL. For more details see [Using Liquibase with Redshift](#using-liquibase-with-redshift) SQL can be manually run against Snowflake. Liquibase support is scheduled to be available by end of fiscal 2017Q2. ### DB Changes Requiring Different RS/SF SQL While most SQL behaves similiarly against both Snowflake & Redshift, Snowflake can be pickier about type casting. If separate SQL is needed to run against Redshift / Snowflake, please check in separate files, prepending snowflake_ in the filename of the file that has the SQL to run against Snowflake, and redshift_ in the filename of the file that has SQL to run against Redshift. Both changeset files can be included in the same PR. ### Grants For DDL changes, include the appropriate GRANTS in your changelog. #### Redshift Grants ##### Pipeline Write Access For tables/views that a ETL pipeline needs to be able to actively write to, grant the appropriate access to the orcdpipeline user. ex. ``` GRANT RULE, INSERT, DELETE, SELECT, UPDATE, TRIGGER, REFERENCES ON dim_track_clean TO orcdpipeline; ``` ##### Read accesss For read access to a table/view, use the dw_queriers groups. ex. ``` GRANT SELECT ON dim_track_clean TO group dw_queriers; ``` #### Snowflake Grants Grants in Snowflake are done via Roles and through an [inherritance basis](https://docs.snowflake.net/manuals/user-guide/security-access-control.html#role-hierarchy-and-privilege-inheritance). Tables are given seperate 'base' Roles for Read/Write and Read permissions, and then these 'base' Roles are granted to the roles used by engineers and systems. These base roles should be part of sql in Snowflake database PRs and follow the below pattern: ``` GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON .. TO _DB__SCHEMA_READWRITE; GRANT SELECT ON FACTS.PROD.FOO TO _DB__SCHEMA_READ; ``` **DO** ``` CREATE TABLE FACTS.PROD.FOO(ROW_ID NUMBER); GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON FACTS.PROD.FOO TO FACTS_DB_PROD_SCHEMA_READWRITE; GRANT SELECT ON FACTS.PROD.FOO TO FACTS_DB_PROD_SCHEMA_READ; ``` **DON'T** ``` CREATE TABLE FACTS.PROD.FOO(ROW_ID NUMBER); GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON FACTS.PROD.FOO TO PROD_OWS_ANALYTICS_ETL; GRANT SELECT ON FACTS.PROD.FOO TO DEV_ENGINEERING; ``` ### Using Liquibase with Redshift #### Setup your `liquibase.properties` file Liquibase uses this file to read default option values. For a full list of values, see [Liquibase CLI docs](http://www.liquibase.org/documentation/command_line.html). To set-up: ``` cp liquibase.properties.shadow liquibase.properties ``` Look at it and be sure to update the relevant properties: * username * password * url * targetdbschemaname * databaseClass (redshift for prod, postgres for dev) ``` username=username password=password logLevel=debug url=jdbc:postgresql://CHANGEME:5432/dev targetdbschemaname=production ``` #### Sample changelog file Please see below for a working example. Note: * You do not need to specify dbms or context * In fact, if you specify context, in order for your changeset to run, you will have to specify the context when running liquibase ``` --liquibase formatted sql --changeset jchung:1 INSERT INTO dim_transactiontype (transactionTypeId, transactionTypeAbbr, transactionTypeDesc) VALUES (33, 'AC', 'Audio Claims'); --rollback DELETE FROM dim_transactiontype WHERE transactionTypeId=33; ``` Other notes on changelogs (specific to datawarehouse/Redshift): * Never include a ```vacuum``` statement in a changeset. This actually gets run every morning. #### Running Liquibase To run a changelog: ``` mvn initialize liquibase:update -DchangeLogFile= ``` To rollback a changelog: ``` mvn initialize liquibase:rollback -DchangeLogFile= -Dliquibase.rollbackCount= ``` To specify a different database properties file from ```liquibase.properties```, use the ```-DdbPropertiesFile```. For example: ``` mvn initialize liquibase:rollback -DchangeLogFile= -DdbPropertiesFile= ``` For other commands, check out [Liquibase CLI docs](http://www.liquibase.org/documentation/command_line.html). #### Gotchas Watch your Java Versions. Binaries in the Maven repositories were compiled with different versions of Java. The version of Java you have on your system should match. The driver being used is specified [here](https://github.com/theorchard/database/blob/master/dw/pom.xml#L54). If you have issues, check [here](http://stackoverflow.com/a/20658546) for more information on JDK compilation.