Data Engineer Interview Questions

Direct Answer

OLTP systems process operational transactions, while OLAP systems and data warehouses organize historical data for analytical queries, reporting, and decision-making.

Detailed Explanation

-- OLTP query: retrieve one current application.
SELECT
  id,
  user_id,
  company_name,
  status,
  updated_at
FROM job_applications
WHERE id = $1
  AND user_id = $2;

-- OLAP query: analyze monthly outcomes.
SELECT
  DATE_TRUNC('month', created_at) AS month,
  source,
  COUNT(*) AS applications,
  COUNT(*) FILTER (
    WHERE status = 'interview'
  ) AS interviews,
  COUNT(*) FILTER (
    WHERE status = 'offer'
  ) AS offers
FROM analytics.fact_job_applications
GROUP BY
  DATE_TRUNC('month', created_at),
  source
ORDER BY month, source;

Common Interview Pitfalls

• Running large analytical scans directly against a latency-sensitive production database.
• Assuming OLTP and OLAP systems have identical schema and performance requirements.
• Treating the data warehouse as the source of truth for operational writes.
• Copying operational tables into a warehouse without defining analytical meaning.
• Ignoring historical data because the operational system stores only current state.
• Using the term database and data warehouse as though they always describe the same workload.

Direct Answer

Fact tables record measurable business events at a defined grain, while dimension tables provide descriptive context and connect to facts in a star-shaped analytical model.

Detailed Explanation

CREATE TABLE analytics.dim_company (
  company_key BIGINT PRIMARY KEY,
  company_id TEXT NOT NULL,
  company_name TEXT NOT NULL,
  industry TEXT,
  company_size TEXT
);

CREATE TABLE analytics.dim_date (
  date_key INTEGER PRIMARY KEY,
  calendar_date DATE NOT NULL UNIQUE,
  calendar_year INTEGER NOT NULL,
  calendar_month INTEGER NOT NULL,
  month_name TEXT NOT NULL
);

CREATE TABLE analytics.fact_job_application (
  application_key BIGINT PRIMARY KEY,
  application_id TEXT NOT NULL,
  user_key BIGINT NOT NULL,
  company_key BIGINT NOT NULL,
  submitted_date_key INTEGER NOT NULL,
  applications_count INTEGER NOT NULL DEFAULT 1,
  days_to_interview INTEGER,

  FOREIGN KEY (company_key)
    REFERENCES analytics.dim_company(company_key),

  FOREIGN KEY (submitted_date_key)
    REFERENCES analytics.dim_date(date_key)
);

-- Grain:
-- One row per submitted job application.

Common Interview Pitfalls

• Creating a fact table without explicitly defining its grain.
• Mixing event-level and daily aggregate rows in the same fact table.
• Placing descriptive text attributes repeatedly in a large fact table without analysis.
• Counting joined rows without considering one-to-many relationships.
• Assuming every numeric column is an additive fact.
• Designing dimensions around source-system tables instead of analytical business meaning.
• Using a star schema while leaving important dimensions without stable keys.

Direct Answer

Normalization separates data to reduce redundancy and update anomalies, while denormalization duplicates or combines data to simplify reads and improve selected analytical access patterns.

Detailed Explanation

-- Normalized operational model.
CREATE TABLE companies (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  industry TEXT
);

CREATE TABLE job_applications (
  id UUID PRIMARY KEY,
  company_id UUID NOT NULL
    REFERENCES companies(id),
  user_id UUID NOT NULL,
  status TEXT NOT NULL
);

-- Denormalized analytical output.
CREATE MATERIALIZED VIEW analytics.application_summary AS
SELECT
  a.id AS application_id,
  a.user_id,
  a.status,
  c.id AS company_id,
  c.name AS company_name,
  c.industry
FROM job_applications AS a
JOIN companies AS c
  ON c.id = a.company_id;

Common Interview Pitfalls

• Denormalizing data before identifying an actual query or performance requirement.
• Duplicating attributes without defining how copied values remain consistent.
• Normalizing analytical models so aggressively that basic reporting requires excessive joins.
• Using denormalization to hide incorrect relationships or ambiguous grain.
• Assuming normalization always produces faster queries.
• Updating historical analytical values when the model is intended to preserve event-time context.
• Creating wide tables without documenting ownership and refresh behavior.

Direct Answer

Joins combine related rows, aggregations collapse groups, CTEs organize query stages, and window functions calculate across related rows without removing row-level detail.

Detailed Explanation

WITH ordered_events AS (
  SELECT
    application_id,
    status,
    occurred_at,
    event_id,
    LAG(status) OVER (
      PARTITION BY application_id
      ORDER BY occurred_at, event_id
    ) AS previous_status,
    ROW_NUMBER() OVER (
      PARTITION BY application_id
      ORDER BY occurred_at DESC, event_id DESC
    ) AS latest_rank
  FROM application_status_events
),
latest_application_status AS (
  SELECT
    application_id,
    status,
    occurred_at
  FROM ordered_events
  WHERE latest_rank = 1
)
SELECT
  status,
  COUNT(*) AS applications
FROM latest_application_status
GROUP BY status
ORDER BY applications DESC;

Common Interview Pitfalls

• Using an inner join when unmatched rows must remain in the result.
• Overcounting entities after joining to a one-to-many table.
• Using NOT IN with nullable values without considering three-valued logic.
• Applying a window function without deterministic ordering.
• Using DISTINCT to conceal an incorrect join rather than fixing the relationship.
• Grouping at a different grain from the metric being calculated.
• Assuming a common table expression always improves query performance.
• Filtering rows before a window calculation when the excluded rows are needed for context.

