Skip Navigation LinksMPX-Data-Dictionary-Snowflake-Doses

california immunization registry

CAIR logoVaccinia (including MPX) Vaccination

- Recipients Snowflake Data Dictionary

Last Updated 10/7/2022

PDFā€‹

LHJ Data Source

Table Name: TBL_IMMUN_MONKEYPOX_RECIPIENTS

Description: This is a recipient level table that contains one row per vaccine recipient and is used by the California Department of Public Health (CDPH) vaccine data team for calculations that involve measures of people (e.g., partially/fully vaccinated, race/ethnicity). The data include all vaccinia vaccine doses (including monkeypox) reported to the California Immunization Registry (CAIR2) and the Healthy Futures Registry (the San Diego Immunization Registry was integrated into CAIR2 in April 2022).


FIELD DEFINITIONS

Field No.

Data Element

Variable Name

Description

1Recipient IDRECIP_IDUnique ID for recipient.
2Administration date ā€“ Dose 1DS1_ADMIN_DATEThe first recorded dose administration date for the recipient. Ignores DOSE_NUM field in dose-level dataset for determination.
3Administration date ā€“ Dose 2DS2_ADMIN_DATEThe second recorded dose administration date for the recipient. Ignores DOSE_NUM field in dose-level dataset for determination.
4Administration date ā€“ Dose 3DS3_ADMIN_DATEIf given, the third recorded dose administration date for the recipient. Ignores DOSE_NUM field in dose-level dataset for determination.
5Administration date ā€“ Dose 4DS4_ADMIN_DATEIf given, the fourth recorded dose administration date for the recipient. Ignores DOSE_NUM field in dose-level dataset for determination.
6Vaccine types receivedVAX_RECEIVEDThe trade name(s) of all vaccines received, for up to 4 doses.

Locally Defined Value Set:
ACAM2000
JYNNEOS
7Vaccination statusFULLY_VACCINATEDRecipientā€™s vaccination status for MPX.
A vaccine recipient is considered fully vaccinated for MPX if they meet at least one of the following criteria:
1) They received one dose of ACAM2000 (CVX 75)
2) They received two JYNNEOS (CVX 206) doses >= 24 days apart.

Locally Defined Value Set:
FALSE (Partially vaccinated)
TRUE (Fully vaccinated)
8Fully vaccinated dateFULLY_VACCINATED_DATEDate recipient became fully vaccinated for MPX.
9Partially vaccinated datePARTIALLY_VACCINATED_DATEDate recipient became partially vaccinated for MPX (i.e., received first dose of JYNNEOS)
10Recipient name: firstRECIP_FIRST_NAMERecipientā€™s first name.
11Recipient name: middleRECIP_MIDDLE_NAMERecipientā€™s middle name.
12Recipient name: lastRECIP_LAST_NAMERecipientā€™s last name.
13Recipient date of birthRECIP_DOBRecipientā€™s date of birth.
14Recipient ageRECIP_AGERecipientā€™s age in years.
15Recipient age groupRECIP_AGE_GROUPRecipientā€™s age group, in years.

Locally Defined Value Set:
16Recipient sexRECIP_SEXRecipient sex. Derived from value recorded for second dose, unless null, then first dose value is used.

Locally Defined Value Set:
F (Female)
M (Male)
O (Other)
X (Non-binary)
U (Unknown/undifferentiated)
17Recipient race/ethnicity  RECIP_RACE_ETHRecipientā€™s race/ethnicity.

When ethnicity is marked ā€˜Hispanic or Latinoā€™ then ā€˜Latinoā€™, when more than one specified race code (1002-5, 2028-9, 2076-8, 2054-5, 2106-3) is noted then ā€˜Multiracialā€™, otherwise first race is noted.
Derived from original ethnicity and race codes:
HL7 User Defined Table 0189
2135-2 (Hispanic or Latino)
2186-5 (Not Hispanic or Latino)
UNK (Unknown ethnicity)
HL7 User Defined Table 0005
1002-5 (American Indian or Alaska Native)
2028-9 (Asian)
2076-8 (Native Hawaiian or Other Pacific Islander)
2054-5 (Black or African American)
2106-3 (White)
2131-1 (Other Race)
UNK (Unknown)
18Recipient address: streetRECIP_ADDRESS_STREETThe street component of the recipientā€™s address.
19Recipient address:
street 2
RECIP_ADDRESS_STREET_2The street 2 component of the recipientā€™s address.
20Recipient address: cityRECIP_ADDRESS_CITYThe city component of the recipientā€™s address.
21Recipient address: stateRECIP_ADDRESS_STATEThe state component of the recipientā€™s address.

US Postal Service two-character state codes
22Recipient address: zip codeRECIP_ADDRESS_ZIPThe zip code of the recipientā€™s address.
23Recipient county nameRECIP_COUNTY_LABELThe county name component of the recipientā€™s address.
ā€˜Outside Californiaā€™ category includes out-of-state residents and records where recipientā€™s state of residence is blank.

