Star Schema

A star schema organizes analytical data around a business process, such as sales, shipments, or payments. A central fact table contains measurements and keys referencing surrounding dimension tables. The dimensions describe entities such as customers, products, stores, and dates. Unlike a highly normalized transactional model, a star typically keeps descriptive hierarchies together in each dimension, reducing the joins needed for common reports.

Start by defining the grain: exactly what one fact row represents. For a retail model, one row might represent one product line on one completed order. The fact table could contain order identifier, customer key, product key, sale-date key, quantity, and net sales amount. Product category and brand belong in the product dimension. Mixing order-level totals with line-level measurements in the same fact table can produce double counting. Measures also need explicit aggregation rules: sales amounts can usually be summed, but percentages should generally be calculated from aggregated numerators and denominators.

Dimension keys commonly use warehouse-generated surrogate identifiers rather than operational business identifiers. This separates analytical identity from source-system changes and supports historical versions. With a slowly changing dimension Type 2 approach, a customer's region change creates a new dimension row with a new surrogate key and validity dates. Each fact references the version applicable to its event time. Type 1 instead overwrites the attribute, which is appropriate when historical values need not be preserved. Load processes must handle late-arriving records and ensure that joins do not accidentally match multiple dimension versions.

In a cloud data warehouse such as Amazon Redshift, the star is implemented as tables queried with SQL joins and aggregations. Physical design remains important: distribution choices affect data movement, sort keys can support selective filtering, and statistics help the optimizer. Replicating a small dimension with an ALL distribution style can reduce join redistribution, but increases storage and maintenance work. Automatic table optimization is another option. Redshift primary-key and foreign-key declarations are informational rather than enforced, so pipelines must validate uniqueness and referential integrity before relying on those declarations.

A data lake can also hold fact and dimension tables, for example as Parquet datasets or Apache Iceberg tables on Amazon S3. AWS Glue Data Catalog records table metadata, and Amazon Athena can query the tables. AWS Lake Formation governs access to supported cataloged resources; it does not create the star model, enforce its relationships, or replace a query engine. Partitioning a large fact dataset by a commonly filtered date can reduce scanned data, whereas excessive partitioning can introduce metadata and small-file overhead.

Amazon DynamoDB requires a different modeling decision. It does not provide server-side relational joins, so directly copying separate fact and dimension tables into DynamoDB is generally unsuitable for ad hoc star-schema analytics. Design DynamoDB keys, indexes, and denormalized items around application access patterns. When analytical joins are required, export or replicate the relevant operational data into a warehouse or lake and construct the star there.

Schema evolution must preserve the grain and meaning of existing data. Adding an optional dimension attribute or a new measure can be compatible, but existing records still need defined null or default semantics. Changing a measurement's unit, redefining a business key, or moving from order grain to line-item grain requires an explicit migration plan, often involving new tables or versioned views. Coordinate catalog metadata, transformations, consumer queries, and access policies when schemas change.

The main trade-off is simpler analytical querying in exchange for transformation work and repeated descriptive attributes. A star is not the same as one completely flattened table, and it is not automatically faster for every workload. Snowflaking normalizes dimensions into additional related tables, trading reduced redundancy for more joins. Choose the model using query patterns, historical reporting requirements, data volume, and maintenance cost rather than assuming that every cloud database should use a star.