Direct Answer

Surrogate keys identify warehouse records independently from source keys, while slowly changing dimension strategies control whether attribute changes overwrite or preserve history.

Detailed Explanation

CREATE TABLE analytics.dim_company (
  company_key BIGINT GENERATED ALWAYS AS IDENTITY
    PRIMARY KEY,
  company_id TEXT NOT NULL,
  company_name TEXT NOT NULL,
  industry TEXT,
  valid_from TIMESTAMPTZ NOT NULL,
  valid_to TIMESTAMPTZ,
  is_current BOOLEAN NOT NULL,

  CHECK (
    valid_to IS NULL OR
    valid_to > valid_from
  )
);

CREATE UNIQUE INDEX one_current_company_version
ON analytics.dim_company(company_id)
WHERE is_current;

-- Type 2 update:
-- 1. Expire the current row.
UPDATE analytics.dim_company
SET
  valid_to = $2,
  is_current = FALSE
WHERE company_id = $1
  AND is_current = TRUE;

-- 2. Insert the new version.
INSERT INTO analytics.dim_company (
  company_id,
  company_name,
  industry,
  valid_from,
  valid_to,
  is_current
)
VALUES ($1, $3, $4, $2, NULL, TRUE);

Common Interview Pitfalls

• Using a changing source identifier as the only warehouse dimension key.
• Applying Type 2 history to every attribute without a business requirement.
• Overwriting historical attributes that reports are expected to preserve.
• Creating overlapping validity periods for one natural key.
• Allowing several current dimension rows for the same natural entity.
• Joining facts to the current dimension row instead of the historically applicable version.
• Failing to define how late-arriving dimensions or facts are handled.
• Treating every source-system change as a valid analytical update.

Direct Answer

Define business processes and grain, ingest immutable source data, create tested dimensions and facts, standardize metrics, protect sensitive data, and support incremental reliable processing.

Detailed Explanation

-- Example event fact at a stable grain.
CREATE TABLE analytics.fact_application_status_event (
  status_event_key BIGINT PRIMARY KEY,
  status_event_id TEXT NOT NULL UNIQUE,
  application_id TEXT NOT NULL,
  user_key BIGINT NOT NULL,
  job_key BIGINT NOT NULL,
  company_key BIGINT NOT NULL,
  status TEXT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  ingested_at TIMESTAMPTZ NOT NULL,
  source_system TEXT NOT NULL,

  CHECK (
    status IN (
      'saved',
      'applied',
      'screening',
      'interview',
      'offer',
      'rejected',
      'withdrawn'
    )
  )
);

-- Cohort-based conversion.
WITH application_cohort AS (
  SELECT
    application_id,
    user_key,
    MIN(occurred_at) FILTER (
      WHERE status = 'applied'
    ) AS applied_at,
    MIN(occurred_at) FILTER (
      WHERE status = 'interview'
    ) AS first_interview_at
  FROM analytics.fact_application_status_event
  GROUP BY application_id, user_key
)
SELECT
  DATE_TRUNC('month', applied_at) AS cohort_month,
  COUNT(*) AS applications,
  COUNT(*) FILTER (
    WHERE first_interview_at IS NOT NULL
  ) AS applications_with_interview,
  COUNT(*) FILTER (
    WHERE first_interview_at IS NOT NULL
  )::NUMERIC /
  NULLIF(COUNT(*), 0) AS interview_conversion
FROM application_cohort
WHERE applied_at IS NOT NULL
GROUP BY DATE_TRUNC('month', applied_at);

Common Interview Pitfalls

• Starting warehouse design from existing source tables instead of business processes and grain.
• Keeping only transformed tables and making historical reprocessing impossible.
• Building funnel metrics from current status instead of historical status events.
• Allowing different dashboards to define the same conversion metric differently.
• Using ingestion time when business analysis requires event time.
• Ignoring duplicate, corrected, late, or out-of-order source records.
• Giving broad analytical users access to unnecessary personal or sensitive data.
• Optimizing partitions and clustering before measuring actual query patterns.
• Creating one universal wide table with several incompatible grains.
• Failing to reconcile warehouse counts with authoritative source systems.

Direct Answer

ETL transforms data before loading it into the target, while ELT loads raw data first and performs transformations within the analytical platform.

Detailed Explanation

-- Raw layer: retain source-aligned records.
CREATE TABLE raw.job_application_events (
  source_event_id TEXT NOT NULL,
  source_payload JSONB NOT NULL,
  source_updated_at TIMESTAMPTZ,
  ingested_at TIMESTAMPTZ NOT NULL,
  ingestion_batch_id TEXT NOT NULL
);

-- Staging layer: parse and standardize.
CREATE VIEW staging.job_application_events AS
SELECT
  source_event_id,
  source_payload ->> 'application_id'
    AS application_id,
  LOWER(source_payload ->> 'status')
    AS normalized_status,
  source_updated_at,
  ingested_at
FROM raw.job_application_events;

-- Serving layer: business-facing output.
CREATE TABLE analytics.fact_application_status_event (
  status_event_id TEXT PRIMARY KEY,
  application_id TEXT NOT NULL,
  status TEXT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL
);

Common Interview Pitfalls

• Using ETL and ELT as interchangeable terms without identifying where transformation occurs.
• Discarding raw source data and making corrected transformations impossible to rerun.
• Applying business logic directly during extraction without retaining source evidence.
• Treating the landing layer as a trusted analytics dataset.
• Mixing source ingestion, business transformation, and publication in one opaque task.
• Assuming ELT eliminates the need for validation and orchestration.

