Data Engineer: Ingestion, Reliability, Large-Scale Analysis, Team Lead

Ingestion pipelines that don't break, monitoring, debugging at scale, analysis on billions of rows, stakeholders and leading a team.

Same series as Parts 1-9. This role is less about one platform and more about engineering fundamentals plus judgment: Python, SQL, an OOP language, pipelines that don't break, debugging at scale, working with stakeholders, and leading juniors. The platform parts of the series (Databricks, Delta, Spark, ADF, Fabric) are your supporting material here, not the main subject.

0. WHAT EACH LINE OF THE JOB DESCRIPTION IS REALLY TESTING

Your positioning in one sentence: "I'm a data engineer with 20+ years of experience who builds ingestion and transformation platforms end to end, Python and SQL first, with earlier C# background that I can map onto production design; I care most about pipelines that are idempotent, observable and boring to operate."

THEORY PRIMER

What "robust ingestion" means (the checklist behind every answer)

A pipeline is robust when each of these is a deliberate design decision, not an accident:

1. Idempotent: re-running the same input produces the same end state (MERGE/overwrite-by-partition, deterministic keys). Retries and backfills then become the same code path.
2. Replayable: raw data is landed unchanged (Bronze/raw zone) so downstream logic can be rebuilt without re-pulling from the source.
3. Delivery semantics are explicit: at-least-once ingestion plus idempotent writes gives effectively-once results. True exactly-once across system boundaries is rare; say so.
4. Failure is classified: transient (retry with backoff), deterministic (don't retry; quarantine and alert), poison records (dead-letter, don't block the batch).
5. State is explicit: watermarks/offsets/checkpoints advance only after the downstream write is durable.
6. Schema change is expected: detect drift, decide per layer whether to evolve or reject.
7. Observable: row counts in/out, freshness, duration, error counts, per-run IDs in every log line.
8. Bounded: timeouts, rate limits, backpressure, memory limits. An unbounded pipeline fails at 3 a.m.

How to think about "billions of rows"

At this scale the unit of cost is bytes scanned and bytes shuffled, not rows or lines of code. Almost every technique is a way of touching fewer bytes:

• Columnar formats (Parquet/ORC/ClickHouse parts): read only the needed columns.
• Partition and sort-key pruning: skip data you can prove is irrelevant.
• Pre-aggregation: compute once, serve many times.
• Approximation (HyperLogLog, sampling) when exactness isn't required, with exactness verified on a slice.
• Avoid operations that need all data in one place: global sorts, exact distinct over high-cardinality keys, cross joins, unbounded window frames.

A senior answer to "how would you analyze 5 billion rows" starts with "what question, what grain, what precision", not with a tool name.

Debugging method (the spine of Section 6)

1. Define the symptom precisely (what, where, since when, how big)
2. Scope: which datasets, partitions, sources, time ranges are affected
3. Timeline: what changed (code, config, schema, volume, upstream, infrastructure)
4. Bisect the pipeline: compare control totals stage by stage (Part 6)
5. Narrow the data: find the smallest failing slice / smallest reproducing input
6. Form a hypothesis, predict what you'd see, test it (don't just poke)
7. Fix, backfill/repair the data, add a regression check, write the postmortem

OOP in data engineering: what it is actually for

Not class hierarchies for their own sake. In pipelines OOP earns its place in four spots: (1) abstracting sources and sinks behind interfaces so connectors are swappable and testable; (2) encapsulating state and configuration (watermark stores, retry policy); (3) dependency injection so tests pass fakes instead of hitting real systems; (4) modelling domain entities with validated, immutable types. Composition over inheritance is the default stance.

Q&A 1: PYTHON (CORE AND SENIOR SUBTLETIES)

1.1 Memory and iteration

Q: How do you process a 50 GB CSV in Python on a machine with 8 GB of RAM?

A: Stream it. Never list() or read() the whole thing. Use a generator that yields fixed-size batches, process and write each batch, and keep memory flat regardless of file size:

import csv
from typing import Iterator

def read_batches(path: str, batch_size: int = 100_000) -> Iterator[list[dict]]:
    batch: list[dict] = []
    with open(path, newline="", encoding="utf-8") as f:
        for row in csv.DictReader(f):
            batch.append(row)
            if len(batch) >= batch_size:
                yield batch
                batch = []
    if batch:
        yield batch

For analytical work I'd skip hand-rolled loops: pandas.read_csv(chunksize=...), Polars lazy scan with streaming, or DuckDB querying the file out-of-core. Better still, convert once to Parquet and query that. The point to make: memory use should be O(batch), not O(file).

Q: Generator vs list: when does it matter, and what's the trap?

A: A generator produces items lazily and holds one at a time; a list materializes everything. The trap: a generator can be consumed only once. Iterating it a second time yields nothing, silently, which produces "empty second pass" bugs (for example computing a count and then processing the same iterator). Also, exceptions inside a generator surface at consumption time, not at creation time, which confuses stack traces.

Q: Why is pandas the wrong tool at billions of rows, and what do you use instead?

A: pandas is single-node, in-memory, and typically needs several times the raw data size in RAM (object dtype strings are especially costly). At billions of rows use an engine that scans columnar data out-of-core or distributes it: Spark/Databricks, ClickHouse, BigQuery/Synapse/Snowflake-style warehouses, or for a single machine DuckDB/Polars on Parquet. pandas remains fine for the result of an aggregation (thousands of rows), for small reference data, and for local prototyping on a sampled slice.

1.2 Concurrency

