AI dimensional modeling & star schema generator

-- Conformed dimension (SCD2)
CREATE TABLE marts.dim_product (
  product_sk    BIGINT      NOT NULL,   -- surrogate key
  product_id    VARCHAR     NOT NULL,   -- natural key
  name          VARCHAR,
  category      VARCHAR,
  valid_from    DATE        NOT NULL,
  valid_to      DATE,
  is_current    BOOLEAN     NOT NULL,
  CONSTRAINT pk_dim_product PRIMARY KEY (product_sk)
);

-- Fact at transaction grain
CREATE TABLE marts.fct_sales (
  sale_id       VARCHAR     NOT NULL,
  date_sk       INT         NOT NULL,   -- FK -> dim_date
  product_sk    BIGINT      NOT NULL,   -- FK -> dim_product
  customer_sk   BIGINT      NOT NULL,   -- FK -> dim_customer
  quantity      INT         NOT NULL,
  amount        NUMERIC(18,2) NOT NULL,
  CONSTRAINT pk_fct_sales PRIMARY KEY (sale_id)
);