USA
Canada
UK
Europe
Australia
Singapore
Japan
IndiaQ1. What is the difference between ETL and ELT, and when do you use each?
Short answer: ETL transforms before loading; ELT loads raw then transforms in the warehouse.
Strong answer: ETL transforms data before loading into the target — good when the target is expensive or transformations are heavy and pre-defined. ELT loads raw data into a powerful warehouse/lakehouse (Snowflake, BigQuery, Databricks) and transforms there with SQL — the modern default, leveraging cheap storage and scalable compute for flexibility and reprocessing.
Likely follow-up: Why has ELT become the default in modern stacks?
What the interviewer is checking: Modern data-architecture understanding.
Common mistake: Assuming ETL is always right regardless of the platform.
Q2. How do you design a scalable data pipeline?
Short answer: Ingest → store → transform → serve, with idempotency, partitioning, orchestration and observability.
Strong answer: Separate ingestion (batch/stream), storage (data lake/warehouse), transformation (SQL/Spark) and serving. Make steps idempotent and re-runnable, partition data for parallelism and pruning, orchestrate with a scheduler (Airflow/dbt), add data-quality checks, and monitor for failures and freshness. Design for schema evolution and backfills from day one.
Likely follow-up: How do you handle a late-arriving or malformed record?
What the interviewer is checking: End-to-end pipeline design.
Common mistake: A brittle, non-idempotent pipeline with no quality checks.
Q3. What is data partitioning and why does it matter?
Short answer: Splitting data by a key (often date) so queries scan less and jobs parallelize.
Strong answer: Partitioning divides a dataset by a column (commonly date) so the engine reads only relevant partitions (partition pruning) and can process them in parallel. Good partitioning slashes query cost and runtime; over-partitioning creates many tiny files and overhead. Choose a key that matches query filters and data volume.
Likely follow-up: What problem do too many small partitions cause?
What the interviewer is checking: Performance and storage understanding.
Common mistake: Partitioning on a high-cardinality column, creating tiny-file problems.
Q4. Explain batch vs streaming processing.
Short answer: Batch processes bounded data on a schedule; streaming processes unbounded data continuously.
Strong answer: Batch processes large, bounded datasets periodically — simple and cost-efficient for non-urgent analytics. Streaming (Kafka + Flink/Spark Structured Streaming) processes events continuously for low-latency use cases (fraud, monitoring, real-time dashboards), but adds complexity around state, ordering and exactly-once semantics. Many systems use both (lambda/kappa).
Likely follow-up: What makes exactly-once hard in streaming?
What the interviewer is checking: Processing-model understanding.
Common mistake: Using streaming complexity where a batch job would do.
Q5. How do you ensure data quality in a pipeline?
Short answer: Validate schema, nulls, ranges, uniqueness and freshness; fail loudly and quarantine bad data.
Strong answer: Add automated checks for schema conformance, null/range/format constraints, uniqueness, referential integrity and freshness (tools like dbt tests or Great Expectations). Quarantine or reject bad rows rather than silently dropping them, alert on failures, and track data lineage so you can trace issues back to the source.
Likely follow-up: What do you do with rows that fail validation?
What the interviewer is checking: Data-reliability discipline.
Common mistake: Silently dropping bad data and reporting wrong numbers.
Q6. What is a data lakehouse?
Short answer: An architecture combining a data lake’s cheap storage with warehouse-like management (e.g., Delta Lake).
Strong answer: A lakehouse (Delta Lake, Iceberg, Hudi on Databricks/Spark) adds ACID transactions, schema enforcement, time travel and performance to cheap object-storage data lakes — unifying data engineering, BI and ML on one copy of data instead of separate lake and warehouse silos. It is a dominant 2026 pattern.
Likely follow-up: What does Delta Lake add over plain Parquet on a lake?
What the interviewer is checking: Current data-platform knowledge.
Common mistake: Confusing a lakehouse with a traditional warehouse or raw lake.
Q7. How do you optimize a slow Spark job?
Short answer: Fix skew and shuffles, cache wisely, tune partitions, and avoid wide transformations where possible.
Strong answer: Profile the Spark UI for skew, excessive shuffles and spills. Mitigate data skew (salting, broadcast joins for small tables), right-size partitions, cache reused datasets, prune columns/partitions early, and prefer narrow transformations. Avoid collecting large data to the driver. Measure stage-by-stage.
Likely follow-up: When would you use a broadcast join?
What the interviewer is checking: Distributed-processing tuning.
Common mistake: Blaming the cluster size instead of fixing skew and shuffles.
Q8. What is idempotency and why is it critical in data pipelines?
Short answer: Re-running the same job produces the same result — essential for safe retries and backfills.
Strong answer: An idempotent pipeline step can run multiple times without duplicating or corrupting data — achieved via deterministic keys, upserts/merge, overwrite-by-partition, and dedup. It is critical because failures, retries and backfills are inevitable; without idempotency you get duplicates and inconsistent state.
Likely follow-up: How do you make an append-based load idempotent?
What the interviewer is checking: Reliability fundamentals.
Common mistake: Append-only loads that duplicate data on retry.