CloudSketch AI Logo
FeaturesTemplatesPricingEnterpriseAboutContactLog in
🌙☀️
Log inStart free
Home/Templates/Data Engineering & Analytics

📊 Data Engineering & Analytics · Database ERD

Sales Star Schema

A classic star schema for sales analytics: one fact table of order lines surrounded by date, customer, product, store and promotion dimensions.

More Data Engineering & Analytics templates

Opening a template is free and never counts toward your daily diagrams. Asking the AI to change it counts as one.

Drawing diagram…

What this diagram shows

  • Facts hold numbers, dimensions hold descriptions
  • Customer history is kept with slowly changing dimension fields
  • A date dimension makes fiscal-year reporting easy

Prompt used

Star schema for retail sales: a fact_sales table with one row per order line (quantity, net amount, discount, cost) linked to dim_date, dim_customer (with valid_from and valid_to for history), dim_product, dim_store and dim_promotion.

Mermaid code
erDiagram
  DIM_DATE ||--o{ FACT_SALES : "sold on"
  DIM_CUSTOMER ||--o{ FACT_SALES : buys
  DIM_PRODUCT ||--o{ FACT_SALES : "sold as"
  DIM_STORE ||--o{ FACT_SALES : "sold at"
  DIM_PROMOTION ||--o{ FACT_SALES : applies
  FACT_SALES {
    bigint sales_key PK
    int date_key FK
    int customer_key FK
    int product_key FK
    int store_key FK
    int promotion_key FK
    int quantity
    decimal net_amount
    decimal discount
    decimal cost
  }
  DIM_DATE {
    int date_key PK
    date full_date
    string month
    string fiscal_quarter
    boolean is_holiday
  }
  DIM_CUSTOMER {
    int customer_key PK
    string customer_id
    string city
    string segment
    date valid_from
    date valid_to
  }
  DIM_PRODUCT {
    int product_key PK
    string sku
    string category
    string brand
  }
  DIM_STORE {
    int store_key PK
    string name
    string region
  }
  DIM_PROMOTION {
    int promotion_key PK
    string name
    string type
  }

Inside this diagram

An entity relationship diagram with 6 tables, 31 columns and 5 relationships.

Tables and their columns

  • DIM_DATE: date_key (PK), full_date, month, fiscal_quarter, is_holiday
  • FACT_SALES: sales_key (PK), date_key (FK), customer_key (FK), product_key (FK), store_key (FK), promotion_key (FK), quantity, net_amount, discount, cost
  • DIM_CUSTOMER: customer_key (PK), customer_id, city, segment, valid_from, valid_to
  • DIM_PRODUCT: product_key (PK), sku, category, brand
  • DIM_STORE: store_key (PK), name, region
  • DIM_PROMOTION: promotion_key (PK), name, type

Relationships

  • DIM_DATE sold on FACT_SALES: each DIM_DATE has zero or more FACT_SALES, each FACT_SALES belongs to exactly one DIM_DATE
  • DIM_CUSTOMER buys FACT_SALES: each DIM_CUSTOMER has zero or more FACT_SALES, each FACT_SALES belongs to exactly one DIM_CUSTOMER
  • DIM_PRODUCT sold as FACT_SALES: each DIM_PRODUCT has zero or more FACT_SALES, each FACT_SALES belongs to exactly one DIM_PRODUCT
  • DIM_STORE sold at FACT_SALES: each DIM_STORE has zero or more FACT_SALES, each FACT_SALES belongs to exactly one DIM_STORE
  • DIM_PROMOTION applies FACT_SALES: each DIM_PROMOTION has zero or more FACT_SALES, each FACT_SALES belongs to exactly one DIM_PROMOTION

Make your own diagram like this

Describe your own system in plain English and the AI ER diagram generator draws it in seconds, in the same style as this template.

Related templates

C4 ArchitectureProData Engineering & Analytics

Modern Data Platform Architecture

A typical modern data stack: data is loaded from apps and SaaS tools into a cloud warehouse, modelled with dbt, orchestrated with Airflow and served to BI dashboards.

Data FlowData Engineering & Analytics

Change Data Capture Pipeline

How every insert, update and delete in an operational database is streamed to the data lake and search index in near real time using change data capture.

FlowchartData Engineering & Analytics

Nightly Batch ETL Workflow

The steps of a nightly batch pipeline with data quality gates: extract, validate, transform, load and publish, stopping safely when checks fail.

DeploymentProData Engineering & Analytics

Real-time Streaming Analytics Deployment

Where a real-time analytics stack runs: Kafka for events, Flink for stream processing, a real-time OLAP database and live dashboards.

State MachineData Engineering & Analytics

Data Pipeline Run States

The states of a single pipeline run in an orchestrator like Airflow, including retries, upstream failures and manual reruns.

Data FlowData Engineering & Analytics

Data Lake Zones (Bronze, Silver, Gold)

The medallion pattern for a data lake: raw data lands in bronze, is cleaned in silver, and turned into business-ready tables in gold.