Direct Answer

A full load processes the complete source dataset, while an incremental load processes records added or changed since a recorded boundary such as a watermark.

Detailed Explanation

-- Read from the last successful composite watermark.
SELECT
  source_id,
  application_id,
  status,
  updated_at
FROM source.application_events
WHERE
  (updated_at, source_id) >
  (:last_updated_at, :last_source_id)
ORDER BY updated_at, source_id
LIMIT 10000;

-- Merge records idempotently.
INSERT INTO staging.application_events (
  source_id,
  application_id,
  status,
  updated_at
)
VALUES (
  :source_id,
  :application_id,
  :status,
  :updated_at
)
ON CONFLICT (source_id)
DO UPDATE SET
  application_id = EXCLUDED.application_id,
  status = EXCLUDED.status,
  updated_at = EXCLUDED.updated_at;

Common Interview Pitfalls

• Advancing the watermark before destination writes complete successfully.
• Using only a non-unique timestamp and skipping rows with equal timestamps.
• Assuming source records always arrive in timestamp order.
• Ignoring updates and deletions after an initial insert is processed.
• Using an incremental load without a method to rebuild from source history.
• Rereading overlapping data without using idempotent destination logic.
• Storing pipeline progress only in transient worker memory.

Direct Answer

Use stable record and batch identifiers, deterministic transformations, atomic publication, merge semantics, and retry-safe checkpoints so repeated execution produces the same result.

Detailed Explanation

BEGIN;

CREATE TEMP TABLE incoming_events (
  event_id TEXT PRIMARY KEY,
  application_id TEXT NOT NULL,
  status TEXT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL
) ON COMMIT DROP;

-- Load the current batch into incoming_events.

INSERT INTO analytics.application_status_events (
  event_id,
  application_id,
  status,
  occurred_at
)
SELECT
  event_id,
  application_id,
  status,
  occurred_at
FROM incoming_events
ON CONFLICT (event_id)
DO UPDATE SET
  application_id = EXCLUDED.application_id,
  status = EXCLUDED.status,
  occurred_at = EXCLUDED.occurred_at;

INSERT INTO pipeline.processed_batches (
  batch_id,
  completed_at
)
VALUES (:batch_id, NOW())
ON CONFLICT (batch_id) DO NOTHING;

COMMIT;

Common Interview Pitfalls

• Appending every retry without a stable event or business key.
• Marking a batch successful before destination publication completes.
• Using nondeterministic timestamps or random identifiers during transformations.
• Assuming an orchestration retry begins before every prior side effect.
• Retrying permanently invalid records without quarantine or limits.
• Deleting and rebuilding an entire shared table for one failed partition.
• Claiming exactly-once behavior without evaluating external destination semantics.

Direct Answer

A DAG defines dependency order and scheduling, while tasks should process explicit data intervals, use bounded retries, declare resources, and remain independently observable.

Detailed Explanation

from datetime import datetime, timedelta

from airflow import DAG
from airflow.decorators import task

with DAG(
    dag_id="daily_application_warehouse",
    start_date=datetime(2026, 1, 1),
    schedule="@daily",
    catchup=True,
    max_active_runs=2,
    default_args={
        "retries": 3,
        "retry_delay": timedelta(minutes=5),
    },
) as dag:

    @task(execution_timeout=timedelta(minutes=30))
    def extract(data_interval_start, data_interval_end):
        return {
            "start": data_interval_start.isoformat(),
            "end": data_interval_end.isoformat(),
            "manifest":
                "s3://data/raw/application-events/"
        }

    @task
    def transform(extraction):
        return {
            "start": extraction["start"],
            "end": extraction["end"]
        }

    @task
    def validate(output):
        pass

    validate(transform(extract()))

Common Interview Pitfalls

• Using the task start time instead of the scheduled data interval.
• Passing large datasets through orchestration metadata.
• Retrying deterministic schema or validation errors repeatedly.
• Creating tasks that produce duplicate output when cleared and rerun.
• Allowing unlimited concurrent backfill runs against a shared source.
• Making every task sequential even when dependencies allow parallel execution.
• Using the orchestration database as the analytical data store.
• Marking a task successful before its published data passes validation.

Direct Answer

Backfills should reprocess bounded historical intervals with versioned logic, controlled concurrency, idempotent writes, schema compatibility, validation, and publication safeguards.

Detailed Explanation

# Controlled historical reprocessing.
airflow dags backfill 
  --dag-id daily_application_warehouse 
  --start-date 2026-05-01 
  --end-date 2026-05-31

Common Interview Pitfalls

• Running an unrestricted backfill that overwhelms production sources or the warehouse.
• Using current wall-clock time instead of each historical data interval.
• Writing directly over trusted production partitions before validation.
• Assuming late-arriving records affect only the latest partition.
• Parsing every historical record with assumptions from only the newest source schema.
• Reprocessing data without recording the transformation-code version.
• Running backfills concurrently with scheduled jobs that write the same partitions.
• Changing business definitions historically without communicating metric restatement.

Direct Answer

Use durable raw ingestion, explicit intervals, idempotent transformations, isolated orchestration, tested publication, lineage, observability, backfill support, and governed access.

Detailed Explanation

type BatchManifest = {
  batchId: string;
  source: string;
  intervalStart: string;
  intervalEnd: string;
  schemaVersion: string;
  extractionPosition: string;
  inputFiles: Array<{
    uri: string;
    checksum: string;
    recordCount: number;
  }>;
  transformationVersion: string;
};

