Data Modeling and Schema Design Questions

Focuses on designing efficient, maintainable data schemas for transactional and analytical systems. Candidates should demonstrate understanding of normalization principles and normal forms, when and why to denormalize for performance, and schema design patterns for different use cases. Expect dimensional modeling topics including fact and dimension tables, star and snowflake schemas, grain definition, slowly changing dimensions, and strategies for handling historical data. The topic also includes trade offs between online transaction processing and online analytical processing designs, query performance considerations, indexing and partitioning strategies, and the ability to evaluate and improve existing schemas to meet business requirements and scale.

MediumTechnical

37 practiced

A dimension must be extended with additional attributes used by new dashboards. Describe a safe schema-evolution strategy that avoids breaking historical reports and minimizes data pipeline downtime. Include steps for schema rollout, backward compatibility, and communicating changes to stakeholders.

Sample Answer

Situation: We need to add attributes to an existing reporting dimension for new dashboards without breaking historical reports or causing pipeline downtime.

Strategy (high level):- Add attributes in a backward-compatible, staged way using schema versioning, views, feature flags and clear data contracts.

Detailed rollout steps:1. Design & contract: - Define new attributes, types, default/null semantics, and SLAs in a data contract. - Document use-cases and downstream consumers (dashboards, ETL jobs, models).

2. Non-destructive schema change: - Add new columns as nullable to the existing dimension table or create a new dimension_v2 table with the extra columns populated by the ETL. - If using a data warehouse (Snowflake/BigQuery/Redshift), prefer adding nullable columns or creating a copy to avoid locking.

3. ETL / pipeline changes: - Implement ETL to populate new attributes into dimension_v2 or the added nullable columns. - Deploy ETL changes behind a feature flag or config toggle so you can enable/disable population without rollback risk.

4. Backfill & validation: - Backfill historical rows for the new fields where possible (batch job) or leave NULLs with clear semantics. - Run verification tests: row counts, referential integrity, sample-based validation, and automated data quality checks.

5. Provide compatibility layer: - Create/versioned views: - dimension_view (stable) continues to expose the schema expected by historical reports. - dimension_view_v2 (or a flag) exposes new attributes for new dashboards. - Keep column names identical for unchanged fields; add new columns only in v2 view.

6. Gradual consumer migration: - Notify dashboard owners and provide migration docs + examples (SQL snippets, Looker/Power BI field mappings). - Encourage new dashboards to use dimension_view_v2. - Monitor usage and run parallel reporting for a transition window.

7. Cutover & cleanup: - Once all consumers have migrated, update the stable view to include new attributes (if desired) and retire v2. - If you created a separate table, consider swapping names atomically (rename) during a low-traffic window. - Remove feature flags and old ETL paths after confirming stability.

Backward compatibility considerations:- Never drop or rename existing columns without a deprecation window.- Keep defaults and NULL semantics stable.- Use views to present a stable contract while allowing physical schema evolution.- Version data contracts and track changes in a metadata registry.

Communication plan:- Issue a kickoff email describing purpose, timeline, and impact; include rollback plan.- Share data contract, example queries, and migration checklist with dashboard owners and data engineers.- Schedule a short walkthrough and Q&A with stakeholder teams.- Provide regular status updates and a clear deprecation timeline (e.g., 90 days) before any breaking changes.- Offer direct support during migration (office hours, Slack channel).

Why this is safe:- Nullable/additive changes + views ensure old reports read identical data.- Feature flags and parallel pipelines allow rollback with minimal downtime.- Data contracts and stakeholder communication reduce surprise downstream impacts.

EasyTechnical

32 practiced

Design a date/time dimension suitable for most BI dashboards. List common attributes (e.g., date_key, year, quarter, fiscal flags, weekdays, holiday flags) and explain how you would handle fiscal calendars, multiple time zones, and granularity for hourly reporting.

Sample Answer

