Data Architecture and Pipelines Questions

Designing data storage, integration, and processing architectures. Topics include relational and NoSQL database design, indexing and query optimization, replication and sharding strategies, data warehousing and dimensional modeling, ETL and ELT patterns, batch and streaming ingestion, processing frameworks, feature stores, archival and retention strategies, and trade offs for scale and latency in large data systems.

HardTechnical

45 practiced

Compare sharding versus replication strategies for an analytical datastore that must support heavy ingestion and high-concurrency reads. For a global company with 1000s of dashboards and peak concurrency in business hours, which approach would you recommend and why? Discuss cross-shard joins, rebalancing, and operational complexity.

Sample Answer

Definition & trade-offs:- Sharding splits data horizontally across nodes by key (e.g., customer_id, region) to scale write throughput and per-node storage. Replication copies the same data to multiple nodes to increase read concurrency and fault tolerance.Recommendation for this global BI use-case:- Prioritize a hybrid: logical sharding by natural high-cardinality dimension (region or tenant) to scale ingestion and storage, plus read replicas per shard (or region) to serve high-concurrency dashboard queries locally. This balances write scaling and read concurrency while keeping latency low for global users.

Cross-shard joins:- Pain point: analytical queries that join across shards become expensive (distributed joins) or require data movement. Mitigate by: - Choosing shard key aligned with most join/filter patterns (e.g., shard by customer or business unit). - Maintaining denormalized materialized views or precomputed aggregates (rollups) per shard. - Using a query layer that can federate results efficiently or run async ETL to a central analytics store for cross-shard global aggregates.

Rebalancing:- Shard rebalancing during growth causes background data movement and ingestion impact. Reduce risk by: - Using consistent hashing or range sharding with minimal reshuffling. - Implementing online rebalancing with throttling and monitoring. - Designing shard boundaries along stable dimensions (region, tenant tiers).

Operational complexity:- Sharding increases operational overhead: routing, monitoring per-shard health, backups, schema migrations, and handling distributed joins.- Replication is simpler for reads but doesn't solve write scale or cross-region latency by itself.- Hybrid adds complexity but is pragmatic: automate deployment, observability, and have prebuilt materializations to avoid ad-hoc distributed queries.

BI implications:- For thousands of dashboards, favor availability and low-latency reads—use replicas close to users and materialized aggregates for common dashboards. Reserve cross-shard heavy ad-hoc analysis for a central data warehouse or dedicated OLAP cluster.

Summary:- Use sharding to scale ingestion + per-shard read replicas for concurrency; mitigate cross-shard joins via careful shard key choice and precomputed aggregates; invest in automation to manage rebalancing and operational complexity.

MediumTechnical

58 practiced

For a fact_sales table with 50 billion rows, propose a partitioning and clustering strategy (e.g., daily partitions, clustering on product_id) to optimize common BI queries like time-based reporting and top-N product lists. Explain trade-offs and maintenance tasks required.

Sample Answer

Requirements: optimize time-based reporting (daily/weekly/monthly rollups) and fast Top-N product queries across 50B rows while controlling cost and maintenance.

Proposal:- Partition by DATE(sale_date) — daily partitions to match BI time filters.- Cluster (sort) on (product_id, ds) or (product_id, store_id) depending on query patterns. For BigQuery: cluster by product_id, store_id. For columnar warehouses like Redshift: sortkey (product_id, sale_date).- Keep an aggregate (daily_sales_by_product) table pre-aggregated for heavy Top-N queries to serve dashboards quickly.

Example (BigQuery):

sql

CREATE TABLE analytics.fact_sales
PARTITION BY DATE(sale_date)
CLUSTER BY product_id, store_id AS
SELECT * FROM raw.sales;

Why this works:- Partition pruning dramatically reduces scanned data for time-windowed reports.- Clustering co-locates rows for a product, improving Top-N and GROUP BY performance and reducing I/O for selective queries.- Pre-aggregates avoid scanning base table for frequent Top-N use cases.

Trade-offs:- Too-fine partitions (hourly) increase metadata overhead; too-coarse (monthly) hurts partition prune effectiveness for daily reports.- Clustering incurs maintenance (re-clustering / data drift). Insert-heavy workloads can reduce clustering effectiveness over time.- Aggregates add storage and ETL complexity but reduce query cost/latency.

