Medicare.yaml

medicare:
  reference: ""
  header: false
  quoting: 3
  index: "unless excluded"
  schema: "medicare"
  schema.audit: "medicare_audit"
  description: "Data model for Medicare"
  tables:
    mbsf_d:
      description: |
        Data from mbsf_d* files that is used to create beneficiary and
        enrollment tables
      create:
        type: materialized view
        from:
          - cms.mbsf_*d*
      columns:
        - bene_id
        - year
        - dual_mo:
            type: INT
            cast:
              '*':  '{column_name}::INT'
        - dual_indicators:
            type: CHAR(1)[12]
            optional: true
            m: ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
            source:
              - dual_$m
            cast:
              'character varying': string_to_array({column_name}, NULL)
        - file
        - record
      indices:
        d_sy_idx:
          columns:
            - year
            - bene_id

    ps:
      description: |
        Patient Summary view. Combines multiple patient summary (aka MBSF)
        files into a single view, transforming columns to the same type
      create:
        type: view
        from:
          - cms.mbsf_ab*
          - cms.mcr_bene_*
#        exclude:
#          - mbsf_ab_2015
      columns:
        - bene_id
        - file
        - record
        - year:
            source: '(CASE WHEN "year"::int < 20 THEN (2000 + "year"::int) WHEN (20 < "year"::int AND "year"::int < 100) THEN (1900 + "year"::int) WHEN "year" IS NULL THEN 2000 ELSE "year"::int END)'
        - state:
            source: '(SELECT state_id FROM public.us_states as s WHERE s.ssa2 = "state")'
        - ssa2:
            source: state
            description: Social Security Administration (SSA) two digit code for state
            reference: https://www.nber.org/research/data/ssa-federal-information-processing-series-fips-state-and-county-crosswalk
        - fips2:
            source: '(SELECT fips2 FROM public.us_states as s WHERE s.ssa2 = "state")'
            description: 2 digit state FIPS, see https://en.wikipedia.org/wiki/Federal_Information_Processing_Standard_state_code
            reference:  https://www.census.gov/library/reference/code-lists/ansi.html#states
        - zip:
            type: INT
            description: US Postal ZIP code, the first 5 digits of it
            cast:
              numeric: "(CASE WHEN {column_name} < 100000 THEN {column_name}::int ELSE ({column_name}/10000)::INT END)"
              "character varying": "SUBSTRING({column_name} FROM 1 FOR 5)::INT"
              integer: "{column_name}::INT"
        - zip4:
            type: INT
            description: Additional 4 digits for US zip 5+4 code, when provided
            source: zip
            cast:
              numeric: "(CASE WHEN {column_name} < 100000 THEN NULL ELSE ({column_name}%10000)::INT END)"
              "character varying": "(CASE WHEN LENGTH({column_name}) < 6 THEN NULL ELSE SUBSTRING({column_name} FROM 6 FOR 4)::INT END)"
              integer: "{column_name}::INT"
        - dob:
            type: date
            description: Date of birth
            cast:
              "character varying": "public.parse_date({column_name})"
              numeric: "to_date(to_char({column_name}, '00000000'), 'YYYYMMDD')"
            source:
              - dob
              - bene_dob
              - bene_birth_dt
        - age:
            type: INT
            description: Age on January 1 of a given year, if provided
            optional: true
            source:
              - age
              - bene_age_at_beg_ref_yr
            cast:
              '*': "{column_name}::INT"
        - dod:
            type: date
            description: Date of beneficiary death, if available
            cast:
              "character varying": "(CASE WHEN {column_name} = '00000000' THEN NULL ELSE public.parse_date({column_name}) END)"
              numeric: "(CASE WHEN {column_name} < 19000000 THEN NULL ELSE to_date(to_char({column_name}, '00000000'), 'YYYYMMDD') END)"
            source:
              - death_dt
              - bef_dod
              - bene_dod
              - bene_death_dt
        - ssa3:
            optional: true
            description: Social Security Administration (SSA) three digit code for county
            reference: https://www.nber.org/research/data/ssa-federal-information-processing-series-fips-state-and-county-crosswalk
            source:
              - cnty_cd
              - bene_county_cd
              - ssa_county
        - sex:
            reference: https://resdac.org/cms-data/variables/sex-beneficiary-0
            type: VARCHAR
            cast:
              '*': "{column_name}::VARCHAR"
            source:
              - sex
              - bene_sex_ident_cd
        - race:
            reference: https://resdac.org/cms-data/variables/beneficiary-race-code-ffs
            type: VARCHAR
            cast:
              '*': "{column_name}::VARCHAR"
            source:
              - bene_race_cd
              - race
        - dod_ndi:
            description: This variable is the date of death as reported on the Beneficiary’s death certificate.
            reference: https://resdac.org/cms-data/variables/ndi-date-deathmbsf
            optional: true
            source:  ndi_death_dt
            type: DATE
        - race_rti:
            description: Research Triangle Institute (RTI) Race Code
            reference: https://resdac.org/cms-data/variables/research-triangle-institute-rti-race-code
            optional: true
            source: rti_race_cd
        - hmo_indicators:
            type: CHAR(1)[12]
            optional: false
            description: Array of indicators if the beneficiary was enrolled in HMO during a given month
            reference: https://resdac.org/cms-data/variables/hmo-indicator
            m: ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
            source:
              - hmoind
              - hmoind$m
              - hmoind12
              - bene_hmo_ind_$m
            cast:
              'character varying': string_to_array({column_name}, NULL)
        - hmo_cvg_count:
            type: INT
            optional: false
            description: Number of months during a given year, the beneficiary received their Part A and Part B benefits through a managed care plan
            reference: https://requests.resdac.org/cms-data/variables/hmo-coverage-count
            source:
              - hmo_mo
              - hmocovg
              - bene_hmo_cvrage_tot_mons
            cast:
              '*': "CASE WHEN {column_name} IS NULL THEN 0 ELSE {column_name}::INT END"
        - buyin_indicators:
            type: CHAR(1)[12]
            optional: true
            description: |
              Array of indicators if the beneficiary’s state of residence paid his/her monthly
              premium for Part B coverage (and Part A if necessary). State Medicaid programs
              can pay those premiums for certain dual eligibles; this action is called
              “buying in” and so this variable is the “buy-in code.”
            reference: https://resdac.org/cms-data/variables/medicare-entitlementbuy-indicator
            m: ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
            source:
              - medicare_buy_in
              - buyin$m
              - buyin12
              - bene_mdcr_entlmt_buyin_ind_$m
            cast:
              'character varying': string_to_array({column_name}, NULL)
        - buyin_cvg_count:
            type: INT
            optional: false
            description: Number of months during a given year, during the year when the beneficiary premium was paid by the state. 
            reference: https://resdac.org/cms-data/variables/state-buy-coverage-count
            source:
              - buyin_mo
              - buycovg
              - state_buy_in_mo
              - bene_state_buyin_tot_mons
              - dual
            cast:
              '*': "CASE WHEN {column_name} IS NULL THEN 0 WHEN year = 2015 AND {column_name} = '1' THEN 12 ELSE {column_name}::INT END"

        - orec:
            type: INT
            optional: true # no data for 2015
            source:
               - orec
               - orig_ent
               - bene_entlmt_rsn_orig
            cast:
              '*': "{column_name}::INT"
        - curec:
            type: INT
            optional: true
            source:
               - cur_ent
               - bene_entlmt_rsn_curr
            cast:
              '*': "{column_name}::INT"

    ip:
      description: |
        Inpatient Adminissions view. Combines multiple inpatient admissions
        files (aka MedPar) into a single view, transforming columns
        to the same type
      create:
        type: view
        from:
          - cms.medpar_*
          - cms.mcr_ip_*
      columns:
        - bene_id
        - file
        - record
        - year:
            type: INT
            source: '(CASE WHEN "year"::int < 20 THEN (2000 + "year"::int) WHEN (20 < "year"::int AND "year"::int < 100) THEN (1900 + "year"::int) WHEN "year" IS NULL THEN 2000 ELSE "year"::int END)'
        - state:
            source: '(SELECT state_id FROM public.us_states as s WHERE s.ssa2 = "state")'
        - ssa2:
            source: state
        - fips2:
            source: '(SELECT fips2 FROM public.us_states as s WHERE s.ssa2 = "state")'
        - zip:
            type: INT
            cast:
              numeric: "(CASE WHEN {column_name} < 100000 THEN {column_name}::int ELSE ({column_name}/10000)::INT END)"
              "character varying": "SUBSTRING({column_name} FROM 1 FOR 5)::INT"
        - zip4:
            type: INT
            source: zip
            cast:
              numeric: "(CASE WHEN {column_name} < 100000 THEN NULL ELSE ({column_name}%10000)::INT END)"
              "character varying": "(CASE WHEN LENGTH({column_name}) < 6 THEN NULL ELSE SUBSTRING({column_name} FROM 6 FOR 4)::INT END)"
        - admission_date_raw:
            type: VARCHAR
            cast:
              numeric: "TRIM(to_char({column_name}, '999999999'))"
              '*': "{column_name}::VARCHAR"
            source:
              - medpar_admsn_dt
              - admsn_dt
              - admsndt
        - admission_date:
            type: DATE
            cast:
              "character varying": "public.parse_date({column_name})"
              numeric: "public.parse_date({column_name})"
            source:
              - medpar_admsn_dt
              - admsn_dt
              - admsndt

        - discharge_date_raw:
            type: VARCHAR
            cast:
              numeric: "TRIM(to_char({column_name}, '999999999'))"
              '*': "{column_name}::VARCHAR"
            source:
              - medpar_dschrg_dt
              - dschrg_dt
              - dschrgdt
        - discharge_date:
            type: DATE
            cast:
              "character varying": "public.parse_date({column_name})"
              numeric: "public.parse_date({column_name})"
            source:
              - medpar_dschrg_dt
              - dschrg_dt
              - dschrgdt

        - adm_day_of_week:
            type: INT
            cast:
              "character varying": "{column_name}::INT"
              numeric: "{column_name}::INT"
            source:
              - medpar_admsn_day_cd
              - admsn_day_cd
              - admsnday

        - src_admsn_cd:
            type: CHAR(1)
            source:
              - src_ip_admsn_cd
              - src_adms
              - medpar_src_ip_admsn_cd
            cast:
              '*': "{column_name}::VARCHAR"

        - dschrg_dstntn_cd:
            type: CHAR(2)
            source:
              - dschrg_dstntn_cd
              - dstntncd
              - medpar_dschrg_dstntn_cd
            cast:
              numeric: "TRIM(to_char({column_name}, '99'))"
              '*': "{column_name}::VARCHAR"

        - dschrgcd:
            type: CHAR(1)
            source:
              - dschrgcd
              - bene_dschrg_stus_cd
              - medpar_bene_dschrg_stus_cd

        - diag[$n=1:25]:
            type: varchar
            optional: true
            source:
              - medpar_dgns_cd_$n
              - dgns_cd0$n
              - dgns_cd$n
              - dgns_$n_cd
              - dgnscd$n

        # Expenditure variables
        - los_day_cnt:
            reference: "https://resdac.org/cms-data/variables/days-beneficiarys-stay-hospitalsnf"
            description: The count in days of the total length of a beneficiary's stay in a hospital
            type: INT
            cast:
              "*": "{column_name}::INT"
            source:
              - los_day_cnt
              - loscnt
              - medpar_los_day_cnt

        - drg_price_amt:
            reference: "https://resdac.org/cms-data/variables/drg-price-amount"
            description: |
              The amount (called the 'DRG price' for purposes of MEDPAR analysis) 
              that would have been paid if no deductibles, coinsurance, primary payers, or outliers 
              were involved (rounded to whole dollars).
            type: NUMERIC
            source:
              - drg_price_amt
              - drgprice
              - medpar_drg_price_amt

        - drg_outlier_pmt_amt:
            reference: "https://resdac.org/cms-data/variables/drg-outlier-approved-payment-amount"
            description: |
              The amount of additional payment (rounded to whole dollars) approved due 
              to an outlier situation over the DRG allowance for the stay.
            type: NUMERIC
            source:
              - drg_outlier_pmt_amt
              - outlramt
              - medpar_drg_outlier_pmt_amt

        - pass_thru_amt:
            reference: "https://resdac.org/cms-data/variables/pass-thru-diem-amount-stay"
            description: |
              The total of all claim pass through per diem amounts (rounded to whole dollars) for the stay.
            type: NUMERIC
            source:
              - pass_thru_amt
              - passthru
              - medpar_pass_thru_amt

        - mdcr_pmt_amt:
            reference: "https://resdac.org/cms-data/variables/total-medicare-payment-amount"
            description: |
              Amount of payment made from the Medicare trust fund for the services covered by the claim record.
              For hospital services, this amount does not include the claim pass-through per diem payments 
              made by Medicare.
            type: NUMERIC
            source:
              - mdcr_pmt_amt
              - pmt_amt
              - medpar_mdcr_pmt_amt

    _ps:
      description: internal intermediate view adding some data conversions
      create:
        type: materialized view
        select: |
          *,
          CASE WHEN ssa3 is NOT NULL THEN
            CASE WHEN year > 2010 THEN
                (SELECT fips3 from public.ssa as s where ps.ssa3 = s.ssa3 and ps.year = s.year and ps.fips2 = s.fips2 ORDER BY 1 DESC LIMIT 1)
              ELSE
                (SELECT fips3 from public.ssa as s where ps.ssa3 = s.ssa3 and 2003 = s.year and ps.fips2 = s.fips2 ORDER BY 1 DESC LIMIT 1)
            END
          ELSE
            (SELECT public.zip_to_fips3(year, zip))
          END AS fips3,
          CASE WHEN ssa3 is NOT NULL THEN
              False
            ELSE
              (SELECT NOT public.is_zip_to_fips_exact(year, zip))
          END AS fips3_is_approximated,
          CASE WHEN year > 2010 THEN (SELECT string_agg(distinct fips3::varchar, ',') from public.ssa as s where ps.ssa3 = s.ssa3 and ps.year = s.year and ps.fips2 = s.fips2)
              ELSE (SELECT string_agg(distinct fips3::varchar, ',') from public.ssa as s where ps.ssa3 = s.ssa3 and 2003 = s.year and ps.fips2 = s.fips2) END AS fips3_list,
          CASE WHEN age is not NULL THEN (year - age) ELSE NULL END as yob
        from: ps
      indices:
        ps_to_enrl_idx:
          columns:
            - bene_id
            - year
            - state
        pelg_yo_idx:
          columns:
            - year
            - orec
        pelg_so_idx:
          columns:
            - state
            - orec
        pelg_yso_idx:
          columns:
            - year
            - state
            - orec

    _beneficiaries:
      description: internal intermediate view adding some data conversions
      create:
        type: view
        from: medicare._ps
        group by:
          - bene_id
      columns:
        - bene_id
        - dob:
            source: "MIN(dob)"
            identifier: true
        - yob:
            source: "MIN(yob)"
        - dod:
            source: "MAX(dod)"
        - first_enrollment_year:
            source: "MIN(year)"
        - last_enrollment_year:
            source: "MAX(year)"
        - all_enrollment_years:
            type: INT[]
            source: "ARRAY_AGG(DISTINCT year ORDER BY year)"
        - race:
            reference: "https://resdac.org/cms-data/variables/raceethnicity-msis"
            source: "string_agg(distinct race, ',')"
            identifier: true
        - race_rti:
            reference: "https://resdac.org/cms-data/variables/research-triangle-institute-rti-race-code"
            source: "string_agg(distinct race_rti, ',')"
        - sex:
            source: "string_agg(distinct sex, ',')"
            identifier: true
        - orec:
            source: MAX(orec)
        - discrepancies:
            source: >
              (
                COUNT(distinct {identifiers}) - 1 +
                CASE
                  (COUNT(DISTINCT dod) FILTER (WHERE dod is not null))
                  WHEN 0 THEN 0
                  ELSE COUNT(DISTINCT dod) FILTER (WHERE dod is not null) - 1
                END
              )
            description: "Number of possible discrepancies for the beneficiary, .i.e., records that use alternative DOB, race, ethnicity or DOD"
        - dob_latest:
            source: |
              CASE
                WHEN MAX(dob) <> MIN(dob) THEN  MAX(dob)
              END
        - yob_latest:
            source: |
              CASE
                WHEN MAX(yob) <> MIN(yob) THEN  MAX(yob)
              END
        - dod_earliest:
            source: |
              CASE
                WHEN MIN(dod) <> MAX(dod) THEN MIN(dod)
              END
    _enrollments:
      description: |
        Internal intermediate view of beneficiary enrollments into
        Medicare program, by year and state of enrollment
      create:
        type: view
        from: medicare._ps
        group by:
          - bene_id
          - year
          - state
      columns:
        - bene_id
        - year
        - state
        - age:
            source: MAX(age)
        - age_min:
            source: |
              CASE
                WHEN MIN(age) <> MAX(age) THEN MIN(age)
              END
        - orec:
            source: MAX(orec)
        - curec:
            source: MAX(curec)
        - state_iso:
            type: "char(5)"
            source: "('US-' || state) "
        - fips2:
            source: MAX(fips2)
        - fips3:
            source: MAX(fips3)
        - fips3_is_approximated:
            source: every(fips3_is_approximated)
        - fips3_valdiated:
            source: public.validate_zip_fips(MAX(zip), MAX(fips2), MAX(fips3))
        - ssa2:
            source: MAX(ssa2)
        - ssa3:
            source: MAX(ssa3)
        - ssa2_list:
            source: "string_agg(distinct ssa2, ',')"
        - ssa3_list:
            source: "string_agg(distinct ssa3, ',')"
        - residence_county:
            source: MAX(fips3)
        - residence_counties:
            source: "string_agg(distinct fips3_list, ',')"
        - fips5:
            source: "(MAX(fips2) || MAX(fips3))"
        - zip:
            source: "MAX(zip)"
        - zips:
            source: "string_agg(distinct zip::varchar, ',')"
        - hmo_indicators:
            source: "MAX(hmo_indicators)"
        - hmo_cvg_count:
            source: "MAX(hmo_cvg_count)"
        - buyin_indicators:
            source: "MAX(buyin_indicators)"
        - buyin_cvg_count:
            source: "MAX(buyin_cvg_count)"
        - dual_indicators:
            source: "(SELECT MAX(dual_indicators) FROM medicare.mbsf_d AS d WHERE medicare._ps.year = d.year and medicare._ps.bene_id = d.bene_id)"
            requires: medicare.mbsf_d
        - dual_cvg_count:
            source: |
              (CASE
                 WHEN EXISTS (SELECT * FROM medicare.mbsf_d AS d WHERE medicare._ps.year = d.year and medicare._ps.bene_id = d.bene_id)
                 THEN
                     COALESCE((SELECT MAX(dual_mo) FROM medicare.mbsf_d AS d WHERE medicare._ps.year = d.year and medicare._ps.bene_id = d.bene_id), 0)
                 ELSE NULL
              END)
            requires: medicare.mbsf_d
        - state_count:
            source: |
              (
                SELECT COUNT(distinct state)
                FROM medicare._ps AS ps2
                WHERE
                  _ps.bene_id = ps2.bene_id
                  AND _ps.year = ps2.year
              )
            requires: medicare._ps
        - died:
            source: |
              CASE EXTRACT (YEAR from MAX(dod))
                WHEN year THEN TRUE
                ELSE FALSE
              END
        - file:
            source: "string_agg(distinct FILE, ',')"
        - record:
            source: "string_agg(distinct RECORD::VARCHAR, ',')"
    beneficiaries:
      description: |
        Cleaned up and unified Medicare beneficiaries table,
        indexed and optimized for efficient queries
      create:
        type: table
        select: "*"
        from: _beneficiaries
      columns:
        - number_of_gap_years:
            type: INT
            source:
              type: generated
              code: "GENERATED ALWAYS AS (last_enrollment_year - first_enrollment_year + 1 - CARDINALITY(all_enrollment_years)) STORED"
        - bene:
            type: HLL_HASHVAL
            description: |
              HLL hash (see https://en.wikipedia.org/wiki/HyperLogLog).
              Can be used to calculate number of beneficiaries
              satisfying a certain given condition
              Use #(hll_add_agg(bene)) or hll_cardinality(hll_add_agg(bene)) aggregator
            reference: https://github.com/citusdata/postgresql-hll/blob/master/REFERENCE.md#basic-operational-functions
            source:
              type: "generated"
              code: "GENERATED ALWAYS AS (hll_hash_text(bene_id)) STORED"
      primary_key:
        - bene_id
      indices:
        bene_rsd_idx:
          columns:
            - race
            - sex
            - discrepancies
      children:
        enrollments:
          description: |
            Cleaned up and unified Medicare beneficiaries enrollments table,
            by state and year. This table is indexed and optimized
            for efficient queries
          create:
            type: table
            select: "*"
            from: _enrollments
            populate: true
          columns:
            - hmo:
                type: boolean
                source:
                  type: generated
                  code: "GENERATED ALWAYS AS (CASE WHEN hmo_cvg_count IS NULL THEN NULL WHEN hmo_cvg_count > 0 THEN TRUE ELSE FALSE END) STORED"
            - buyin:
                type: boolean
                source:
                  type: generated
                  code: "GENERATED ALWAYS AS (CASE WHEN buyin_cvg_count IS NULL THEN NULL WHEN buyin_cvg_count > 0 THEN TRUE ELSE FALSE END) STORED"
            - dual:
                type: boolean
                source:
                  type: generated
                  code: "GENERATED ALWAYS AS (CASE WHEN dual_cvg_count IS NULL THEN NULL WHEN dual_cvg_count > 0 THEN TRUE ELSE FALSE END) STORED"
            - bene:
                type: HLL_HASHVAL
                description: |
                  HLL hash (see https://en.wikipedia.org/wiki/HyperLogLog).
                  Can be used to calculate number of beneficiaries
                  satisfying a certain given condition
                  Use #(hll_add_agg(bene)) or hll_cardinality(hll_add_agg(bene)) aggregator
                reference: https://github.com/citusdata/postgresql-hll/blob/master/REFERENCE.md#basic-operational-functions
                source:
                  type: "generated"
                  code: "GENERATED ALWAYS AS (hll_hash_text(bene_id)) STORED"
          primary_key:
            - bene_id
            - year
            - state
          indices:
            bsy_idx:
              columns:
                - bene_id
                - state
                - year
            sy_idx:
              columns:
                - state
                - year
            sy_iso_idx:
              columns:
                - state_iso
                - year
            fips_idx:
              columns:
                - fips2
                - fips3
            zc_idx:
              columns:
                - zip
                - fips5
            scz_idx:
              columns:
                - state
                - fips3
                - zip
          children:
            admissions:
              description: |
                Medicare inpatient admissions
              invalid.records:
                action: "INSERT"
                target:
                  schema: $schema.audit
              create:
                type: table
                select: |
                  bene_id,
                  file,
                  record,
                  year,
                  state,
                  ssa2,
                  fips2,
                  zip,
                  zip4,
                  admission_date_raw,
                  discharge_date_raw,
                  admission_date,
                  discharge_date,
                  adm_day_of_week,
                  dschrgcd,
                  dschrg_dstntn_cd,
                  src_admsn_cd,
                  btrim(diag1) As primary_diagnosis,
                  array_remove(ARRAY[
                  btrim(btrim(diag1)),
                  btrim(diag2),
                  btrim(diag3),
                  btrim(diag4),
                  btrim(diag5),
                  btrim(diag6),
                  btrim(diag7),
                  btrim(diag8),
                  btrim(diag9),
                  btrim(diag10),
                  btrim(diag11),
                  btrim(diag12),
                  btrim(diag13),
                  btrim(diag14),
                  btrim(diag15),
                  btrim(diag16),
                  btrim(diag17),
                  btrim(diag18),
                  btrim(diag19),
                  btrim(diag20),
                  btrim(diag21),
                  btrim(diag22),
                  btrim(diag23),
                  btrim(diag24),
                  btrim(diag25)
                  ], NULL) As diagnoses,
                  los_day_cnt,
                  drg_price_amt,
                  drg_outlier_pmt_amt,
                  pass_thru_amt,
                  mdcr_pmt_amt 

                from: ip
                populate: false
              columns:
                - admission_year:
                    type: "INT"
                    source:
                      type: "generated"
                      code: "GENERATED ALWAYS AS (EXTRACT (YEAR FROM admission_date)) STORED"
                - diagnoses:
                    type: "VARCHAR(12)[10]"
                    source: None
                - state_iso:
                    type: "char(5)"
                    source:
                      type: generated
                      code: "GENERATED ALWAYS AS ('US-' || state)  STORED"
                    index:
                      include:
                        - bene
                        - pd_hll_hash
                - bene:
                    type: HLL_HASHVAL
                    description: |
                      HLL hash (see https://en.wikipedia.org/wiki/HyperLogLog).
                      Can be used to calculate number of beneficiaries
                      satisfying a certain given condition
                      Use #(hll_add_agg(bene)) or hll_cardinality(hll_add_agg(bene)) aggregator
                    reference: https://github.com/citusdata/postgresql-hll/blob/master/REFERENCE.md#basic-operational-functions
                    source:
                      type: "generated"
                      code: "GENERATED ALWAYS AS (hll_hash_text(bene_id)) STORED"
                    index: false
                - pd_hll_hash:
                    type: HLL_HASHVAL
                    source:
                      type: "generated"
                      code: "GENERATED ALWAYS AS (hll_hash_text(primary_diagnosis)) STORED"
                    index: false
                - icd_hll:
                    type: HLL
                    source:
                      type: "generated"
                      code: "GENERATED ALWAYS AS (hll_arr_agg(diagnoses)) STORED"
                    index: false
              primary_key:
                - bene_id
                - year
                - state
                - admission_date
                - discharge_date
              indices:
                adm_ys_idx:
                  columns:
                    - state
                    - year
                adm_ys_iso_idx:
                  columns:
                    - state_iso
                    - year
                  include:
                    - bene
                adm_ysdz_idx:
                  columns:
                    - state
                    - year
                    - zip
                    - primary_diagnosis

  ## QC Tables
    qc_enrl_bene:
      description: |
        Joined data for beneficiaries and enrollments
      create:
        type:  view
        select: '*'
        from: enrollments natural join beneficiaries
      columns:
        - consistent_dob:
            type: VARCHAR(12)
            source: |
              (CASE
                WHEN dob IS NULL THEN 'MISSING'
                WHEN dob_latest IS NOT NULL THEN 'AMBIGUOUS'
                ELSE 'CONSISTENT'
              END)
        - consistent_dod:
            type: VARCHAR(12)
            source: |
              (CASE
                WHEN dod IS NULL THEN 'NONE'
                WHEN dod_earliest IS NOT NULL THEN 'AMBIGUOUS'
                ELSE 'CONSISTENT'
              END)
        - consistent_sex:
            type: VARCHAR(12)
            source: |
              (CASE
                WHEN sex LIKE '%,%' THEN 'AMBIGUOUS'
                ELSE 'CONSISTENT'
              END)
        - consistent_race:
            type: VARCHAR(12)
            source: |
              (CASE
                WHEN race LIKE '%,%' THEN 'AMBIGUOUS'
                ELSE 'CONSISTENT'
              END)
    qc_enrollments:
      description: |
        Aggregate data for beneficiaries and enrollments
      create:
        type: materialized view
        from: qc_enrl_bene
        nullable group by:
          - year
          - state
          - zip
          - fips3
          - orec
          - curec
          - hmo
          - dual
          - buyin
          - consistent_dob
          - consistent_dod
          - consistent_sex
          - consistent_race
          - fips3_is_approximated
          - fips3_valdiated
      columns:
        - year
        - state
        - zip
        - fips3
        - orec
        - curec
        - hmo
        - dual
        - buyin
        - consistent_dob
        - consistent_dod
        - consistent_sex
        - consistent_race
        - fips3_is_approximated
        - fips3_valdiated
        - state_iso:
            type: CHAR(5)
            source: MAX(state_iso)
        - NumRecords:
            type: BIGINT
            source: COUNT(*)
            index: false
        - NumDistinctBeneficaries:
            type: BIGINT
            source: "(#(hll_add_agg(bene)))::BIGINT"
            index: false
        - bene_hll:
            source: "hll_add_agg(bene)"
            index: false

    qc_adm_union:
      description: |
        Union of data from admissions table with the records
        discarded because of validation issues
      create:
        type:  view
        from: admissions
        select: |
           reason,
              year,
              state,
              state_iso,
              admission_year,
              diagnoses,
              zip,
              admission_date,
              discharge_date,
              adm_day_of_week,
              primary_diagnosis,
              bene_id,
              bene,
              pd_hll_hash,
              icd_hll
           FROM medicare_audit.admissions
           UNION ALL 
           SELECT 'OK'::VARCHAR AS reason,
              year,
              state,
              state_iso,
              admission_year,
              diagnoses,
              zip,
              admission_date,
              discharge_date,
              adm_day_of_week,
              primary_diagnosis,
              bene_id,
              bene,
              pd_hll_hash,
              icd_hll

    qc_admissions:
      description: |
        Aggregate data for admissions, beneficiaries and enrollments
      create:
        type: materialized view
        from: qc_adm_union
        nullable group by:
          - year
          - state
          - zip
          - reason
      columns:
        - year
        - state
        - zip
        - reason
        - state_iso:
            type: CHAR(5)
            source: MAX(state_iso)
        - NumRecords:
            type: BIGINT
            source: COUNT(*)
            index: false
        - NumDistinctBeneficaries:
            type: BIGINT
            source: "(#(hll_add_agg(bene)))::BIGINT"
            index: false
        - bene_hll:
            source: "hll_add_agg(bene)"
            index: false