Tricky Data Engineering interview questions

75 questions with answers (DEN-426 to DEN-500). Try each one aloud before reading the answer.

SQL Fundamentals

DEN-426 · Does WHERE column = NULL find rows with missing values?

Trap: Any comparison with NULL yields UNKNOWN, so the condition never matches. Use IS NULL or IS NOT NULL, or null-safe operators like IS DISTINCT FROM.

DEN-427 · Does COUNT(column) count all rows in a table?

Trap: COUNT(column) ignores NULLs in that column; COUNT(*) counts every row. Mixing them up silently undercounts records with missing values.

DEN-428 · Does a LEFT JOIN always return every row from the left table exactly once?

Trap: It returns every left row at least once, but duplicate matches on the right side repeat left rows. And a filter on the right table in the WHERE clause turns it into an inner join by removing unmatched rows.

DEN-429 · Does ORDER BY in a subquery or view guarantee the final result order?

Trap: Only the outermost ORDER BY guarantees order; optimisers may ignore inner ordering. Always sort in the final query.

DEN-430 · Does AVG(column) include NULL values as zero?

Trap: Aggregates ignore NULLs, so AVG divides by the count of non-NULL values. If missing should mean zero, use AVG(COALESCE(column, 0)).

DEN-431 · Is UNION a faster way to combine results than UNION ALL?

Trap: UNION removes duplicates, which needs sorting or hashing, so it is slower. Use UNION ALL unless you actually need deduplication.

DEN-432 · Does SELECT DISTINCT fix duplicate rows caused by a bad join?

Trap: It hides the symptom, often at high cost, and may still return wrong aggregates. Fix the join keys or aggregate to the right grain before joining.

Advanced SQL & Query Optimization

DEN-433 · Does adding more indexes always make a database faster?

Trap: Indexes speed reads but slow inserts, updates and deletes, consume storage and memory, and too many can confuse the optimiser. Index for actual query patterns.

DEN-434 · Is a CTE always materialised once and reused?

Trap: Behaviour depends on the engine and version; many optimisers inline CTEs, so a CTE referenced twice may be computed twice. Use temporary tables or explicit materialisation when reuse matters.

DEN-435 · Does a query plan showing an index seek guarantee good performance?

Trap: An index seek followed by millions of key lookups can be slower than a scan. Check estimated versus actual rows and the whole plan, not just one operator.

DEN-436 · Do window functions reduce the number of rows like GROUP BY?

Trap: Window functions compute values across related rows but return one row per input row. Use GROUP BY or filter on the window result (e.g. ROW_NUMBER() = 1) to reduce rows.

DEN-437 · Does wrapping an indexed column in a function in WHERE still use the index?

Trap: Usually not: WHERE YEAR(order_date) = 2025 prevents a normal index seek. Rewrite as a range (order_date >= '2025-01-01' AND order_date < '2026-01-01') or use a function-based index.

DEN-438 · Is OFFSET-based pagination fine for large tables?

Trap: Large offsets force the database to scan and discard all skipped rows, getting slower page by page, and results shift if rows change. Keyset pagination is more efficient.

DEN-439 · Is SELECT * harmless in a columnar warehouse if you only look at a few rows?

Trap: Columnar engines charge or spend time by columns scanned, and LIMIT often doesn't reduce the data read. SELECT * can scan the whole table.

Data Modeling

DEN-440 · Is a fully normalised model always best for analytics?

Trap: Normalisation reduces redundancy for transactional systems, but analytical queries then need many joins. Dimensional or denormalised models are usually faster and easier for analytics.

DEN-441 · Should natural business keys be used as primary keys in a warehouse dimension?

Trap: Business keys can change, be reused, collide across source systems and can't represent history in SCD Type 2. Use surrogate keys in dimensions and keep the business key as an attribute.

DEN-442 · Does SCD Type 2 keep history for every column automatically?

Trap: Only the columns you track as Type 2 create new rows; other columns may be overwritten (Type 1). Deciding which attributes need history is a business decision.

DEN-443 · Is a star schema always faster than one big table?

Trap: Modern columnar warehouses often query wide denormalised tables very fast, avoiding joins. Star schemas still win for conformed dimensions, reuse and maintainability; the best choice depends on the engine and use case.