Maintenance tasks:- Monitor partition sizes and access patterns; consider switching to weekly partitions if daily partitions are tiny.- Re-cluster or run OPTIMIZE/REINDEX jobs periodically (or rely on automatic re-clustering if supported).- Maintain/upsert strategy: write to staging partitions and run compaction jobs to avoid many small files.- Refresh daily/near-real-time aggregate tables and maintain partition expiration policies for cold data.- Update statistics and monitor query plans; add secondary clusters/sort keys if workload shifts.

Metrics to monitor: average scanned bytes per query, top query latency, most-frequent filters, partition growth, and clustering depth/efficiency.

EasyTechnical

71 practiced

List common automated data quality checks a BI team should run on ingestion and transformed data (for example null checks, range checks, duplicate detection). For each check, briefly explain what failure modes it catches and how a BI analyst should respond to alerts.

Sample Answer

Below are common automated data-quality checks a BI team should run on ingestion and transformed data, what failure modes they catch, and recommended analyst responses.

- Null / Missing Value Checks - Catches: missing required fields (e.g., order_id, user_id), upstream schema changes, incomplete loads. - Response: verify source extraction logs, check recent schema changes, contact data owner; if transient, backfill; if schema change, update ETL and dashboards.

- Type / Schema Validation - Catches: wrong data types, added/removed columns, unexpected JSON structure. - Response: inspect upstream producer, revert breaking deploys or adjust parser; add automated schema registry alerts.

- Range / Business-Logic Checks - Catches: out-of-range values (negative prices, percent >100), unit mismatches. - Response: flag affected rows, investigate transformation logic and source units, apply fixes and reprocess if needed.

- Row Count / Volume Monitoring - Catches: complete misses or partial loads, sudden drops/spikes. - Response: check ingestion pipeline, upstream availability, and schedule reruns; notify stakeholders if SLA missed.

- Duplicate Detection (unique key / primary key violation) - Catches: double ingestion, idempotency failures, replays. - Response: deduplicate, identify root cause (retries, CDC issues), patch producer or consumer.

- Referential Integrity / Foreign Key Checks - Catches: orphan records (e.g., transactions without users), late-arriving dimensions. - Response: hold suspect records in quarantine, request dimension backfill, or apply late-binding joins.

- Distribution / Drift Monitoring (histogram / cardinality changes) - Catches: concept drift, new categories, bot traffic, sampling changes. - Response: review recent releases/marketing campaigns, adjust models/dashboards, or escalate if impacting KPIs.

- Freshness / Latency Checks - Catches: stale data, delayed pipelines. - Response: check ETL jobs, resource contention, and re-run; inform consumers of outage impact.

- Aggregation / Checksum Validation - Catches: incorrect aggregations after transforms, data corruption. - Response: recompute from source, compare checksums, fix transformation logic and reprocess.

- Null Ratio/Completeness Thresholds for KPI Columns - Catches: degrading completeness that can bias metrics. - Response: alert product owners, lower-confidence flags on dashboards, and backfill where possible.

For alerts: include context (previous baseline, sample offending rows, job IDs, timestamps), severity labels, and suggested next steps. Maintain runbooks describing ownership, triage steps, and rollback/reprocess procedures so analysts can resolve or escalate quickly.

HardTechnical

49 practiced

Your streaming sink is creating millions of small Parquet files in the data lake, causing slow queries and high cost. Propose architectural and operational solutions (file format, batching, compaction, streaming-to-warehouse alternatives) to reduce small-file overhead and improve query performance.

Sample Answer

Situation: Our streaming sink is producing millions of small Parquet files, which fragments parquet row groups, blows up metadata, increases cloud storage and query latency — bad for dashboard freshness and cost.

Proposal (architectural + operational):

1. Use an ACID table format (architecture)- Move from raw file-per-event Parquet to a managed table format that supports atomic writes and compaction: Delta Lake, Apache Hudi or Iceberg. Benefits: built-in small-file detection, metadata pruning, and transactional merges so downstream BI queries are consistent.

2. Batch + micro-batch writes (architecture)- Switch streaming jobs from record-at-a-time to micro-batches (e.g., 1–5 min windows or size-based triggers) so each write produces a larger file.- Aim for target file sizes 128–512 MB (cloud defaults: 128–256MB) to keep Parquet row-group efficiency.

3. Partitioning, bucketing and layout (architecture)- Partition by high-cardinality time grain (date/hour) but avoid tiny partitions (hourly for high-volume, daily for lower volume).- Use clustering/Z-order (if supported) or bucketing on common BI filter columns (customer_id, region) to reduce scan.

