Title: Data Engineering — LearnCoachAssist

Q: What is ETL and how does it work?
A: 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.

Q: What is ELT and how does it differ from ETL?
A: ELT stands for Extract, Load, Transform. Unlike ETL, raw data is loaded directly into the target system (e.g., a cloud data warehouse) and transformations happen inside the warehouse using its compute power. ELT leverages the scalability of modern warehouses like BigQuery, Snowflake, and Redshift to handle transformations at scale.

Q: What is a data pipeline?
A: 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.

Q: What is a data warehouse?
A: A data warehouse is a centralized repository designed for analytical querying and reporting. It stores structured, historical data that has been cleaned and transformed from operational systems. Data warehouses use schema-on-write, meaning data is structured before being stored, and are optimized for read-heavy OLAP workloads.

Q: What is a star schema in data warehousing?
A: A star schema is a data modeling technique where a central fact table (containing measurable metrics) is surrounded by dimension tables (containing descriptive attributes). It is called a "star" because the diagram resembles a star shape. Star schemas are denormalized for fast query performance and are the most common schema in data warehouses.

Q: What is a snowflake schema?
A: 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.

Q: What is Apache Spark?
A: Apache Spark is a distributed computing framework for large-scale data processing. It provides APIs in Python (PySpark), Scala, Java, and R, and supports batch processing, stream processing, machine learning (MLlib), and graph processing. Spark uses in-memory computation, making it significantly faster than Hadoop MapReduce for many workloads.

Q: What is Apache Kafka?
A: 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.

Q: What is a data lake?
A: 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.

Q: What is the difference between batch and stream processing?
A: 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.

Q: What is data quality and why does it matter?
A: 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.

Q: What is Apache Airflow?
A: Apache Airflow is an open-source workflow orchestration platform for authoring, scheduling, and monitoring data pipelines. Pipelines are defined as DAGs (Directed Acyclic Graphs) in Python code. Airflow provides a web UI for monitoring, supports retries and alerting, and integrates with cloud services, databases, and processing frameworks.

Q: What is a DAG in the context of data orchestration?
A: 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.

Q: What is data modeling?
A: 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.

Q: What is normalization in databases?
A: 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.

Q: What is denormalization and when is it used?
A: 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.

Q: What is data partitioning?
A: 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.

Q: What is data governance?
A: 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.

Q: What is Change Data Capture (CDC)?
A: 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.

Q: What is a data catalog?
A: A data catalog is an organized inventory of data assets in an organization that provides metadata, data lineage, descriptions, and search capabilities. It helps data engineers and analysts discover, understand, and trust available datasets. Popular tools include Apache Atlas, AWS Glue Data Catalog, Alation, and DataHub.

Q: What is data mesh?
A: 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.

Q: What is a lakehouse architecture?
A: 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.

Q: What are Slowly Changing Dimensions (SCD)?
A: 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.

Q: What is a surrogate key?
A: A surrogate key is a system-generated, artificial primary key (usually an auto-incrementing integer or UUID) that has no business meaning. It is used in data warehouses to uniquely identify rows in dimension tables, decoupling the warehouse key from the source system's natural key. This provides stability even when source keys change or are reused.

Q: What is idempotency in data pipelines?
A: Idempotency means that running a pipeline operation multiple times with the same input produces the same result without side effects. For example, an idempotent load uses INSERT ... ON CONFLICT DO UPDATE or replaces an entire partition. This is critical for pipeline reliability because it allows safe retries after failures without creating duplicate data.

Q: What is schema-on-read vs schema-on-write?
A: 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.

Q: What is columnar storage and why is it used in analytics?
A: 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.

Q: What is Apache Parquet?
A: 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.

Q: What is Apache Avro?
A: Apache Avro is a row-based data serialization format that stores data with its schema in the same file. It supports schema evolution, allowing readers to handle data written with older or newer schemas. Avro is commonly used for Kafka message serialization, data exchange between systems, and storing data in Hadoop ecosystems.