A well-designed date/time dimension (conformed dimension for BI) should cover daily and optionally time-of-day granularity and include attributes that support common filters, grouping, and business logic.

Key attributes (daily grain):- date_key (int, YYYYMMDD) — surrogate natural key- date (date) — actual date- year, month, day, day_of_month- month_name, month_abbrev, quarter, quarter_name- iso_week, week_of_year- weekday, weekday_name, is_weekend (bool)- day_of_year, week_of_month- fiscal_year, fiscal_quarter, fiscal_period, fiscal_month, is_fiscal_year_end (nullable)- is_business_day (bool), holiday_name (nullable), is_holiday (bool)- season, business_month_bucket (for custom grouping)- flags: is_month_start, is_month_end, is_quarter_start, is_quarter_end- last_day_of_month (date)- created_ts, updated_ts (for ETL metadata)

Time / datetime extensions (if hourly/minute granularity):- datetime_key (YYYYMMDDHH or YYYYMMDDHHMM)- hour_of_day, am_pm, minute_of_hour, time_slot_label

Handling fiscal calendars:- Support configurable fiscal calendars by storing multiple fiscal mappings: either additional columns for common fiscal starts (e.g., fiscal_start_month) or a separate fiscal_calendar table joined by date. Populate fiscal_year/period/quarter based on business rules (start month or 4-4-5 patterns). Keep business rules in ETL code and document assumptions; provide both Gregorian and fiscal fields so reports don’t need runtime calc.

Multiple time zones:- Keep the date dimension in UTC (canonical). For user-local reporting, provide a time_zone table and use event timestamps converted to target TZ in ETL or BI layer. For daily aggregates, store both date_utc and date_local (for major timezones) or generate a time_dimension per timezone if business requires strict local-day boundaries (e.g., retail). Provide tz_offset or tz_name on datetime rows to enable correct bucketing.

Granularity for hourly reporting:- Create a time dimension at the lowest required grain (hourly or minute). Use surrogate datetime_key and join facts on truncated timestamp to that key. For high-volume facts, keep daily dimension and pre-aggregate hourly facts or build an hourly partitioned fact table. Ensure indexes/partitions by timestamp and keep hierarchies (date → hour → minute) to support rollups efficiently.

Best practices:- Conformed dimension shared across marts, immutable rows, documented ETL, include surrogate keys, and populate far past/future horizons (e.g., ±10 years). Store holiday and business-day logic centrally so dashboards remain consistent.

HardTechnical

36 practiced

Compare OLTP normalized schemas and OLAP dimensional schemas for the same business domain. As a BI analyst, explain the tradeoffs in terms of data freshness, query complexity, storage requirements, and concurrency. Propose a hybrid approach that supports both near real-time operational reporting and deep analytical queries.

Sample Answer

OLTP (normalized) vs OLAP (dimensional) — short comparison and hybrid proposal for a BI analyst.

Definitions & purpose:- OLTP normalized schemas (3NF) are optimized for transactional integrity: many narrow tables, foreign keys, minimal redundancy. Good for inserts/updates.- OLAP dimensional schemas (star/snowflake) are optimized for analytical queries: fact tables + denormalized dimensions for fast aggregations and simple joins.

Trade-offs:- Data freshness: OLTP is real-time by nature; OLAP typically lags due to ETL. Near-real-time ELT/streaming can reduce latency but adds complexity.- Query complexity: OLTP queries require many joins and are expensive for aggregates. Dimensional schemas simplify queries (single fact + dims), enabling fast dashboard performance and easier self-service.- Storage: Normalized OLTP is space-efficient. Dimensional models store denormalized data and pre-aggregations, increasing storage but improving read performance.- Concurrency: OLTP handles high write concurrency with row-level transactions. OLAP workloads are read-heavy; running large analytical queries on OLTP can cause contention and degrade transactional performance.

