# Shopify Global Schema POC — Project Notes

## Overview
A dbt project to consolidate Shopify store metadata from two sources into a single unified `STORES` model, written to Snowflake.

---

## Environment Setup

### Installation
```bash
pip3 install dbt-snowflake
```
- dbt version: 1.11.7
- dbt-snowflake version: 1.11.3

### Project location
```
~/shopify_global_schema/
```

### dbt profile location
```
~/.dbt/profiles.yml
```

### PATH setup
Added to `~/.zshrc`:
```bash
export PATH="$HOME/Library/Python/3.13/bin:$PATH"
```
This allows `dbt` to be run from any terminal without the full path.

---

## Snowflake Connection (`~/.dbt/profiles.yml`)

```yaml
shopify_global_schema:
  target: qa
  outputs:
    qa:
      type: snowflake
      account: SME-DELPHI
      user: bill.fleischer@sonymusic-pde.com
      authenticator: externalbrowser   # browser-based SSO via Microsoft
      role: FANSIFTER_ENGINEERING_PRIVACY
      database: QA_CRM_ECOMMERCE_DATA  # <-- UPDATE THIS when new DB is ready
      warehouse: EXPLORATION_WH
      schema: shopify_global_schema_dbt_test
      threads: 4
```

> **Note:** Authentication opens a browser window (Microsoft SSO). This is expected behavior.

---

## Current Blocker

The role `FANSIFTER_ENGINEERING_PRIVACY` does not have write privileges on `QA_CRM_ECOMMERCE_DATA`.

### Resolution plan
Create a **new dedicated database** for this POC work (name TBD), then:
1. Update `database:` in `~/.dbt/profiles.yml` to the new DB name
2. Run `dbt debug` to confirm connection
3. Run `dbt run --select unified_stores`

---

## Previewing the Model

Use `dbt show` to preview rows in the terminal:
```bash
dbt show --select unified_stores --limit 50
```

**dbt Power User** VS Code extension (`innoverio.vscode-dbt-power-user`) is installed and provides a "Preview Results" button directly in the editor — recommended for iterating on the model.

---

## Models

### `models/unified_stores.sql`
Consolidates two store master lists into a single view.

**Source 1:** `CRM_ECOMMERCE_DATA.CONSOLIDATION_DATA.ECOMMERCE_STORES`
- CRM-managed store list
- Columns of note: `STORE_ID`, `SNOWFLAKE_SCHEMA`, `ARTIST`, `LABEL`, `TERRITORY`, `DEDICATED_ARTIST_STORE`, etc.

**Source 2:** Built from Fansifter app reporting tables
- `FANSIFTER_APP_REPORTING.PROD.SHOPIFY_STORE_ASSOCIATION`
- `FANSIFTER_APP_REPORTING.PROD_SHOPIFY.SHOPIFY_SHOP`
- `FANSIFTER_APP_REPORTING.PROD.FS_ARTIST_ROSTER_DBT`
- `FANSIFTER_APP_REPORTING.PROD.SHOPIFY_COLLECTION_ARTIST`
- `ORCHARD_APP_REPORTING.DELPHI_PROD.VENDOR`

**Join key:** `ECOMMERCE_STORES.SNOWFLAKE_SCHEMA` = `fansifter_stores.SOURCE_SCHEMA`

**Join type:** `FULL OUTER JOIN` — captures stores in either or both sources

**Key output columns:**
| Column | Source | Notes |
|---|---|---|
| `SOURCE_SCHEMA` | Both (coalesced) | Unique key linking both sources |
| `STORE_ID` | Both (coalesced, cast to varchar) | ecommerce STORE_ID preferred; falls back to Shopify numeric ID |
| `STORE_ASSOCIATION_ID` | fansifter | |
| `STORE_NAME` | Both (coalesced) | |
| `SHOPIFY_DOMAIN` | fansifter / ecommerce | fansifter preferred; falls back to `STORE_URL_PREFIX` with `/products?/` stripped |
| `CUSTOM_DOMAIN` | fansifter | |
| `ARTIST` | Both (coalesced) | `'Multiple Artists'` when comma-separated list detected |
| `ALL_ARTISTS` | Both (coalesced) | Full comma-separated artist list; null when single artist |
| `VENDOR_ID` / `VENDOR_NAME` | fansifter | |
| `TERRITORY` / `LABEL` | ecommerce_stores | |
| `DEDICATED_ARTIST_STORE` | ecommerce_stores / derived | `false` if multiple artists; uses ecommerce value if present; inferred `true` for single-artist fansifter-only stores |
| `DATA_SOURCE` | Derived | Full database name(s): `CRM_ECOMMERCE_DATA`, `FANSIFTER_APP_REPORTING`, or both |

