Analytics Infrastructure and Query Performance Questions

Designing analytics data infrastructure and optimizing query performance for analytics workloads. Includes data modeling for analytics, columnar versus row storage trade offs, clustering and partitioning strategies, indexing and materialized views, caching and result reuse, profiling and tuning slow queries, cost and latency trade offs for large scale analytics, and considerations for ingest pipelines and analytical storage choices.

MediumTechnical

25 practiced

BI tools often provide query result caching. Compare tool-level caching versus building server-side pre-aggregates. What are the pros and cons of each approach, and how would you coordinate cache invalidation with data freshness SLAs?

Sample Answer

Tool-level caching vs server-side pre-aggregates

Brief definitions:- Tool-level caching: BI tool stores query results (in-memory or on-disk) for repeated dashboard queries. Usually short-lived, per-user or per-workspace.- Server-side pre-aggregates: Materialized tables or summary tables built in the warehouse/DB (e.g., materialized views, aggregated tables updated on a schedule or via incremental pipelines).

Pros / Cons

Tool-level caching- Pros: - Fast to implement (enabled in BI tool), no infra changes. - Low latency for identical queries and interactive exploration. - Good for low-volume ad-hoc re-runs and user-specific views.- Cons: - Fragile for changing queries or filters (cache miss on parameter changes). - Hard to coordinate across users and tools; limited visibility into cache hit rates. - Memory/storage limits in tool; not suitable for large-scale aggregation. - Cache invalidation often coarse (time-to-live) and can serve stale data.

Server-side pre-aggregates- Pros: - Predictable performance at scale (DB-optimized, indexed). - Reusable across tools and analysts; supports many filter permutations if well-designed (rollups, cubes). - Enables controlled refresh strategies (incremental, streaming).- Cons: - Requires engineering effort: design, pipelines, storage cost. - Can bloat schema if too many aggregates; risk of over-aggregation that loses fidelity. - Longer lead time to change if business questions evolve.

Coordinating cache invalidation with data freshness SLAs (practical approach for a Data Analyst)- Establish SLA tiers: e.g., near-real-time (≤5 min) for operational dashboards, hourly for reporting, daily for strategic analyses.- Map sources → acceptable latency and choose strategy: - For strict SLAs, use server-side pre-aggregates with near-real-time pipelines or event-driven incremental updates; set BI cache TTL shorter than SLA (or disable cache). - For relaxed SLAs, rely on BI caching with TTL aligned to SLA (e.g., TTL = SLA/2) so stale responses are bounded.- Implement single source of truth metadata: - Track last-updated timestamps on tables/materializations and expose them to dashboards (timestamp widget). - BI should check that timestamp and, where supported, invalidate or bypass cache when source timestamp > cache generation time.- Automation and observability: - Automate invalidation when ETL jobs finish (webhooks or API calls to BI to purge specific dashboard caches). - Monitor cache hit rates, query latency, and freshness SLA compliance; alert on violations.- Governance: - Document which dashboards use pre-aggregates vs live queries; include expected freshness. - Coordinate with data engineering for new aggregates and with stakeholders when changing refresh cadence.

Recommendation for a Data Analyst:- Prefer server-side pre-aggregates for high-traffic, repetitive, SLA-sensitive dashboards. Use tool caching for interactive exploration and low-traffic reports. Always surface data timestamps and align cache TTLs and materialization refresh schedules to the agreed SLA, and automate cache invalidation on upstream updates.

HardTechnical

25 practiced

You are leading an initiative to reduce analytics query costs by 30% across the organization. Outline an actionable plan that includes stakeholders, metrics to track, quick wins (low effort/high impact), medium-term refactors, and long-term cultural/process changes to sustain savings without hurting data quality.

Sample Answer

Situation: Our org’s analytics spend has grown rapidly; as lead analyst I was asked to cut query costs by 30% while preserving data quality and stakeholder trust.

Task: Build and execute an actionable program that delivers measurable cost savings across reporting, ad-hoc analysis, and BI consumption.

Action:- Stakeholders & roles - Data Engineering: implement platform-level changes (materialized views, caching). - BI/Data Analytics (my team): identify high-cost queries, refactor SQL, educate analysts. - Finance/Cloud Ops: validate cost metrics, enable tagging and chargeback. - Product/Ops/Business SMEs: prioritize reports and accept trade-offs. - IT/Governance: enforce access controls and SLAs.

- Metrics to track (weekly/monthly) - Total analytics query cost ($) and % change vs baseline - Cost per dashboard / report and top 20 cost-contributing queries (Pareto) - Query count, average runtime, bytes scanned per query - Cache/materialized view hit rate, percentage of scheduled vs ad-hoc queries - Number of expensive queries optimized and time-to-resolution - Business impact metrics (report usage and stakeholder satisfaction)