Derived from FIPs based on County_Code. When recip_address_county is null, derived from recip_address_zip. See Recip_Address_County_New.
24Responsible organizationRESPONSIBLE_ORGThe name of the parent organization or health system that originated and is accountable for the content of the record. If an organization has several clinics or facilities, this would be the organization that represents all of the clinics/facilities. (The "Administered at location" field is the name of individual physical location.)
25Administered at locationADMIN_NAMEThe name of the physical clinic or facility that reported the vaccination, refusal, or missed appointment. In a small practice setting, this could be the same as the responsible organization.

ā€‹26

ā€‹Administration address: concatenated
ā€‹MIXED_ADMIN_ADDRESS
ā€‹Concatenation of the vaccinating providerā€™s address street, city, state, and zip code.
27Administration address: stateADMIN_ADDRESS_STATEThe state component of where the vaccine is administered/ planned to be administered. Exception for mobile clinics.

US Postal Service two-character state codes
28Administration address:
zip code
ADMIN_ADDRESS_ZIPThe zip code component of where the vaccine is being administered/ planned to be administered. Exception for mobile clinics.
29Administration address:
county name
ADMIN_COUNTY_LABELThe county component of where the vaccine is being administered/ planned to be administered. Exception for mobile clinics.

Join with FIPS table based on County_Code to get the name/description.
30Mixed zipMIXED_ZIPIf recipient zip code is null, this field is populated by admin zip code.
31Mixed countyMIXED_COUNTYIf recip_address_county_new is NULL or unknown, populate this with admin_county_label.
ā€˜Outside Californiaā€™ category includes out-of-state residents and records where recipientā€™s state of residence is blank.
32Local health jurisdictionLHJLocal health jurisdiction of recipientā€™s residence; if zip code (mixed_zip) is in Berkeley, Long Beach, or Pasadena cities, LHJ = Berkeley, Long Beach and Pasadena, respectively, otherwise LHJ = mixed_county.
ā€˜Outside Californiaā€™ category includes out-of-state residents and records where recipient's state of residence is blank.

