Basic Data Engineering interview questions

150 questions with answers (DEN-001 to DEN-150). Try each one aloud before reading the answer.

SQL Fundamentals

DEN-001 · What is SQL?

Structured Query Language is the declarative standard language for defining, querying and manipulating data in relational databases. You state what result you want, and the database's optimizer decides how to compute it.

DEN-002 · What is a primary key vs a foreign key?

A primary key uniquely identifies each row in a table and cannot be NULL. A foreign key is a column in one table that references the primary key of another table, enforcing referential integrity between them.

DEN-003 · What are DDL, DML, DCL and TCL?

DDL defines structure (CREATE, ALTER, DROP). DML manipulates data (SELECT, INSERT, UPDATE, DELETE). DCL controls access (GRANT, REVOKE). TCL manages transactions (COMMIT, ROLLBACK, SAVEPOINT).

DEN-004 · What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot use aggregates. HAVING filters groups after GROUP BY and can use aggregates.

SELECT dept, AVG(salary) FROM emp
WHERE active = 1
GROUP BY dept
HAVING AVG(salary) > 50000;

DEN-005 · What types of joins exist in SQL?

INNER JOIN returns matching rows only. LEFT/RIGHT OUTER JOIN keeps all rows from one side with NULLs for non-matches. FULL OUTER JOIN keeps all rows from both sides. CROSS JOIN returns the Cartesian product. A self join joins a table to itself.

DEN-006 · What is the difference between UNION and UNION ALL?

UNION combines result sets and removes duplicates, which needs a sort or hash step. UNION ALL keeps all rows, including duplicates, and is faster. Use UNION ALL unless you actually need deduplication.

DEN-007 · What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes selected rows, is logged per row and can be filtered with WHERE. TRUNCATE removes all rows quickly with minimal logging and keeps the structure. DROP removes the table itself, including its structure and data.

DEN-008 · What does NULL mean in SQL?

NULL represents a missing or unknown value, not zero or an empty string. Comparisons with NULL yield UNKNOWN, so you must test it with IS NULL or IS NOT NULL.

DEN-009 · What are aggregate functions?

Functions that compute one value from a set of rows: COUNT, SUM, AVG, MIN, MAX. They are typically used with GROUP BY, and all except COUNT(*) ignore NULLs.

DEN-010 · What is the logical order of execution of a SELECT query?

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. This is why a column alias defined in SELECT usually cannot be used in WHERE.

DEN-011 · What does DISTINCT do?

It removes duplicate rows from the result based on all selected columns. COUNT(DISTINCT col) counts unique non-NULL values of a column.

DEN-012 · What is a CASE expression?

SQL's conditional expression, which returns a value based on the first matching condition.

SELECT order_id,
       CASE WHEN amount >= 1000 THEN 'high'
            WHEN amount >= 100  THEN 'medium'
            ELSE 'low' END AS bucket
FROM orders;

DEN-013 · What is the difference between a UNIQUE constraint and a primary key?

Both enforce uniqueness. A table has only one primary key, and it cannot contain NULLs. A table can have many UNIQUE constraints, and most databases allow NULLs in them.

Advanced SQL & Query Optimization

DEN-014 · What is an index?

A separate data structure, usually a B-tree, that maps column values to row locations so the database can find rows without scanning the whole table. It speeds up reads but adds storage and slows writes.

DEN-015 · What is the difference between a clustered and a non-clustered index?

A clustered index defines the physical order of rows in the table, so there can be only one. A non-clustered index is a separate structure with pointers to rows, and a table can have many.

DEN-016 · What is a view vs a materialized view?

A view is a stored query that runs each time you select from it and stores no data. A materialized view stores the query result physically and must be refreshed, trading staleness and storage for faster reads.

DEN-017 · What is a CTE?

A Common Table Expression is a named, temporary result set defined with WITH and used within a single statement. It improves readability and enables recursion.

WITH big_orders AS (SELECT * FROM orders WHERE amount > 1000)
SELECT customer_id, COUNT(*) FROM big_orders GROUP BY customer_id;

