Business Intelligence and Data Warehouse Architecture Questions

Design end to end business intelligence systems and the underlying data warehouse architecture. Topics include data ingestion patterns for batch and streaming sources, change data capture, transformation layers and the choice between extract transform load and extract load transform approaches, dimensional modeling and schema choices such as star and snowflake schemas, fact and dimension table design, slowly changing dimensions strategies, medallion and layered architectures, and the visualization and consumption layer. Also cover pipeline orchestration, monitoring, observability, data quality checks, and trade offs between centralized and federated approaches as well as real time versus batch processing.

HardTechnical

69 practiced

The CEO wants a near-real-time revenue dashboard updated within five minutes, but the platform team says the cost of streaming everything is too high. How would you negotiate requirements and redesign the pipeline to balance freshness, correctness, and cost?

EasyTechnical

88 practiced

Describe the medallion or layered architecture used in data platforms. What problem does each layer solve, and how does this pattern help with quality, reuse, and auditing?

MediumTechnical

119 practiced

Your company has several business units that each want autonomy over their analytics while leadership wants a single version of the truth. How would you evaluate centralized versus federated warehouse ownership, and what hybrid model might work?

MediumTechnical

73 practiced

In SQL, how would you write a validation query for an incremental CDC load to confirm that updated rows replaced prior versions, deleted rows were handled correctly, and duplicate natural keys were not introduced? Describe the logic and key edge cases.

Sample Answer

I’d validate the CDC load by comparing source and target at both the key and record level. The main checks are: updated rows should reflect the latest version, deleted rows should be absent or marked deleted, and natural keys should remain unique in the target.

Example logic:

sql

-- 1) Duplicate natural keys in target
SELECT natural_key, COUNT(*) AS cnt
FROM target_table
GROUP BY natural_key
HAVING COUNT(*) > 1;

-- 2) Updated source rows should match latest target version
SELECT s.natural_key
FROM source_cdc s
JOIN target_table t ON s.natural_key = t.natural_key
WHERE s.op = 'U'
  AND s.updated_at > t.updated_at;

-- 3) Deleted rows should not remain active
SELECT t.natural_key
FROM target_table t
JOIN source_cdc s ON t.natural_key = s.natural_key
WHERE s.op = 'D'
  AND t.is_deleted = FALSE;

Key edge cases:- Late-arriving updates with older timestamps.- Multiple changes for the same key in one batch.- Hard deletes versus soft deletes.- Replayed CDC events causing duplicates.

In practice, I’d also compare row counts by operation type and checksum a subset of columns to catch silent merge errors.

HardTechnical

75 practiced

Your organization is considering replacing a traditional warehouse with a lakehouse to support both BI reporting and machine learning feature engineering. What criteria would you use to decide, and where do the trade-offs usually show up in governance, performance, and cost?

Unlock Full Question Bank

Get access to hundreds of Business Intelligence and Data Warehouse Architecture interview questions and detailed answers.

Join thousands of developers preparing for their dream job.