Data Modeling and Architecture Questions

Design and modeling principles for transactional and analytical data systems. Topics include entity relationship modeling, normalization and denormalization trade offs, dimensional modeling with fact and dimension tables and star and snowflake schemata, indexing strategies, partitioning and sharding, and schema design for performance and maintainability. Cover data pipelines and integration patterns including extract transform load and extract load transform approaches, data warehousing and data lake concepts, ETL orchestration, and how sources feed into reporting and business intelligence systems. Also include considerations for data quality, governance, and the differences between online transaction processing and online analytical processing workloads.

EasyTechnical

44 practiced

Explain the trade-offs between normalization and denormalization for reporting systems. Discuss effects on update complexity, query speed, storage footprint, and how different BI tools cope with highly normalized vs denormalized data sources.

EasyTechnical

43 practiced

Compare views versus materialized views for BI dashboards. Describe circumstances where a materialized view is recommended, the trade-offs involved (freshness vs performance), and refresh strategies you might use for nightly and near-real-time dashboards.

MediumTechnical

42 practiced

Explain conformed dimensions and why they are important in an enterprise data warehouse. Provide a concrete example where a conformed 'customer' dimension enables consistent reporting across Sales and Support subject areas and describe a mechanism to enforce conformance.

MediumSystem Design

46 practiced

Design a star-schema for an e-commerce orders reporting subject area. Define the fact table (grain) and list key dimension tables and attributes (customer, product, time, store, promotion). Explain why you chose that grain and any surrogate keys, and outline one ETL step you would implement to populate the order_line fact.

Sample Answer

Requirements / grain:- Business need: support order-level and line-item level KPIs (revenue, margin, quantity, conversion) across time, customer, product, store, promotion.- Chosen grain: one row per order line (order_id + order_line_number). This enables roll-up to order and customer level and simple measures per SKU per order.

Fact table: fact_order_line- Surrogate key: order_line_sk (INT, surrogate)- Natural keys: order_id, order_line_number- Foreign keys (surrogate FKs): customer_sk, product_sk, time_sk_order_date, time_sk_ship_date (nullable), store_sk, promotion_sk (nullable)- Measures: quantity, unit_price, unit_cost, line_discount_amt, line_tax_amt, line_shipping_amt, line_total_amt (revenue), profit_amt, returned_qty, refunded_amt- Audit: order_created_ts, last_updated_ts, source_system, load_batch_id

Dimension tables & key attributes:- dim_customer (customer_sk PK) - natural: customer_id - attrs: name, email_hash, signup_date, customer_tier, lifetime_value_bucket, country, region, is_active- dim_product (product_sk PK) - natural: product_id, sku - attrs: product_name, category, subcategory, brand, color, size, current_price, cost, is_discontinued- dim_time (time_sk PK) — conformed time dimension - attrs: date_key (YYYYMMDD), full_date, day, week, month, quarter, year, fiscal_period, is_holiday- dim_store (store_sk PK) - natural: store_id - attrs: store_name, store_type (online/retail/marketplace), region, manager, open_date, status- dim_promotion (promotion_sk PK) - natural: promotion_id - attrs: promo_name, promo_type, start_date, end_date, discount_type, discount_value

Why surrogate keys:- Surrogates provide stable PK/FK independent of source changes, enable slowly changing dimensions (type 2) handling, smaller join keys for performance, and consistent conformed dims across marts.

ETL step to populate fact_order_line (example extract-transform-load):1. Source extract: - Extract orders and order_lines from OLTP (orders, order_items, shipments, promotions) plus product and customer snapshots.2. Transform (key step — lookup & calculate): - For each order_line row: - Lookup consitent surrogate keys: customer_sk (by customer_id and effective_date), product_sk (by sku and effective_date), store_sk, promotion_sk (if promo code exists and active), time_sk for order_date. - Calculate measures: unit_cost from dim_product effective version; line_total_amt = quantity * unit_price - line_discount_amt + line_tax_amt + line_shipping_amt; profit_amt = (unit_price - unit_cost) * quantity - line_discount_alloc. - Handle SCD2: if customer/product dimension has current row with validity, choose matching version by order_date. - Flag returned/refunded lines by joining returns table.3. Load: - Insert into fact_order_line using surrogate keys; deduplicate by (order_id, order_line_number); set load_batch_id and timestamps. - Maintain idempotency: use staging + merge/upsert to avoid duplicates on re-run.

Notes / trade-offs:- Line-grain supports product-level analytics (top SKUs, promotion impact). If volume is extremely high, consider partitioning fact by month/year and compressing numeric measures. Conformed dim_time allows consistent date-based reporting across facts.

HardSystem Design

43 practiced

Design a workflow to support GDPR/CCPA 'right to be forgotten' requests across your data warehouse, streaming systems, and BI caches. You must ensure deletions/pseudonymizations are applied, traceable, and do not break analytics that require aggregated privacy-safe results.