type PublicationResult = {
  batchId: string;
  outputDataset: string;
  outputPartition: string;
  sourceRecords: number;
  publishedRecords: number;
  rejectedRecords: number;
  qualityChecksPassed: string[];
  publishedAt: string;
};

async function publishBatch(
  manifest: BatchManifest
): Promise<PublicationResult> {
  await validateManifest(manifest);

  const staged = await transformToStaging(
    manifest
  );

  const quality = await runQualityChecks(
    staged
  );

  if (!quality.passed) {
    throw new Error(
      'Batch failed publication checks'
    );
  }

  return publishPartitionAtomically(
    manifest,
    staged,
    quality
  );
}

Common Interview Pitfalls

• Transforming data without retaining a durable replayable raw layer.
• Using ingestion time as the only boundary for event-time analytics.
• Allowing failed quality checks to publish incomplete production data.
• Keeping extraction checkpoints only inside temporary worker processes.
• Allowing backfills and schedules to overwrite the same partition concurrently.
• Treating every source-schema change as automatically compatible.
• Using the orchestration metadata database to transfer large datasets.
• Collecting pipeline success metrics without measuring data freshness or completeness.
• Giving broad warehouse access to sensitive raw source content.
• Designing for maximum throughput before establishing reproducibility and correctness.

Direct Answer

A Spark driver builds and schedules work, executors run tasks over data partitions, and the cluster manager allocates the resources used by the application.

Detailed Explanation

from pyspark.sql import SparkSession

spark = (
    SparkSession.builder
    .appName("daily-application-metrics")
    .getOrCreate()
)

applications = spark.read.parquet(
    "s3://analytics/raw/job-applications/"
)

print(
    applications.rdd.getNumPartitions()
)

monthly = (
    applications
    .groupBy("application_month", "source")
    .count()
)

monthly.write.mode("overwrite").parquet(
    "s3://analytics/marts/monthly-applications/"
)

Common Interview Pitfalls

• Treating the driver and executors as though they perform identical responsibilities.
• Assuming additional executors improve a stage with very few partitions.
• Collecting a large distributed dataset into driver memory.
• Treating executor loss as equivalent to permanent loss of the original source data.
• Increasing cluster size before inspecting the execution plan and data distribution.
• Running unrelated Spark applications through one unrestricted shared resource pool.

Direct Answer

A DataFrame represents distributed structured data, transformations build a logical plan lazily, and actions trigger Spark to optimize and execute that plan.

Detailed Explanation

from pyspark.sql import functions as F

events = spark.read.parquet(
    "s3://analytics/raw/application-events/"
)

prepared = (
    events
    .filter(F.col("status").isNotNull())
    .select(
        "application_id",
        "status",
        "occurred_at"
    )
    .withColumn(
        "event_date",
        F.to_date("occurred_at")
    )
)

daily_counts = (
    prepared
    .groupBy("event_date", "status")
    .count()
)

# Inspect the optimized and physical plans.
daily_counts.explain(mode="formatted")

# The write action triggers execution.
daily_counts.write.mode("overwrite").parquet(
    "s3://analytics/marts/daily-status-counts/"
)

Common Interview Pitfalls

• Assuming every transformation executes immediately when it is declared.
• Calling collect on a dataset that cannot safely fit in driver memory.
• Using Python user-defined functions when built-in Spark SQL expressions are available.
• Running several actions without recognizing that upstream computation may repeat.
• Ignoring the physical plan when a structured query performs poorly.
• Using an RDD for ordinary structured transformations without a clear requirement.
• Reading every source column when only a small subset is required.

Direct Answer

Partitions determine parallel work placement, while shuffles redistribute records across executors for operations such as joins, grouping, sorting, and repartitioning.

Detailed Explanation

from pyspark.sql import functions as F

applications = spark.read.parquet(
    "s3://analytics/facts/applications/"
)

companies = spark.read.parquet(
    "s3://analytics/dimensions/companies/"
)

# Repartition the large fact dataset for a key-based operation.
partitioned_applications = applications.repartition(
    400,
    "company_id"
)

# Broadcast only when the dimension is safely small.
enriched = partitioned_applications.join(
    F.broadcast(companies),
    on="company_id",
    how="left"
)

summary = (
    enriched
    .groupBy("industry")
    .agg(
        F.countDistinct("application_id")
        .alias("applications")
    )
)

summary.explain(mode="formatted")

Common Interview Pitfalls

• Treating every shuffle as a problem that must always be eliminated.
• Using one partition for a large output and forcing serial execution.
• Broadcasting a dataset that is too large for executor memory.
• Using repartition repeatedly without understanding each shuffle it creates.
• Producing thousands of tiny partitions and output files.
• Assuming source-file partitions remain optimal after joins and aggregations.
• Using groupByKey-style collection when an aggregation can reduce data earlier.

Direct Answer

Inspect task and partition distributions, identify hot keys, filter unnecessary data, use suitable join strategies, salt keys when necessary, and allow AQE to optimize runtime plans.

Detailed Explanation

from pyspark.sql import functions as F

key_distribution = (
    applications
    .groupBy("company_id")
    .count()
    .orderBy(F.desc("count"))
)

key_distribution.show(20, truncate=False)

# Example salting for a known hot key.
salt_buckets = 16

salted_applications = applications.withColumn(
    "salt",
    F.when(
        F.col("company_id") == "hot-company",
        (
            F.rand(seed=42) * salt_buckets
        ).cast("integer")
    ).otherwise(F.lit(0))
)

salt_values = spark.range(
    0,
    salt_buckets
).withColumnRenamed("id", "salt")

