# shopify-global-schema

A dbt project that consolidates Shopify e-commerce data from ~184 artist stores into a single unified Snowflake schema (`SHOPIFY_STORES_GLOBAL`) for reporting.

## Background

Sony Music / The Orchard manages Shopify stores for artists across two separate backend systems:

- **CRM** (~174 stores): raw Shopify data synced via Fivetran into per-store schemas under `CRM_ECOMMERCE_DATA`
- **Fansifter** (~10 stores): raw Shopify data synced via Fivetran into per-store schemas under `PROD_SHOPIFY_INTEGRATIONS`

Each store gets its own Snowflake schema (e.g. `CRM_ECOMMERCE_DATA.SHOPIFY_UK_COLDPLAY`), meaning orders, customers, products, and other Shopify entities are spread across ~184 separate schemas. This project unions all of that into one consistent place so analysts can query across all stores without needing to know which backend system each store lives in.

## The core challenge

Each store's Shopify tables have slightly different columns depending on which Shopify features that store uses. The `generate_shopify_table_union` macro solves this by dynamically introspecting `INFORMATION_SCHEMA` at dbt run time to discover every store schema, reconcile column lists across all schemas, and emit a single `UNION ALL` query — filling missing columns with typed `NULL`s and resolving type mismatches (e.g. `TEXT` vs `TIMESTAMP_TZ`).

## Output

The project writes to `SHOPIFY_STORES_GLOBAL` in Snowflake and runs hourly via a Jenkins scheduler. The main tables are:

| Table | Description |
|-------|-------------|
| `SHOPIFY_STORES_MASTER` | One row per store; the spine for all other models |
| `ORDERS` | One row per Shopify order across all stores (~2.84M rows) |
| `CUSTOMER` | One row per customer account |
| `PRODUCT` / `PRODUCT_VARIANT` | Product catalogue |
| `FULFILLMENT` / `FULFILLMENT_ORDER` | Fulfilment data |
| `TRANSACTION` | Payment transactions |
| `ABANDONED_CHECKOUT` | Checkout abandonment events |
| + others | `ORDER_LINE`, `REFUND`, `INVENTORY_*`, `COLLECTION_*`, etc. |

Views (`v_*`) are also generated over each table, adding `_DATE` columns for every timestamp field.

## Setup

### Prerequisites

- **Python 3.12 or 3.13** (dbt core + dependencies require ≥3.12; Python 3.14+ is not yet supported)
- **Poetry** (dependency manager)
- **Snowflake access** with Microsoft SSO (dev target)

### Installation

If you don't have a supported Python (3.12 or 3.13), install one via pyenv:

```bash
pyenv install 3.12.10   # or a 3.13.x release
```

Install Poetry:

```bash
brew install poetry   # macOS
# or: pip install poetry
```

Clone the repo and install dependencies with Poetry:

```bash
cd shopify-global-schema
poetry env use python3.12           # Use Python 3.12 (or python3.13)
poetry install                      # Install dbt, sqlfluff, and dependencies
```

### Configuration

The `profiles.yml` is checked into the repo. Before running dbt, load environment variables and set your Snowflake user:

```bash
set -a && source .env && set +a    # Load .env vars (Snowflake credentials, etc.)
export SNOWFLAKE_USER=your.name@sonymusic-pde.com
```

Test the connection:

```bash
poetry run dbt debug --target dev   # Opens browser for Microsoft SSO
```

The `dev` target uses browser-based SSO — **a browser window will open when you run any dbt command**. Run commands directly in a terminal, not from an IDE extension or automated context.

## Usage

All dbt and sqlfluff commands must be prefixed with `poetry run` and executed with environment variables loaded:

```bash
set -a && source .env && set +a    # Load .env vars each session
```

### Common dbt Commands