- Quick wins (low effort / high impact) - Identify top 10 costliest queries (by bytes scanned / $) and apply simple fixes: add predicates, limit columns, push filters earlier. - Disable or reschedule very expensive dashboards to off-peak hours; memoize daily snapshots for dashboards that don’t need realtime. - Set default query limits and result sampling for ad-hoc sandboxes. - Enable cost-tagging and alerting for queries > threshold. - Implement a “query review” Slack workflow where any >X-hour query requires approval.

- Medium-term refactors (weeks–months) - Build aggregated tables / materialized views for common rollups; replace frequent heavy joins with pre-joined datasets. - Introduce query templates and a library of vetted, cost-efficient SQL patterns. - Move heavy ETL work upstream into scheduled batch transforms (Data Engineering collaboration). - Implement BI layer extracts for high-traffic dashboards (precomputed extracts in Tableau/Power BI). - Automate identification + ticketing for expensive queries; set SLA to refactor within N days.

- Long-term cultural & process changes (quarter+) - Governance: enforce data-product ownership, SLAs, and lifecycle for datasets (deprecate unused tables). - Cost-awareness training for analysts: teach cost-effective SQL, explain cloud billing drivers. - Chargeback or showback model: monthly cost reports per team to incentivize optimization. - Measurement-driven incentives: include query-cost KPIs in team OKRs. - Observability: maintain dashboards for query costs, usage, and quality; run quarterly audits. - Continuous improvement forum: monthly “cost clinic” where analysts present reductions & patterns.

Result:- Expected: 30%+ cost reduction within 3–6 months from combined quick wins and medium refactors, with sustained reductions from governance and culture change.- How success is validated: sustained drop in total analytics spend, preserved or improved dashboard usage and stakeholder NPS, fewer runaway queries, and visible increase in materialized view/cache hit rates.

This plan balances fast savings with structural fixes and culture change to keep costs down without degrading data quality.

MediumTechnical

26 practiced

Explain how encoding techniques (dictionary, run-length, delta) and compression interact with columnar formats. For a low-cardinality column like country_code and a high-cardinality column like user_id, recommend effective encodings and explain why they improve performance.

Sample Answer

Columnar formats (like Parquet) store values by column which lets encodings and compression be applied per-column for big wins: they reduce disk I/O, improve cache locality and speed vectorized operations.

Encodings overview:- Dictionary encoding: map distinct values to small integer ids. Very effective for low-cardinality columns because ids are small and repeated; enables further compression (bit-packing, RLE) and faster comparisons.- Run-length encoding (RLE): stores repeated consecutive values as (value, count). Great when identical values cluster (sorted or partitioned data).- Delta encoding: store differences between successive values (or between values and a base). Works well for monotonic numeric sequences (timestamps, increasing IDs).

Recommendations:- country_code (low cardinality, e.g., ~200 values): Use dictionary encoding + optionally RLE if data is partitioned/sorted by country. Dictionary reduces storage drastically; small integer ids compress well and speed up filter predicates (predicate pushdown uses ids). Combined with column compression (snappy/zstd) yields small files and fast reads.- user_id (high cardinality, many unique values): Avoid dictionary (would be as big as raw). Use delta encoding if user_id is numeric and roughly monotonic (e.g., auto-increment); otherwise store as plain fixed-width or use byte-level compression (ZSTD). Parquet’s hybrid approach (bit-packing + optional delta) plus efficient compression gives best IO/CPU tradeoff.

Why performance improves:- Less disk read (smaller files), better cache use, fewer CPU cycles in scans.- Encodings enable vectorized processing and faster predicate evaluation (dictionary id comparisons).- Choosing encoding by cardinality and data distribution prevents costly encoding overhead (e.g., dictionary build on high-cardinality).

Edge cases:- If user_id is random UUID, delta won’t help—use plain bytes + strong compressor.- If country_code has many nulls, consider null-bitmap + dictionary.

HardSystem Design

20 practiced

Design an ingestion pipeline to handle 100k clickstream events per second for analytics. Include choices for transport (Kafka/PubSub), landing storage (S3), buffering, schema enforcement, deduplication, backpressure handling, and how to support near-real-time analytics and reprocessing.

Sample Answer

Requirements:- Ingest 100k click events/sec (peak), low latency for near-real-time analytics (<= 5s), durable landing on S3 for historical/reprocessing, schema enforcement, deduplication, backpressure handling, ability to reprocess from raw.