DEN-018 · What is the difference between a subquery and a correlated subquery?

A plain subquery runs independently of the outer query. A correlated subquery references outer-query columns, so it is logically evaluated once per outer row, though optimizers often rewrite it as a join.

DEN-019 · What is a window function?

A function that computes a value across a set of rows related to the current row, defined by OVER (PARTITION BY … ORDER BY …), without collapsing rows the way GROUP BY does.

DEN-020 · What is a query execution plan?

The optimizer's chosen sequence of operations (scans, joins, sorts, aggregations) for running a query. You view it with EXPLAIN or EXPLAIN ANALYZE to find full scans, bad join orders or misestimated row counts.

DEN-021 · What is a stored procedure?

A named, precompiled block of SQL and procedural logic stored in the database and called with parameters. It encapsulates business logic, reduces network round trips and can be granted permissions separately.

DEN-022 · What does ACID stand for?

Atomicity (all or nothing), Consistency (constraints remain valid), Isolation (concurrent transactions do not interfere) and Durability (committed data survives crashes). These are the guarantees of relational transactions.

DEN-023 · What are the standard SQL isolation levels?

Read Uncommitted, Read Committed, Repeatable Read and Serializable, from weakest to strongest. Higher levels prevent more anomalies (dirty reads, non-repeatable reads, phantoms) at the cost of concurrency.

DEN-024 · What is a self join? Give an example.

A join of a table to itself using aliases, often for hierarchies.

SELECT e.name AS employee, m.name AS manager
FROM emp e LEFT JOIN emp m ON e.manager_id = m.id;

DEN-025 · What is a CROSS JOIN?

It returns the Cartesian product of two tables: every row of one paired with every row of the other, giving m × n rows. It is useful for generating combinations, such as dates × products.

DEN-026 · What does COALESCE do?

It returns the first non-NULL argument in its list. COALESCE(discount, 0) replaces a NULL discount with 0.

Data Modeling

DEN-027 · What are conceptual, logical and physical data models?

A conceptual model shows business entities and relationships. A logical model adds attributes, keys and normalization, independent of any database. A physical model maps it to a specific database, with data types, indexes and partitions.

DEN-028 · What is normalization?

Organizing tables to reduce redundancy and update anomalies by splitting data into related tables based on functional dependencies. It is applied through normal forms such as 1NF, 2NF and 3NF.

DEN-029 · What are 1NF, 2NF and 3NF?

1NF: atomic values and no repeating groups. 2NF: 1NF plus no partial dependency on part of a composite key. 3NF: 2NF plus no transitive dependency, so non-key columns depend only on the key.

DEN-030 · What is denormalization?

Deliberately adding redundancy, such as merging tables or duplicating columns, to reduce joins and speed up reads. It is common in analytics and warehouses, at the cost of extra storage and harder updates.

DEN-031 · What is an ER diagram?

An Entity-Relationship diagram that visually models entities, their attributes and the relationships between them, including cardinality. It is used in conceptual and logical design.

DEN-032 · What are the types of relationship cardinality?

One-to-one (user ↔ profile), one-to-many (customer → orders) and many-to-many (students ↔ courses). A many-to-many relationship needs a junction table in a relational schema.

DEN-033 · What is a junction (bridge) table?

A table that resolves a many-to-many relationship by holding foreign keys to both related tables, for example enrollment(student_id, course_id). It often carries attributes of the relationship itself, such as a grade.

DEN-034 · What is a surrogate key vs a natural key?

A natural key comes from business data, such as an email or SSN. A surrogate key is a system-generated identifier, such as an integer sequence or hash, with no business meaning. Surrogates stay stable when business values change.

DEN-035 · What is a composite key?

A primary key made of two or more columns that together uniquely identify a row, for example (order_id, line_number) in an order-lines table.

DEN-036 · What is a fact table?

The central table in a dimensional model that stores measurable events (sales amount, quantity) at a defined grain, along with foreign keys to dimension tables. It is usually long and narrow.

DEN-037 · What is a dimension table?

