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.

HardSystem Design

26 practiced

Design an approach to implement column-level security and row-level security in your analytics platform so that different roles and tenants see only authorized data. Address strategies that minimize performance overhead: pushdown of predicates to the warehouse, view-based masking, query rewriting, caching considerations, and how to audit correctness without degrading query latency.

Sample Answer

Requirements:- Row-level security (RLS): users/roles/tenants only see permitted rows (e.g., tenant_id, region).- Column-level security (CLS): hide or mask sensitive columns (PII, salary) by role.- Low latency: leverage warehouse compute, avoid app-layer filtering where possible.- Auditability: prove correctness without adding per-query latency.

High-level design:- Centralized Policy Service: stores ACLs, masking rules, tenant mappings, and policy versioning. Exposes a signed, short-lived policy token and an API for policy fetch.- Query Planner / Gateway (in BI layer or middleware): intercepts BI tool queries, authenticates user, fetches effective policies, rewrites queries to push predicates and masking to the warehouse.- Warehouse: enforces most work via parameterized views/virtual tables and role-specific policies (e.g., Snowflake Secure Views, BigQuery Authorized Views, or RLS features).- Audit Log & Async Validator: write-only audit stream of queries + applied policy id; background job replays sampled queries to validate enforcement.

Key strategies:1) Predicate pushdown- Compute effective row predicates at the gateway (user roles → SQL WHERE clauses). Inject them into queries as parameterized, deterministic predicates (e.g., WHERE tenant_id = :t OR region IN (:r...)).- Use parameterized prepared statements to allow warehouse query plan reuse and avoid SQL injection.- Prefer warehouse-native RLS (e.g., BigQuery row-level security, Snowflake masking policies) when available; gateway still asserts and supplies policy IDs.

2) View-based masking & Authorized Views- Create thin authorized views per logical subject area with masking logic defined in the warehouse (conditional CASE/ masking policies) so sensitive columns never leave warehouse unmasked.- Use dynamic data masking features where supported to avoid manual CASE proliferation.- Build a small set of parameterized views (per role group/tenant) rather than per-user to limit object explosion.

3) Query rewriting- Gateway rewrites or layers predicates: FROM base_table -> FROM authorized_view or appends WHERE clauses. Maintain a canonical AST-based rewriter to avoid string concatenation errors.- Add policy metadata comment (/* policy=policy_id v=3 */) to queries so audits can correlate enforcement.

4) Caching- Cache policy results and prepared statements in the gateway with short TTL and invalidation on policy updates. Cache only non-sensitive derived results (e.g., metadata, aggregate caches) and ensure cached data respects the most restrictive policy across users.- Use result caching at the warehouse for identical parameterized queries (prepared statements), enabled by predicate pushdown and parameterization.

5) Auditing & correctness without latency impact- Synchronous: attach policy_id and signature to query; warehouse logs include this metadata (via query comments or session tags).- Asynchronous validation: sample a small percentage of queries and replay them in a separate validation environment using an independent enforcement engine to confirm identical results. Log discrepancies and alert.- Periodic full audits: run scheduled deterministic checks (e.g., for each role, query count and sample results) outside business hours.- Tamper-evident audit log: append-only audit store (e.g., Kafka → immutable data lake) with PolicyService signed events.

Trade-offs and notes:- Full in-warehouse enforcement (RLS/masking) gives strongest guarantees but increases object/role management complexity. Gateway rewriting gives flexibility and centralization but requires rigorous security of gateway and careful SQL AST rewriting.- Parameterization and warehouse-native features maximize cache hit rate and minimize query compilation time.- Avoid application-side filtering of results — it risks leaking sensitive columns and prevents efficient pushdown.- Ensure least-privilege for BI service accounts and rotate keys; enforce network-level restrictions between gateway and warehouse.

Operational concerns:- Monitoring: track cache hit rates, query latencies, audit replay discrepancies, policy change frequency.- Policy lifecycle: CI for policy changes, automated tests (unit for predicates, integration tests that run against a staging warehouse).- Performance knobs: limit predicate complexity, precompute join mapping tables for tenant membership, use materialized aggregates for heavy dashboards with row/col filters baked into materializations.

