model warehouse {
  version "2.0"
  description "Retail sales star schema."
}

naming {
  table {
    pattern "(dim|fact)_[a-z_]+"
  }
}

lint {
  dimensional-star error
  orphan-entity off
}

domain measure {
  type decimal(18, 4)
  not null
  default 0
}

entity dim_customer {
  description "Customer dimension (type 1)."
  stereotype dimension
  attribute customer_key : integer not null
  attribute customer_id : text(40) not null
  attribute full_name : text(200) not null
  attribute segment : text(40)
  attribute country : char(2)
  key primary pk_dim_customer (customer_key)
  key unique uq_dim_customer_id (customer_id)
}

entity dim_date {
  description "Calendar dimension, one row per day."
  stereotype dimension
  attribute date_key : integer not null
  attribute full_date : date not null
  attribute day_of_month : smallint not null
  attribute day_of_week : smallint not null
  attribute day_name : text(9) not null
  attribute day_of_year : smallint not null
  attribute week_of_year : smallint not null
  attribute month_of_year : smallint not null
  attribute month_name : text(9) not null
  attribute quarter : smallint not null
  attribute year : smallint not null
  attribute is_weekend : boolean not null
  attribute is_holiday : boolean not null default false
  attribute fiscal_year : smallint not null
  attribute fiscal_quarter : smallint not null
  key primary pk_dim_date (date_key)
  key unique uq_dim_date_full_date (full_date)
}

entity dim_product {
  stereotype dimension
  attribute product_key : integer not null
  attribute sku : text(40) not null
  attribute name : text(200) not null
  attribute category : text(100)
  attribute brand : text(100)
  key primary pk_dim_product (product_key)
  key unique uq_dim_product_sku (sku)
}

entity dim_store {
  stereotype dimension
  attribute store_key : integer not null
  attribute store_code : text(20) not null
  attribute name : text(200) not null
  attribute region : text(100)
  key primary pk_dim_store (store_key)
  key unique uq_dim_store_code (store_code)
}

entity fact_sales {
  description "One row per sold line item."
  stereotype fact
  attribute sales_key : bigint not null
  attribute date_key : integer not null
  attribute customer_key : integer not null
  attribute product_key : integer not null
  attribute store_key : integer not null
  attribute quantity : integer not null
  attribute gross_amount : domain measure
  attribute discount_amount : domain measure
  attribute net_amount : domain measure
  attribute tax_amount : domain measure
  key primary pk_fact_sales (sales_key)
}

relationship fact_sales_customer {
  parent dim_customer
  child fact_sales
  columns (customer_key -> customer_key)
  cardinality one to many
}

relationship fact_sales_date {
  parent dim_date
  child fact_sales
  columns (date_key -> date_key)
  cardinality one to many
}

relationship fact_sales_product {
  parent dim_product
  child fact_sales
  columns (product_key -> product_key)
  cardinality one to many
}

relationship fact_sales_store {
  parent dim_store
  child fact_sales
  columns (store_key -> store_key)
  cardinality one to many
}

area "Sales Star" {
  description "The sales fact and its dimensions."
  entity dim_customer at 40 40
  entity dim_date at 360 40
  entity dim_product at 680 40
  entity dim_store at 1000 40
  entity fact_sales at 520 420
}