A table of descriptive attributes (customer, product, date) used to filter, group and label facts. It is usually wide and much smaller than the fact table.

DEN-038 · What is the grain of a fact table?

The precise definition of what one row represents, for example "one order line per product per order". Declaring the grain first is essential because it determines which dimensions and measures are valid.

DEN-039 · What is Data Vault modeling?

A modeling method built from hubs (business keys), links (relationships) and satellites (descriptive history with load timestamps). It is insert-only and auditable, and suits integrating many changing sources.

Relational & NoSQL Databases

DEN-040 · What is an RDBMS?

A Relational Database Management System stores data in tables with rows and columns, enforces schemas and constraints, supports SQL and provides ACID transactions. Examples are PostgreSQL, MySQL, Oracle and SQL Server.

DEN-041 · What is NoSQL, and what are its main types?

Non-relational databases designed for flexible schemas and horizontal scale. The main types are key-value (Redis, DynamoDB), document (MongoDB), wide-column (Cassandra, HBase) and graph (Neo4j).

DEN-042 · What is a key-value store?

A database that stores values addressed by a unique key, with very fast get/put operations. Redis and DynamoDB are examples, used for caches, sessions and lookups.

DEN-043 · What is a document database?

A database that stores semi-structured documents, usually JSON or BSON, with nested fields and flexible schemas. MongoDB and Couchbase are examples, and they suit entities that are read and written as a whole.

DEN-044 · What is a wide-column store?

A database that organizes data by row key and column families, where rows can have different columns. Cassandra and HBase are examples, built for high write throughput across many nodes.

DEN-045 · What is a graph database?

A database that stores nodes and edges with properties and traverses relationships efficiently. Neo4j is an example, used for social networks, fraud rings and recommendations.

DEN-046 · What is the CAP theorem?

In a distributed system facing a network partition, you must choose between consistency (every read sees the latest write) and availability (every request gets a response). Without a partition, both can be provided.

DEN-047 · What is BASE?

Basically Available, Soft state, Eventually consistent. It is the consistency model of many NoSQL systems, which favor availability and accept that replicas converge over time instead of being immediately consistent.

DEN-048 · What is sharding?

Horizontally partitioning data across multiple database servers by a shard key, so each node holds a subset of rows. It scales writes and storage but complicates cross-shard queries and transactions.

DEN-049 · What is replication?

Copying data to multiple nodes for availability, durability and read scaling. It can be synchronous (no data loss, higher latency) or asynchronous (faster, but replicas may lag).

DEN-050 · What is the difference between row-oriented and column-oriented storage?

Row stores keep all columns of a row together, which suits OLTP point reads and writes. Column stores keep each column together, which suits analytics because queries read only the needed columns and compress well.

DEN-051 · What is a time-series database?

A database optimized for timestamped measurements, with fast appends, time-based partitioning, downsampling and retention policies. InfluxDB and TimescaleDB are examples, used for metrics and IoT.

DEN-052 · When would you choose NoSQL over a relational database?

When you need massive horizontal scale, very high write throughput, flexible or evolving schemas, or simple key-based access patterns. Relational databases remain better for complex joins and multi-row transactions.

Data Warehousing

DEN-053 · What is a data warehouse?

A central repository of integrated, cleaned, historical data from many sources, organized for analytical queries and reporting. It is subject-oriented, time-variant and non-volatile.

DEN-054 · What is OLTP vs OLAP?

OLTP systems handle many small, concurrent transactions on normalized data, such as orders and payments. OLAP systems handle complex analytical queries over large volumes of denormalized, aggregated or historical data.

DEN-055 · What is a data mart?

A subject-specific subset of a data warehouse, such as sales or finance, built for one business area or team. It is smaller and simpler to query than the full warehouse.

DEN-056 · What is the difference between the Kimball and Inmon approaches?

Kimball is bottom-up: build dimensional data marts with conformed dimensions that together form the warehouse. Inmon is top-down: build a normalized enterprise warehouse first, then derive dimensional marts from it.

DEN-057 · What is an operational data store (ODS)?