Hybrid approach (practical design):1. Transactional source: keep full OLTP as system of record.2. Change capture: use CDC (Debezium, AWS DMS) to stream changes to a staging zone (raw event/log table).3. Near-real-time operational layer: populate a narrow “operational reporting” schema (lightly transformed, event-time indexed) in a fast store (Postgres, ClickHouse, or Snowflake micro-partitioned table) refreshed continuously—supports dashboards that need minute-level freshness and simple aggregations.4. Analytical layer: run scheduled ELT into dimensional model (star) in a data warehouse for heavy aggregations, historical analysis, and BI semantic layer. Use incremental transforms and materialized views for expensive metrics.5. Serving & orchestration: use a query routing layer (BI tool connection or data virtualization) to direct operational dashboards to the operational layer and deep analytics to the dimensional warehouse; implement a semantic layer (LookML/Power BI datasets) to unify metrics.6. Performance optimizations: add materialized aggregates, partitions, columnar storage for facts; cache frequently used visuals; SLA-based retention policies to balance storage.

Why this works:- Preserves transactional performance while enabling minute-level operational insights.- Keeps long-term, compute-heavy analytics isolated, enabling complex ad-hoc queries without impacting OLTP.- Provides a single metric definition via semantic layer, reducing inconsistencies.

Considerations & trade-offs:- More infrastructure and orchestration complexity (CDC, streaming, transform jobs).- Storage grows (raw + operational + dimensional); control via retention and aggregated rollups.- Need robust data quality, reconciliation, and monitoring to ensure consistency across layers.

As a BI analyst, I’d prioritize building the semantic layer and monitoring freshness/consistency SLAs so stakeholders get reliable near-real-time KPIs and accurate deep analysis.

MediumTechnical

29 practiced

Describe indexing and physical design strategies for star schemas in MPP warehouses (e.g., Redshift, Snowflake). Discuss distribution/sort keys, clustering keys, and how to choose them given query patterns that join fact to dimensions on date and product_id and aggregate by region.

Sample Answer

Brief approach: pick physical design to minimize network shuffles and maximize pruning for common predicates (date ranges, product_id joins) while keeping maintenance manageable.

Redshift (MPP):- Distribution key: choose distkey = product_id if joins to product dim on product_id are very frequent and product_id is reasonably high-cardinality and evenly distributed. This colocates matching rows and avoids redistribution. If most queries filter large date ranges but join on product less, consider distkey = date (or even compound key via sort) — but date can be skewed.- Sort key: use sortkey = date (possibly compound sortkey (date, product_id)) so range scans for time windows are fast and data is kept contiguous for aggregations. If you have many point-lookups on product_id, use interleaved sortkeys (product_id, date) to balance.- Other: run VACUUM/SORT and ANALYZE; consider dist style ALL for small dimension tables (product, region) to avoid joins.

Snowflake:- Micro-partitions already cluster by load order; use clustering key on (date) or (date, product_id) only if query pruning is poor. Clustering on date gives efficient range pruning for time-series. Clustering on product_id helps if many selective product lookups and joins.- Use automatic clustering for high churn if available; monitor clustering depth and maintenance costs.

Schema-level optimizations:- Denormalize critical lookup attributes (region) into fact to avoid join cost when region is frequently aggregated.- Create materialized views or aggregate tables by region/date to serve dashboards with heavy-aggregate patterns.- Use distribution/allocation and clustering choices guided by query profile: for joins-heavy workflows prefer colocating keys; for time-window heavy dashboards prioritize sort/clustering on date.

Trade-offs & maintenance:- Dist/cluster selection reduces query time but increases load/maintenance cost (vacuum, clustering). Re-evaluate with query stats, skew detection, and cost-benefit of materialized aggregates.

MediumTechnical

29 practiced

Create a migration plan (high level) to convert a busy orders table from storing full addresses inline to referencing an addresses table (normalization), ensuring no downtime and minimal risk. Include steps for data migration, application compatibility, and rollback.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.