Data Engineering Practice Exam

Question 1 Data Engineering

What is a star join optimization?

A distribution key controls how rows are spread across compute nodes. With KEY distribution, rows with the same key value go to the same node, which co-locates joins. With ALL distribution, the entire table is copied to every node (good for small dimension tables). With EVEN distribution, rows are spread round-robin (good when no join column is dominant). ClickHouse is an open-source columnar OLAP database that delivers extremely fast analytical queries on petabyte-scale data. It uses vectorized query execution, data skipping indexes, and MergeTree storage engines. ClickHouse is popular for observability, ad analytics, and real-time reporting. A CTE is a named temporary result set defined with WITH that you can reference within a single query. CTEs improve readability of complex queries, allow recursion (e.g., org-chart traversal), and break logic into logical steps. In most modern warehouses CTEs are inlined, so they don't necessarily improve performance — but they make SQL far easier to maintain. Star join optimization is a query technique used by MPP warehouses (Snowflake, Redshift, BigQuery) to broadcast small dimension tables to all nodes while distributing the large fact table, then perform a local join. This avoids shuffling the fact table when a query filters by a dimension. The optimizer recognizes a star schema and applies this plan automatically.

Question 2 Data Engineering

What is data profiling?

Apache Druid is a real-time analytics database optimized for sub-second OLAP queries on high-cardinality, time-series, and event data. It is columnar, supports streaming ingestion (Kafka, Kinesis), and pre-aggregates data via roll-ups. Common in user-facing analytics dashboards and operational monitoring. A transactional fact table records individual events as they occur, with each row representing a single business event (sale, click, login). It is the most granular fact table and grows append-only. Transactional fact tables support both current and historical analysis at fine granularity. Spark Structured Streaming uses a watermark defined on event time to bound how long the engine waits for late data. withWatermark("event_time", "10 minutes") means state for a window is kept for 10 minutes after the watermark passes its end, after which late data is dropped. Watermarks enable state cleanup and bounded memory. Data profiling is the process of examining a dataset to collect statistics and understand its structure, content, and quality. It includes analyzing data types, null counts, unique values, distributions, min/max values, and patterns. Profiling is an essential first step in data engineering to identify quality issues before building pipelines.

Question 3 Data Engineering

What is a stateful stream processing operator?

Kafka Streams is a client library that runs stream processing inside a Java application, tightly coupled to Kafka topics. Apache Flink is a standalone distributed processing engine that supports many sources and sinks (Kafka, Kinesis, files, databases) with advanced state management. Flink is more powerful for complex jobs; Kafka Streams is simpler when everything stays in Kafka. Great Expectations is an open-source Python library for data validation and quality testing. You define expectations—declarative assertions about your data—such as expect_column_values_to_not_be_null or expect_column_values_to_be_between. It generates data quality reports and integrates with orchestrators like Airflow to catch issues before bad data enters downstream systems. A stateful operator in a stream processor (Flink, Kafka Streams) maintains information across events — e.g., running counts, last-seen values, or session windows. State is stored in RocksDB locally and checkpointed to durable storage for fault tolerance. Stateful operators enable complex logic like pattern detection and joins between streams. Denormalization is the intentional introduction of redundancy into a database schema to improve read performance. It combines related tables or adds redundant columns to reduce the number of joins needed for queries. Denormalization is commonly used in data warehouses and OLAP systems where fast query performance is more important than storage efficiency.

Question 4 Data Engineering

What is the small files problem in HDFS and data lakes?

Data mesh is a decentralized data architecture paradigm proposed by Zhamak Dehghani. It treats data as a product owned by domain teams rather than centralized data teams. Its four principles are: domain-oriented ownership, data as a product, self-serve data infrastructure, and federated computational governance. Data downtime is the period during which data is incomplete, inaccurate, missing, or stale. It is the data equivalent of website downtime, but often invisible to end users until they notice a wrong dashboard. Reducing data downtime is a key motivation for data observability and pipeline testing. The small files problem occurs when a distributed file system holds millions of tiny files. Each file consumes metadata slots (in HDFS the NameNode RAM), and processing many small files causes excessive seek overhead. Solutions include compacting into larger files, using sequence/Parquet files, and leveraging columnar table formats like Iceberg or Delta that pack many records per file. A role-playing dimension is a single dimension table that is used multiple times in the same fact table, with each usage representing a different role. The classic example is a dim_date joined three times as order_date, ship_date, and delivery_date. It is implemented with multiple foreign keys in the fact table referencing the same dimension.

Question 5 Data Engineering

What is the difference between deduplication and upsert?

The outbox pattern solves dual-write consistency between a database and a message broker. Instead of writing to the DB and publishing to Kafka in the same business transaction (which can fail partially), the service writes the message to an outbox table in the same transaction. A separate process (Debezium, a CDC reader) tails the outbox and publishes to Kafka, ensuring at-least-once delivery. Deduplication identifies and removes duplicate rows in a dataset, typically keeping one per key. Upsert writes a row to a target, inserting it if new and updating if the key exists. They often work together: CDC streams are deduped to find the latest change per key, then upserted into the warehouse. SCD Type 0 (also called "retain original") means the dimension attribute is never updated — e.g., a customer's original signup date or country of birth. It is used for attributes that should be frozen at the time of the event to preserve historical accuracy in reports. A primary key uniquely identifies each row in a table and cannot be null or duplicated. A foreign key is a column in one table that references the primary key of another table, establishing a referential link. Foreign keys enforce referential integrity, preventing orphan rows and maintaining consistent relationships across tables.

Question 6 Data Engineering

What is Apache Parquet?

Data sharding is the practice of horizontally splitting a large dataset across multiple databases or nodes, each holding a subset of rows based on a shard key. Unlike partitioning within a single system, sharding distributes data across independent servers to improve scalability and throughput. Choosing a good shard key (e.g., user ID) is critical to avoid data skew. Apache Parquet is an open-source columnar storage file format optimized for analytics workloads. It supports efficient compression and encoding schemes, nested data structures, and predicate pushdown for fast query filtering. Parquet is widely used in big data ecosystems with tools like Spark, Hive, Presto, and cloud data lakes. Apache Kafka is a distributed event streaming platform used for building real-time data pipelines. It operates on a publish-subscribe model where producers write messages to topics and consumers read from them. Kafka provides high throughput, fault tolerance through replication, and message durability, making it ideal for streaming architectures. A heap table stores rows in no particular order; primary key indexes do not control physical layout. A clustered index (SQL Server, MySQL InnoDB) physically orders table data by the primary key. Clustered tables are faster for range queries on the PK and for sequential reads; heap tables can be faster for bulk inserts and random access patterns.

Question 7 Data Engineering

What is a periodic snapshot fact table?

