# spotrates-cea

Daily spot exchange rates ingestion (USD base, 168 currencies) from the [Frankfurter API](https://frankfurter.dev) into Snowflake and ReportingDB.

## Data flow

1. **Backfill** — Fetches historical rates (default: 5 years) from the Frankfurter API and loads them into `sme_analytics.fi.dim_spot_exchange_rates` in Snowflake via a staging table + MERGE.

2. **Daily** — Fetches rates since the last loaded date and upserts into Snowflake. Idempotent — safe to re-run.

3. **Sync** — Pushes new rows from Snowflake to `prod_shared.dim_spot_exchange_rates` in Redshift using a high-water-mark pattern (`max(rate_date)` as the cutoff).

## API

[Frankfurter](https://frankfurter.dev) — free, no API key, no rate caps. Sources rates from the ECB and 53 other central banks. Covers 168 currencies with history back to 1999. Rates are published on business days only (no weekends/holidays).

## Target tables

| Alias | Table |
|-------|-------|
| Snowflake table | `sme_analytics.fi.dim_spot_exchange_rates` |
| Snowflake view | `sme_analytics.shared.dim_spot_exchange_rates` |
| Redshift table | `prod_shared.dim_spot_exchange_rates` |

## Schema

| Column | Type | Description |
|--------|------|-------------|
| `rate_date` | `date` | Date the rate applies to (business days only) |
| `base_currency` | `varchar(3)` | Base currency code (always USD) |
| `quote_currency` | `varchar(3)` | Quote currency code (e.g. EUR, SEK, NOK) |
| `rate` | `float` | Exchange rate: 1 USD = rate quote_currency |
| `loaded_at` | `timestamp` | Row insertion timestamp |

## Usage

```bash
# Backfill 5 years of history
uv run python -m src backfill

# Backfill from a specific date
uv run python -m src backfill --start-date 2020-01-01

# Fetch latest rates into Snowflake
uv run python -m src daily

# Sync new rows to Redshift
uv run python -m src sync

# Daily + sync
uv run python -m src all
```

### Options

```
--start-date, -s  Start date for backfill (YYYY-MM-DD, default: 5 years ago)
--log-file, -l    Log file name (default: spotrates-cea.log, stored in LOGDIR)
```

## Project structure

```
spotrates-cea/
  pyproject.toml          # Dependencies (uv)
  src/
    __main__.py           # CLI entry point (backfill | daily | sync | all)
    config/               # Tables, paths, warehouses, API config
    db/                   # Singleton Snowflake/Redshift connections
      queries.py          # Jinja2 SQL template loader
    services/
      api.py              # Frankfurter API client (NDJSON, 90-day chunking)
      ingest.py           # Backfill and daily ingest into Snowflake
      sync.py             # Snowflake-to-Redshift high-water-mark sync
  sql/
    snw-upsert.sql        # MERGE into main table from staging
    rdb-get-cutoff.sql    # Get max(rate_date) from Redshift
    _create/              # DDL for table/view initialization
```

## Environment variables

| Variable | Purpose |
|----------|---------|
| `INFAMOUSDIR` | Base path override (defaults to project root) |
| `LOGDIR` | Directory for rotating log files |
| Snowflake/Redshift credentials | Managed by `djagitit` (`SnowflakeDB`, `ReportingDB`) |