Q: What is dbt (data build tool)?
A: dbt is an open-source transformation tool that enables data analysts and engineers to transform data in the warehouse using SQL SELECT statements. It handles materializations (tables, views, incremental models), testing, documentation, and dependency management. dbt follows software engineering practices like version control, code review, and CI/CD for analytics code.

Q: What is data lineage?
A: Data lineage tracks the origin, movement, and transformation of data throughout its lifecycle—from source to destination. It answers questions like "where did this data come from?" and "what transformations were applied?" Data lineage is essential for debugging, impact analysis, regulatory compliance, and building trust in data. Tools like OpenLineage and dbt provide lineage capabilities.

Q: What is a fact table in dimensional modeling?
A: A fact table is the central table in a star or snowflake schema that stores quantitative, measurable business metrics (facts) such as sales amount, quantity, or duration. Each row represents a business event and contains foreign keys referencing dimension tables plus measures. Fact tables are typically the largest tables in a data warehouse.

Q: What is a dimension table?
A: 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.

Q: What is the difference between OLTP and OLAP?
A: 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.

Q: What is a Spark DataFrame?
A: 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().

Q: What are Kafka topics and partitions?
A: 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.

Q: What is a Kafka consumer group?
A: 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.

Q: What is data profiling?
A: 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.

Q: What is an incremental load strategy?
A: An incremental load processes only new or changed data since the last pipeline run, rather than reloading the entire dataset. It uses markers like timestamps, sequence numbers, or CDC logs to detect changes. Incremental loads are more efficient than full loads for large datasets and reduce processing time and resource consumption.

Q: What is Apache Iceberg?
A: Apache Iceberg is an open table format for large analytic datasets that brings warehouse-like features to data lakes. It supports ACID transactions, schema evolution, time travel (querying historical snapshots), and partition evolution (changing partitioning without rewriting data). Iceberg works with Spark, Trino, Flink, and other query engines.

Q: What is Delta Lake?
A: Delta Lake is an open-source storage layer developed by Databricks that adds ACID transactions and data reliability to data lakes. It stores data in Parquet format with a transaction log for versioning. Key features include time travel, schema enforcement, schema evolution, and MERGE operations for upserts on data lake tables.

Q: What is schema evolution?
A: 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.

Q: What is data sharding?
A: 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.

Q: What is a materialized view?
A: A materialized view is a precomputed query result stored as a physical table that can be refreshed periodically. Unlike a regular view (which runs the query on each access), a materialized view trades storage space for faster read performance. They are commonly used in data warehouses and tools like dbt to speed up expensive aggregation queries.

Q: What is the difference between a data lake and a data warehouse?
A: 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.

Q: What is Great Expectations?
A: 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.

Q: What is window processing in stream processing?
A: 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.

Q: What is Apache Flink?
A: 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.

Q: What is a data vault modeling approach?
A: 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.

Q: What is backfilling in data engineering?
A: 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.

Q: What does ACID stand for in database systems?
A: ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity: a transaction either fully completes or fully rolls back. Consistency: a transaction moves the database from one valid state to another. Isolation: concurrent transactions do not interfere with each other. Durability: committed data survives crashes and power loss.

Q: What is the CAP theorem tradeoff?
A: 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.

Q: What is the difference between strongly consistent and eventually consistent systems?
A: 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.

Q: What is the difference between a primary key and a foreign key?
A: 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.

Q: What are the main types of NoSQL databases?
A: Main NoSQL types:
- Key-value stores: Redis, DynamoDB — simple lookups by key
- Document stores: MongoDB, CouchDB — JSON-like documents
- Column-family: Cassandra, HBase — wide columns grouped by row key
- Graph databases: Neo4j, Neptune — nodes and edges for relationships
Each type optimizes for a different access pattern.

