Data Infrastructure and Architecture Experience Questions

A prompt to describe the candidate's hands on experience building and operating data infrastructure. Candidates should be prepared to discuss specific pipelines, ETL or ELT systems, streaming frameworks, data warehouses and lakes, the scale of data processed, tooling and platforms used, performance and cost trade offs they made, monitoring and data quality practices, incidents or scalability challenges they addressed, and measurable outcomes or improvements resulting from their work.

EasyTechnical

71 practiced

Compare Tableau, Power BI, and Looker for enterprise BI usage. Discuss when to use a semantic or metrics layer (for example LookML or dbt metrics) versus defining calculations in the BI tool, with respect to governance, reusability, performance, and self-service for analysts.

Sample Answer

High-level comparison:- Tableau: Strong visualization flexibility and analyst-friendly authoring. Good for exploratory analysis and executive dashboards. Weaker native governance for semantic consistency unless paired with a governed data source; can centralize via Tableau Prep/Server catalogs.- Power BI: Tight Microsoft ecosystem integration, affordable, good for self-service and embedded reporting. Offers dataflows and shared datasets for reuse; governance improves with Fabric/Power BI Premium and Azure AD.- Looker: Built around a semantic model (LookML). Excellent for governed, reusable metrics and consistent business logic across reports; less visual freedom than Tableau but strong for scale and metric consistency.

Semantic/metrics layer vs BI-tool calculations:- Governance: Use semantic layer (LookML, dbt metrics) to centralize business logic and enforce single source of truth. This reduces metric drift vs ad-hoc BI-tool calculations.- Reusability: Metrics in a semantic layer are reusable across teams and tools; BI-tool calculations are brittle and duplicated.- Performance: Pushing calculations to the warehouse (dbt/LookML) leverages DB performance and avoids row-level processing in the client; BI-tool extracts can be faster for small datasets but scale poorly.- Self-service: Balance is key — provide curated, documented semantic metrics for common KPIs while allowing analysts sandboxed calculated fields in the BI tool for exploration. Encourage templates and certified datasets to guide users.

Recommendation for a BI Analyst:- If organization needs consistency at scale: prioritize semantic/metrics layer (LookML or dbt metrics) and use Looker or Power BI with certified datasets.- If rapid visual exploration is primary: Tableau or Power BI with strong data governance practices (shared datasets, data catalog) plus a backend metrics layer where possible.- Operational rule: “Metric in the semantic layer for reporting; ad-hoc in-tool for exploration.”

MediumTechnical

70 practiced

How do you decide whether to pre-aggregate metrics (materialized tables/views) or compute them on-the-fly for interactive dashboards? Describe criteria including query patterns, cardinality, freshness requirements, compute cost, and give a hybrid caching/materialization strategy with eviction rules and refresh cadence.

Sample Answer

Start by mapping requirements to trade-offs: query patterns, cardinality, freshness, and compute cost.

Decision criteria- Query patterns: If dashboards run repeated, identical aggregations (same group-bys, filters, time windows) with many concurrent users, pre-aggregate is preferred. If queries are ad-hoc, highly dynamic, or use arbitrary dimensions, compute-on-the-fly.- Cardinality: Low-to-moderate cardinality dimensions (date, region, product category) are excellent for materialization. High-cardinality (user_id, transaction_id) should generally be queried live or rolled up.- Freshness: Near-real-time metrics (seconds-minutes) push toward streaming incremental views or live computation; daily/ hourly KPIs are good candidates for batch materialization.- Compute cost: If queries are expensive (large joins, scans) and run frequently, materialize to save cost. If compute is cheap or infrequent, avoid storage/maintenance overhead.

Hybrid caching/materialization strategy- Tier 1 (materialized aggregates): Pre-compute canonical aggregates for common dashboards: daily/hourly time series, top-level funnels, regional roll-ups. Store as partitioned, compressed tables (date partition + dimension).- Tier 2 (query cache): Cache recent query results (minutes-hours) for ad-hoc interactive exploration using a fast cache (Redis or BI-tool in-memory).- Tier 3 (live compute): Allow on-the-fly computation for drilldowns into high-cardinality dimensions or one-off analyses.