Q: Threading vs multiprocessing vs asyncio: when do you use each in an ingestion job?

A:

• asyncio: many concurrent I/O-bound calls with an async-capable client (hundreds of paginated API requests, object-store reads). Single thread, cooperative, very low overhead. Blocking calls inside it stall everything.
• threading: I/O-bound work using blocking libraries (most database drivers, requests, SDKs). The GIL is released during I/O so threads overlap waiting.
• multiprocessing / process pools: CPU-bound work (parsing, compression, heavy transforms in pure Python) because separate processes bypass the GIL. Cost: serialization of arguments/results between processes and higher memory.

Q: Explain the GIL and its practical impact.

A: CPython's Global Interpreter Lock lets only one thread execute Python bytecode at a time, so CPU-bound threads don't run in parallel; I/O-bound threads do overlap, because the lock is released while waiting. C extensions (NumPy, pyarrow, many compression libs) often release the GIL, so vectorized/native work can parallelize on threads. Optional free-threaded builds of CPython exist, but the default build still has the GIL [verify the current status of free-threaded Python before the interview]. The practical rule: I/O-bound goes to threads or asyncio, CPU-bound goes to processes or to native/vectorized libraries.

Q: Write a bounded-concurrency fetch for a paginated, rate-limited API.

A: Key ideas: cap concurrency with a semaphore, respect 429/Retry-After, back off with jitter, and make each page fetch independently retryable.

import asyncio, random
import httpx

async def fetch_page(client, sem, url, params, max_attempts=5):
    async with sem:
        for attempt in range(1, max_attempts + 1):
            resp = await client.get(url, params=params, timeout=30)
            if resp.status_code == 429 or resp.status_code >= 500:
                retry_after = float(resp.headers.get("Retry-After", 0))
                delay = max(retry_after, min(30, 0.5 * 2 ** (attempt - 1)))
                await asyncio.sleep(random.uniform(0, delay) if not retry_after else delay)
                continue
            resp.raise_for_status()
            return resp.json()
        raise RuntimeError(f"gave up on {params}")

async def fetch_all(url, pages, concurrency=8):
    sem = asyncio.Semaphore(concurrency)
    async with httpx.AsyncClient() as client:
        return await asyncio.gather(
            *(fetch_page(client, sem, url, {"page": p}) for p in pages)
        )

Follow-up points: offset pagination can skip or duplicate rows if the source is being written to, so prefer cursor/keyset pagination or a stable updated_at + id ordering; persist the cursor only after the page has been durably written.

1.3 Language semantics that trip people up

Q: What is the mutable default argument bug?

A: Default values are evaluated once at function definition, so a mutable default is shared across calls:

def add(item, bucket=[]):      # bug: one list shared by every call
    bucket.append(item)
    return bucket

def add(item, bucket=None):    # fix
    bucket = [] if bucket is None else bucket
    bucket.append(item)
    return bucket

Q: Late-binding closures in loops: what's the issue?

A: Closures capture variables, not values, so [lambda: i for i in range(3)] returns lambdas that all see the final i. Fix with a default argument (lambda i=i: i) or functools.partial. It shows up when building per-table tasks in a loop (for example generating one Airflow/ADF-style task per table).

Q: What are context managers for, and why do they matter in pipelines?

A: They guarantee cleanup (connections closed, files flushed, locks released, transactions rolled back) even on exceptions. Use with for files, DB connections, transactions, temporary directories; write your own with contextlib.contextmanager for things like "acquire a pipeline lock, run, release". Resource leaks in long-running jobs are a classic cause of slow-burn production failures.

Q: Decorators: give a real use in a data pipeline.

A: Cross-cutting concerns without cluttering business logic: retry with backoff, timing/metrics, structured logging with a run ID, idempotency guards. Always apply functools.wraps to keep function metadata.

import functools, random, time

def retry(max_attempts=5, base_delay=0.5, max_delay=30,
          retry_on=(ConnectionError, TimeoutError)):
    def decorator(fn):
        @functools.wraps(fn)
        def wrapper(*args, **kwargs):
            for attempt in range(1, max_attempts + 1):
                try:
                    return fn(*args, **kwargs)
                except retry_on:
                    if attempt == max_attempts:
                        raise
                    delay = min(max_delay, base_delay * 2 ** (attempt - 1))
                    time.sleep(random.uniform(0, delay))   # full jitter
        return wrapper
    return decorator

Retry only idempotent operations and only on transient exception types.

Q: is vs ==, shallow vs deep copy, __slots__, dataclasses: when do these matter?

A: == compares values, is compares identity (use is None). Shallow copies share nested objects, which causes accidental mutation of "copied" config dicts. @dataclass(frozen=True, slots=True) gives cheap immutable records with lower memory per instance, useful when you hold many objects. For validated external data (API payloads, config) use pydantic models so bad input fails at the boundary with a clear error instead of three stages later.

1.4 Quality and engineering practice

Q: How do you structure and test a Python pipeline project?

A: Package layout with clear layers: connectors/ (sources/sinks), transforms/ (pure functions, no I/O), pipelines/ (orchestration of the two), config/. Pure transform functions are trivially unit-testable with pytest and small fixtures. Connectors get contract tests against an interface and are replaced by fakes in unit tests. Add integration tests on a small real sample (for example a containerized database), type checking (mypy/pyright), linting (ruff), and run everything in CI. Pin dependencies with a lock file for reproducible runs.

Q: How do you log in a pipeline so you can debug it at 3 a.m.?

