databricks/oral_health_schema.sql.deprecated

/*
 * ER/Studio Data Architect SQL Code Generation
 * Project :      NOHDP_Oral_Health_Schema_Databricks_Std_Naming_Key_TypeApplied.DM1
 *
 * Date Created : Wednesday, April 22, 2026 07:19:07
 * Target DBMS : Databricks
 */

/* 
 * TABLE: dim_data_source 
 */

CREATE TABLE dim_data_source
(
    source_key                   string         NOT NULL,
    data_steward_desc            string,
    data_steward_code            string,
    dataset_desc                 string,
    dataset_code                 string,
    collection_mode_type         string,
    record_created_dttm          timestamp,
    record_last_modified_dttm    timestamp,
    load_run_id                  bigint,
    CONSTRAINT pk_dim_data_source PRIMARY KEY (source_key) NOT ENFORCED 
)
;

/* 
 * TABLE: dim_date 
 */

CREATE TABLE dim_date
(
    date_key              int          NOT NULL,
    full_date             date         NOT NULL,
    day_of_month          int,
    day_of_week           int,
    day_of_week_name      string,
    is_weekend            boolean,
    week_of_year          int,
    iso_week              string,
    month_number          int,
    month_name            string,
    month_abbr            string,
    year_month            int,
    year_month_name       string,
    quarter_number        int,
    quarter_name          string,
    year                  int,
    fiscal_year           int,
    fiscal_quarter        int,
    fiscal_month          int,
    is_holiday            boolean     DEFAULT FALSE,
    holiday_name          string,
    is_pilot_period       boolean     DEFAULT FALSE,
    is_baseline_period    boolean     DEFAULT FALSE,
    CONSTRAINT dim_date_pk PRIMARY KEY (date_key) 
)
TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'supported')
;

/* 
 * TABLE: dim_geography 
 */

CREATE TABLE dim_geography
(
    geography_key                string         NOT NULL,
    geo_type                     string,
    fips_code                    string,
    geo_name_desc                string,
    county_name_desc             string,
    record_start_dttm            timestamp,
    record_end_dttm              timestamp,
    current_record_ind           smallint,
    record_created_dttm          timestamp,
    record_last_modified_dttm    timestamp,
    load_run_id                  bigint,
    CONSTRAINT pk_dim_geography PRIMARY KEY (geography_key) NOT ENFORCED 
)
;

/* 
 * TABLE: dim_measure 
 */

CREATE TABLE dim_measure
(
    measure_key                   string         NOT NULL,
    source_key                    string,
    measure_code                  string,
    measure_desc                  string,
    measure_long_desc             string,
    measure_category_type         string,
    measure_level_type            string,
    measure_tooltip_desc          string,
    base_unit_desc                string,
    unit_prefix_code              string,
    unit_suffix_code              string,
    nohss_indicator_nbr           string,
    nohss_indicator_group_type    string,
    nohss_indicator_desc          string,
    dashboard_trend_ind           boolean,
    dashboard_cross_ind           boolean,
    record_start_dttm             timestamp,
    record_end_dttm               timestamp,
    current_record_ind            smallint,
    record_created_dttm           timestamp,
    record_last_modified_dttm     timestamp,
    load_run_id                   bigint,
    CONSTRAINT pk_dim_measure PRIMARY KEY (measure_key) NOT ENFORCED 
)
;

/* 
 * TABLE: dim_postal 
 */

CREATE TABLE dim_postal
(
    postal_key                   string         NOT NULL,
    postal_code                  string,
    record_created_dttm          timestamp,
    record_last_modified_dttm    timestamp,
    load_run_id                  bigint,
    CONSTRAINT pk_dim_postal PRIMARY KEY (postal_key) NOT ENFORCED 
)
;

/* 
 * TABLE: dim_state 
 */

CREATE TABLE dim_state
(
    state_key                    string         NOT NULL,
    state_fips_nbr               int,
    state_name_desc              string,
    record_created_dttm          timestamp,
    record_last_modified_dttm    timestamp,
    load_run_id                  bigint,
    CONSTRAINT pk_dim_state PRIMARY KEY (state_key) NOT ENFORCED 
)
;

/* 
 * TABLE: dim_statistic_type 
 */

