# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## What this repo does

A dbt project that consolidates Shopify store data from ~184 stores across two Snowflake source systems into a single unified schema (`SHOPIFY_STORES_GLOBAL`) for reporting. The two sources are:

- **CRM-managed stores** (~174 stores): raw Shopify data synced by Fivetran into per-store schemas under `CRM_ECOMMERCE_DATA` (e.g. `CRM_ECOMMERCE_DATA.SHOPIFY_UK_COLDPLAY.ORDER`)
- **Fansifter-managed stores** (~10 stores): raw Shopify data synced by Fivetran into per-store schemas under `PROD_SHOPIFY_INTEGRATIONS`

## dbt Commands

```bash
# Run all models
dbt run

# Run a specific model
dbt run --select shopify_stores_master

# Run by tag (see model layers below)
dbt run --select tag:staging
dbt run --select tag:mart
dbt run --select tag:mart_views

# Load seed CSVs
dbt seed --full-refresh

# Full refresh (rebuild incrementals from scratch)
dbt run --full-refresh

# Preview model output in terminal
dbt show --select <model_name> --limit 50

# SQL linting
sqlfluff lint models/
sqlfluff fix models/
```

**Auth note:** The `dev` target uses `externalbrowser` (Microsoft SSO) and will open a browser window. Any `dbt` command run non-interactively (e.g. from Claude Code) will time out waiting for the browser login — run these commands directly in a terminal.

## Profiles

`profiles.yml` is checked into the repo (unlike the usual `~/.dbt/profiles.yml` pattern). Four targets:

| Target | Database | Auth |
|--------|----------|------|
| `dev` | `DELPHI_EXPLORATION` / `EXPLORATION_SANDBOX` | `externalbrowser` SSO |
| `dev-local` | env-driven (`SNOWFLAKE_DATABASE` / `SNOWFLAKE_SCHEMA`) | RSA private key path (env vars) |
| `qa` | `SHOPIFY_STORES_GLOBAL` / `QA_SHOPIFY_STORE_SCHEMA` | RSA private key (env vars) |
| `prod` | `SHOPIFY_STORES_GLOBAL` / `SHOPIFY_GLOBAL_SCHEMA` | RSA private key (env vars) |

The `dev` target reads `SNOWFLAKE_USER` and `SNOWFLAKE_WAREHOUSE` from env vars but falls back to defaults. The `dev-local` target uses RSA key-pair auth instead of browser SSO (so it can run non-interactively against a personal sandbox); it reads `SNOWFLAKE_PRIVATE_KEY_PATH`, `SNOWFLAKE_KEY_PASSPHRASE`, `SNOWFLAKE_USER`, `SNOWFLAKE_ROLE`, `SNOWFLAKE_DATABASE`, `SNOWFLAKE_SCHEMA`, and optionally `SNOWFLAKE_AUTHENTICATOR`. The `qa`/`prod` targets require `SNOWFLAKE_PRIVATE_KEY`, `SNOWFLAKE_KEY_PASSPHRASE`, `SNOWFLAKE_USER`, `SNOWFLAKE_ROLE`, `SNOWFLAKE_DATABASE`, `SNOWFLAKE_SCHEMA`, and `SNOWFLAKE_WAREHOUSE`.

## Model Layers

### `models/core/` — `tag:staging` (materialised as tables)

Foundation layer. Not to be confused with a traditional `staging` layer — these are actually intermediate/spine models:

- **`shopify_stores_master`** (alias `SHOPIFY_STORES_MASTER`): The spine for the entire project. One row per store; full-outer-joins CRM and Fansifter store registries; resolves vendor, artist, brand, custom domain, store name overrides. Every mart model joins here on `STORE_SCHEMA`.
- **`int_paid_order_dates`**: First/last paid order date per store. Kept separate to avoid a circular dependency with `shopify_stores_master` (which the order mart model references).
- **`int_last_refresh`**: Latest Fivetran sync timestamp per store from `ABANDONED_CHECKOUT._FIVETRAN_SYNCED`.