ORC (Optimized Row Columnar) and Parquet are both columnar formats with similar capabilities. Historically ORC has been tightly integrated with Hive and provides strong predicate pushdown via zone maps, while Parquet has wider adoption across Spark, Presto, Trino, and BigQuery. The choice usually depends on the engine ecosystem; both support compression, schema evolution, and nested types. A bloom filter is a probabilistic data structure that tests whether an element is possibly in a set, with possible false positives but no false negatives. It is used in HBase, RocksDB, and Parquet to skip whole row groups or data files that cannot contain a value, dramatically speeding up point lookups and join filtering on cold data. A periodic snapshot fact table records state at regular intervals, e.g., end-of-day account balance, monthly inventory level. Each row represents a period, not an event. Snapshots are useful for tracking slowly evolving states and answering "what was the value at time T?" without complex SCD logic. Kafka Streams is a client library that runs stream processing inside a Java application, tightly coupled to Kafka topics. Apache Flink is a standalone distributed processing engine that supports many sources and sinks (Kafka, Kinesis, files, databases) with advanced state management. Flink is more powerful for complex jobs; Kafka Streams is simpler when everything stays in Kafka.

Question 8 Data Engineering

What is exactly-once delivery in Kafka transactions?

The small files problem occurs when a distributed file system holds millions of tiny files. Each file consumes metadata slots (in HDFS the NameNode RAM), and processing many small files causes excessive seek overhead. Solutions include compacting into larger files, using sequence/Parquet files, and leveraging columnar table formats like Iceberg or Delta that pack many records per file. Log compaction retains only the latest value for each key, discarding older versions. It is used for change logs and slowly changing state. Retention (time or size based) discards old messages regardless of key. Compaction preserves current state over time, while retention preserves recent messages regardless of content. Kafka's transactional producer uses a transaction coordinator and an __transaction_state topic to atomically commit reads and writes across partitions. Producers call initTransactions, beginTransaction, send messages, then commitTransaction. This guarantees that either all messages in a transaction are visible to read-committed consumers, or none are. A Kafka consumer group is a set of consumers that cooperate to consume messages from one or more topics. Each partition is assigned to exactly one consumer within the group, enabling parallel processing. If a consumer fails, Kafka rebalances the partitions among the remaining consumers. Different consumer groups independently consume the same messages.

Question 9 Data Engineering

What is schema evolution?

Spark Structured Streaming uses a watermark defined on event time to bound how long the engine waits for late data. withWatermark("event_time", "10 minutes") means state for a window is kept for 10 minutes after the watermark passes its end, after which late data is dropped. Watermarks enable state cleanup and bounded memory. An upsert (update-or-insert) writes a row to a table, inserting it if new and updating it if the key already exists. Implementations: INSERT ... ON CONFLICT DO UPDATE (Postgres), MERGE (SQL Server, Snowflake, Delta Lake), PUT (DynamoDB), or replace-by-key in Kafka log-compacted topics. Upserts are fundamental to maintaining up-to-date state in warehouses. Strong consistency guarantees that once a write is acknowledged, all subsequent reads return the new value (e.g., traditional RDBMS). Eventual consistency allows temporary divergence between replicas, with the guarantee that all replicas will converge to the same value given enough time. Eventual consistency is common in distributed systems like DynamoDB and Cassandra for higher availability and partition tolerance. Schema evolution is the ability to change a dataset's schema over time (e.g., adding, removing, or renaming columns) without breaking existing queries or consumers. Formats like Avro, Parquet, and table formats like Iceberg and Delta Lake support schema evolution, allowing pipelines to adapt to upstream changes gracefully.

Question 10 Data Engineering

What is a DAG in the context of data orchestration?

Exactly-once semantics (EOS) means each event is processed and its effects (writes to sinks, side effects) are committed exactly one time, even in the face of failures. EOS requires idempotent sinks or transactional output, plus checkpointing of source offsets. Apache Flink supports exactly-once end-to-end, while Spark Structured Streaming provides it within micro-batch checkpoints. OLTP (Online Transaction Processing) systems handle high volumes of short, transactional operations (inserts, updates) and are optimized for write performance. OLAP (Online Analytical Processing) systems are designed for complex analytical queries over large datasets, optimized for read performance. Data warehouses are OLAP systems, while production databases are typically OLTP. A DAG (Directed Acyclic Graph) is a collection of tasks with defined dependencies and execution order, where the graph has no cycles. In tools like Airflow, each node represents a task (e.g., extract data, run a transformation), and edges define the order of execution. DAGs ensure tasks run in the correct sequence and enable parallel execution of independent tasks. A view is a stored SQL query that runs every time it is referenced. A materialized view is a query result physically persisted and refreshed on a schedule or trigger. A cache is a temporary, application-managed copy keyed by query parameters (e.g., Redis). Materialized views trade storage for repeated-query speed; caches trade memory for low-latency repeated reads.

Question 11 Data Engineering

What is schema-on-read vs schema-on-write?

Data quality refers to the accuracy, completeness, consistency, timeliness, and validity of data. Poor data quality leads to incorrect analytics, bad business decisions, and compliance risks. Key practices include data validation, profiling, anomaly detection, and implementing quality checks at each stage of the pipeline using tools like Great Expectations or dbt tests. Key differences:

Data lake: Stores raw, unstructured/semi-structured data; schema-on-read; cheaper storage; used for exploration and ML
Data warehouse: Stores structured, transformed data; schema-on-write; optimized for SQL analytics and reporting

Modern architectures often use both, with lakes feeding warehouses, or adopt a lakehouse to combine their strengths. Schema-on-write enforces a predefined schema when data is written to storage (used in data warehouses and relational databases). Schema-on-read stores raw data without enforcing structure and applies a schema only when data is read (used in data lakes). Schema-on-write ensures consistency; schema-on-read provides flexibility for diverse data formats. TimescaleDB is a PostgreSQL extension that turns Postgres into a time-series database. It uses hypertables and chunks (partitioned by time) plus continuous aggregates for fast time-based queries. It is fully SQL-compatible, making it a good choice when teams want a single Postgres database for both relational and time-series data.

Question 12 Data Engineering

What is a covering index?

A covering index is an index that includes all columns referenced by a query (in SELECT, WHERE, JOIN) so the database can satisfy the query entirely from the index without reading the table. It greatly improves read performance for narrow queries. The trade-off is larger index size and slower writes. Fact is the broad term for any data recorded in a fact table — including foreign keys to dimensions. Measure is a specific numeric value in a fact table that can be aggregated (sum, avg, count), e.g., revenue, quantity. All measures are facts; not all facts are measures (foreign keys are facts but not measures). A stateful operator in a stream processor (Flink, Kafka Streams) maintains information across events — e.g., running counts, last-seen values, or session windows. State is stored in RocksDB locally and checkpointed to durable storage for fault tolerance. Stateful operators enable complex logic like pattern detection and joins between streams. CQRS (Command Query Responsibility Segregation) splits a system into a write model (commands) and a read model (queries), often with different storage optimized for each. It is useful when read and write patterns differ significantly — e.g., a transactional DB for writes and a denormalized warehouse for analytics. CQRS pairs naturally with event sourcing and event-driven architectures.

Question 13 Data Engineering

What is a feature store?