Eviction rules & refresh cadence- Materialized tables: - Full refresh cadence: daily for non-critical KPIs, hourly for operational dashboards. - Incremental refresh: stream or CDC-based near-real-time for critical metrics (last-hour/last-5-min bucket). - Retention/eviction: keep hot partitions (last 90 days) online; archive older partitions to cheaper storage.- Query cache: - Time-to-live (TTL): 1–5 minutes for highly dynamic dashboards, 15–60 minutes for low-change pages. - Size-based eviction: LRU for caching layer; evict entries not accessed in last N days. - Invalidate on upstream changes: invalidate cached entries when source schema or ETL job fails or when key upstream tables are modified.

Operational practices- Instrument dashboards to log query patterns and latencies; prioritize materialization by frequency × cost.- Maintain a catalog documenting materialized tables, owners, refresh status, and SLAs.- Run cost vs. latency reviews monthly; iterate refresh cadence and materialization scope based on usage and business needs.

This approach balances interactivity, freshness, and cost while giving a clear path to prioritize which metrics to pre-aggregate.

HardSystem Design

96 practiced

Design a streaming analytics pipeline to ingest and aggregate 1,000,000 events per second for real-time dashboards with a 10-second SLA. Specify ingestion layer (e.g., Kafka, Kinesis), stream processing engine (Flink/Beam), aggregation/windowing approach, state backend, nearline storage for quick queries, long-term storage, fault tolerance, and autoscaling considerations.

Sample Answer

Requirements clarified:- Ingest 1,000,000 events/sec, deliver aggregated metrics for dashboards within 10s SLA, support fast ad-hoc queries for recent data and long-term analytics.

High-level architecture:Event producers → Kafka (ingest & buffer) → Stream processing (Apache Flink) → Nearline OLAP (ClickHouse / Apache Druid) for dashboards → Long-term storage (S3 Parquet + Hive/BigQuery) → BI tools (Looker/Tableau)

Choices & reasoning:- Ingestion: Kafka (self-managed or Confluent). Rationale: high throughput, partitioning, retention, consumer parallelism, mature ecosystem. Configure ~200–400 partitions (estimate depends on broker CPU/IO) to distribute 1M eps.- Stream processing: Apache Flink (low-latency, stateful, event-time processing, exactly-once). Use Flink on Kubernetes or YARN.- Aggregation/windowing: Use event-time tumbling windows of 10s for SLA aggregated metrics (counts, sums, rate, distinct estimates). For rolling insights provide sliding windows (e.g., 1m sliding by 10s). Use incremental, combinable aggregations to minimize state/shipping.- State backend: RocksDB state backend with local SSDs + incremental checkpoints to distributed durable storage (S3/GCS). RocksDB handles large state and allows incremental snapshots.- Nearline storage: ClickHouse or Druid for sub-second/second-level reads; write aggregated 10s buckets from Flink into it via bulk/batched upserts. Schema: time-bucketed partitions to speed queries; pre-aggregate to minimize query-time work.- Long-term: Raw events and periodic hourly/daily rollups written as Parquet to S3 for reprocessing, BI, and ad-hoc analytics.- Fault tolerance: Kafka replication factor 3, Flink checkpointing interval 5s, enable savepoints and job restart strategy with high-availability JobManager (multiple replicas). Use exactly-once semantics for sink writes (Kafka transactions or idempotent writes to ClickHouse/Druid). Monitor lag and alert.- Autoscaling: - Ingestion: scale brokers/partitions horizontally; use provisioning based on throughput. - Processing: scale Flink TaskManagers by parallelism mapped to Kafka partitions. Use Kubernetes + KEDA or Flink adaptive batch scaling; enable reactive scaling based on input lag, CPU, and checkpoint duration. - Nearline: scale ClickHouse/Druid clusters (shards/replicas) with data rebalancing.Operational considerations:- Backpressure and flow-control: ensure Kafka retention and producer throttling; tune Flink buffers and checkpoint timeouts.- Observability: metrics (throughput, lag, checkpoint duration), distributed tracing, SLAs for end-to-end latency.- Cost/perf trade-offs: more partitions & TaskManagers reduces latency but increases ops cost; choose batch windows and aggregation granularity that balance dashboard freshness and resource use.Example data flow (simplified):Producers → Kafka(partitions 200+) → Flink(parallelism 200+) apply event-time 10s tumbling → RocksDB checkpoints → write 10s aggregates → ClickHouse (for dashboards) + S3 (raw/parquet)

