Large Dataset Management and Technical Analysis Questions

Develop skills in working efficiently with large datasets: data cleaning and validation, efficient aggregation and manipulation, handling missing data, identifying and managing outliers. Master advanced Excel features or learn SQL for database queries. Practice data quality assessment. Learn efficient workflows that scale with dataset size. Understand data security and privacy considerations.

MediumTechnical

40 practiced

Explain how Parquet partitioning and predicate pushdown speed queries on large datasets. Give examples where partitioning by date helps and where partitioning by a high-cardinality key will hurt. Discuss the effect of too-small files (micro-partitions) and how to choose partition granularity.

EasyTechnical

47 practiced

Compare CSV and Parquet file formats for storing datasets >100GB used for ML training. Discuss differences in storage size, read performance, schema enforcement, compression, columnar vs row access patterns, and their practical impact on feature ETL and model training workflows.

Sample Answer

Situation: You need to store and train models on datasets >100GB. Choosing between CSV and Parquet affects cost, speed, and pipeline reliability.

Comparison:- Storage size: Parquet is typically 5–10x smaller than CSV for tabular data because it stores binary encoded typed values and column-wise compression. CSV is plain-text, so larger and more expensive at scale.- Read performance: Parquet offers much faster reads for analytics and ML when you only need subsets of columns (predicate pushdown, column pruning). CSV forces full-file scanning and parsing, which is CPU- and I/O-heavy.- Schema enforcement: Parquet embeds a schema (types, nullability). CSV has no enforced schema; types must be inferred or supplied, increasing risk of silent parsing errors.- Compression: Parquet uses efficient columnar codecs (Snappy, Zstd) with per-column tuning; CSV can be gzip/bzip2 but compression is less effective because textual representation is larger and splits across rows.- Columnar vs row access: Parquet is columnar — ideal for feature selection, vectorized transforms, and column-wise operations. CSV is row-oriented — better for streaming row-by-row operations but inefficient for feature-heavy ML workloads.

Practical impact on feature ETL and training workflows:- ETL: Parquet reduces I/O and parsing time, supports predicate pushdown for incremental loads, and guarantees consistent types — fewer ETL failures and faster jobs. CSV requires explicit schema handling, more compute to parse, and longer job runtimes.- Model training: Parquet enables faster data loading when training uses subsets of features or batch-wise column reads; it integrates well with distributed frameworks (Spark, Dask). For streaming single-row inference pipelines, CSV may be acceptable but still suboptimal.- Recommendations: Use Parquet (or ORC) for large-scale ML training and feature stores. Keep a small CSV only for human-readable exports or tiny datasets. Ensure Parquet files are reasonably sized (e.g., 128MB–1GB) and partitioned for efficient parallel reads; choose codec (Snappy for speed, Zstd for better compression) based on read/write trade-offs.

HardSystem Design

42 practiced

Design an architecture to serve high-cardinality features (e.g., per-user or per-item embeddings) at P95 latency <20ms. Consider online store choices (Redis, DynamoDB), sharding and caching strategies, cold-start handling, consistency between offline recomputes and online updates, and how to update embeddings in near-real-time without service disruption.

Sample Answer

Requirements & constraints:- P95 read latency < 20ms for per-user/item embeddings at scale (millions–hundreds of millions keys)- Allow offline batch recompute (periodic retrain) and near-real-time updates (streamed incremental updates)- Strong eventual consistency acceptable; reads should be fresh within seconds for most use-cases- Minimal disruption during updates

High-level architecture:Client → regional cache tier (local in-memory + LRU) → distributed caching layer (Redis Cluster / Dynomite) → persistent online store (DynamoDB / Bigtable) → offline storage (S3 + feature-store) and streaming update pipeline (Kafka → stream processors)