A feature store is a centralized system that stores, versions, and serves ML features for both training and real-time inference. It provides low-latency online serving and point-in-time correct offline retrieval for training. Examples: Feast, Tecton, Databricks Feature Store. Feature stores prevent training-serving skew. Partition pruning is an optimization where the query engine skips entire partitions that cannot contain matching rows. If a table is partitioned by event_date and you query WHERE event_date = '2025-06-01', the engine reads only that one partition instead of all data. It works best when queries always include the partition key. Snappy: fast compression/decompression with modest ratio — Google-developed, common with Parquet. Gzip: higher ratio, slower speed — universal compatibility. Zstd: high ratio and fast speed — Facebook-developed, increasingly preferred for analytics. Choice trades off storage cost (compression ratio) against CPU (compression speed). Apache Arrow is a cross-language, columnar in-memory data format with zero-copy reads. It eliminates serialization overhead when moving data between systems (Spark, Pandas, Polars, DuckDB, Flink). Arrow enables fast inter-process communication and powers vectorized compute engines. It is a foundational technology in modern data tooling.

Question 14 Data Engineering

What is the difference between a data lake and a data warehouse?

Key differences:

Data lake: Stores raw, unstructured/semi-structured data; schema-on-read; cheaper storage; used for exploration and ML
Data warehouse: Stores structured, transformed data; schema-on-write; optimized for SQL analytics and reporting

Modern architectures often use both, with lakes feeding warehouses, or adopt a lakehouse to combine their strengths. InfluxDB is a purpose-built time-series database for high-ingest metrics, events, and IoT data. It uses a custom line protocol and TSI (Time-Structured Merge Tree) for efficient storage and queries. InfluxDB is widely used in DevOps monitoring and IoT applications. Deduplication removes duplicate records that result from retries, out-of-order delivery, or multi-source joins. Common methods: ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) in SQL, dropDuplicates(["key"]) in Spark, or idempotent writes to a unique-key table. Dedup is essential for correct analytics. Strategies for late data: (1) Drop events arriving after the watermark closes the window; (2) Update the result by re-emitting for a late-arrival window (requires stateful stream processing); (3) Append to a separate "late" table for later correction. The choice depends on business tolerance for inaccuracy and the cost of reprocessing.

Question 15 Data Engineering

What is Apache Arrow and why is it important?

Data Vault is a data modeling methodology designed for enterprise data warehouses that emphasizes agility, scalability, and auditability. It uses three core entity types:

Hubs: Business keys (e.g., customer ID)
Links: Relationships between hubs
Satellites: Descriptive attributes with history

Data Vault handles changing sources well and supports parallel loading. Apache Arrow is a cross-language, columnar in-memory data format with zero-copy reads. It eliminates serialization overhead when moving data between systems (Spark, Pandas, Polars, DuckDB, Flink). Arrow enables fast inter-process communication and powers vectorized compute engines. It is a foundational technology in modern data tooling. Deduplication removes duplicate records that result from retries, out-of-order delivery, or multi-source joins. Common methods: ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) in SQL, dropDuplicates(["key"]) in Spark, or idempotent writes to a unique-key table. Dedup is essential for correct analytics. Data partitioning divides a large dataset into smaller, more manageable segments called partitions, typically based on a key such as date, region, or ID range. It improves query performance by enabling partition pruning—only scanning relevant partitions. Common strategies include range partitioning, hash partitioning, and list partitioning.

Question 16 Data Engineering

What is a CTE (Common Table Expression) and when should you use it?

Processing time is the wall-clock time when the engine processes the event. Ingestion time is the time the event enters the pipeline (assigned at the source operator). Event time is the timestamp embedded in the data. Event time gives deterministic results independent of arrival delays; processing time is the simplest but produces nondeterministic results. A CTE is a named temporary result set defined with WITH that you can reference within a single query. CTEs improve readability of complex queries, allow recursion (e.g., org-chart traversal), and break logic into logical steps. In most modern warehouses CTEs are inlined, so they don't necessarily improve performance — but they make SQL far easier to maintain. log.retention.hours controls time-based retention: messages are deleted after this many hours, regardless of size. log.segment.bytes controls the maximum size of an individual segment file on disk; the broker rolls a new segment when the current one fills. Retention is evaluated by the segment boundary, so old messages may briefly exceed the retention time until the segment closes. Data modeling is the process of designing the structure of data in a database or warehouse to support efficient storage and querying. It involves defining entities, attributes, relationships, and constraints. Common approaches include dimensional modeling (star/snowflake schemas) for analytics and entity-relationship modeling for transactional systems.

Question 17 Data Engineering

What is a data pipeline?

Deduplication removes duplicate records that result from retries, out-of-order delivery, or multi-source joins. Common methods: ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) in SQL, dropDuplicates(["key"]) in Spark, or idempotent writes to a unique-key table. Dedup is essential for correct analytics. The saga pattern manages distributed transactions across multiple services by sequencing local transactions with compensating actions (rollbacks). There are two styles: orchestration (a central coordinator drives the steps) and choreography (services react to events). Sagas trade strong ACID for eventual consistency in microservices. A data pipeline is an automated series of steps that moves data from one or more sources to a destination system. It typically includes stages for ingestion, transformation, validation, and loading. Pipelines can be batch-based, real-time, or a hybrid of both, and they are often orchestrated by tools like Apache Airflow or Prefect. Data downtime is the period during which data is incomplete, inaccurate, missing, or stale. It is the data equivalent of website downtime, but often invisible to end users until they notice a wrong dashboard. Reducing data downtime is a key motivation for data observability and pipeline testing.

Question 18 Data Engineering

What is a semantic layer?

A semantic layer is a centrally defined, metric-aware layer between raw data and BI tools. It defines business concepts (e.g., "active customer," "net revenue") as code, ensuring every dashboard and analyst query uses the same definition. Tools: dbt Semantic Layer, Cube, LookML, Looker. The semantic layer reduces metric fragmentation across teams. Kafka's transactional producer uses a transaction coordinator and an __transaction_state topic to atomically commit reads and writes across partitions. Producers call initTransactions, beginTransaction, send messages, then commitTransaction. This guarantees that either all messages in a transaction are visible to read-committed consumers, or none are. A dead letter queue (DLQ) is a special destination for messages that cannot be processed successfully after a number of retries. DLQs isolate poison messages so they do not block the main pipeline, allowing engineering teams to inspect, fix, and replay them later. DLQs are a common pattern in Kafka, SQS, and event-driven systems. Both Snowflake and BigQuery automatically cache query results for a short window (typically 24 hours). If a new query is textually identical and the underlying data has not changed, the engine returns the cached result in seconds with zero compute cost. Caching is transparent to the user and is one reason developers should re-use identical query text.

Question 19 Data Engineering

What is columnar storage and why is it used in analytics?

The saga pattern manages distributed transactions across multiple services by sequencing local transactions with compensating actions (rollbacks). There are two styles: orchestration (a central coordinator drives the steps) and choreography (services react to events). Sagas trade strong ACID for eventual consistency in microservices. A bus matrix is a grid that maps business processes (rows) to conformed dimensions (columns). It is the master plan for an enterprise data warehouse built via the Kimball approach. Each filled cell indicates that a fact table for that process will be designed with those conformed dimensions — ensuring cross-mart consistency. Columnar storage stores data by columns rather than by rows. This is highly efficient for analytics because queries typically read only a subset of columns. Benefits include better compression ratios (similar values are stored together) and faster aggregation queries. Columnar formats like Parquet and ORC are standard in data lake and warehouse systems. Reverse ETL is the process of moving data from a warehouse back into operational systems such as CRM (Salesforce, HubSpot), marketing tools, or support platforms. The goal is to put warehouse-curated customer, product, and event data into the tools that frontline teams use daily. Tools: Hightouch, Census, RudderStack Reverse ETL.

