Star schema or wide tables for your ecommerce warehouse
Every ecommerce team eventually hits the same wall: the spreadsheet-turned-dashboard that worked at $2M in revenue falls over at $10M. The question that follows isn't "which BI tool should we buy" — it's "how do we actually structure the data underneath it." And that question usually comes down to two camps: star schema or wide tables. Pick wrong and you'll spend the next year fighting your own warehouse instead of shipping insights.
This isn't an academic debate. It's a decision that determines how fast your team can answer "what's our true ROAS by channel this week" without someone manually joining six spreadsheets at 11pm.
What each approach actually is
A star schema splits data into fact tables (orders, line items, ad spend, refunds) and dimension tables (customers, products, channels, dates). You join them at query time. It's normalized, it's the textbook Kimball approach, and it's what most data warehousing courses teach because it's efficient and avoids duplicating data.
A wide table — sometimes called a denormalized or "one big table" (OBT) approach — pre-joins everything into a single flat table. One row per order line item might already contain the customer's lifetime value, the product category, the marketing channel, the campaign name, and the date dimension fields all baked in. No joins needed at query time. You just filter and aggregate.
Both are valid. Neither is "more professional." The right call depends on your query patterns, your team's SQL fluency, and how often your dimensions change.
Where star schema wins
Star schema earns its keep when you have multiple fact tables that need to share dimensions consistently. If orders, returns, and ad spend all need to reference the same product dimension and the same date dimension, normalizing that dimension once means you fix a product category mapping in one place and it propagates everywhere. In a wide table world, you'd have to find and fix that mapping in three or four separate flattened tables.
Star schema also keeps storage and compute sane as your dimensions grow large. If your customer dimension has fifty attributes — acquisition channel, cohort, LTV tier, geography, tags — you don't want fifty columns duplicated across every single order line row. Normalizing that out keeps your fact tables lean and your dimension changes cheap.
It's also the better fit if you have analysts who write a lot of ad hoc SQL and know how to join confidently. Star schema rewards people who think in relational terms. If your team is comfortable with joins and wants the flexibility to slice data in ways you haven't pre-built, star schema gives them room to work.
Where wide tables win
Wide tables win when your end users are not SQL-first. If your team lives in Looker Studio, a lightweight BI tool, or hands dashboards to merchandising and ops people who will never write a join, a wide table removes an entire category of error. Nobody fat-fingers a join key or accidentally creates a fan-out that doubles your revenue numbers. The data is already correct and already flat.
Performance is the other big argument. Modern columnar warehouses — BigQuery, Snowflake, Redshift — are optimized to scan wide tables fast. Joins cost money and time, especially at scale, especially with window functions stacked on top. If your dashboard refresh is timing out or your warehouse bill is creeping up because of repeated joins across the same tables every query, collapsing those into a pre-joined wide table at the dbt layer can cut both cost and latency dramatically.
Wide tables also map more naturally to how ecommerce people actually think about data. Nobody in a marketing meeting thinks "let me join the fact_orders table to dim_channel." They think "show me revenue by channel by week." A wide table that already has channel and week sitting in the same row as revenue matches that mental model exactly.
The real-world hybrid approach
Most mature ecommerce data stacks don't pick one and walk away — they layer both. Build your star schema (or at least clean fact and dimension tables) as the source of truth in your warehouse. That's where data integrity lives: your order facts, your customer dimension, your product dimension, your marketing spend facts, all normalized and reconciled.
Then, in your transformation layer — dbt is the obvious tool here — build wide, denormalized tables on top of that star schema specifically for the dashboards and reports people actually use every day. One wide table for "daily channel performance," pre-joined with blended CAC, true ROAS after returns, and contribution margin. Another wide table for "customer cohort analysis" with LTV, first channel, and repeat purchase rate already flattened in.
This gives you the best of both: a normalized core that's easy to maintain and audit when something breaks, and flat, fast, foolproof tables for the humans who need answers without writing SQL. When your return rate calculation needs fixing, you fix it once in the star schema's fact_returns table and every downstream wide table inherits the fix on the next run.
The mistake to avoid is building wide tables directly off raw source data — straight from Shopify, your ad platforms, your 3PL — without a clean normalized layer in between. That's how you end up with five different wide tables that each calculate "net revenue" slightly differently because nobody agreed on returns handling or tax treatment upstream. The star schema layer is where you enforce that single definition once. The wide tables are just different views into that same clean truth.
How to actually decide
Ask three questions before you commit to an architecture. First, who's querying this data — analysts who write SQL, or operators who need a dashboard? Second, how many fact tables need to share the same dimensions, and how often do those dimensions change? Third, what's your actual query latency and cost right now, and is it a problem?
If you're a small team with one or two people touching the warehouse and most consumption happens through a few core dashboards, start wide. You'll move faster and avoid premature normalization that nobody needs yet. If you're scaling past a few data sources — Shopify, Amazon, ad platforms, your 3PL, your ERP — and multiple teams need consistent, auditable definitions of revenue and cost, invest in the star schema core early, even if it feels like overhead at first. Retrofitting normalization onto a pile of ungoverned wide tables later is far more painful than building it in from the start.
The schema you pick isn't a religious choice. It's an engineering tradeoff between flexibility and speed, between SQL power and dashboard safety. Build the normalized core for correctness, build wide tables for consumption, and you won't have to choose at all.