Free browser tool · No sign-in required

Star Schema Designer — Free Tool + SQL DDL Export

Build fact and dimension tables on a visual canvas, label every column with its warehouse role — surrogate key, measure, attribute — then export SQL DDL for PostgreSQL, MySQL, SQL Server or SQLite. Everything runs in your browser; nothing is uploaded.

  • ✓Fact & dimension tables
  • ✓FK relationship lines
  • ✓SQL DDL & PNG export
fact_sales DK date_key SK customer_key SK product_key M revenue dim_date SK date_key (PK) A year, quarter, month dim_customer SK customer_key (PK) A name, segment dim_product SK product_key (PK) A name, category dim_store SK store_key (PK) A name, city, region
Illustrative star schema. Build yours in the designer below.
Canvas Drag tables · click to select
Column types: SK Surrogate Key NK Natural Key DK Date Key M Measure A Attribute DD Degenerate Dim

What Is a Star Schema?

A star schema is a database design pattern used in data warehouses and analytical systems. At its centre sits a fact table that records business events — sales, clicks, shipments, support tickets — at a precisely defined grain. Surrounding that fact table are dimension tables that describe the who, what, where and when of each event. The arrangement, when drawn, looks like a star.

Ralph Kimball and Margy Ross documented the approach in The Data Warehouse Toolkit, and it remains the dominant pattern for subject-area analytical stores because it produces simple, predictable SQL. Most columnar query engines — BigQuery, Redshift, Snowflake the cloud platform, DuckDB — are optimized for the many-to-one join pattern a star schema produces.

Star Schema vs. Snowflake Schema

Both patterns use a central fact table. The difference is how dimension tables are organized. A star schema keeps every attribute for a dimension in one flat, denormalized table: a product dimension holds product name, subcategory, category and brand in a single row. A snowflake schema normalizes those into separate tables, so category sits in its own table joined to subcategory, which joins to product.

CharacteristicStar schemaSnowflake schema
Dimension structureFlat, denormalizedNormalized, hierarchical
Number of joinsFewer (fact → dim only)More (fact → dim → sub-dim)
StorageMore redundant textLess redundant, more rows
Query complexitySimpler SQLMore complex SQL
BI tool handlingWell supportedSupported, needs care

For most analytical workloads, star schemas win on simplicity. The extra storage from denormalized text is small compared to the fact table, and modern columnar stores compress repetitive strings efficiently anyway.

Fact Tables and Grain

The grain statement is the most important design decision you make for a fact table. It defines, in plain language, exactly what one row represents. "One row per sales order line item" is a declared grain. "One row per daily store inventory count" is another. Mixing facts from different grains produces numbers that cannot be summed correctly and will not make sense to analysts.

Fact tables typically carry three kinds of columns: foreign keys to every dimension — usually surrogate keys labeled SK or date keys labeled DK — and measures labeled M. Date keys use a compact integer in YYYYMMDD format rather than a DATE type, which supports efficient partition pruning on most platforms. Degenerate dimensions labeled DD capture values like order number that describe the event but need no dimension lookup table.

Additive vs. semi-additive measures: An additive measure, such as revenue or quantity, can be summed across all dimension values. A semi-additive measure, such as a bank account balance or inventory on hand, can be summed across some dimensions but not time — balance cannot be summed across months, only sampled at a point or averaged.

Dimension Tables

Dimension tables provide the descriptive context that makes fact numbers meaningful. A date dimension turns a date key integer into year, quarter, month, week number, fiscal period and holiday flag — all available without a join to any calendar API. A customer dimension turns a customer key into name, segment, region and acquisition channel.

Well-designed dimension tables include a surrogate key (SK) as the primary key, a natural key (NK) that holds the original source-system identifier, and human-readable attributes (A) for every descriptive field. This design decouples the warehouse from upstream source changes: when a customer changes their source ID, only the dimension row is updated, not the millions of fact rows that reference that customer's warehouse key.

Slowly Changing Dimensions

Customer names change. Products get reclassified. Sales regions are reorganized. When a dimension attribute changes, the warehouse must decide whether to overwrite the old value or preserve it. This is the slowly changing dimension problem, abbreviated SCD.

Type 1 overwrites the old value. Historical fact rows then join to the current attribute, losing history. Type 2 adds a new dimension row for each change, with effective_date and expiration_date columns; fact rows that referenced the old key still read the attribute value that was current when the event occurred. Type 3 adds a previous-value column alongside the current one, capturing only the most recent change. Type 2 is the standard approach when historical accuracy is required, and you can add those date columns to any dimension table in this designer.

SQL DDL Export

This tool generates CREATE TABLE statements from the schema you build on the canvas. Dimension tables are emitted before the fact table so foreign key constraints resolve in dependency order — a database cannot create a FK that points to a table that does not yet exist. Each dialect gets appropriate data types for the column semantic roles.