This design balances security (warehouse-enforced masking/RLS where possible), performance (predicate pushdown, parameterization, caching), and auditability (policy tagging, asynchronous validation) appropriate for BI workflows and compliance needs.

HardSystem Design

25 practiced

You manage a massive fact table with hundreds of millions of unique user_ids. Common queries include point lookups by user_id and cohort analyses across user ranges. Propose an approach using partitioning, hashing/sharding, and clustering to optimize both point and cohort queries while keeping operational maintenance reasonable. Explain trade-offs in query complexity, cross-shard joins, and re-sharding risk.

Sample Answer

Requirements & goals:- Fast point lookups by user_id (low-latency, single-user queries for dashboards/filters).- Efficient cohort scans over ranges or segments (time/cohort windows).- Keep operational complexity low (minimal live re-sharding, easy maintenance).- Work with BI tools (support SQL, pushdown filters, manageable joins).

High-level approach:1. Logical partitioning by date (event_date / ingestion_date) to prune large historical ranges for cohort windows.2. Hash-based sharding on user_id within each date partition to guarantee O(1) point lookup routing.3. Within each shard/partition, use clustering (sorted local index) on (user_id, event_timestamp, cohort_key) to accelerate range scans for cohort queries that filter by user and time span.

Architecture & components:- Ingest -> write into daily partitions (e.g., YYYY-MM-DD) and compute shard_id = hash(user_id) % N -> route to shard file/table (can be physical DB shard or logical shard in a distributed warehouse like BigQuery/Redshift Spectrum/ClickHouse).- Each partition-shard stores data sorted (clustered) by user_id then event_timestamp; maintain local indexes on user_id for fast point lookup.- Metadata service tracks shard map and partition retention; BI tools query a query router that rewrites SQL with partition and shard predicates (or use federated access in your warehouse).

Query patterns:- Point lookup: router computes shard_id from user_id, directs query to single shard + partition(s) (very low latency).- Cohort scan (e.g., users active in period X): query prunes partitions by date range; if cohort is range of user_ids, router can translate to a set of shard_ids and query those in parallel; within each shard the clustering speeds sequential reads for time windows.

Trade-offs:- Query complexity: Cohort queries spanning many shards require fan-out and result-aggregation, increasing latency and network I/O. Mitigate by parallel execution, pushing down predicates, and pre-aggregating time-bucketed materialized views for common cohorts.- Cross-shard joins: Joins between two massive user-centric tables require either co-location (ensure same sharding key) or broadcast small tables. Design all large, frequently-joined tables to use the same user_id sharding to make joins local. If impossible, use staged joins: aggregate per-shard, then merge.- Re-sharding risk: Hash sharding with N fixed has a re-shard pain point. Use a consistent-hash ring or use logical shards (virtual buckets >> physical nodes) so you can move buckets with limited data movement. Alternatively use a distributed warehouse that handles rebalancing automatically.- Operational maintenance: Choose N (shard count) >= expected growth and use virtual buckets to avoid frequent resharding. Keep partition retention policy to bound storage and indexing costs. Automate shard map, backups, and monitoring.

Practical optimizations for BI context:- Create materialized cohort tables for repetitive analyses (rolling 7/30/90-day cohorts).- Expose query templates and pushdown filters in BI dashboards so dashboards provide user_id or date filters to leverage pruning.- Monitor skew (hot users) and implement hotspot handling (cache hot user data, route to replica).

Summary:Date partitions + hash sharding by user_id + local clustering gives very fast point lookups and reasonably efficient cohort scans. The main trade-offs are increased query fan-out and re-sharding complexity; mitigate by co-sharding related tables, using virtual buckets/consistent hashing, materialized aggregates, and routing logic that pushes predicates to storage.

MediumTechnical

20 practiced

Design an SLA and alerting system for data freshness where some datasets must be updated within 15 minutes and others within 24 hours. Describe how you would monitor last_update timestamps, define SLA thresholds and escalation policies, route notifications to responsible owners, and avoid false positives due to upstream delays or maintenance windows.