Sample Answer

Requirements and constraints- Functional: accept legal deletion/pseudonymization requests, remove or mask personal data across sources (OLTP, data warehouse, streaming, BI caches), propagate changes downstream, keep aggregated analytics intact where privacy-safe.- Non-functional: auditable, traceable, low-latency propagation, configurable retention/SLA, minimal dashboard breakage, secure key management.

High-level architectureRequest Intake (Legal/Portal) → Identity Resolution & Verification → Deletion Orchestrator (workflow service) → Source Actions (OLTP / Auth DB) + CDC stream (Kafka) → Transformation Layer (tokenization/pseudonymization) → Data Warehouse (e.g., Snowflake/BigQuery) + BI Cache/Looker/Tableau layer → Audit & Lineage Store (immutable) → Monitoring & QA.

Workflow steps1. Intake & verify: User submits right-to-be-forgotten request via portal/Support. Verify identity, assign request id.2. Resolve identities: Map all identifiers (user_id, email, device_id) using Identity Graph service; produce canonical principal id and list of locations to act on.3. Decide action: For each dataset define policy: delete, hard-delete, pseudonymize (reversible/irreversible), or cohort-aggregate substitution. Policies stored in Data Governance Catalog.4. Execute at source first: Orchestrator triggers deletion/pseudonymization in transactional systems (APIs/DB). Prefer source-of-truth action to prevent re-ingestion.5. Propagate via CDC: Use CDC (Debezium / DB log → Kafka) to emit tombstones and change events with metadata (request_id, policy, timestamp). Events follow a schema with action type and lineage info.6. Transformation & Warehouse enforcement: ETL/ELT (dbt, Spark) consumes CDC, applies transformation rules: - Hard-delete: remove rows or mark as deleted and scrub PII columns. - Pseudonymize: replace PII with token (format-preserving or reversible with KMS) and mark tokenized=true, store mapping only in secure vault if reversible allowed. - Aggregation fallback: if deletion would make a metric invalid (small counts), replace with aggregated/suppressed value and flag. Writes to DW must retain request_id & provenance tags for traceability.7. BI cache handling: BI tools often cache extracts. Orchestrator invalidates affected data sources and triggers refreshes: - For Live queries, no extra step beyond DW change. - For extracted caches, identify impacted dashboards via Data Lineage system, invalidate cache and schedule recompute; if immediate recompute is impossible, show privacy-safe message or aggregated placeholder.8. Traceability & audit: Append immutable audit records (append-only ledger in DW or ledger DB) with request_id, actions taken, timestamps, affected datasets, who approved, and hashes of removed records for verification. Expose compliance reports to legal.9. Verification & reconciliation: Periodic jobs compare expected tombstones vs applied deletions; run sample queries to ensure PII removed. SLA alerts if mismatches.10. Access control & key management: Reversible tokens use envelope encryption with KMS and tight access policies. Store mappings only when legally allowed.

Privacy-safe analytics strategies- Aggregation with noise or differential privacy for small cohorts.- k-anonymity / suppression: hide groups below threshold; return "≤k" instead of exact number.- Synthetic approximations for exploratory analytics.- Flag dashboards with lineage metadata so analysts know which metrics are affected by deletions.

Traceability and reproducibility- Every change event carries request_id and lineage tags so you can trace back from a dashboard value to source rows and the deletion action.- Maintain a Data Catalog mapping fields to sensitivity levels and deletion policies.- Retain non-PII analytical aggregates (with provenance) to preserve long-term analytics while respecting deletion.

Trade-offs and considerations- Latency vs consistency: Immediate source deletion + CDC gives eventual consistency in DW; for strict regs you may need to block queries until DW refresh.- Hard-delete removes ability to reconstitute historical analyses; reversible pseudonymization preserves analytics but increases risk and legal complexity.- BI user experience: frequent cache invalidations add load; use targeted invalidation and bulk recompute windows.

Operational & governance- Define SLAs per request type, retention of audit logs, legal hold exceptions.- Automated tests, simulated deletion drills in staging, and annual audits.- Role-based access: only authorized personnel can view mappings or reverse tokens.

Example minimal event schema (conceptual){ request_id, principal_id, action: [hard_delete | pseudonymize | aggregate_substitute], affected_table, affected_row_keys, timestamp, lineage_id}

Why this works for a BI Analyst- Ensures dashboards remain accurate for aggregated KPIs via substitution/noise while PII removed.- Lineage tags let you explain when a metric changed due to privacy actions.- Audit trails let compliance and stakeholders verify actions without exposing data.- The pattern supports collaboration with engineering, data governance, and legal to keep reporting reliable and compliant.

Unlock Full Question Bank

Get access to hundreds of Data Modeling and Architecture interview questions and detailed answers.

Join thousands of developers preparing for their dream job.