Component choices & reasoning:- Persistent store: DynamoDB (managed, single-digit ms median, auto-scaling, global tables) or Bigtable. Use for durability and cold-read backing.- Distributed cache: Redis Cluster deployed in each region for sub-ms reads; choose cluster mode with hash slots and sufficient memory to hold hot partitions.- Local in-process cache: tiny LRU/TTL (e.g., Caffeine) to eliminate RPCs for immediate hotspots and stay within P95 budget.- Streaming: Kafka + Flink/Beam to process events and compute incremental embedding updates or deltas.

Sharding & partitioning:- Hash-based sharding on key (user_id/item_id) across Redis cluster nodes and DynamoDB partitions. Ensure stable hashing to avoid large reshuffles; use consistent hashing for application-level routing.- Maintain a routing map in service discovery (consul/etcd) for cluster topology to send writes directly to owning shard when doing shard-local writes.

Caching strategy:- Read-through cache: on miss, fetch from persistent store, populate Redis and local cache. Use small TTL (e.g., 60–300s) tuned by access patterns.- Hot-key protection: request coalescing (singleflight) to prevent cache stampedes; probabilistic pre-warming for heavy hitters.- Staleness policy: include embedding_version metadata with entries; clients accept slightly stale embeddings but can request strong-read (bypass cache) for critical flows.

Cold-start handling:- For new keys, serve: - default embedding (global average or demographic-based), - light-weight model to compute initial embedding from features on demand, - enqueue background task to compute full embedding and populate stores.- Return low-confidence flag so downstream can adjust scoring.

Consistency between offline recompute and online updates:- Maintain versioned embeddings: (key, version, timestamp). Offline recompute writes a new version atomically.- Merge strategy: stream processors apply incremental updates on top of current version; offline bulk writes produce a new version > current. On write, compare versions: accept only if incoming_version >= stored_version to avoid reverting fresh updates.- Use compare-and-set (DynamoDB conditional writes) or per-key monotonic version numbers to avoid race conditions.

Near-real-time updates without disruption:- Apply updates via streaming processors that write to both Redis and DynamoDB asynchronously: - Step 1: compute delta embedding in stream job - Step 2: write to DynamoDB with new version (conditional write if needed) - Step 3: update Redis cache (or invalidate then repopulate) — prefer atomic replace to avoid partial reads- Use graceful rollouts and feature-flagged switches. For large offline swaps, write new version to persistent store first, then atomically swap cache pointers or invalidate caches; clients read new version after TTL or on-demand reload.- For zero-downtime schema changes, support multi-version serving (code reads version X or X+1) and phased promotion.

Operational considerations:- Monitor P95 latency end-to-end, cache hit ratio, hot-key skew. Autoscale Redis shards and provisioned capacity for DynamoDB.- Chaos-test cache failures: degrade to DynamoDB with circuit-breaker and tail-latency protection.- Security: encrypt in transit and at rest, IAM roles for stream processors.

Trade-offs:- Redis gives lowest latency but requires operational complexity and memory cost. DynamoDB reduces ops but has higher cold-read latency—mitigated by aggressive caching.- Strong consistency increases latency; use versioning + conditional writes to get safe eventual correctness with low latency.

This architecture delivers P95 <20ms by keeping hot embeddings in RAM (local + Redis), using singleflight/coalescing to avoid stampedes, versioning to ensure safe merges, and streaming pipelines to update embeddings in near real-time without service disruption.

HardTechnical

39 practiced

Design a robust sampling and assignment strategy for an A/B test on a platform where users access via multiple devices. Goals: ensure statistical power, avoid user-level leakage across variants, handle long-tail user activity, and support sequential monitoring with alpha spending. Describe unit of randomization, deterministic bucketing, assignment storage, and cross-device deduplication.

Sample Answer

Approach overview- Randomize at the true user identity (the highest-quality persistent id available: account_id if logged-in, else household_id, else probabilistic user_id from identity graph). This prevents leakage across devices and keeps analyses at the correct unit.- Use deterministic, stateless bucketing so every event/device consistently maps to the same variant.

