# Load data into Snowflake

## How to use

After running `generate-data`, these scripts can load the data into Snowflake.
Data for each run is combined into the master result set in `ORCHARD_APP_REPORTING_V2.PROD_HIVE_MODEL_REPORTING.HIVE_MODEL_RESPONSE_TRACKING`.

> ⚠️ Note that as new HIVE models are added, they will likely add to the models included in the response data. To accommodate this, the new columns will need to be added to the HIVE_MODEL_RESPONSE_TRACKING table. The [./sql/add_model_column.sql](./sql/add_model_column.sql) should be updated with the new columns, and the new column names should be added to the `COLUMNS_TO_ADD` variable in [./app.py](./app.py)

1. Copy `.env.example` to `.env` and fill in values.

2. Ensure you have an active AWS session.

3. Run `docker compose run --rm --build load-data-snowflake`

To run arbitrary sql, populate [./sql/append_results.sql](./sql/append_results.sql) with the SQL and update the Dockerfile command to target `run_arbitrary_sql.py`

## Oneoff investigation loads

Oneoff asset sets (run with `FILE_ID_MODE=filename` in generate-data) load into a dedicated
`HIVE_INVESTIGATION_*` table instead of appending to `HIVE_MODEL_RESPONSE_TRACKING`:

1. Set `RUN_ID` and `TABLE_NAME` (e.g. `HIVE_INVESTIGATION_2026_03_25_HYMN`) in `.env`.
2. Update the class columns in [./sql/create_oneoff_table.sql](./sql/create_oneoff_table.sql) to match the target model's output.
3. Run `docker compose run --rm --build load-data-snowflake python load_oneoff.py`

## Access

You will need to have access to the `assets` application family.