Question 20 Data Engineering

What is data governance?

Separation of storage and compute decouples where data is persisted (object storage like S3) from where queries are processed (ephemeral compute clusters). This lets you scale each independently, run multiple engines against the same data, and shut down compute when idle. It is the foundation of modern lakehouse and cloud warehouse design. A data lakehouse stores data in open columnar formats on cheap object storage with a metadata layer (Iceberg, Delta) adding ACID, schema, and time travel. A data warehouse (Snowflake, Redshift, BigQuery) is a proprietary, managed system optimized for SQL analytics with separate storage and compute. Lakehouses offer flexibility and lower cost; warehouses offer mature tooling and performance. Data governance is a framework of policies, processes, and standards that ensures data is managed properly across an organization. It covers data quality, security, privacy, compliance (e.g., GDPR), access control, and metadata management. Good governance ensures data is trustworthy, discoverable, and used responsibly. Log compaction retains only the latest value for each key, discarding older versions. It is used for change logs and slowly changing state. Retention (time or size based) discards old messages regardless of key. Compaction preserves current state over time, while retention preserves recent messages regardless of content.

Question 21 Data Engineering

What is a bloom filter and where is it used in data engineering?

The Bronze layer holds raw, immutable data exactly as it arrived from source systems, often as append-only files (Parquet, JSON). It captures lineage, supports reprocessing, and serves as the system of record. Bronze is queried rarely — only for replay or auditing. A bloom filter is a probabilistic data structure that tests whether an element is possibly in a set, with possible false positives but no false negatives. It is used in HBase, RocksDB, and Parquet to skip whole row groups or data files that cannot contain a value, dramatically speeding up point lookups and join filtering on cold data. Partition pruning is an optimization where the query engine skips entire partitions that cannot contain matching rows. If a table is partitioned by event_date and you query WHERE event_date = '2025-06-01', the engine reads only that one partition instead of all data. It works best when queries always include the partition key. Denormalization is the intentional introduction of redundancy into a database schema to improve read performance. It combines related tables or adds redundant columns to reduce the number of joins needed for queries. Denormalization is commonly used in data warehouses and OLAP systems where fast query performance is more important than storage efficiency.

Question 22 Data Engineering

What is data quality and why does it matter?

Kafka Streams is a client library that runs stream processing inside a Java application, tightly coupled to Kafka topics. Apache Flink is a standalone distributed processing engine that supports many sources and sinks (Kafka, Kinesis, files, databases) with advanced state management. Flink is more powerful for complex jobs; Kafka Streams is simpler when everything stays in Kafka. A narrow transformation (e.g., map, filter) processes data within a single partition — no shuffle across the network. A wide transformation (e.g., groupBy, join with different keys) requires data to be reshuffled across partitions. Wide transformations are expensive because they trigger network I/O and disk spills, and they are the main source of Spark job slowness. Data quality refers to the accuracy, completeness, consistency, timeliness, and validity of data. Poor data quality leads to incorrect analytics, bad business decisions, and compliance risks. Key practices include data validation, profiling, anomaly detection, and implementing quality checks at each stage of the pipeline using tools like Great Expectations or dbt tests. Micro-partitions in Snowflake are immutable 50–500 MB compressed columnar units of storage. Each micro-partition stores metadata including min/max values per column. The query engine uses these statistics to prune micro-partitions that cannot match a query, often scanning only a small fraction of the table.

Question 23 Data Engineering

What is normalization in databases?

Strong consistency guarantees that once a write is acknowledged, all subsequent reads return the new value (e.g., traditional RDBMS). Eventual consistency allows temporary divergence between replicas, with the guarantee that all replicas will converge to the same value given enough time. Eventual consistency is common in distributed systems like DynamoDB and Cassandra for higher availability and partition tolerance. A schema registry is a central service that stores, versions, and enforces schemas for data streams, especially Kafka topics. Producers register a schema before writing; consumers fetch it. The Confluent Schema Registry supports Avro, JSON Schema, and Protobuf with backward/forward compatibility checks, preventing breaking schema changes. The ISR set is the collection of replicas for a partition that are caught up with the leader. The leader waits for acknowledgments from replicas in the ISR (or all of them, depending on acks) before considering a write durable. If a replica falls behind, it is removed from the ISR; if the leader fails, a new leader is elected from the ISR. Normalization is the process of organizing database tables to reduce data redundancy and improve data integrity. It involves dividing large tables into smaller, related tables following normal forms (1NF, 2NF, 3NF, etc.). For example, storing customer addresses in a separate table linked by a foreign key rather than repeating address data in every order row.

Question 24 Data Engineering

What is the difference between OLTP and OLAP?

A view is a stored SQL query that runs every time it is referenced. A materialized view is a query result physically persisted and refreshed on a schedule or trigger. A cache is a temporary, application-managed copy keyed by query parameters (e.g., Redis). Materialized views trade storage for repeated-query speed; caches trade memory for low-latency repeated reads. OLTP (Online Transaction Processing) systems handle high volumes of short, transactional operations (inserts, updates) and are optimized for write performance. OLAP (Online Analytical Processing) systems are designed for complex analytical queries over large datasets, optimized for read performance. Data warehouses are OLAP systems, while production databases are typically OLTP. A bus matrix is a grid that maps business processes (rows) to conformed dimensions (columns). It is the master plan for an enterprise data warehouse built via the Kimball approach. Each filled cell indicates that a fact table for that process will be designed with those conformed dimensions — ensuring cross-mart consistency. At-most-once: messages may be lost but never duplicated (ack before processing). At-least-once: messages are never lost but may be duplicated (ack after processing). Exactly-once: messages are delivered and processed exactly one time, requiring careful commit coordination between offsets and outputs. Stronger guarantees typically cost more in latency and complexity.

Question 25 Data Engineering

What is CQRS and when does it make sense?

A metric store is a system that catalogs, versions, and serves canonical business metrics (KPIs). Unlike raw data, metrics are defined with logic, dimensions, and time grain. Examples: Transform, Cube, Supergrain. A metric store complements a semantic layer by giving metrics first-class governance. ETL stands for Extract, Transform, Load. Data is first extracted from source systems, then transformed (cleaned, enriched, aggregated) in a staging area, and finally loaded into a target data warehouse. It is the traditional approach for data integration where transformations happen before data reaches the warehouse. Deduplication identifies and removes duplicate rows in a dataset, typically keeping one per key. Upsert writes a row to a target, inserting it if new and updating if the key exists. They often work together: CDC streams are deduped to find the latest change per key, then upserted into the warehouse. CQRS (Command Query Responsibility Segregation) splits a system into a write model (commands) and a read model (queries), often with different storage optimized for each. It is useful when read and write patterns differ significantly — e.g., a transactional DB for writes and a denormalized warehouse for analytics. CQRS pairs naturally with event sourcing and event-driven architectures.

Question 26 Data Engineering

What is predicate pushdown?