Q: When should you choose a document database over a relational database?
A: Choose a document database (e.g., MongoDB) when data is flexibly structured, schemas evolve rapidly, you store nested or hierarchical data, and your access patterns are document-level rather than requiring complex multi-row joins. Use a relational database when you need strong ACID transactions across many rows, complex joins, and a stable schema.

Q: What is the difference between Hadoop HDFS and cloud object storage like S3?
A: HDFS (Hadoop Distributed File System) stores large files across commodity cluster nodes with block-level replication managed by a NameNode. S3 and other cloud object stores provide virtually unlimited object storage over HTTP APIs with 11 nines of durability and no cluster to manage. Cloud object stores are now preferred for data lakes due to scalability, cost, and decoupling of storage and compute.

Q: What is the small files problem in HDFS and data lakes?
A: 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.

Q: What is predicate pushdown?
A: 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.

Q: What is partition pruning?
A: 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.

Q: What is data skew and why is it dangerous in distributed processing?
A: 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.

Q: What is the difference between narrow and wide transformations in Spark?
A: 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.

Q: What is a Spark shuffle and what causes it?
A: A shuffle is the process of redistributing data across partitions in Spark, typically triggered by groupByKey, reduceByKey, join, or repartition. Shuffle writes data to local disk and transfers it over the network between executors. It is the most expensive operation in Spark and is the primary target for optimization.

Q: What is a broadcast join and when should you use it?
A: A broadcast join copies a small DataFrame to every executor, avoiding a shuffle of the large side. It is ideal when one side of the join fits in memory (typically < 200 MB by default in Spark) and the other is much larger. Use broadcast(small_df) in PySpark or MAPJOIN hint in SQL to force the optimization.

Q: What is a salting technique for handling data skew?
A: 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.

Q: What is exactly-once semantics in stream processing?
A: 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.

Q: What is the difference between at-most-once, at-least-once, and exactly-once delivery?
A: 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.

Q: What is an event-time vs processing-time watermark?
A: 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.

Q: What are common late-arriving data strategies?
A: 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.

Q: What is backpressure in stream processing?
A: Backpressure is a flow-control mechanism where a slow consumer signals upstream producers to slow down when its buffer fills. Reactive Streams and frameworks like Akka Streams, Flink, and Kafka clients implement backpressure to prevent OOM errors and crashes when downstream cannot keep up with the rate of incoming data.

Q: What is a dead letter queue (DLQ) used for?
A: 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.

Q: What is the difference between Kafka's log.retention.hours and log.segment.bytes?
A: 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.

Q: What is Kafka's ISR (in-sync replica) set?
A: 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.

Q: What does the acks setting in Kafka producers control?
A: The acks setting controls how many broker acknowledgments a producer must receive before considering a write successful. acks=0 means fire-and-forget (lowest latency, no durability). acks=1 waits only for the leader. acks=all (or -1) waits for all in-sync replicas (strongest durability, highest latency).

Q: What is compaction in Kafka and how is it different from retention?
A: 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.

Q: What is exactly-once delivery in Kafka transactions?
A: 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.

Q: What is the difference between Kafka Streams and Apache Flink?
A: 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.

Q: What is an upsert and how is it implemented in different systems?
A: 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.

Q: What is the difference between merge and update SQL statements?
A: 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.

Q: What is a primary advantage of ORC over Parquet?
A: 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.

Q: What is the difference between Snappy, Gzip, and Zstd compression?
A: 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).

Q: What is data skew in joins and how do you detect it?
A: Data skew in joins happens when the join key is unevenly distributed, so a few keys match the vast majority of rows. Detect it by running SELECT key, COUNT(*) FROM t GROUP BY key ORDER BY 2 DESC LIMIT 10. If one key has millions of rows, expect a skewed stage in Spark/Flink. Mitigations: broadcast join, salting, or splitting the join into two phases.

Q: What is a factless fact table?
A: 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.

