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.

EasyTechnical

35 practiced

What is partitioning in a data warehouse? Explain common partitioning strategies (range/date, hash, list) and give concrete examples of when to use each. Discuss how partitioning affects query pruning, maintenance (e.g., compaction/vacuum), and small-file problems.

Sample Answer

Partitioning in a data warehouse means physically dividing a large table into smaller segments (partitions) based on a key so queries, maintenance, and IO operate on subsets instead of the whole table.

Common strategies and when to use them:- Range / Date partitioning: split by continuous ranges (e.g., dt >= '2025-01-01' and < '2025-02-01'). Best for time-series logs, event data, or daily fact tables where most queries filter on date. Enables fast drop/archive of old data.- Hash partitioning: distribute rows by hash(key) across N partitions (e.g., user_id % 16). Good for evenly spreading write/read load when predicates don’t filter well on ranges—useful for high-cardinality keys and parallelism.- List partitioning: route specific values to partitions (e.g., region IN ('US','CA') vs ('EMEA')). Useful when a small number of discrete categories are common access patterns or have different retention/policies.

Effects:- Query pruning: with good partition keys, the planner prunes partitions and scans only relevant ones, drastically reducing IO and latency (critical for analytics joins/aggregations).- Maintenance (compaction/vacuum): partitions let you compact/vacuum independently, speeding maintenance and reducing lock impact. For append-heavy tables, you can compact recent partitions more frequently. Also easier to run ALTER/REINDEX on smaller units.- Small-file problem: many tiny partitions/files (e.g., per-minute partitions or many micro-files from parallel writes) hurt metadata and read throughput. Mitigate by coalescing partitions, using larger partition granularity (daily vs hourly), or running compaction jobs (e.g., Spark/Delta/Glue compaction) to merge small files.

Practical guidelines for a Data Scientist:- Choose a partition key aligned with typical filter predicates (time for analytics; hashed user_id for lookup-heavy workloads).- Balance partition granularity: fine enough for pruning and lifecycle operations, coarse enough to avoid small-file/metadata overhead.- Collaborate with engineers/DBAs to set compaction schedules and retention policies that match your model training and reporting needs.

MediumTechnical

36 practiced

You manage a large data lake with many producers. Propose a practical governance plan to enforce schema evolution rules and backwards compatibility, including automated tests and CI/CD steps to prevent breaking downstream analytics consumers.

Sample Answer

Requirements & goals:- Enforce safe schema evolution (no silent breaking changes) for producers- Automated verification in CI/CD so bad changes never reach downstream analytics- Fast feedback for producers; visibility for consumers (data scientists)

Governance plan (practical steps):1. Centralize contracts: require all table/file schemas registered in a schema registry (Avro/Protobuf) or metadata catalog (Glue/Atlas) and use a canonical format (Avro/Delta schema).2. Define semantic compatibility rules by data domain: - Backwards compatible: add nullable fields, widen numeric types, add optional enums - Disallowed without migration: removing fields, changing types non-widening, changing primary keys - Versioning policy: major/minor schema versions; major requires migration plan3. CI/CD enforcement for producer changes: - Pre-commit/lint: provide schema linting tools + templates in repo - PR checks: run automated compatibility test against latest registered schema (use avro-tools or compatibility API). Fail PR if incompatible per policy. - Integration test: apply schema to a staging data sink, run a test consumer job (sample notebook/pipeline) that exercises common downstream reads and feature pipelines. - Contract tests: producers publish sample messages/rows; consumer-side contract tests run in pipeline to detect breaking semantic assumptions (e.g., field presence, units). - Canary ingestion: new schema deployed to a shadow namespace; run delta comparisons and alert on metric drift (row counts, null rates, cardinality).4. Monitoring & rollback: - Automated data-quality checks post-deploy (great_expectations): schema conformance, null thresholds, value ranges. - Alerting and automated rollback if checks fail.5. Governance operations: - Required PR template documenting schema change rationale, consumer impact, migration steps - Consumer registry of dependent pipelines and owners; require consumer sign-off for major changes - Quarterly audits and dashboards: compatibility violations, incidents, time-to-fix