An integrated, near-current store of operational data from several sources, used for operational reporting. Unlike a warehouse, it holds little history and is updated frequently.

DEN-058 · What is a staging area?

An intermediate storage layer where raw extracted data lands before transformation and loading. It decouples extraction from transformation, enables reruns and audits, and avoids load on source systems.

DEN-059 · What is a conformed dimension?

A dimension with identical keys, attributes and meaning shared across multiple fact tables or marts, such as a common Date or Customer dimension. It enables consistent drill-across reporting.

DEN-060 · What is a degenerate dimension?

A dimension key stored directly in the fact table with no dimension table of its own, typically a transaction identifier such as an invoice or order number.

DEN-061 · What is MPP?

Massively Parallel Processing: a shared-nothing architecture where data is distributed across many nodes, and each node processes its slice in parallel. Redshift, Synapse dedicated pools and Teradata are MPP systems.

DEN-062 · What are additive, semi-additive and non-additive measures?

Additive measures, such as sales amount, can be summed across all dimensions. Semi-additive measures, such as account balance, can be summed across some dimensions but not time. Non-additive measures, such as ratios and percentages, cannot be summed meaningfully.

DEN-063 · What are the common OLAP operations?

Roll-up (aggregate to a higher level), drill-down (go to more detail), slice (fix one dimension value), dice (filter several dimensions) and pivot (rotate the axes of the view).

DEN-064 · Why does every warehouse have a date dimension?

It precomputes calendar attributes (day of week, month, quarter, fiscal period, holiday flags) once, so queries can filter and group by them without complex date functions. It also gives consistent definitions across reports.

DEN-065 · Name some popular cloud data warehouses.

Snowflake, Google BigQuery, Amazon Redshift, Azure Synapse Analytics and Databricks SQL. All separate or elastically scale compute and use columnar storage.

ETL/ELT & Orchestration

DEN-066 · What is ETL vs ELT?

ETL extracts data, transforms it on a separate engine and then loads it into the target, the traditional warehouse pattern. ELT loads raw data first and transforms it inside the warehouse or lakehouse using its compute, the common modern cloud pattern.

DEN-067 · What is a data pipeline?

An automated series of steps that moves data from sources to destinations, including ingestion, transformation, validation and loading. It runs on a schedule or on events.

DEN-068 · What is Apache Airflow?

An open-source platform to author, schedule and monitor workflows as Python-defined DAGs. It orchestrates tasks and their dependencies, retries and schedules, while the heavy processing usually runs in other systems.

DEN-069 · What is a DAG in Airflow?

A Directed Acyclic Graph of tasks with dependencies and a schedule. "Acyclic" means there are no loops, so there is always a valid execution order.

DEN-070 · What are operators and sensors in Airflow?

An operator defines one unit of work, such as PythonOperator, BashOperator or SQLExecuteQueryOperator. A sensor is a special operator that waits for a condition, such as a file arriving or another task finishing.

DEN-071 · What is dbt?

data build tool: a framework for writing transformations as SQL SELECT statements with Jinja templating. It compiles and runs them in the warehouse and adds dependency management, testing, documentation and lineage.

DEN-072 · What is a dbt model?

A .sql file containing a single SELECT statement. dbt materializes it as a view, table or incremental table, named after the file.

DEN-073 · What is the difference between a full load and an incremental load?

A full load reloads the entire dataset each run, which is simple but expensive. An incremental load processes only new or changed records since the last run, using timestamps, IDs or CDC.

DEN-074 · What is Change Data Capture (CDC)?

A technique that captures inserts, updates and deletes from a source database, often by reading its transaction log, and streams them to downstream systems. It avoids full reloads and keeps targets nearly in sync.

DEN-075 · What is backfilling?

Running a pipeline for past time periods to load historical data or to reprocess after a bug fix or logic change. Pipelines must be idempotent and parameterized by date for backfills to be safe.

DEN-076 · What is XCom in Airflow?

Cross-communication: a mechanism for tasks to pass small pieces of metadata, such as a file path or row count, through Airflow's metadata database. It is not meant for large datasets.

