# Snowflake ETL
A collection of utils and etl jobs for feeding the Snowflake data warehouse.


## Install
```sh
# clone repo
git clone git@github.com:theorchard/snowflake-etl.git
cd snowflake-etl

# create a python virtual environment to isolate from global packages
# (you could also pick you interpreter here)
virtualenv env
source env/bin/activate

# install project requirements
pip install -r reqs.pip
pip install -r reqs-test.pip   # if you intend to run tests [CI]
pip install -r reqs-dev.pip     # if you want some nice dev tools

# install the project itself
pip install .            # using pip
python setup.py install  # using setuptools
python setup.py develop  # if you plan to develop and don't want to reinstall on certain modifications
```

## Run Tests
```sh
# unit tests
py.test tests/test_*

# func tests
py.test tests/func

# coverage [unit]
py.test --cov snowflake tests/test_* --cov-report html
py.test --cov snowflake tests/test_* --cov-report xml --junitxml=build/pyunit.xml  # jenkins style

# linter
flake8 snowflake tests
```

## Run SWF Processes (Manually)
```sh
# decider
garcon decider <flow module name>

# activity worker
garcon worker <flow module name>

# flow execution
garon exec <flow module name> -c '<json context>'
```

## Bin scripts
`https://github.com/theorchard/snowflake-etl/tree/master/bin_scripts` – here we keep Python scripts, which are executing by Jenkins jobs.
They are well documented by comments and docstrings.


## How to add a new source table to sync to Snowflake
All the new tables, which you want to sync via snapshot load strategy (full copy to Snowflake every time) should be added to the sql2sf workflow (rs2sf is in the process of being deprecated).

We suggest to synchronize MySQL tables via sql2sf workflow only if their size does not exceed 30 million rows.

For now there are the Jenkins jobs only for snapshot syncs, but in the near future we'll enable also the jobs for date_range and incremental syncs (Redshift only).

In order to add a table, you have to specify it in `snowflake-etl/conf/sql2sf_sources.yml`. An example: 
```yaml
db_hosts:
  reportsar:
    db_type: mysql
    schemas:
      art_relations:
        db_config:
          host: ENV[MYSQL_ART_RELATIONS_HOST]
          port: ENV[MYSQL_ART_RELATIONS_PORT]
          user: ENV[MYSQL_ART_RELATIONS_USER]
          password: ENV[MYSQL_ART_RELATIONS_PASSWORD]
        tables:
          release_dms_master_restriction:
            primary_key: restriction_id
            unload_in_chunks: True
            chunk_size: 20000000
            sanity_threshold: 0
            wrap_to_stripspecialchars: True
```
If you're adding a new db host, you'll need to add env variables to the `.env.shadow` file, and also inject them to the prod-snowflake-etl ElasticBeanstalk container, and also export them to the env of the Jenkins jobs which are executing sql2sf workflows.

db_type for MySQL db (doesn't matter if it's standalone, RDS, or Aurora) is mysql.
db_hosts are just the aliases, you can use whatever name you like. schemas should exist within this host (so you need to use `art_relations`, and you can't use random alias like `ar`).

* `unload_in_chunks`, `chunk_size` (this is not actual row count in a chunk, but a step for a range of values of a primary key), `primary_key` parameters are optional. They are required for big MySQL tables. NB! For MySQL tables sql2sf workflow performs full snapshot syncs, so you can't add really big tables (more than 10 mln rows). 
* `sanity_threshold` parameter should be set explicitly. You can't omit it. It's required for fast-changing tables which could change during the workflow run. So if you might expect that some rows could be added or deleted during the ETL process, you could set this option. If it's 0, then the sanity check (the number of rows in the source table vs. number of rows in the stg_ Snowflake table will be strict). 
* `wrap_to_stripspecialchars` parameter is optional. If it's set, all the text fields in the SELECT statement will be wrapped with stripSpecialChars db function, which basically deletes \n, \t and \r symbols.
* `transient` parameter is optional. Mainly in use for Redshift syncs. If True then stg_ table in Snowflake will be transient, which reduces bills (but also failsafe and time-travel). MySQL tables are small, so this isn't important for them.
* `date_col` parameter is optional, it's is only for Redshift tables which support date_range and incremental syncs.
* `sync_incremental_support` parameter parameter is optional, it's is only for Redshift tables which supports sync_incremental query_type. 

After you add the table, and merge your PR, you have to re-deploy [EB container](http://jenkins.theorchard.com:8080/job/prod-snowflake-deploy/).

[The Jenkins job for snapshot syncs](http://jenkins.theorchard.com:8080/job/prod-snowflake-sql2sf-snapshots/). You won't need to change it after you add a table to `sql2sf_sources.yml`, except of you want to sync tables to some different Snowflake db and schema. By default the table from our example above syncs into `art_relations.production.release_dms_master_restriction` in Snowflake.

**Please be aware that for now any schema changes done to the source table need to be applied to the Snowflake table, the workflows do not handle this by themselves**.

Also it would be great to add a table to [Monty](https://monty.theorchard.io/static/feed_ingestion_dashboard.html). Just add it into [this config](https://github.com/theorchard/feed-status-monitor/blob/master/feed_status/sql2sf_config.py), and re-deploy Monty.