Design logical model and consumption

Quick Overview

This question evaluates understanding of logical data modeling, ERD-level design with keys and cardinalities, slowly changing dimensions, normalization versus denormalization trade-offs, and strategies for exposing curated datasets and read paths for analytics and regulatory consumers, indicating competency in data modeling and data delivery.

Company: EY

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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.

Overview: This question evaluates understanding of logical data modeling, ERD-level design with keys and cardinalities, slowly changing dimensions, normalization versus denormalization trade-offs, and strategies for exposing curated datasets and read paths for analytics and regulatory consumers, indicating competency in data modeling and data delivery.

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.

Tables

customers_dim(customer_sk INT, customer_id INT, customer_name VARCHAR(100), risk_segment VARCHAR(20), is_active CHAR(1), effective_from DATE, effective_to DATE, current_flag CHAR(1))

accounts(account_id INT, customer_id INT, account_type VARCHAR(20), opened_date DATE, closed_date DATE)

trades(trade_id INT, account_id INT, trade_date DATE, instrument VARCHAR(20), quantity INT, price DECIMAL(18,2), side VARCHAR(4))

positions(position_id INT, account_id INT, instrument VARCHAR(20), position_date DATE, quantity INT)

limits(limit_id INT, account_id INT, limit_type VARCHAR(50), limit_value DECIMAL(18,2), currency VARCHAR(3), effective_from DATE, effective_to DATE)

Hints

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