Example CI/CD pipeline steps (producer repo):- Pre-merge: schema-lint -> compatibility-check against registry -> unit tests- Merge: publish new schema to staging registry version -> deploy to staging sink -> run consumer integration tests + data-quality suite- Canary: deploy to production shadow stream for 24–48 hours -> monitoring/alerts- Promote: if no failures, mark schema as promoted and notify consumers

Why this works:- Automated compatibility checks prevent most breaking PRs early- Contract + integration tests catch semantic mismatches consumers care about- Canary + monitoring catch runtime issues not visible in static checks- Clear policies + sign-offs handle edge cases requiring migration

Metrics to track:- PR failures due to compatibility (should be low but informative)- Time from schema change to consumer sign-off- Number of production incidents caused by schema changes- Data-quality test pass rate after deployments

As a data scientist, I’d contribute consumer-side contract tests (sample feature pipelines, model inference on sample data) to ensure new schemas don’t silently corrupt features or labels.

EasyTechnical

27 practiced

Compare columnar formats (Parquet, ORC) and row-based formats (Avro, CSV) for analytics workloads. Discuss compression, predicate pushdown, schema evolution, small-file handling, and suitability for ML feature pipelines. Which format would you choose for: (a) large historical analytics, (b) event streaming with small writes?

EasyTechnical

37 practiced

Define OLTP and OLAP. For a data scientist building predictive models and dashboards, why does this distinction matter when choosing where to store data and how to design schemas?

MediumTechnical

33 practiced

Compare three approaches to accelerate high-concurrency dashboards: materialized views, pre-aggregated OLAP cubes, and on-the-fly query execution with caching. For each approach, summarize latency, freshness, maintenance overhead, and cost implications.

Sample Answer

Materialized views- Latency: Very low for reads (precomputed results served directly), predictable sub-second to low-second depending on storage/compute.- Freshness: Depends on refresh policy (incremental/continuous vs scheduled). Near-real-time if CDC/streaming refresh used, otherwise can lag (minutes–hours).- Maintenance overhead: Moderate. Requires defining refresh logic, handling invalidation on schema changes, and monitoring refresh failures. Incremental refresh reduces work but is more complex.- Cost: Moderate — storage for view results + compute to refresh. Cost rises with refresh frequency and data volume but is cheaper than full re-computation per query.

Pre-aggregated OLAP cubes (e.g., Druid, ClickHouse, Cube/Star schema)- Latency: Extremely low for typical aggregations (optimized storage, columnar scanning, indexes). Good for high-concurrency dashboards.- Freshness: Can be near-real-time with streaming ingestion, or batched. Granularity choices affect freshness/accuracy trade-offs.- Maintenance overhead: Higher upfront design (aggregate levels, partitioning, rollups). Ongoing ETL/ingestion pipelines, schema evolution, and cardinality management needed.- Cost: Higher engineering cost + cluster resources for ingestion and storage. But per-query compute cost is low; scales well for many concurrent users.

On-the-fly query execution with caching (memoization, CDN, app-level cache)- Latency: Variable — first-hit latency equals raw query time (potentially high); subsequent hits fast if cached.- Freshness: Freshness depends on cache TTL or invalidation strategy; can be configured per-dashboard or query but risks staleness or cache churn.- Maintenance overhead: Lower initial engineering effort; requires robust cache invalidation, hit-rate monitoring, and possibly query routing/queueing for spikes.- Cost: Lower initial infrastructure cost but can be high under heavy cache miss rates due to repeated compute. Cost-effective when access patterns are skewed (hot dashboards).

Practical guidance for a data scientist- If dashboards need fast, consistent performance and predictable SLAs, choose OLAP cubes for scale and many pre-aggregations.- If data changes frequently but queries are stable, materialized views with incremental refresh strike a balance.- If access is sparse or patterns are highly skewed, start with on-the-fly queries plus smart caching to avoid overprovisioning.- Combine approaches: use cubes or materialized views for heavy/critical dashboards and caching for ad-hoc or low-traffic views. Include monitoring (latency, freshness, cache hit-rate) and cost alerts to guide tuning.

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.