DEN-077 · What does ref() do in dbt?

{{ ref('model_name') }} references another model. dbt resolves it to the correct schema and table for the target environment and uses it to build the dependency DAG and run order.

DEN-078 · What is reverse ETL?

Syncing modeled data from the warehouse back into operational tools, such as a CRM, marketing platform or support desk, so business teams can act on analytics.

Apache Spark & Big Data

DEN-079 · What is Apache Spark?

A distributed, general-purpose processing engine for large-scale data. It keeps intermediate data in memory where possible and offers APIs in Python, Scala, Java, SQL and R for batch, SQL, streaming and machine learning.

DEN-080 · What are the main components of Hadoop?

HDFS (distributed file storage with replicated blocks), YARN (cluster resource management and scheduling) and MapReduce (a disk-based batch processing model). Hive, HBase and Spark are common ecosystem tools that run on it.

DEN-081 · What is an RDD?

A Resilient Distributed Dataset: Spark's low-level, immutable, partitioned collection of objects. It is fault-tolerant through lineage, which means lost partitions are recomputed from the transformations that created them.

DEN-082 · What is the difference between a DataFrame and an RDD?

A DataFrame is a distributed table with named columns and a schema, optimized by the Catalyst optimizer and Tungsten engine. An RDD is untyped, low-level and not optimized. Prefer DataFrames for most work.

DEN-083 · What is the difference between transformations and actions in Spark?

Transformations (select, filter, join, groupBy) define a new dataset lazily. Actions (count, collect, show, write) trigger actual computation and return results or write output.

DEN-084 · What is lazy evaluation in Spark?

Spark records transformations in a logical plan and executes nothing until an action is called. This lets the optimizer combine steps, push down filters and avoid unnecessary work.

DEN-085 · What are the driver and executors in Spark?

The driver runs the main program, builds the execution plan and schedules tasks. Executors are processes on worker nodes that run tasks, hold cached data and report results back to the driver.

DEN-086 · What is a partition in Spark?

A chunk of a distributed dataset processed by one task on one core. The number of partitions determines parallelism.

DEN-087 · What are narrow and wide transformations?

In a narrow transformation (map, filter), each output partition depends on one input partition, so no shuffle is needed. In a wide transformation (groupBy, join, distinct), output partitions need data from many input partitions, which requires a shuffle.

DEN-088 · What is the difference between cache() and persist()?

cache() stores a dataset using the default storage level, which is MEMORY_AND_DISK for DataFrames. persist(level) lets you choose the level, such as memory only, disk only or serialized. Both are lazy and take effect on the next action.

DEN-089 · What is a SparkSession?

The unified entry point to Spark since 2.0, used to create DataFrames, run SQL, read and write data and set configuration.

from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("etl").getOrCreate()

DEN-090 · How do you read a CSV file and filter it in PySpark?

df = (spark.read.option("header", True)
      .option("inferSchema", True).csv("s3://bucket/sales.csv"))
df.filter(df.amount > 100).select("id", "amount").show()

In production, supply an explicit schema rather than inferSchema, which needs an extra pass over the data.

Streaming (Kafka, Flink, Spark Streaming)

DEN-091 · What is batch processing vs stream processing?

Batch processing handles bounded datasets periodically, such as nightly jobs, with high throughput and high latency. Stream processing handles unbounded data continuously as events arrive, with low latency.

DEN-092 · What is Apache Kafka?

A distributed, durable, partitioned commit log used as an event streaming platform. Producers append records to topics, and consumers read them independently at their own pace, with high throughput and replay.

DEN-093 · What are a topic, a partition and an offset in Kafka?

A topic is a named stream of records. Each topic is split into partitions, which are ordered, append-only logs that provide parallelism. An offset is the sequential position of a record within a partition.

DEN-094 · What is a consumer group in Kafka?

A set of consumers sharing a group ID that divides a topic's partitions among themselves, so each partition is read by exactly one consumer in the group. Different groups each receive all the data independently.

DEN-095 · What is a Kafka broker?

