How should we choose between star schema and a flat table for sales reporting?

[NoraGH]

I’m trying to decide between using a star schema or a single flat table for our new sales reporting, and I’m worried about query performance down the line. Our fact table is growing fast, and I’m not sure if the simplicity of a flat table will become a bottleneck for our analysts.

[Jack_W]

We started with a flat table for dashboards, and the early months were fine. Once the sales fact grew into billions of rows, filters on date and product began racing across dozens of columns and the BI tool kept pulling from a single wide scan. We eventually moved to a dimensional model with a dedicated fact table and a handful of dimensions, added targeted aggregates, and partitioned by date. The difference showed up as a noticeable drop in query times for common reports.

[Alexander.W]

Flat tables are tempting because you can explain them quickly to analysts, but we found the joins in the BI layer started eating time as the data grew. We kept the flat table for a while and tried materialized aggregates and summary tables, which helped some reports but caused drift and more ETL complexity.

[EleanorH]

Do you really need every attribute in one wide table, or is there a real problem with how the analysts are filtering and what they actually need? I’m wary of solving the wrong problem here.

[MarkHC]

We did a staged approach, keeping a current year layer for fast access and building a lighter set of aggregates for older data, then measured latency before committing to a full redesign. It was painful to maintain but it bought time to validate what analysts actually use and what they can skip.