Schema Templates

CREATE TABLE bronze.raw_claims (
  claim_id        VARCHAR,
  member_id       VARCHAR,
  provider_id     VARCHAR,
  service_date    DATE,
  amount_billed   DECIMAL(12,2),
  amount_paid     DECIMAL(12,2),
  status          VARCHAR,
  payload         JSON,
  _source         VARCHAR NOT NULL,
  _loaded_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  _hash           VARCHAR
);

CREATE INDEX ON bronze.raw_claims(_loaded_at);

CREATE TABLE silver.claims_clean (
  claim_id        VARCHAR PRIMARY KEY,
  member_id       VARCHAR NOT NULL,
  provider_id     VARCHAR NOT NULL,
  service_date    DATE    NOT NULL,
  amount_billed   DECIMAL(12,2) NOT NULL,
  amount_paid     DECIMAL(12,2) NOT NULL,
  net_amount      DECIMAL(12,2) GENERATED ALWAYS AS (amount_billed - amount_paid) STORED,
  status          VARCHAR NOT NULL CHECK (status IN ('paid','denied','pending')),
  is_inpatient    BOOLEAN NOT NULL DEFAULT FALSE,
  _source         VARCHAR NOT NULL,
  _loaded_at      TIMESTAMP NOT NULL,
  _updated_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE gold.claims_summary (
  service_month   DATE        NOT NULL,
  provider_id     VARCHAR     NOT NULL,
  claim_count     INTEGER     NOT NULL,
  total_billed    DECIMAL(14,2) NOT NULL,
  total_paid      DECIMAL(14,2) NOT NULL,
  denial_rate     DECIMAL(5,4)  NOT NULL,
  _built_at       TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (service_month, provider_id)
);