target bq : bigquery {
  schema public {
    table dim_customer {
      entity dim_customer
      comment "Customer dimension (type 1)."
      column customer_key int64 not null attribute customer_key
      column customer_id string(40) not null attribute customer_id
      column full_name string(200) not null attribute full_name
      column segment string(40) attribute segment
      column country string(2) attribute country
      primary key pk_dim_customer (customer_key)
      unique uq_dim_customer_id (customer_id)
    }
    table dim_date {
      entity dim_date
      comment "Calendar dimension, one row per day."
      column date_key int64 not null attribute date_key
      column full_date date not null attribute full_date
      column day_of_month int64 not null attribute day_of_month
      column day_of_week int64 not null attribute day_of_week
      column day_name string(9) not null attribute day_name
      column day_of_year int64 not null attribute day_of_year
      column week_of_year int64 not null attribute week_of_year
      column month_of_year int64 not null attribute month_of_year
      column month_name string(9) not null attribute month_name
      column quarter int64 not null attribute quarter
      column year int64 not null attribute year
      column is_weekend bool not null attribute is_weekend
      column is_holiday bool not null default false attribute is_holiday
      column fiscal_year int64 not null attribute fiscal_year
      column fiscal_quarter int64 not null attribute fiscal_quarter
      primary key pk_dim_date (date_key)
      unique uq_dim_date_full_date (full_date)
    }
    table dim_product {
      entity dim_product
      column product_key int64 not null attribute product_key
      column sku string(40) not null attribute sku
      column name string(200) not null attribute name
      column category string(100) attribute category
      column brand string(100) attribute brand
      primary key pk_dim_product (product_key)
      unique uq_dim_product_sku (sku)
    }
    table dim_store {
      entity dim_store
      column store_key int64 not null attribute store_key
      column store_code string(20) not null attribute store_code
      column name string(200) not null attribute name
      column region string(100) attribute region
      primary key pk_dim_store (store_key)
      unique uq_dim_store_code (store_code)
    }
    table fact_sales {
      entity fact_sales
      comment "One row per sold line item."
      option cluster_by "customer_key"
      option partition_by "RANGE_BUCKET(date_key, GENERATE_ARRAY(20200101, 20301231, 10000))"
      column sales_key int64 not null attribute sales_key
      column date_key int64 not null attribute date_key
      column customer_key int64 not null attribute customer_key
      column product_key int64 not null attribute product_key
      column store_key int64 not null attribute store_key
      column quantity int64 not null attribute quantity
      column gross_amount numeric(18, 4) not null default 0 attribute gross_amount
      column discount_amount numeric(18, 4) not null default 0 attribute discount_amount
      column net_amount numeric(18, 4) not null default 0 attribute net_amount
      column tax_amount numeric(18, 4) not null default 0 attribute tax_amount
      primary key pk_fact_sales (sales_key)
      foreign key fk_fact_sales_dim_customer (customer_key) references dim_customer (customer_key) relationship fact_sales_customer
      foreign key fk_fact_sales_dim_date (date_key) references dim_date (date_key) relationship fact_sales_date
      foreign key fk_fact_sales_dim_product (product_key) references dim_product (product_key) relationship fact_sales_product
      foreign key fk_fact_sales_dim_store (store_key) references dim_store (store_key) relationship fact_sales_store
      index ix_fact_sales_fact_sales_customer (customer_key)
      index ix_fact_sales_fact_sales_date (date_key)
      index ix_fact_sales_fact_sales_product (product_key)
      index ix_fact_sales_fact_sales_store (store_key)
    }
  }
}

