Snowflake star schema template

Tables in this data model

• dim_customer — 7 columns
• dim_product — 4 columns
• dim_date — 4 columns
• dim_store — 4 columns
• dim_promotion — 3 columns
• fct_order_line — 9 columns
• fct_inventory_snapshot — 5 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE OR REPLACE TABLE dim_customer (
  customer_key  NUMBER(38,0)  NOT NULL PRIMARY KEY,
  customer_id   VARCHAR(36)   NOT NULL,
  country       VARCHAR(2),
  segment       VARCHAR(40),
  valid_from    TIMESTAMP_NTZ NOT NULL,
  valid_to      TIMESTAMP_NTZ,
  is_current    BOOLEAN
);

CREATE OR REPLACE TABLE dim_product (
  product_key NUMBER(38,0) NOT NULL PRIMARY KEY,
  sku         VARCHAR(32)  NOT NULL,
  category    VARCHAR(80),
  brand       VARCHAR(80)
);

CREATE OR REPLACE TABLE dim_date (
  date_key   NUMBER(8,0) NOT NULL PRIMARY KEY,
  cal_date   DATE        NOT NULL,
  fiscal_qtr VARCHAR(6),
  is_holiday BOOLEAN
);

CREATE OR REPLACE TABLE dim_store (
  store_key NUMBER(38,0) NOT NULL PRIMARY KEY,
  store_id  VARCHAR(20)  NOT NULL,
  region    VARCHAR(40),
  format    VARCHAR(20)
);

CREATE OR REPLACE TABLE dim_promotion (
  promotion_key NUMBER(38,0) NOT NULL PRIMARY KEY,
  promo_code    VARCHAR(30),
  discount_pct  NUMBER(5,2)
);

CREATE OR REPLACE TABLE fct_order_line (
  order_line_key NUMBER(38,0)  NOT NULL PRIMARY KEY,
  customer_key   NUMBER(38,0)  NOT NULL REFERENCES dim_customer(customer_key),
  product_key    NUMBER(38,0)  NOT NULL REFERENCES dim_product(product_key),
  date_key       NUMBER(8,0)   NOT NULL REFERENCES dim_date(date_key),
  store_key      NUMBER(38,0)  NOT NULL REFERENCES dim_store(store_key),
  promotion_key  NUMBER(38,0)  REFERENCES dim_promotion(promotion_key),
  quantity       NUMBER(10,0)  NOT NULL,
  net_amount     NUMBER(18,2)  NOT NULL,
  cost_amount    NUMBER(18,2)
);

CREATE OR REPLACE TABLE fct_inventory_snapshot (
  snapshot_key NUMBER(38,0) NOT NULL PRIMARY KEY,
  product_key  NUMBER(38,0) NOT NULL REFERENCES dim_product(product_key),
  store_key    NUMBER(38,0) NOT NULL REFERENCES dim_store(store_key),
  date_key     NUMBER(8,0)  NOT NULL REFERENCES dim_date(date_key),
  on_hand_qty  NUMBER(10,0) NOT NULL
);

Related: Database schema diagram tool · ER diagram from SQL · All database & er models templates · All 521 templates