salted_companies = (
    companies
    .filter(F.col("company_id") == "hot-company")
    .crossJoin(salt_values)
    .unionByName(
        companies
        .filter(F.col("company_id") != "hot-company")
        .withColumn("salt", F.lit(0))
    )
)

joined = salted_applications.join(
    salted_companies,
    on=["company_id", "salt"],
    how="left"
)

Common Interview Pitfalls

• Increasing executor memory without investigating the skewed key distribution.
• Adding random salting without preserving correct join or aggregation semantics.
• Broadcasting a relation based only on row count rather than serialized size.
• Assuming Adaptive Query Execution corrects every source of skew automatically.
• Ignoring null and default values that concentrate into one join partition.
• Repartitioning on the same severely skewed key without another mitigation.
• Treating long tasks as skew without comparing input and shuffle distributions.
• Using a join that unintentionally creates a many-to-many row explosion.

Direct Answer

Spark can recompute lost partitions from lineage, persistence avoids repeated work, checkpoints truncate lineage using durable storage, and failed tasks can be retried.

Detailed Explanation

from pyspark import StorageLevel

prepared = (
    events
    .filter("application_id IS NOT NULL")
    .select(
        "application_id",
        "status",
        "occurred_at"
    )
    .persist(StorageLevel.MEMORY_AND_DISK)
)

# First action materializes persisted partitions.
event_count = prepared.count()

# Subsequent actions can reuse them.
status_counts = (
    prepared
    .groupBy("status")
    .count()
)

latest_events = (
    prepared
    .groupBy("application_id")
    .max("occurred_at")
)

status_counts.write.mode("overwrite").parquet(
    "s3://analytics/marts/status-counts/"
)

latest_events.write.mode("overwrite").parquet(
    "s3://analytics/marts/latest-events/"
)

prepared.unpersist()

Common Interview Pitfalls

• Caching every DataFrame regardless of reuse or memory cost.
• Forgetting to unpersist datasets after their final use.
• Assuming cached data survives every executor or cluster failure.
• Treating local checkpointing as durable fault-tolerant storage.
• Using retries for deterministic transformation or schema errors.
• Assuming Spark task retry makes an external side effect exactly once.
• Creating extremely long lineage without evaluating checkpointing or materialization.

Direct Answer

Use partitioned durable inputs, explicit schemas, incremental idempotent processing, optimized joins, measured resources, quality gates, observability, and controlled publication.

Detailed Explanation

from pyspark.sql import functions as F

processing_date = dbutils.widgets.get(
    "processing_date"
)

events = (
    spark.read
    .schema(application_event_schema)
    .parquet(
        "s3://analytics/raw/application-events/"
        f"event_date={processing_date}/"
    )
)

companies = (
    spark.read
    .schema(company_schema)
    .parquet(
        "s3://analytics/dimensions/companies/"
    )
)

prepared = (
    events
    .filter(F.col("application_id").isNotNull())
    .select(
        "event_id",
        "application_id",
        "company_id",
        "status",
        "occurred_at"
    )
    .dropDuplicates(["event_id"])
)

enriched = prepared.join(
    F.broadcast(companies),
    on="company_id",
    how="left"
)

output = (
    enriched
    .repartition(
        200,
        F.col("event_date")
    )
)

staging_path = (
    "s3://analytics/staging/"
    f"application-events/{processing_date}/"
)

output.write.mode("overwrite").parquet(
    staging_path
)

run_quality_checks(
    dataset_path=staging_path,
    processing_date=processing_date
)

publish_partition_atomically(
    staging_path=staging_path,
    target_path=(
        "s3://analytics/facts/"
        f"application-events/event_date={processing_date}/"
    )
)

Common Interview Pitfalls

• Starting performance tuning by increasing cluster size without inspecting the physical plan.
• Inferring the schema repeatedly across a large production dataset.
• Partitioning output by a high-cardinality identifier and creating tiny files.
• Broadcasting an input without measuring its serialized size.
• Allowing concurrent runs to overwrite the same output partition.
• Publishing output before completeness and quality checks pass.
• Using Python UDFs for transformations available through Spark SQL functions.
• Ignoring key skew and accidental many-to-many joins.
• Using one extremely large executor configuration for every stage and workload.
• Measuring job success without monitoring data freshness and reconciliation.

Direct Answer

Batch processing handles bounded collections on a schedule, while stream processing continuously handles unbounded events as they arrive with lower processing latency.

Detailed Explanation

type ProcessingRequirement = {
  maximumLatencyMinutes: number;
  requiresHistoricalReplay: boolean;
  inputIsContinuous: boolean;
  outputRequiresImmediateAction: boolean;
};

function chooseProcessingMode(
  requirement: ProcessingRequirement
): 'batch' | 'streaming' | 'hybrid' {
  if (
    requirement.outputRequiresImmediateAction &&
    requirement.inputIsContinuous
  ) {
    return requirement.requiresHistoricalReplay
      ? 'hybrid'
      : 'streaming';
  }

  if (
    requirement.maximumLatencyMinutes >= 60
  ) {
    return 'batch';
  }

  return 'hybrid';
}

Common Interview Pitfalls

• Choosing streaming because it appears more modern without a low-latency requirement.
• Assuming stream processing eliminates the need for historical batch reconciliation.
• Running frequent micro-batches while claiming true per-event processing semantics.
• Building separate batch and streaming definitions that calculate different business metrics.
• Ignoring the operational cost of continuously running stateful streaming jobs.
• Using batch processing for decisions that require immediate user or security action.

Direct Answer