Sample Answer

Framework: define requirements → implement monitoring → alerting & escalation → safeguards to prevent false positives → operationalize.

Requirements:- Two SLA classes: Near-real-time (15 min) and Daily (24 hrs).- Monitor source and transformed layers (raw ingestion, ETL, reporting).- Clear owner for each dataset (data owner, ETL owner, BI owner).

Design:- Instrument every dataset with last_update timestamp and last_successful_pipeline_run metadata (source_system, partition/key, row_count).- Central freshness service (lightweight job or DB view) runs every 1–5 minutes and computes freshness = now() − last_update. It classifies status: - OK: freshness <= threshold - Warning: threshold < freshness <= threshold + grace (e.g., 25% of threshold, min 5 min) - Breach: freshness > threshold + grace

SLA thresholds & grace:- 15-min datasets: Warning at 15m, Breach at 20m.- 24-hr datasets: Warning at 24h, Breach at 26h.- Per-dataset override possible for business criticality.

Alerting & routing:- Integrate with notification system (PagerDuty/Slack/email).- Alerts include dataset name, layer (source/ETL/report), last_update, last_success_run, recent errors/log links, run IDs, owner(s).- Route to dataset owner; if no ack within 15 min (near-real-time) / 4 hrs (daily), escalate to ETL team lead then BI manager.

Avoid false positives:- Check upstream pipeline health before alerting: if upstream ingestion shows known delay (backpressure/consumer lag) or active maintenance flag, suppress or annotate alerts.- Maintain a maintenance calendar and allow scheduled windows via API toggle; support temporary suppressions with auto-expiry.- Use anomaly filters: if multiple dependent datasets are delayed simultaneously from same source, open a single parent incident rather than many child alerts.- Implement debounce (require two consecutive checks failing) to avoid flapping.

Operationalization:- Dashboards in BI tool showing SLA compliance, MTTR, incident history, and owner contacts.- Runbooks per dataset with triage steps.- Monthly SLA review with stakeholders; adjust thresholds/grace based on business impact and observed false-positive rates.

This balances timely detection, clear ownership, and pragmatic safeguards against noise.

MediumTechnical

18 practiced

Design a refresh strategy for materialized views that power hourly dashboards. The views join a large fact and several dimension tables and must be refreshed every hour with minimal cost. Provide pseudocode or step steps for an incremental refresh using watermarks or CDC, describe concurrency control to avoid double-refresh, and discuss fallback to full refresh and monitoring.

Sample Answer

Situation: You need hourly dashboards backed by materialized views built from a large fact table + dimensions. Goal: refresh each hour with minimal cost, correctness, and robust fallback.

Approach summary:- Use incremental refresh driven by a watermark (last_updated timestamp) or CDC stream to apply only changed rows from fact + changed dimension keys.- Maintain a control table recording last_watermark, refresh_status, started_at, finished_at, and lock_token to avoid double-refresh.- If incremental refresh fails or exceeds a time/cost threshold, fall back to scheduled full refresh during low-cost window and alert.

Pseudocode (incremental using watermark):

python

# control table: mv_control(view_name, last_watermark, status, lock_token)
def incremental_refresh(view_name):
    token = generate_uuid()
    if not acquire_lock(view_name, token):    # atomic compare-and-set
        return "Already running"

    try:
        last_wm = get_last_watermark(view_name)
        # Pull changed fact rows and dims since last_wm
        facts = SELECT * FROM fact WHERE last_updated > last_wm AND last_updated <= now()
        dims = SELECT * FROM dim WHERE last_updated > last_wm AND last_updated <= now()

        # Apply upserts into materialized staging table
        for row in facts:
            upsert_into_mv_staging(row, dims_lookup(row))

        # compute aggregates/joins incrementally (delta apply)
        apply_delta_to_mv(view_name)

        update_control(view_name, last_watermark=now(), status='idle')
    except Exception as e:
        update_control(view_name, status='error')
        raise
    finally:
        release_lock(view_name, token)