A Kafka server that stores partitions and serves producer and consumer requests. A cluster has several brokers, and each partition has one leader broker plus follower replicas on other brokers.

DEN-096 · What is Apache Flink?

A distributed stream processing engine with true record-at-a-time processing, event-time semantics, managed state and exactly-once state consistency through checkpoints. It treats batch as a special case of streaming.

DEN-097 · What is Spark Structured Streaming?

Spark's streaming API, which treats a stream as an unbounded table that is queried incrementally with the DataFrame API. By default it runs in micro-batches, with checkpointing for fault tolerance.

DEN-098 · What is event time vs processing time?

Event time is when the event actually occurred, embedded in the record. Processing time is when the system processes it. Event time gives correct results despite delays and out-of-order arrival.

DEN-099 · What are tumbling, sliding and session windows?

Tumbling windows are fixed-size and non-overlapping (every 5 minutes). Sliding windows are fixed-size and overlapping (10-minute windows every 1 minute). Session windows group events separated by gaps shorter than an inactivity timeout.

DEN-100 · What are at-most-once, at-least-once and exactly-once delivery?

At-most-once may lose messages but never duplicates them. At-least-once never loses messages but may duplicate them. Exactly-once ensures each message affects the result once, through idempotence or transactions.

DEN-101 · How does Kafka retain data?

Records are kept for a configured time (retention.ms) or size (retention.bytes), regardless of whether they have been consumed. Alternatively, log compaction keeps only the latest value per key.

DEN-102 · What is the difference between a message queue and publish/subscribe?

In a queue, each message is consumed by one consumer and then removed. In pub/sub, each message is delivered to every subscriber. Kafka supports both through consumer groups.

Data Lakes & Lakehouse (Delta, Iceberg, Hudi)

DEN-103 · What is a data warehouse vs a data lake?

A warehouse stores structured, curated data with schema-on-write for SQL analytics. A lake stores raw data of any type (structured, semi-structured, unstructured) cheaply in object storage, with schema-on-read.

DEN-104 · What is schema-on-write vs schema-on-read?

Schema-on-write validates and structures data when it is loaded, as in warehouses. Schema-on-read stores data as is and applies a schema when it is queried, as in lakes. The first catches errors early, and the second offers flexibility.

DEN-105 · What is Apache Parquet?

An open, columnar file format with per-column compression and encoding, plus row-group statistics (min/max) for predicate pushdown. It is the default format for analytics on data lakes.

DEN-106 · What is the difference between Avro and Parquet?

Avro is row-based with an embedded schema and strong schema evolution, so it suits streaming, messaging and write-heavy ingestion. Parquet is columnar and suits analytical reads of a few columns over many rows.

DEN-107 · What is ORC?

Optimized Row Columnar, a columnar file format from the Hive ecosystem with stripes, lightweight indexes and compression. It is similar in purpose to Parquet and popular with Hive.

DEN-108 · What is the medallion architecture?

A layered lakehouse design: Bronze holds raw ingested data, Silver holds cleaned, conformed and deduplicated data, and Gold holds business-level aggregates and marts ready for consumption.

DEN-109 · What is Delta Lake?

An open table format that adds a JSON/Parquet transaction log (_delta_log) on top of Parquet files. It provides ACID transactions, MERGE/UPDATE/DELETE, schema enforcement and time travel.

DEN-110 · What is Apache Iceberg?

An open table format that tracks data files through a tree of metadata files, manifest lists and manifests. It provides ACID commits, hidden partitioning, partition and schema evolution and time travel across engines such as Spark, Trino and Flink.

DEN-111 · What is Apache Hudi?

Hadoop Upserts Deletes and Incrementals: a table format focused on fast upserts, deletes and incremental pulls on data lakes. It offers Copy-on-Write and Merge-on-Read table types.

DEN-112 · What is time travel in a table format?

The ability to query a table as it was at an earlier version or timestamp, using retained snapshots.

SELECT * FROM sales VERSION AS OF 42;           -- Delta
SELECT * FROM sales TIMESTAMP AS OF '2024-01-01';

