# Snowflake
This repo contains everything related to Snowflake.

## Setting up Snowflake in local (Mac)

1. Go to this [link](https://docs.snowflake.com/en/developer-guide/snowflake-cli/installation/installation#label-snowcli-install-homebrew) and install with homebrew. (You can also try the other approaches to install.)

2. Follow this [link](https://docs.snowflake.com/en/user-guide/key-pair-auth) to generate an **unencrypted** public/private key pair. The public and private key files will be named rsa_key.pub and rsa_key.p8, respectively. Then log into Snowflake, create a new worksheet, and run the following query with your Snowflake username and the contents of your public key file:
    ```sql
    alter user YOUR_SNOWFLAKE_USERNAME_HERE set rsa_public_key='YOUR PUBLIC KEY HERE'
    ```

3. Add the following params in your local .env file under the /code/ folder. For SNOWFLAKE_ACCOUNT_USER, use your own Snowflake username, and for SNOWFLAKE_PRIVATE_KEY_FILE, use the full filepath where you stored your private key. Otherwise, use the values in this AWS secrets [link](https://us-east-2.console.aws.amazon.com/secretsmanager/secret?name=prod-snowflake-account&region=us-east-2) (under 590183763515).
    ```bash
    SNOWFLAKE_SECRET_NAME=<value>
    SNOWFLAKE_SECRET_REGION=<value>
    SNOWFLAKE_ACCOUNT=<value>
    SNOWFLAKE_ACCOUNT_USER=<value>
    SNOWFLAKE_ACCOUNT_PASSWORD=<value> -- this is deprecated
    SNOWFLAKE_ACCOUNT_ROLE=<value>
    SNOWFLAKE_PRIVATE_KEY_FILE=<the full path where your rsa_key.p8 file is stored on your local laptop>
    ```

## Deploying changes to staging
1. Make changes locally to the files in this repository.

2. **If you're updating an existing table** (i.e. adding a new column, renaming an existing column, etc.): in addition to modifying the table.sql file, do the following:

    - Under the table directory, on the same level as the table.sql file, create a *table_updates* subdirectory if there isn't one already.

    - Inside the table_updates directory, create a new SQL file following the convention *YYYY_MM_DD_<operation>_<row_name>.sql*. (For example: "2025_06_30_add_ios_temp_play_duration_sec_v2.sql")
    
    - In that SQL file, add an ```alter table``` statement corresponding to the update you're making to the table. (For example: ```alter table agg_play_info_hourly add column ios_temp_play_duration_sec float;```) This statement is what will actually modify the table on Snowflake when you deploy your changes. *Changes that are only made in table.sql will not be reflected on Snowflake.*

    - If you're updating an RDS table and there's a corresponding deleted_table.sql file, add an ```alter table``` statement for that table as well so it can also be updated.

3. Before deploying your changes, suspend any tasks that are affected by the changes you're making (i.e. if your task is part of a DAG, suspend the root task).

4. Push your changes to any test branch. The CICD pipeline will automatically deploy any SQL files you put in /tables/ or /utils/ to Snowflake staging.

5. Any tasks you deploy will be suspended. To reenable them and ensure they're running on their intended schedule, log into Snowflake and manually resume them. If a task belongs to a DAG, make sure you resume the parent task as well.

## Deploying changes to production
1. Before deploying your changes, suspend any tasks that are affected by the changes you're making (i.e. if your task is part of a DAG, suspend the root task).

2. Merge your test branch into main. The changes you made on that branch will get automatically deployed to production.

3. To reenable any tasks you deployed (or their parent tasks), either resume them on Snowflake or run the following command for each task:
    ```
    uv run resume_task.py --name <task name>
    ```

## Setting up an operational local CLI (optional)
Run the following command to setup a connection in the terminal using the credentials in this [link](https://us-east-2.console.aws.amazon.com/secretsmanager/secret?name=prod-snowflake-account&region=us-east-2).

Note before you run the command:

* The password will not work anymore, so just press enter and leave the password blank.
* The private key file is the full path where your rsa_key.p8 is stored in your local.
* For any information not listed in the link, you can just leave it blank.

```bash
snow connection add # after this a config.toml file should be created at <your path>/Application Support/snowflake/
``` 
Some useful commands:

```bash
snow connection list # check the connections

snow connection set-default <connection name> # set a connection as the default connection

snow connection test -c <connection name> # test if a connection work or not

snow sql -f <path of sql fiel> # execute the sql in a file

snow sql -q "<sql command>" # execute the raw sql

ALTER USER <username> SET MINS_TO_UNLOCK= 0; # unlock the account if you find an account is locked
```

**NOTE**: If you need to deploy a file manually to staging instead of using the CICD pipeline, run one of the following commands:
```bash
uv run deploy_to_staging.py --file <path of your file>
```
Or (if you're in the snowflake/code directory):
```bash
./run_deploy_to_staging.sh <path of your file>
```


## GitHub Integration with Snowflake

This guide provides instructions for integrating GitHub with Snowflake using secrets, API integrations, and executing SQL scripts.


### Prerequisites

1. A [GitHub personal access token](https://www.youtube.com/watch?v=ObDriYI_dME).
2. A Snowflake account with the required privileges.


### Steps to Generate GitHub Token

1. Log in to your GitHub account.
2. Navigate to **Settings** > **Developer Settings** > **Personal Access Tokens**.
3. Generate a new token with the required permissions for accessing repositories.
4. Save the token for use in the secret creation step.


### Create and Manage Secrets in Snowflake

1. Create a Secret

    Replace `<name>`, `<github account name>`, and `<github access token>` with your values.

    ```sql
    CREATE OR REPLACE SECRET <name>
        TYPE = PASSWORD
        USERNAME = '<github account name>'
        PASSWORD = '<github access token>';
    ```

2. Verify Secret Creation

    ```sql
    SHOW SECRETS;
    ```


### Create an API Integration

1. Define API Integration

    Replace `<name>`, `<github url>`, and `github_secret` with appropriate values.

    ```sql
    CREATE OR REPLACE API INTEGRATION <name>
        API_PROVIDER = GIT_HTTPS_API
        API_ALLOWED_PREFIXES = ('<github url>')
        ALLOWED_AUTHENTICATION_SECRETS = (github_secret)
        ENABLED = TRUE;
    ```

2. Verify API Integration

    ```sql
    SHOW INTEGRATIONS;
    ```

### Link GitHub Repository

1. Create a Git Repository in Snowflake

    Replace `<name>`, `<git api name>`, `<secret name>`, and `<github url>/<repo>` with your values.

    ```sql
    CREATE OR REPLACE GIT REPOSITORY <name>
        API_INTEGRATION = <git api name>
        GIT_CREDENTIALS = <secret name>
        ORIGIN = '<github url>/<repo>';
    ```

2. Verify Repository Creation

    ```sql
    SHOW GIT REPOSITORIES;
    ```


### Execute SQL Script from GitHub

Replace `<folder>` and `<file>` with the appropriate path to your SQL script on snowflake.

```sql
EXECUTE IMMEDIATE FROM @snowflake/branches/main/<folder>/<file>.sql;
```


## Notes

- Ensure proper permissions for creating secrets and API integrations in Snowflake.
- Replace placeholders (`<name>`, `<github account name>`, etc.) with actual values.
- Test your setup by executing a SQL script from the linked GitHub repository.


## AppsFlyer Tables Troubleshooting
### Background
- AppsFlyer's database contains views. In SUNO_PROD.PROD, we should have a corresponding table for each view.
- AppsFlyer's data sharing instance only contains data for the last 14 days. To preserve the history of our data, we must regularly dump their data into SUNO_PROD for historical analysis.
  - We have Snowflake daily tasks set up for this. They start with the prefix `AF_`.
- Sometimes AppsFlyer may add tables or columns to their database instance. This shouldn't happen too often, but when it does we must update our Snowflake tables and tasks to accommodate.

### When you receive a Slack alert indicating disparity between APPSFLYER and SUNO_PROD
1. Run `uv run scripts/appsflyer/diff_af_tables.py --apply-changes`. This performs a diff between the `APPSLFYER.SNOWFLAKE.*` and `SUNO_PROD.PROD.AF_*` table schemas and asks if you want to apply the changes to your repo and Snowflake to make them in sync.
2. If APPSFLYER has tables SUNO_PROD doesn't:
   1. Follow the instructions in the script to apply changes. Create a PR for any changes.
   2. Add the task to `daily_job_procedure.sql` to ensure #data-alerts is notified of any failures to this new task.
   3. Merge your PR.
   4. Deploy this task by running `uv run deploy_local_changes.py --file <task file>`
   5. Deploy changes to `daily_job_procedure.sql` with the same command format as above.
   6. Resume the updated tasks with `uv run resume_task.py --name <task name>`
3. If an APPSFLYER table has columns SUNO_PROD doesn't:
   1. Follow the instructions in the script to apply changes. Create a PR for any changes.
   2. Merge your PR.