medicaid:
reference: "https://gitlab-int.rc.fas.harvard.edu/rse/francesca_dominici/dominici_data_pipelines/-/blob/master/medicaid/reports/schema.yml"
header: false
quoting: 3
index: "unless excluded"
schema: "medicaid"
schema.audit: "medicaid_audit"
description: "Data model for Medicaid"
tables:
beneficiaries:
create:
type: materialized view
from: cms.ps
group by:
- bene_id
columns:
- bene_id
- dob:
source: "MIN(el_dob)"
identifier: true
- dod:
source: "MAX(el_dod)"
identifier: true
- race_ethnicity_code:
description: "https://resdac.org/cms-data/variables/raceethnicity-msis"
source: "string_agg(distinct el_race_ethncy_cd, ',')"
identifier: true
- sex:
source: "string_agg(distinct el_sex_cd, ',')"
identifier: true
- duplicates:
source: "COUNT(distinct {identifiers})"
description: "number of duplicate records for the beneficiary"
- dob_latest:
source: |
CASE
WHEN MAX(el_dob) <> MIN(el_dob) THEN MAX(el_dob)
END
- dod_earliest:
source: |
CASE
WHEN MIN(el_dod) <> MAX(el_dod) THEN MIN(el_dod)
END
children:
monthly:
create:
type: materialized view
from: cms.ps
indices:
primary:
columns:
- bene_id
- state
- year
- max_elg_cd
columns:
- bene_id
- msis_id
- year:
source: max_yr_dt
- state:
source: state_cd
- month:
source: "unnest(ARRAY[1,2,3,4,5,6,7,8,9,10,11,12])"
- max_elg_cd:
source: |
unnest(ARRAY[
max_elg_cd_mo_1,
max_elg_cd_mo_2,
max_elg_cd_mo_3,
max_elg_cd_mo_4,
max_elg_cd_mo_5,
max_elg_cd_mo_6,
max_elg_cd_mo_7,
max_elg_cd_mo_8,
max_elg_cd_mo_9,
max_elg_cd_mo_10,
max_elg_cd_mo_11,
max_elg_cd_mo_12
])
- el_mdcr_ben:
source: |
unnest(ARRAY[
el_mdcr_ben_mo_1,
el_mdcr_ben_mo_2,
el_mdcr_ben_mo_3,
el_mdcr_ben_mo_4,
el_mdcr_ben_mo_5,
el_mdcr_ben_mo_6,
el_mdcr_ben_mo_7,
el_mdcr_ben_mo_8,
el_mdcr_ben_mo_9,
el_mdcr_ben_mo_10,
el_mdcr_ben_mo_11,
el_mdcr_ben_mo_12
])
- el_mdcr_dual:
source: |
unnest(ARRAY[
el_mdcr_dual_mo_1,
el_mdcr_dual_mo_2,
el_mdcr_dual_mo_3,
el_mdcr_dual_mo_4,
el_mdcr_dual_mo_5,
el_mdcr_dual_mo_6,
el_mdcr_dual_mo_7,
el_mdcr_dual_mo_8,
el_mdcr_dual_mo_9,
el_mdcr_dual_mo_10,
el_mdcr_dual_mo_11,
el_mdcr_dual_mo_12
])
- mc_combo:
source: |
unnest(ARRAY[
mc_combo_mo_1,
mc_combo_mo_2,
mc_combo_mo_3,
mc_combo_mo_4,
mc_combo_mo_5,
mc_combo_mo_6,
mc_combo_mo_7,
mc_combo_mo_8,
mc_combo_mo_9,
mc_combo_mo_10,
mc_combo_mo_11,
mc_combo_mo_12
])
- el_rstrct_bnft_flg:
source: |
unnest(ARRAY[
el_rstrct_bnft_flg_1,
el_rstrct_bnft_flg_2,
el_rstrct_bnft_flg_3,
el_rstrct_bnft_flg_4,
el_rstrct_bnft_flg_5,
el_rstrct_bnft_flg_6,
el_rstrct_bnft_flg_7,
el_rstrct_bnft_flg_8,
el_rstrct_bnft_flg_9,
el_rstrct_bnft_flg_10,
el_rstrct_bnft_flg_11,
el_rstrct_bnft_flg_12
])
enrollments:
create:
type: materialized view
#type: view
from: cms.ps
group by:
- bene_id
- max_yr_dt
- state_cd
columns:
- year:
source: max_yr_dt
- state:
source: state_cd
- state_iso:
source: "('US-' || state_cd) "
- residence_county:
source: "MAX(el_rsdnc_cnty_cd_ltst)"
- residence_counties:
source: "string_agg(distinct el_rsdnc_cnty_cd_ltst, ',')"
- fips5:
source: "(SELECT fips2 FROM public.us_states as s WHERE s.state_id = ps.state_cd) || MAX(el_rsdnc_cnty_cd_ltst)"
- zip9:
source: "MAX(el_rsdnc_zip_cd_ltst)"
# Manipulations
# ALTER TABLE medicaid.enrollments RENAME column zip to zip9;
# ALTER TABLE medicaid.enrollments ADD column zip int
# GENERATED ALWAYS AS (
# CASE WHEN year < 2005 THEN
# SUBSTRING(zip9 FROM 1 FOR 5)::int
# ELSE
# SUBSTRING(zip9 FROM 5)::int
# END
# ) STORED;
- zips:
source: "string_agg(distinct el_rsdnc_zip_cd_ltst, ',')"
- months_eligible:
source:
select: "COUNT(distinct month)"
from: monthly
where: "max_elg_cd != '00'"
- months_dual_eligible:
source:
select: "COUNT(distinct month)"
from: monthly
where: "max_elg_cd != '00' AND el_mdcr_ben > 0"
- state_count:
source: |
(
SELECT COUNT(distinct state_cd)
FROM cms.ps AS ps2
WHERE
cms.ps.bene_id = ps2.bene_id
AND cms.ps.max_yr_dt = ps2.max_yr_dt
)
- died:
source: |
CASE EXTRACT (YEAR from MAX(el_dod))
WHEN max_yr_dt THEN TRUE
ELSE FALSE
END
_eligibility:
create:
type: view
#type: view
from: medicaid.monthly
group by:
- bene_id
- year
- state
- month
columns:
- year
- state
- month
- eligibility_code:
description: "https://resdac.org/cms-data/variables/medicare-medicaid-dual-eligibility-code-january"
source: MAX(max_elg_cd)
- managed_care_code:
description: "https://resdac.org/cms-data/variables/managed-care-combinations-jan"
source: MAX(mc_combo)
- medicare_eligibility:
source: MAX(el_mdcr_ben)
- restrictions:
description: "https://resdac.org/cms-data/variables/restricted-benefits-jan"
source: MAX(el_rstrct_bnft_flg)
eligibility:
create:
type: table
select: "*"
from: _eligibility
primary_key:
- bene_id
- year
- state
- month
indices:
eligibility_ys_idx:
columns:
- state
- year
children:
admissions:
invalid.records:
action: "INSERT"
#action: "IGNORE"
target:
schema: $schema.audit
description: "https://resdac.org/cms-data/files/max-ip/data-documentation"
columns:
- state:
type: "VARCHAR(2)"
source: "STATE_CD"
- year:
type: "INT"
source:
type: "generated"
code: "GENERATED ALWAYS AS (EXTRACT (YEAR FROM admission_date)) STORED"
- month:
type: "INT"
source:
type: "generated"
code: "GENERATED ALWAYS AS (EXTRACT (MONTH FROM admission_date)) STORED"
- admission_date:
type: "DATE"
source:
type: "compute"
columns: # parameters refer to column names in the table, while columns refer to column names in the source
- "ADMSN_DT"
code: "datetime.strptime({1}, '%Y%m%d').date()"
- discharge_date:
type: "DATE"
source:
type: "compute"
columns: # parameters refer to column names in the table, while columns refer to column names in the source
- "SRVC_END_DT"
code: "datetime.strptime({1}, '%Y%m%d').date()"
- diagnosis:
type: "VARCHAR(12)[10]"
source: "DIAG_CD_*"
- FILE:
description: RESDAC original file name
index:
required_before_loading_data: true
source:
type: file
type: VARCHAR(128)
primary_key:
- bene_id
- admission_date
- discharge_date
indices:
adm_ys_idx:
columns:
- state
- year
source_columns:
- BENE_ID
- MSIS_ID
- STATE_CD
- YR_NUM
- EL_DOB
- EL_SEX_CD
- EL_RACE_ETHNCY_CD
- RACE_CODE_1
- RACE_CODE_2
- RACE_CODE_3
- RACE_CODE_4
- RACE_CODE_5
- ETHNICITY_CODE
- EL_SS_ELGBLTY_CD_LTST
- EL_SS_ELGBLTY_CD_MO
- EL_MAX_ELGBLTY_CD_LTST
- EL_MAX_ELGBLTY_CD_MO
- EL_MDCR_ANN_XOVR_OLD
- MSNG_ELG_DATA
- EL_MDCR_XOVR_CLM_BSD_CD
- EL_MDCR_ANN_XOVR_99
- MSIS_TOS
- MSIS_TOP
- MAX_TOS
- PRVDR_ID_NMBR
- NPI
- TAXONOMY
- TYPE_CLM_CD
- ADJUST_CD
- PHP_TYPE
- PHP_ID
- MDCD_PYMT_AMT
- TP_PYMT_AMT
- PYMT_DT
- CHRG_AMT
- PHP_VAL
- MDCR_COINSUR_PYMT_AMT
- MDCR_DED_PYMT_AMT
- ADMSN_DT
- SRVC_BGN_DT
- SRVC_END_DT
- DIAG_CD_1
- DIAG_CD_2
- DIAG_CD_3
- DIAG_CD_4
- DIAG_CD_5
- DIAG_CD_6
- DIAG_CD_7
- DIAG_CD_8
- DIAG_CD_9
- PRNCPL_PRCDR_DT
- PRCDR_CD_SYS_1
- PRCDR_CD_1
- PRCDR_CD_SYS_2
- PRCDR_CD_2
- PRCDR_CD_SYS_3
- PRCDR_CD_3
- PRCDR_CD_SYS_4
- PRCDR_CD_4
- PRCDR_CD_SYS_5
- PRCDR_CD_5
- PRCDR_CD_SYS_6
- PRCDR_CD_6
- RCPNT_DLVRY_CD
- MDCD_CVRD_IP_DAYS
- PATIENT_STATUS_CD
- DRG_REL_GROUP_IND
- DRG_REL_GROUP
- UB_92_REV_CD_GP_1
- UB_92_REV_CD_CHGS_1
- UB_92_REV_CD_UNITS_1
- UB_92_REV_CD_GP_2
- UB_92_REV_CD_CHGS_2
- UB_92_REV_CD_UNITS_2
- UB_92_REV_CD_GP_3
- UB_92_REV_CD_CHGS_3
- UB_92_REV_CD_UNITS_3
- UB_92_REV_CD_GP_4
- UB_92_REV_CD_CHGS_4
- UB_92_REV_CD_UNITS_4
- UB_92_REV_CD_GP_5
- UB_92_REV_CD_CHGS_5
- UB_92_REV_CD_UNITS_5
- UB_92_REV_CD_GP_6
- UB_92_REV_CD_CHGS_6
- UB_92_REV_CD_UNITS_6
- UB_92_REV_CD_GP_7
- UB_92_REV_CD_CHGS_7
- UB_92_REV_CD_UNITS_7
- UB_92_REV_CD_GP_8
- UB_92_REV_CD_CHGS_8
- UB_92_REV_CD_UNITS_8
- UB_92_REV_CD_GP_9
- UB_92_REV_CD_CHGS_9
- UB_92_REV_CD_UNITS_9
- UB_92_REV_CD_GP_10
- UB_92_REV_CD_CHGS_10
- UB_92_REV_CD_UNITS_10
- UB_92_REV_CD_GP_11
- UB_92_REV_CD_CHGS_11
- UB_92_REV_CD_UNITS_11
- UB_92_REV_CD_GP_12
- UB_92_REV_CD_CHGS_12
- UB_92_REV_CD_UNITS_12
- UB_92_REV_CD_GP_13
- UB_92_REV_CD_CHGS_13
- UB_92_REV_CD_UNITS_13
- UB_92_REV_CD_GP_14
- UB_92_REV_CD_CHGS_14
- UB_92_REV_CD_UNITS_14
- UB_92_REV_CD_GP_15
- UB_92_REV_CD_CHGS_15
- UB_92_REV_CD_UNITS_15
- UB_92_REV_CD_GP_16
- UB_92_REV_CD_CHGS_16
- UB_92_REV_CD_UNITS_16
- UB_92_REV_CD_GP_17
- UB_92_REV_CD_CHGS_17
- UB_92_REV_CD_UNITS_17
- UB_92_REV_CD_GP_18
- UB_92_REV_CD_CHGS_18
- UB_92_REV_CD_UNITS_18
- UB_92_REV_CD_GP_19
- UB_92_REV_CD_CHGS_19
- UB_92_REV_CD_UNITS_19
- UB_92_REV_CD_GP_20
- UB_92_REV_CD_CHGS_20
- UB_92_REV_CD_UNITS_20
- UB_92_REV_CD_GP_21
- UB_92_REV_CD_CHGS_21
- UB_92_REV_CD_UNITS_21
- UB_92_REV_CD_GP_22
- UB_92_REV_CD_CHGS_22
- UB_92_REV_CD_UNITS_22
- UB_92_REV_CD_GP_23
- UB_92_REV_CD_CHGS_23
- UB_92_REV_CD_UNITS_23
# admissions:
# invalid.records:
# action: "INSERT"
# #action: "IGNORE"
# target:
# schema: $schema.audit
# description: "https://resdac.org/cms-data/files/max-ip/data-documentation"
# columns:
# - bene_id:
# description: Encrypted 723 CCW Beneficiary ID
# index: true
# type: VARCHAR(15)
# source: BENE_ID
# - state:
# description: State
# index: true
# type: VARCHAR(2)
# source: STATE_CD
# - year:
# type: "INT"
# source:
# type: "generated"
# code: "GENERATED ALWAYS AS (EXTRACT (YEAR FROM admission_date)) STORED"
# - month:
# type: "INT"
# source:
# type: "generated"
# code: "GENERATED ALWAYS AS (EXTRACT (MONTH FROM admission_date)) STORED"
# - admission_date:
# type: "DATE"
# source:
# type: "compute"
# columns: # parameters refer to column names in the table, while columns refer to column names in the source
# - "ADMSN_DT"
# code: "datetime.strptime({1}, '%Y%m%d').date()"
# - discharge_date:
# type: "DATE"
# source:
# type: "compute"
# columns: # parameters refer to column names in the table, while columns refer to column names in the source
# - "SRVC_END_DT"
# code: "datetime.strptime({1}, '%Y%m%d').date()"
# - diagnosis:
# type: "VARCHAR(12)[10]"
# source: "DIAG_CD_*"
# - FILE:
# description: RESDAC original file name
# index:
# required_before_loading_data: true
# source:
# type: file
# type: VARCHAR(128)
# primary_key:
# - bene_id
# - admission_date
# - discharge_date
# indices:
# foreign:
# columns:
# - bene_id
# - state
# - year
# - month