Q: What is a junk dimension?
A: A junk dimension combines low-cardinality flags and indicators (e.g., order status, payment method, is_gift) into a single dimension table to avoid cluttering the fact table. Each unique combination of flags becomes one row in the junk dimension with its own surrogate key. This reduces fact table width and improves compression in columnar storage.

Q: What is a degenerate dimension?
A: 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.

Q: What is a role-playing dimension?
A: 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.

Q: What is a conformed dimension?
A: A conformed dimension is a dimension table that has the same meaning and key across multiple data marts or fact tables. For example, a single dim_customer is used by sales, support, and marketing marts. Conformed dimensions enable consistent reporting across business areas and prevent conflicting definitions of the same entity.

Q: What is a Kimball vs Inmon approach to data warehousing?
A: 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.

Q: What is the difference between a data lakehouse and a data warehouse?
A: 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.

Q: What is a file format's row group and how does it affect performance?
A: A row group (Parquet) or stripe (ORC) is a horizontal batch of rows (typically 64–256 MB) stored column-by-column. Query engines read row groups in parallel and use min/max statistics for predicate pushdown. Larger row groups improve compression but worsen read amplification; smaller ones give finer-grained skipping but increase metadata overhead.

Q: What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?
A: 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.

Q: What is a CTE (Common Table Expression) and when should you use it?
A: 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.

Q: What is the difference between UNION and UNION ALL?
A: 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.

Q: What is a star join optimization?
A: 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.

Q: What is a covering index?
A: 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.

Q: What is the difference between VARCHAR and CHAR data types?
A: CHAR(n) is a fixed-length string padded with spaces — fast comparisons but wastes space. VARCHAR(n) is a variable-length string up to n characters, saving space for short values but with slight per-row overhead. Use CHAR only for short codes of consistent length (e.g., country codes); use VARCHAR for general text.

Q: What is a heap table vs an indexed (clustered) table?
A: 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.

Q: What is a bloom filter and where is it used in data engineering?
A: 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.

Q: What is min/max indexing (zone maps) in columnar files?
A: Min/max indexes (also called zone maps) store the minimum and maximum value of each column for each row group or stripe. Query engines use them for predicate pushdown: if the query value is outside the [min, max] range, the entire row group is skipped. This is one of the largest performance wins in columnar file formats.

Q: What is a vectorized query engine?
A: 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.

Q: What is Apache Arrow and why is it important?
A: 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.

Q: What is the difference between a row-oriented and column-oriented execution engine?
A: 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.

Q: What is a materialized view vs a regular view vs a cache?
A: 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.

Q: What is query result caching in Snowflake and BigQuery?
A: 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.

Q: What is a workload manager (WLM) in a data warehouse?
A: 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.

Q: What is the difference between shared-disk and shared-nothing architectures?
A: In a shared-disk architecture (e.g., traditional Oracle RAC), all nodes access the same storage; the bottleneck is usually the storage network. In a shared-nothing architecture (e.g., Snowflake, Redshift, Spark, BigQuery), each node has its own CPU, memory, and disk; queries are parallelized across nodes that exchange data over the network. Shared-nothing scales horizontally more easily.

Q: What is compute cluster scaling in Snowflake?
A: 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.

Q: What is separation of storage and compute?
A: 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.

Q: What is a primary index in Snowflake vs Redshift?
A: 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.

Q: What is micro-partitioning in Snowflake?
A: 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.

Q: What is a sort key in Redshift and how does it help?
A: 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.

Q: What is a distribution key in Redshift?
A: 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).

Q: What is the difference between internal and external tables in a data lake?
A: 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.

Q: What is a managed table in Databricks Unity Catalog?
A: In Databricks Unity Catalog, a managed table stores both data and metadata under the catalog's storage location, and DROP TABLE deletes the data files. An external table registers data from an existing cloud location, and dropping only removes the registration. Managed tables give stronger governance; external tables prevent accidental data loss.

