# dbt-fansifter-shopify
dbt Core transformations for Shopify data ingested from multiple sources and unified in one model for the Fansifter app

### Overview

This project reads Shopify order data from multiple source databases, deduplicates and unifies it into a single incremental model consumed by the Fansifter app.

**Data sources:**
- `fansifter` — multi-schema Shopify integrations (`PROD_SHOPIFY_INTEGRATIONS` / `QA_SHOPIFY_INTEGRATIONS`)
- `crm` — CRM ecommerce data (`CRM_ECOMMERCE_DATA`)
- `d2c` — planned, not yet active

**Model layers:**

| Layer | Materialization | Description |
|---|---|---|
| `tmp` | view | Union all schemas from source database via `get_tables_across_schemas` |
| `staging` | ephemeral | Deduplicate by `id`, add `source_schema` |
| `int` | ephemeral | Union fansifter + crm staging, deduplicate across sources |
| `mart` | incremental (merge) | Final `shopify_order_dbt` table, unique key `id` |

### Using this repository

#### Prerequisites
- UV
- Python 3.12

#### Install dependencies

```bash
uv sync
```

#### Setup DBT

```bash
uv run dbt deps
```

#### Validate connection

```bash
uv run dbt debug
```

### Makefile

```bash
make env              # Install dev requirements
make packages         # Install dbt packages
make lint             # Lint all SQL files
make fmt              # Auto-fix SQL formatting
make lint_models path=models/shopify   # Lint specific folder
make fmt_models path=models/shopify    # Fix specific folder
make yml_models path=models/shopify/marts  # Generate models.yml from SQL files
make ci_lint_and_test # Run linters and tests in Docker
make dbt_test         # Run dbt tests in Docker
make clean            # Remove target, .venv, logs
```

### DBT commands

**Run models:**
```bash
uv run dbt run
uv run dbt run --select shopify_order_dbt
uv run dbt run --select models/shopify
```

**Run tests:**
```bash
uv run dbt test
uv run dbt test --select shopify_order_dbt
```

**Build (run + test):**
```bash
uv run dbt build
uv run dbt build --select shopify_order_dbt
```

**Full refresh incremental model:**
```bash
uv run dbt run --full-refresh --select shopify_order_dbt
```

**Graph operators:**
```bash
uv run dbt run --select "+shopify_order_dbt"   # model and all ancestors
uv run dbt run --select "shopify_order_dbt+"   # model and all descendants
```

**Troubleshoot selection:**
```bash
uv run dbt ls --select models/shopify
```

**Retry last failed command:**
```bash
uv run dbt retry
```

### Visualising DBT models

```bash
uv run dbt docs generate
uv run dbt docs serve   # opens http://localhost:8080
```