DEN-444 · Can you sum any numeric fact across all dimensions?

Trap: Semi-additive facts like account balances or inventory levels can't be summed across time, and non-additive ones like ratios can't be summed at all. Use the right aggregation for each measure.

DEN-445 · Does a data model designed once stay correct forever?

Trap: Business processes, sources and questions change, so models need versioning, governance and periodic review.

DEN-446 · Is Data Vault a replacement for dimensional models used by BI tools?

Trap: Data Vault is an integration layer optimised for auditability and change; users still query dimensional marts or views built on top of it.

Relational & NoSQL Databases

DEN-447 · Does NoSQL mean "no SQL" and no schema?

Trap: It means "not only SQL". Many NoSQL systems support SQL-like queries, and data always has an implicit schema that applications rely on; schema-on-read just moves enforcement into code.

DEN-448 · Does the CAP theorem mean you must choose two of consistency, availability and partition tolerance all the time?

Trap: CAP applies only during a network partition: then a system must choose consistency or availability. Without partitions, systems can offer both; PACELC adds the latency vs consistency trade-off in normal operation.

DEN-449 · Is MongoDB unable to do transactions?

Trap: MongoDB supports multi-document ACID transactions (since 4.0), though single-document operations remain the efficient default and transactions have performance costs.

DEN-450 · Does adding read replicas fix a write bottleneck?

Trap: Replicas scale reads only; all writes still go to the primary. Write scaling needs sharding, partitioning or a distributed database, plus query and schema optimisation.

DEN-451 · Does eventual consistency mean data may never become consistent?

Trap: It means replicas converge to the same value if no new updates occur, typically within milliseconds to seconds, but reads in between may be stale. Applications must tolerate or work around that window.

DEN-452 · Is a relational database always too slow for big data?

Trap: Modern relational and distributed SQL databases handle very large volumes with partitioning, columnar storage and MPP. The choice depends on workload, not just data size.

Data Warehousing

DEN-453 · Does a data warehouse replace the need for a data lake?

Trap: Warehouses excel at structured analytics, but raw files, semi-structured data, ML training data and cheap long-term storage often belong in a lake. Many organisations use both or a lakehouse.

DEN-454 · Is a bigger warehouse size always faster for every query?

Trap: Larger warehouses help large, parallelisable queries, but small queries gain little and cost more. Concurrency issues need multi-cluster scaling, not just bigger size.

DEN-455 · Does loading data into a warehouse make it trustworthy?

Trap: Bad source data, broken transformations and inconsistent definitions still produce wrong numbers. Trust needs testing, governance and documentation.

DEN-456 · Are materialised views always up to date?

Trap: Depending on the engine, they refresh on a schedule, incrementally in the background or on demand, so queries may see stale data. Check refresh behaviour and staleness guarantees before relying on them.

DEN-457 · Does a date stored as a timestamp mean the same day everywhere?

Trap: Without explicit time zones, events near midnight fall on different dates in different regions, and daylight saving shifts break hourly reports. Store UTC timestamps and convert deliberately for reporting.

DEN-458 · Is a single "source of truth" table enough to make metrics consistent?

Trap: Teams can still apply different filters and definitions on top of it. Consistent metrics need shared definitions in a semantic layer or certified models.

ETL/ELT & Orchestration

DEN-459 · Does a green (successful) Airflow run mean the data is correct?

Trap: It only means tasks didn't raise errors. A job can succeed while loading zero rows, duplicates or wrong values; data quality checks and freshness monitoring are needed to know the data is right.

DEN-460 · Is ELT always better than ETL?

Trap: ELT uses the warehouse's compute and keeps raw data, which suits cloud analytics, but ETL is still better when data must be masked before landing, heavily transformed outside SQL, or when warehouse compute is expensive.

DEN-461 · Is it safe to rerun a failed pipeline from the start?

Trap: Only if every step is idempotent. Pipelines that append blindly or send side effects (emails, API calls) can create duplicates or repeated actions on rerun.

DEN-462 · Does Airflow's start_date mean the first run happens on that date?

Trap: In classic scheduling, a run for an interval starts after the interval ends, so the first run happens one schedule interval after start_date, and catchup may create many past runs.

DEN-463 · Should all pipeline logic live inside Airflow tasks?

