Technical Depth Verification Questions
Tests genuine mastery in one or two technical domains claimed by the candidate. Involves deep dives into real world problems the candidate has worked on, the tradeoffs they encountered, architecture and implementation choices, performance and scalability considerations, debugging and failure modes, and lessons learned. The goal is to verify that claimed expertise is substantive rather than superficial by asking follow up questions about specific decisions, alternatives considered, and measurable outcomes.
MediumSystem Design
70 practiced
Design question: How would you implement row-level access control and data masking in a cloud data warehouse so that analysts see only permitted rows/columns without creating many data copies? Describe the access enforcement mechanism, audit trail, and performance implications.
Sample Answer
Requirements & constraints:- Enforce row-level (who can see which rows) and column-level masking (sensitive columns obfuscated) without proliferating physical copies.- Centralized, auditable, low-latency for analysts using BI tools.High-level design:- Central policy service + enforcement that integrates with the cloud warehouse’s native RLS/masking features and a SQL query gateway as fallback.Components and responsibilities:1. Policy store & engine (e.g., OPA or Apache Ranger): - Stores attribute-based policies (user, role, data sensitivity, tags, tenant_id, time, purpose). - Evaluates decisions based on request context (caller, role, purpose).2. Identity & attribute provider: - Integrate with IAM/SSO (Azure AD, GCP IAM, AWS IAM) to supply user attributes and purpose/consent.3. Enforcement layer: - Prefer native warehouse features (Snowflake Row Access Policies + Dynamic Data Masking, BigQuery Row-Level Security + Column-level Policy Tags, Redshift Spectrum/GRANTs) to avoid copies. - Where native features lack, use a query gateway (Trino/Presto or a lightweight proxy) that rewrites queries to inject WHERE predicates and CASE expressions for masking or routes queries to secure views. - Implement parameterized secure views (or secure functions) that call the policy engine to decide visible rows/columns dynamically.4. Metadata & tagging: - Catalog tables/columns with sensitivity tags (e.g., PII, PHI) in Data Catalog (Glue/Data Catalog/Dataplex).5. Audit trail: - Log every access decision and query: user, timestamp, policies applied, rows filtered/masked summary, query text (redact sensitive literals), decision id. - Store logs in immutable, append-only store (warehouse table with write-once retention or Cloud Logging with retention & export). - Correlate logs with lineage to enable forensics and fine-grained billing.Enforcement mechanics (examples):- Row-level: policy -> boolean expression (e.g., tenant_id = user.tenant_id OR user.role='etl_admin'); injected as WHERE predicate or implemented via RLS feature.- Masking: dynamic masking via CASE WHEN policy_allows THEN column ELSE masked_value END; or use built-in masking functions so data remains encrypted at rest.Performance considerations:- Push predicates into the warehouse engine for predicate pushdown and partition pruning to avoid scanning full tables.- Use partitioning/clustering on row-key(s) used in policies (tenant_id, region).- Cache policy decisions for short TTLs at gateway to avoid per-row calls to policy engine.- For high-cardinality masking/joins, prefer warehouse-native functions to avoid network roundtrips.- Materialized views or columnar projections for heavy-read patterns where policies are stable; refresh controlled and access-limited.Trade-offs:- Native RLS/masking reduces latency and complexity but may be limited in expressiveness; gateway + policy engine is flexible but adds latency and an operational component.- Caching improves latency but risks slight policy-staleness (handle by short TTLs and invalidation hooks on policy change).Edge cases & security:- Emergency access (break-glass) logged and multi-approval gated.- Test policies in read-only mode and simulate before enforcement.- Ensure metadata and audit logs are themselves access-controlled and immutable.Summary:Combine native warehouse RLS/masking where possible, backed by a centralized policy engine and a query-gateway fallback. Push predicates into the warehouse, tag sensitive columns, cache decisions carefully, and produce detailed immutable audit logs to satisfy governance without creating many data copies.
HardSystem Design
126 practiced
Migration case: You must migrate a petabyte-scale data warehouse across cloud providers with minimal downtime and preserved query history. Outline a phased migration plan, data transfer strategies, consistency checkpoints, cutover criteria, and major risks with mitigation strategies (e.g., data fidelity, performance regressions).
Sample Answer
Requirements & constraints (clarify): preserve full query history, minimal downtime (target <2 hours), migrate ~1 PB, maintain data fidelity, support current BI/ETL tools, rollback capability.Phased migration plan1. Prep & validation (2–4 weeks) - Inventory: schemas, table sizes, partitioning, access patterns, retention, metadata, audit logs, query history location. - Proof-of-concept: migrate representative datasets (hot, warm, cold) and run benchmark queries. - Provision target infra, networking, IAM, and replication tooling.2. Initial bulk transfer (weeks) - Move cold data (archival partitions) first using offline bulk methods. - Build and validate metadata sync (tables, views, grants).3. Continuous replication (ongoing until cutover) - Set up change data capture (CDC) + log replay for hot/warm datasets and query history. - Keep schemas and materialized views in sync.4. Parallel validation & performance tuning (1–2 weeks) - Run shadow queries, A/B compare results, tune distribution/partitioning, optimizer hints.5. Final cutover (planned maintenance window) - Freeze writes or put in dual-write briefly, drain pipelines, finalize deltas, switch DNS/connection strings, monitor.6. Post-cutover monitoring & rollback window - Monitor data quality, latency, query performance; keep old system for fallback.Data transfer strategies- Bulk: encrypted multi-part transfers (cloud storage export/import), use high-throughput transfer appliances or direct peering and multipart parallel uploads.- Incremental: CDC via transaction logs, streaming (Debezium/Kafka Connect or cloud-native DMS), idempotent replay.- Query history: export audit/query logs (compressed), preserve original timestamps and user metadata; replicate to target analytics store and object storage for long-term retention.- Metadata: treat as first-class—use infra-as-code to recreate schemas, grants, and lineage.Consistency checkpoints- After bulk load: row counts, checksums per partition (Parquet/ORC file-level checksums).- Incremental validation: apply-binlog offsets and verify LSN/SCN match; use per-table last-updated timestamps.- Query-level validation: deterministic query result diff for sample queries; histogram and data distribution comparisons.- Hash-based end-to-end: compute partitioned row-hash (e.g., MD5 of concatenated key+payload) to compare source vs target.Cutover criteria (must pass all)- 100% metadata and cold data migrated and validated.- CDC lag = 0 and sustained for configurable window (e.g., 10 min).- Sampled query result parity within tolerance (exact for critical dimensions).- BI dashboards validated and smoke tests passed (<X% variance).- Rollback plan tested and ready.Major risks & mitigations- Data fidelity loss: use checksums, per-partition hashes, end-to-end query diffs; keep immutable exports for replay.- Performance regressions: run benchmarking, right-size cluster, adapt partitioning/distribution keys, enable query caching; keep autoscaling policies.- Schema drift & incompatible types: map types in advance, implement compatibility layers, maintain transformation scripts for edge cases.- Extended downtime during cutover: practice dry-run cutovers, use dual-write or short freeze windows, pre-warm caches and compiled queries.- Security & access leaks: replicate IAM/roles with least privilege, rotate keys, run penetration checks.- Cost overruns: estimate egress, compute, storage; use staged throttles, spot/preemptible compute for non-critical tasks.- Rollback failure: maintain synchronized snapshots and a tested rollback checklist; freeze deletions on source until post-validation.Metrics to track- CDC lag, validation pass rates, row-hash mismatches, query latency P50/P95, cost per TB transferred, time-to-cutover.This plan balances minimal downtime with rigorous validation so stakeholders retain trust in data accuracy and historical auditability.
HardSystem Design
69 practiced
Change Data Capture (CDC): Design a CDC system to capture row-level changes from heterogeneous OLTP databases (MySQL, Postgres, SQL Server) and deliver ordered, reliable events to downstream analytics and materialized tables. Include connector strategy, schema evolution handling, ordering guarantees, and how to handle schema drift and retries.
Sample Answer
Requirements & constraints:- Capture row-level changes (INSERT/UPDATE/DELETE + DDL) from MySQL, Postgres, SQL Server.- Deliver ordered, reliable events to analytics and materialized tables.- Support schema evolution and drift, retries, and heterogeneous connectors.High-level architecture:- Source DBs → Connectors (Debezium per DB) → Kafka (topic per logical table) → Schema Registry (Avro/Protobuf) → Stream processors (Kafka Streams / Flink) → Sinks (warehouse, materialized tables, analytics) + DLQ/monitoringConnector strategy:- Use Debezium connectors deployed per DB (Kubernetes operators or Kafka Connect cluster). Configure connector per database instance with: - snapshot.mode=initial/never (controlled snapshots for new tables) - include.schema.changes=true to capture DDL events - SMTs to normalize names, add metadata (source, lsn/lsn_position, commit_ts)- For multi-tenant or high-churn setups, run connector per schema/DB to bound failure blast radius.Topic & partitioning / ordering guarantees:- Create one Kafka topic per logical table (or per table+schema). Partition by primary key hash (or composite PK) to guarantee ordering per key.- Attach metadata to each event: LSN/binlog offset, commit timestamp, transaction id.- Ordering guarantees: Kafka guarantees order within a partition. Downstream consumers must process in-partition sequentially for per-row ordering. Global ordering across tables requires external sequence (not feasible at scale) — instead use causal ordering via transaction ids when needed.- For transactional multi-row transactions, Debezium emits transaction boundaries; processors should apply events only after transaction commit flag.Schema evolution & schema drift:- Serialize events with Avro/Protobuf + Confluent Schema Registry. Store both "before" and "after" records with schema references.- Use backward/forward compatibility rules in registry. For incompatible DDL (breaking changes), require CDC operator workflow: - Emit DDL events to a special topic; run automated validation job that simulates compatibility and, if needed, require manual approval. - Support canonicalization: map heterogeneous types (e.g., SQL Server datetime2 → timestamp) using SMTs and a canonical schema layer.- For evolving schemas in materialized tables, maintain migration jobs: stream processor writes to staging with schema versioning, then apply ALTER TABLE / backfill safely.Processing, exactly-once & retries:- Use Kafka Streams or Flink with Kafka transactional sinks to provide end-to-end exactly-once semantics where supported.- Consumer processing pattern: - Read events partition-sequentially - Deduplicate using event id (transaction id + offset) and state store for idempotency - Apply transformations and writes atomically using transactions- Retry strategy: - Transient failures: exponential backoff with bounded retries - Poison messages: after retries, write to DLQ topic with diagnostic metadata - For schema-related errors: route to schema-drifts topic for manual intervention- Backpressure: scale consumer parallelism by increasing partitions; use rate limiting on sink.Operational considerations:- Monitor connector lag, topic end offsets, consumer lag, error rates, schema registry compatibility errors.- Maintain schema history topic backups (Debezium’s schema.history).- Automated snapshot orchestration for new tables and for reconciliation.- RBAC, encryption in transit & at rest, and retention/compaction policies: compact topics keyed by primary key to keep latest state for materialized views.Trade-offs:- Per-key ordering (partitioned) vs global ordering (expensive). Prefer per-key guarantees for scalability.- Using Avro+Schema Registry gives strong evolution tooling but requires governance for incompatible changes.Example handling of DDL break:- Debezium emits DDL to ddl-topic with schema version X → compatibility validator flags breaking change → pause connector, alert engineers → after schema migration plan applied and registry updated, resume connector and run controlled backfill.This design provides ordered, reliable, evolvable CDC across heterogeneous OLTP sources with clear operational paths for schema drift and robust retry/DLQ handling.
MediumBehavioral
64 practiced
Behavioral/depth check: Tell me about a time you convinced stakeholders to change an architecture due to scalability limitations. Walk me through the technical evidence you presented, alternatives you evaluated, the decision process, and the business impact after the change.
Sample Answer
Situation: At my previous company we ingested clickstream and transactional data into a nightly monolithic Spark job on EMR that wrote to Redshift. As data volume grew 5x in 9 months, job runtimes slipped from 45 minutes to 7+ hours, nightly SLAs were missed, analysts saw slow Redshift queries, and cloud costs spiked.Task: As the data engineer owner of the pipeline, I needed to convince product, analytics, and infra stakeholders to change the architecture to meet latency SLAs, control cost, and support future scale.Action:- Collected technical evidence: execution timelines, Spark job stage-level metrics, GC/shuffle skew stats, cluster CPU/memory utilization, S3 I/O metrics, and Redshift query/queue heatmaps. I showed linear growth of job time with data size, heavy shuffle writes, and hotspots in a few wide joins causing repeated retries.- Measured business impact: number of SLA breaches, delayed dashboards, and incremental cloud costs (~40% YoY increase).- Proposed alternatives and evaluated trade-offs: 1) Vertical scaling EMR (more nodes / larger instances) — quick but costly and temporary. 2) Tuning current jobs (broadcast joins, caching) — limited gains; still O(n) growth. 3) Move to partitioned columnar files (Parquet/Delta) on S3 + incremental/micro-batch processing with Spark Structured Streaming + publish to a columnar warehouse (Redshift Spectrum or Snowflake) — higher engineering upfront, better long-term scalability and cost. 4) Full migration to a managed streaming + warehouse (Kafka + BigQuery) — more vendor lock-in and larger migration effort.- Built a 2-week POC for option 3: converted one pipeline to write partitioned, compressed Parquet on S3 with partition pruning and schema evolution via Delta format; replaced full nightly recompute with idempotent micro-batches using Structured Streaming; used Redshift Spectrum for queries over S3 to avoid full ingest into Redshift.- Presented POC results in a short demo + data: end-to-end latency dropped from 7h → 20min for that dataset, Redshift query times improved 5x for common analyst queries, EMR cluster hours reduced 40%, and cost projections showed a 30–40% annual saving versus scaling EMR.- Facilitated decision: recommended phased rollout, prioritized critical datasets, and scheduled rollback checkpoints. Secured buy-in by aligning improvements to stakeholder KPIs (faster dashboards, lower costs, and reliability).Result: Stakeholders approved the phased architecture change. Over 3 months we migrated top 6 pipelines. Outcomes:- Median pipeline latency fell from 6 hours to ~30 minutes.- Nightly SLA breaches dropped to zero.- Monthly infrastructure cost for data processing fell ~35%.- Analyst satisfaction rose (faster dashboards, fewer ad-hoc requests).- The new architecture supported 4x data growth over the next year without infra changes.Learning: Technical evidence (metrics + POC) plus tying outcomes to business KPIs makes architectural change decisions easier to accept. Phased POC reduces perceived risk and builds trust across teams.
MediumTechnical
79 practiced
Coding + architecture: Describe and sketch (pseudo-code or Spark API) a streaming sliding-window aggregator in Spark Structured Streaming that computes per-user 1-minute moving average of event value with late data handling and state cleanup. State how you ensure bounded state and correct outputs on restart.
Sample Answer
Approach (brief): use event-time stateful processing keyed by userId. Track a bounded time-indexed aggregate for the last 1 minute (sum, count) using small time buckets (e.g., 1s) to avoid storing every event. Use watermarks to drop late data beyond allowed lateness and use event-time timeouts for state cleanup. Persist progress with checkpointing so state is restored consistently on restart.Scala-like pseudocode (Structured Streaming, mapGroupsWithState):Key concepts / reasoning:- Watermark (withWatermark) bounds how late data is accepted; this bounds state size because we evict buckets older than (watermark - window).- Bucketing (per-second) keeps state bounded even for high-rate streams; you store O(window / bucketSize) buckets per user.- map/flatMapGroupsWithState with EventTimeTimeout lets Spark call state-timeout callbacks for cleanup.- Checkpointing: set checkpointLocation for query; Spark will persist state and progress in durable storage so on restart the state is restored and processing resumes deterministically.- Correct outputs on restart: rely on event-time processing + watermark determinism and Spark’s exactly-once sink semantics (use transactional sinks like Kafka or use idempotent writes). Use output mode Update/Append as appropriate.- Lateness trade-offs: choose watermark lateness that balances accepting late events vs bounded state.- Edge cases: skewed users (add eviction caps or TTL), clock skew, out-of-order extremes.
scala
import org.apache.spark.sql.streaming.{GroupState, GroupStateTimeout}
case class Event(userId: String, ts: Long, value: Double)
case class Bucket(tsSec: Long, sum: Double, count: Long)
case class UserState(buckets: Map[Long, (Double, Long)]) // tsSec -> (sum,count)
def updateUser(userId: String, events: Iterator[Event], state: GroupState[UserState]) = {
val allowedLatenessMs = 2*60*1000L // e.g. 2 min watermark
val windowMs = 60*1000L
val nowWatermark = state.getCurrentWatermarkMs() // conceptual; Spark uses watermark externally
val s = state.getOption.getOrElse(UserState(Map.empty))
var buckets = s.buckets
// ingest events into per-second buckets
events.foreach { e =>
val sec = e.ts / 1000
val (sum,cnt) = buckets.getOrElse(sec, (0.0,0L))
buckets = buckets.updated(sec, (sum + e.value, cnt + 1))
}
// evict buckets older than watermark or older than window
val minAllowedSec = ((nowWatermark - windowMs) / 1000).max(0)
buckets = buckets.filter { case (sec,_) => sec >= minAllowedSec }
// compute rolling 1-minute avg
val (totalSum, totalCount) = buckets.values.foldLeft((0.0,0L)){case ((s,c),(ss,cc)) => (s+ss,c+cc)}
val avg = if (totalCount>0) totalSum/totalCount else null
if (buckets.nonEmpty) {
state.update(UserState(buckets))
state.setTimeoutTimestamp((minAllowedSec + windowMs/1000 + allowedLatenessMs/1000)*1000) // schedule cleanup
} else {
state.remove()
}
Iterator((userId, avg, /*output event-time = current watermark or max ts in buckets*/))
}
val ds = spark.readStream
.format("kafka")... // parse Event with timestamp column "ts"
.withWatermark("ts", "2 minutes")
.as[Event]
.groupByKey(_.userId)
.flatMapGroupsWithState(OutputMode.Update(), GroupStateTimeout.EventTimeTimeout())(updateUser)Unlock Full Question Bank
Get access to hundreds of Technical Depth Verification interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.