Q: What is a checkpoint in stream processing?
A: 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.

Q: What is the difference between checkpointing and WAL (write-ahead log)?
A: 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.

Q: What is a stateful stream processing operator?
A: 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.

Q: What is exactly-once in Apache Flink and how is it achieved?
A: Apache Flink achieves end-to-end exactly-once through distributed checkpoints (Chandy-Lamport algorithm) and two-phase commit sinks. The source records offsets in the checkpoint, and sinks write to external systems transactionally (e.g., Kafka transactions, idempotent file writers). On failure, Flink restores state and offsets, then replays — output commits exactly once.

Q: What is a watermark in Flink?
A: A watermark in Flink is a time progress marker inserted into the event stream that says "no event with timestamp less than T will arrive after this point." Watermarks drive window evaluation: a window is closed when a watermark passes the window's end. Handling out-of-order events is impossible without watermarks.

Q: What is the difference between processing time and ingestion time?
A: 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.

Q: What is Apache Beam?
A: Apache Beam is a unified programming model for batch and stream processing that can be executed on multiple runners — Apache Flink, Spark, Google Cloud Dataflow, etc. You write the pipeline once using Beam's SDK (Java, Python, Go), and choose a runner for execution. Beam is popular for portability and migration between engines.

Q: What is a Dataflow template in Google Cloud?
A: A Dataflow template is a pre-built pipeline definition that can be launched repeatedly with different parameters. Templates are useful for standard ingestion, ETL, and export patterns (e.g., Pub/Sub to BigQuery). They encapsulate best-practice code so teams can run production pipelines without re-implementing them.

Q: What is reverse ETL?
A: 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.

Q: What is a feature store?
A: 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.

Q: What is a semantic layer?
A: 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.

Q: What is a metric store?
A: 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.

Q: What is the difference between a dashboard and a report?
A: 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.

Q: What is a slowly changing dimension Type 4 and Type 6?
A: SCD Type 4 uses a separate "history" mini-dimension to track fast-changing attributes (e.g., customer_segment, region_code) while the main dimension stays small. SCD Type 6 is a hybrid that combines Type 1 + Type 2 + Type 3: current value, historical rows, and a "current flag" on the latest version. Both are advanced patterns used in mature warehouses.

Q: How does an event-driven architecture differ from a request-response one?
A: 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.

Q: What is the outbox pattern?
A: 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.

Q: What is the saga pattern and why is it used?
A: 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.

Q: What is CQRS and when does it make sense?
A: 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.

Q: What is event sourcing?
A: 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.

Q: What is the difference between a database, a data warehouse, and a data lakehouse?
A: 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.

Q: What is Apache Druid?
A: 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.

Q: What is ClickHouse?
A: 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.

Q: What is Apache Pinot?
A: 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.

Q: What is TimescaleDB?
A: 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.

Q: What is InfluxDB?
A: 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.

Q: What is a data lake's medallion architecture?
A: The medallion architecture (popularized by Databricks) organizes a data lake into Bronze (raw ingested data), Silver (cleaned, deduplicated, conformed), and Gold (business-level aggregates ready for BI). Each layer applies progressively more transformation, providing a clear separation of concerns and traceability.

Q: What is the Bronze layer in a medallion architecture?
A: 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.

Q: What is the Silver layer in a medallion architecture?
A: The Silver layer contains cleaned, enriched, and conformed data: deduplicated, schema-enforced, joined to reference data, with nulls handled. It is the foundation for most downstream analytics and is typically the layer dbt models target. Silver answers "what is the current state of the business?"

Q: What is the Gold layer in a medallion architecture?
A: 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.

Q: What is data observability?
A: 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.

Q: What is data SLI and SLO?
A: 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.

Q: What is data downtime?
A: 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.