target dbx : databricks {
  schema default {
    table dim_customer {
      entity dim_customer
      comment "Customer dimension (type 1)."
      column customer_key int not null identity always attribute customer_key
      column customer_id string not null attribute customer_id
      column full_name string not null attribute full_name
      column segment string attribute segment
      column country string attribute country
      primary key pk_dim_customer (customer_key)
      unique uq_dim_customer_id (customer_id)
    }
    table dim_date {
      entity dim_date
      comment "Calendar dimension, one row per day."
      column date_key int not null identity always attribute date_key
      column full_date date not null attribute full_date
      column day_of_month smallint not null attribute day_of_month
      column day_of_week smallint not null attribute day_of_week
      column day_name string not null attribute day_name
      column day_of_year smallint not null attribute day_of_year
      column week_of_year smallint not null attribute week_of_year
      column month_of_year smallint not null attribute month_of_year
      column month_name string not null attribute month_name
      column quarter smallint not null attribute quarter
      column year smallint not null attribute year
      column is_weekend boolean not null attribute is_weekend
      column is_holiday boolean not null default false attribute is_holiday
      column fiscal_year smallint not null attribute fiscal_year
      column fiscal_quarter smallint not null attribute fiscal_quarter
      primary key pk_dim_date (date_key)
      unique uq_dim_date_full_date (full_date)
    }
    table dim_product {
      entity dim_product
      column product_key int not null identity always attribute product_key
      column sku string not null attribute sku
      column name string not null attribute name
      column category string attribute category
      column brand string attribute brand
      primary key pk_dim_product (product_key)
      unique uq_dim_product_sku (sku)
    }
    table dim_store {
      entity dim_store
      column store_key int not null identity always attribute store_key
      column store_code string not null attribute store_code
      column name string not null attribute name
      column region string attribute region
      primary key pk_dim_store (store_key)
      unique uq_dim_store_code (store_code)
    }
    table fact_sales {
      entity fact_sales
      comment "One row per sold line item."
      option partition_by "date_key"
      column sales_key bigint not null identity always attribute sales_key
      column date_key int not null attribute date_key
      column customer_key int not null attribute customer_key
      column product_key int not null attribute product_key
      column store_key int not null attribute store_key
      column quantity int not null attribute quantity
      column gross_amount decimal(18, 4) not null default 0 attribute gross_amount
      column discount_amount decimal(18, 4) not null default 0 attribute discount_amount
      column net_amount decimal(18, 4) not null default 0 attribute net_amount
      column tax_amount decimal(18, 4) not null default 0 attribute tax_amount
      primary key pk_fact_sales (sales_key)
      foreign key fk_fact_sales_dim_customer (customer_key) references dim_customer (customer_key) relationship fact_sales_customer
      foreign key fk_fact_sales_dim_date (date_key) references dim_date (date_key) relationship fact_sales_date
      foreign key fk_fact_sales_dim_product (product_key) references dim_product (product_key) relationship fact_sales_product
      foreign key fk_fact_sales_dim_store (store_key) references dim_store (store_key) relationship fact_sales_store
      index ix_fact_sales_fact_sales_customer (customer_key)
      index ix_fact_sales_fact_sales_date (date_key)
      index ix_fact_sales_fact_sales_product (product_key)
      index ix_fact_sales_fact_sales_store (store_key)
    }
  }
}

target mysql : mysql {
  schema warehouse {
    table dim_customer {
      entity dim_customer
      comment "Customer dimension (type 1)."
      option engine "InnoDB"
      column customer_key int not null identity by_default attribute customer_key
      column customer_id varchar(40) not null attribute customer_id
      column full_name varchar(200) not null attribute full_name
      column segment varchar(40) attribute segment
      column country char(2) attribute country
      primary key pk_dim_customer (customer_key)
      unique uq_dim_customer_id (customer_id)
    }
    table dim_date {
      entity dim_date
      comment "Calendar dimension, one row per day."
      option engine "InnoDB"
      column date_key int not null attribute date_key
      column full_date date not null attribute full_date
      column day_of_month smallint not null attribute day_of_month
      column day_of_week smallint not null attribute day_of_week
      column day_name varchar(9) not null attribute day_name
      column day_of_year smallint not null attribute day_of_year
      column week_of_year smallint not null attribute week_of_year
      column month_of_year smallint not null attribute month_of_year
      column month_name varchar(9) not null attribute month_name
      column quarter smallint not null attribute quarter
      column year smallint not null attribute year
      column is_weekend tinyint(1) not null attribute is_weekend
      column is_holiday tinyint(1) not null default false attribute is_holiday
      column fiscal_year smallint not null attribute fiscal_year
      column fiscal_quarter smallint not null attribute fiscal_quarter
      primary key pk_dim_date (date_key)
      unique uq_dim_date_full_date (full_date)
    }
    table dim_product {
      entity dim_product
      option engine "InnoDB"
      column product_key int not null identity by_default attribute product_key
      column sku varchar(40) not null attribute sku
      column name varchar(200) not null attribute name
      column category varchar(100) attribute category
      column brand varchar(100) attribute brand
      primary key pk_dim_product (product_key)
      unique uq_dim_product_sku (sku)
    }
    table dim_store {
      entity dim_store
      option engine "InnoDB"
      column store_key int not null identity by_default attribute store_key
      column store_code varchar(20) not null attribute store_code
      column name varchar(200) not null attribute name
      column region varchar(100) attribute region
      primary key pk_dim_store (store_key)
      unique uq_dim_store_code (store_code)
    }
    table fact_sales {
      entity fact_sales
      comment "One row per sold line item."
      option engine "InnoDB"
      partition by hash (date_key) {
        partition p0 ""
        partition p1 ""
        partition p2 ""
        partition p3 ""
      }
      column sales_key bigint not null identity by_default attribute sales_key
      column date_key int not null attribute date_key
      column customer_key int not null attribute customer_key
      column product_key int not null attribute product_key
      column store_key int not null attribute store_key
      column quantity int not null attribute quantity
      column gross_amount decimal(18, 4) not null default 0 attribute gross_amount
      column discount_amount decimal(18, 4) not null default 0 attribute discount_amount
      column net_amount decimal(18, 4) not null default 0 attribute net_amount
      column tax_amount decimal(18, 4) not null default 0 attribute tax_amount
      primary key pk_fact_sales (sales_key, date_key)
      index ix_fact_sales_customer (customer_key)
      index ix_fact_sales_date (date_key)
      index ix_fact_sales_product (product_key)
      index ix_fact_sales_store (store_key)
    }
  }
}