Trap: Airflow is an orchestrator; heavy processing inside workers limits scale and makes testing harder. Push processing to Spark, dbt or the warehouse and keep tasks lightweight.

DEN-464 · Does CDC capture every change perfectly with no extra work?

Trap: CDC needs correct log retention, handles schema changes and deletes carefully, and ordering per key matters; initial snapshots, replays and large transactions also need planning.

Apache Spark & Big Data

DEN-465 · Does calling cache() store the DataFrame in memory immediately?

Trap: Caching is lazy; the data is cached only when an action first computes it. Caching also uses memory that could otherwise serve shuffles, so cache only data that is reused.

DEN-466 · Is Spark always faster than a single machine for data processing?

Trap: For data that fits on one machine (up to tens of GBs), tools like DuckDB, Polars or pandas are often faster and cheaper because Spark has scheduling, serialisation and shuffle overhead.

DEN-467 · Does more partitions always mean more parallelism and speed?

Trap: Too many tiny partitions create scheduling overhead and small output files; too few under-use the cluster. Aim for balanced, reasonably sized partitions.

DEN-468 · Is collect() a safe way to inspect a Spark DataFrame?

Trap: collect() brings all data to the driver and can crash it with large datasets. Use show(), take() or write samples instead.

DEN-469 · Does Spark automatically fix data skew?

Trap: AQE can split skewed join partitions, but skew in aggregations or custom logic may still need salting or redesign.

DEN-470 · Does adding executors always speed up a job?

Trap: If the job is limited by skew, I/O, a single-threaded driver step or small input, extra executors won't help and only add cost.

Streaming (Kafka, Flink, Spark Streaming)

DEN-471 · Does Kafka guarantee that messages are processed exactly once by default?

Trap: The default is at-least-once: retries can duplicate messages and consumer crashes can reprocess them. Exactly-once needs idempotent producers, transactions and transactional or idempotent sinks.

DEN-472 · Is Kafka a database you can query like a table?

Trap: Kafka is a distributed log optimised for sequential reads by offset; it has no indexes for ad-hoc queries. Data is usually materialised into databases or lakes, or queried via stream processors like ksqlDB.

DEN-473 · Does adding consumers to a group always increase throughput?

Trap: Each partition is read by only one consumer in a group, so consumers beyond the partition count sit idle. Increase partitions first.

DEN-474 · Is processing time a good basis for windowed aggregations?

Trap: Processing time reflects when events arrive, not when they happened, so delays, retries and backfills place events in the wrong windows. Use event time with watermarks for correct results.

DEN-475 · Does a streaming pipeline remove the need for batch processing?

Trap: Batch is still useful for backfills, reprocessing, complex historical joins and reconciliation; many architectures combine both.

DEN-476 · Is lower latency always worth it in data pipelines?

Trap: Real-time pipelines cost more and are more complex to build and operate. Many use cases are well served by hourly or daily batch.

Data Lakes & Lakehouse (Delta, Iceberg, Hudi)

DEN-477 · Does deleting a record from a Delta or Iceberg table remove it from storage immediately?

Trap: The delete creates a new table version, but old data files remain for time travel until snapshots are expired and files are vacuumed. For GDPR erasure you must also clean up history.

DEN-478 · Is a data lake just a place to dump all raw files?

Trap: Without organisation, metadata, quality checks and access control, a lake becomes a data swamp. Zones, catalogs and governance are essential.

DEN-479 · Does partitioning a table by a high-cardinality column like user_id speed up queries?

Trap: It creates millions of tiny partitions and files, slowing metadata operations and queries. Partition by low-to-moderate cardinality columns and cluster by high-cardinality ones.

DEN-480 · Is Parquet always smaller than CSV?

Trap: Usually yes for large tables, but very small files or poorly typed data may not compress well, and Parquet has metadata overhead. Its main advantage is efficient analytic reads.

DEN-481 · Does using an open table format guarantee any engine can read your data?

Trap: Engine support varies by format version and features (e.g. deletion vectors, new Iceberg specs). Check compatibility before relying on multi-engine access.

DEN-482 · Can time travel be used as a long-term backup?

Trap: Time travel is limited by retention and vacuum settings and lives in the same storage; it isn't a substitute for proper backups or cross-region copies.