Q: What is a data contract?
A: A data contract is an explicit agreement between data producers and consumers about the schema, semantics, SLAs, and ownership of a dataset. It is versioned and enforced (e.g., via schema registry). Data contracts decouple teams, prevent breaking changes, and make producers accountable for quality — the equivalent of an API contract for data.

Q: What is a schema registry?
A: 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.

Q: What is Avro schema compatibility checking?
A: Schema registries like Confluent enforce compatibility levels: backward (new schema can read old data), forward (old schema can read new data), full (both), and none. Registering a breaking change (e.g., removing a required field) fails the registration. This protects consumers from unexpected runtime errors.

Q: What is Protobuf vs Avro for Kafka serialization?
A: Avro is a row-based, dynamically typed format with rich schema evolution. Protobuf (Protocol Buffers) is binary, strongly typed, more compact, and faster to encode, but schema evolution is more rigid. Choose Avro for flexibility and broad tooling; Protobuf for performance and typed APIs.

Q: What is change data capture (CDC) and what are the three main methods?
A: CDC propagates row-level changes from a source database to downstream systems. Main methods: (1) Log-based: read database transaction logs (Debezium, AWS DMS) — least intrusive; (2) Trigger-based: write change rows from triggers — adds DB load; (3) Timestamp-based: poll rows with updated_at — simple but misses deletes.

Q: What is Debezium?
A: 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.

Q: What is AWS DMS?
A: 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.

Q: What is a Slowly Changing Dimension Type 0?
A: 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.

Q: What is the difference between a transaction and a micro-batch?
A: 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.

Q: What is a streaming sink and what should you consider when choosing one?
A: A streaming sink is the destination where processed events are written — Kafka, S3, a database, etc. Considerations: delivery guarantees (exactly-once, at-least-once), throughput, latency, and schema stability. Sinks that support transactions (Kafka, Delta Lake) enable end-to-end exactly-once; others (HTTP, generic DB) usually require idempotency at the destination.

Q: What is watermarking in Spark Structured Streaming?
A: 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.

Q: What is a trigger in Spark Structured Streaming?
A: A trigger controls how often a streaming query produces micro-batches. Options: processingTime="10 seconds" (run every 10s), once=True (one batch, then stop), continuous="1 second" (experimental, low-latency continuous processing). The default is micro-batch; the right trigger depends on latency and cost.

Q: What is data deduplication in pipelines?
A: 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.

Q: What is the difference between deduplication and upsert?
A: 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.

Q: What is a watermark in event-time processing?
A: A watermark is a lower-bound timestamp on event-time progress, indicating that no events with a timestamp below the watermark are expected to arrive. Watermarks allow windowed aggregations to close deterministically despite late-arriving data. They are a key concept in Flink, Beam, and Spark Structured Streaming.

Q: What is the difference between a fact and a measure?
A: 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).

Q: What is an additive, semi-additive, and non-additive measure?
A: Additive: can be summed across all dimensions (e.g., revenue). Semi-additive: can be summed across some dimensions but not others (e.g., account_balance — sum across accounts but not time). Non-additive: cannot be summed meaningfully (e.g., ratios, percentages — must be recomputed from base measures). Choose aggregation functions accordingly.

Q: What is a grain in a fact table?
A: 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.

Q: What is a periodic snapshot fact table?
A: 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.

Q: What is an accumulating snapshot fact table?
A: 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.

Q: What is a transactional fact table?
A: 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.

Q: What is a bus matrix in Kimball methodology?
A: 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.

Q: What is a fact constellation schema?
A: A fact constellation (or galaxy schema) is a data model with multiple fact tables sharing dimension tables. For example, sales facts and inventory facts both join to dim_product and dim_date. It allows analysis across processes in a single query while keeping facts separate.

Q: What is the difference between a star schema and a galaxy schema?
A: A star schema has one fact table surrounded by dimension tables. A galaxy (fact constellation) schema has multiple fact tables sharing some conformed dimension tables. Galaxy schemas model multiple business processes; star schemas model a single process.