Shopify Data Warehouse Schema: Orders, Products, Customers and SQL

Quick answer

The five core Shopify tables and how they join, as one query: every order line with its order, customer, product and variant.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select
  o.id as order_id,
  o.created_at,
  c.email,
  p.title as product,
  v.title as variant,
  ol.quantity,
  ol.price * ol.quantity - ol.total_discount as line_revenue
from shopify.order_line ol
join shopify."order" o on o.id = ol.order_id
left join shopify.customer c on c.id = o.customer_id
left join shopify.product p on p.id = ol.product_id
left join shopify.product_variant v on v.id = ol.variant_id
where not o.test and o.cancelled_at is null
limit 100;

Schema assumptions

Fivetran’s classic Shopify schema. Fivetran’s newer GraphQL-based schema keeps the table names but renames money columns (for example total_price_set_shop_amount, currency_code).

Table	Grain	Key	Joins to	Columns used here
order	one row per order	id	customer.id via customer_id	created_at, cancelled_at, test, currency, total_price, total_discounts, source_name, _fivetran_deleted
order_line	one row per line item	id	order.id via order_id; product.id; product_variant.id via variant_id	quantity, price, total_discount, sku, title
customer	one row per customer	id		email, created_at, state
product	one row per product	id		title, product_type, vendor, status, _fivetran_deleted
product_variant	one row per sellable variant	id	product.id via product_id	title, sku, price, inventory_quantity
refund	one row per refund	id	order.id via order_id	created_at
order_line_refund	one row per refunded line	id	refund.id via refund_id; order_line.id via order_line_id	quantity, subtotal

Other sources	What’s different
Airbyte	Plural names: orders, customers, products, product_variants, order_refunds. orders.line_items and orders.refunds are JSON arrays; orders.customer is a JSON object.
demo_shop (AI2SQL)	customers(id, name, country, signup_date), products(id, name, category, price), orders(id, customer_id, order_date, status, total), order_items(id, order_id, product_id, quantity, unit_price).

Copy-ready SQL

1. Fact table: order lines with net revenue (Postgres view)

One row per order line, refunds subtracted, test, cancelled and deleted rows removed. Change: analytics to the schema you build into.

-- PostgreSQL (Fivetran Shopify schema "shopify")
create schema if not exists analytics;

create or replace view analytics.fct_order_lines as
with line_refunds as (
  select order_line_id, sum(quantity) as refunded_qty, sum(subtotal) as refunded_amount
  from shopify.order_line_refund
  group by order_line_id
)
select
  ol.id as order_line_id,
  o.id as order_id,
  o.customer_id,
  ol.product_id,
  ol.variant_id,
  o.created_at as ordered_at,
  o.currency,
  o.source_name as channel,
  ol.quantity,
  coalesce(lr.refunded_qty, 0) as refunded_qty,
  ol.price * ol.quantity - ol.total_discount as gross_revenue,
  ol.price * ol.quantity - ol.total_discount - coalesce(lr.refunded_amount, 0) as net_revenue
from shopify.order_line ol
join shopify."order" o on o.id = ol.order_id
left join line_refunds lr on lr.order_line_id = ol.id
where not o.test
  and o.cancelled_at is null
  and not coalesce(o._fivetran_deleted, false);

2. Customer dimension (Postgres view)

First order date, order count and lifetime net revenue per customer. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
create or replace view analytics.dim_customers as
select
  c.id as customer_id,
  c.email,
  c.created_at as signed_up_at,
  min(f.ordered_at) as first_order_at,
  count(distinct f.order_id) as orders,
  coalesce(sum(f.net_revenue), 0) as lifetime_net_revenue
from shopify.customer c
left join analytics.fct_order_lines f on f.customer_id = c.id
group by c.id, c.email, c.created_at;

3. Product dimension at variant level (Postgres view)

One row per sellable variant with its product attributes. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
create or replace view analytics.dim_products as
select
  v.id as variant_id,
  p.id as product_id,
  p.title as product,
  v.title as variant,
  v.sku,
  coalesce(nullif(p.product_type, ''), '(none)') as product_type,
  p.vendor,
  v.price as list_price,
  v.inventory_quantity
from shopify.product_variant v
join shopify.product p on p.id = v.product_id
where not coalesce(p._fivetran_deleted, false);

4. Net revenue by product type per month, on the star schema

Every report gets simpler once the views exist. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select
  date_trunc('month', f.ordered_at)::date as month,
  d.product_type,
  sum(f.quantity - f.refunded_qty) as net_units,
  sum(f.net_revenue) as net_revenue
from analytics.fct_order_lines f
join analytics.dim_products d on d.variant_id = f.variant_id
group by 1, 2
order by 1 desc, net_revenue desc;