target pg : postgresql {
  schema dw {
    comment "Data warehouse star schema."
    table dim_customer {
      entity dim_customer
      comment "Customer dimension (type 1)."
      column customer_key integer not null identity always attribute customer_key
      column customer_id varchar(40) not null attribute customer_id
      column full_name varchar(200) not null attribute full_name
      column segment varchar(40) attribute segment
      column country char(2) attribute country
      primary key pk_dim_customer (customer_key)
      unique uq_dim_customer_id (customer_id)
    }
    table dim_date {
      entity dim_date
      comment "Calendar dimension, one row per day."
      column date_key integer not null attribute date_key
      column full_date date not null attribute full_date
      column day_of_month smallint not null attribute day_of_month
      column day_of_week smallint not null attribute day_of_week
      column day_name varchar(9) not null attribute day_name
      column day_of_year smallint not null attribute day_of_year
      column week_of_year smallint not null attribute week_of_year
      column month_of_year smallint not null attribute month_of_year
      column month_name varchar(9) not null attribute month_name
      column quarter smallint not null attribute quarter
      column year smallint not null attribute year
      column is_weekend boolean not null attribute is_weekend
      column is_holiday boolean not null default false attribute is_holiday
      column fiscal_year smallint not null attribute fiscal_year
      column fiscal_quarter smallint not null attribute fiscal_quarter
      primary key pk_dim_date (date_key)
      unique uq_dim_date_full_date (full_date)
    }
    table dim_product {
      entity dim_product
      column product_key integer not null identity always attribute product_key
      column sku varchar(40) not null attribute sku
      column name varchar(200) not null attribute name
      column category varchar(100) attribute category
      column brand varchar(100) attribute brand
      primary key pk_dim_product (product_key)
      unique uq_dim_product_sku (sku)
    }
    table dim_store {
      entity dim_store
      column store_key integer not null identity always attribute store_key
      column store_code varchar(20) not null attribute store_code
      column name varchar(200) not null attribute name
      column region varchar(100) attribute region
      primary key pk_dim_store (store_key)
      unique uq_dim_store_code (store_code)
    }
    table fact_sales {
      entity fact_sales
      comment "One row per sold line item."
      partition by range (date_key) {
        partition fact_sales_2024 "FROM (20240101) TO (20250101)"
        partition fact_sales_2025 "FROM (20250101) TO (20260101)"
      }
      column sales_key bigint not null identity always attribute sales_key
      column date_key integer not null attribute date_key
      column customer_key integer not null attribute customer_key
      column product_key integer not null attribute product_key
      column store_key integer not null attribute store_key
      column quantity integer not null attribute quantity
      column gross_amount numeric(18, 4) not null default 0 attribute gross_amount
      column discount_amount numeric(18, 4) not null default 0 attribute discount_amount
      column net_amount numeric(18, 4) not null default 0 attribute net_amount
      column tax_amount numeric(18, 4) not null default 0 attribute tax_amount
      primary key pk_fact_sales (sales_key, date_key)
      foreign key fk_fact_sales_customer (customer_key) references dim_customer (customer_key) relationship fact_sales_customer
      foreign key fk_fact_sales_date (date_key) references dim_date (date_key) relationship fact_sales_date
      foreign key fk_fact_sales_product (product_key) references dim_product (product_key) relationship fact_sales_product
      foreign key fk_fact_sales_store (store_key) references dim_store (store_key) relationship fact_sales_store
      index ix_fact_sales_customer (customer_key)
      index ix_fact_sales_date (date_key)
      index ix_fact_sales_date_store (date_key, store_key desc) using btree include (net_amount) where "quantity > 0"
      index ix_fact_sales_product (product_key)
      index ix_fact_sales_store (store_key)
    }
  }
}

