# detect-overwritten-atmos-deliveries

Queries Snowflake for full-package Amazon (DMS 187) deliveries (metadata updates excluded, as they do not remove Atmos audio from Amazon) that may have overwritten Dolby Atmos spatial audio manual Amazon deliveries (DMS 1565), then checks each 187 delivery's XML metadata file in S3 for the presence of Atmos. Deliveries before the spatial-asset-ingester cutoff date (2026-04-17) are assumed to lack Atmos without inspecting XML. Outputs a CSV with results and a summary of which UPCs need correction.

## Usage

```bash
# Check yesterday's deliveries (default)
python analyze_atmos_deliveries.py

# Check a specific date
python analyze_atmos_deliveries.py --date 2026-06-15

# Check all historical deliveries (automatically restores Glacier-archived XMLs)
python analyze_atmos_deliveries.py --all-time

# Check a specific date and restore any Glacier-archived XMLs
python analyze_atmos_deliveries.py --date 2026-06-15 --retrieve-glacierized-xml
```

## Setup

```bash
pip install -r requirements.txt
cp .env.shadow .env  # fill in your credentials
```

AWS credentials must also be configured (e.g. via `awsume`).

## Snowflake authentication using key pair

Snowflake uses RSA key-pair authentication. To set up, follow the Snowflake docs:
https://docs.snowflake.com/en/user-guide/snowsql-start#using-key-pair-authentication

TL;DR:
```bash
mkdir -p ~/.config/snowflake && cd ~/.config/snowflake  # recommended storage location
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
```

Make sure to set a passphrase and then put this into the `SNOWFLAKE_KEY_PASSPHRASE` environment variable in your `.env` file.

Then add the public key to your user via Terraform:
https://github.com/theorchard/terraform-infra/blob/master/prod/snowflake/delphi/human_users/variables.tf

Note: if you put the key somewhere other than `~/.config/snowflake`, set the path in the `SNOWFLAKE_PRIVATE_KEY_PATH` variable.

## Output

Results are written to `outputs/atmos_check_<label>_<timestamp>.csv`, where `<label>` is either the delivery date (e.g. `2026-06-15`) or `all_time`.

| Column | Description |
|---|---|
| `UPC` | Product UPC |
| `LAST_1565_DELIVERY_DATE` | Date of the most recent manual Dolby Atmos delivery to DMS 1565 |
| `LAST_187_DELIVERY_DATE` | Date of the DMS 187 delivery that may have overwritten Atmos |
| `ATMOS_IN_DELIVERY` | `True` if ImmersiveEdition found in XML, `False` if not, `None` if XML unavailable |
| `STATUS` | `Atmos present`, `No Atmos — needs correction`, `No Atmos — needs correction (pre-cutoff)`, `Archived — cannot check`, `Archived — restore timed out`, or `XML not found` |
| `FILENAME` | S3 key of the delivery XML (e.g. `metadata/<md5>-0.xml`) |