DEN-113 · What is object storage?

Flat, highly durable and cheap storage of objects addressed by key, accessed over HTTP APIs. Examples are Amazon S3, Azure Blob/ADLS Gen2 and Google Cloud Storage. It is the storage foundation of data lakes.

DEN-114 · What is a data swamp?

A data lake that has degraded into an unusable dump because of missing metadata, governance, quality checks and ownership, so users cannot find or trust its data.

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

DEN-115 · What is Amazon S3?

AWS's object storage service, designed for 99.999999999% (11 nines) durability. It stores objects in buckets and is the usual storage layer for data lakes on AWS.

DEN-116 · What is AWS Glue?

A serverless data integration service with a Data Catalog (a Hive-compatible metastore), crawlers for schema discovery and managed Spark/Python ETL jobs.

DEN-117 · What is Amazon Redshift?

AWS's columnar, MPP cloud data warehouse. It is available as provisioned clusters or Redshift Serverless and can query S3 data through Redshift Spectrum.

DEN-118 · What is Amazon Athena?

A serverless, interactive query service that runs SQL (Trino/Presto-based) directly on data in S3 using the Glue Data Catalog. It is billed by data scanned, so columnar formats and partitioning reduce cost.

DEN-119 · What is Azure Data Factory?

Azure's managed data integration and orchestration service. It builds pipelines of copy and transformation activities across many connectors, with scheduling, triggers and monitoring.

DEN-120 · What is ADLS Gen2?

Azure Data Lake Storage Gen2: Azure Blob Storage with a hierarchical namespace, which gives real directories, atomic renames and POSIX-like ACLs. It is the lake storage layer for Azure analytics.

DEN-121 · What is Google BigQuery?

GCP's serverless, columnar data warehouse that separates storage and compute. Compute is billed on demand by bytes scanned or through reserved slot capacity, and it supports partitioning, clustering and standard SQL.

DEN-122 · What are Google Cloud Pub/Sub and Dataflow?

Pub/Sub is a managed, global messaging service for event ingestion. Dataflow is a managed runner for Apache Beam pipelines that handles both batch and streaming with autoscaling.

DEN-123 · What are the layers of Snowflake's architecture?

A storage layer (compressed columnar micro-partitions in cloud object storage), a compute layer (independent virtual warehouses) and a cloud services layer (metadata, optimization, security and transactions).

DEN-124 · What is a Snowflake virtual warehouse?

An independent compute cluster that runs queries and DML. It comes in T-shirt sizes, can auto-suspend and auto-resume, and is billed in credits per second while running.

DEN-125 · What is Databricks?

A cloud data and AI platform built on Apache Spark and Delta Lake. It provides managed clusters, notebooks, SQL warehouses, jobs and Unity Catalog governance in a lakehouse architecture.

DEN-126 · What is Amazon EMR?

Elastic MapReduce: a managed AWS service for running big-data frameworks such as Spark, Hive, Presto/Trino and HBase on EC2, EKS or serverless, with transient or long-running clusters.

Data Quality, Governance & Security

DEN-127 · What are the main dimensions of data quality?

Accuracy, completeness, consistency, timeliness (freshness), validity (conforms to rules and formats) and uniqueness (no duplicates).

DEN-128 · What is data governance?

The policies, roles, processes and standards that ensure data is accurate, secure, compliant and used appropriately. It covers ownership, access, quality, lineage and lifecycle.

DEN-129 · What is data lineage?

A record of where data came from, how it was transformed and where it flows downstream. It supports impact analysis, debugging, auditing and compliance.

DEN-130 · What is a data catalog?

A searchable inventory of data assets with technical and business metadata, owners, lineage and usage. Examples are AWS Glue Data Catalog, Unity Catalog, DataHub, Collibra and Alation.

DEN-131 · What are technical, business and operational metadata?

Technical metadata describes schemas, types and locations. Business metadata gives definitions, owners and glossary terms. Operational metadata records run times, row counts, freshness and job status.

DEN-132 · What is PII?