CREATE TABLE dim_statistic_type
(
    statistic_key                string         NOT NULL,
    statistic_type               string,
    calculation_method_desc      string,
    adjustment_desc              string,
    record_created_dttm          timestamp,
    record_last_modified_dttm    timestamp,
    load_run_id                  bigint,
    CONSTRAINT pk_dim_statistic_type PRIMARY KEY (statistic_key) NOT ENFORCED 
)
;

/* 
 * TABLE: dim_stratification 
 */

CREATE TABLE dim_stratification
(
    stratification_key              string         NOT NULL,
    stratification_category_type    string,
    stratification_level_desc       string,
    stratification_group_type       string,
    record_created_dttm             timestamp,
    record_last_modified_dttm       timestamp,
    load_run_id                     bigint,
    CONSTRAINT pk_dim_stratification PRIMARY KEY (stratification_key) NOT ENFORCED 
)
;

/* 
 * TABLE: dim_survey_period 
 */

CREATE TABLE dim_survey_period
(
    survey_period_key            string         NOT NULL,
    date_type                    string,
    year_nbr                     int,
    year_start_nbr               int,
    year_end_nbr                 int,
    approx_date                  date,
    duration_desc                string,
    record_created_dttm          timestamp,
    record_last_modified_dttm    timestamp,
    load_run_id                  bigint,
    CONSTRAINT pk_dim_survey_period PRIMARY KEY (survey_period_key) NOT ENFORCED 
)
;

/* 
 * TABLE: fact_oral_health_observation 
 */

CREATE TABLE fact_oral_health_observation
(
    observation_key              string              NOT NULL,
    measure_key                  string,
    geography_key                string,
    stratification_key           string,
    statistic_key                string,
    postal_key                   string              NOT NULL,
    state_key                    string              NOT NULL,
    survey_period_key            string              NOT NULL,
    date_key                     int                 NOT NULL,
    population_desc              string,
    value_nbr                    decimal(18, 6),
    ci_present_ind               boolean,
    ci_lower_nbr                 decimal(18, 6),
    ci_upper_nbr                 decimal(18, 6),
    proportion_nbr               decimal(18, 6),
    prop_lower_ci_nbr            decimal(18, 6),
    prop_upper_ci_nbr            decimal(18, 6),
    cell_size_unweighted_nbr     int,
    direction_desc               string,
    source_row_id_nbr            bigint,
    record_created_dttm          timestamp,
    record_last_modified_dttm    timestamp,
    load_run_id                  bigint,
    CONSTRAINT pk_fact_oral_health PRIMARY KEY (observation_key) NOT ENFORCED 
)
;

/* 
 * TABLE: dim_measure 
 */

ALTER TABLE dim_measure ADD CONSTRAINT fk_measure_source 
    FOREIGN KEY (source_key)
    REFERENCES dim_data_source  NOT ENFORCED
;


/* 
 * TABLE: fact_oral_health_observation 
 */

ALTER TABLE fact_oral_health_observation ADD CONSTRAINT Refdim_postal11 
    FOREIGN KEY (postal_key)
    REFERENCES dim_postal
;

ALTER TABLE fact_oral_health_observation ADD CONSTRAINT Refdim_state12 
    FOREIGN KEY (state_key)
    REFERENCES dim_state
;

ALTER TABLE fact_oral_health_observation ADD CONSTRAINT Refdim_survey_period14 
    FOREIGN KEY (survey_period_key)
    REFERENCES dim_survey_period
;

ALTER TABLE fact_oral_health_observation ADD CONSTRAINT Refdim_date15 
    FOREIGN KEY (date_key)
    REFERENCES dim_date
;

ALTER TABLE fact_oral_health_observation ADD CONSTRAINT fk_fact_geography 
    FOREIGN KEY (geography_key)
    REFERENCES dim_geography  NOT ENFORCED
;

ALTER TABLE fact_oral_health_observation ADD CONSTRAINT fk_fact_measure 
    FOREIGN KEY (measure_key)
    REFERENCES dim_measure  NOT ENFORCED
;

ALTER TABLE fact_oral_health_observation ADD CONSTRAINT fk_fact_statistic 
    FOREIGN KEY (statistic_key)
    REFERENCES dim_statistic_type  NOT ENFORCED
;

ALTER TABLE fact_oral_health_observation ADD CONSTRAINT fk_fact_stratification 
    FOREIGN KEY (stratification_key)
    REFERENCES dim_stratification  NOT ENFORCED
;