Star schema

Star schema designer overview

The Star schema designer turns a cleansed (silver) schema into a dimensional (gold) model: fact tables, dimension tables, surrogate keys, and the load procedures that fill them. AI proposes the design. You review and adjust it. Then Kenseme builds the tables and a Pipeline that loads them.

In the app: Data Model › Schemas › your source schema › Star Schema tab

[screenshot]
A star schema plan's dashboard: the toolbar, dimension and fact cards with their progress, and the Recommended Build Order.

Before you start

  • The Star Schema tab appears only when the Star Schema feature is on for your organization. It is included in the Trial, Business, and Enterprise plans, and an admin can turn it on or off. See Feature flags.
  • Start from a schema that is already cleansed and conformed. The designer analyzes that schema’s tables, columns, and relationships.
  • You need write access to create and change plans. Plans can’t be created or changed while your organization is in read-only mode.

When to use it

  • You have a silver schema and want a Kimball-style star schema on top of it.
  • You would rather review and correct a design than type one from scratch.
  • You want the load logic generated too, not only the table DDL.

It is the wrong tool if you want a normalized (3NF) model, a single wide table, or if your source data isn’t cleansed yet.

The journey

A plan moves through five stages. Each has its own page.

  1. Set up a plan. Name it, choose the destination schema and target platform, set naming conventions, and give the AI some context about your business.
  2. Review the AI’s design. Read the proposed bus matrix, untick what you don’t want, refine items with extra guidance, and create the plan.
  3. Build each dimension and fact. For each item, confirm or prepare its source, create its table, and generate its load procedure. Or build the whole plan in one click.
  4. Finish and deploy. Validate the plan, generate the master procedure, and download every script in dependency order.
  5. Keep it in step with its source. When the source schema changes, check for drift and heal what is safe to heal.

Where things end up

Artifact Where it is created
Source views that combine several source tables The source schema
Dimension, fact, and junk dimension tables The plan’s destination schema
Load procedures (or Python load functions for Databricks) The destination schema, as part of a Pipeline named after the plan
Master procedure that runs every load in order The destination schema, in the same Pipeline
Date dimension create-and-populate script The destination schema, as a script

Creating a plan from the review step creates the Pipeline for you. Everything you build afterwards is a normal Kenseme object: open it in its schema, version it, and generate SQL for it like anything else.

Note: Kenseme designs and generates. It doesn’t run anything against your database. You deploy the scripts yourself.

Terms used in the designer

Term Meaning
Plan One star schema design: its settings, its items, and its Pipeline.
Item One dimension or fact in the plan.
Bus matrix The grid of dimensions (rows) against facts (columns) showing which dimensions each fact uses.
Dimension lookup How a fact resolves a source key to a dimension’s surrogate key.
Role-playing dimension One dimension used more than once by a fact, for example DimDate (OrderDate) and DimDate (ShipDate).
Junk dimension A dimension that gathers low-cardinality flags from a fact’s source into one table.
Execution group A build wave. Dimensions are group 1; facts come in later groups once the dimensions they use are ready.