Personally Identifiable Information: data that can identify a person directly (name, email, national ID) or in combination (date of birth plus postcode). It requires protection under privacy laws.

DEN-133 · What is data masking?

Hiding sensitive values by replacing them with realistic but fictitious or partially obscured values, such as ****-1234. Static masking alters copies, for example for test environments. Dynamic masking hides values at query time based on the user's role.

DEN-134 · What is encryption at rest vs in transit?

Encryption at rest protects stored data on disks and object storage, for example with AES-256 and managed keys. Encryption in transit protects data moving over networks, typically with TLS.

DEN-135 · What is RBAC?

Role-Based Access Control: permissions are granted to roles, and users receive roles. This simplifies managing access to databases, schemas and tables compared with granting privileges to individual users.

DEN-136 · What GDPR rights matter most for data engineers?

The right of access, the right to erasure ("right to be forgotten"), the right to rectification and data portability, together with the principles of purpose limitation and data minimization. Pipelines must be able to find and delete or anonymize a person's data.

DEN-137 · What is Master Data Management (MDM)?

Processes and tools that create a single, trusted "golden record" for core entities such as customers and products, by matching, deduplicating and merging records from many systems.

DEN-138 · What is the difference between a data owner and a data steward?

A data owner is the accountable business person who decides on access and usage of a dataset. A data steward manages day-to-day quality, definitions and metadata on the owner's behalf.

Python, Pipeline Design & Cost Optimization

DEN-139 · Why is Python popular in data engineering?

It has readable syntax and a rich ecosystem (pandas, PySpark, Airflow, requests, SQLAlchemy, boto3), along with strong cloud SDKs and easy glue between systems. Heavy computation is delegated to optimized engines.

DEN-140 · What is the difference between a list and a tuple in Python?

A list is mutable and uses []. A tuple is immutable and uses (), so it is hashable when its elements are hashable and can serve as a dictionary key, for example a composite key.

DEN-141 · What is a pandas DataFrame?

An in-memory, two-dimensional labeled table with typed columns, used for single-machine data manipulation. It is limited by the RAM of one machine.

DEN-142 · What is a Python generator, and why use one in pipelines?

A function that uses yield to produce items lazily, one at a time, so large files or API results can be processed in constant memory.

def read_lines(path):
    with open(path) as f:
        for line in f:
            yield line.rstrip("\n")

DEN-143 · How do you parse a JSON file in Python?

import json
with open("events.json") as f:
    data = json.load(f)        # json.loads() for a string

For newline-delimited JSON, parse each line separately with json.loads(line).

DEN-144 · Why use virtual environments?

They isolate each project's Python interpreter and package versions (venv, conda, poetry, uv), which avoids dependency conflicts and makes builds reproducible.

DEN-145 · How should a pipeline script handle exceptions?

Catch specific exceptions, log them with context, retry transient failures, and re-raise or exit non-zero so the orchestrator marks the task as failed. Never silently swallow errors.

DEN-146 · What is a data SLA?

A Service Level Agreement for data, such as "the sales table is refreshed by 06:00 daily with 99.5% on-time delivery". It sets measurable freshness, availability and quality expectations for consumers.

DEN-147 · What is push vs pull ingestion?

In push ingestion, the source sends data to the platform, through webhooks, producers or file drops. In pull ingestion, the pipeline fetches data from the source, through API polling or database queries, on its own schedule.

DEN-148 · Why use the logging module instead of print?

logging supports levels, timestamps, structured formats and multiple handlers (files, stdout, log services), and you can configure it without changing code. print gives none of this.

DEN-149 · What are the main cost drivers of a cloud data platform?

Compute (clusters, warehouses, query scans), storage (volume, retained versions and snapshots), data transfer (egress, cross-region) and managed-service fees. Compute is usually the largest.

DEN-150 · What is serverless vs provisioned compute?

Serverless compute scales automatically and bills per use (queries, bytes scanned, seconds), with no cluster management. Provisioned compute gives you fixed capacity that you pay for while it runs, which can be cheaper for steady, heavy workloads.

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