target rs : redshift {
  schema public {
    table dim_customer {
      entity dim_customer
      comment "Customer dimension (type 1)."
      column customer_key integer not null identity always attribute customer_key
      column customer_id varchar(40) not null attribute customer_id
      column full_name varchar(200) not null attribute full_name
      column segment varchar(40) attribute segment
      column country char(2) attribute country
      primary key pk_dim_customer (customer_key)
      unique uq_dim_customer_id (customer_id)
    }
    table dim_date {
      entity dim_date
      comment "Calendar dimension, one row per day."
      column date_key integer not null identity always attribute date_key
      column full_date date not null attribute full_date
      column day_of_month smallint not null attribute day_of_month
      column day_of_week smallint not null attribute day_of_week
      column day_name varchar(9) not null attribute day_name
      column day_of_year smallint not null attribute day_of_year
      column week_of_year smallint not null attribute week_of_year
      column month_of_year smallint not null attribute month_of_year
      column month_name varchar(9) not null attribute month_name
      column quarter smallint not null attribute quarter
      column year smallint not null attribute year
      column is_weekend boolean not null attribute is_weekend
      column is_holiday boolean not null default false attribute is_holiday
      column fiscal_year smallint not null attribute fiscal_year
      column fiscal_quarter smallint not null attribute fiscal_quarter
      primary key pk_dim_date (date_key)
      unique uq_dim_date_full_date (full_date)
    }
    table dim_product {
      entity dim_product
      column product_key integer not null identity always attribute product_key
      column sku varchar(40) not null attribute sku
      column name varchar(200) not null attribute name
      column category varchar(100) attribute category
      column brand varchar(100) attribute brand
      primary key pk_dim_product (product_key)
      unique uq_dim_product_sku (sku)
    }
    table dim_store {
      entity dim_store
      column store_key integer not null identity always attribute store_key
      column store_code varchar(20) not null attribute store_code
      column name varchar(200) not null attribute name
      column region varchar(100) attribute region
      primary key pk_dim_store (store_key)
      unique uq_dim_store_code (store_code)
    }
    table fact_sales {
      entity fact_sales
      comment "One row per sold line item."
      option distkey "customer_key"
      option sortkey "date_key"
      column sales_key bigint not null identity always attribute sales_key
      column date_key integer not null attribute date_key
      column customer_key integer not null attribute customer_key
      column product_key integer not null attribute product_key
      column store_key integer not null attribute store_key
      column quantity integer not null attribute quantity
      column gross_amount numeric(18, 4) not null default 0 attribute gross_amount
      column discount_amount numeric(18, 4) not null default 0 attribute discount_amount
      column net_amount numeric(18, 4) not null default 0 attribute net_amount
      column tax_amount numeric(18, 4) not null default 0 attribute tax_amount
      primary key pk_fact_sales (sales_key)
      foreign key fk_fact_sales_dim_customer (customer_key) references dim_customer (customer_key) relationship fact_sales_customer
      foreign key fk_fact_sales_dim_date (date_key) references dim_date (date_key) relationship fact_sales_date
      foreign key fk_fact_sales_dim_product (product_key) references dim_product (product_key) relationship fact_sales_product
      foreign key fk_fact_sales_dim_store (store_key) references dim_store (store_key) relationship fact_sales_store
      index ix_fact_sales_fact_sales_customer (customer_key)
      index ix_fact_sales_fact_sales_date (date_key)
      index ix_fact_sales_fact_sales_product (product_key)
      index ix_fact_sales_fact_sales_store (store_key)
    }
  }
}