Producers publish events to partitioned topics, offsets identify records within each partition, and consumer groups divide partition ownership among cooperating consumers.

Detailed Explanation

import {
  Kafka,
  logLevel
} from 'kafkajs';

const kafka = new Kafka({
  clientId: 'application-analytics',
  brokers: ['broker-1:9092', 'broker-2:9092'],
  logLevel: logLevel.INFO
});

const consumer = kafka.consumer({
  groupId: 'application-warehouse-v1'
});

await consumer.connect();

await consumer.subscribe({
  topic: 'job-application-events',
  fromBeginning: false
});

await consumer.run({
  eachMessage: async ({
    topic,
    partition,
    message
  }) => {
    const eventId = message.key?.toString();
    const payload = message.value?.toString();

    await processEventIdempotently({
      eventId,
      payload,
      topic,
      partition,
      offset: message.offset
    });
  }
});

Common Interview Pitfalls

• Assuming records are globally ordered across every partition in a topic.
• Using random partitioning when per-entity event ordering is required.
• Adding more consumers than partitions and expecting additional parallelism.
• Treating an offset as globally unique without topic and partition information.
• Using the same consumer group for services that need independent copies of events.
• Committing offsets before required destination processing has completed.
• Choosing partition keys that create severe hot-partition skew.

Direct Answer

Event time reflects when an event occurred, processing time reflects when it was handled, windows group events, and watermarks estimate event-time progress.

Detailed Explanation

from datetime import timedelta

import apache_beam as beam

events = (
    pipeline
    | 'ReadEvents' >> read_application_events()
    | 'AssignEventTimestamps' >> beam.Map(
        attach_source_event_timestamp
    )
)

windowed = (
    events
    | 'FiveMinuteWindows' >> beam.WindowInto(
        beam.window.FixedWindows(5 * 60),
        allowed_lateness=10 * 60,
        accumulation_mode=(
            beam.trigger.AccumulationMode.ACCUMULATING
        )
    )
    | 'KeyByStatus' >> beam.Map(
        lambda event: (event['status'], 1)
    )
    | 'CountByStatus' >> beam.CombinePerKey(sum)
)

Common Interview Pitfalls

• Using processing time for a business metric that requires source event time.
• Assuming a watermark guarantees that no earlier event can arrive.
• Dropping late data without measuring or documenting the resulting incompleteness.
• Keeping windows open indefinitely and allowing unbounded state growth.
• Using one event timestamp field without defining its business meaning.
• Ignoring idle partitions that prevent watermark advancement.
• Comparing real-time and batch metrics without applying the same late-data policy.
• Using a sliding window without accounting for records appearing in several windows.

Direct Answer

At-most-once may lose records, at-least-once may duplicate them, and exactly-once requires coordinated processing, state, and destination semantics.

Detailed Explanation

BEGIN;

INSERT INTO analytics.application_events (
  event_id,
  application_id,
  status,
  occurred_at,
  source_partition,
  source_offset
)
VALUES (
  :event_id,
  :application_id,
  :status,
  :occurred_at,
  :source_partition,
  :source_offset
)
ON CONFLICT (event_id)
DO UPDATE SET
  application_id = EXCLUDED.application_id,
  status = EXCLUDED.status,
  occurred_at = EXCLUDED.occurred_at,
  source_partition = EXCLUDED.source_partition,
  source_offset = EXCLUDED.source_offset;

INSERT INTO streaming_consumer_progress (
  consumer_name,
  source_partition,
  committed_offset
)
VALUES (
  :consumer_name,
  :source_partition,
  :next_offset
)
ON CONFLICT (
  consumer_name,
  source_partition
)
DO UPDATE SET
  committed_offset = EXCLUDED.committed_offset;

COMMIT;

Common Interview Pitfalls

• Claiming exactly-once behavior based only on the message broker configuration.
• Committing source progress before the destination effect succeeds.
• Using at-least-once processing without stable event identifiers.
• Replaying historical events through consumers that trigger irreversible side effects.
• Deduplicating events by complete payload rather than a logical event key.
• Keeping deduplication state forever without a bounded retention requirement.
• Assuming an external API participates in a stream-processing transaction.
• Selecting expensive exactly-once processing when duplicates are safely tolerated.

Direct Answer

Define enforceable schemas and quality rules, track dataset lineage and ownership, classify sensitive fields, measure freshness, and control publication and access.

Detailed Explanation

version: 2

models:
  - name: fact_application_events
    description: >
      One row per unique application status event.
    config:
      contract:
        enforced: true

    columns:
      - name: event_id
        data_type: string
        constraints:
          - type: not_null
          - type: unique

      - name: application_id
        data_type: string
        constraints:
          - type: not_null

      - name: status
        data_type: string
        constraints:
          - type: not_null
        data_tests:
          - accepted_values:
              arguments:
                values:
                  - saved
                  - applied
                  - interview
                  - offer
                  - rejected

      - name: occurred_at
        data_type: timestamp
        constraints:
          - type: not_null

Common Interview Pitfalls

• Testing only whether a pipeline task ran without validating the produced data.
• Treating schema compatibility as proof that business meaning is unchanged.
• Adding fields without documenting ownership, meaning, or sensitivity.
• Blocking every pipeline for minor noncritical quality deviations.
• Publishing incomplete partitions before freshness and reconciliation checks pass.
• Tracking table-level lineage while ignoring important field-level transformations.
• Giving broad analytical access to raw personal or document content.
• Changing or deleting contracted fields without a deprecation period.
• Defining quality thresholds without an owner or response process.

Direct Answer

