A data dictionary lists each table, what one row is and what each column holds. Read it before you write a query.
- Grain: what one row stands for. hd_encounters has one row per visit or stay; hd_diagnoses has one row per code on an encounter, so a stay with four codes has four rows. Counting rows of the wrong table counts the wrong thing.
- Keys: a primary key identifies a row (patient_id in hd_patients). A foreign key points to another table's primary key (patient_id in hd_encounters). Some tables need two columns together: in hd_diagnoses, encounter_id and seq.
- Empty values that mean something: an empty discharge_date means the patient is still in a bed; empty emergency times on a clinic visit mean the field does not apply. Neither is missing data.
- Code columns hold values from a code set (icd10_code) or a local list (test_code, encounter_type).
Know the grain before you count.