Surrogate keys (SK) in a dimension table become the primary key column using SERIAL or BIGSERIAL in PostgreSQL, INT IDENTITY(1,1) in SQL Server, INT AUTO_INCREMENT in MySQL, and INTEGER PRIMARY KEY in SQLite. Date keys (DK) always use INTEGER, since YYYYMMDD integers are efficient for range scans and partition pruning. Measures (M) default to NUMERIC(18,4) or DECIMAL(18,4). Attributes (A) default to VARCHAR(255) or its dialect equivalent.

Star Schema Examples

Sales Analytics

A retail sales star schema centres on fact_sales at "one row per order line item" grain. Foreign keys reference dim_date, dim_customer, dim_product and dim_store. Measures are quantity, unit_price, discount_amount and net_revenue. The date dimension includes year, quarter, month, week and is_holiday so reports can slice by any calendar period without touching the fact table.

E-commerce Orders

An e-commerce schema tracks fact_orders at order-line grain with dimensions for date, customer, product, shipping method and promotion. Degenerate dimensions such as order_id and line_number appear as DD columns in the fact table because they carry context from the transaction but need no lookup table of their own.

HR Attrition

An HR schema tracks employee headcount and departures in fact_headcount at monthly employee grain. Dimensions cover employee, department, job level, office location and date. Measures are headcount, new_hires, separations and voluntary_exits. This is a semi-additive fact table: headcount cannot be summed across months, only across other dimensions at a point in time. Load any of these three templates from the editor panel.

Star Schema Design Best Practices

  • Declare the grain before you add any columns. If you cannot state what one row represents in one sentence, the design needs more thought.
  • Put all dimension foreign keys in the fact table. The fact table is the intersection point; dimension tables do not join to other dimensions in a star schema.
  • Use surrogate keys as primary keys in every dimension. Never use business keys directly as warehouse primary keys — sources change their identifiers.
  • Build a date dimension with enough attributes for all reporting periods: fiscal year, week number, holiday flags, trading-day flags.
  • Keep dimension tables wide rather than deep. Twenty attribute columns in one flat table is easier to work with than a normalized hierarchy of four tables requiring extra joins.
  • Conform dimensions across subject areas. A dim_customer used by both a sales schema and a support schema lets analysts compare numbers across those subject areas without ambiguity.
FAQ

Star Schema Designer questions

Answers about star schemas, fact tables, dimensions and SQL export.

What is a star schema?+

A star schema is a data warehouse design pattern with one central fact table surrounded by denormalized dimension tables. The shape resembles a star. Ralph Kimball popularized the approach in the 1990s, and most analytical query engines handle it well because joins stay shallow — typically one join from fact to each dimension.

What is the difference between a star schema and a snowflake schema?+

In a star schema, dimension tables are flat and denormalized — a product dimension holds category and subcategory in the same row. A snowflake schema normalizes those into separate tables. Star schemas produce simpler SQL and suit BI tools better; snowflake schemas use less storage but need more joins.

What is a fact table in a star schema?+

A fact table stores measurable, numeric business events at a declared grain — one row per sales order line item, one row per daily inventory count. It holds foreign keys to every dimension table and numeric measures such as quantity and revenue. Fact tables are typically very long (many rows) but relatively few distinct values per column.

What is a dimension table in a star schema?+

A dimension table stores descriptive attributes about the entities that appear in the fact table: customers, products, dates, stores. Rows change slowly compared to facts. A well-designed dimension includes a surrogate key (SK) as primary key, a natural key (NK) to track the source identifier, and human-readable attributes (A).

What is grain in a data warehouse?+

Grain is the precise definition of what one row in a fact table represents. "One row per sales order line item" is a declared grain. Defining grain before adding measures is the most important design step — mixing facts at different levels of detail produces incorrect totals.

What is a surrogate key (SK)?+

A surrogate key is a system-generated integer with no business meaning — typically a sequence or identity column. It is the primary key in a dimension table and a foreign key in the fact table. Using surrogate keys decouples the warehouse from upstream source system changes: a customer who changes their ID does not break historical fact rows.

What is a slowly changing dimension (SCD)?+

A slowly changing dimension tracks how attribute values change over time. Type 1 overwrites the old value, losing history. Type 2 adds a new dimension row for each change with effective_date and expiration_date, preserving full history. Type 3 adds a previous-value column for one level of history. Type 2 is standard when historical accuracy matters.

How do I export a star schema as SQL?+

Use the SQL DDL button in this tool. Select your target database — PostgreSQL, MySQL, SQL Server or SQLite — and the tool generates CREATE TABLE statements in dependency order: dimension tables first so the fact table foreign key constraints resolve correctly. Copy the SQL or download as a .sql file.

Can I use a star schema with PostgreSQL?+

Yes. PostgreSQL handles star schemas well. This tool generates PostgreSQL DDL using SERIAL or BIGSERIAL for surrogate keys, standard SQL data types and FOREIGN KEY constraints. For large fact tables, consider partitioning by date key and adding indexes on each foreign key column.

What is the Kimball methodology?+

The Kimball methodology, developed by Ralph Kimball, is a bottom-up approach to data warehouse design that starts with business processes and builds dimensional models — star schemas — directly accessible to analysts. It emphasizes conformed dimensions shared across subject areas so facts from different schemas can be combined without ambiguity.