Unit of randomization- Primary: account_id (user-level). Secondary/fallback: household_id for shared accounts, then deterministic pseudo-user from device-graph for anonymous users.- Define experiment population as those with stable id confidence >= threshold; handle long-tail low-activity users via stratified inclusion (see below).

Deterministic bucketing & assignment- Compute bucket = HMAC_SHA256(salt || experiment_id || user_id) → 64-bit integer → bucket_percent = bucket / 2^64.- Map bucket_percent to arms by pre-defined ranges (e.g., 0–0.5 control, 0.5–1.0 treatment). Use experiment-specific salt and version to avoid cross-experiment correlation.- Ensure hashing is idempotent across services; salt lives in central config.

Assignment storage & exposure logging- Store assignments in a lightweight Assignment Service (key-value): {user_id, experiment_id, assignment, timestamp, assign_version}. Write-once semantics: first assignment wins for experiment lifetime to avoid churn.- Also log exposures (event-level) as immutable records with user_id, device_id, assignment, and timestamp to reconstruct intent-to-treat and per-device instrumentation.- Provide API for read-only deterministic fallback (recompute bucket) if KV unavailable.

Cross-device deduplication & identity resolution- Primary dedupe by account_id: all devices authenticated to same account map to same user_id and assignment.- For anonymous devices: maintain an identity graph (signals: cookie, mobile ad id, IP+ua joins, login events). Use probabilistic linking with per-edge confidence; only collapse nodes to a pseudo-user id if confidence > threshold (tunable).- In analysis pipeline, join device events to final resolved user_id; mark unresolved devices as device-level and exclude or analyze separately.- For households: map devices to household_id; randomize at household if product semantics require shared experience.

Handling long-tail users & power- Compute power using variance inflation for heavy-tailed engagement metrics (use bootstrap or analytic delta method on overdispersed counts). Consider: - Minimum exposure window (e.g., 7–14 days) to capture sporadic users. - Stratified sampling: ensure representation by activity deciles; oversample rare but high-variance strata or enforce minimum n per stratum. - Option to run separate experiments for low-activity users or combine with blocking to reduce variance.- Include sample size padding for expected attrition and de-duplication uncertainty.

Sequential monitoring & alpha spending- Pre-specify number of looks or use continuous monitoring with an alpha-spending function (Lan-DeMets) and choose spending family (O’Brien–Fleming for conservative early looks; Pocock for more uniform).- Implement test statistic that supports sequential testing: use z-statistics with pooled variance or mSPRT/likelihood-ratio for continuous monitoring; compute adjusted p-values and stop boundaries at each look.- Lock analysis plan (metrics, look schedule or spending function) in experiment config; automatically enforce via Experiment Runner to prevent analyst-driven peeking.- Log interim results and decisions in experiment metadata.

Operational safeguards & best practices- Make assignment deterministic and immutable for experiment duration; change experiment version to re-randomize.- Audit logs for any assignment overrides.- Instrument rollback: store assignment history to revert changes safely.- Monitoring: track assignment imbalance, cross-device leakage rates, and identity-graph churn; auto-alert if thresholds exceeded.- Analysis: always do intent-to-treat as primary, plus per-protocol and device-level sensitivity checks; report effective sample after deduplication.

Why this works- User-level randomization + deterministic bucketing prevents leakage across devices.- Assignment service + immutable exposure logs enable reproducible analysis.- Identity graph with confidence thresholds balances dedupe accuracy vs. false merges.- Stratification and padding control variance from long-tail users, preserving power.- Alpha-spending / sequential methods allow flexible monitoring while controlling Type I error.

HardTechnical

47 practiced

Outline a memory-efficient, out-of-core data transformation strategy in Python for datasets that don't fit in RAM. Discuss memory-mapped arrays, PyArrow RecordBatches and streaming, on-disk columnar formats (Feather/Parquet), block-wise transforms, checkpointing intermediate results, and how to integrate with scikit-learn-style pipelines.