**Removed columns:** `PLATFORM`, `ARTIST_ID`, `TERRITORY_ID`, `LABEL_ID`, `TLA`, `MAILING_LIST_ID`, `FORM_ID`, `ACTIVE`, `STORE_URL_PREFIX`, `CONSOLIDATED_DATE_CREATED`, `CONSOLIDATED_DATE_MODIFIED`

**Other notes:**
- Vendor `7123` is excluded from fansifter source (reason TBD)
- `STORE_ID` cast to varchar to resolve type mismatch between ecommerce (string e.g. `S00032`) and Shopify (numeric)

---

## Session History

### 2026-03-23 (Session 1)
- Installed `dbt-snowflake` (dbt 1.11.7)
- Initialized dbt project at `~/shopify_global_schema/` using `--skip-profile-setup`
- Created `~/.dbt/profiles.yml` with SSO (externalbrowser) auth
- `dbt debug` passed — connection to Snowflake confirmed
- Created `models/unified_stores.sql` — full outer join of two store sources
- Attempted `dbt run --select unified_stores` → **blocked**: role `FANSIFTER_ENGINEERING_PRIVACY` has no write access to `QA_CRM_ECOMMERCE_DATA`
- Ran `unified_stores.sql` manually in Snowflake to validate the query logic
- Removed 8 columns from model based on review: `PLATFORM`, `ARTIST_ID`, `TERRITORY_ID`, `LABEL_ID`, `TLA`, `MAILING_LIST_ID`, `FORM_ID`, `ACTIVE`
- Decision made to create a **new dedicated database** for dbt output (name TBD)

### 2026-03-24 (Session 3)
- Renamed `unified_stores` model to `shopify_stores_master` (alias `BF_SHOPIFY_STORES_MASTER`)
- Successfully ran `dbt run --select shopify_stores_master` — 184 rows written to `DELPHI_EXPLORATION.EXPLORATION_SANDBOX.BF_SHOPIFY_STORES_MASTER`
  - The profile `database:` was updated to `DELPHI_EXPLORATION` at some point — write access confirmed
- Added two seed mapping tables to fill null `LABEL_NAME`, `VENDOR_ID`, and `CUSTOM_DOMAIN` for ecommerce-only stores:
  - `seeds/BF_territory_label_mapping.csv` — 38 TERRITORY/LABEL combinations pre-populated from Snowflake; `LABEL_NAME` and `VENDOR_ID` to be filled in manually
  - `seeds/BF_store_custom_domain.csv` — headers only; STORE_SCHEMA → CUSTOM_DOMAIN mappings to be filled in manually
- Added `seeds:` config block to `dbt_project.yml` targeting `DELPHI_EXPLORATION.EXPLORATION_SANDBOX`
- Updated `shopify_stores_master.sql` to reference seed CTEs and coalesce seed values for ecommerce-only stores
- `dbt seed` fails when run non-interactively (e.g. via Claude Code) — the `externalbrowser` SSO auth times out before the browser login can be completed. Must be run directly in a terminal where the browser window can be interacted with.

### 2026-03-23 (Session 2)
- Added `$HOME/Library/Python/3.13/bin` to `~/.zshrc` PATH so `dbt` works from any terminal
- Consolidated `STORE_ID` and `SHOPIFY_STORE_ID` into single `STORE_ID` column (coalesced, cast to varchar)
- Updated `SHOPIFY_DOMAIN` to fall back to `STORE_URL_PREFIX` (with `/products?/` stripped) for ecommerce-only stores; removed `STORE_URL_PREFIX` as a separate column
- Added `ALL_ARTISTS` column — stores full comma-separated artist list for multi-artist stores
- Updated `ARTIST` column — shows `'Multiple Artists'` when multiple artists detected
- Updated `DEDICATED_ARTIST_STORE` — `false` for multi-artist stores; inferred `true` for single-artist fansifter-only stores
- Updated `DATA_SOURCE` to show full Snowflake database name(s) instead of descriptive labels
- Removed `CONSOLIDATED_DATE_CREATED` and `CONSOLIDATED_DATE_MODIFIED` columns
- Installed **dbt Power User** VS Code extension for in-editor query preview
- Validated query output via Snowflake MCP connection

---

## Next Steps

1. [x] Decide on new database name and request creation — using `DELPHI_EXPLORATION`
2. [x] Run `dbt run --select shopify_stores_master` — 184 rows confirmed
3. [ ] Fill in `LABEL_NAME` and `VENDOR_ID` in `seeds/BF_territory_label_mapping.csv`
4. [ ] Fill in `STORE_SCHEMA` → `CUSTOM_DOMAIN` mappings in `seeds/BF_store_custom_domain.csv`
5. [ ] Run `dbt seed` interactively in terminal (browser SSO required)
6. [ ] Run `dbt run --select shopify_stores_master` to rebuild with seed data
7. [ ] Validate that ecommerce-only stores now have non-null `LABEL_NAME`, `VENDOR_ID`, `CUSTOM_DOMAIN`
8. [ ] Clarify why vendor `7123` is excluded