Predicate pushdown is a query optimization where filter conditions are pushed down to the storage layer so irrelevant rows are excluded before being read into memory. For example, Parquet stores min/max statistics per row group, so a query like WHERE year = 2025 can skip entire row groups. This dramatically reduces I/O and improves query performance on large datasets. A transactional fact table records individual events as they occur, with each row representing a single business event (sale, click, login). It is the most granular fact table and grows append-only. Transactional fact tables support both current and historical analysis at fine granularity. An internal (managed) table has its storage and metadata managed by the engine (e.g., Hive, Iceberg) — dropping the table deletes the data. An external table references data stored outside the engine's control (e.g., a path in S3) — dropping the table leaves the data intact. External tables are standard for data lakes because the data outlives any single engine. OLTP (Online Transaction Processing) systems handle high volumes of short, transactional operations (inserts, updates) and are optimized for write performance. OLAP (Online Analytical Processing) systems are designed for complex analytical queries over large datasets, optimized for read performance. Data warehouses are OLAP systems, while production databases are typically OLTP.

Question 27 Data Engineering

What is AWS DMS?

AWS Database Migration Service (DMS) is a managed service that migrates data into AWS and supports ongoing CDC replication from sources like Oracle, SQL Server, MySQL, and Postgres to targets like S3, Redshift, and Kinesis. It handles schema conversion via the AWS Schema Conversion Tool. CQRS (Command Query Responsibility Segregation) splits a system into a write model (commands) and a read model (queries), often with different storage optimized for each. It is useful when read and write patterns differ significantly — e.g., a transactional DB for writes and a denormalized warehouse for analytics. CQRS pairs naturally with event sourcing and event-driven architectures. ETL stands for Extract, Transform, Load. Data is first extracted from source systems, then transformed (cleaned, enriched, aggregated) in a staging area, and finally loaded into a target data warehouse. It is the traditional approach for data integration where transformations happen before data reaches the warehouse. ROW_NUMBER() assigns a unique sequential number to every row, even with ties. RANK() gives the same number to tied rows but skips the next rank (1, 2, 2, 4). DENSE_RANK() gives the same number to tied rows and does not skip (1, 2, 2, 3). Choose based on whether gaps in ranking matter.

Question 28 Data Engineering

What is the difference between checkpointing and WAL (write-ahead log)?

A WAL logs every change before it is applied, allowing recovery by replaying the log. A checkpoint periodically snapshots the entire state at a point in time. WALs are continuous and append-only; checkpoints are batched and complete state. Many systems use both: WAL for ongoing durability, checkpoints to bound recovery time. An internal (managed) table has its storage and metadata managed by the engine (e.g., Hive, Iceberg) — dropping the table deletes the data. An external table references data stored outside the engine's control (e.g., a path in S3) — dropping the table leaves the data intact. External tables are standard for data lakes because the data outlives any single engine. Strong consistency guarantees that once a write is acknowledged, all subsequent reads return the new value (e.g., traditional RDBMS). Eventual consistency allows temporary divergence between replicas, with the guarantee that all replicas will converge to the same value given enough time. Eventual consistency is common in distributed systems like DynamoDB and Cassandra for higher availability and partition tolerance. Deduplication removes duplicate records that result from retries, out-of-order delivery, or multi-source joins. Common methods: ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) in SQL, dropDuplicates(["key"]) in Spark, or idempotent writes to a unique-key table. Dedup is essential for correct analytics.

Question 29 Data Engineering

What is a grain in a fact table?

The grain of a fact table is the level of detail each row represents — e.g., one row per transaction, per click, or per daily snapshot. The grain must be declared explicitly and consistently, because all facts and dimension keys must align to it. Mixing grains is a common and serious data modeling error. Debezium is an open-source log-based CDC platform that reads database transaction logs (Postgres WAL, MySQL binlog, MongoDB oplog) and publishes every change as a Kafka message. It captures inserts, updates, and deletes with the old and new row state. Debezium is the de facto standard for streaming CDC. A workload manager (WLM) divides a warehouse's compute into queues with different priorities, resource limits, and concurrency levels. For example, ETL jobs may run on a small queue with high memory per query, while ad-hoc analyst queries get many slots with small memory. WLM prevents noisy-neighbor problems and protects critical SLAs. Backfilling is the process of running a data pipeline for historical time periods that were missed or need to be reprocessed. For example, if a daily pipeline failed for three days, backfilling reruns it for those dates. Tools like Airflow support backfilling via airflow dags backfill --start-date 2024-01-01. Idempotent pipelines make backfilling safe and predictable.

Question 30 Data Engineering

What is Kafka's ISR (in-sync replica) set?

A heap table stores rows in no particular order; primary key indexes do not control physical layout. A clustered index (SQL Server, MySQL InnoDB) physically orders table data by the primary key. Clustered tables are faster for range queries on the PK and for sequential reads; heap tables can be faster for bulk inserts and random access patterns. Slowly Changing Dimensions (SCD) are dimension records that change over time in a data warehouse. Common types:

Type 1: Overwrite the old value (no history)
Type 2: Add a new row with versioning (preserves full history)
Type 3: Add a new column to store the previous value (limited history)

Type 2 is the most widely used for maintaining historical accuracy. A semantic layer is a centrally defined, metric-aware layer between raw data and BI tools. It defines business concepts (e.g., "active customer," "net revenue") as code, ensuring every dashboard and analyst query uses the same definition. Tools: dbt Semantic Layer, Cube, LookML, Looker. The semantic layer reduces metric fragmentation across teams. The ISR set is the collection of replicas for a partition that are caught up with the leader. The leader waits for acknowledgments from replicas in the ISR (or all of them, depending on acks) before considering a write durable. If a replica falls behind, it is removed from the ISR; if the leader fails, a new leader is elected from the ISR.

Question 31 Data Engineering

What is a transactional fact table?

A transactional fact table records individual events as they occur, with each row representing a single business event (sale, click, login). It is the most granular fact table and grows append-only. Transactional fact tables support both current and historical analysis at fine granularity. A feature store is a centralized system that stores, versions, and serves ML features for both training and real-time inference. It provides low-latency online serving and point-in-time correct offline retrieval for training. Examples: Feast, Tecton, Databricks Feature Store. Feature stores prevent training-serving skew. Apache Pinot is a real-time, distributed OLAP datastore designed for low-latency user-facing analytics (think LinkedIn's "Who viewed my profile" dashboards). It supports upserts, star-tree indexes, and ingestion from Kafka. Like Druid, it targets sub-second query latency over billions of rows. Snowflake has no primary index concept — it uses micro-partitions automatically and prunes them based on column statistics. Redshift uses a sort key (analogous to a clustered index) and a distribution key that controls how rows are spread across nodes. Choosing good distribution and sort keys in Redshift is critical for performance.

Question 32 Data Engineering

What is data deduplication in pipelines?

Deduplication removes duplicate records that result from retries, out-of-order delivery, or multi-source joins. Common methods: ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) in SQL, dropDuplicates(["key"]) in Spark, or idempotent writes to a unique-key table. Dedup is essential for correct analytics. The small files problem occurs when a distributed file system holds millions of tiny files. Each file consumes metadata slots (in HDFS the NameNode RAM), and processing many small files causes excessive seek overhead. Solutions include compacting into larger files, using sequence/Parquet files, and leveraging columnar table formats like Iceberg or Delta that pack many records per file. Key differences:

