Dimensional Modeling and Star Schema Concepts Questions
Understand fact and dimension tables, surrogate keys, and slowly changing dimensions. Be able to write queries that efficiently query dimensional data structures. Understand grain of fact tables and how to aggregate appropriately.
MediumTechnical
22 practiced
Discuss ELT versus ETL when building dimensional models in modern cloud warehouses. As a data engineer, list the benefits and drawbacks of pushing transformations into the warehouse (ELT) compared to doing heavy transforms in an external engine before loading.
Sample Answer
Definition and short framing:- ETL: extract → transform in an external engine (Spark, Airflow + DB/compute) → load cleaned dimensional tables into the warehouse.- ELT: extract → load raw data into cloud warehouse (BigQuery/Redshift/Snowflake) → transform inside the warehouse (SQL, stored procedures, views, dbt).Benefits of pushing transforms into the warehouse (ELT):- Performance & simplicity: Modern warehouses use massively parallel processing and columnar storage optimized for SQL; many transformations run faster and simpler in-warehouse.- Reduced architecture complexity: Fewer moving parts (no separate compute cluster to maintain), simpler data lineage when using tools like dbt.- Freshness & experimentation: Analysts can run ad-hoc transforms or materialized views against raw data for quick experiments.- Cost model fit: Pay-for-query/compute can be cheaper than running and managing persistent Spark clusters for intermittent workloads.- Governance & metadata: Centralized security, access controls, and auditing inside the warehouse.Drawbacks / when ETL (external transforms) may be preferable:- Heavy or specialized compute: Complex machine learning feature engineering, graph processing, or non-SQL ops are better on Spark or specialized engines.- Cost predictability: High-frequency large-volume ELT queries can become expensive and unpredictable; external scheduled batch compute may be cheaper for steady heavy jobs.- Data privacy / compliance: Sensitive raw data may need masking before landing in shared warehouse, requiring pre-load transforms.- Dependency on warehouse limits: Concurrency limits, UDF performance, and vendor-specific SQL can be constraints.- Cold-start and vendor lock-in: Moving heavy logic into warehouse SQL/dbt can make multi-cloud portability harder.Practical guidance for a data engineer:- Hybrid approach: Land raw data in the warehouse, do lightweight ELT (cleaning, dedup, transforms) and offload heavy compute (ML features, complex joins at massive scale) to Spark/Databricks when needed.- Use dbt for maintainable in-warehouse transformations, CI, testing, and lineage.- Monitor cost and performance: track query cost, optimize partitions/clustering, and set budgets.- Enforce governance: mask PII pre-load or use separate raw and curated schemas with strict access controls.Summary: ELT leverages modern warehouse strengths for simplicity, speed, and analyst autonomy; ETL remains valuable for specialized compute, cost predictability, and compliance. Choose a pragmatic hybrid guided by workload, cost model, and governance requirements.
MediumTechnical
28 practiced
List and describe five automated data quality checks you would run nightly against a dimensional model to detect issues like orphaned facts, duplicate surrogate keys, unexpected growth in dimension size, missing current flags, and referential integrity problems. Explain remediation actions for each check.
Sample Answer
1) Orphaned facts check - What: Count fact rows whose foreign-key surrogates have no matching dimension row (LEFT JOIN where dim IS NULL). - Implementation: nightly SQL/Spark job per fact table; report count and sample keys. - Alert rule: any >0 or sustained >N/day. - Remediation: inspect pipeline that populates FKs (upstream join/lookup), identify late/missing dimension loads, re-run ETL for missing dimension members, backfill facts or attach to a “unknown” dimension row, add idempotent repair job.2) Duplicate surrogate key check - What: detect duplicate surrogate keys in dimension tables (group by surrogate_key HAVING count>1). - Implementation: dedupe query, show row counts and sample payload differences. - Alert rule: any duplicates. - Remediation: freeze writes, reconcile source natural keys/timestamps to pick canonical row (latest by effective_date), merge duplicates, fix upstream key generation (sequence/counter) and add unique constraint/index.3) Unexpected growth in dimension size - What: compare current dim row count and delta% vs historical baseline (7/30-day median). Also track distinct natural-key growth. - Implementation: time-series monitor; alert on spikes (e.g., >30% day-over-day or >3σ). - Remediation: sample new rows to detect junk/ETL bug (e.g., missing dedupe), roll back faulty load, tighten deduplication logic, add validation upstream (schema, business rules), and adjust alerts if new business reason valid.4) Missing current flags for SCD Type 2 - What: verify each natural key has exactly one row with current_flag = true. Report keys with zero or >1 current. - Implementation: group by natural_key, sum(current_flag) filter <>1. - Alert rule: any anomalies or >threshold. - Remediation: re-run SCD closing/opening logic for affected keys, fix edge-case handling (overlapping effective dates), patch ETL to enforce invariants and add post-load assertions.5) Referential integrity / foreign-key violation trend - What: detect FK mismatches across all relationships and trending rate of violations (not just orphans). Also check staging → dim consistency. - Implementation: nightly referential integrity report, sample violating rows and earliest occurrence. - Alert rule: new violations or rising trend. - Remediation: triage whether violations due to delayed dimension loads, schema changes, or bad source data; fix source or ordering, backfill missing dims, consider transactional ordering or tombstone rows, and implement constraints or CDC-based guards.For each check add: automated alert (Slack/email + ticket), attach diagnostic artifacts (sample rows, pipeline run IDs), and require SLAed owner assignment with playbook steps to remediate and postmortem.
EasyTechnical
23 practiced
Compare star schema and snowflake schema for an analytics warehouse. As a data engineer, when would you choose a star schema over a snowflake schema and why? Discuss maintenance, query performance, storage, and the impact on ETL complexity with examples.
Sample Answer
Definition & core difference:- Star schema: a central fact table connected directly to denormalized dimension tables (wide, flat).- Snowflake schema: dimensions normalized into multiple related tables (hierarchies split across tables).When I’d choose star schema (as a data engineer):- Prioritize fast analytical queries and simplicity for analysts.- Use when dimensions are relatively static, cardinality is moderate, and storage costs are acceptable.Comparison by area:- Query performance: Star wins for typical OLAP queries — fewer joins, simpler SQL, better use of columnar scans and query planners. Example: sales_fact JOIN customer_dim + product_dim is one hop per dim vs. multiple joins in snowflake.- Maintenance: Star is simpler to maintain (single dimension table to update), easier to document and understand for BI teams. Snowflake requires managing more FK relationships and cascade updates.- Storage: Snowflake can save space by eliminating repeated attribute values (normalized). Star uses more storage due to denormalized attributes, but with modern columnar compression this overhead is often small.- ETL complexity: Star simplifies ETL — single upsert/per-dimension load, fewer joins during load. Snowflake ETL must maintain multiple dimension tables and handle referential integrity across them (more merge/upsert steps). Example: loading a product with category/subcategory; star writes one product_dim row, snowflake must ensure category and subcategory tables exist and link.Trade-offs & guideline:- Choose star for analyst productivity, faster query performance, and simpler pipelines.- Choose snowflake when dimension attribute redundancy is huge (massive cardinality or long hierarchies) and storage or strict normalization is a priority.- Hybrid approach: keep a star for most queries but maintain normalized source-of-truth dimension tables upstream for governance and occasional rebuilds.
HardTechnical
20 practiced
Propose an aggregate-awareness strategy for BI: how to maintain multiple levels of pre-aggregated tables, choose which aggregate to query at runtime, keep aggregates in sync with base facts, and backfill aggregates after schema changes. Discuss routing logic and a simple cost model to choose aggregates.
Sample Answer
Requirements & constraints:- Support interactive BI queries over large fact tables with multiple granularities (hour/day/month), low-latency for common patterns, eventual consistency acceptable within SLAs, ability to backfill after schema changes.High-level strategy:- Maintain a hierarchy of pre-aggregated materialized tables (MV): raw_facts, agg_hour, agg_day, agg_month. Each MV stores aggregations by a fixed dimension grain + precomputed measures. Use partitioning (date) and clustering on join keys.Keeping MVs in sync:- Use CDC / event-driven pipelines (Kafka -> Spark/Flink/Beam) to apply incremental deltas to MVs in near real-time: apply inserts/updates/deletes to raw_facts and stream incremental aggregations to agg_hour (micro-batch). For higher-level MVs (agg_day/month) run scheduled small-window rollups from lower-level MVs (agg_hour -> agg_day) using idempotent upserts (MERGE).- For strong correctness, support periodic reconciliation jobs that recompute a partition and compare checksums; if mismatch, mark partition stale and trigger full recompute.Backfill & schema evolution:- When schema changes (new measure/column or corrected dimension), mark affected partitions as "needs backfill". Backfill process: 1) Materialize compute plan: which base partitions and which MVs are affected. 2) Run distributed backfill jobs to recompute those partitions directly from raw_facts (or from snapshots) into a new temp MV partition. 3) Swap partitions atomically (rename) or MERGE into production MV.- Keep lineage metadata (e.g., in a metastore) to accelerate incremental backfills.Routing logic (runtime):- At query time, determine candidate MVs that satisfy query predicates and required grain/accuracy (e.g., user requests day-level totals => prefers agg_day if it covers requested dimensions and filters).- Filter candidates by freshness (max_lag <= SLA) and coverage (contains all requested grouping keys/filters).- Score candidates with simple cost model and choose lowest cost.Simple cost model:- Estimate cost = estimated_rows_read / effective_throughput + compute_cost + staleness_penalty.- Practical scoring terms: - RowsRead ≈ partitions_to_scan * avg_rows_per_partition / selectivity_factor - ComputeCost ≈ CPU_cost_per_row * RowsRead - StalenessPenalty = alpha * age_in_seconds (if age > SLA then infinite) - JoinPenalty if query needs joins (extra cost to join dimension tables)- Normalize and pick MV with minimal score; fallback to raw_facts for correctness if no MV qualifies.Routing pseudo:Operational notes & trade-offs:- Storage vs latency: more MVs → lower query latency but higher storage and maintenance cost.- Complexity: incremental CDC + MERGE reduces backfill window; full recompute reserved for large schema shifts.- Monitoring: expose MV freshness, partition checksum, query routing logs and cost model accuracy metrics; automated alerts on stale or failing MVs.This approach balances interactive performance, operational correctness, and manageable engineering complexity for BI at scale.
python
candidates = find_mvs_covering(query)
candidates = filter_by_freshness(candidates, SLA)
scores = {m: score(m, query) for m in candidates}
best = argmin(scores) or raw_facts
route_to(best)HardTechnical
25 practiced
As a data engineer, outline approaches to store and query PII attributes in a dimension while enabling analytics and complying with privacy requirements: consider hashing, tokenization with a vault, reversible encryption, and separate pseudonymized analytics dims. Discuss trade-offs for joins, performance, and re-identification risk.
Sample Answer
Situation: You need to store/query PII in a dimension so analysts can join and run analytics while meeting privacy/regulatory constraints.High-level options and trade-offs:1) Hashed PII (one-way, deterministic)- How: Store H = hash(PII + salt). Use keyed HMAC or SHA-256 with a secret salt.- Joins: Deterministic hashes allow joins across systems if same salt/key used.- Performance: Fast, minimal storage overhead.- Re-identification risk: High if PII domain is small (emails, phone numbers) — susceptible to brute force / rainbow table attacks unless salt is long/secret and rate limits applied.- Use when: Low-risk linking required, and brute-force mitigations exist.2) Tokenization with vault (non-reversible in DB)- How: Replace PII with opaque token issued by a vault (Deterministic or random tokens). Vault stores mapping and enforces access controls/audit.- Joins: Deterministic tokenization supports joins; random tokens do not.- Performance: Slight latency at ingestion to call vault; tokens are small and fast in queries.- Re-identification risk: Low in DB (vault needed to resolve). Vault compromise is central risk.- Use when: Strong governance, need central control and audit of re-identification.3) Reversible encryption (deterministic vs non-deterministic)- How: Use AEAD (e.g., AES-GCM) with KMS-managed keys. Deterministic encryption (ECB-like) allows equality joins; randomized (with IV) prevents joins.- Joins: Deterministic = possible joins; Non-deterministic = cannot join on ciphertext.- Performance: Crypto overhead at read/write; can precompute encrypted join keys to speed queries.- Re-identification risk: Moderate — protected by KMS and access controls; key compromise allows decryption.- Use when: Need to query but must maintain confidentiality; enforce strict key policy and rotation.4) Separate pseudonymized analytics dims (surrogate keys + linkage table)- How: Keep a tight, access-controlled identity store mapping PII ↔ internal ID; analytics dims contain only surrogate ID and non-PII attributes.- Joins: Analysts join on surrogate ID — fast and simple.- Performance: Excellent for large-scale analytics; avoids crypto on query path.- Re-identification risk: Low in analytics layer; identity store is high-value and must be heavily protected/audited.- Use when: Clear separation of identity and analytics; supports scale and minimizes exposure.Cross-cutting considerations and recommendations:- Prefer central identity vault (tokenization or identity store) + pseudonymized analytics dims for best balance of queryability and risk.- Use deterministic crypto/tokenization only when you need equality joins; otherwise prefer randomized encryption or one-way hashes.- Key management: Use cloud KMS, strict IAM, key rotation, and isolated key custodianship.- Minimize PII exposure in compute (use enclave/secure processing for re-identification), audit all de-tokenization, and enforce row/column-level access controls and query logging.- Consider additional protections: rate limiting, differential privacy/aggregation, and retention policies.Example pattern: Ingest raw PII into a secure staging VPC -> tokenize via vault (deterministic token for email) -> store token in analytics dim with surrogate ID and signals -> analysts join on token/surrogate. Vault holds mapping and is the only component allowed to re-identify under strict approval and auditing.
Unlock Full Question Bank
Get access to hundreds of Dimensional Modeling and Star Schema Concepts interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.