Data Modeling Best Practices for Analytics

Choosing a modeling approach

Analytics data modeling is a spectrum from highly normalized (3NF) to fully denormalized (One Big Table). The right choice depends on your query patterns, team skills, and warehouse capabilities. Here are the dominant approaches.

Star schema

Fact tables (events, transactions) surrounded by dimension tables (users, products, dates). Facts contain foreign keys and measures; dimensions contain descriptive attributes. This is the most established pattern for BI and reporting.

Pros: Intuitive for business users, fast aggregation queries, well-supported by BI tools (Looker, Tableau, Power BI).

Cons: Requires upfront design effort, JOINs can be expensive at scale, slowly-changing dimensions add complexity.

Snowflake schema

Star schema with normalized dimensions — a dimension table further decomposed into sub-dimensions. For example, Product → Category → Department. Reduces redundancy but adds JOIN complexity.

Best for: Scenarios where dimension data is large and frequently updated, or where storage costs matter more than query simplicity.

One Big Table (OBT)

Pre-join all dimensions into a single wide table. Each row contains all context. No JOINs at query time — every query is a simple scan + filter + aggregate.

Pros: Simplest queries, fastest performance on columnar engines (BigQuery, Snowflake), easy for less-technical analysts.

Cons: Massive data duplication, slower ELT rebuild times, schema changes require full table rebuild.

Activity schema

Model all business events as a single stream: (entity_id, activity, timestamp, attributes). A universal fact table where the activity column determines the event type. Popularized by the Activity Schema spec.

Pros: Extremely flexible, handles new event types without schema changes, simple ELT.

Cons: Can be confusing for BI tools that expect dimensional models, wide-column queries are less intuitive.

Slowly Changing Dimensions (SCD)

How to handle dimension attributes that change over time:

• Type 1: Overwrite the old value. Simple but loses history.
• Type 2: Add a new row with valid_from/valid_to dates. Preserves full history but increases table size.
• Type 3: Add a "previous" column. Simple but only tracks one historical change.

Type 2 is the standard for analytics. Use surrogate keys (auto-incrementing integers) rather than natural keys to handle multiple versions of the same entity.

dbt modeling conventions

If you're using dbt, follow the staging → intermediate → marts pattern:

1. Staging models (stg_): 1:1 with source tables, renamed columns, cast types, light cleaning.
2. Intermediate models (int_): Business logic, JOINs, deduplication, window functions.
3. Mart models (fct_, dim_): Final consumer-facing tables, documented and tested.

Visualize your data model

Data models are easier to reason about visually. Diagram your fact and dimension tables, their relationships, and the lineage from source to mart. Datadef supports table nodes with column definitions, join edges, and lineage tracking — making it the ideal tool for data modeling documentation.