Data lake: Stores raw, unstructured/semi-structured data; schema-on-read; cheaper storage; used for exploration and ML
Data warehouse: Stores structured, transformed data; schema-on-write; optimized for SQL analytics and reporting

Modern architectures often use both, with lakes feeding warehouses, or adopt a lakehouse to combine their strengths. The CAP theorem states that a distributed data system can provide at most two of three guarantees simultaneously: Consistency (every read sees the latest write), Availability (every request gets a response), and Partition tolerance (the system keeps working despite network partitions). Since partitions are unavoidable, the real choice is between consistency and availability.

Question 33 Data Engineering

What is a primary advantage of ORC over Parquet?

Apache Arrow is a cross-language, columnar in-memory data format with zero-copy reads. It eliminates serialization overhead when moving data between systems (Spark, Pandas, Polars, DuckDB, Flink). Arrow enables fast inter-process communication and powers vectorized compute engines. It is a foundational technology in modern data tooling. Event sourcing persists the sequence of state changes as events rather than storing current state. The current state is derived by replaying events. Benefits: full audit trail, time travel, easy reconstruction. Challenges: query complexity (often paired with CQRS) and event schema evolution. Kafka's compacted topics are a common event store. In a request-response architecture, services synchronously call each other and wait for replies — coupling, latency, and failure modes compound. In an event-driven architecture, services publish events to a broker (Kafka, SNS) and react asynchronously — producers and consumers are decoupled and can scale independently. EDA is the foundation of modern data streaming systems. ORC (Optimized Row Columnar) and Parquet are both columnar formats with similar capabilities. Historically ORC has been tightly integrated with Hive and provides strong predicate pushdown via zone maps, while Parquet has wider adoption across Spark, Presto, Trino, and BigQuery. The choice usually depends on the engine ecosystem; both support compression, schema evolution, and nested types.

Question 34 Data Engineering

What is micro-partitioning in Snowflake?

Micro-partitions in Snowflake are immutable 50–500 MB compressed columnar units of storage. Each micro-partition stores metadata including min/max values per column. The query engine uses these statistics to prune micro-partitions that cannot match a query, often scanning only a small fraction of the table. An internal (managed) table has its storage and metadata managed by the engine (e.g., Hive, Iceberg) — dropping the table deletes the data. An external table references data stored outside the engine's control (e.g., a path in S3) — dropping the table leaves the data intact. External tables are standard for data lakes because the data outlives any single engine. A checkpoint is a durable snapshot of a stream processing job's state and source offsets, written periodically to reliable storage. On failure, the engine restores the state from the last checkpoint and resumes from the saved offsets, providing at-least-once or exactly-once semantics. Flink and Spark Structured Streaming both rely on checkpoints for fault tolerance. A data pipeline is an automated series of steps that moves data from one or more sources to a destination system. It typically includes stages for ingestion, transformation, validation, and loading. Pipelines can be batch-based, real-time, or a hybrid of both, and they are often orchestrated by tools like Apache Airflow or Prefect.

Question 35 Data Engineering

What is Apache Pinot?

