Data Storage Architecture Choice | Dataford Interview Questions

SSIS questions Star/ Snowflake Schema Difference

Write a PostgreSQL query against the provided dimensional tables to summarize outstanding loan balances by reporting month and region. The query should preserve fact rows whose branch or region mapping is missing, exclude reporting months outside the first quarter of 2026, and exclude zero-total regions.

Output

One row per reporting month and region
Columns: region_name, reporting_month, total_outstanding_balance, reporting_branches
Sort by month ascending, then region name ascending

Schema

fact_loan_snapshot

Column	Type	Description
snapshot_idPK	INT	Unique loan snapshot record identifier
branch_id	INT	Branch associated with the snapshot
snapshot_date	DATE	Date on which the balance was recorded
outstanding_balance	DECIMAL(12,2)	Outstanding loan balance at the snapshot date

dim_branch

Column	Type	Description
branch_idPK	INT	Unique branch identifier
branch_name	VARCHAR(100)	Branch display name
region_id	INT	Normalized region identifier

dim_region

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(100)	Region display name

Your question is Data Storage Architecture Choice. 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