month	product_type	net_units	net_revenue
2026-09-01	Bags	1204	28890.00
2026-09-01	Shirts	860	21310.00

5. SQL Server tables for Shopify orders, products and customers

If you load Shopify’s API into SQL Server yourself. Keys are bigint, money decimal(18,2), timestamps datetimeoffset. Change: column lists to the fields you pull.

-- SQL Server (T-SQL)
create table dbo.customers (
  id bigint not null primary key,
  email nvarchar(320) null,
  first_name nvarchar(255) null,
  last_name nvarchar(255) null,
  created_at datetimeoffset not null
);

create table dbo.products (
  id bigint not null primary key,
  title nvarchar(255) not null,
  product_type nvarchar(255) null,
  vendor nvarchar(255) null,
  status nvarchar(20) not null
);

create table dbo.product_variants (
  id bigint not null primary key,
  product_id bigint not null references dbo.products (id),
  title nvarchar(255) null,
  sku nvarchar(255) null,
  price decimal(18, 2) not null,
  inventory_quantity int null
);

create table dbo.orders (
  id bigint not null primary key,
  customer_id bigint null references dbo.customers (id),
  created_at datetimeoffset not null,
  cancelled_at datetimeoffset null,
  currency char(3) not null,
  total_price decimal(18, 2) not null,
  total_discounts decimal(18, 2) not null default 0,
  financial_status nvarchar(30) null,
  source_name nvarchar(100) null,
  test bit not null default 0
);

create table dbo.order_lines (
  id bigint not null primary key,
  order_id bigint not null references dbo.orders (id),
  product_id bigint null references dbo.products (id),
  variant_id bigint null references dbo.product_variants (id),
  quantity int not null,
  price decimal(18, 2) not null,
  total_discount decimal(18, 2) not null default 0
);

create index ix_orders_customer_created on dbo.orders (customer_id, created_at);
create index ix_order_lines_order on dbo.order_lines (order_id);

6. Revenue by product type per month on those SQL Server tables

The same report on the tables above. Change: nothing.

-- SQL Server (T-SQL)
select
  datefromparts(year(o.created_at), month(o.created_at), 1) as month,
  coalesce(nullif(p.product_type, ''), '(none)') as product_type,
  sum(ol.quantity) as units,
  sum(ol.price * ol.quantity - ol.total_discount) as revenue
from dbo.order_lines ol
join dbo.orders o on o.id = ol.order_id
left join dbo.products p on p.id = ol.product_id
where o.test = 0 and o.cancelled_at is null
group by datefromparts(year(o.created_at), month(o.created_at), 1), coalesce(nullif(p.product_type, ''), '(none)')
order by month desc, revenue desc;

7. Order lines from Airbyte’s nested JSON (Postgres)

Airbyte keeps line items inside each order. Unnest them to get the same order-line grain. Change: the airbyte_shopify schema name.

-- PostgreSQL (Airbyte Shopify schema "airbyte_shopify")
select
  o.id as order_id,
  o.created_at,
  (li ->> 'product_id')::bigint as product_id,
  (li ->> 'variant_id')::bigint as variant_id,
  li ->> 'title' as title,
  (li ->> 'quantity')::int as quantity,
  (li ->> 'price')::numeric as price
from airbyte_shopify.orders o
cross join lateral jsonb_array_elements(o.line_items) as li
where not o.test and o.cancelled_at is null
limit 100;

order_id	created_at	product_id	variant_id	title	quantity	price
5810235293845	2026-10-11 16:20	8123456789012	44123456789012	Canvas Tote	2	24.00

8. Revenue by product type per month in BigQuery

Fivetran’s schema in BigQuery; order needs backticks. Change: your_project.shopify to your dataset.

-- BigQuery (Fivetran Shopify dataset "shopify")
select
  date_trunc(date(o.created_at), month) as month,
  coalesce(nullif(p.product_type, ''), '(none)') as product_type,
  sum(ol.price * ol.quantity - ol.total_discount) as revenue
from `your_project.shopify.order_line` ol
join `your_project.shopify.order` o on o.id = ol.order_id
left join `your_project.shopify.product` p on p.id = ol.product_id
where not o.test and o.cancelled_at is null
group by month, product_type
order by month desc, revenue desc

select
  date_trunc('month', o.order_date)::date as month,
  p.category,
  sum(oi.quantity) as units,
  sum(oi.quantity * oi.unit_price) as revenue
from order_items oi
join orders o on o.id = oi.order_id
join products p on p.id = oi.product_id
where o.status in ('completed', 'shipped')
group by 1, 2
order by 1 desc, revenue desc