# Update dim tables

Updates Snowflake dimension tables after a product transfer completes. For each release in the transfer job, this Lambda:

1. Fetches transfer job details (destination vendor, subaccount, and revenue cutoff date) from `ows-project-manager`.
2. Closes open rows in `FACTS.<schema>.DIM_RELEASE_HISTORY` by setting `END_DATE_INCLUSIVE` and `END_STATEMENT_PERIOD_ID`.
3. Inserts new rows reflecting the new vendor/subaccount ownership, starting the day after the revenue cutoff date.

## Event payload

### Input

| Field | Type | Description |
|-------|------|-------------|
| `job_id` | `int` | ID of the completed transfer job. |

Additional fields from the Step Function state are passed through unchanged.

### Output

Returns the input event merged with `update_dim_tables_result`:

| Field | Type | Description |
|-------|------|-------------|
| `update_dim_tables_result.job_id` | `int` | The processed transfer job ID. |
| `update_dim_tables_result.release_ids` | `list[int]` | Release IDs whose dimension rows were updated. |

## Environment variables

| Variable | Default | Description |
|----------|---------|-------------|
| `ENVIRONMENT` | `qa` | Deployment environment (`qa` or `prod`). |
| `SNOWFLAKE_USER_NAME` | — | Snowflake username. |
| `SNOWFLAKE_ACCOUNT` | `delphi` | Snowflake account identifier. |
| `SNOWFLAKE_ROLE` | — | Snowflake role to assume. |
| `SNOWFLAKE_SCHEMA` | `QA` | Schema prefix used in Snowflake queries (e.g. `QA` or `PROD`). |
| `SNOWFLAKE_PRIVATE_KEY_PATH` | — | Path to the Snowflake private key (`.p8`). For local dev, the `dev` service mounts `~/.ssh/snowflake/` and sets this automatically. In AWS, the key is read from Secrets Manager instead. |
| `SNOWFLAKE_KEY_PASSPHRASE` | — | Passphrase for the Snowflake private key. |
| `SENTRY_DSN` | — | Optional Sentry DSN for error tracking. |

## Setup

This Lambda uses M2M token authentication, which requires AWS access to retrieve credentials at runtime. See [AWS Access](https://www.notion.so/AWS-Access-f841b9dd815d4443a80e96a86c92cd2f) for setup instructions.

1. Copy `.env.shadow` to `.env` and fill in Snowflake credentials:
   ```sh
   cp .env.shadow .env
   ```

2. Place your Snowflake private key at `~/.ssh/snowflake/snowflake_key.p8`. The `dev` service mounts this directory automatically.

3. Run locally — exposes the Lambda invocation endpoint on port 9000:
   ```sh
   docker compose up dev
   ```

   Invoke the function in a second terminal:
   ```sh
   curl -XPOST "http://localhost:9000/2015-03-31/functions/function/invocations" -d '{"job_id": 123}'
   ```

## Development

Run linting and tests:
```sh
docker compose run --rm --build unit-lint
```

Format code:
```sh
docker compose run --rm --build format
```

Update the lock file (required after changing `pyproject.toml`):
```sh
docker compose run --rm --build update-lockfile
```