Scalable Data Architecture and Modeling Questions

Design data architectures and data models that support high performance analytics and dashboards at scale. Topics include schema design patterns such as star and snowflake schemas, normalization versus denormalization trade offs, indexing and partitioning strategies, materialized views and aggregation layers, appropriate use of real time versus scheduled batch updates, storage and file format considerations, query pattern driven modeling, handling large volumes and high concurrency, refresh and latency trade offs, monitoring and performance tuning, cost versus performance trade offs, and data governance and lineage to ensure correctness and maintainability. Interview candidates should be able to reason about architecture decisions in the context of query performance, update cadence, concurrency, and operational constraints.

HardSystem Design

35 practiced

Design an analytical data architecture to support the following constraints: 1) 1 TB ingested per day, 2) 10 million analytical queries per day with 5k concurrent dashboards during business hours, 3) sub-second median dashboard latency for key reports, and 4) cost budget cap. Sketch major components (ingest, raw zone, curated warehouse, serving/cache layer), explain where pre-aggregation happens, and describe how you'd scale to handle concurrency.

MediumTechnical

28 practiced

You have a fact table with 2 billion rows of events. Typical queries filter by event_date (recent ranges) and occasionally by user_id. Propose a partitioning and indexing/clustering strategy for this table in a cloud data warehouse (BigQuery/Snowflake/Redshift) and justify your choices based on typical query patterns and cost implications.

Sample Answer

Recommendation summary:- Partition by event_date (date or date_shard) using native time partitioning.- Add secondary clustering/sort on user_id for selective user queries.- Use different implementations per warehouse: BigQuery — date partitioning + clustering on user_id; Snowflake — range partitioning (event_date) via automatic micro-partitions and create a clustering key on (event_date, user_id); Redshift — distribution key on user_id if joins by user are frequent, sort key on event_date (compound or interleaved depending on query mix).

Why:- Most queries filter recent date ranges: partitioning by event_date prunes I/O and dramatically reduces scanned data and cost.- Occasional user_id filters benefit from clustering/sort: colocating rows for the same user speeds point/limited scans without paying full table scan.- BigQuery clustering reduces bytes scanned (charged) when filters match clustered columns. Snowflake clustering (automatic re-cluster) improves pruning and elimination of micro-partitions. Redshift sort keys enable range/point scan speed; choosing dist key on user_id reduces shuffle during joins if user-centric joins are common.

Cost/operational trade-offs:- Partitioning reduces query cost but can increase storage metadata and maintenance (especially small partitions). Use daily partitions for recent hot data, consider weekly for very old historical ranges to avoid many tiny files.- Clustering/re-clustering has maintenance cost: in BigQuery it’s automatic at query time, Snowflake charges for re-clustering if enabled, Redshift requires VACUUM/SORT or WLM tuning.- For Redshift, avoid choosing user_id as dist key if user cardinality is extreme and causes skew; consider EVEN or key on a moderate-cardinality join column.

Additional notes:- Monitor query patterns and adjust: if user_id queries increase, promote user_id to distribution/sort; if most queries add more predicates (country, event_type), extend clustering keys to include those.- Keep retention/archival: move older partitions to cheaper storage or separate historical table to lower storage and compute costs.

MediumTechnical

36 practiced

Schema evolution: explain how you would handle adding a new non-nullable column to a wide fact table that already has many partitions and downstream dashboards. Consider compatibility across ETL, BI tools, and historical data. Provide a step-by-step migration plan to minimize downtime and incorrect results.

Sample Answer

High-level goal: add a new non-nullable column to a wide, partitioned fact table without breaking ETL, BI dashboards, or historical reports. Minimize downtime and ensure correctness via a staged, reversible rollout.

Migration plan (step-by-step):

1. Design & default semantics- Decide default value/meaning for existing rows (e.g., 'unknown', 0, false) and whether business logic allows it.

2. Add nullable column first- ALTER TABLE fact ADD COLUMN new_col TYPE NULLABLE;- Deploy ETL changes to populate new_col for new data only (write path updated).

3. Backfill historical partitions incrementally- Create backfill jobs per partition in low-traffic windows. Example SQL:

sql

UPDATE fact
SET new_col = <derived_value_or_default>
WHERE partition_date = '2025-01-01' AND new_col IS NULL;

- Monitor job performance, run samples and row counts.

4. Dual-read for dashboards and tests- Update BI layer (views or semantic layer) to coalesce(new_col, default) so dashboards are correct during backfill.- Run shadow queries comparing old logic vs new_col-derived values on sampled reports.

5. Verify & QA- Data quality checks: row counts, null percentage, key aggregations before/after.- Ask stakeholders to validate critical dashboards.

6. Make column non-nullable- Once all partitions backfilled and tests pass, ALTER TABLE ... SET NOT NULL (or add constraint) during low-impact window.

7. Clean up & communicate- Remove coalescing workarounds in BI, retire backfill scripts, document change and update ETL runbooks.

Rollback plan:- If data issues: revert ETL writes to stop populating, set BI to use original logic, and re-run backfill after fixes.

Notes on compatibility:- Use views/semantic layer so BI tools need minimal change.- Incremental backfill avoids long locks on large partitions.- Automate validations and alert on regressions (counts, nulls, ambassador dashboards).

EasyTechnical

30 practiced

Compare columnar file formats (Parquet/ORC) to row-based formats (CSV/Avro) for analytics workloads. For an analytics pipeline ingesting 5 TB/day with interactive BI queries, explain how format choice, compression, and column pruning affect query performance and storage cost.

EasyTechnical

54 practiced

Describe Slowly Changing Dimension (SCD) types 1, 2, and 3. Give concrete examples of attributes (e.g., customer email, billing address, loyalty-tier) for each SCD type and explain how using SCD type 2 affects storage, keys, and queries for historical analysis.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.