dbt-shopify-silver

A production-ready dbt package that normalizes raw Shopify data into a clean, analytics-ready Silver layer, handling GID parsing, currency normalization, deduplication, and boolean standardization across all major Shopify entities.

---

Why this package?

Raw Shopify data is messy:

• IDs are GraphQL GIDs (gid://shopify/Order/12345), not plain integers
• Monetary amounts are strings with inconsistent decimal places
• Booleans arrive as 'true', '1', true, or 'TRUE' depending on the API version
• Bronze tables accumulate duplicates from webhook retries and re-ingestion

This package handles all of it so your analysts can SELECT order_id, total_price without worrying about parsing.

---

Supported Models

Model	Source	Primary Key	Description
silver_shopify_orders	raw_orders	order_id	Deduplicated, normalized orders
silver_shopify_order_line_items	raw_order_line_items	(order_id, line_item_id)	Line items per order
silver_shopify_customers	raw_customers	customer_id	Customer profiles
silver_shopify_products	raw_products	product_id	Product catalog
silver_shopify_product_variants	raw_product_variants	variant_id	Product variants
silver_shopify_checkouts	raw_checkouts	checkout_id	All checkouts, including abandoned

---

Adapter Compatibility

Adapter	Supported
Snowflake	✅
BigQuery	✅
Redshift	✅
DuckDB	✅ (used for integration tests)

No warehouse-specific SQL; all models use dbt cross-database macros.

---

Installation

Add to your project's packages.yml:

packages:
  - package: your-org/dbt_shopify_silver
    version: [">=0.1.0", "<0.2.0"]

Then run:

dbt deps

---

Quickstart

1. Configure variables

In your project's dbt_project.yml:

vars:
  shopify_schema: 'bronze'               # Schema where raw tables live
  shopify_database: 'my_warehouse'       # Database (defaults to target.database)
  shopify_orders_table: 'raw_orders'     # Override if your table has a different name

2. Run the models

# Run all Silver models
dbt run --select dbt_shopify_silver

# Run a specific model
dbt run --select silver_shopify_orders

# Run with tests
dbt build --select dbt_shopify_silver

---

Variable Reference

Variable	Default	Description
shopify_schema	'bronze'	Source schema name
shopify_database	target.database	Source database
shopify_orders_table	'raw_orders'	Orders source table name
shopify_customers_table	'raw_customers'	Customers source table name
shopify_products_table	'raw_products'	Products source table name
shopify_product_variants_table	'raw_product_variants'	Variants source table name
shopify_order_line_items_table	'raw_order_line_items'	Line items source table name
shopify_checkouts_table	'raw_checkouts'	Checkouts source table name
shopify_currency_precision	2	Decimal places for currency rounding

---

Macros

This package exports 8 utility macros for use in your own models:

Macro	Description
parse_shopify_gid(column)	Extract BIGINT from GID string
extract_gid_type(column)	Extract entity type from GID
normalize_currency_amount(amount, currency)	Normalize to NUMERIC(18,2)
shopify_deduplicate(relation, key, ts)	ROW_NUMBER deduplication
get_latest_record(ref, key, ts)	Inline latest-row subquery
standardize_shopify_boolean(column)	Normalize all boolean representations
safe_cast_numeric(column, p, s)	Adapter-aware safe numeric cast
coalesce_timestamps(columns)	COALESCE over timestamp columns

See docs/MACRO_REFERENCE.md for full documentation.

---

Metadata Columns

Every Silver model includes these columns:

Column	Type	Description
_ingested_at	TIMESTAMP	When this row was written to Silver
_source_system	VARCHAR	Always 'shopify'
_row_hash	VARCHAR	MD5 hash of all business columns for change detection

---

How to Contribute

Contributions are welcome! Please read CONTRIBUTING.md for:

• Local development setup
• Coding conventions (also in docs/CONVENTIONS.md)
• How to add new models and macros
• Pull request checklist

---

License

MIT License; see LICENSE.