Designing, building, and operating end-to-end data and analytics platforms that collect, transform, store, and serve event, product, and revenue data for reporting, analysis, and decision making. Core areas include event instrumentation and tag management to capture user journeys, marketing attribution, and experimental events; data ingestion strategies and connectors; extract-transform-load (ETL/ELT) pipelines and streaming processing; orchestration and workflow management; and the trade-offs between batch and real-time architectures. Candidates must be able to design storage and serving layers, including data warehouses, data lakes, lakehouse patterns, and managed analytical databases, and to choose storage formats, partitioning, and indexing strategies driven by volume, velocity, variety, and access patterns. Data modeling for analytics covers raw event layers, curated semantic layers, dimensional modeling, and metric definitions that support business intelligence and product analytics. Governance and reliability topics include data quality validation, freshness monitoring, lineage, metadata and cataloging, schema evolution, master data considerations, and role-based access control. Operational concerns include scaling storage, processing, and query concurrency; fault tolerance and resiliency; monitoring, observability, and alerting; and cost, performance, and capacity planning trade-offs. Finally, candidates should be able to evaluate and select tools and frameworks for orchestration, stream processing, and business intelligence; integrate analytics platforms with downstream consumers; and explain how architecture and operational choices support marketing, product, and business decisions while balancing tooling investment and team skills.
HardTechnical
56 practiced
You must decide whether to recommend a commercial managed analytics platform versus building an open-source stack given a client's constraints: limited engineering skill, strict compliance, and a 5-year cost target. Describe evaluation criteria, a scoring approach, and how you would present a clear recommendation including risks and mitigations.
Sample Answer
Situation: A regulated client with limited engineering skill, strict compliance (PII, audit trails), and a 5‑year total cost target asked whether to buy a commercial managed analytics platform or build on open‑source.Task: I needed to produce a defensible recommendation that balanced compliance, operating risk, time-to-value, and 5‑year TCO.Action:- Defined evaluation criteria aligned to client priorities: - Compliance & security (certifications, data residency, auditability) - Time-to-value & required skills (onboarding time, operator burden) - Total cost of ownership (licensing, infra, staffing, upgrades over 5 years) - Reliability & SLAs (uptime, DR, support responsiveness) - Extensibility & vendor lock-in (APIs, migration paths) - Performance & scalability (expected data volumes, query SLAs)- Built a weighted scoring model reflecting client priorities (example weights: Compliance 30%, TCO 25%, Time-to-value 20%, Reliability 10%, Extensibility 10%, Performance 5%). For each option I scored 1–5 and computed weighted totals.- Gathered data: vendor quotes, managed platform compliance docs (SOC2, ISO, HIPAA), benchmarks, salary and hiring estimates, and cloud infra projections.- Ran sensitivity analysis on staffing costs and unexpected compliance work (e.g., additional encryption, logging).- Recommendation delivery: presented a one-page executive summary with the scorecard, 3‑year and 5‑year TCO charts, risk matrix, and migration/mitigation plan.Result / Recommendation (example outcome):- Managed commercial platform scored higher primarily due to compliance certs, faster time-to-value, and reduced staffing risk. Even if license costs are higher, the limited engineering skill and compliance burden push the balance to managed for years 0–3; build could be reconsidered after maturity and hiring.- Key risks and mitigations: - Vendor lock-in: negotiate data portability SLAs, standardized export formats, and short-term pilots to validate exit paths. - Cost overrun: include usage caps, reserved pricing, and quarterly TCO reviews; plan for staged adoption to limit spend. - Compliance gaps: require vendor contract clauses for audit support, encryption at rest/in transit, and penetration test reports; engage external compliance consultant for initial validation. - Skills atrophy: retain a small in‑house ops/architecture team and train them during pilot to preserve institutional knowledge.This approach gives the client a transparent, quantifiable decision with actionable mitigations and a staged review cadence to reassess build vs buy as needs, skills, and costs evolve.
MediumSystem Design
54 practiced
Design an event ingestion pipeline for a global consumer app that must ingest 100,000 events/sec sustained from web and mobile, provide processed event streams and materialized views to product analytics with <5s end-to-end latency, tolerate regional outages, and support replays. Describe components, buffering, ordering, deduplication, and SLA trade-offs.
Sample Answer
Requirements clarification:- Ingest 100k events/sec globally (sustained) from mobile/web- End-to-end processed streams + materialized views for analytics with <5s latency- Regional outage tolerance and replay capability- Support deduplication and ordering guarantees where neededHigh-level architecture:- Edge SDKs → CDN + Regional API Gateways → Regional Ingest Cluster (partitioned streaming) → Stream Processing layer → Materialized view stores & analytics sinks → Global API/Query layerComponents and choices:1. Ingest edge:- Lightweight SDKs batching events (50–200 events, <200ms) and assigning client-side event_id + timestamp.- CDN + regional load balancers terminate TLS and forward to regional collectors.2. Regional ingestion & buffering:- Use partitioned durable log per region (managed Kafka or Kinesis). Configure retention 7+ days for replay.- Partitioning key = tenant/user_id or event_type to preserve ordering where needed.- Provision partitions/shards: for 100k/sec, ~200 partitions at 500 eps/partition headroom; scale per region by traffic split.3. Ordering:- Strong ordering only within partition key (user/session). Global ordering is prohibitively expensive; document this trade-off.- For cross-partition correlated workflows, attach causal metadata (session sequence) and implement reordering in processors if bounded lateness acceptable.4. Deduplication & idempotency:- Producers include unique event_id; ingestion layer performs fast idempotent write (Kafka dedupe on idempotent producer + broker dedupe).- Stream processors maintain recent-event_id bloom filters / compacted dedupe-store (TTL e.g., 24h) for at-least-once to appear once semantics.- For stricter exactly-once, use stream processor with transactional sinks (Flink/KS with two-phase commit).5. Stream processing & materialized views:- Stateful, low-latency stream processors (Flink/Kafka Streams) colocated in-region reading local topics, emitting processed streams and updating local materialized views (RocksDB-backed).- Materialized views served from Redis/ElastiCache or ClickHouse for analytical queries. Use change-data-capture to export to long-term analytics.6. Replay and retention:- Keep raw topics for 7–30 days; replay by reprocessing topic offsets into processors. Support targeted replays by key/time range.- Use schema registry and event versioning to ensure reprocessability.7. Cross-region resilience:- Multi-region active-active ingestion: write to local regional topics; async replicate compacts/aggregates to global topic via MirrorMaker or Kinesis Replication.- On regional outage, SDKs failover to nearest healthy region (configurable) and include original timestamp/event_id to preserve sequencing and dedupe.8. Latency, scaling, and SLAs:- To meet <5s E2E: keep batching small, processing windows minimal (<1s), use colocated processors and in-memory view caches, and provision network/carrier SLAs.- Trade-offs: - Exactly-once vs latency/throughput: exactly-once increases latency and complexity — choose idempotent at ingest + at-least-once processing with dedupe for most use-cases. - Global ordering vs scalability: sacrifice global ordering for throughput; provide session-level ordering. - Retention vs storage cost: longer retention simplifies replays but increases storage and replication cost.Operational considerations:- Monitoring: end-to-end SLOs (ingest latency, processing lag, consumer lag), alerting, and synthetic replays.- Capacity planning: add headroom (30–50%), autoscaling brokers/processors, partition rebalance strategies.- Security: encryption in transit/at rest, ingestion rate limiting, token-based auth.Example throughput sizing (simple):- 100k eps → if 3 regions, ~34k eps/region, ~70 partitions/region at 500 eps/partition → round to 100 partitions/region for headroom.This design provides low-latency, replayable, regionally resilient ingestion, with ordering scoped to partitions and practical deduplication ensuring product analytics meet the <5s SLA with clear trade-offs documented for customers.
EasyTechnical
67 practiced
Describe role-based access control (RBAC) for an analytics platform spanning object storage (data lake), data warehouse, BI tools, and a metadata catalog. As a Solutions Architect, outline roles, least-privilege rules, and how you'd enforce separation between sensitive finance data and self-service product analytics.
Sample Answer
Role-based access control (RBAC) is assigning permissions to roles (not individuals) and mapping users/groups to those roles. For an analytics platform spanning a data lake (object storage), data warehouse, BI tools, and a metadata catalog, I’d design RBAC with clear role tiers, least-privilege rules, and enforced separation of sensitive finance data from product analytics.Roles (examples)- Platform Admin: full infra & IAM management (very limited membership)- Data Owner: defines access policies for a dataset (per business domain)- Data Steward: metadata and quality operations, catalog write/read for approved datasets- ETL/Engineer: create/modify pipelines, write access to staging zones, limited writes to curated zones- Analyst (Product): read/query curated product analytics datasets, create BI content- Analyst (Finance): read/query finance datasets, create BI content- BI Consumer: view dashboards only- Auditor/Compliance: read-only across sensitive logs and access trailsLeast-privilege rules- Grant minimal dataset/table/column-level permissions required for tasks.- Use role-scoped temporary credentials (short-lived tokens) for interactive sessions.- Separate environments: dev/staging/prod with stricter controls in prod.- Approve cross-domain access via exception workflow (time-limited, logged, manager & DPO approval).Enforcement mechanisms- Centralized IAM integrated with SSO/IdP and groups (SCIM sync).- Data warehouse: use role + row-level security (RLS) and column-level masking for PII/finance columns.- Object storage: bucket/object ACLs + prefix-based policies; separate buckets for sensitive finance.- Metadata catalog: enforce tag-based access control (sensitivity tags) so policies follow data.- BI tools: connect via service accounts mapped to roles; disable export/csv for sensitive dashboards.- Audit & monitoring: log all access (object, query, dashboard), alert on anomalous queries or privilege escalations.Separation of finance vs product analytics- Physical separation: different schemas/databases or buckets for finance vs product.- Policy separation: tag finance data as "sensitive:finance" and enforce deny-by-default for product roles.- RLS + masking: finance role sees full columns; product role sees masked/aggregated views only.- Approval & just-in-time access: require ticketed access with automatic revocation for any cross-access.- Regular review: quarterly access recertification and automated policy tests (canary queries) to verify no leaks.Trade-offs & practical notes- Favor automation: IaC for policies, automated tagging, and CI for policy changes.- Balance usability: provide curated aggregated views for product analysts to reduce need for raw access.- Compliance: ensure retention of audit logs and integrate DLP where appropriate.This design enforces least privilege, provides clear separation, and gives auditors traceable controls while enabling self-service for non-sensitive analytics.
MediumTechnical
71 practiced
Write a PostgreSQL query to detect duplicate events in an events table where duplicates share the same event_id but may have multiple ingestion_time entries. Schema:
Flag rows where event_id has multiple ingestion_time values and mark the earliest ingestion per event for retention.
Sample Answer
Approach: identify event_ids that have >1 distinct ingestion_time, mark all rows for those event_ids as duplicates except the earliest ingestion_time per event (retain earliest). Use window functions to rank ingestion_time per event_id and flag.
sql
-- Mark duplicates: keep row with row_number = 1 (earliest ingestion_time) per event_id
WITH ranked AS (
SELECT
event_id,
user_id,
event_name,
occurred_at,
ingestion_time,
ROW_NUMBER() OVER (
PARTITION BY event_id
ORDER BY ingestion_time ASC NULLS LAST
) AS rn,
COUNT(DISTINCT ingestion_time) OVER (PARTITION BY event_id) AS distinct_ingestions
FROM events
)
SELECT
event_id,
user_id,
event_name,
occurred_at,
ingestion_time,
distinct_ingestions,
CASE
WHEN distinct_ingestions > 1 AND rn > 1 THEN true
ELSE false
END AS is_duplicate,
CASE WHEN rn = 1 THEN true ELSE false END AS keep_row
FROM ranked
ORDER BY event_id, ingestion_time;
Key points:- ROW_NUMBER keeps deterministic single retained row per event (earliest ingestion_time).- COUNT(DISTINCT ingestion_time) identifies event_ids with multiple ingestions.- NULLS LAST makes rows with null ingestion_time treated as newest (adjust if desired).Performance:- Uses single pass window functions; cost O(n log n) due to ordering. Add index on (event_id, ingestion_time) for faster execution on large tables.Edge cases:- If ingestion_time ties exist, ROW_NUMBER picks arbitrary tie-break; use additional ORDER BY (e.g., occurred_at, user_id) to deterministically break ties.- If ingestion_time can be NULL and you want to keep NULL as earliest, change NULLS LAST to NULLS FIRST.Alternatives:- Use DISTINCT ON (event_id) to select earliest rows for retention, then join to flag others. Suitable when you only need retained rows.
MediumTechnical
54 practiced
Compare micro-batch processing (e.g., Spark Structured Streaming micro-batches) to true streaming (e.g., Flink) for analytics workloads. Discuss differences in throughput, latency, state management, fault recovery, operational complexity, and suitability for typical analytics patterns.
Sample Answer
Brief framing: Micro-batch (Spark Structured Streaming) groups records into small time-bounded batches; true streaming (Flink) processes each event (or fine-grained event-time windows) continuously. Both can serve analytics, but their trade-offs differ by latency, throughput, state, recovery, operations, and typical analytic patterns.Throughput- Micro-batch: High throughput for large-volume, coarse-grained analytics; batching amortizes scheduling and serialization overhead.- True streaming: Also high throughput; frameworks like Flink optimize per-record processing and operator fusion—throughput comparable or better at high-parallelism and low-record-overhead workloads.Latency- Micro-batch: Inherent latency equals micro-batch interval plus processing time (tens to hundreds of milliseconds to seconds). Good for near-real-time analytics.- True streaming: Sub-millisecond to millisecond latencies possible; better for strict low-latency SLAs and per-event responses.State management- Micro-batch: Maps state to batch-level aggregations; simpler mental model, but large states may require external stores or checkpointing frameworks.- True streaming: Rich, low-latency keyed state with incremental updates, RocksDB integration, TTLs, and efficient on-heap/off-heap management—better for complex stateful analytics (sessionization, joins).Fault recovery- Micro-batch: Recovery by reprocessing affected batches; deterministic at batch boundaries, simpler semantics but can reprocess larger windows of data.- True streaming: Exactly-once semantics with lightweight incremental checkpoints and savepoints; faster recovery without reprocessing entire recent windows.Operational complexity- Micro-batch: Easier to reason about, simpler deployments for teams familiar with batch Spark; predictable resource usage but tuning batch interval, backpressure, and micro-batch sizing is necessary.- True streaming: More components (state backends, RocksDB tuning), operator-level metrics and fine-grained configs; steeper operational learning curve but more control.Suitability for analytics patterns- Aggregations, windowed analytics with relaxed latency: Micro-batch is cost-effective and simpler.- Per-event enrichments, complex stateful joins, session windows, CEP, strict low-latency alerts: True streaming preferred.- Large historical reprocessing + streaming hybrid: Spark’s unified batch/stream APIs simplify pipelines; Flink requires integration for bulk reprocessing (but can still do it).Recommendation (Solutions Architect view)- If client needs sub-second latencies, complex stateful logic, and strict exactly-once processing, choose true streaming (Flink).- If client prioritizes developer familiarity with Spark, cost predictability, simpler ops and can tolerate batch-interval latency, choose Spark Structured Streaming.- For enterprise cases, consider hybrid: Flink for low-latency serving/alerting + Spark for heavy analytics, with a shared event lake and schema/versioning to reduce duplication.
Unlock Full Question Bank
Get access to hundreds of Data and Analytics Infrastructure interview questions and detailed answers.