Cloud Data Platforms (AWS, Azure, GCP, Snowflake, Databricks)

DEN-483 · Is serverless always cheaper than provisioned compute?

Trap: Serverless is cheap for spiky or low workloads, but for steady high utilisation, reserved or provisioned capacity is often much cheaper. Compare on actual usage patterns.

DEN-484 · Does moving to the cloud automatically reduce data platform costs?

Trap: Without governance, auto-suspend, right-sizing and cost monitoring, cloud bills often grow faster than on-premise costs. Cloud gives flexibility, not automatic savings.

DEN-485 · Does Snowflake's auto-suspend mean an idle warehouse costs nothing?

Trap: A suspended warehouse costs nothing for compute, but each resume has a minimum billing period (60 seconds) and frequent short queries can keep it running; storage and other services are still billed.

DEN-486 · Is data in a cloud bucket private just because nobody has the URL?

Trap: Misconfigured bucket policies or ACLs can expose data publicly, and URLs leak through logs and code. Block public access at account level, use least-privilege IAM and audit configurations regularly.

DEN-487 · Does a managed cloud service mean security is entirely the provider's job?

Trap: Under the shared responsibility model, the provider secures the infrastructure, but you remain responsible for access control, data classification, encryption settings, network configuration and user management.

DEN-488 · Does BigQuery's LIMIT clause reduce the amount of data you pay for?

Trap: On-demand pricing charges for the bytes in the columns scanned, and LIMIT doesn't reduce the scan for most queries. Select fewer columns and filter on partitioned or clustered columns instead.

Data Quality, Governance & Security

DEN-489 · If a dataset passes all its tests, is it correct?

Trap: Tests only check the rules someone wrote. Wrong business logic, unexpected distribution shifts or missing data that meets constraints can still slip through, so combine tests with observability and reconciliation.

DEN-490 · Does masking a column in reports make the data anonymous?

Trap: Masking hides values from some users, but the underlying data and other columns may still identify people (quasi-identifiers like postcode, birth date and gender together). Anonymisation needs a proper risk assessment.

DEN-491 · Is encrypting data at rest enough to protect it?

Trap: Encryption at rest protects against stolen disks, but anyone with valid access can still read decrypted data. Access control, monitoring, network security and encryption in transit are also needed.

DEN-492 · Does a data catalog alone create data governance?

Trap: A catalog is a tool; governance needs owners, policies, processes and culture. An unmaintained catalog quickly becomes outdated.

DEN-493 · Does hashing an email address make it anonymous?

Trap: Hashes of known values can be reversed by hashing candidate emails (dictionary attacks), and the same hash links records across datasets. It is pseudonymisation, still personal data under GDPR; use salts or keyed hashing and treat it as sensitive.

DEN-494 · Is data lineage only useful for compliance?

Trap: Lineage is just as valuable for debugging broken reports, impact analysis before changes, and cost and cleanup decisions.

Python, Pipeline Design & Cost Optimization

DEN-495 · Is pandas a good choice for every data pipeline because everyone knows it?

Trap: pandas is single-threaded and memory-bound, so it struggles with large data. Polars, DuckDB, Spark or warehouse SQL scale better for big datasets.

DEN-496 · Does a pipeline that runs faster always cost less?

Trap: Speed may come from larger or more expensive compute. Cost depends on compute size × time and storage, so measure cost per run, not just runtime.

DEN-497 · Is it fine to hard-code credentials in a private repository?

Trap: Private repositories are cloned, forked and backed up widely, and leaked credentials are a common cause of breaches. Use a secrets manager and rotate credentials.

DEN-498 · Does adding retries always make pipelines more reliable?

Trap: Retrying non-transient errors wastes time, and retries without idempotency can duplicate data or overload failing systems. Retry only transient failures with backoff.

DEN-499 · Is a pipeline finished when it produces correct output once?

Trap: Production pipelines need monitoring, alerting, documentation, tests, ownership and recovery procedures to keep working as data and systems change.

DEN-500 · Does storing every intermediate dataset forever help reproducibility?

Trap: It increases storage cost and risk (privacy, clutter). Reproducibility comes from versioned code, configurations and source snapshots, with retention policies for intermediates.

Other levels: Basic · Intermediate · Advanced · All Data Engineering questions