Concurrency control:- acquire_lock uses a DB transaction: UPDATE mv_control SET lock_token=token, status='running', started_at=now() WHERE view_name=? AND (status IS NULL OR status='idle' OR lock_expired).- lock_expired if started_at older than timeout (e.g., 2x hourly).- Only the locker with matching token can release; this prevents double-refresh.

Fallback / full refresh:- Detect if incremental processed > X rows or duration > Y minutes -> mark degraded and schedule full refresh in maintenance window.- Full refresh steps: snapshot new materialized view into temp table, validate row counts/checksums, swap with minimal downtime (atomic rename or transactional swap).- If full refresh fails, roll back to previous MV and alert.

Monitoring & alerts:- Emit metrics: last_successful_refresh_time, duration, rows_processed, error_rate, cost_estimate.- Dashboards/alerts: alert if refresh > 15 min, failure, or last_successful > 2 hours.- Add data quality checks (row counts, null rates, key integrity) post-refresh and alert on anomalies.- Retain audit logs in mv_control for troubleshooting.

Trade-offs & best practices:- Watermark simple but misses deletes; CDC preferred when deletes/updates matter.- Keep dimension changes lightweight: maintain small change tables or join-on-read for slowly changing dimensions.- Use partitioning and predicate pushdown on fact to speed delta reads.- Test on staging, backfill strategy for late-arriving data, and document SLA with stakeholders.

MediumTechnical

21 practiced

A dashboard shows different totals between dev and prod environments. Outline a comprehensive investigation plan to determine why results diverge. Consider dataset snapshot comparisons, access permissions, difference in ETL scheduling or sampling, schema drift, view versions, feature flags, and external lookups. Explain how you would validate and fix the root cause.

Sample Answer

1) Clarify scope & reproduce- Confirm which metrics/tables differ, the exact queries or dashboard tiles, timestamps, filters, and user accounts used in dev vs prod.- Reproduce divergence with same inputs (date range, user, filters).

2) Snapshot comparisons (data parity)- Take simultaneous snapshots of source tables in both envs (same snapshot_time).- Compare row counts, sum checksums:

sql

SELECT COUNT(*) cnt, SUM(hash) checksum FROM (
  SELECT *, MD5(CONCAT_WS('|', col1,col2,...)) AS hash FROM schema.table WHERE snapshot_time='2025-12-01'
) t;

- Run GROUP BY aggregations used by the dashboard to surface skewed keys.

3) ETL / scheduling / sampling- Compare latest successful ETL run timestamps and logs.- Check incremental windows, late-arriving data, or sampling flags in dev jobs.- Validate job configs: partition ranges, LIMIT clauses, test-mode flags.

4) Schema drift & view/version differences- Diff schemas (columns, data types, nullability, defaults).- Inspect view definitions and materialized views in both envs:

sql

SHOW CREATE VIEW schema.view_name;

- Ensure same SQL, same UDF/library versions.

5) Access permissions & row-level security- Verify row-level filters, RLS policies, or masking present only in one env.- Query as dashboard service account vs your account to detect permission-filtered results.

6) Feature flags & config- Check feature flags that change joins, filters, or sampling. Validate flag states in both envs.

7) External lookups & third-party data- Validate external API call responses, lookup tables, and refresh cadence are identical.- If joins to enrichment tables, confirm keys and refresh timestamps match.

8) Root-cause validation- Once suspected cause identified, make a controlled change in dev: e.g., point dev to prod snapshot or toggle flag, then re-run dashboard to see if numbers align.- Create unit SQL tests comparing aggregates between environments.

9) Fix and deploy- If ETL scheduling: fix job cadence or backfill missing partitions and rerun downstream pipelines.- If view/schema/version mismatch: sync view defs, deploy migration with tests.- If permissions: adjust RLS or service-account permissions and document justification.- If sampling/feature flag: switch to production flag state and reprocess affected datasets.

10) Prevent recurrence- Add automated parity checks (nightly aggregate diff alerts), include schema/version checks in CI, enforce environment config parity, and add runbook.

Key validations: reproducible toggles, before/after snapshots, checksums, and monitoring to confirm fix.

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.