# D2C Integration: Architecture and Data Flow ## Overview The `shopify-global-schema` dbt project consolidates Shopify store data from approximately 690–710 stores across three distinct sources into a single unified reporting database (`SHOPIFY_STORES_GLOBAL`) for global business analytics. ### The Three Data Sources | Source | Database | # Stores | Fivetran Destination | |--------|----------|----------|----------------------| | **CRM-Managed** | `CRM_ECOMMERCE_DATA` | ~174 | Individual schemas per store | | **Fansifter-Managed** | `PROD_SHOPIFY_INTEGRATIONS` | ~10–25 | Individual schemas per store | | **D2C (New)** | `SHOPIFY_STORES_GLOBAL` | ~500 | Individual schemas per store | > **Note on counts:** all store counts below are approximate. The D2C count in particular fluctuates with how many onboarded stores have actually synced into `SHOPIFY_STORES_GLOBAL` and survived `STORE_ID` deduplication against CRM/Fansifter. Treat the numbers as order-of-magnitude estimates, not exact figures — query `SHOPIFY_STORES_MASTER` grouped by `SOURCE` for the live count. --- ## Data Flow: Beginning to End ### At the Beginning (After Fivetran Syncs, Before dbt Runs) **Database: `SHOPIFY_STORES_GLOBAL`** - **Raw D2C store schemas**: ~500–520 schemas created by your automated Fivetran script (e.g., `SHOPIFY_COLUMBIA_US_DAFT_PUNK_STORE`, `SHOPIFY_SME_UK_COLDPLAY_OFFICIAL`) - Each contains raw Shopify tables: `SHOP`, `ORDER`, `CUSTOMER`, `PRODUCT`, `FULFILLMENT`, `TRANSACTION`, etc. - Only stores without existing CRM or Fansifter connectors get D2C connectors - **Output schemas** (contain previous run's data, about to be replaced): - `SHOPIFY_GLOBAL_SCHEMA`: Prod consolidated tables - `SHOPIFY_GLOBAL_SCHEMA_VIEWS`: Prod convenience views - `QA_SHOPIFY_STORE_SCHEMA`: QA consolidated tables - `QA_SHOPIFY_STORE_SCHEMA_VIEWS`: QA convenience views **Queried remotely (not in this DB):** - `CRM_ECOMMERCE_DATA`: ~174 raw CRM store schemas - `PROD_SHOPIFY_INTEGRATIONS`: ~10–25 raw Fansifter schemas - `ORCHARD_APP_REPORTING_V2.D2C.ARTIST_STORES`: D2C store ownership/label mapping, synced from Google Sheets via Fivetran. **Note:** this metadata table lives in `ORCHARD_APP_REPORTING_V2`, *not* in `SHOPIFY_STORES_GLOBAL`. Only the raw per-store Shopify schemas live in `SHOPIFY_STORES_GLOBAL`. ### During dbt Run The dbt pipeline executes in three layers: #### Layer 1: Core / Staging (`models/core/`, tag:staging) **Macros discover all raw schemas:** - `generate_shopify_table_union()` queries `INFORMATION_SCHEMA` across all three source databases - `generate_fivetran_synced_union()` and custom queries discover store schema names **D2C schema discovery:** - Queries `SHOPIFY_STORES_GLOBAL.INFORMATION_SCHEMA.TABLES` for schemas matching `SHOPIFY_%` (your naming pattern) - Explicitly excludes output schemas: `SHOPIFY_GLOBAL_SCHEMA`, `QA_SHOPIFY_STORE_SCHEMA`, etc. **Core models build:** 1. **`SHOPIFY_INT_D2C_STORES`** - Unions raw `SHOP` table from all D2C store schemas in `SHOPIFY_STORES_GLOBAL` - Inner-joins to `ORCHARD_APP_REPORTING_V2.D2C.ARTIST_STORES` on Shopify domain (matching `MYSHOPIFY_DOMAIN` or custom domain) to resolve vendor, artist, label, and other metadata — only stores present in `ARTIST_STORES` are retained - **Deduplicates against CRM and Fansifter by `STORE_ID`** (Shopify's numeric store ID) - Anti-joins against `generate_crm_shop_union()` output - Anti-joins against `FANSIFTER_APP_REPORTING.PROD_SHOPIFY.SHOPIFY_SHOP` - Output: Only truly new D2C stores (~500–520, but typically fewer if some were already synced) 2. **`SHOPIFY_STORES_MASTER`** (the spine) - Combines all three sources: - CRM stores (~174 rows) from `CRM_ECOMMERCE_DATA.CONSOLIDATION_DATA.ECOMMERCE_STORES` - Fansifter stores (~10–25 rows) from `FANSIFTER_APP_REPORTING.PROD.SHOPIFY_STORE_ASSOCIATION` - D2C stores (~500–520 rows) from `SHOPIFY_INT_D2C_STORES` - Full-outer-joins CRM and Fansifter on schema name - UNIONs in D2C rows (with secondary `STORE_ID` deduplication as safety check) - Output: **~690–710 rows**, one row per store - Enriches with metadata: - Vendor and label names resolved from `ORCHARD_APP_REPORTING` - Custom domains from seed CSV - Store name overrides from seed CSV - Vendor overrides from seed CSV - Artist names and multi-artist JSON arrays 3. **`SHOPIFY_INT_PAID_ORDER_DATES`** - Aggregates first and last paid order date per store - Sources: unions `ORDER` tables from all three sources via `generate_shopify_table_union('ORDER')` - Keyed by `SOURCE_IDENTIFIER` (database.schema format) 4. **`SHOPIFY_INT_LAST_REFRESH`** - Aggregates latest Fivetran sync timestamp per store - Sources: unions Fivetran sync timestamps from `CUSTOMER`, `ORDER`, `ABANDONED_CHECKOUT` via `generate_fivetran_synced_union()` - Keyed by `SOURCE_IDENTIFIER` #### Layer 2: Mart (`models/mart/`, tag:mart) One table per Shopify entity. Each: - Calls `generate_shopify_table_union('')` to union that table across all ~690+ stores - Joins to `SHOPIFY_STORES_MASTER` to enrich with store metadata (vendor, artist, label, domain, etc.) - Applies PII masking rules - Materialised as a table **Tables produced:** - `ABANDONED_CHECKOUT`, `COLLECTION`, `COLLECTION_PRODUCT`, `CUSTOMER`, `CUSTOMER_ADDRESS` - `FULFILLMENT`, `FULFILLMENT_EVENT`, `FULFILLMENT_ORDER`, `FULFILLMENT_ORDER_LINE`, `FULFILLMENT_ORDER_LINE_ITEM` - `INVENTORY_ITEM`, `INVENTORY_LEVEL`, `INVENTORY_QUANTITY` - `ORDER_DISCOUNT_CODE`, `ORDER_LINE`, `ORDER_LINE_REFUND`, `ORDER_URL_TAG` - `ORDERS` (~12–14 million rows: existing 2.84M + new D2C rows) - `PRODUCT`, `PRODUCT_TAG`, `PRODUCT_VARIANT` - `REFUND`, `SHOP`, `TRANSACTION` #### Layer 3: Mart Views (`models/mart_views/`, tag:mart_views) Thin views over mart tables. The `generate_date_view()` macro: - Introspects mart table columns - Adds a `_DATE` cast for every column ending in `_at` - Materialised as views **Views produced:** - `v_abandoned_checkout`, `v_collection`, `v_collection_product`, `v_customer`, `v_customer_address` - `v_fulfillment`, `v_fulfillment_event`, `v_fulfillment_order`, `v_fulfillment_order_line`, `v_fulfillment_order_line_item` - `v_inventory_item`, `v_inventory_level`, `v_inventory_quantity` - `v_order_discount_code`, `v_order_line`, `v_order_line_refund`, `v_order_url_tag` - `v_orders`, `v_product`, `v_product_tag`, `v_product_variant` - `v_refund`, `v_shop`, `v_transaction` #### On-Run-End Hook **`apply_masking_policies()`** (QA and prod targets only): - Reapplies Snowflake column-level PII masking policies - Necessary because dbt's drop-and-recreate cycle destroys column bindings - Uses `FORCE` for idempotency ### At the End (After dbt Run Completes) **Database: `SHOPIFY_STORES_GLOBAL`** - **Raw D2C store schemas**: Unchanged (~500–520) - **Output schemas** (`SHOPIFY_GLOBAL_SCHEMA`): - **4 core tables**: - `SHOPIFY_INT_D2C_STORES`: ~500–520 D2C-sourced stores - `SHOPIFY_INT_PAID_ORDER_DATES`: First/last paid order per store across all sources - `SHOPIFY_INT_LAST_REFRESH`: Latest Fivetran sync timestamp per store - `SHOPIFY_STORES_MASTER`: **~690–710 rows**, one per store, fully enriched - **24 mart tables**: Consolidated data across all ~690–710 stores - `ORDERS` alone: ~12–14 million rows - **25+ convenience views** (`v_*`): Thin views with auto-casted date columns - **QA versions**: Identical structure in `QA_SHOPIFY_STORE_SCHEMA` and `QA_SHOPIFY_STORE_SCHEMA_VIEWS` - **Remote databases**: Unchanged (CRM and Fansifter databases are read-only to dbt) --- ## Deduplication and Safety ### Problem Approximately 700 unvetted stores need to be onboarded. However, ~174–199 of them may already be synced via CRM or Fansifter. If both connectors were active, data would be double-counted in reporting. ### Solution: Multi-Layer Deduplication **Layer 1: At Source (in `int_d2c_stores`)** - The D2C ingestion query joins D2C raw schemas (`SHOPIFY_STORES_GLOBAL`) to `ORCHARD_APP_REPORTING_V2.D2C.ARTIST_STORES` on Shopify domain - Anti-joins against CRM shop IDs: `STORE_ID not in (select ID from generate_crm_shop_union())` - Anti-joins against Fansifter shop IDs: `STORE_ID not in (select ID from FANSIFTER_APP_REPORTING.PROD_SHOPIFY.SHOPIFY_SHOP)` - Result: Only stores with no existing connector are included **Layer 2: In Master (in `shopify_stores_master`)** - `d2c_stores` CTE pulls from `int_d2c_stores` - Secondary anti-join on `STORE_ID`: - Filters out any D2C store already in CRM's `shopify_shop_ids` - Filters out any D2C store in Fansifter's `SHOPIFY_SHOP` - Result: Belt-and-suspenders protection against accidental double-counting ### Why Store ID, Not Schema Name? Schema naming conventions differ across databases: - CRM: `SHOPIFY_UK_COLDPLAY` - Fansifter: Custom names per Fansifter schema - D2C: `SHOPIFY_[OWNER]_[COUNTRY]_SLUG` (your pattern) A store's `STORE_ID` (Shopify's numeric identifier) is globally unique and the only reliable deduplication key. ### Enforcement: Build-Time Guards Three dbt tests fail the build if the deduplication invariants are ever violated. **These guards ship in companion PR #62 (branch `D2C-114_dedup_tests`), not in this PR** — they are described here so the dedup design is documented in one place: - **`assert_master_store_id_unique`** — no `STORE_ID` appears more than once in `SHOPIFY_STORES_MASTER`. - **`assert_master_store_single_source`** — no `STORE_ID` is claimed by more than one `SOURCE` (CRM / Fansifter / D2C). - **`assert_orders_single_source_path`** — no store's orders arrive via more than one `SOURCE_IDENTIFIER` in the `ORDERS` mart (catches dual-sync double-counting at the order grain even if the master spine looks clean). The same PR adds two analyses for informational coverage checks (not pass/fail): `d2c_coverage_check` (does every onboarded D2C store resolve to a master row?) and `d2c_order_counts` (per-store order row counts for D2C stores). --- ## PII Masking and Security ### Masked to NULL These fields are replaced with NULL in QA and prod: | Table | Fields | |-------|--------| | `ORDERS` | Customer name, phone, address, browser IP, user agent, order notes | | `FULFILLMENT_ORDER` | Address name, email, phone, address lines | | `TRANSACTION` | Payment credit card number, cardholder name, authorization | ### Hashed (Not NULL) - **Email** in `ORDERS`: Cryptographically hashed at the database level (visible, but not readable as plain text) ### No Masking - `PRODUCT`, `INVENTORY_*`, `SHOP`, `FULFILLMENT_EVENT`, `REFUND`, and all other tables contain no PII ### Application - Masking is enforced by Snowflake column-level policies - Re-applied after every dbt run via `apply_masking_policies()` hook - **Dev target**: No masking (developers see raw data) - **QA/prod targets**: Full masking ### Row-Level Security - Financial visibility is restricted by `VENDOR_ID` - Label managers can only see data for their own stores/vendors - Enforced in downstream tools (not in dbt, but in reporting layer) --- ## ARTIST_STORES Metadata in Final Output The `ARTIST_STORES` table (`ORCHARD_APP_REPORTING_V2.D2C.ARTIST_STORES`, synced from Google Sheets via Fivetran) provides critical mappings for each store. These columns flow through `int_d2c_stores` into `SHOPIFY_STORES_MASTER` and then into **all 24 mart tables** (via the master spine join): | ARTIST_STORES Column | Output Column | Final Location | Purpose | |---|---|---|---| | `STORE_ARTIST_NAME` | `ARTIST` | `SHOPIFY_STORES_MASTER` | Artist name (reporting dimension) | | `LABEL_VENDOR` | `VENDOR_NAME` | `SHOPIFY_STORES_MASTER` | Label/vendor name (human-readable) | | `MAPPED_LABEL_ID` | **`VENDOR_ID`** | **`SHOPIFY_STORES_MASTER` → ALL MART TABLES** | **Row-level security enforcement** | | `MAPPED_LABEL_NAME` | `LABEL_NAME` | `SHOPIFY_STORES_MASTER` | Label name (reporting dimension) | | `MAPPED_LABEL_BRAND` | `BRAND` | `SHOPIFY_STORES_MASTER` | Brand (SME/AWAL/The Orchard/etc.) | | `MAPPED_GLOBAL_PARTICIPANT_UUID` | `GLOBAL_PARTICIPANT_ID` | `SHOPIFY_STORES_MASTER` | Artist ID (cross-system tracking) | | `SELLING_COUNTRY` | `SELLING_COUNTRY` | `SHOPIFY_STORES_MASTER` → `CRM_TERRITORY` | Geographic reporting | | `REP_OWNER` | `REP_OWNER` | `SHOPIFY_STORES_MASTER` | Sales rep assignment | | `MERCH_COMPANY` | `MERCH_COMPANY` | `SHOPIFY_STORES_MASTER` | Merchandising/operations company | | `ACTIVE_` | `ACTIVE` | `SHOPIFY_STORES_MASTER` | Store active status | | `MULTIPLE_ARTIST_SITE_` | `MULTI_ARTIST_STORE` | `SHOPIFY_STORES_MASTER` | Multi-artist flag | ### Critical: VENDOR_ID **`VENDOR_ID` is the most important column.** It enforces row-level security in downstream reporting tools, ensuring label managers only see data for their own stores. Without accurate `VENDOR_ID` mapping, users will either see no data or the wrong stores. Since `ARTIST_STORES` is the source of truth for D2C store metadata, ensuring these columns are correctly populated in the upstream Google Sheet before Fivetran syncs is essential to the integrity of the entire pipeline. --- ## D2C Schema Naming Pattern Your Fivetran connector script creates schemas using this pattern: ``` SHOPIFY[_OWNER][_COUNTRY]_SLUG ``` Examples: - `SHOPIFY_COLUMBIA_US_DAFT_PUNK_STORE` - `SHOPIFY_SME_UK_COLDPLAY_OFFICIAL` - `SHOPIFY_UNIVERSAL_AU_ARTIST_X` This pattern is used by dbt to identify raw D2C schemas. The output-schema exclusion list is centralised in the `d2c_output_schema_exclusions` dbt variable (`dbt_project.yml`) and looped over at query time: ```sql where TABLE_SCHEMA like 'SHOPIFY_%' and TABLE_SCHEMA not in ( {% for schema in var('d2c_output_schema_exclusions') %}'{{ schema }}'{% if not loop.last %}, {% endif %}{% endfor %} ) ``` ```yaml # dbt_project.yml vars: d2c_output_schema_exclusions: - 'SHOPIFY_GLOBAL_SCHEMA' - 'SHOPIFY_GLOBAL_SCHEMA_VIEWS' - 'QA_SHOPIFY_STORE_SCHEMA' - 'QA_SHOPIFY_STORE_SCHEMA_VIEWS' - 'INFORMATION_SCHEMA' ``` --- ## Deployment The dbt project runs hourly via Jenkins: - **Docker image**: Built on merge to `master`, tagged with commit SHA - **Scheduler**: `Jenkinsfile.scheduler` runs the image every hour at `:30` past each hour (America/New_York timezone) - **Credentials**: Snowflake credentials passed via AWS Secrets Manager - **Environments**: QA and prod both run the same code, with environment-specific targets in `profiles.yml` ### Promotion to Prod - QA always runs the latest image - Prod is pinned to a specific image SHA via `ECR_IMAGE_TAG_PROD` - To promote a build, update that variable in `Jenkinsfile.scheduler` ### Alerts - Slack notifications go to `#shopify_global_schema_dbt_alerts` on scheduler failure/recovery --- ## Key Files | File | Purpose | |------|---------| | `models/core/int_d2c_stores.sql` | D2C store discovery and deduplication | | `models/core/shopify_stores_master.sql` | Master spine: ~690–710 stores across all sources | | `models/core/int_paid_order_dates.sql` | Aggregated first/last paid order date per store | | `models/core/int_last_refresh.sql` | Aggregated latest sync timestamp per store | | `models/mart/*.sql` | 24 consolidated entity tables (ORDERS, CUSTOMER, PRODUCT, etc.) | | `models/mart_views/*.sql` | 25+ convenience views with auto-casted dates | | `macros/generate_shopify_table_union.sql` | Core macro: discovers and unions raw tables from all three sources | | `macros/generate_fivetran_synced_union.sql` | Aggregates Fivetran sync timestamps across sources | | `macros/generate_crm_shop_union.sql` | Discovers CRM shop IDs for deduplication | | `profiles.yml` | Snowflake connection config (`dev`/`dev-local`/`qa`/`prod` targets) | | `seeds/shopify_store_custom_domain.csv` | Custom domain mappings | | `seeds/shopify_store_name_overrides.csv` | Manual store name corrections | | `seeds/shopify_vendor_overrides.csv` | Manual vendor ID/name assignments | | `tests/assert_master_store_id_unique.sql` | *(companion PR #62)* Double-count guard 1/3: no `STORE_ID` appears twice in the master spine | | `tests/assert_master_store_single_source.sql` | *(companion PR #62)* Double-count guard 2/3: no `STORE_ID` is claimed by more than one `SOURCE` | | `tests/assert_orders_single_source_path.sql` | *(companion PR #62)* Double-count guard 3/3: no store's orders arrive via more than one `SOURCE_IDENTIFIER` | | `analyses/d2c_coverage_check.sql` | *(companion PR #62)* Informational: does every onboarded D2C store resolve to a master row? | | `analyses/d2c_order_counts.sql` | *(companion PR #62)* Informational: per-store order row counts for D2C-sourced stores | --- ## Data Lineage DAG ``` ┌─────────────────────────────────────────────────────────────────────────────┐ │ EXTERNAL SOURCE SYSTEMS │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ CRM_ECOMMERCE_DATA PROD_SHOPIFY_INTEGRATIONS SHOPIFY_STORES_GLOBAL │ (174 store schemas) (10-25 store schemas) (raw D2C store schemas) │ ├─SHOP ├─SHOP ├─SHOP │ ├─ORDER ├─ORDER ├─ORDER │ ├─CUSTOMER ├─CUSTOMER ├─CUSTOMER │ ├─PRODUCT ├─PRODUCT ├─PRODUCT │ ├─FULFILLMENT ├─FULFILLMENT ├─FULFILLMENT │ └─... (24 tables total) └─... (24 tables total) └─... (24 tables total) │ (raw Shopify only — NOT │ CONSOLIDATION_DATA the ARTIST_STORES table) │ └─ECOMMERCE_STORES │ (CRM store registry) ORCHARD_APP_REPORTING_V2.D2C │ └─ARTIST_STORES (D2C ownership/label mapping, │ FANSIFTER_APP_REPORTING │ synced from Google Sheets) │ ├─SHOPIFY_STORE_ASSOCIATION ├─STORE_ARTIST_NAME │ ├─SHOPIFY_COLLECTION_ARTIST ├─LABEL_VENDOR │ ├─FS_ARTIST_ROSTER_DBT ├─MAPPED_LABEL_ID │ └─PROD_SHOPIFY (SHOPIFY_SHOP) ├─MAPPED_LABEL_NAME │ ├─MAPPED_LABEL_BRAND │ ORCHARD_APP_REPORTING ├─SELLING_COUNTRY │ ├─VENDOR ├─REP_OWNER │ ├─GLOBAL_PARTICIPANT ├─MERCH_COMPANY │ ├─COMPANY_BRAND_HAS_LABEL_VENDOR ├─MAPPED_GLOBAL_PARTICIPANT_UUID │ └─COMPANY_BRAND └─ACTIVE_, MULTIPLE_ARTIST_SITE_ │ │ SEED CSVs (Local) │ ├─shopify_store_custom_domain.csv │ ├─shopify_store_name_overrides.csv │ └─shopify_vendor_overrides.csv │ └─────────────────────────────────────────────────────────────────────────────┘ │ │ (dbt queries all sources) ▼ ┌─────────────────────────────────────────────────────────────────────────────┐ │ LAYER 1: CORE / STAGING MODELS │ │ (models/core/, tag:staging, full rebuild) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌─────────────────────────────────────────────────────────────────────┐ │ │ │ generate_crm_shop_union() │ │ │ │ └─ Discovers CRM SHOP.ID across all ~174 CRM schemas │ │ │ │ (used for deduplication) │ │ │ └─────────────────────────────────────────────────────────────────────┘ │ │ │ │ │ │ │ │ │ │ │ │ ┌───────────▼──────┐ ┌────────────▼──────┐ ┌──────────▼───────┐ │ │ │ SHOPIFY_INT_D2C │ │ ECOMMERCE_STORES │ │ FANSIFTER_STORES │ │ │ │ _STORES │ │ (CRM full-outer) │ │ (Fansifter f.o.j)│ │ │ │ │ │ │ │ │ │ │ │ Union D2C raw │ │ Combines ~174 CRM │ │ Combines ~10-25 │ │ │ │ SHOP from all │ │ stores with full │ │ Fansifter stores │ │ │ │ 500-520 schemas, │ │ outer join logic │ │ with artist JSON │ │ │ │ join ARTIST_ │ │ + vendor mapping │ │ + artist mapping │ │ │ │ STORES on domain,│ │ │ │ │ │ │ │ anti-join CRM & │ │ OUTPUT: │ │ OUTPUT: │ │ │ │ Fansifter by │ │ ~174 rows │ │ ~10-25 rows │ │ │ │ STORE_ID │ │ │ │ │ │ │ │ │ │ (intermediate) │ │ (intermediate) │ │ │ │ OUTPUT: │ │ │ │ │ │ │ │ ~500-520 D2C │ │ │ │ │ │ │ │ stores only │ │ │ │ │ │ │ │ (dedup'd) │ │ │ │ │ │ │ └────────┬─────────┘ └────────┬──────────┘ └──────────┬───────┘ │ │ │ │ │ │ │ │ D2C stores only │ CRM stores │ Fansifter │ │ │ (dedup'd against │ (from master) │ stores │ │ │ CRM & Fansifter) │ │ │ │ └─────────────────────┼────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌────────────────────────────┐ │ │ │ SHOPIFY_STORES_MASTER │ │ │ │ (THE SPINE) │ │ │ │ │ │ │ │ Union of: │ │ │ │ • CRM stores (~174) │ │ │ │ • Fansifter (~10-25) │ │ │ │ • D2C stores (~500-520) │ │ │ │ │ │ │ │ OUTPUT: │ │ │ │ ~690-710 rows │ │ │ │ (one per store) │ │ │ │ │ │ │ │ Enriched with: │ │ │ │ • Vendor/label names │ │ │ │ • Artist names & JSON │ │ │ │ • Custom domains │ │ │ │ • Country/territory │ │ │ │ • Rep/merch company │ │ │ └──────────┬────────────────┘ │ │ │ │ │ ┌───────────────────────────────┼───────────────────────────────────┐ │ │ │ │ │ │ │ │ ┌──────────────────────┐ │ ┌──────────────────────────┐ │ │ │ │ │INT_PAID_ORDER_DATES │ │ │INT_LAST_REFRESH │ │ │ │ │ │ │ │ │ │ │ │ │ │ │Union all ORDER tables│ │ │Union sync timestamps │ │ │ │ │ │from 3 sources, │ │ │from CUSTOMER/ORDER/ │ │ │ │ │ │aggregate first/last │ │ │ABANDONED_CHECKOUT │ │ │ │ │ │paid order date │ │ │ │ │ │ │ │ │per store │ │ │OUTPUT: │ │ │ │ │ │ │ │ │~690-710 rows │ │ │ │ │ │OUTPUT: │ │ │(per-store sync time) │ │ │ │ │ │~690-710 rows │ │ │ │ │ │ │ │ │(per-store dates) │ │ │ │ │ │ │ │ └──────────────────────┘ │ └──────────────────────────┘ │ │ │ │ │ │ │ │ └───────────────────────────────┼───────────────────────────────────┘ │ │ │ │ └───────────────────────────────────┼───────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────────────────────┐ │ LAYER 2: MART MODELS (24 tables + seeds) │ │ (models/mart/, tag:mart, full rebuild, joined to master spine) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ All mart models follow the same pattern: │ │ │ │ For each Shopify entity (ORDERS, CUSTOMER, PRODUCT, etc.): │ │ │ │ CRM PROD_SHOPIFY_ SHOPIFY_STORES_GLOBAL SEED CSVs │ │ raw tables INTEGRATIONS D2C raw schemas │ │ │ │ │ │ │ │ └───────────┼────────────────────┘ │ │ │ │ │ │ │ ┌───────────▼────────────────────────────────────────┘ │ │ │ generate_shopify_table_union('') │ │ │ • Discovers INFORMATION_SCHEMA across all sources │ │ │ • Unions raw tables, handles column mismatches │ │ │ • Fills missing columns with typed NULLs │ │ │ OUTPUT: Unioned table ~690+ stores │ │ │ │ │ └───────────┬─────────────────────────────────────────────────────┘ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ JOIN to SHOPIFY_STORES_MASTER │ │ │ │ (enrich with vendor, artist, label, domain metadata) │ │ │ └────┬────────────────────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ MART TABLES (24 total, all in SHOPIFY_GLOBAL_SCHEMA): │ │ │ ├─────────────────────────────────────────────────────────┤ │ │ │ ORDERS (~12-14M rows) PRODUCT │ │ │ │ CUSTOMER PRODUCT_TAG │ │ │ │ CUSTOMER_ADDRESS PRODUCT_VARIANT │ │ │ │ ORDER_LINE SHOP │ │ │ │ ORDER_LINE_REFUND FULFILLMENT │ │ │ │ ORDER_DISCOUNT_CODE FULFILLMENT_EVENT │ │ │ │ ORDER_URL_TAG FULFILLMENT_ORDER │ │ │ │ REFUND FULFILLMENT_ORDER_LINE │ │ │ │ TRANSACTION FULFILLMENT_ORDER_LINE_ITEM│ │ │ │ COLLECTION INVENTORY_ITEM │ │ │ │ COLLECTION_PRODUCT INVENTORY_LEVEL │ │ │ │ ABANDONED_CHECKOUT INVENTORY_QUANTITY │ │ │ │ │ │ │ │ All enriched with store metadata via SHOPIFY_STORES_ │ │ │ │ MASTER join on STORE_SCHEMA │ │ │ └─────────────────────────────────────────────────────────┘ │ │ │ │ SEEDS (stored as tables): │ │ ├─shopify_store_custom_domain (132 rows) │ │ ├─shopify_store_name_overrides (1 row) │ │ └─shopify_vendor_overrides (7 rows) │ │ │ └─────────────────────────────────────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────────────────────┐ │ LAYER 3: MART VIEWS (25+ thin views, auto-casted dates) │ │ (models/mart_views/, tag:mart_views, generated from mart tables) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ For each mart table, generate_date_view() creates a view that: │ │ • Introspects the table columns │ │ • Adds a _DATE cast for every column ending in _at │ │ │ │ Mart Table generate_date_view() View (in SHOPIFY_GLOBAL_SCHEMA_VIEWS) │ ─────────────────────────────────────────────────────────────────────── │ │ ORDERS ──────────────────────► v_orders │ │ CUSTOMER ──────────────────────► v_customer │ │ PRODUCT ──────────────────────► v_product │ │ ORDER_LINE ──────────────────────► v_order_line │ │ (... 20+ more views ...) │ │ │ │ Plus views over staging tables: │ │ ├─SHOPIFY_STORES_MASTER view (in SHOPIFY_GLOBAL_SCHEMA_VIEWS) │ │ └─(other internal staging table views) │ │ │ └─────────────────────────────────────────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────────────────────┐ │ ON-RUN-END HOOK (QA/PROD ONLY) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ apply_masking_policies() │ │ • Reapplies Snowflake column-level PII masking policies │ │ • Masks customer data in: ORDERS, FULFILLMENT_ORDER, TRANSACTION │ │ • Hashes email in ORDERS │ │ • Idempotent (uses FORCE flag) │ │ │ └─────────────────────────────────────────────────────────────────────────────┘ │ ▼ ┌─────────────────────────────────────────────────────────────────────────────┐ │ FINAL OUTPUT SCHEMAS │ │ (in SHOPIFY_STORES_GLOBAL database) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ SHOPIFY_GLOBAL_SCHEMA QA_SHOPIFY_STORE_SCHEMA │ │ ├─ Core tables (4) ├─ Core tables (4) │ │ │ ├─SHOPIFY_INT_D2C_STORES │ ├─SHOPIFY_INT_D2C_STORES │ │ │ ├─SHOPIFY_INT_PAID_ORDER_DATES │ ├─SHOPIFY_INT_PAID_ORDER_DATES │ │ │ ├─SHOPIFY_INT_LAST_REFRESH │ ├─SHOPIFY_INT_LAST_REFRESH │ │ │ └─SHOPIFY_STORES_MASTER │ └─SHOPIFY_STORES_MASTER │ │ │ │ │ │ ├─ Mart tables (24) ├─ Mart tables (24) │ │ │ ├─ORDERS (~12-14M rows) │ ├─ORDERS (QA subset) │ │ │ ├─CUSTOMER │ ├─CUSTOMER │ │ │ └─ (... 22 more ...) │ └─ (... 22 more ...) │ │ │ │ │ │ └─ PII masked to NULL / hashed └─ PII masked to NULL / hashed │ │ │ │ SHOPIFY_GLOBAL_SCHEMA_VIEWS QA_SHOPIFY_STORE_SCHEMA_VIEWS │ │ ├─ v_orders (auto-casted dates) ├─ v_orders (auto-casted dates) │ │ ├─ v_customer ├─ v_customer │ │ ├─ v_product ├─ v_product │ │ └─ (... 22+ more convenience views) └─ (... 22+ more views) │ │ │ └─────────────────────────────────────────────────────────────────────────────┘ ``` ## Key Takeaways from the DAG 1. **Three independent sources** (CRM, Fansifter, D2C) are discovered and unioned at the macro level 2. **SHOPIFY_STORES_MASTER is the bottleneck/spine** — all downstream tables depend on it for enrichment 3. **Deduplication happens early** — in `int_d2c_stores` and again in the master model 4. **All 24 mart tables follow the same pattern** — union + join to master + apply rules 5. **Views are thin convenience layers** — they add nothing except auto-casted date columns 6. **PII masking is post-run** — applied after tables materialize to ensure it's not lost during dbt's drop/recreate cycle