Design logical model and consumption

Design a logical data model and consumption layer supporting business and technical services architecture. Provide an ERD‑level description for Accounts, Trades, Positions, Limits, and Customers including keys and cardinalities. Explain normalization vs. denormalization choices, SCD handling for Customers, and how you expose curated datasets for analytics (e.g., star schema vs. data lakehouse tables). Then outline a read path for three consumers: dashboards, regulatory extracts, and ad‑hoc notebooks.

Curated Trade Risk Dataset from Accounts, Trades, Positions, Limits, and SCD Customers

You work in a trading firm that maintains core transaction data in a normalized schema and exposes curated datasets for analytics (dashboards, regulatory extracts, ad-hoc notebooks).

Given the following logical entities:

• CUSTOMERS are modeled as a Slowly Changing Dimension (Type 2): each customer can have multiple versions over time with effective_from/effective_to dates.
• ACCOUNTS belong to CUSTOMERS.
• TRADES are booked against ACCOUNTS.
• POSITIONS are daily end-of-day quantities per ACCOUNT and instrument.
• LIMITS define an account-level position-notional limit that applies over a validity period.

Write a single SQL query that produces a curated, denormalized analytics dataset at the trade level with the following columns:

• trade_id
• trade_date
• account_id
• customer_id (from the owning account)
• customer_name and risk_segment AS-OF the trade_date (using the SCD2 customers_dim table)
• instrument
• trade_quantity (the trade quantity)
• trade_price (the trade price)
• position_quantity (end-of-day quantity for that account & instrument on the trade_date)
• position_notional = ABS(position_quantity * trade_price)
• limit_value (the POSITION_NOTIONAL limit effective on the trade_date for that account)
• breach_flag = 'Y' if position_notional > limit_value, otherwise 'N'

Assume:

• A trade should join to exactly one customer version, position row, and limit row based on date ranges and keys.
• LIMITS.limit_type = 'POSITION_NOTIONAL' identifies the correct limit.

Return one row per trade in the sample data. Order the result by trade_id.

Table customers_dim

column	type	constraints
customer_sk	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
customer_name	VARCHAR(100)	NOT NULL
risk_segment	VARCHAR(20)	NOT NULL
is_active	CHAR(1)	NOT NULL
effective_from	DATE	NOT NULL
effective_to	DATE	NOT NULL
current_flag	CHAR(1)	NOT NULL

Sample data:

current_flag	customer_id	customer_name	customer_sk	effective_from	effective_to	is_active	risk_segment
N	100	Alpha Corp	1	2024-01-01	2024-06-30	Y	Low
Y	100	Alpha Corp	2	2024-07-01	9999-12-31	Y	Medium
Y	200	Beta LLC	3	2024-01-01	9999-12-31	Y	High

Table accounts

column	type	constraints
account_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
account_type	VARCHAR(20)	NOT NULL
opened_date	DATE	NOT NULL
closed_date	DATE

Sample data:

account_id	account_type	closed_date	customer_id	opened_date
10	MARGIN	NULL	100	2024-01-10
20	CASH	NULL	200	2024-02-01

Table trades

column	type	constraints
trade_id	INT	PRIMARY KEY, NOT NULL
account_id	INT	NOT NULL
trade_date	DATE	NOT NULL
instrument	VARCHAR(20)	NOT NULL
quantity	INT	NOT NULL
price	DECIMAL(18,2)	NOT NULL
side	VARCHAR(4)	NOT NULL

Sample data:

account_id	instrument	price	quantity	side	trade_date	trade_id
10	AAPL	150	500	BUY	2024-06-15	1000
10	AAPL	160	400	BUY	2024-08-01	1001
20	GOOG	2000	300	BUY	2024-06-20	1002

Table positions

column	type	constraints
position_id	INT	PRIMARY KEY, NOT NULL
account_id	INT	NOT NULL
instrument	VARCHAR(20)	NOT NULL
position_date	DATE	NOT NULL
quantity	INT	NOT NULL

Sample data:

account_id	instrument	position_date	position_id	quantity
10	AAPL	2024-06-15	1	500
10	AAPL	2024-08-01	2	900
20	GOOG	2024-06-20	3	300

Table limits

column	type	constraints
limit_id	INT	PRIMARY KEY, NOT NULL
account_id	INT	NOT NULL
limit_type	VARCHAR(50)	NOT NULL
limit_value	DECIMAL(18,2)	NOT NULL
currency	VARCHAR(3)	NOT NULL
effective_from	DATE	NOT NULL
effective_to	DATE	NOT NULL

Sample data:

account_id	currency	effective_from	effective_to	limit_id	limit_type	limit_value
10	USD	2024-01-01	2024-12-31	1	POSITION_NOTIONAL	100000
20	USD	2024-01-01	2024-12-31	2	POSITION_NOTIONAL	50000

Expected output

One row per trade, enriched with as-of customer attributes (SCD2), end-of-day positions on the trade date, applicable position-notional limit, and a breach flag when position_notional exceeds the limit.

account_id	breach_flag	customer_id	customer_name	instrument	limit_value	position_notional	position_quantity	risk_segment	trade_date	trade_id	trade_price	trade_quantity
10	N	100	Alpha Corp	AAPL	100000	75000	500	Low	2024-06-15	1000	150	500
10	Y	100	Alpha Corp	AAPL	100000	144000	900	Medium	2024-08-01	1001	160	400
20	Y	200	Beta LLC	GOOG	50000	600000	300	High	2024-06-20	1002	2000	300

Hints

1. To get the correct customer version, join customers_dim on customer_id and ensure trade_date falls between effective_from and effective_to.
2. Join positions and limits on account_id (and instrument/date for positions), then compute position_notional and breach_flag with a CASE expression.