4. Compaction / Optimize jobs (operational)- Schedule background compaction jobs: small-files -> larger files during low-usage windows. Use Delta OPTIMIZE, Hudi compaction, or Spark coalesce/repartition + overwrite.- Implement incremental compaction (hot partitions first), and retain original raw stream for reprocessing if needed.

5. Streaming-to-warehouse alternatives (architecture)- For near-real-time dashboards, push aggregated/denormalized results directly into a columnar warehouse (BigQuery, Snowflake, Redshift) via streaming APIs or change-data-capture. Warehouses handle ingestion and query performance better for BI.- Or use a hybrid: stream raw to lake + CDC/ETL that writes curated tables into the warehouse for dashboards.

6. Operational controls & monitoring- Instrument metrics: avg file size, file count per partition, number of files created per hour, query planning time.- Alerts if avg file size < threshold or file-count growth spikes.- Automate retention / vacuum: remove tiny transient files older than X hours.- Keep schema evolution controlled and maintain table compaction logs.

7. Query-side optimizations (business-facing)- Encourage BI users to hit curated aggregated tables / materialized views rather than raw-event tables.- Use semantic layer / cubes that refresh on compaction completion to avoid scanning many small files.

Expected outcomes:- Fewer files, larger contiguous Parquet files → lower metadata overhead, faster planning and scanning, lower storage API costs.- More predictable query latency for dashboards and reduced compute cost.- Near-real-time needs met via streaming aggregates or warehouse streaming; raw lake retained for full-fidelity analytics.

If helpful, I can outline a phased runbook (quick wins: increase micro-batch interval, add nightly compaction; medium: migrate to Delta/Hudi; long: push curated tables to warehouse).

HardTechnical

53 practiced

Evaluate performing heavy aggregations in ELT (warehouse SQL, e.g., dbt) versus delegating them to a specialized OLAP engine (ClickHouse/Druid). Discuss performance, scalability, operational overhead, cost, and migration considerations for a BI team.

Sample Answer

Brief answer: Both approaches are valid; choice depends on query patterns, scale, cost tolerance, and team skills. Below is a structured comparison and practical recommendation for a BI team.

Performance- Warehouse SQL (dbt): Excellent for moderately sized, concurrency-light analytical workloads and for transformations that benefit from ACID consistency. Performance depends on cluster size, materialized views/tables, and indexes; complex rollups on very large raw tables can be slow.- OLAP engine (ClickHouse/Druid): Designed for sub-second aggregations on high-cardinality, high-throughput time-series or event data via columnar storage, vectorized execution, pre-aggregations (Druid), and aggressive compression.

Scalability- Warehouse: Vertical or managed horizontal scaling; often more expensive at scale and can hit concurrency limits during many interactive dashboards.- OLAP: Built for horizontal scale and massive ingest; better for many concurrent users and high-cardinality group-bys.

Operational overhead- Warehouse: Lower if using managed cloud warehouses; dbt handles transformations and testing easily. Simpler stack for small teams.- OLAP: Higher ops (cluster tuning, ingestion, rollups, retention). Managed OLAP reduces this but still requires schema/design expertise.

Cost- Warehouse: Predictable but can be expensive for large scan-heavy aggregations and long query times.- OLAP: Potentially lower per-query cost at scale due to efficient storage/CPU usage, but adds infrastructure or managed service bills and extra ETL complexity.

Migration considerations- Data modeling: Need to redesign some transforms into rollups/segments/pre-aggregations.- Data consistency and latency: OLAP may be near-real-time; if strict transactional consistency required, warehouse may remain ground truth.- Tooling & BI integration: Ensure connectors, SQL dialect differences, and semantic layer (LookML/Looker/PowerBI datasets) are updated.- Skills & governance: Train team in OLAP tuning; maintain lineage and testing (dbt can still manage upstream ELT).

Recommendation for a BI team- Start by profiling queries: If >10–20% of dashboards perform large scans/group-bys or latency >5–10s under scale, evaluate OLAP.- Hybrid approach: Keep raw/cleaned canonical tables in warehouse (managed by dbt) and push hot, high-cardinality aggregates or event streams to OLAP for interactive dashboards. This balances developer productivity, governance, and interactive performance.- Pilot with one use case, measure cost/ops, then expand with clear migration plan, monitoring, and updated semantic layer.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.