This design delivers sub-10s aggregated metrics, fast dashboard queries on nearline OLAP, durable raw storage for audits and reprocessing, with fault-tolerance and autoscaling strategies.

MediumTechnical

78 practiced

Describe a testing strategy for ETL/ELT pipelines powering BI: unit tests for SQL and transformation logic, integration tests for pipeline DAGs, end-to-end reconciliation tests for critical metrics, regression tests to catch changes, and test-data generation. Explain where tests should run (CI vs nightly) and how failures should surface to engineers and stakeholders.

Sample Answer

A robust testing strategy for ETL/ELT that powers BI combines fast, focused checks in CI with heavier reconciliation and regression runs on a schedule. Key layers:

1) Unit tests (CI, on every PR)- SQL assertions and transformation logic using dbt tests, SQL unit tests, and small pytest fixtures for stored procedures.- Validate column existence, types, nullability, simple aggregations, and business rules (e.g., order_amount >= 0).- Use lightweight synthetic/sample datasets (seed tables in dbt) to keep feedback <10 min.

2) Integration tests (CI + pre-merge, extended nightly)- Run DAGs or task graphs in a sandbox (Airflow/Kubeflow local executor or ephemeral infra) against representative test data.- Verify table lineage, joins, incremental logic, and failure/retry semantics.

3) End-to-end reconciliation (nightly / cadence)- Recompute critical KPIs from raw sources through to BI layer and compare to canonical metrics (tolerance thresholds).- Backfill/backtest previous windows to detect drift/regressions.

4) Regression tests & data-contract checks (CI + scheduled)- Snapshot critical aggregates; run anomaly detection on changes.- Enforce schema/data-contract checks at source and destination (e.g., column added/renamed triggers a failure).

5) Test-data generation- Use factories to produce edge cases: nulls, duplicates, outliers, late-arriving rows, high cardinality keys.- Maintain representative production-like samples for nightly E2E.

Where tests run- CI (fast unit + integration): on PRs and merges for quick feedback.- Nightly / scheduled: heavy E2E reconciliation, full regression suites, stress tests, and long-running integration across systems.

Failure surfacing- Engineers: Failures block merges for CI; provide clear actionable error messages, failing SQL, diffs, and a reproducible test dataset. Integrate with PR tooling and ticketing (GitHub/GitLab checks, JIRA).- Stakeholders: Use automated alerts for critical KPIs (Slack/email + status page). For non-critical regressions, send summarized daily reports with impact estimates (delta, affected dashboards, time range) and ownership.- Incident workflow: auto-create an incident when E2E checks exceed thresholds; assign on-call data engineer/BI owner; postmortem for persistent failures.

Metrics to monitor testing health- Test coverage for transformations, CI pass rate, mean time to detect (MTTD) and resolve (MTTR) data incidents, false-positive rate of tests.

This approach balances fast developer feedback with broader confidence from scheduled E2E checks and clear escalation to keep dashboards trustworthy.

EasyTechnical

94 practiced

Compare a data warehouse and a data lake for analytics in a BI environment. Explain roles in a BI stack, storage and compute patterns (schema-on-read vs schema-on-write), where to keep raw versus curated data, examples of tooling (Snowflake, BigQuery, S3 + Delta Lake), and the major trade-offs for query performance, governance, and cost.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.