Kafka Streams is a client library that runs stream processing inside a Java application, tightly coupled to Kafka topics. Apache Flink is a standalone distributed processing engine that supports many sources and sinks (Kafka, Kinesis, files, databases) with advanced state management. Flink is more powerful for complex jobs; Kafka Streams is simpler when everything stays in Kafka. The Gold layer holds business-level aggregates and curated datasets ready for BI, ML, and reporting. It is highly denormalized, project-shaped, and optimized for the specific consumption pattern (dashboards, reverse ETL, ML features). Gold is the smallest layer by volume but the most consumed. A vectorized engine processes data in batches of rows (vectors) rather than one row at a time, using SIMD CPU instructions to apply operators on whole vectors. This dramatically reduces interpretation overhead and CPU branch mispredictions. Examples: DuckDB, ClickHouse, Velox, and Apache Arrow-based execution layers. Apache Pinot is a real-time, distributed OLAP datastore designed for low-latency user-facing analytics (think LinkedIn's "Who viewed my profile" dashboards). It supports upserts, star-tree indexes, and ingestion from Kafka. Like Druid, it targets sub-second query latency over billions of rows.

Question 36 Data Engineering

What is Apache Flink?

A degenerate dimension is a reference number stored in the fact table (e.g., order_id, invoice_number, transaction_id) that does not have its own dimension table. It serves as a join back to the operational system and is useful for tracing individual transactions, even though it has no descriptive attributes. SCD Type 0 (also called "retain original") means the dimension attribute is never updated — e.g., a customer's original signup date or country of birth. It is used for attributes that should be frozen at the time of the event to preserve historical accuracy in reports. Apache Flink is a distributed stream processing framework designed for stateful, real-time data processing with low latency. Unlike Spark's micro-batch approach, Flink processes events one at a time with true streaming semantics. It supports event-time processing, exactly-once guarantees, and complex event processing, making it ideal for real-time analytics and event-driven applications. A lakehouse combines the best features of data lakes and data warehouses into a single architecture. It stores data in open formats on cheap object storage (like a lake) while supporting ACID transactions, schema enforcement, and SQL analytics (like a warehouse). Technologies like Delta Lake, Apache Iceberg, and Apache Hudi enable this pattern.

Question 37 Data Engineering

What is the difference between a transaction and a micro-batch?

AWS Database Migration Service (DMS) is a managed service that migrates data into AWS and supports ongoing CDC replication from sources like Oracle, SQL Server, MySQL, and Postgres to targets like S3, Redshift, and Kinesis. It handles schema conversion via the AWS Schema Conversion Tool. A transaction is a single unit of work with ACID guarantees (database, Kafka). A micro-batch is a small group of records processed together at fixed intervals (Spark Streaming's default). Micro-batches trade off latency (typically 0.5–2 seconds) for simpler, more reliable exactly-once semantics than true streaming. An accumulating snapshot fact table tracks the lifecycle of a process with multiple milestone dates (e.g., order placed, paid, shipped, delivered). Each row has several date foreign keys and is updated as the process advances. Accumulating snapshots are great for measuring end-to-end process duration and bottlenecks. Data skew occurs when some partitions or keys hold disproportionately more data than others. In distributed systems like Spark, this causes certain executors to do most of the work while others sit idle, leading to stragglers and OOM errors. Common mitigations include salting keys, adaptive partition sizes, and choosing a partitioning key with high cardinality.

Question 38 Data Engineering

What is a Kimball vs Inmon approach to data warehousing?

The Bronze layer holds raw, immutable data exactly as it arrived from source systems, often as append-only files (Parquet, JSON). It captures lineage, supports reprocessing, and serves as the system of record. Bronze is queried rarely — only for replay or auditing. The Kimball approach (bottom-up) builds dimensional data marts directly from source systems, then integrates them via a bus matrix of conformed dimensions. The Inmon approach (top-down) builds a normalized enterprise data warehouse (EDW) first, then derives subject-area marts from it. Kimball prioritizes rapid delivery of analytics; Inmon prioritizes data integrity and a single source of truth. A WAL logs every change before it is applied, allowing recovery by replaying the log. A checkpoint periodically snapshots the entire state at a point in time. WALs are continuous and append-only; checkpoints are batched and complete state. Many systems use both: WAL for ongoing durability, checkpoints to bound recovery time. Both Snowflake and BigQuery automatically cache query results for a short window (typically 24 hours). If a new query is textually identical and the underlying data has not changed, the engine returns the cached result in seconds with zero compute cost. Caching is transparent to the user and is one reason developers should re-use identical query text.

Question 39 Data Engineering

What is the CAP theorem tradeoff?

Database: optimized for OLTP — many small writes, point reads. Data warehouse: optimized for OLAP — complex SQL over structured historical data. Lakehouse: combines open file formats on object storage with warehouse-style features (ACID, schema, time travel) — best of both, but a younger stack. Choose by access pattern and data variety. A bus matrix is a grid that maps business processes (rows) to conformed dimensions (columns). It is the master plan for an enterprise data warehouse built via the Kimball approach. Each filled cell indicates that a fact table for that process will be designed with those conformed dimensions — ensuring cross-mart consistency. log.retention.hours controls time-based retention: messages are deleted after this many hours, regardless of size. log.segment.bytes controls the maximum size of an individual segment file on disk; the broker rolls a new segment when the current one fills. Retention is evaluated by the segment boundary, so old messages may briefly exceed the retention time until the segment closes. The CAP theorem states that a distributed data system can provide at most two of three guarantees simultaneously: Consistency (every read sees the latest write), Availability (every request gets a response), and Partition tolerance (the system keeps working despite network partitions). Since partitions are unavoidable, the real choice is between consistency and availability.

Question 40 Data Engineering

What is data SLI and SLO?

A primary key uniquely identifies each row in a table and cannot be null or duplicated. A foreign key is a column in one table that references the primary key of another table, establishing a referential link. Foreign keys enforce referential integrity, preventing orphan rows and maintaining consistent relationships across tables. A data SLI (Service Level Indicator) is a quantitative measure of pipeline health — e.g., "data freshness" or "% of null values in column X." A data SLO (Service Level Objective) is a target for that indicator, like "99% of dashboards are updated within 1 hour." SLOs make data reliability a first-class engineering concern. Denormalization is the intentional introduction of redundancy into a database schema to improve read performance. It combines related tables or adds redundant columns to reduce the number of joins needed for queries. Denormalization is commonly used in data warehouses and OLAP systems where fast query performance is more important than storage efficiency. A transactional fact table records individual events as they occur, with each row representing a single business event (sale, click, login). It is the most granular fact table and grows append-only. Transactional fact tables support both current and historical analysis at fine granularity.

Question 41 Data Engineering

What is a sort key in Redshift and how does it help?

A sort key in Redshift determines the physical order of rows within each block. Range-restricted queries on the sort key benefit from zone-map pruning, similar to a clustered index. COMPOUND sort keys order by all listed columns; INTERLEAVED gives equal weight to each column. Choose sort keys based on frequent filter and join columns. Normalization is the process of organizing database tables to reduce data redundancy and improve data integrity. It involves dividing large tables into smaller, related tables following normal forms (1NF, 2NF, 3NF, etc.). For example, storing customer addresses in a separate table linked by a foreign key rather than repeating address data in every order row. A role-playing dimension is a single dimension table that is used multiple times in the same fact table, with each usage representing a different role. The classic example is a dim_date joined three times as order_date, ship_date, and delivery_date. It is implemented with multiple foreign keys in the fact table referencing the same dimension. A bloom filter is a probabilistic data structure that tests whether an element is possibly in a set, with possible false positives but no false negatives. It is used in HBase, RocksDB, and Parquet to skip whole row groups or data files that cannot contain a value, dramatically speeding up point lookups and join filtering on cold data.

Question 42 Data Engineering

What is exactly-once semantics in stream processing?

A feature store is a centralized system that stores, versions, and serves ML features for both training and real-time inference. It provides low-latency online serving and point-in-time correct offline retrieval for training. Examples: Feast, Tecton, Databricks Feature Store. Feature stores prevent training-serving skew. Snowflake decouples compute (a virtual warehouse) from storage. A virtual warehouse can be resized on the fly (e.g., X-Small to Large) and scaled out by adding clusters for concurrent queries. Because storage is shared, multiple warehouses can query the same data without copying it, and warehouses can be suspended when idle to save cost. The outbox pattern solves dual-write consistency between a database and a message broker. Instead of writing to the DB and publishing to Kafka in the same business transaction (which can fail partially), the service writes the message to an outbox table in the same transaction. A separate process (Debezium, a CDC reader) tails the outbox and publishes to Kafka, ensuring at-least-once delivery. Exactly-once semantics (EOS) means each event is processed and its effects (writes to sinks, side effects) are committed exactly one time, even in the face of failures. EOS requires idempotent sinks or transactional output, plus checkpointing of source offsets. Apache Flink supports exactly-once end-to-end, while Spark Structured Streaming provides it within micro-batch checkpoints.

Question 43 Data Engineering

What is data modeling?

Data modeling is the process of designing the structure of data in a database or warehouse to support efficient storage and querying. It involves defining entities, attributes, relationships, and constraints. Common approaches include dimensional modeling (star/snowflake schemas) for analytics and entity-relationship modeling for transactional systems. UNION combines and de-duplicates rows from two queries, requiring a sort or hash. UNION ALL appends all rows from both queries, including duplicates, with no extra work. Use UNION ALL whenever you do not need dedup — it is significantly faster, especially for large datasets. Window processing groups streaming events into finite sets for aggregation. Common window types:

Tumbling window: Fixed-size, non-overlapping time intervals
Sliding window: Fixed-size intervals that overlap by a slide duration
Session window: Dynamic windows based on activity gaps

Windowing enables operations like "count events per 5-minute window" in tools like Flink and Spark Streaming. A data lake is a centralized storage repository that holds vast amounts of raw data in its native format—structured, semi-structured, or unstructured. Unlike a data warehouse, a data lake uses schema-on-read, meaning structure is applied only when the data is queried. Common implementations use HDFS, Amazon S3, or Azure Data Lake Storage.

Question 44 Data Engineering

What is a bus matrix in Kimball methodology?

A Spark DataFrame is a distributed collection of data organized into named columns, similar to a table in a relational database. It provides a high-level API for transformations and actions using languages like Python and Scala. Example: df = spark.read.parquet("data.parquet") followed by df.filter(df.age > 30).groupBy("city").count(). A bus matrix is a grid that maps business processes (rows) to conformed dimensions (columns). It is the master plan for an enterprise data warehouse built via the Kimball approach. Each filled cell indicates that a fact table for that process will be designed with those conformed dimensions — ensuring cross-mart consistency. Event sourcing persists the sequence of state changes as events rather than storing current state. The current state is derived by replaying events. Benefits: full audit trail, time travel, easy reconstruction. Challenges: query complexity (often paired with CQRS) and event schema evolution. Kafka's compacted topics are a common event store. A dimension table stores descriptive attributes used to filter, group, and label facts in a data warehouse. Examples include dim_customer (name, city, segment), dim_product (category, brand), and dim_date (year, month, quarter). Dimension tables are usually smaller and wider (many columns) compared to fact tables.

Question 45 Data Engineering

What is an upsert and how is it implemented in different systems?

Both Snowflake and BigQuery automatically cache query results for a short window (typically 24 hours). If a new query is textually identical and the underlying data has not changed, the engine returns the cached result in seconds with zero compute cost. Caching is transparent to the user and is one reason developers should re-use identical query text. A data pipeline is an automated series of steps that moves data from one or more sources to a destination system. It typically includes stages for ingestion, transformation, validation, and loading. Pipelines can be batch-based, real-time, or a hybrid of both, and they are often orchestrated by tools like Apache Airflow or Prefect. An upsert (update-or-insert) writes a row to a table, inserting it if new and updating it if the key already exists. Implementations: INSERT ... ON CONFLICT DO UPDATE (Postgres), MERGE (SQL Server, Snowflake, Delta Lake), PUT (DynamoDB), or replace-by-key in Kafka log-compacted topics. Upserts are fundamental to maintaining up-to-date state in warehouses. Event time is the timestamp embedded in the data when the event actually occurred. Processing time is the wall-clock time when the system processes the event. A watermark is a threshold that tracks how far the system is willing to wait for late-arriving data. Event-time processing with watermarks is essential for correct windowed aggregation when events arrive out of order.

Question 46 Data Engineering

What is the difference between narrow and wide transformations in Spark?

An UPDATE statement changes values in rows that already match a predicate. A MERGE statement (also called UPSERT) combines INSERT, UPDATE, and DELETE in a single statement by matching source rows to target rows by key. MERGE is preferred in data warehousing because it handles both new and changed rows atomically and works well with CDC ingestion patterns. A narrow transformation (e.g., map, filter) processes data within a single partition — no shuffle across the network. A wide transformation (e.g., groupBy, join with different keys) requires data to be reshuffled across partitions. Wide transformations are expensive because they trigger network I/O and disk spills, and they are the main source of Spark job slowness. Columnar storage stores data by columns rather than by rows. This is highly efficient for analytics because queries typically read only a subset of columns. Benefits include better compression ratios (similar values are stored together) and faster aggregation queries. Columnar formats like Parquet and ORC are standard in data lake and warehouse systems. Spark Structured Streaming uses a watermark defined on event time to bound how long the engine waits for late data. withWatermark("event_time", "10 minutes") means state for a window is kept for 10 minutes after the watermark passes its end, after which late data is dropped. Watermarks enable state cleanup and bounded memory.

Question 47 Data Engineering

What is a factless fact table?

A factless fact table is a fact table that contains no numeric measures—only foreign keys to dimensions. It records events or coverage such as "student attended class on date" or "product was on promotion in store on date." Factless tables answer questions like "how many users logged in per day?" by counting rows. OLTP (Online Transaction Processing) systems handle high volumes of short, transactional operations (inserts, updates) and are optimized for write performance. OLAP (Online Analytical Processing) systems are designed for complex analytical queries over large datasets, optimized for read performance. Data warehouses are OLAP systems, while production databases are typically OLTP. Data observability is the practice of monitoring a data system's health across five pillars: freshness, volume, schema, quality, and lineage. Tools like Monte Carlo, Great Expectations, and Datafold detect anomalies, broken pipelines, and silent data corruption. Observability is to data what application monitoring is to services. ORC (Optimized Row Columnar) and Parquet are both columnar formats with similar capabilities. Historically ORC has been tightly integrated with Hive and provides strong predicate pushdown via zone maps, while Parquet has wider adoption across Spark, Presto, Trino, and BigQuery. The choice usually depends on the engine ecosystem; both support compression, schema evolution, and nested types.

Question 48 Data Engineering

What is a salting technique for handling data skew?

A snowflake schema is a variation of the star schema where dimension tables are normalized into multiple related tables. For example, a product dimension might be split into product, category, and brand tables. This reduces data redundancy but requires more joins, which can impact query performance. A dashboard is an interactive, real-time view of metrics designed for ongoing monitoring (KPIs, alerts). A report is a static, periodic deliverable (PDF, email) summarizing data over a period. Dashboards prioritize exploration and drill-down; reports prioritize distribution and record-keeping. In a request-response architecture, services synchronously call each other and wait for replies — coupling, latency, and failure modes compound. In an event-driven architecture, services publish events to a broker (Kafka, SNS) and react asynchronously — producers and consumers are decoupled and can scale independently. EDA is the foundation of modern data streaming systems. Salting is a technique to reduce skew by appending a random prefix to skewed keys, then splitting the join into a two-phase process: first join with salted keys, then join the partial results. This distributes hot keys across many partitions so work is parallelized. It is commonly used in Spark and Hive jobs that suffer from groupBy or join skew.

Question 49 Data Engineering

What is a heap table vs an indexed (clustered) table?

A heap table stores rows in no particular order; primary key indexes do not control physical layout. A clustered index (SQL Server, MySQL InnoDB) physically orders table data by the primary key. Clustered tables are faster for range queries on the PK and for sequential reads; heap tables can be faster for bulk inserts and random access patterns. Batch processing handles data in large, discrete chunks at scheduled intervals (e.g., hourly or daily), while stream processing handles data continuously in near real-time as it arrives. Batch is suited for historical analytics and reporting; streaming is suited for real-time dashboards, fraud detection, and event-driven systems. Tools like Spark support both paradigms. Change Data Capture (CDC) is a technique for identifying and tracking changes (inserts, updates, deletes) in a source database and propagating them to a target system. CDC methods include log-based (reading database transaction logs), trigger-based, and timestamp-based approaches. Tools like Debezium and AWS DMS are commonly used for CDC. A topic in Kafka is a named category or feed to which messages are published. Each topic is divided into partitions, which are ordered, immutable sequences of messages that enable parallelism and scalability. Messages within a partition are assigned sequential offsets. Partitions allow multiple consumers to read from a topic concurrently.

Question 50 Data Engineering

What is a Slowly Changing Dimension Type 0?

Data partitioning divides a large dataset into smaller, more manageable segments called partitions, typically based on a key such as date, region, or ID range. It improves query performance by enabling partition pruning—only scanning relevant partitions. Common strategies include range partitioning, hash partitioning, and list partitioning. The small files problem occurs when a distributed file system holds millions of tiny files. Each file consumes metadata slots (in HDFS the NameNode RAM), and processing many small files causes excessive seek overhead. Solutions include compacting into larger files, using sequence/Parquet files, and leveraging columnar table formats like Iceberg or Delta that pack many records per file. SCD Type 0 (also called "retain original") means the dimension attribute is never updated — e.g., a customer's original signup date or country of birth. It is used for attributes that should be frozen at the time of the event to preserve historical accuracy in reports. A row-oriented engine processes operators one row at a time, suited for OLTP. A column-oriented engine processes data column-by-column, applying operators on entire vectors of values, ideal for analytics that touch few columns of many rows. Modern engines like ClickHouse and DuckDB combine vectorized execution with columnar storage for high performance.