Sample Answer

Approach: process data block-wise on disk using columnar on-disk formats (Parquet/Feather) + PyArrow RecordBatches and memory-mapped arrays for numeric blobs; perform transforms per-block, checkpoint intermediate results to disk, and expose a scikit-learn-compatible transformer that streams blocks for fit/transform.

Example pattern (simplified):

python

import pyarrow as pa
import pyarrow.parquet as pq
import numpy as np
from sklearn.base import TransformerMixin, BaseEstimator

CHUNK_ROWS = 100_000

class OutOfCoreTransformer(BaseEstimator, TransformerMixin):
    def __init__(self, numeric_cols):
        self.numeric_cols = numeric_cols
        self.mean_ = None

    def fit(self, source_parquet):
        sums = np.zeros(len(self.numeric_cols))
        counts = 0
        for batch in pq.ParquetFile(source_parquet).iter_batches(batch_size=CHUNK_ROWS, columns=self.numeric_cols):
            arr = pa.table(batch).to_pandas().values  # small, fits per-chunk
            sums += arr.sum(axis=0)
            counts += arr.shape[0]
        self.mean_ = sums / counts
        return self

    def transform(self, source_parquet, target_parquet):
        writer = None
        for batch in pq.ParquetFile(source_parquet).iter_batches(batch_size=CHUNK_ROWS):
            tbl = pa.Table.from_batches([batch])
            df = tbl.to_pandas()
            # block-wise transform without loading entire dataset
            df[self.numeric_cols] = (df[self.numeric_cols] - self.mean_) / (df[self.numeric_cols].std(ddof=0) + 1e-8)
            out_tbl = pa.Table.from_pandas(df)
            if writer is None:
                writer = pq.ParquetWriter(target_parquet, out_tbl.schema)
            writer.write_table(out_tbl)
        if writer:
            writer.close()
        return target_parquet

Key concepts:- PyArrow RecordBatches: streamable, low-overhead chunks; iterate with ParquetFile.iter_batches.- On-disk columnar (Parquet/Feather): compact, column-prunable, splits into row-groups allowing parallel reads.- Memory-mapped arrays (np.memmap): for very large dense numeric arrays (e.g., embeddings) enable numpy-like access without full RAM load; good for random access and repeated reads.- Block-wise transforms: compute statistics in one pass, then apply transforms per-block to avoid holding full dataset.- Checkpointing: write intermediate Parquet/Feather files or use versioned file prefixes to allow restarting from last successful block; store metadata (schema, stats) in companion JSON.- Integration with scikit-learn: implement TransformerMixin/BaseEstimator; fit should compute global stats by streaming; transform should accept file paths and produce new on-disk artifact. Use joblib.Memory or custom metadata to persist fitted parameters.- Streaming best practices: pick batch size to fit memory, use columns selection to reduce IO, compress Parquet with snappy for speed.- Fault tolerance & restart: write blocks atomically (write to temp file then rename), maintain a manifest of completed row-groups.- Performance tips: predicate pushdown, column projection, use multiple readers in parallel for independent row-groups, avoid converting to pandas where possible—operate on pyarrow arrays for lower overhead.

Edge cases:- Highly skewed columns: use robust stats (median, quantiles) or two-pass approach.- Categorical cardinality: encode with hashing or incremental dicts; persist encoder mapping.- Mixed workloads: for ML model training, consider converting final dataset into memory-mapped numpy arrays (float32) for fast epoch-wise access.

Trade-offs:- PyArrow/Parquet = excellent IO and analytics; Feather is faster for simple round-trips.- Memmap enables fast access but is less flexible for schema changes.

This approach balances memory efficiency, resumability, and compatibility with scikit-learn-style workflows.

Unlock Full Question Bank

Get access to hundreds of Large Dataset Management and Technical Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.