Use durable partitioned ingestion, governed schemas, event-time processing, replayable storage, idempotent sinks, autoscaling, quality controls, and observable recovery paths.

Detailed Explanation

type StreamingEvent<TPayload> = {
  eventId: string;
  eventType: string;
  eventVersion: number;
  partitionKey: string;
  occurredAt: string;
  producer: {
    service: string;
    version: string;
  };
  correlationId?: string;
  payload: TPayload;
};

type ApplicationStatusChanged = {
  applicationId: string;
  userId: string;
  previousStatus: string | null;
  currentStatus: string;
};

const event: StreamingEvent<
  ApplicationStatusChanged
> = {
  eventId: crypto.randomUUID(),
  eventType: 'application.status.changed',
  eventVersion: 1,
  partitionKey: 'application_123',
  occurredAt: new Date().toISOString(),
  producer: {
    service: 'application-service',
    version: '2026.08.05'
  },
  correlationId: 'request_456',
  payload: {
    applicationId: 'application_123',
    userId: 'user_789',
    previousStatus: 'applied',
    currentStatus: 'interview'
  }
};

Common Interview Pitfalls

• Using an event service without retaining enough history for required replay.
• Selecting a partition key that sends most traffic to one partition.
• Publishing events without stable identifiers or schema versions.
• Treating broker delivery as proof that the downstream destination was updated.
• Allowing poison messages to retry forever and block partition progress.
• Using processing time when reports require business event time.
• Sending sensitive documents or secrets through broadly accessible streams.
• Deploying stateful processor changes without a state migration or replay plan.
• Monitoring message throughput without monitoring consumer lag and event freshness.
• Claiming exactly-once behavior without evaluating the final sink.

Direct Answer

Partitioning skips irrelevant data ranges, clustering groups related values, indexes accelerate selected lookups, and columnar formats reduce analytical I/O.

Detailed Explanation

-- Warehouse-style table layout.
CREATE TABLE analytics.application_events (
  event_id TEXT NOT NULL,
  application_id TEXT NOT NULL,
  company_id TEXT NOT NULL,
  status TEXT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  event_date DATE NOT NULL
)
PARTITION BY RANGE (event_date);

CREATE TABLE analytics.application_events_2026_08
PARTITION OF analytics.application_events
FOR VALUES FROM ('2026-08-01')
TO ('2026-09-01');

CREATE INDEX application_events_company_date_idx
ON analytics.application_events_2026_08 (
  company_id,
  event_date
);

-- A filter on event_date allows partition pruning.
SELECT
  company_id,
  COUNT(*) AS applications
FROM analytics.application_events
WHERE event_date >= DATE '2026-08-01'
  AND event_date < DATE '2026-09-01'
GROUP BY company_id;

Common Interview Pitfalls

• Partitioning by a high-cardinality identifier and creating thousands of tiny partitions.
• Assuming partitioning automatically helps queries that do not filter the partition field.
• Creating indexes for every column without considering write and storage overhead.
• Using CSV for large analytical datasets when column pruning and compression are important.
• Creating very small Parquet files that increase scheduling and metadata overhead.
• Selecting clustering columns that are rarely used in filters or joins.
• Confusing database indexes with data-lake partition directories.

Direct Answer

Freshness measures how current data is, latency measures processing delay, throughput measures volume, completeness measures expected coverage, and reliability measures sustained correctness.

Detailed Explanation

type DataReliabilityMetrics = {
  sourceMaxEventTime: Date;
  publishedAt: Date;
  expectedRecordCount: number;
  publishedRecordCount: number;
  duplicateRecordCount: number;
  rejectedRecordCount: number;
};

function calculateReliability(
  metrics: DataReliabilityMetrics
) {
  const freshnessSeconds =
    (
      metrics.publishedAt.getTime() -
      metrics.sourceMaxEventTime.getTime()
    ) / 1000;

  const completeness =
    metrics.expectedRecordCount === 0
      ? 1
      : metrics.publishedRecordCount /
        metrics.expectedRecordCount;

  const duplicateRate =
    metrics.publishedRecordCount === 0
      ? 0
      : metrics.duplicateRecordCount /
        metrics.publishedRecordCount;

  return {
    freshnessSeconds,
    completeness,
    duplicateRate,
    rejectedRecordCount:
      metrics.rejectedRecordCount
  };
}

Common Interview Pitfalls

• Treating task completion as proof that the published data is complete.
• Using freshness and processing duration as though they mean the same thing.
• Reporting throughput without measuring duplicates or rejected records.
• Defining latency without identifying its start and end points.
• Monitoring only infrastructure health and not data-product health.
• Using one freshness target for datasets with different business requirements.
• Ignoring source delays when interpreting pipeline latency.

Direct Answer

Inspect the physical plan and runtime metrics, reduce scanned data, preserve pruning and pushdown, validate estimates, improve joins, and tune physical design from evidence.

Detailed Explanation

EXPLAIN (
  ANALYZE,
  BUFFERS,
  VERBOSE
)
SELECT
  c.industry,
  COUNT(*) AS applications
FROM analytics.fact_job_application AS a
JOIN analytics.dim_company AS c
  ON c.company_key = a.company_key
WHERE a.submitted_date >= DATE '2026-08-01'
  AND a.submitted_date < DATE '2026-09-01'
GROUP BY c.industry;

-- Review:
-- 1. Estimated versus actual rows.
-- 2. Scan type and partition pruning.
-- 3. Join algorithm.
-- 4. Sort or hash memory behavior.
-- 5. Buffer reads and execution time.

Common Interview Pitfalls