High-level architecture:Clients (web/mobile) → Edge collectors / CDN logs → Ingress (HTTP batching/gRPC) → Messaging layer (Kafka or Pub/Sub) → Stream processors (Flink/Beam/Kafka Streams) → Near-RT materialized views / OLAP stores (ClickHouse, BigQuery) → Cold/raw store (S3 partitioned by date/hour + compressed Parquet) → Batch ETL for reprocessing → BI tools.

Choices & rationale:- Transport: Kafka (self-managed) or Pub/Sub (managed). Both support 100k/s; choose Pub/Sub for less ops, Kafka for fine-grained control (exactly-once with Kafka Streams + transactional producers).- Landing storage: S3 (or GCS) as immutable raw lake. Write via buffering writers that produce time/partitioned Parquet files (snappy) for query efficiency.- Buffering: On clients use batched sends (50–500 events), ingress collectors buffer into producer batches. Messaging layer retentions allow consumer replay.- Schema enforcement: Use Avro/Protobuf + Confluent Schema Registry (or equivalent). Validate at producer and again at stream processor; malformed messages routed to a dead-letter topic/S3.- Deduplication: Include event_id + client_timestamp. Use stream processor windowed state (Flink/Beam) with state TTL to drop duplicates within acceptable window; also persist event_id hashes to a Bloom filter or external dedupe store for larger windows. Use Kafka exactly-once semantics where available.- Backpressure: Clients detect 429; collectors expose adaptive rate limiting and local buffering. Messaging layer throttles producers; stream processors scale horizontally and use consumer-lag monitoring to autoscale. Use tiered storage: if real-time processors lag, continue writing raw events to S3 for later batch processing.- Near-real-time analytics: Stream processors emit aggregated metrics (minute-level) into an OLAP store (ClickHouse/BigQuery) and a materialized view service (pre-aggregations). Dashboards read from these stores for sub-5s freshness.- Reprocessing: Raw immutable Parquet on S3 is the source of truth. Maintain catalog (Hive/Glue) with partitions. To reprocess, run batch jobs (Spark/Beam) reading historical partitions, apply updated business logic or schema migrations, and write new derived datasets and update downstream stores.

Scalability & trade-offs:- Kafka gives lower-latency control; Pub/Sub reduces ops. Bloom filters/stateful dedupe trades memory for dedupe window length. Prioritize durability (S3 raw landing) to ensure reprocessing even under heavy load. Monitor producer latency, consumer lag, schema violations, and SLOs; add autoscaling and circuit-breakers.

MediumTechnical

18 practiced

You have a 1 TB CSV dataset that you convert to Parquet with Snappy compression and columnar encoding. Estimate the likely storage size after conversion and explain factors that influence the compression ratio (cardinality, data types, null density). Provide a rough numeric bound and justify your assumptions.

Sample Answer

Short answer: converting 1 TB of CSV to Parquet with Snappy will usually shrink it by ~3–10×. A reasonable rough bound is 100–350 GB in typical cases; with very favorable structure you might get down to 50–100 GB, and with very adversarial (high-cardinality free text) data you might only reduce to 400–700 GB.

Why these ranges (assumptions + justification):- CSV is plain text with repeated schema and delimiters; Parquet is columnar and uses encodings (dictionary, run-length, bitpacking) plus per-column compression. Snappy is fast but moderate compression (worse ratio than gzip/zstd).- Typical case assumption: mixed schema (numeric + low- to medium-cardinality dimension strings + timestamps), many repeated values/nulls, and some wide columns → dictionary+columnar encoding compresses well → 3–8× savings → 125–333 GB.- Favorable case: many integers/floats, lots of repeats/nulls, low cardinality strings → dictionary and delta/bit-packing excel → 10–20× → 50–100 GB.- Adversarial case: mostly long free-text fields, high cardinality, low repetition → columnar still helps somewhat but Snappy gains are limited → ~1.5–2.5× → 400–700 GB.

Key factors that influence ratio:- Cardinality: low-cardinality columns get dictionary encoded and compress extremely well; high-cardinality unique strings defeat dictionary.- Data types: binary/numeric types stored compactly (fixed width, delta/bitpack); strings in CSV are much larger.- Null density: many nulls compress efficiently (Parquet has efficient null bitmaps).- Column width and distribution: repeated values and sorted data improve delta/RLE effectiveness.- Chunk/row-group size and file layout: larger row groups improve encoding efficiency; small files add overhead.- Compressor choice: Snappy sacrifices ratio for speed; zstd/gzip give better compression at higher CPU cost.

Example quick calc (typical case):- 1 TB CSV → assume 6× reduction → ≈170 GB.That’s a pragmatic estimate for business ETL where speed matters and schema includes a mix of numerics and categorical strings.

Unlock Full Question Bank

Get access to hundreds of Analytics Infrastructure and Query Performance interview questions and detailed answers.

Join thousands of developers preparing for their dream job.