### `models/mart/` — `tag:mart` (materialised as tables)

One model per Shopify entity. Each model calls `generate_shopify_table_union('<TABLE_NAME>')` to union that table across all ~184 store schemas, then joins to `shopify_stores_master` for store metadata enrichment. Models include: `order` (alias `ORDERS`), `order_line`, `customer`, `customer_address`, `product`, `product_variant`, `fulfillment`, `transaction`, etc.

### `models/mart_views/` — `tag:mart_views` (materialised as views, schema suffix `_views`)

Thin views over mart tables. Each view calls `generate_date_view('<mart_model>')` which introspects the mart table's columns and adds a `_DATE` cast for every `*_at` timestamp column. Named `v_<entity>` (e.g. `v_order`, `v_customer`).

## Key Macros

- **`generate_shopify_table_union(table_name)`** — The core macro. Queries `INFORMATION_SCHEMA.COLUMNS` at run time to discover all store schemas for both source databases, builds a master column list (CRM columns first, Fansifter-only columns appended), handles type mismatches (`TEXT`↔`TIMESTAMP_TZ`), and emits a `UNION ALL` across all schemas. Missing columns are `NULL`-cast to the canonical type. The table name is double-quoted in the generated SQL (important for reserved words like `ORDER`).

- **`generate_date_view(source_model)`** — Introspects a mart table's columns and generates a `SELECT` that adds `<col>_DATE` (a `::DATE` cast) for every column ending in `_at`.

- **`generate_crm_shop_union()`** — Unions `SHOP.ID` across all CRM store schemas; used in `shopify_stores_master` to retrieve canonical Shopify store IDs.

- **`apply_masking_policies()`** — Runs on-run-end for `qa` and `prod` targets only. Re-applies Snowflake column-level PII masking policies (defined in `theorchard/database#27598`) after each run because dbt's drop-and-recreate cycle removes column bindings. Uses `FORCE` so it is idempotent.

## Seeds

Three seed CSVs in `seeds/` are used by `shopify_stores_master`:

- **`shopify_store_custom_domain.csv`** — Maps `STORE_SCHEMA` to a known custom domain URL for stores not resolved via Fansifter metadata.
- **`shopify_store_name_overrides.csv`** — Manual store name corrections.
- **`shopify_vendor_overrides.csv`** — Manual vendor ID/name assignments; COR schemas (`SHOPIFY_COR_*`) are also matched in-SQL to vendor 11111 (Ceremony of Roses).

## Deployment

CI (`Jenkinsfile`) builds a Docker image on merge to `master` and pushes it to ECR with the commit SHA as tag.

The scheduler (`Jenkinsfile.scheduler`) runs the Docker image hourly (`:30` past each hour, `America/New_York`) on both QA and prod. It passes environment-specific Snowflake credentials via AWS Secrets Manager. Prod is pinned to a specific image SHA (`ECR_IMAGE_TAG_PROD`) — update this line to promote a build to prod.

The Docker entrypoint (`scripts/dbt-run.sh`) always runs `dbt seed --full-refresh`, then either:
- Runs `python scripts/daily-run.py` (which runs staging → mart → mart_views in order) when `DBT_DAILY_RUN=true`
- Runs an ad-hoc `dbt run` with optional `--full-refresh` and `--select` when triggered manually via Jenkins parameters

Slack alerts go to `#shopify_global_schema_dbt_alerts` on scheduler failure/recovery.

## Important Exclusions

- Schema `SHOPIFY_UK_MICHAEL_JACKSON` is excluded from CRM queries — that store is also synced via Fansifter as `SHOPIFY_UK_MJ_STORE` and would double-count.
- Schema `SHOPIFY_IT_SME_ITALY_STORE` is excluded from order date aggregations.
- Fansifter vendor `7123` is excluded (internal/test account).
- Test schemas matching `%TEST_LABEL%` are excluded from Fansifter source queries.