• Tuning a query without inspecting its physical execution plan.
• Adding indexes without confirming the filter selectivity and workload.
• Wrapping partition fields in expressions that prevent effective pruning.
• Selecting every column from a columnar dataset when only a few are required.
• Ignoring stale statistics and inaccurate row-count estimates.
• Increasing compute resources without detecting an accidental many-to-many join.
• Using EXPLAIN ANALYZE on a production write statement without understanding that it executes.
• Optimizing runtime while failing to verify that query results remain correct.

Direct Answer

Track data and execution health, classify failures, retry transient errors safely, preserve checkpoints, isolate bad data, reconcile outputs, and replay bounded intervals.

Detailed Explanation

type PipelineRun = {
  runId: string;
  intervalStart: string;
  intervalEnd: string;
  codeVersion: string;
  sourceRecords: number;
  destinationRecords: number;
  duplicateRecords: number;
  rejectedRecords: number;
  status:
    | 'running'
    | 'validated'
    | 'published'
    | 'failed';
};

function validateRun(run: PipelineRun): void {
  const accountedFor =
    run.destinationRecords +
    run.rejectedRecords;

  if (accountedFor !== run.sourceRecords) {
    throw new Error(
      'Source and destination counts do not reconcile'
    );
  }

  if (run.duplicateRecords > 0) {
    throw new Error(
      'Duplicate records detected'
    );
  }
}

async function recoverRun(
  failedRun: PipelineRun
): Promise<void> {
  await replayInterval({
    start: failedRun.intervalStart,
    end: failedRun.intervalEnd,
    codeVersion: failedRun.codeVersion,
    idempotent: true
  });
}

Common Interview Pitfalls

• Monitoring task success without validating the resulting dataset.
• Retrying deterministic data or schema failures repeatedly.
• Restarting failed workers before preserving useful evidence.
• Running recovery writes without stable keys or partition isolation.
• Publishing partially recovered output before reconciliation passes.
• Quarantining invalid records without retaining their source context.
• Rebuilding an entire warehouse when only one bounded interval failed.
• Alerting on every retry rather than actionable user or data-product impact.

Direct Answer

Measure cost by workload and data product, reduce unnecessary scans and recomputation, manage retention and file layout, isolate workloads, and optimize from usage evidence.

Detailed Explanation

type DataWorkloadCost = {
  workload: string;
  computeCost: number;
  storageCost: number;
  networkCost: number;
  successfulRuns: number;
  processedTerabytes: number;
};

function calculateUnitCost(
  cost: DataWorkloadCost
) {
  const totalCost =
    cost.computeCost +
    cost.storageCost +
    cost.networkCost;

  return {
    workload: cost.workload,
    totalCost,
    costPerSuccessfulRun:
      cost.successfulRuns === 0
        ? null
        : totalCost / cost.successfulRuns,
    costPerTerabyte:
      cost.processedTerabytes === 0
        ? null
        : totalCost /
          cost.processedTerabytes
  };
}

Common Interview Pitfalls

• Reducing retention without confirming replay, audit, or compliance requirements.
• Measuring total cloud spend without attributing cost to workloads or data products.
• Using full refreshes for large datasets when safe incremental processing is available.
• Allowing development queries and backfills to consume unrestricted production capacity.
• Creating many small compressed files that increase planning and read overhead.
• Deleting raw data while retaining only derived outputs that cannot be reproduced.
• Optimizing for minimum cost while violating freshness or reliability objectives.
• Scaling compute without controlling the volume of data scanned.

Direct Answer

Use durable source-aligned ingestion, governed contracts, layered transformations, replayable processing, quality-gated publication, isolated workloads, observability, and tested recovery.

Detailed Explanation

type DataProductDefinition = {
  name: string;
  owner: string;
  grain: string;
  sources: string[];
  serviceLevel: {
    maximumFreshnessMinutes: number;
    minimumCompleteness: number;
  };
  qualityChecks: Array<{
    name: string;
    behavior:
      | 'block-publication'
      | 'quarantine'
      | 'warn';
  }>;
  security: {
    classification:
      | 'public'
      | 'internal'
      | 'confidential'
      | 'restricted';
    retentionDays: number;
  };
  recovery: {
    replayable: boolean;
    recoveryTimeMinutes: number;
    recoveryPointMinutes: number;
  };
};

const applicationFunnel: DataProductDefinition = {
  name: 'application-funnel',
  owner: 'career-analytics',
  grain: 'one row per job application',
  sources: [
    'application-status-events',
    'jobs',
    'companies'
  ],
  serviceLevel: {
    maximumFreshnessMinutes: 30,
    minimumCompleteness: 0.999
  },
  qualityChecks: [
    {
      name: 'unique-application-id',
      behavior: 'block-publication'
    },
    {
      name: 'known-status-values',
      behavior: 'quarantine'
    }
  ],
  security: {
    classification: 'confidential',
    retentionDays: 730
  },
  recovery: {
    replayable: true,
    recoveryTimeMinutes: 120,
    recoveryPointMinutes: 15
  }
};

Common Interview Pitfalls

• Building the platform around tools instead of business requirements and data products.
• Transforming source data without retaining a durable replayable layer.
• Allowing batch and streaming systems to calculate the same metric differently.
• Publishing data without ownership, grain, freshness, or quality expectations.
• Giving broad users access to raw sensitive source content.
• Treating successful backups as proof that restoration will work.
• Allowing unrestricted backfills to compete with production workloads.
• Optimizing performance before establishing correctness and reconciliation.
• Supporting machine-learning features without point-in-time correctness.
• Centralizing technical infrastructure while leaving data meaning and quality unowned.