```bash
# Run all models
poetry run dbt run --target dev

# Run a specific model
poetry run dbt run --select shopify_stores_master --target dev

# Run by layer (tag-based)
poetry run dbt run --select tag:staging --target dev    # core spine models (SHOPIFY_STORES_MASTER, etc.)
poetry run dbt run --select tag:mart --target dev        # mart tables (ORDERS, CUSTOMER, PRODUCT, etc.)
poetry run dbt run --select tag:mart_views --target dev  # convenience views (v_orders, v_customer, etc.)

# Full rebuild (from scratch, not incremental)
poetry run dbt run --full-refresh --target dev

# Reload seed CSVs (store name overrides, custom domains, vendor overrides)
poetry run dbt seed --full-refresh --target dev

# Preview a model output in the terminal
poetry run dbt show --select <model_name> --limit 50 --target dev

# Check for dbt-specific issues
poetry run dbt test --target dev
```

### SQL Linting

```bash
# Lint all SQL
poetry run sqlfluff lint models/

# Fix linting errors automatically
poetry run sqlfluff fix models/
```

### Snowflake Targets

The project has four targets defined in `profiles.yml`. The profile's default target is **`qa`** (set via `target: qa` at the profile root), so always pass `--target` explicitly for local work:
- **`dev`**: `EXPLORATION_SANDBOX` / `DELPHI_EXPLORATION` database, browser SSO
- **`dev-local`**: env-driven database/schema, RSA private-key auth (no browser) — use for non-interactive local runs against a personal sandbox
- **`qa`** (default): `QA_SHOPIFY_STORE_SCHEMA`, RSA key auth
- **`prod`**: `SHOPIFY_GLOBAL_SCHEMA`, RSA key auth

Specify `--target dev` for local development (or `--target dev-local` to skip the browser SSO).

## D2C Schema Naming Convention

Your automated Fivetran connector script creates D2C raw schemas using this pattern:

```
SHOPIFY[_OWNER][_COUNTRY]_SLUG
```

Examples:
- `SHOPIFY_COLUMBIA_US_DAFT_PUNK_STORE` (Columbia Records, US, Daft Punk)
- `SHOPIFY_SME_UK_COLDPLAY_OFFICIAL` (SME, UK, Coldplay Official)
- `SHOPIFY_UNIVERSAL_AU_ARTIST_X` (Universal, Australia, Artist X)

### Why This Pattern Matters

The dbt macros discover D2C raw schemas by querying Snowflake's `INFORMATION_SCHEMA` in the `SHOPIFY_STORES_GLOBAL` database. The challenge: **that same database contains dbt's output schemas** (`SHOPIFY_GLOBAL_SCHEMA`, `QA_SHOPIFY_STORE_SCHEMA`, etc.).

Without careful filtering, dbt would accidentally read its own output tables back as source data during the next run, causing:
- Duplicate store rows in `SHOPIFY_STORES_MASTER`
- Duplicate order rows in `ORDERS`
- Double-counting in all downstream analytics

### The Solution: Pattern Matching + Exclusions

The macros use this filtering logic:

```sql
where TABLE_SCHEMA like 'SHOPIFY_%'                    -- Allowlist D2C pattern
  and TABLE_SCHEMA not in (                            -- Denylist output schemas
    'SHOPIFY_GLOBAL_SCHEMA',
    'SHOPIFY_GLOBAL_SCHEMA_VIEWS',
    'QA_SHOPIFY_STORE_SCHEMA',
    'QA_SHOPIFY_STORE_SCHEMA_VIEWS',
    'INFORMATION_SCHEMA'
  )
```

**Why both?** The `LIKE` pattern identifies D2C schemas (which follow your naming convention), but the explicit exclusion list catches edge cases where output schema names also match the pattern.

If a new output schema is added in the future (e.g., `STAGING_SHOPIFY_STORE_SCHEMA`), update the `d2c_output_schema_exclusions` list in `dbt_project.yml` (used by all D2C `INFORMATION_SCHEMA` discovery queries).
This is a known brittleness; a future iteration may move to a dedicated D2C source database to eliminate this concern entirely.

## Application family

`fansifter`