when mixed_zip in (ā€˜94701,ā€™ ā€˜94702,ā€™ ā€˜94703,ā€™ ā€˜94704,ā€™ ā€˜94705,ā€™ ā€˜94707,ā€™ ā€˜94708,ā€™ ā€˜94709,ā€™ ā€˜94710,ā€™ ā€˜94712,ā€™ ā€˜94720') then ā€˜Berkeleyā€™
 when mixed_zip in (ā€˜90801,ā€™ ā€˜90802,ā€™ ā€˜90803,ā€™ ā€˜90804,ā€™ ā€˜90805,ā€™ ā€˜90806,ā€™ ā€˜90807,ā€™ ā€˜90808,ā€™ ā€˜90809,ā€™ ā€˜90810,ā€™ ā€˜90813,ā€™ ā€˜90814,ā€™ ā€˜90815,ā€™ ā€˜90822,ā€™ ā€˜90831,ā€™ ā€˜90832',
 when mixed_zip in (ā€˜90833,ā€™ ā€˜90834,ā€™ ā€˜90835,ā€™ ā€˜90840,ā€™ ā€˜90842,ā€™ ā€˜90844,ā€™ ā€˜90845,ā€™ ā€˜90846,ā€™ ā€˜90847,ā€™ ā€˜90848,ā€™ ā€˜90853,ā€™ ā€˜90888,ā€™ ā€˜90899') then ā€˜Long Beachā€™
when left(mixed_zip,5) in (ā€˜91101,ā€™ ā€˜91102,ā€™ ā€˜91103,ā€™ ā€˜91104,ā€™ ā€˜91105,ā€™ ā€˜91106,ā€™ ā€˜91107,ā€™ ā€˜91109,ā€™ ā€˜91110,ā€™ ā€˜91114,ā€™ ā€˜91115,ā€™ ā€˜91116,ā€™ ā€˜91117,ā€™ ā€˜91121,ā€™ ā€˜91123,ā€™ ā€˜91124,ā€™ ā€˜91125,ā€™ ā€˜91126,ā€™ ā€˜91129,ā€™ ā€˜91131,ā€™ ā€˜91182,ā€™ ā€˜91184,ā€™ ā€˜91185,ā€™ ā€˜91188,ā€™ ā€˜91189,ā€™ ā€˜91191,ā€™ ā€˜91199') then ā€˜Pasadenaā€™
ELSE mixed_countyā€‹

ā€‹33
ā€‹Census tract ā€“ recipient
ā€‹CENSUS_TRACT_10
ā€‹2010 census tract based on recipient address only
ā€‹34
ā€‹Census tract ā€“ administration address
ā€‹CENSUS_TRACT_ADMIN_10
ā€‹2010 census tract based on vaccinating providerā€™s address only.
ā€‹35
ā€‹HPI 3.0 - census tract
ā€‹HPI_3_TRACT
ā€‹

Healthy Places Index 3.0 score based on the 2010 census tract from the recipientā€™s address.

Healthy Places Index


ā€‹36
ā€‹HPI 3.0 ā€“ zip
ā€‹HPI_3_ZIP
ā€‹Healthy PIaces Index 3.0 score based on the recipientā€™s address zip (if null, uses the ZCTA zip from the zip-to-ZCTA crosswalk); if recipientā€™s address zip is still null, uses the vaccinating providerā€™s address zip (if providerā€™s address zip is null, uses the ZCTA from the zip-to-ZCTA crosswalk).

Healthy Places Index



ā€‹37
ā€‹HPI 3.0 - mixed
ā€‹HPI_3_MIXED
ā€‹
HPI_3_TRACT; if null, uses HPI_3_ZIP.

Healthy Places Index



38VEM quartile HPIQUARTILEVaccine Equity Metric (VEM) score quartiles based on mixed_zip.
Vaccine Equity Metric (VEM) score quartiles combine the Public Health Alliance of Southern Californiaā€™s Healthy Places Index (HPI) measure with CDPH-derived scores to estimate factors that impact health, like income, education, and access to health care. Zip codes range from less healthy community conditions in Quartile 1 to more healthy community conditions in Quartile 4.

Locally Defined Value Set:
1 (VEM Quartile 1)
2 (VEM Quartile 2)
3 (VEM Quartile 3)
4 (VEM Quartile 4)
Null (no VEM score assigned)
39VEM quartile based on recipient zip codeHPIQUARTILE_RCP_ZIPVaccine Equity Metric (VEM) score quartiles based on recipient zip code
Vaccine Equity Metric (VEM) score quartiles combine the Public Health Alliance of Southern Californiaā€™s Healthy Places Index (HPI) measure with CDPH-derived scores to estimate factors that impact health, like income, education, and access to health care. Zip codes range from less healthy community conditions in Quartile 1 to more healthy community conditions in Quartile 4.

Locally Defined Value Set:
1 (VEM Quartile 1)
2 (VEM Quartile 2)
3 (VEM Quartile 3)
4 (VEM Quartile 4)
Null (no VEM score assigned)
40Recipient address: county based on zip codeHPI_COUNTY_RCP_ZIPThe county name of the recipientā€™s address, derived from the zip code of the recipientā€™s address.

County derived from recip_address_zip
41
Administered at location: typeADMIN_TYPE_DESCThe characteristic of the provider site that reported the vaccination, refusal, or missed appointment.

Locally Defined Value Set:
1 (Childcare facility)
2 (College/university)
3 (Community HC)
4 (Corrections facility - all ages)
5 (Drug treatment facility)
6 (Family planning)
7 (Family practice)
8 (Fed-qual or rural HC)
9 (Foster care)
10 (Health department-associated site)
11 (Health plans)
12 (HIE/data aggregator)
13 (HIV/STD clinic)
14 (Home health)
15 (Hospital - birthing)
16 (Tribal/IHS clinic)
17 (Internal medicine)
18 (Juvenile detention center)
19 (Local health dept)
20 (Military healthcare)
21 (Multispecialty practice)
22 (Nursing home/LTC)
23 (OBGYN)
24 (Other)
25 (Other IIS)
26 (Pediatrics)
27 (Pharmacy)
28 (Private practice)
29 (School/school district)
30 (Special vaccine clinic)
31 (State agency)
32 (State public health)
33 (Urgent care)
34 (WIC program)
35 (Hospital)
36 (School clinic)
37 (Non clinical site)
38 (Pandemic only)
39 (Pending)
42Lot numbersLOT_NUMBERSThe lot numbers for all COVID vaccinations administered, concatenated.
43EmailEMAIL_ADDRESSRecipientā€™s email address.
44Phone numberPHONE_NUMBERRecipientā€™s phone number.
ā€‹45
ā€‹Dose 1 vaccination event ID
ā€‹DS1_VAX_EVENT_ID
ā€‹The vaccination eventā€™s unique identifier within the system for first reported dose (based on vaccination date).

n/a



ā€‹46
ā€‹Dose 2 vaccination event ID
ā€‹DS2_VAX_EVENT_ID
ā€‹The vaccination eventā€™s unique identifier within the system for second reported dose (based on vaccination date).

n/a
ā€‹47
ā€‹Dose 3 vaccination event ID
ā€‹DS3_VAX_EVENT_ID
ā€‹The vaccination eventā€™s unique identifier within the system for third reported dose (based on vaccination date).

n/a
ā€‹48
ā€‹Dose 4 vaccination event ID
ā€‹DS4_VAX_EVENT_ID
ā€‹The vaccination eventā€™s unique identifier within the system for fourth reported dose (based on vaccination date).

n/a
49Patient sharing indicatorLOCKED_INDPatient sharing status.
If 1, patient has requested that their record not be shared with organizations other than their own provider or public health.

Locally Defined Value Set:
0 (FALSE)
1 (TRUE)
ā€‹50
ā€‹Snapshot time
ā€‹SNAPSHOT_TIME
ā€‹Data current as of this timestamp.

n/a
51Table creation dateTBL_CREATE_DTTMDate/time the recipient level view materialization (table creation) is performed.

Notes: Minimum intervals are based on CDC recommendations, using 28-day months and including a 4-day grace period. 


Page Last Updated :