A: Structured (JSON) logs with a run_id, pipeline, stage, batch_id on every line; log counts in/out, durations, and the reason for any skipped or quarantined record; never log secrets or full PII payloads. Use levels deliberately (INFO for stage boundaries, WARNING for recoverable anomalies, ERROR for failures needing action) and emit metrics separately from logs.

Q: How do you handle configuration and secrets?

A: Config from environment/files validated at startup (fail fast on missing or malformed values); secrets from a secret manager (Key Vault, AWS/GCP equivalents), never from code or the repo; separate config per environment, same code everywhere.

Q&A 2: OOP, DESIGN PATTERNS, AND C# / JAVA

2.1 Design fundamentals

Q: Explain the four OOP pillars with data-engineering examples.

A:

• Encapsulation: a WatermarkStore hides whether state lives in a table, a blob, or a key-value store; callers see get() and commit().
• Abstraction: a Source interface exposes read(since) without revealing whether it is REST, SFTP or JDBC.
• Inheritance: use sparingly (a shared BaseConnector for retry/logging plumbing); deep hierarchies become brittle.
• Polymorphism: the pipeline runs any Source and Sink implementation without knowing the concrete type.

Q: SOLID: which principles matter most for pipeline code, and how do they show up?

A:

• Single responsibility: extraction, transformation and loading are separate units, so a change in the API client doesn't touch business rules.
• Open/closed: add a new source by adding a class, not editing the pipeline.
• Liskov substitution: any Sink implementation must honor the same contract (for example idempotent write), otherwise swapping implementations breaks correctness.
• Interface segregation: small interfaces (Reader, Writer) instead of one fat DataConnector.
• Dependency inversion: the pipeline depends on interfaces and receives implementations by constructor injection, which is what makes testing without real systems possible.

Q: Composition vs inheritance: which do you default to and why?

A: Composition. A pipeline has a source, a sink, a retry policy and a state store; it isn't any of them. Composition keeps behavior swappable at runtime and avoids the fragile-base-class problem. Inheritance is justified for genuine "is-a" relationships with stable shared behavior, or the template-method pattern.

Q: Abstract class vs interface?

A: An interface defines a contract with no state (in Python: Protocol or ABC with only abstract methods; in C#/Java: interface). An abstract class can also share state and implementation. Use an interface when unrelated types need to satisfy the same contract; an abstract class when subclasses share real implementation (and in C#/Java you can only inherit one class but implement many interfaces). Modern C#/Java interfaces can carry default method implementations, which blurs the line, so base the choice on whether you need shared state.

Q: Which design patterns do you actually use in data pipelines?

A:

• Strategy: swappable behavior (full vs incremental load, different dedup rules, different retry policies).
• Factory: build the right connector from config (type: "rest" yields RestSource).
• Adapter: wrap a third-party client behind your own Source interface so vendor changes stay local.
• Template method: a base pipeline defines extract -> validate -> transform -> load -> commit, subclasses fill in steps.
• Decorator: add retry, metrics or caching around a connector without changing it.
• Repository / unit of work: encapsulate persistence and transactional boundaries.
• Observer / event hooks: emit "batch completed" events to metrics and alerting.

2.2 A concrete design to be able to sketch on a whiteboard

Q: Design the code structure for an ingestion framework that supports many sources.

A (Python):

from abc import ABC, abstractmethod
from dataclasses import dataclass
from datetime import datetime
from typing import Iterator, Protocol

@dataclass(frozen=True, slots=True)
class SourceRecord:
    key: str
    payload: dict
    updated_at: datetime

class Source(ABC):
    @abstractmethod
    def read(self, since: datetime) -> Iterator[list[SourceRecord]]:
        """Yield batches ordered by (updated_at, key)."""

class Sink(Protocol):
    def write(self, batch: list[SourceRecord]) -> None: ...   # must be idempotent

class StateStore(Protocol):
    def get_watermark(self, pipeline: str) -> datetime: ...
    def commit_watermark(self, pipeline: str, value: datetime) -> None: ...

class Pipeline:
    def __init__(self, name: str, source: Source, sink: Sink, state: StateStore):
        self._name, self._source, self._sink, self._state = name, source, sink, state

    def run(self) -> int:
        since = self._state.get_watermark(self._name)
        total, high = 0, since
        for batch in self._source.read(since):
            self._sink.write(batch)                      # durable first...
            total += len(batch)
            high = max(high, max(r.updated_at for r in batch))
        self._state.commit_watermark(self._name, high)   # ...then advance state
        return total

The same shape in C#, which is the form to use when the interviewer prefers it:

public sealed record SourceRecord(string Key, JsonDocument Payload, DateTime UpdatedAt);

public interface ISource
{
    IAsyncEnumerable<IReadOnlyList<SourceRecord>> ReadAsync(DateTime since, CancellationToken ct);
}
public interface ISink  { Task WriteAsync(IReadOnlyList<SourceRecord> batch, CancellationToken ct); }
public interface IStateStore
{
    Task<DateTime> GetWatermarkAsync(string pipeline, CancellationToken ct);
    Task CommitWatermarkAsync(string pipeline, DateTime value, CancellationToken ct);
}

public sealed class Pipeline(string name, ISource source, ISink sink, IStateStore state)
{
    public async Task<int> RunAsync(CancellationToken ct)
    {
        var since = await state.GetWatermarkAsync(name, ct);
        var high = since; var total = 0;
        await foreach (var batch in source.ReadAsync(since, ct))
        {
            await sink.WriteAsync(batch, ct);            // durable first...
            total += batch.Count;
            var batchMax = batch.Max(r => r.UpdatedAt);
            if (batchMax > high) high = batchMax;
        }
        await state.CommitWatermarkAsync(name, high, ct); // ...then advance state
        return total;
    }
}

Points to say out loud: dependencies arrive by constructor injection; the watermark advances only after the sink write is durable; because Sink.write is idempotent, a crash between write and commit simply replays one batch harmlessly. Known weakness to volunteer: this commits the watermark once per run; for very long runs commit per batch (or per N batches) so a failure doesn't redo hours of work.

2.3 C# specifics (your earlier experience; the things worth refreshing)

Q: How does async/await work in C#, and does it create threads?

A: async methods compile to a state machine; await yields control back to the caller while the awaited I/O is pending and resumes the method when it completes. It doesn't by itself create threads. For I/O-bound work that means high concurrency with few threads. Pitfalls: blocking on async code with .Result/.Wait() can deadlock in contexts with a synchronization context; async void should only be used for event handlers; always pass a CancellationToken.

Q: IEnumerable<T> vs IQueryable<T>, and what is deferred execution?

A: LINQ queries over IEnumerable execute in memory and lazily (nothing runs until enumeration). IQueryable builds an expression tree that a provider (such as Entity Framework) translates to SQL and executes at the database. The classic bug: calling .ToList() or switching to IEnumerable too early pulls the entire table into memory and filters in the application. Another: enumerating a deferred query twice runs it twice.

Q: Entity Framework Core vs Dapper vs raw http://ADO.NET for data-heavy work?

A: EF Core is productive for CRUD and domain models but adds overhead and change-tracking cost; disable tracking (AsNoTracking) for reads. Dapper is a thin, fast mapper over raw SQL, good for ingestion and reporting queries. For bulk loads neither is right: use SqlBulkCopy (SQL Server) or the provider's bulk/COPY mechanism, which is orders of magnitude faster than row-by-row inserts.

Q: Value types vs reference types, struct vs class, and why it matters for performance?

A: struct instances are copied by value and (when local) live on the stack or inline in their container; class instances live on the heap and are collected by the garbage collector. Large numbers of small short-lived objects increase GC pressure; Span<T>/Memory<T> and ArrayPool<T> reduce allocations in hot parsing paths. Don't make large structs (copy cost) or mutable structs (surprising semantics).

Q: IDisposable and using?

A: Deterministic release of unmanaged resources (connections, file handles, readers). using var conn = ...; disposes at scope end even on exceptions, the C# analogue of Python's with and Java's try-with-resources. The garbage collector doesn't promptly release these, so forgetting using leaks connections under load.

Q: How would you do producer/consumer ingestion in C#?

A: System.Threading.Channels with a bounded Channel<T>: producers read from the source and WriteAsync into the channel; consumers ReadAllAsync and write batches to the sink; the bounded capacity gives backpressure (producers wait when consumers are slow). Parallel.ForEachAsync with MaxDegreeOfParallelism for bounded parallel I/O. Use HttpClientFactory rather than creating HttpClient per request to avoid socket exhaustion.

Q: Java parallels, in case they ask?

A: Streams and Optional for functional-style processing; try-with-resources for cleanup; CompletableFuture and (Java 21+) virtual threads for concurrent I/O; JDBC batching (addBatch/executeBatch) for loads; the JVM GC and memory model are comparable to .NET's, and Spark/Kafka ecosystem tooling is JVM-native, which is why Java/Scala skills show up in data roles.

Q: Your C# is not recent. How do you answer if they probe depth?

A: Say it plainly and show transferable strength: "My recent production work is Python and SQL; I used C# earlier in my career and I'm comfortable with the language model, async, LINQ and dependency injection, so I can read and write it; I'd ramp quickly on current idioms. The design skills (interfaces, DI, idempotent I/O, testing) are the same across languages." Then prove it with the sketch in 2.2 in C# syntax. Don't claim recent framework depth you can't back up.

Refresher plan (about 3 evenings, if you want to be safe): (1) build a small .NET console app that streams a CSV through a Channel<T> into a database with SqlBulkCopy; (2) rewrite the Pipeline above with xUnit tests using fake ISource/ISink; (3) skim what's new in recent C# versions (records, pattern matching, primary constructors, nullable reference types, IAsyncEnumerable).

Q&A 3: SQL AT LARGE SCALE

(Window functions, dedup, SCD, gaps-and-islands, top-N and running totals are covered with worked code in Part 5. Here: what changes at billions of rows.)

Q: A query over a 5-billion-row table is slow. What do you check first?

A: The plan and the bytes: EXPLAIN, then ask (1) is partition/sort-key pruning happening (does the filter use the partition/clustering column in its native form, not wrapped in a function); (2) are only needed columns read (SELECT * on a columnar table is a cost bug); (3) join order and strategy (broadcast the small side); (4) is something forcing a global operation (a global ORDER BY, COUNT(DISTINCT) on a high-cardinality column, a window without PARTITION BY); (5) skew; (6) is the same expensive aggregate recomputed on every run when it could be pre-aggregated.

Q: How do you count distinct users over billions of events cheaply?

A: Exact distinct needs every distinct value held or shuffled, which is expensive. Use an approximate sketch (HyperLogLog) when ±1-2% is acceptable: approx_count_distinct in Spark/Databricks, uniq/uniqHLL12 in ClickHouse (uniqExact when you need exact). Better still, pre-aggregate daily distinct sets or sketches and merge them. State the accuracy trade-off and confirm exactness on a sample or a single day for validation.

Q: How do you compute a metric incrementally instead of rescanning history?

A: Keep an aggregate table by grain (for example date, customer) and only recompute the partitions affected by new or late data, overwriting those partitions idempotently:

-- recompute only the days touched by this batch
INSERT OVERWRITE daily_revenue PARTITION (day)
SELECT customer_id, SUM(amount) AS revenue, CAST(order_ts AS DATE) AS day
FROM orders
WHERE CAST(order_ts AS DATE) IN (SELECT DISTINCT CAST(order_ts AS DATE) FROM new_orders_batch)
GROUP BY customer_id, CAST(order_ts AS DATE);

Gotcha: late-arriving data that touches old days means the "affected partitions" set must be derived from the data, not assumed to be "today".

Q: Find rows in A that have no match in B, on two huge tables.

A: Use an anti-join, not NOT IN:

SELECT a.*
FROM a
LEFT ANTI JOIN b ON a.id = b.id;        -- Spark/Databricks
-- portable: WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id)

NOT IN has surprising NULL semantics (a single NULL in the subquery makes every comparison unknown and returns no rows) and often plans worse.

Q: How do you deduplicate a billion-row table safely?

A: ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY updated_at DESC, tiebreaker DESC) = 1 (always include a deterministic tiebreaker), run per partition where possible so the window sorts small groups, and write to a new table then swap rather than deleting in place. In ClickHouse the idiomatic approach is ReplacingMergeTree(version) with the caveat that deduplication happens at merge time, not at insert time, so reads that need exact current state use FINAL (cost!) or an aggregation like argMax(value, version) grouped by key.

Q: Sampling for exploration: what are the traps?

A: (1) Random row sampling breaks joins: sample both sides by a hash of the join key so matching keys survive together. (2) Rare events disappear in small samples. (3) Sampling skewed distributions hides the heavy hitters that cause production problems. (4) Aggregates scale differently (counts need scaling; ratios don't). Use sampling to design the query, then validate the final numbers on full data or at least one full partition.

Q: How do you make sure a billion-row aggregation is correct, not just fast?

A: Reconcile: compare total and row counts against an independent control (source totals, a simpler query on a small partition), check that grain assumptions hold (no join fan-out: row count before and after a join with a one-to-one assumption), watch for implicit casts and NULL handling, test on a slice where you can compute the answer by hand, and keep the query deterministic (explicit ordering where ties matter). Same reconciliation mindset as Part 6.

Q&A 4: DESIGNING INGESTION PIPELINES

Q: How would you design an ingestion pipeline from a REST API into a warehouse?

A: Stages and decisions:

1. Extract with cursor/keyset pagination, bounded concurrency, rate-limit handling, and per-request retry with backoff.
2. Land raw (Bronze): store the response payload as-is plus metadata (_ingested_at, _source, _run_id, request params). Raw is append-only and replayable.
3. Validate at the boundary: schema checks, required fields, types; route failures to a quarantine/dead-letter location with the reason.
4. Transform and load into curated tables with an idempotent MERGE on the business key, guarded by a timestamp/version comparison so replays and out-of-order data are harmless.
5. Commit state (cursor/watermark) only after the load is durable.
6. Observe: counts in/out, freshness, error rate, duration; alert on deviation from expectation, not just on hard failure.

Q: At-least-once vs exactly-once: what do you promise?

A: Most sources and transports give at-least-once. I design for at-least-once delivery plus idempotent writes, which yields effectively-once results. Exactly-once end to end needs transactional coupling of state and output (for example Kafka transactions with a transactional sink, or Delta/Spark Structured Streaming checkpointing with idempotent foreachBatch), and only within that boundary. I say clearly where the guarantee ends.

Q: How do you handle schema drift?

A: Detect it explicitly (compare incoming schema to the expected contract), classify (new nullable column, type widening, rename, removal), and decide per layer: Bronze captures drift (evolve or store the raw JSON), Silver/Gold change only through reviewed code. Alert on drift. Renames can't be inferred from structure, so they need a mapping step. Agree a data contract with the producer where possible (Section 8).

Q: Late-arriving and out-of-order data?

A: Use event time (not arrival time) for business logic, accept a defined lateness window, and make loads idempotent so reprocessing a window is safe. For aggregates, recompute affected partitions. For streaming, watermarks define how long to wait; anything later goes to a correction path (a late-data table or a periodic restatement) rather than silently dropped. Decide with the business how much lateness is tolerable, because it trades freshness against completeness.

Q: A source sends a poison record that crashes the batch. What do you do?

A: Never let one bad record block a million good ones. Catch at the parse/validate boundary, write the record and the error reason to a dead-letter/quarantine table, continue, and alert when the quarantine rate exceeds a threshold. Keep the original bytes so the record can be repaired and replayed. A spike in quarantined records usually signals an upstream change and should page someone.

Q: Backfill a year of history without hurting production?

A: Same code path as normal runs (parameterized by date window), idempotent writes, throttled concurrency, run in a separate compute pool or off-peak, partition-scoped so concurrent windows don't conflict, with reconciliation per window and a progress table so it is resumable. Don't backfill by hand-editing production tables.

Q: Batch vs streaming for a new ingestion requirement?

A: Start from the decision latency the business actually needs. Most "real-time" requests are satisfied by micro-batch every few minutes at a fraction of the complexity and cost. Choose streaming when latency requirements are genuinely seconds, when events must trigger actions, or when volume makes periodic bulk loads impractical. Streaming adds state management, ordering, exactly-once concerns and 24/7 operations burden.

Q: File-based ingestion (SFTP/blob drops): what breaks?

A: Partially written files (use a "done" marker or atomic rename, or only process files older than N minutes), duplicate drops (track file name + checksum/size in a manifest), changed schemas or delimiters, encoding issues, empty files, and unbounded small-file accumulation. Keep a file manifest table (file, size, checksum, status, rows) which doubles as the audit trail and reconciliation anchor.

Q&A 5: MONITORING, RELIABILITY AND DATA QUALITY

Q: How do you monitor a dataset's health?

A: Five signals, each checked automatically per run or per interval:

• Freshness: age of the newest record vs SLA.
• Volume: row counts vs expected (rolling baseline per weekday/hour, not a fixed number).
• Schema: columns/types vs contract.
• Distribution: null rates, distinct counts, min/max, category proportions vs history (catches silent logic or upstream changes that keep counts normal).
• Integrity/reconciliation: control totals against the source, key uniqueness, referential checks (the full framework is in Part 6).

Q: What is the difference between an SLA, SLO and SLI for data?

A: SLI is the measured indicator (for example "percent of daily loads finished by 06:00"). SLO is the internal target for it (for example 99% of days). SLA is the commitment made to a consumer, often with consequences. Define them per dataset with the consumers; tier datasets so the critical ones get tight objectives and paging while others get ticket-level alerts.

Q: How do you avoid alert fatigue?

A: Alert on symptoms that need action (freshness SLO about to be missed, reconciliation failure), not on every internal exception; route by severity and ownership; use dynamic baselines for volume; deduplicate and group related alerts; attach context (run ID, failed stage, link to logs and runbook); review alerts regularly and delete the ones nobody acts on. A page that doesn't need a human decision is a bug in the monitoring.

Q: A dashboard number is wrong but no pipeline failed. How could the monitoring have caught it?

A: Failures that don't throw are the dangerous ones: a changed upstream definition, a join fan-out, a timezone shift, a filter dropping a category, a late partition. Catch them with distribution checks, reconciliation against source totals, "expected vs actual by segment" comparisons, and row-count-after-join checks. Then add the specific check that would have caught this incident (every incident should leave behind a new check).

Q: What belongs in a runbook?

A: Symptom, impact, how to confirm, likely causes in order of probability, exact diagnostic queries/commands, safe remediation steps (including re-run/backfill instructions and whether it's idempotent), escalation path, and how to verify recovery. Good runbooks let a person who didn't write the pipeline resolve the incident.

Q: How do you define data ownership and on-call for pipelines?

A: Every dataset has a named owner (team, not an individual), a documented SLO, and a runbook; the team that builds it runs it. Rotate on-call, keep it sustainable by fixing the top recurring alert sources each sprint, and track toil.

Q&A 5b: ANALYSIS ON BILLIONS OF ROWS (THE MINDSET QUESTIONS)

Q: You're asked "how many active customers did we have each month for the last 3 years?" on a 6-billion-row event table. Walk through it.

A: Clarify first: what is "active" (any event? a purchase? a login?), calendar or rolling month, which timezone, how to treat test/internal accounts, and required precision. Then: restrict columns and the time range (partition pruning), compute distinct customers per month with the agreed definition, using an exact count if it is a reported figure or a sketch if exploratory, materialize the monthly result into a small table (so the dashboard never rescans history), validate one month against an independent computation, and document the definition so the number is reproducible.

Q: What are common correctness traps in large-scale analysis?

A: Join fan-out inflating sums; double-counting from duplicates in raw data; NULL handling in comparisons and aggregations; timezone and daylight-saving boundaries in daily/monthly bucketing; averaging averages instead of weighting; survivorship bias from filtering to currently existing entities; implicit type casts; late-arriving data making yesterday's number change; and inconsistent definitions across teams.

Q: How do you profile an unfamiliar huge dataset quickly?

A: Sample for shape, then compute cheap full-scan statistics on a few key columns: row count, min/max timestamps, null rates, approximate distinct counts, top-N values by frequency (heavy hitters and skew), duplicate rate on the presumed key, and partition/file layout. Inspect the largest and smallest partitions. This takes minutes and prevents days of wrong assumptions.

Q&A 6: DEBUGGING COMPLEX ISSUES AT SCALE

For each scenario, state the method, not just the answer.

Q: Yesterday's total revenue differs between the warehouse and the source system by 0.4%. How do you find out why?

A: Reconcile stage by stage (Part 6): source vs raw, raw vs curated, curated vs reporting, comparing count, sum, distinct keys, nulls. The first stage where the numbers diverge localizes the cause. Then split by dimension (day, source file/partition, region, customer) to find the smallest slice that carries the discrepancy, and inspect actual rows from that slice on both sides. Typical causes in order of likelihood: late-arriving records, duplicates or a dedup rule dropping valid rows, a filter, a join dropping rows (inner vs left), a timezone/cutoff boundary, a type/rounding conversion.

Q: A pipeline that took 40 minutes now takes 4 hours. What changed?

A: Compare a fast run and a slow run: input volume, data distribution (new skewed key?), plan changes (stats went stale, join strategy flipped from broadcast to sort-merge), file layout (small-file buildup), cluster/capacity contention, upstream delay, a code or dependency deploy. In the Spark UI find the slow stage and look at task-time distribution (skew), shuffle size, spill, and storage wait (Part 4's sequence). Fix the cause, then add a duration-anomaly alert so it's caught early next time.

Q: Duplicates suddenly appear in a table that was clean.

A: Find when they started (partition/ingest timestamp), then what changed at that time. Candidates: a retry or re-run that appended instead of merging, a replayed source batch, a changed or non-unique business key, a CDC event duplication, two jobs writing concurrently, or a join fan-out upstream. Check whether duplicates are exact copies (replay) or differ in values (versioning/key problem). Repair idempotently (dedupe into a new table and swap), fix the root cause, and add a uniqueness check on the true business key.

Q: A job fails intermittently, roughly once a week, and nobody can reproduce it.

A: Collect evidence rather than guessing: correlate failure times with schedules of other jobs, source maintenance windows, rate limits, storage throttling, token/credential expiry, daylight-saving changes, month-end volume, and a specific partition or file. Improve logging so the next occurrence captures the state (inputs, run ID, resource metrics). Make the job resilient meanwhile (retry on the identified transient class, timeouts). Intermittent usually means concurrency, external dependency, or data-dependent edge cases.

Q: Out-of-memory error in a Python ingestion job. Approach?

A: Confirm where memory grows (profile with tracemalloc/memory profiler, watch RSS over time). Usual culprits: materializing a full result set (use cursors/batches), accumulating a list across batches, pandas object-dtype strings, unbounded caches, large intermediate joins, or loading the whole file. Fix by streaming/batching, dropping references, using narrower dtypes/Arrow, and setting memory limits so failure is explicit rather than a mystery kill by the OS.

Q: Two reports disagree and both owners insist theirs is right.

A: Treat it as a definition problem first: write down each report's exact logic (filters, grain, time boundary, source table, inclusion rules) and diff them. Reproduce both numbers from the same raw data. Usually each is correct under its own definition. Resolve by agreeing one governed definition, publishing it in the semantic layer or data dictionary, and deprecating the other, communicating the change to both owners.

Q: How do you write a postmortem?

A: Blameless, factual timeline (detection, impact, mitigation, resolution), root cause (using "five whys" until you reach a systemic cause, not "person X made a mistake"), what worked in detection and response, what didn't, and concrete action items with owners and dates, including a new check/alert and a runbook update.

Q&A 8: WORKING WITH BUSINESS STAKEHOLDERS

Q: A stakeholder says "I need a real-time dashboard." What do you do?

A: Ask what decision it supports and how quickly someone acts on it. "Real time" often means "fresher than the daily report." Offer options with cost and complexity: hourly batch, 5-minute micro-batch, true streaming, and what each means in infrastructure, reliability and cost. Agree on a freshness SLO they can accept and start with the cheapest option that meets it.

Q: How do you translate a vague request into technical requirements?

A: Clarify the question being answered, the metric definitions and grain, the consumers, freshness and accuracy needs, history/backfill needs, access and sensitivity constraints, and the acceptance criteria (how we'll know it's right, for example reconciliation to a known figure). Write it down, confirm it, and keep a short definitions document. Show a quick prototype on real data early, because stakeholders discover what they want by seeing numbers.

Q: Two stakeholders define "active customer" differently. What do you do?

A: Surface the conflict, quantify the difference using both definitions on real data, bring both owners together with a recommendation, and record the decision in a governed metric definition. Where both are legitimately needed, name them differently (active_30d_purchase vs active_30d_login) instead of arguing over one name.

Q: How do you say no (or "not yet") to a request?

A: Explain the trade-off in business terms (risk, cost, timeline), offer alternatives (smaller scope, phased delivery, interim manual extract), and be explicit about what is being deprioritized and why. Protect reliability work and data quality time in planning, since stakeholders rarely ask for it but always suffer when it's missing.

Q: How do you communicate during a data incident?

A: Early, short, regular, and honest: what is affected, which numbers should not be trusted, the current status and next update time. Mark affected datasets/dashboards, notify downstream consumers proactively, and follow with a summary and prevention steps. Trust is lost by silence and by quiet corrections, not by incidents.

Q: What is a data contract and why use one?

A: An agreement between producer and consumer on schema, semantics, freshness, quality expectations and change process (versioning, deprecation notices). Enforced with automated checks in CI or at ingestion, it turns upstream surprises into explicit, negotiated changes and gives you someone to talk to when a field changes.

Q&A 9: LEADING JUNIOR ENGINEERS

Q: How do you approach code review for juniors?

A: Review for correctness and risk first (idempotency, failure handling, data correctness, security), then design and readability, then style (automate style with linters). Explain why, link to principles or examples, distinguish "must fix" from "suggestion", ask questions rather than dictate when the choice is debatable, and praise good decisions specifically. Keep reviews fast so they don't block; use pairing for larger design questions.

Q: How do you delegate work and keep quality up without micromanaging?

A: Match task difficulty to the person's level, give context (the business goal and constraints, not just the ticket), agree on the definition of done, require a short design note for anything non-trivial and review that before code is written, and check in at planned points. Provide guardrails that make the right thing the easy thing: project templates, CI with tests and linting, shared libraries for retry/logging/config, and a reusable pipeline skeleton.

Q: A junior's change broke production. How do you handle it?

A: Fix the incident first, then run a blameless review focused on why the system let it happen (missing test, no staging check, unclear ownership, unsafe deploy), then add the control. Talk to the person privately and supportively: what they learned, what they'd do differently. Make it safe to report mistakes quickly, because hidden mistakes are far more expensive than visible ones.

Q: How do you help a junior grow technically?

A: Give progressively harder, real tasks with clear scope; pair on debugging so they see the method (Section 6), not just the fix; encourage them to write design notes and present them; point them to specific resources; set short-term goals with feedback every week or two; let them own a small component end to end including its monitoring. Measure progress by independence and quality of decisions, not lines of code.

Q: How do you set and maintain engineering standards across a team?

A: Write down a short set of conventions (naming, project structure, testing expectations, logging, error handling, idempotency rules), automate enforcement (pre-commit, CI), keep a living example repository, hold design reviews for significant pipelines, and revisit standards when incidents show a gap. Standards should reduce decisions, not add ceremony.

Q: How do you balance your own hands-on work with leading?

A: Reserve time for focused technical work on the hardest design problems, delegate well-scoped implementation, review rather than rewrite, and make yourself non-blocking (documented decisions, clear ownership, runbooks). If everything waits for you, you've become the bottleneck, which is a leadership failure mode.

Q&A 10: LIVE-CODING PRACTICE

Exercise 1 (Python): merge two sorted iterators of records by key, lazily.

import heapq
from typing import Iterable, Iterator

def merge_sorted(a: Iterable[dict], b: Iterable[dict]) -> Iterator[dict]:
    return heapq.merge(a, b, key=lambda r: r["key"])

Talking point: constant memory, O(n) time; heapq.merge is lazy and requires each input to be pre-sorted.

Exercise 2 (Python): deduplicate a stream keeping the latest record per key, when keys fit in memory but rows don't.

def latest_per_key(rows):
    latest = {}
    for r in rows:
        cur = latest.get(r["key"])
        if cur is None or r["updated_at"] > cur["updated_at"]:
            latest[r["key"]] = r
    return latest.values()

Discuss: memory is O(distinct keys); if keys don't fit, partition the data by hash(key) % N, process each partition independently, or use an external sort / a database or Spark.

Exercise 3 (Python): group a stream into fixed-size batches.

from itertools import islice

def batched(iterable, n):
    it = iter(iterable)
    while batch := list(islice(it, n)):
        yield batch

(Python 3.12+ has itertools.batched.)

Exercise 4 (SQL): customers whose spend increased every month for the last 3 months.

WITH m AS (
  SELECT customer_id,
         DATE_TRUNC('month', order_date) AS month,
         SUM(amount) AS spend
  FROM orders
  WHERE order_date >= ADD_MONTHS(DATE_TRUNC('month', CURRENT_DATE), -3)
    AND order_date <  DATE_TRUNC('month', CURRENT_DATE)
  GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
d AS (
  SELECT customer_id, month, spend,
         LAG(spend) OVER (PARTITION BY customer_id ORDER BY month) AS prev_spend
  FROM m
)
SELECT customer_id
FROM d
GROUP BY customer_id
HAVING COUNT(*) = 3
   AND SUM(CASE WHEN prev_spend IS NOT NULL AND spend > prev_spend THEN 1 ELSE 0 END) = 2;

Discuss: customers missing a month are excluded by COUNT(*) = 3; define what "increase" means for a zero-spend month and ask the interviewer.

Exercise 5 (SQL): sessionize events with a 30-minute inactivity gap.

WITH flagged AS (
  SELECT user_id, event_ts,
         CASE WHEN event_ts - LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts)
                   > INTERVAL 30 MINUTES
                OR LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) IS NULL
              THEN 1 ELSE 0 END AS new_session
  FROM events
)
SELECT user_id, event_ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_ts
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
FROM flagged;

Talking point: flag boundaries with LAG, then a running sum turns flags into session IDs (a variant of the gaps-and-islands idea in Part 5).

Exercise 6 (design, 10 minutes on a whiteboard): "Ingest 2 billion events per day from partner APIs and files into an analytics store with a 1-hour freshness SLO." Cover: landing zone and raw retention, partitioning by event date, idempotent loads keyed on event ID, DLQ, schema contract and drift handling, late data window, monitoring signals (freshness, volume baseline, distribution), backfill path, cost levers (columnar format, compaction, partition pruning), ownership and runbook.

BEHAVIORAL ANSWERS TO PREPARE (STAR SKELETONS)

Prepare one real story for each; fill in your own numbers.

Your own projects give you real material: the metadata-driven GitOps ingestion design, the CDC architecture that was fully built but constrained by infrastructure limits, the medallion platform with reconciliation checks, and the ERP-to-ClickHouse warehouse work (fill in the specifics you want to stand behind).

QUESTIONS TO ASK THEM (SHOWS SENIORITY)

1. What are the largest and most unreliable datasets today, and what fails most often?
2. How are data quality and freshness defined and monitored currently? Who gets paged?
3. What's the split between building new pipelines and maintaining or debugging existing ones?
4. How many juniors would I lead, what's their experience, and how are reviews and standards handled now?
5. Which stakeholder groups depend on the data, and how are definitions governed?
6. What does the stack look like (languages, orchestration, storage, warehouse), and where is the C#/Java used versus Python?
7. What does a successful first six months look like?

GOTCHAS TABLE (FAST REVIEW)

RAPID-FIRE ONE-LINERS

• Robust pipeline = idempotent + replayable + observable + bounded.
• Effectively-once = at-least-once delivery + idempotent writes.
• Billions of rows = minimize bytes scanned and shuffled; clarify question, grain, precision first.
• Debugging = scope, timeline, bisect with control totals, smallest failing slice, hypothesis, fix + check + postmortem.
• Monitoring = freshness, volume, schema, distribution, reconciliation.
• OOP in pipelines = interfaces for sources/sinks, composition, dependency injection, immutable records.
• Leading juniors = context, design review before code, guardrails, blameless culture, grow independence.