# streamlit-sme-merch-app
Streamlit app for showing SME Merch related dashboards application_family: fansifter



* Project setup

After cloning this repository run following command to
create virtual environment and install required packages:

```poetry install```


* Prerequisites:

Make sure you have Snowflake CLI installed:
https://docs.snowflake.com/en/developer-guide/snowflake-cli/installation/installation

Make sure your that for Snowflake CLI you have connection profile called sme_merch that uses
FANSIFTER_ENGINEERING_PRIVACY profile to deploy/manage development version of Streamlit app related resources. 
This role is also used by local Streamlit app to run queries or to run ETL scripts.

```
[connections.sme_merch]
account = "delphi.us-east-1"
user = "xxxxxx@sonymusic-pde.com"
role = "FANSIFTER_ENGINEERING_PRIVACY"
database = "FANSIFTER_APP_REPORTING"
schema = "PROD_STREAMLIT_SME_MERCH"
warehouse = "PROD_ETL_WH"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "/Users/xxxxx/.ssh/snowflake/rsa_key.p8"
```

Makefile is used to run related tasks. To see all available tasks:
type ```make help```

Most of the commands take advantage of Snowflake CLI.
Here are Snow CLI commands for Streamlit:
https://docs.snowflake.com/en/developer-guide/snowflake-cli/command-reference/streamlit-commands/overview

* Deploying Streamlit app

This project takes advantage of Jenkinsfile so each PR will result
in Jenkins pipeline run that deploys Streamlit.

If you want to run Streamlit in your own Snowflake schema then run following code to apply changes:
```make deploy_streamlit_app```

* Running streamlit app locally

To run Streamlit app locally, type:
```make run_streamlit_locally```

Local deployment takes advantage of connections.json file that
contains profile used by Snowpark to connect to Snowflake.
Inside the Snowflake's Streamlit app, the connection is automatically
assigned but not in local Streamlit deployment.


* User permissions (managed by Terraform)

Users belonging to Snowflake role called SME_MERCH_STREAMLIT_ACCESS 
should have access to this Streamlit app.

This role is granted database role ```FANSIFTER_APP_REPORTING.DB_PROD_STREAMLIT_SME_MERCH_SCHEMA_SME_MERCH_STREAMLIT```.

And these roles are results of ```streamlit_database_role_SME_MERCH```
module in Terraform.

This means that there is currently no need to manage
user permissions directly from Streamlit app's share button.

# Scheduling of ETL
If you execute command:
```make create_snowflake_tasks```
then it will trigger snowflake_tasks.py file that generates flow of Snowflake tasks in database. Each flow is responsible
for calling stored procedure that contains ETL logic. Flows are scheduled to run multiple times a day to meet
end user requirements.


* Ad-hoc ETL and Snowflake procedures


To generate latest data locally, you can run following command:
```make run_etl_locally```
This will trigger main_pipeline.py script that contains references to all ETL scripts.
  * How to refresh procedures? (Current workaround)

Each referenced file also contains Snowpark code to create Snowflake procedures. If there is a need to
refresh procedure definition in Snowflake then each ETL file must be run separately and before that uncommenting the Snowpark session & stored procedure creation part.