Join Types in Star Schemas | Dataford Interview Questions

Can you explain the difference between various join types and when to use them in a star schema?

Use the provided fact and dimension tables to demonstrate the appropriate join behavior for preserving every fact row, including rows with missing dimension matches.

Output

One row per encounter with encounter_id, encounter_date, patient_name, and service_name.
Include all encounters, retain NULLs for missing dimension matches, and order by encounter_id ascending.

Schema

fact_encounters

Column	Type	Description
encounter_idPK	INTEGER	Unique encounter identifier
encounter_date	DATE	Date of the encounter
patient_id	INTEGER	Patient dimension key
service_id	INTEGER	Service dimension key

dim_patient

Column	Type	Description
patient_idPK	INTEGER	Unique patient dimension key
patient_name	VARCHAR(100)	Patient display name

dim_service

Column	Type	Description
service_idPK	INTEGER	Unique service dimension key
service_name	VARCHAR(100)	Service display name

Your question is Join Types in Star Schemas. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem