Data Engineering

The dangerous number

Data engineering interviews concentrate senior judgment in an unusual place: a result can be wrong without looking broken. A duplicated event stream can still feed a polished dashboard. A late vendor file can quietly lower revenue. A change in identity rules can move daily active users while every scheduled job reports success.

Consider one prompt and let it resist the first answer:

Product leadership wants daily active users across web, iOS, and Android.
Clients emit different events, people may be anonymous before login, mobile
events arrive late, and the metric will guide next quarter's investment.
Design the data path and rollout.

Naming Kafka, Spark, Airflow, dbt, Snowflake, BigQuery, Databricks, Iceberg, or Delta does not yet answer the question. Those may become reasonable choices, but none tells leadership what an active user is or when the number is safe to act on.

This is trust under delay. Source applications change. Offline devices reconnect. Users merge accounts. Business definitions evolve. The pipeline’s job is not to make those facts disappear; it is to carry their consequences honestly to the consumer.

Begin with the decision, not the transport

Roadmap investment needs a stable comparative measure. It does not need sub-second finality. That observation changes the design. A daily batch metric with a published freshness target may be decision-grade, while a low-latency stream can remain explicitly provisional for launch monitoring.

Before drawing boxes, define the result:

• The grain is one resolved person per reporting day.
• A qualifying action is a deliberate product interaction, not any emitted event. Internal traffic and known bots are excluded.
• The reporting time zone is named. Event time assigns activity to a day; ingestion time tells us when the evidence arrived.
• Anonymous activity joins an authenticated person only under a documented identity rule. Ambiguous joins remain ambiguous rather than being forced.
• A day is provisional during the accepted late-arrival window and final only after the closing policy has run.

These statements form the semantic and temporal contract. They also expose questions that architecture alone would hide. Does activity before login belong to the person after login? Can an identity merge restate last month? What happens at a daylight-saving boundary? The interview becomes useful when the candidate is willing to decide, qualify, and test those details.

The source contract comes next. Each client emits a versioned activity event with an event identifier, client timestamp, ingestion timestamp, platform and app version, activity type, and whatever anonymous or authenticated identifier is legitimately available. Producers own compatibility and instrumentation health. The data team owns normalization and consumer status. Neither team can promise correctness alone.

Follow one event to the published metric

Keep raw events immutable enough to replay. Landing storage preserves the original payload and ingestion metadata, partitions it for bounded reads, and restricts identifiers more tightly than the final metric table. Malformed or unsupported versions go to a visible quarantine path; silently dropping them would turn a parser decision into a product trend.

Normalization converts client-specific event names and versions into the agreed activity vocabulary. It records reject reasons and the transformation version. An identity model then maps device, anonymous, and account identifiers with effective times. That model is a slowly changing relationship, not a timeless lookup: a merge learned today must have an explicit policy for whether it changes yesterday’s count.

The metric transform produces at most one row per resolved person and day. It applies exclusions, carries a provisional-or-final state, and records the metric-definition version. A semantic layer or certified model publishes that definition once for dashboards and downstream analysis. The dashboard shows freshness, owner, definition, and known caveats beside the value. A green job status is operational evidence; it is not a quality claim.

Orchestration should express this dependency without becoming the only place where meaning lives. Runs have stable identities, bounded retries, input partitions, code and contract versions, and an owner. Rerunning a partition is idempotent: it either overwrites that partition deterministically or merges on a key whose conflict behavior is defined. Sensors distinguish “not arrived” from “arrived empty.” Backfills use the same transformations as scheduled work and cannot accidentally mix old and new definitions.

Warehouses and lakehouses are choices inside this design. Governed SQL, concurrency control, workload isolation, and predictable BI service may favor a managed warehouse. Large shared files, several compute engines, batch and ML reuse, or open table formats may favor a lakehouse. Either choice inherits work: partition and clustering strategy, compaction or materialization, catalog discipline, schema evolution, retention, access policy, and cost attribution. “Open” and “managed” move responsibilities; they do not remove them.

Quality checks should threaten the claim

The useful checks are the ones that could stop publication or change how the number is labeled.

Volume by platform and app version reveals an instrumentation loss hidden by the total. Schema and accepted-value checks catch a client that renamed an activity. Uniqueness at the person-day grain catches duplication after a join. Identity-resolution coverage detects a login-flow change that reclassifies anonymous users. Late-arrival rate measures how much provisional days usually move. Comparison with the previous metric gives the rollout an independent control.

Every check needs scope, threshold, response, and owner. A failed freshness check might keep yesterday’s trusted value visible with a delayed label. A uniqueness violation might quarantine a new partition. An identity-coverage shift might permit exploratory use but block investment reporting. An alert with no control attached merely teaches people to ignore the data platform.

Lineage makes those controls usable. It should answer which clients and raw partitions produced the metric, which definition and transformation versions ran, which dashboards and exports consume it, and who owns each boundary. During a schema migration or incident, that is blast-radius evidence, not catalog decoration.

Batch, stream, CDC, or events

Choose ingestion from the fact and the decision.

Application events fit product behavior such as sessions or workflow steps, but client drift, retries, bot traffic, identity stitching, and taxonomy decay belong in the design. Change data capture fits database state whose committed changes are authoritative, but it couples consumers to source schemas, transactions, deletes, and migrations. Files and snapshots may be the honest interface for vendors or bulk recomputation. A stream earns its complexity when delay creates material harm or enables a specific user experience.

Streaming changes the temporal problem rather than abolishing it. Event time and processing time diverge. Watermarks express a policy about how long state stays open; they are not proof that no older event will arrive. Checkpoints recover processor state, but end-to-end “exactly once” still depends on source identity, broker behavior, state updates, sink commits, and external effects. A credible answer says where duplication remains possible and makes writes idempotent or deduplicated there.

For the DAU prompt, the low-latency view can update as events arrive while marking recent days provisional. The daily reconciliation path then consumes the accepted late window, re-evaluates identity, and finalizes partitions. This hybrid costs more and creates two paths to compare. It is justified only if somebody can name a decision that benefits from the early view.

The coding round: preserve the grain

Practical rounds may ask for SQL, event parsing, incremental loading, deduplication, data tests, or a broken transformation. The strongest opening is usually an invariant, not syntax: “After this step there is at most one row per event”; “this join must not multiply people”; “this rerun produces the same partition.”

For a daily aggregate, begin by deduplicating stable event identifiers before resolving identity. Assign the reporting day from event time under the named time-zone rule, then select qualifying activity and reduce to the person-day grain. In portable pseudocode SQL:

with one_event as (
  select *
  from (
    select e.*,
           row_number() over (
             partition by event_id order by ingested_at
           ) as copy_number
    from normalized_activity e
    where event_time >= :recompute_from
  ) ranked
  where copy_number = 1
), person_day as (
  select distinct
         identity.person_id,
         reporting_date(one_event.event_time, :reporting_zone) as activity_day
  from one_event
  join identity_resolution identity
    on identity.observed_id = one_event.observed_id
   and one_event.event_time >= identity.effective_from
   and one_event.event_time < identity.effective_to
  where one_event.activity_type in (:qualifying_actions)
)
select activity_day, count(*) as daily_active_users
from person_day
group by activity_day;

The query deliberately leaves policy visible. What happens when identity has no match? Are effective intervals non-overlapping? Is reporting_date defined for ambiguous local times? How far back does :recompute_from look, and what arrivals fall outside it? An interviewer may perturb any of those assumptions. Do not patch each case with an unexplained filter. Restate the invariant and decide how the consumer should see uncertainty.

Incremental loading adds deletes, updates, lookback windows, and deterministic merge behavior. Semi-structured parsing adds version dispatch, missing-field policy, quarantine, and reject-rate monitoring. Query optimization begins only after correctness: inspect scanned partitions, join cardinality, shuffle, materialization, repeated access patterns, and workload isolation before buying more compute.

When the official number falls

Now change the scene. After an ordinary warehouse deploy, the CFO’s revenue dashboard drops 20 percent. “Rerun the DAG” is not yet a diagnosis and may publish the same defect twice.

First contain the decision risk. Mark the affected view unsafe, identify the reporting periods, dashboards, exports, customer-facing analytics, and models that consume it, and preserve the suspect output. If the prior version remains trusted, serve it with a staleness notice. Finance gets a time for the next update and a statement of what is known, not an unqualified estimate.

Trace backward from the visible total. Compare dashboard filters and semantic definitions, then sums, row counts, null rates, uniqueness, and join cardinality at each transformation boundary. Inspect orchestration for skipped, partial, late, or empty inputs. Compare source orders, payments, refunds, currencies, tax rules, subscription states, and schemas. Find the first boundary where an invariant changes; do not let a plausible recent deploy end the search prematurely.

Suppose a new customer dimension contains two current rows for some accounts, multiplying refunds in the revenue join. The immediate repair restores the one-current-customer invariant, rebuilds only affected partitions with an idempotent run, and validates revenue against independent payment and ledger controls. The release note states which dates and reports were restated. A uniqueness gate at the dimension boundary and a join-cardinality assertion address this failure more precisely than a generic anomaly detector.

That sequence exposes production judgment across data quality, orchestration, lineage, recovery, and communication. It also gives a project deep dive its necessary shape: impact, evidence, decision, repair, institutional change, and what the engineer would now do earlier.

Governance and cost alter the architecture

The raw activity path contains identifiers that the final count does not need. Minimize them at collection, classify their sensitivity, restrict raw access, mask or aggregate in broader models, log approved access, and give deletion and retention work an executable path. A catalog entry cannot compensate for copied raw identifiers in uncontrolled analyst tables.

Historical correction and deletion can conflict. Immutable landing data still needs retention limits and governed erasure or tombstone handling where law and policy require it. Backfills must reproduce authorized data, not resurrect records that should no longer exist. In an interview, name the privacy owner and approval boundary rather than treating “compliance” as a late box.

Cost is another contract with consumers. Attribute storage and compute by workload, team, tenant, or metric; identify scans, joins, retention, refresh frequency, small-file overhead, and idle capacity; then change the expensive mechanism with its user consequence visible. Removing an unused model is better than tuning it. Moving a dashboard from minute to hourly refresh may be better than preserving a latency nobody uses. Consolidating compute can save money while worsening noisy-neighbor behavior and recovery isolation.

For the DAU system, partition pruning, bounded recomputation, deliberate materialization, raw-retention policy, and separation of exploratory from certified workloads make spend legible. “Reduce warehouse cost by 30 percent” is therefore a product and governance prompt as much as a tuning prompt.

What each interview round can reveal

A likely loop includes practical coding, system design, debugging, project deep dive, and behavioral discussion. The artifacts vary, but the same question persists: can this person keep meaning and ownership intact while the system changes?

In coding, state grain, null, time, deduplication, and rerun invariants before optimizing. In system design, follow one fact from producer to consumer and include quality gates, lineage, recovery, privacy, and economics. In debugging, localize the first broken boundary, quantify blast radius, contain unsafe use, and match prevention to cause.

For a deep dive, prepare a reliability story, a disputed-semantics story, and a platform-economics or governance story. Name the durable artifact left behind: a producer contract, versioned definition, quality gate, lineage view, runbook, certified dataset, migration plan, cost attribution, or deprecation policy. The interviewer needs evidence that your influence survived your attention.

Behavioral probes often put trust under pressure. Product may want an unstable dashboard released. A source team may keep breaking consumers. Finance and product may defend different revenue definitions. Privacy review may block a dataset. Cost reduction may threaten freshness. Strong answers make the conflict specific, separate exploratory from decision-grade use, reduce or version scope where appropriate, assign owners, and give stakeholders a useful next step. They neither surrender the constraint nor hide behind it.

Failure patterns worth interrupting

Listen for the sentence that closes the data question too early:

• “We use Kafka, Spark, and Airflow.” Tools do not define grain, correctness, ownership, or repair.
• “The dashboard is green.” A successful run can publish semantically wrong or incomplete data.
• “The watermark handles late events.” A watermark closes state according to policy; later evidence still needs an explicit fate.
• “Exactly once prevents duplicates.” End-to-end guarantees stop at the first boundary not participating in the protocol.
• “We can backfill it.” A repair needs scope, idempotency, validation, rollback, authorized inputs, and consumer notice.
• “Lineage is in the catalog.” During change or failure, lineage must reveal owners, versions, inputs, consumers, and blast radius.
• “We will add privacy later.” Centralized data makes delayed minimization and access design more dangerous, not less.
• “Use less compute.” Cost work must preserve an explicit freshness, correctness, isolation, or recovery promise.

Each correction reopens the missing contract rather than substituting a new product name.

Practice the metric until it changes

Use the DAU prompt as a rehearsal and vary one constraint at a time:

1. Write the source owner, qualifying action, grain, identity rule, reporting zone, late window, finalization policy, access boundary, and metric owner.
2. Add a duplicate emit and an app version that renames one activity. Show which checks fire and which publication state consumers see.
3. Let an account merge arrive a week late. Decide whether history changes, what partitions rerun, and how the restatement is announced.
4. Replace daily investment reporting with an operational intervention that needs results in five minutes. Justify the new streaming state and its provisional semantics.
5. Delete one person’s data after a historical metric was finalized. Trace erasure, recomputation, audit evidence, and aggregate policy.
6. Cut cost without saying “use less compute.” Name unused assets, workload owners, refresh schedules, materializations, retention, and rollback.

Then practice the revenue incident. State the first invariant you would check, the immediate consumer notice, the independent validation control, and the cause-specific prevention. Finally, rehearse saying, in two minutes, that a dataset is not decision-grade yet while still giving the stakeholder a safe way to learn.

A compact data answer frame

When a prompt sprawls, make these lines concrete:

Consumer decision and acceptable delay:
Source fact, producer, and compatibility contract:
Grain, identity, event-time, and definition rules:
Ingestion, storage, transformation, and serving path:
Quality evidence, lineage, freshness state, and owners:
Replay, backfill, rollback, and restatement policy:
Access, minimization, retention, audit, and deletion path:
Cost driver and the user promise it buys:

You are ready when a successful job no longer persuades you by itself. You can follow a number from a consumer decision back through its semantic model, transformations, orchestration, raw evidence, and producing system; then move forward again through repair, governance, cost, and communication. That is the specialty signal the interview is trying to sample.

Related links

• Databases and Storage Engines
• Transactions and Isolation
• Queues, Logs, and Stream Processing
• Search and Retrieval Systems
• Observability, Performance, and Cost