target snow : snowflake {
  schema public {
    table dim_customer {
      entity dim_customer
      comment "Customer dimension (type 1)."
      column customer_key number(10, 0) not null identity always attribute customer_key
      column customer_id varchar(40) not null attribute customer_id
      column full_name varchar(200) not null attribute full_name
      column segment varchar(40) attribute segment
      column country char(2) attribute country
      primary key pk_dim_customer (customer_key)
      unique uq_dim_customer_id (customer_id)
    }
    table dim_date {
      entity dim_date
      comment "Calendar dimension, one row per day."
      column date_key number(10, 0) not null identity always attribute date_key
      column full_date date not null attribute full_date
      column day_of_month number(5, 0) not null attribute day_of_month
      column day_of_week number(5, 0) not null attribute day_of_week
      column day_name varchar(9) not null attribute day_name
      column day_of_year number(5, 0) not null attribute day_of_year
      column week_of_year number(5, 0) not null attribute week_of_year
      column month_of_year number(5, 0) not null attribute month_of_year
      column month_name varchar(9) not null attribute month_name
      column quarter number(5, 0) not null attribute quarter
      column year number(5, 0) not null attribute year
      column is_weekend boolean not null attribute is_weekend
      column is_holiday boolean not null default false attribute is_holiday
      column fiscal_year number(5, 0) not null attribute fiscal_year
      column fiscal_quarter number(5, 0) not null attribute fiscal_quarter
      primary key pk_dim_date (date_key)
      unique uq_dim_date_full_date (full_date)
    }
    table dim_product {
      entity dim_product
      column product_key number(10, 0) not null identity always attribute product_key
      column sku varchar(40) not null attribute sku
      column name varchar(200) not null attribute name
      column category varchar(100) attribute category
      column brand varchar(100) attribute brand
      primary key pk_dim_product (product_key)
      unique uq_dim_product_sku (sku)
    }
    table dim_store {
      entity dim_store
      column store_key number(10, 0) not null identity always attribute store_key
      column store_code varchar(20) not null attribute store_code
      column name varchar(200) not null attribute name
      column region varchar(100) attribute region
      primary key pk_dim_store (store_key)
      unique uq_dim_store_code (store_code)
    }
    table fact_sales {
      entity fact_sales
      comment "One row per sold line item."
      option cluster_by "date_key"
      column sales_key number(19, 0) not null identity always attribute sales_key
      column date_key number(10, 0) not null attribute date_key
      column customer_key number(10, 0) not null attribute customer_key
      column product_key number(10, 0) not null attribute product_key
      column store_key number(10, 0) not null attribute store_key
      column quantity number(10, 0) not null attribute quantity
      column gross_amount number(18, 4) not null default 0 attribute gross_amount
      column discount_amount number(18, 4) not null default 0 attribute discount_amount
      column net_amount number(18, 4) not null default 0 attribute net_amount
      column tax_amount number(18, 4) not null default 0 attribute tax_amount
      primary key pk_fact_sales (sales_key)
      foreign key fk_fact_sales_dim_customer (customer_key) references dim_customer (customer_key) relationship fact_sales_customer
      foreign key fk_fact_sales_dim_date (date_key) references dim_date (date_key) relationship fact_sales_date
      foreign key fk_fact_sales_dim_product (product_key) references dim_product (product_key) relationship fact_sales_product
      foreign key fk_fact_sales_dim_store (store_key) references dim_store (store_key) relationship fact_sales_store
      index ix_fact_sales_fact_sales_customer (customer_key)
      index ix_fact_sales_fact_sales_date (date_key)
      index ix_fact_sales_fact_sales_product (product_key)
      index ix_fact_sales_fact_sales_store (store_key)
    }
  }
}