Data Engineering Interview Questions and Answers

Data engineering is a critical field in today's data-driven world, focusing on building and maintaining the infrastructure and systems for collecting, storing and processing data. To succeed in this role, professionals must be proficient in various technical and conceptual areas.

1. What is Data Engineering?

Data Engineering is the discipline of designing, building and managing the infrastructure and systems that collect, store and process large volumes of data efficiently and reliably.

• It focuses on transforming raw data into a structured and accessible format so that it can be used by data analysts, data scientists and business intelligence tools to generate insights
• Involves working with data pipelines, ETL processes and storage systems.
• Ensures data is clean, reliable and in the right format for analysis.
• Often overlaps with cloud computing, big data frameworks and distributed systems.
• Plays a critical role in enabling machine learning and analytics at scale.

2. What are the roles and responsibilities of a Data Engineer?

A Data Engineer designs, builds and maintains the architecture and pipelines that move data from source systems to analytical platforms, while ensuring quality, security and performance.

• Designing and implementing data pipelines (ETL/ELT) for batch and real-time processing.
• Building and maintaining databases, data warehouses and data lakes.
• Ensuring data quality, integrity and governance.
• Working with big data technologies like Hadoop, Spark, Kafka and cloud services.
• Optimizing data workflows for performance and cost efficiency.
• Collaborating with data scientists, analysts and business stakeholders to understand data requirements.

3. What is the Data Engineering Lifecycle?

The Data Engineering Lifecycle describes the complete journey of data from its source to business consumption. It ensures that data is collected, processed and delivered in a reliable and scalable manner.

1. Data Generation: Data is generated from various sources such as Applications , Databases , IoT devices , APIs , Websites , Logs .
2. Data Ingestion: The generated data is collected and transferred into storage systems using batch or streaming pipelines.
3. Data Storage: The ingested data is stored in databases, data lakes or data warehouses depending on the use case.
4. Data Processing: Raw data is cleaned, transformed, validated and enriched using ETL or ELT pipelines.
5. Data Serving: The processed data is made available to BI dashboards, machine learning models and business applications.
6. Monitoring and Governance: The pipeline is continuously monitored to ensure data quality, security and reliability.

4. How is Data Engineering different from Data Science?

Data Engineering and Data Science are complementary fields in the data ecosystem.

Data Engineering

• Data Engineering focuses on designing, building, and maintaining data pipelines and infrastructure.
• It is responsible for collecting, transforming, storing, and processing large volumes of data.
• It ensures that data is clean, reliable, and readily available for analytics and machine learning.
• It works with distributed systems, databases, cloud platforms, and ETL/ELT pipelines.
• Common tools include Apache Spark, Hadoop, Kafka, Airflow, SQL, Snowflake, and cloud platforms such as AWS, Azure, or Google Cloud.
• Goal: Build scalable and efficient data infrastructure for analytics and machine learning.

Data Science

• Data Science focuses on extracting meaningful insights from data using statistics, machine learning, and data visualization.
• It involves data exploration, feature engineering, model building, evaluation, and deployment.
• It works with both structured and unstructured data.
• It helps organizations make data-driven decisions and build predictive models.
• Common tools include Python, R, TensorFlow, Scikit-learn, Pandas, and Jupyter Notebook.
• Goal: Analyze data and develop models that generate insights and predictions.

5. How is Data Engineering different from Data Analysis?

Data Engineering and Data Analysis are two important fields in the data ecosystem.

Data Engineering

• Data Engineering focuses on designing, building, and maintaining data pipelines and infrastructure.
• It is responsible for collecting, cleaning, transforming, and storing data from multiple sources.
• It ensures data is reliable, scalable, and readily available for analysis.
• It works with databases, ETL/ELT pipelines, cloud platforms, and distributed systems.
• Common tools include Apache Spark, Hadoop, Kafka, Airflow, SQL, Snowflake, and cloud platforms such as AWS, Azure, or Google Cloud.
• Goal: Build efficient and scalable data infrastructure for analytics and machine learning.

Data Analysis

• Data Analysis focuses on examining and interpreting data to identify patterns, trends, and business insights.
• It involves data cleaning, querying, visualization, and statistical analysis.
• It uses processed and structured data to answer business questions and support decision-making.
• It creates reports, dashboards, and visualizations to communicate findings.
• Common tools include SQL, Excel, Power BI, Tableau, Python, Pandas, and R.
• Goal: Extract meaningful insights from data to support business decisions.

6. What is a Data Pipeline?

A data pipeline is a series of processes that automatically move data from one or more source systems to a destination where it can be analyzed or used by applications. Components are:

• Data Sources
• Data Ingestion
• Data Validation
• Data Transformation
• Data Storage
• Monitoring

7. What is the difference between a Data Pipeline and an ETL Pipeline?

Data Engineering and Data Analysis are two important fields in the data ecosystem.

Data Engineering

• Data Engineering focuses on designing, building, and maintaining data pipelines and infrastructure.
• It is responsible for collecting, cleaning, transforming, and storing data from multiple sources.
• It ensures data is reliable, scalable, and readily available for analysis.
• It works with databases, ETL/ELT pipelines, cloud platforms, and distributed systems.
• Common tools include Apache Spark, Hadoop, Kafka, Airflow, SQL, Snowflake, and cloud platforms such as AWS, Azure, or Google Cloud.
• Goal: Build efficient and scalable data infrastructure for analytics and machine learning.

Data Analysis

• Data Analysis focuses on examining and interpreting data to identify patterns, trends, and business insights.
• It involves data cleaning, querying, visualization, and statistical analysis.
• It uses processed and structured data to answer business questions and support decision-making.
• It creates reports, dashboards, and visualizations to communicate findings.
• Common tools include SQL, Excel, Power BI, Tableau, Python, Pandas, and R.
• Goal: Extract meaningful insights from data to support business decisions.

8. What is the difference between structured data, unstructured data and semi-structured data?

Structured, Semi-Structured, and Unstructured data differ in how they are organized and stored.

Structured Data

• Structured data is organized in a predefined format using rows and columns.
• It follows a fixed schema, making it easy to store and query.
• It is typically stored in relational databases.
• It can be efficiently queried using SQL.
• Examples include customer records, banking transactions, employee databases, and sales records.
• Goal: Store highly organized data for efficient querying and analysis.

Semi-Structured Data

• Semi-Structured data does not follow a fixed relational schema but contains organizational elements such as tags, keys, or metadata.
• It has a flexible structure that can evolve over time.
• It is commonly stored in NoSQL databases or document stores.
• It is easier to process than unstructured data but less rigid than structured data.
• Examples include JSON, XML, HTML, emails, and log files.
• Goal: Store data with a flexible structure while preserving some organization.

Unstructured Data

• Unstructured data has no predefined schema or format.
• It cannot be easily stored in rows and columns.
• It often requires AI, machine learning, or natural language processing (NLP) techniques for analysis.
• It is typically stored in file systems, object storage, or data lakes.
• Examples include text documents, images, videos, audio files, PDFs, and social media posts.
• Goal: Store rich content that does not fit into a fixed structure.

9. Explain OLTP vs OLAP systems.

OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) are database systems designed for different purposes.

OLTP (Online Transaction Processing)

• OLTP systems are designed to process a large number of real-time transactions.
• They handle operations such as INSERT, UPDATE, DELETE, and SELECT.
• They store current, operational data and support day-to-day business activities.
• They prioritize fast query execution, data consistency, and ACID-compliant transactions.
• They are commonly used in banking systems, e-commerce websites, reservation systems, and ATM transactions.
• Goal: Efficiently process and manage daily business transactions.

OLAP (Online Analytical Processing)

• OLAP systems are designed to analyze large volumes of historical data.
• They perform complex queries involving aggregations, trends, and multidimensional analysis.
• They store historical and summarized data in data warehouses.
• They prioritize fast analytical queries over transaction processing.
• They are commonly used in business intelligence, reporting, dashboards, forecasting, and decision support systems.
• Goal: Support data analysis and strategic decision-making.

10. What is Data Ingestion?

Data ingestion is the process of collecting and importing data from various sources into a storage system, such as a data lake, data warehouse or database where it can be processed and analyzed.

• It is a critical first step in any data pipeline, enabling organizations to centralize and prepare data from multiple sources for downstream analytics, machine learning or reporting.
• Can involve structured, semi-structured or unstructured data.
• Ensures that data is reliably transferred from source to target systems without loss or corruption.
• Forms the foundation for ETL/ELT processes.
• Can be batch-based (periodic ingestion) or stream-based (real-time ingestion).

11. What are the Common Methods of Ingesting Data?

Data can be ingested using different methods depending on the source, volume and processing requirements. Choosing the right ingestion method ensures timely and efficient data availability for analytics and processing.

Batch Ingestion

• Data is collected and loaded at scheduled intervals (e.g., hourly, daily, or weekly).
• Processes large volumes of data in a single batch.
• More cost-effective than real-time ingestion.
• Suitable when immediate data availability is not required.
• Use cases: Daily sales reports, payroll processing, and data warehousing.

Real-Time (Streaming) Ingestion

• Data is ingested continuously as it is generated.
• Provides immediate access to the latest data with minimal latency.
• Enables instant processing and analysis.
• Requires streaming technologies such as Apache Kafka or Apache Flink.
• Use cases: Fraud detection, stock market monitoring, IoT sensors, and live dashboards.

Micro-Batch Ingestion

• Data is collected in small batches and processed at short, regular intervals (e.g., every few seconds or minutes).
• Offers a balance between batch and real-time processing.
• Provides lower latency than traditional batch ingestion.
• Reduces the overhead of continuous streaming.
• Use cases: Website clickstream analysis, log processing, and near real-time analytics.

Change Data Capture (CDC)

• Captures only the changes made to the source database, such as inserts, updates, and deletes.
• Transfers only modified records instead of the entire dataset.
• Reduces processing time and network overhead.
• Keeps source and target systems synchronized efficiently.
• Use cases: Database replication, ETL pipelines, and real-time database synchronization.

API-Based Ingestion

• Data is retrieved from external applications or services through APIs.
• Supports integration with cloud services, SaaS platforms, and third-party applications.
• Can be used for both batch and real-time data ingestion.
• Commonly uses REST or GraphQL APIs.
• Use cases: Weather data, social media feeds, payment gateways, and CRM systems.

File-Based Ingestion

• Data is imported from files such as CSV, JSON, XML, Excel, or Parquet.
• One of the simplest methods for exchanging data between systems.
• Can be scheduled or performed manually.
• Suitable for structured and semi-structured data.
• Use cases: Data migration, reporting, and ETL workflows.

12. What is the difference between Batch Processing and Stream Processing?

Batch Processing and Stream Processing are two approaches for processing data.

Batch Processing

• Batch Processing collects data over a period of time and processes it as a single batch.
• It is suitable for handling large volumes of historical data.
• Data is processed at scheduled intervals such as hourly, daily, or weekly.
• It has higher latency because results are available only after the batch is processed.
• It is commonly used for ETL jobs, payroll processing, billing systems, and monthly reports.
• Goal: Efficiently process large datasets where real-time results are not required.

Stream Processing

• Stream Processing processes data continuously as it is generated.
• It enables real-time or near real-time data analysis.
• Data is processed event by event with very low latency.
• It is ideal for applications requiring immediate insights and rapid decision-making.
• It is commonly implemented using technologies such as Apache Kafka, Apache Flink, and Apache Spark Streaming.
• Goal: Process continuous data streams and provide real-time insights.

13. What is the role of Apache Kafka in Data Ingestion

Apache Kafka is an open-source distributed event streaming platform used for building high-throughput, fault-tolerant and scalable real-time data pipelines.

• Publish-Subscribe Model: Producers publish data to Kafka topics and consumers subscribe to these topics to process the data.
• High Throughput & Scalability: Kafka can handle millions of events per second, making it ideal for large-scale data ingestion.
• Durability & Fault Tolerance: Messages are replicated across multiple brokers, preventing data loss.
• Decoupling of Systems: Kafka allows producers and consumers to operate independently, simplifying pipeline design.
• Real-Time Processing: Supports integration with frameworks like Apache Spark, Flink or Storm for real-time analytics.
• Use Cases: Clickstream tracking, IoT sensor ingestion, log aggregation, financial transaction streaming and monitoring pipelines.

14. What is CDC?

Change Data Capture (CDC) is a technique in data engineering that identifies and captures changes made to a data source—such as inserts, updates and deletes—and delivers them to a downstream system or data warehouse in near real-time.

• Real-time Updates: Captures changes as they happen, enabling near real-time analytics.
• Efficiency: Avoids full table scans or complete reloads, reducing network and compute overhead.
• Data Synchronization: Keeps data warehouses, data lakes and downstream systems up-to-date.
• Supports Multiple Sources: Can capture changes from databases, log files or message queues.

15. What advantages and challenges do you face while ingesting data from multiple sources?

Advantages

• Centralizes data from multiple sources for easier processing.
• Supports both batch and real-time data pipelines.
• Enables timely availability of data for analytics and reporting.
• Facilitates scalability and decoupling of data producers and consumers.
• Helps maintain data consistency and reliability across pipelines.

Challenges

• Handling heterogeneous data formats (structured, semi-structured, unstructured).
• Schema evolution or mismatches between source systems.
• Data quality issues (duplicates, missing or inconsistent data).
• Synchronizing data from sources with different update frequencies.
• Scalability issues with very high-volume or high-velocity sources.
• Ensuring security, compliance and privacy across multiple sources.
• Fault tolerance and error handling complexity in multi-source pipelines.

16. What is ETL? Explain each stage.

ETL (Extract, Transform, Load) is a core data engineering process used to move data from various source systems to a target system, such as a data warehouse or data lake, in a structured and usable format.

Stages

1. Extract:

• Involves retrieving data from multiple sources which can include relational databases, NoSQL databases, APIs, log files, cloud storage or streaming platforms.
• Can be performed as a full extraction (all data) or incremental extraction (only new or updated records).
• Handles heterogeneous data formats: structured (tables), semi-structured (JSON, XML) or unstructured (logs, text).

2. Transform:

• Cleanses and validates data to remove duplicates, inconsistencies and errors.
• Converts data into a standard, structured format suitable for the target system.
• Applies business logic and rules, including aggregations, joins, filtering, derivations and enrichment with additional data sources.

3. Load:

• Loads the transformed data into the target storage system (data warehouse, data lake or database).
• Can be a full load (replacing existing data) or an incremental load (appending or updating new records).
• Ensures the data is ready for querying, reporting or analytics without loss or corruption.

17. Difference between ETL and ELT.

ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) are data integration processes used to move data from source systems to data warehouses or data lakes. ETL transforms data before loading it into the target system, whereas ELT loads raw data first and performs transformations within the target system.

ETL (Extract, Transform, Load)

• ETL extracts data from multiple sources, transforms it into the required format, and then loads it into the target system.
• Data is cleaned, validated, and transformed before loading.
• It is commonly used with traditional data warehouses.
• It requires a separate ETL processing engine for transformations.
• It is suitable when only processed and validated data should be stored.
• Goal: Deliver clean and transformed data to the target system before loading.

ELT (Extract, Load, Transform)

• ELT extracts data from source systems, loads it directly into the target system, and performs transformations afterward.
• Raw data is stored first, allowing multiple transformation strategies later.
• It is commonly used with modern cloud data warehouses and data lakes.
• It leverages the processing power of the target system for transformations.
• It is suitable for handling large volumes of structured and unstructured data.
• Goal: Load data quickly and transform it within the target system when needed.

18. What are Data Quality Checks?

Data quality checks ensure that data entering a pipeline is accurate, complete, consistent and reliable.

Common quality checks include:

• Null value validation
• Duplicate detection
• Schema validation
• Data type validation
• Range validation
• Referential integrity checks
• Uniqueness checks
• Business rule validation

19. What is Idempotency in ETL Pipelines?

Idempotency means that executing the same ETL job multiple times produces the same final result without creating duplicate or inconsistent records.

• Prevents duplicate data.
• Makes pipelines fault tolerant.
• Simplifies recovery after failures.
• Ensures consistent analytics.

Example: if yesterday's ETL pipeline accidentally runs twice, customer records should still appear only once in the target table.

20. What are some tools used for ETL?

1. Apache NiFi

• Open-source tool for automating and managing data flows.
• Supports real-time streaming and batch data ingestion.
• Provides visual interface for building pipelines.

2. Talend

• Comprehensive ETL and data integration platform.
• Supports cloud, on-premises and hybrid deployments.
• Provides components for data quality, profiling and transformation.

3. Informatica PowerCenter

• Enterprise-grade ETL tool widely used in large organizations.
• Supports complex transformations, scheduling and monitoring.
• Provides robust data governance and metadata management.

4. Apache Airflow

• Open-source workflow orchestration tool.
• Used to schedule and manage ETL pipelines as Directed Acyclic Graphs (DAGs).
• Can integrate with multiple data sources and processing frameworks.

5. dbt (Data Build Tool)

• Focused on the transform part of ELT.
• Works on top of data warehouses like Snowflake, BigQuery or Redshift.
• Allows SQL-based transformations and version-controlled analytics pipelines.

21. What are the different types of data storage systems?

Data storage systems can be broadly categorized based on their structure, purpose and use case:

1. Relational Databases (RDBMS)

• Structured data stored in tables with rows and columns.
• Supports SQL for querying.
• Examples: MySQL, PostgreSQL ,Oracle, SQL Server.
• Use Case: OLTP systems, transactional data, structured reporting.

2. NoSQL Databases

Designed for unstructured or semi-structured data.

Types include:

• Document Stores: MongoDB, Couchbase
• Key-Value Stores: Redis, DynamoDB
• Columnar Stores: Cassandra, HBase
• Graph Databases: Neo4j, JanusGraph

Use Case: Big data applications, real-time analytics, flexible schema.

3. Data Warehouses

• Centralized storage for structured and processed data optimized for analytics.
• Supports complex queries and reporting.
• Examples: Snowflake, Amazon Redshift, Google BigQuery.
• Use Case: OLAP systems, business intelligence, dashboards.

4. Data Lakes

• Stores raw, structured, semi-structured and unstructured data.
• Supports large-scale storage and processing with distributed systems.
• Examples: AWS S3 + Glue, Azure Data Lake, Hadoop HDFS.
• Use Case: Big data processing, machine learning, archival storage.

5. Object Storage Systems

1. Stores data as objects with metadata; scalable and cost-effective.
2. Examples: Amazon S3, Google Cloud Storage, MinIO.
3. Use Case: Large files, backups, unstructured data storage.

6. In-Memory Databases / Caches

• Stores data in RAM for ultra-fast access.
• Examples: Redis, Memcached, Apache Ignite.
• Use Case: Real-time analytics, session stores, caching.

7. File Systems / Distributed File Systems

• Stores files in a hierarchical structure or distributed manner.
• Examples: HDFS (Hadoop), NFS, GlusterFS.
• Use Case: Big data storage, batch processing, archival storage.

22. What is the difference between a Database, Data Warehouse and Data Lake?

A Database, Data Warehouse, and Data Lake are all used to store data, but they serve different purposes.

Database

• A Database is designed to store and manage current operational data.
• It primarily stores structured data in tables.
• It supports fast read and write operations for day-to-day transactions.
• It is optimized for OLTP (Online Transaction Processing) workloads.
• Common examples include MySQL, PostgreSQL, Oracle Database, and SQL Server.
• Goal: Efficiently manage operational data and support business transactions.

Data Warehouse

• A Data Warehouse is a centralized repository that stores cleaned, integrated, and historical data from multiple sources.
• It primarily stores structured data optimized for analytics.
• It is designed for complex queries, reporting, and business intelligence.
• It supports OLAP (Online Analytical Processing) workloads.
• Common examples include Snowflake, Amazon Redshift, Google BigQuery, and Azure Synapse.
• Goal: Support business intelligence, reporting, and analytical queries.

Data Lake

• A Data Lake is a centralized storage system that stores raw data in its original format.
• It can store structured, semi-structured, and unstructured data.
• Data is stored without requiring a predefined schema.
• It is suitable for big data processing, machine learning, and advanced analytics.
• Common examples include Amazon S3, Azure Data Lake Storage, and Hadoop HDFS.
• Goal: Store large volumes of diverse data for future processing and analysis.

23. What is a Data Lake?

A Data Lake is a centralized repository that stores large volumes of raw data in its native format until it is needed for analysis. Unlike traditional databases, a data lake can store structured, semi-structured and unstructured data without requiring a predefined schema.

Advantages

• Low-cost storage
• Highly scalable
• Supports machine learning
• Schema-on-read
• Stores all data formats

Common Technologies

• Amazon S3
• Azure Data Lake Storage
• Hadoop HDFS

24. What is the architecture of a typical Data Warehouse?

A Data Warehouse (DW) is a centralized system designed to store, manage and analyze structured data from multiple sources. Its architecture is generally layered to support efficient extraction, transformation, storage and reporting.

Layers:

1. Data Sources Layer

• Includes operational databases, transactional systems (OLTP), flat files, APIs and external data sources.
• Data can be structured, semi-structured or external reference data.

2. ETL / Data Integration Layer

• Extracts data from source systems, transforms it (cleansing, aggregation, standardization) and loads it into the warehouse.
• Handles data validation, deduplication and quality checks.

3. Staging Layer

• Temporary storage area where raw data is stored before transformation.
• Allows testing and validation of ETL processes without impacting the main warehouse.

4. Data Storage Layer (Core Data Warehouse)

• Stores cleaned and transformed data, typically in a schema-based structure (star or snowflake schema).
• Can include fact tables (quantitative data) and dimension tables (descriptive attributes).
• Optimized for fast querying and reporting.

5. Data Access / Presentation Layer

• Provides interfaces for business intelligence tools, dashboards, ad-hoc queries and reporting.
• Supports OLAP operations, analytical queries and drill-down analysis.

6. Metadata & Management Layer

• Maintains information about the data (metadata), schema definitions, lineage and audit trails.
• Helps in monitoring, governance and ensuring data quality.

7. Optional Advanced Layers

• Data Marts: Subsets of the warehouse tailored for specific business units.
• OLAP Cubes: Pre-aggregated multidimensional structures for faster analytics.
• Data Governance & Security: Ensures compliance, access control and encryption.

25. What is a Data Mart?

• A Data Mart is a smaller, department-specific subset of a data warehouse designed for a particular business function such as Sales, Finance or Marketing.
• Instead of accessing the complete enterprise warehouse, teams use data marts to improve query performance and simplify reporting.

Types

• Dependent Data Mart
• Independent Data Mart
• Hybrid Data Mart

26. What are some popular data warehouse technologies?

Some of the widely used data warehouse platforms in data engineering:

1. Snowflake

• Cloud-based data warehouse with separation of storage and compute.
• Supports structured and semi-structured data (JSON, Parquet, Avro).
• Scalable, pay-as-you-go and fully managed.

2. Amazon Redshift

• Fully managed cloud data warehouse on AWS.
• Columnar storage and massively parallel processing (MPP) for fast analytics.
• Integrates well with AWS ecosystem (S3, Glue, Athena).

3. Google BigQuery

• Serverless, cloud-native data warehouse on GCP.
• Supports SQL-based queries on massive datasets with automatic scaling.
• Optimized for analytics, machine learning integration and real-time querying.

4. Microsoft Azure Synapse Analytics

• Cloud-based analytics service combining data warehousing and big data analytics.
• Integrates with Azure ecosystem (Data Factory, Power BI).
• Supports both structured and semi-structured data.

5. Teradata

• Enterprise-grade data warehouse solution.
• High-performance analytics for large-scale structured data.
• Offers both on-premises and cloud deployment options.

6. Oracle Exadata / Oracle Autonomous Data Warehouse

• Enterprise solution for structured data analytics.
• Optimized for high-performance OLAP queries.
• Supports automation and advanced analytics features.

7. IBM Db2 Warehouse

• Columnar data warehouse supporting hybrid cloud deployment.
• Optimized for analytics, reporting and AI workloads.

27. What is columnar storage? Why is it important in data warehouses?

Columnar storage is a method of storing data in a database where data is stored column by column instead of row by row.

Importance:

• Faster Query Performance: Only the relevant columns are read during queries, reducing I/O and speeding up analytical operations.
• Better Compression: Similar data types in a column can be compressed more efficiently, saving storage space.
• Efficient Aggregations: Columnar storage enables faster execution of aggregate functions like SUM, AVG, MIN, MAX.
• Optimized for Analytics: Ideal for OLAP workloads where operations are column-centric (e.g., sales totals, counts).
• Reduces Resource Usage: Less memory and CPU are needed for queries that only touch a subset of columns.

28. Difference between SQL and NoSQL databases.

SQL and NoSQL are two types of database management systems used to store and manage data.

SQL Databases

• SQL (Structured Query Language) databases store data in tables consisting of rows and columns.
• They follow a fixed schema, meaning the structure of the data must be defined before storing it.
• They support ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring reliable transactions.
• They are ideal for applications requiring complex queries, joins, and strong data consistency.
• Common examples include MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server.
• Goal: Store and manage structured data with strong consistency and reliable transactions.

NoSQL Databases

• NoSQL (Not Only SQL) databases use non-relational data models such as document, key-value, column-family, or graph.
• They support dynamic schemas, allowing the data structure to change over time.
• They can store structured, semi-structured, and unstructured data.
• They are designed for horizontal scaling and handling large volumes of distributed data.
• Common examples include MongoDB (Document), Redis (Key-Value), Cassandra (Column-Family), and Neo4j (Graph).
• Goal: Efficiently manage large-scale and flexible data with high scalability.

29. Explain the different types of NoSQL databases.

NoSQL databases are non-relational databases designed to handle large volumes of structured, semi-structured and unstructured data.

1. Key-Value Databases

Key-value databases store data as a collection of unique key-value pairs, where each key acts as an identifier for retrieving its associated value.

• Simple key-value storage model.
• High performance and low latency.
• Easy to scale horizontally.
• Suitable for caching and session management.

Examples: Redis , Amazon DynamoDB , Riak

2. Document Databases

Document databases store data as flexible documents, typically in JSON, BSON, or XML formats. Each document can have its own structure, making it easy to handle evolving or varying data schemas.

• Flexible schema.
• Stores nested and hierarchical data.
• Easy to modify document structure.
• Supports indexing and querying of document fields.

Examples: MongoDB ,Couchbase ,Amazon DocumentDB

3. Column-Family Databases

Column-family databases organize data into columns instead of rows. Related columns are grouped into column families, allowing efficient storage and retrieval of large datasets

• Stores data in column families.
• Highly scalable.
• Efficient for large analytical workloads.
• Optimized for distributed environments.

Examples: Apache Cassandra ,Apache HBase ,ScyllaDB

4. Graph Databases

Graph databases represent data as nodes (entities) and edges (relationships). They are specifically designed to efficiently store and query highly connected data where relationships are as important as the data itself.

• Relationship-focused data model.
• Fast graph traversal.
• Efficient for complex relationship queries.
• Supports connected data analysis.

Examples: Neo4j ,Amazon Neptune ,JanusGraph

30. What is data modeling in Data Engineering?

Data modeling in data engineering is the process of structuring and organizing data to define how it will be stored, accessed and managed in databases or data warehouses.

• It involves designing schemas, relationships and constraints to ensure data integrity, efficiency and usability for analytics and applications.
• Defines how data is stored in tables, columns and relationships.
• Ensures data integrity and avoids redundancy.
• Supports query optimization and faster data retrieval.
• Helps in designing fact and dimension tables for data warehouses.

31. Explain the different types of Data Models.

Data modeling is performed at different levels to represent business requirements, system design and database implementation.

1. Conceptual Data Model

A Conceptual Data Model provides a high-level representation of the data and its relationships from a business perspective. It focuses on what data is required rather than how it will be stored.

• High-level business view.
• Identifies entities and relationships.
• Independent of any database technology.
• Used during requirement gathering.

2. Logical Data Model

A Logical Data Model adds more detail by defining attributes, relationships and constraints while remaining independent of a specific database.

• Defines entities and attributes.
• Specifies primary and foreign keys.
• Defines relationships between entities.
• Database-independent.

3. Physical Data Model

A Physical Data Model represents how the database will actually be implemented in a specific DBMS.

• Database-specific.
• Defines tables, indexes and partitions.
• Includes data types and storage details.
• Optimized for performance.

32. Explain Star Schema vs Snowflake Schema.

Star Schema and Snowflake Schema are data modeling techniques used in data warehouses.

Star Schema

• Star Schema consists of a central fact table surrounded by multiple dimension tables.
• Dimension tables are denormalized, meaning data redundancy may exist.
• It has a simple structure that is easy to design and understand.
• It requires fewer joins, resulting in faster query performance.
• It is commonly used in business intelligence (BI) and reporting applications.
• Goal: Improve query performance and simplify data analysis.

Snowflake Schema

• Snowflake Schema also consists of a central fact table, but the dimension tables are further divided into multiple related tables.
• Dimension tables are normalized, reducing data redundancy.
• It has a more complex structure than the Star Schema.
• It requires more joins, which may slightly reduce query performance.
• It is suitable when minimizing storage and maintaining data consistency are important.
• Goal: Reduce data redundancy while maintaining an organized data model.

33. What are facts and dimensions in a data warehouse?

In a data warehouse, facts and dimensions are the core components of a schema. Fact tables store quantitative data or metrics about business processes while dimension tables store descriptive attributes that provide context to those facts.

Fact Tables:

• Contain measurable, quantitative data like sales amount order quantity or revenue.
• Usually have foreign keys referencing dimension tables.
• Often large in size, storing millions of transactional records.
• Examples: Sales Fact orders Fact, Revenue Fact.

Dimension Tables:

• Contain descriptive attributes that provide context to facts.
• Used for filtering, grouping and labeling data in analysis.
• Smaller in size compared to fact tables.
• Examples: Customer, Product, Time, Location.

34. What is Metadata in Data Engineering?

Metadata is data about data. It provides information that describes the structure, source, ownership and characteristics of data, making it easier to discover, understand and manage datasets.

• Business Metadata: Describes the business meaning of data.
• Technical Metadata: Describes how data is stored and processed.
• Operational Metadata: Describes how data is processed and managed.

35. What is difference between Normalization and Denormalization?

Normalization and Denormalization are database design techniques used to organize data.

Normalization

• Normalization is the process of organizing data into multiple related tables to eliminate redundancy.
• It reduces data duplication and improves data consistency.
• It ensures that each piece of information is stored only once.
• It involves applying normal forms such as 1NF, 2NF, 3NF, and BCNF.
• It may require multiple table joins, which can slow down complex queries.
• It is commonly used in OLTP (Online Transaction Processing) systems.
• Goal: Minimize redundancy and maintain data integrity.

Denormalization

• Denormalization is the process of combining related tables by introducing controlled redundancy.
• It reduces the need for complex joins, improving query performance.
• It increases data duplication, which can lead to higher storage requirements.
• It is commonly used in OLAP (Online Analytical Processing) systems and data warehouses.
• Updating data may become more complex because the same information can exist in multiple places.
• Goal: Improve query performance and reduce query execution time.

36. What are primary keys and foreign keys?

Primary Keys and Foreign Keys are database constraints used to uniquely identify records and establish relationships between tables.

Primary Key

A Primary Key is a column or set of columns that uniquely identifies each record in a table.

• Must be unique.
• Cannot contain NULL values.
• Only one primary key per table.

Foreign Key

A Foreign Key is a column that references the Primary Key of another table, creating a relationship between the two tables.

• Can contain duplicate values.
• Maintains referential integrity.
• Links related tables.

37. What are Surrogate keys and Natural keys?

A key uniquely identifies records in a database table. Keys can be classified as Natural Keys or Surrogate Keys based on how they are generated.

Natural Key

A Natural Key is derived from existing business data and has a real-world meaning.

Advantages

• Already available in the business domain.
• Easy to understand.

Limitations

• Business values may change.
• Can be long or composite.
• May expose sensitive information.

Surrogate Key

A Surrogate Key is an artificially generated unique identifier with no business meaning.

Advantages

• Stable and never changes.
• Improves join performance.
• Simplifies relationships.
• Commonly used in data warehouses.

38. What is Partitioning ?

Partitioning is a database optimization technique in which a large table or dataset is divided into smaller, manageable parts called partitions.Partitioning improves query performance, scalability, and maintenance of large databases.

How Partitioning Works

• A large table is split into multiple partitions based on a partitioning key, such as date, region, or customer ID.
• Each partition stores a subset of the data.
• Queries access only the relevant partitions instead of scanning the entire table, reducing query execution time.
• Applications continue to query the table as if it were a single table.

39. What is the difference between Partitioning and Bucketing?

Partitioning and Bucketing are data organization techniques used in databases and big data frameworks such as Hive and Spark.

Partitioning

• Partitioning divides a large dataset into smaller partitions based on the value of one or more columns.
• Each partition contains data with a specific value or range of values.
• It reduces the amount of data scanned during queries by reading only the relevant partitions.
• It is best suited for columns with a limited number of distinct values, such as date, year, or region.
• Commonly used in data warehouses and distributed file systems.
• Goal: Improve query performance by reducing data scanning.

Bucketing

• Bucketing divides data into a fixed number of buckets using a hash function on a selected column.
• Rows with the same hash value are placed in the same bucket.
• It distributes data evenly across buckets, reducing data skew.
• It improves the performance of joins, aggregations, and sampling.
• It is useful when the partition column has a high number of distinct values.
• Goal: Evenly distribute data and optimize joins and query execution.

40. What is Data Lineage in Data Engineering?

Data Lineage is the process of tracking the complete lifecycle of data as it moves through different systems, from its source to its final destination. It records where the data originates, how it is transformed and where it is ultimately consumed.

Components

1. Data Source

The original location from which data is collected, such as relational databases, APIs, files, IoT devices or streaming platforms.

2. Data Transformation

The operations performed on the data before it reaches the target system. These may include filtering, aggregation, validation, enrichment, joins and format conversion.

3. Data Movement

The transfer of data between different storage and processing systems, such as databases, data lakes, data warehouses and cloud platforms.

4. Data Destination

The final location where the processed data is stored or consumed, such as dashboards, reports, machine learning models or business applications.

41. What is Big Data?

• Big Data refers to extremely large and complex datasets that traditional data processing tools cannot handle efficiently.
• It encompasses data from diverse sources, often in real-time and requires specialized technologies for storage, processing and analysis. \
• Big Data is used to uncover insights, trends and patterns that drive business decisions, predictive analytics and machine learning applications.

42. What are the 6 V’s of Big Data?

6 V’s of Big Data are:

• Volume – The massive amount of data generated every second.
• Velocity – The speed at which data is created, collected and processed.
• Variety – Different types of data: structured, semi-structured, unstructured.
• Veracity – The accuracy, quality and trustworthiness of the data.
• Value – The usefulness of the data in driving business insights or decisions.
• Variability – The inconsistency or changing nature of data flows, such as seasonal trends or context-dependent meaning.

43. What is Hadoop?

Hadoop is an open-source framework designed for distributed storage and processing of large datasets across clusters of computers.

• It allows organizations to store massive volumes of structured, semi-structured and unstructured data and process it efficiently using parallel computation.
• Based on distributed computing, dividing tasks across multiple nodes.
• Handles large-scale data that cannot be processed on a single machine.
• Fault-tolerant: automatically replicates data across nodes to prevent loss.
• Supports integration with tools like Hive, Pig, Spark and HBase for analytics and querying.

44. Explain Hadoop Architecture.

Hadoop follows a master-worker architecture, where one or more master nodes manage the cluster while multiple worker nodes store and process data.

1. Hadoop Distributed File System (HDFS)

• HDFS stores large files by dividing them into smaller blocks and distributing them across multiple machines.
• It ensures high availability by replicating each data block across different nodes.

2. YARN (Yet Another Resource Negotiator)

YARN manages cluster resources and schedules jobs submitted by users.

Its responsibilities include:

• Resource allocation.
• Job scheduling.
• Monitoring application execution.
• Managing cluster workloads.

3. MapReduce

MapReduce is the distributed processing framework responsible for executing computations on data stored in HDFS.

It processes data in two phases:

• Map Phase
• Reduce Phase

4. Hadoop Common

• Hadoop Common contains the shared libraries, utilities and APIs required by all Hadoop modules.
• It provides the basic functionality needed for communication between Hadoop components.

45. What is HDFS?

The Hadoop Distributed File System (HDFS) is the distributed storage layer of Hadoop that is designed to store and manage very large datasets across multiple machines.

Features

• Distributed storage across multiple machines.
• Automatic block replication for fault tolerance.
• High throughput for large datasets.
• Horizontal scalability by adding more nodes.
• Designed for processing very large files.
• Supports batch processing workloads.

46. Explain the architecture of HDFS.

HDFS is the primary storage system of Hadoop, designed to store very large files across multiple machines in a distributed and fault-tolerant manner.

1. NameNode:

• The master node that manages metadata (file names, directories, block locations).
• Keeps track of which blocks are stored on which DataNodes.
• Handles file system operations like opening, closing and renaming files.

2. DataNode:

• Worker nodes that store the actual data blocks.
• Handle read and write requests from clients.
• Periodically send heartbeats and block reports to the NameNode to confirm their status.

3. Secondary NameNode (Checkpoint Node):

• Periodically merges the fsimage and edit logs from the NameNode for backup.
• Not a failover node, but helps reduce NameNode workload.

4. HDFS Blocks:

• Files are split into blocks (default 128 MB or 256 MB) and stored across DataNodes.
• Each block is replicated (default 3 copies) for fault tolerance.

5. Client Interaction:

• Clients first contact the NameNode to get block locations.
• Then read/write directly to the DataNodes for efficient data transfer.

6. Fault Tolerance:

• Automatic replication ensures data availability even if nodes fail.
• NameNode monitors cluster health to maintain replication levels.

47. What is the role of NameNode and DataNode in HDFS?

1. NameNode: It is the master node of HDFS that manages the metadata of the file system. It keeps track of the structure of directories, file names, permissions and the mapping of files to the data blocks stored across the cluster.

Roles:

• Manages metadata like file names, directories and block locations.
• Handles namespace operations such as create, delete, rename and open/close files.
• Monitors DataNode health through heartbeats and block reports.
• Coordinates replication of blocks to maintain fault tolerance.
• Ensures high availability with checkpointing (via Secondary NameNode).

2. DataNode: A DataNode is a worker node in HDFS responsible for storing the actual data blocks. DataNodes handle read and write requests from clients and report their status to the NameNode.

Roles:

• Stores actual data blocks on local storage.
• Responds to read and write requests from clients.
• Sends heartbeats and block reports to the NameNode.
• Performs block replication and deletion as instructed by the NameNode.
• Ensures data availability and fault tolerance within the cluster.

48. What is MapReduce?

• MapReduce is a distributed programming model used in Hadoop for processing large datasets across multiple machines in parallel.
• It divides a large processing task into smaller sub-tasks, executes them simultaneously and combines the results to produce the final output.

Phases of MapReduce

1. Map Phase

• The input data is divided into smaller splits.
• Each Mapper processes a split independently and generates intermediate key-value pairs.

2. Shuffle and Sort Phase

• The framework groups intermediate data based on keys.
• Records having the same key are sent to the same Reducer.

3. Reduce Phase

• The Reducer processes grouped data and generates the final output.

49. Explain the MapReduce Workflow.

The MapReduce workflow describes how Hadoop processes large datasets from input to final output using parallel computation.

• Step 1: Input Data Input files are stored in HDFS. The files are divided into smaller input splits.
• Step 2: Map Phase Each Mapper processes one input split independently. It converts raw input into intermediate key-value pairs.
• Step 3: Shuffle and Sort The Hadoop framework groups records having the same key.
• Step 4: Reduce Phase Reducers aggregate values belonging to each key.
• Step 5: Final Output The processed output is stored back into HDFS.

50. What are the advantages and limitations of Hadoop?

Apache Hadoop is widely used for processing and storing massive datasets because of its distributed architecture. However, like any technology, it has both advantages and limitations.

Advantages

• Scalability: Easily scales by adding more machines to the cluster.
• Fault Tolerance: Data replication ensures availability even if nodes fail.
• Cost-Effective: Runs on inexpensive commodity hardware.
• Distributed Processing: Processes data in parallel, reducing execution time.
• Handles Multiple Data Types: Supports structured, semi-structured and unstructured data.
• Open Source: Freely available with a large community and ecosystem.

Limitations

• High Latency: Not suitable for real-time data processing.
• Small File Problem: Managing a large number of small files reduces HDFS performance.
• Complex Development: Writing MapReduce programs can be time-consuming.
• Resource Intensive: Requires significant storage and computing resources for large clusters.
• Limited Support for Iterative Processing: MapReduce is inefficient for machine learning and graph processing, where repeated computations are common.
• Security Configuration: Requires additional setup for authentication, authorization and encryption.

51. How does NameNode communicate with DataNodes?

In HDFS, the NameNode and DataNodes maintain continuous communication to ensure the reliability, consistency and availability of data across the cluster.

Communication Mechanism:

1. Heartbeats: DataNodes send regular heartbeat signals to the NameNode to indicate they are alive. If a heartbeat is missed, the NameNode considers the node dead and triggers replication of its blocks elsewhere.

2. Block Reports: DataNodes periodically send detailed reports of all blocks they store. The NameNode uses these reports to maintain an accurate mapping of files to blocks and their replicas.

3. Commands from NameNode:

• Block replication: Ensures the required number of replicas exist for fault tolerance.
• Block deletion: Removes obsolete or extra block copies.
• Data rebalancing: Moves blocks to maintain even storage distribution across nodes.

4. Client Interaction: Clients first request block locations from the NameNode, then read/write directly with DataNodes for efficient data transfer.

52. What is YARN? Explain its architecture.

YARN (Yet Another Resource Negotiator) is Hadoop’s cluster resource management layer that separates resource management and job scheduling from the data processing components like MapReduce.

1. ResourceManager (RM):

The master daemon responsible for resource allocation across all applications in the cluster. Consists of two main components:

• Scheduler: Allocates resources to applications based on constraints (queues, capacities or fair sharing).
• ApplicationManager: Manages application lifecycle, negotiates containers for execution.

2. NodeManager (NM):

• Runs on each worker node and monitors container resource usage (CPU, memory, disk).
• Reports node status and resource availability to the ResourceManager.
• Launches and manages containers assigned to its node.

3. ApplicationMaster (AM):

• Unique for each application/job.
• Negotiates resources with the ResourceManager.
• Monitors execution of tasks, handles failures and communicates progress back to the client.

4. Containers:

• A unit of resource allocation on a node (CPU, memory, disk).
• Tasks run inside containers managed by the NodeManager.

5. Client:

• Submits applications to the ResourceManager.
• Monitors job progress and retrieves results from the ApplicationMaster.

53. What is the CAP Theorem? Explain with examples.

The CAP Theorem (also known as Brewer’s theorem) states that in a distributed system, it is impossible to simultaneously guarantee all three of the following properties: Consistency, Availability and Partition Tolerance. A system can only provide two out of the three at any given time.

1. Consistency (C):

• Every read from the database returns the most recent and correct data.
• This means all nodes in the distributed system see the same data at the same time.
• Example: In a banking system, if you transfer money, your balance should update instantly and consistently on all servers.

2. Availability (A):

• The system guarantees that every request receives a response, whether it is successful or not.
• It does not guarantee that the data returned is the latest, only that the system is responsive.
• Example: In an e-commerce site, even if some servers are down, you can still search for products — though the latest stock count may not be shown.

3. Partition Tolerance (P):

• The system continues to operate even if there are network failures that prevent communication between nodes.
• Distributed systems are built across multiple machines and networks, so partitions are unavoidable in practice.
• Example: If one data center cannot talk to another due to a network issue, the system still works with the available nodes.

Examples:

• CP Systems (Consistency + Partition Tolerance): Prioritize accurate data over availability. During network issues, the system may refuse requests. Example: *HBase, Zookeeper, MongoDB (strict mode).
• AP Systems (Availability + Partition Tolerance): Prioritize availability. The system always responds, but data may be slightly outdated. Example: Cassandra, DynamoDB, CouchDB.
• CA Systems (Consistency + Availability): Work well without partitions (usually single-node systems). Data is accurate and always available, but not fault-tolerant. Example: Traditional RDBMS like MySQL, PostgreSQL (non-distributed).

54. What is replication in distributed systems? Why is it important?

Replication in distributed systems is the process of storing copies of the same data across multiple nodes or servers. This ensures that data remains available, reliable and fault-tolerant even if some nodes fail or go offline.

Importance:

• Fault Tolerance: If one node fails, other replicas can serve the data, preventing data loss.
• High Availability: Replication ensures the system can respond to requests even during node or network failures.
• Load Balancing: Read requests can be served by multiple replicas, improving system performance and reducing latency.
• Data Reliability: Multiple copies protect against disk failures, corruption or accidental deletion.
• Disaster Recovery: Replicated data across geographically distributed nodes helps recover from site-level disasters.

55. What are consistency models in distributed systems?

Consistency models define how quickly updates made on one node become visible to other nodes in a distributed system. Different models provide different trade-offs between consistency, availability and performance.

1. Strong Consistency:

• Guarantees that all reads always return the most recent write.
• Every node sees the same data at the same time.
• Ensures accuracy and correctness, but may reduce availability or increase latency in distributed systems.
• Example: HBase (in strong consistency mode), traditional RDBMS clusters.

2. Eventual Consistency:

• Guarantees that all updates will eventually propagate to all nodes, but reads may return stale data temporarily.
• Provides high availability and partition tolerance at the cost of immediate consistency.
• Example: Cassandra, DynamoDB, CouchDB.

3. Causal Consistency:

• Ensures that related updates are seen in the same order by all nodes.
• Example: If a user posts a comment and then edits it, all nodes see the edit after the original comment.

4. Read-your-writes Consistency:

• Guarantees that a node always sees its own writes immediately.
• Useful in applications where a user expects their changes to be visible instantly on the same session.

56. What is Sharding in Distributed Databases?

Sharding is a horizontal partitioning technique that distributes large datasets across multiple database servers called shards. Each shard stores only a subset of the data, allowing the database to scale horizontally and handle increasing workloads efficiently.

• Range-Based Sharding: Data is divided based on ranges of values (e.g., customer IDs 1–1000 on one shard, 1001–2000 on another).
• Hash-Based Sharding: A hash function determines the shard where data is stored.
• Directory-Based Sharding: A lookup table maps records to specific shards.

57. What is Data Skew? How do you handle it?

Data skew occurs when data is unevenly distributed across partitions or processing nodes. Some nodes receive significantly more data than others, causing certain tasks to take much longer and slowing down the overall pipeline.

Causes

• Uneven distribution of key values.
• Highly frequent records for a particular key.
• Poor partitioning strategy.
• Imbalanced joins.

Techniques to Handle Data Skew

• Use better partitioning keys.
• Apply salting to distribute heavily repeated keys.
• Broadcast smaller tables during joins.
• Repartition or rebalance datasets.
• Filter or preprocess skewed data before processing.

58. How is data engineering done in the cloud vs on-premises?

Cloud and On-Premises are two deployment models for data engineering.

Cloud Data Engineering

• Cloud Data Engineering uses cloud platforms such as AWS, Azure, or Google Cloud to build and manage data pipelines.
• Infrastructure is managed by the cloud provider, reducing maintenance effort.
• Resources can be scaled up or down on demand.
• It follows a pay-as-you-go pricing model.
• It provides built-in services for data storage, processing, security, and analytics.
• Goal: Build scalable, flexible, and cost-efficient data pipelines without managing physical infrastructure.

On-Premises Data Engineering

• On-Premises Data Engineering runs on servers and hardware owned and maintained by the organization.
• The organization is responsible for managing infrastructure, updates, backups, and security.
• Scaling requires purchasing and installing additional hardware.
• It involves a higher upfront investment but offers complete control over the environment.
• It is commonly used in industries with strict security or regulatory requirements.
• Goal: Build and manage data pipelines with full control over infrastructure and data.

59. What is serverless data engineering?

Serverless data engineering is an approach where cloud providers automatically manage the infrastructure required for data processing. Developers focus on building pipelines, while provisioning, scaling and maintenance of servers are handled by the cloud platform.

Characteristics

• No server management.
• Automatic scaling.
• Pay only for resources consumed.
• Event-driven execution.
• High availability.

60. What are containers (Docker) and Kubernetes used for in Data Engineering?

Containers, such as Docker, are lightweight, portable environments that package an application along with its dependencies, libraries and configuration.

Use Cases:

• Isolation & Portability: Containers package ETL scripts, Spark jobs or data APIs to run consistently across development, testing and production.
• Scalability: Kubernetes can scale data pipelines automatically based on workload, ensuring efficient resource usage.
• Fault Tolerance: Kubernetes monitors containers, restarts failed jobs and distributes workloads across nodes.
• Simplified Deployment: Deploy complex workflows like Spark clusters, Kafka consumers or Airflow pipelines easily with reproducible environments.
• Resource Management: Kubernetes efficiently manages CPU, memory and storage for multiple concurrent data processing jobs.
• Integration: Works well with cloud-native services, data lakes and warehouse platforms for end-to-end data engineering pipelines.

61. How do you handle schema evolution in a data pipeline?

Schema evolution is the ability of a data pipeline to adapt to changes in the structure of incoming data over time, such as added or removed columns, changes in data types or nested field modifications.

Ways to Handle Schema Evolution:

• Schema-on-Read: Use flexible data formats like Parquet, Avro or ORC which store schema with the data and allow the pipeline to interpret the schema at read time.
• Versioned Schemas: Maintain multiple schema versions in a schema registry (e.g., Avro Schema Registry) so applications can handle different versions without breaking.
• Default Values and Null Handling: Introduce default values or allow nulls for newly added fields to maintain backward compatibility.
• Automated Schema Detection: Use tools like Apache Spark’s schema inference or AWS Glue crawlers to detect and adapt to schema changes automatically.
• Backward and Forward Compatibility: Ensure that both older and newer schema versions can be read and processed without errors.
• Testing and Monitoring: Continuously validate schemas with unit tests and data quality checks to detect incompatible changes early.

62. What is the Lambda Architecture in Big Data?

The Lambda Architecture is a design pattern for building robust, fault-tolerant and scalable Big Data systems that can handle both batch and real-time data processing.

Components of Lambda Architecture

Batch Layer:

• Stores raw, immutable data in a distributed storage system (e.g., HDFS, S3).
• Performs batch processing to compute accurate, comprehensive views of the data.
• Example technologies: Hadoop MapReduce, Spark Batch.

Speed (or Real-Time) Layer:

• Processes incoming streaming data to provide low-latency views.
• Handles data that has not yet been processed by the batch layer.
• Example technologies: Apache Spark Streaming, Apache Flink, Apache Kafka Streams.

Serving Layer:

• Combines outputs from the batch and speed layers.
• Provides queryable views for analytics, dashboards or reporting.
• Example technologies: HBase, Cassandra, Druid.

63. What is the Kappa Architecture?

The Kappa Architecture is a simplified alternative to the Lambda Architecture for processing large-scale data. Unlike Lambda which has separate batch and speed layers, Kappa uses a single stream-processing pipeline to handle both real-time and historical data.

Key Components:

1. Stream Processing Layer:

• All data, whether new or historical, is processed as a continuous stream.
• Provides real-time analytics while still being able to recompute historical data by replaying logs.
• Example technologies: Apache Kafka, Apache Flink, Apache Spark Structured Streaming.

2. Serving Layer:

• Stores the processed results for querying and analytics.
• Example technologies: Cassandra, Elasticsearch, Druid.

3. Data Sources:

• Data can come from logs, message queues, databases or IoT devices.
• Historical data can be re-ingested into the stream for recomputation.

64. Explain Lambda vs Kappa Architecture.

Lambda Architecture and Kappa Architecture are two data processing architectures used to handle large-scale data.

Lambda Architecture

• Lambda Architecture processes data using two separate layers: a Batch Layer and a Speed (Streaming) Layer.
• The Batch Layer processes all historical data to produce accurate results.
• The Speed Layer processes real-time data to provide low-latency updates.
• A Serving Layer combines outputs from both layers for querying.
• It provides high accuracy but is more complex because the same logic often needs to be implemented twice.
• Goal: Combine batch and real-time processing to achieve both accuracy and low latency.

Kappa Architecture

• Kappa Architecture processes all data through a single stream processing pipeline.
• Both historical and new data are processed using the same streaming engine.
• Historical data can be reprocessed by replaying events from the event log.
• It has a simpler architecture with lower maintenance overhead.
• It is ideal for applications where streaming systems can handle both real-time and historical processing.
• Goal: Simplify data processing using a single streaming pipeline.

65. Explain NAS vs DAS in Hadoop.

NAS (Network Attached Storage) and DAS (Direct Attached Storage) are two storage architectures.

DAS (Direct Attached Storage)

• DAS refers to storage devices that are directly connected to a server or node.
• Each Hadoop DataNode stores its data on locally attached disks.
• It provides high read and write performance because data is accessed locally.
• Hadoop uses data locality, where computation is moved to the node containing the data, reducing network traffic.
• It is the recommended storage architecture for Hadoop clusters.
• Goal: Provide fast local storage for distributed data processing.

NAS (Network Attached Storage)

• NAS is a centralized storage system connected to multiple servers through a network.
• Data is accessed over the network rather than from local disks.
• It simplifies storage management because data is stored in a central location.
• It may create network bottlenecks when many nodes access data simultaneously.
• Hadoop generally does not recommend NAS for storing HDFS data because it reduces the benefits of data locality.
• Goal: Provide centralized file storage that can be shared across multiple systems.

66. How would you design a data pipeline that handles unexpected IoT data, supports near real-time dashboards and maintains historical trends?

Pipeline will look like this:

1. Data Ingestion: Use Kafka or Kinesis to handle high-throughput streaming data.

2. Data Validation & Cleaning: Implement real-time schema validation, handle malformed records and store bad data in a separate location (Dead Letter Queue).

3. Processing: Use Spark Streaming, Flink or serverless processing to transform and aggregate data.

4. Storage:

• Raw Data: Store in a data lake (HDFS, S3) for historical analysis.
• Processed Data: Store in a data warehouse (Redshift, BigQuery) for dashboards.

5. Schema Evolution: Use schema-on-read with formats like Parquet or Avro; maintain schema registry.

6. Fault Tolerance: Replicate data in HDFS or Kafka; implement retry mechanisms.

7. Monitoring & Alerting: Track pipeline health, latency and errors.

67 How would you migrate a petabyte-scale on-premises Hadoop data warehouse to the cloud while ensuring minimal downtime and data consistency?

Steps to Handle the Migration:

• Choose Migration Strategy: Lift-and-shift for entire datasets or incremental/hybrid migration to keep analytics running during the move.
• Data Transfer Tools: Use high-throughput tools like AWS Snowball, GCP Transfer Service or managed ETL pipelines for bulk data transfer.
• Incremental Sync: Implement Change Data Capture (CDC) to continuously sync new and updated records during migration.
• Validation & Testing: Compare source and target data to ensure accuracy and completeness; perform spot checks and automated validation.
• Cost Optimization: Store rarely accessed historical data in cold storage and leverage auto-scaling cloud services for active workloads.
• Monitoring & Rollback: Track migration progress, handle failures and maintain a rollback plan in case of critical issues.

68. What is Apache Spark? Explain its architecture.

Spark is a distributed, in-memory processing engine faster than Hadoop MapReduce because it avoids writing intermediate results to disk.

• Driver: runs the main program, builds the DAG, schedules tasks.
• Cluster Manager: allocates resources (YARN, Kubernetes, or standalone).
• Executors: worker processes that run tasks and cache data.
• Tasks: smallest unit of work, one per data partition.

69. RDD vs DataFrame vs Dataset.

RDD (Resilient Distributed Dataset), DataFrame, and Dataset are the three core data abstractions in Apache Spark.

RDD (Resilient Distributed Dataset)

• RDD is Spark's fundamental distributed data structure.
• It stores data as a collection of objects distributed across cluster nodes.
• It does not have a predefined schema.
• It provides full control over low-level transformations and distributed processing.
• It is fault-tolerant through lineage information.
• Goal: Perform low-level distributed data processing with maximum flexibility.

DataFrame

• A DataFrame is a distributed collection of data organized into rows and named columns.
• It has a predefined schema, similar to a table in a relational database.
• It is optimized using Spark's Catalyst Optimizer and Tungsten Execution Engine, resulting in better performance than RDDs.
• It supports SQL queries and high-level APIs.
• It is the most commonly used abstraction for data processing in Spark.
• Goal: Efficiently process structured and semi-structured data with high performance.

Dataset

• A Dataset is a strongly typed distributed collection of objects.
• It combines the optimization benefits of DataFrames with the type safety of RDDs.
• It provides compile-time type checking, reducing runtime errors.
• It is mainly available in Java and Scala (Python users typically work with DataFrames).
• It is suitable for applications that require both performance and type safety.
• Goal: Provide optimized distributed processing with compile-time type safety.

70. Transformations vs Actions — why laziness matters.

In Apache Spark, Transformations and Actions are the two main types of operations performed on RDDs, DataFrames, and Datasets.

Transformations

• Transformations create a new RDD, DataFrame, or Dataset from an existing one.
• They are lazy, meaning Spark records the operations but does not execute them immediately.
• Multiple transformations can be chained together.
• Spark builds a Directed Acyclic Graph (DAG) of transformations before execution.
• Common transformations include map(), filter(), flatMap(), groupBy(), join(), and select().
• Goal: Define a sequence of data processing steps without executing them immediately.

Actions

• Actions trigger the execution of all pending transformations.
• They return a result to the driver program or write data to external storage.
• Spark executes the optimized execution plan only when an action is called.
• Actions produce outputs such as values, files, or database records.
• Common actions include count(), collect(), first(), show(), reduce(), save(), and write().
• Goal: Execute the computation and produce the final result.

71. Difference between repartition() vs coalesce().

repartition() and coalesce() are Spark operations used to change the number of partitions in an RDD, DataFrame, or Dataset.

repartition()

• repartition() increases or decreases the number of partitions.
• It performs a full shuffle to evenly redistribute data across partitions.
• It helps balance data and avoid data skew.
• It is more expensive because of network communication during shuffling.
• It is commonly used before joins or parallel processing when balanced partitions are required.
• Goal: Evenly redistribute data across a specified number of partitions.

coalesce()

• coalesce() is mainly used to decrease the number of partitions.
• It minimizes or avoids data shuffling, making it faster than repartition().
• It merges existing partitions instead of redistributing all data.
• It is efficient when reducing partitions after filtering or processing data.
• It may result in uneven partition sizes because data is not fully redistributed.
• Goal: Reduce the number of partitions efficiently with minimal overhead.

72. How to handle Data Skew in Spark.

Skew is one or a few partitions hold disproportionately more data, causing a straggler task.

• Enable AQE (Spark 3.0+ auto-splits skewed partitions).
• Salting: add a random suffix to the skewed key, spread across more partitions, aggregate in two stages.
• Broadcast join if one side is small enough for executor memory.
• Isolate and process the skewed key separately, then union back.

73. Difference between .cache() vs .persist().

cache() and persist() are Spark techniques used to store intermediate datasets in memory or disk so they can be reused without recomputation.

cache()

• cache() stores the dataset in memory using Spark's default storage level (MEMORY_AND_DISK in modern Spark versions).
• It avoids recomputing the dataset when it is used multiple times.
• It is simple to use because no storage level needs to be specified.
• If memory is insufficient, Spark stores the remaining partitions on disk (default behavior).
• It is best suited when the dataset fits in memory and is accessed repeatedly.
• Goal: Improve performance by caching frequently used datasets with the default storage level.

persist()

• persist() also stores the dataset for reuse but allows you to specify the storage level.
• It supports storage options such as MEMORY_ONLY, MEMORY_AND_DISK, DISK_ONLY, and others.
• It provides greater flexibility for managing memory and storage.
• It is useful when datasets are too large to fit entirely in memory or when disk storage is preferred.
• It is commonly used in large-scale Spark applications with specific storage requirements.
• Goal: Optimize performance by choosing the most appropriate storage level.

74. What is a Data Lakehouse?

Combines a data lake's low-cost, flexible storage with a warehouse's transactional reliability and schema enforcement.

• Enabled by open table formats: Delta Lake, Apache Iceberg, Apache Hudi — add ACID transactions, versioning/time-travel, and schema enforcement on top of files in S3/ADLS/GCS.
• Removes the need for two separate systems (lake for ML/raw data + warehouse for BI).
• Examples: Databricks (Delta Lake), Snowflake (Iceberg), AWS Athena + Iceberg.

75. What are Slowly Changing Dimensions (SCD)? Explain the types.

Slowly Changing Dimensions define how a data warehouse handles changes to dimension attributes over time for example, when a customer's address or a product's category changes.

• Type 0: fixed, never changes.
• Type 1: overwrite, no history.
• Type 2: new row + effective/end dates or current flag — full history, most commonly used.
• Type 3: extra column for previous value only.
• Type 4: separate history table.
• Type 6: hybrid of 1+2+3.

76. Difference between ACID vs BASE.

ACID and BASE are two models that define how databases manage data consistency and availability.

ACID

• ACID stands for Atomicity, Consistency, Isolation, and Durability.
• It ensures that transactions are processed reliably and completely.
• It provides strong consistency, meaning all users see the same data after a transaction is committed.
• It is commonly used in relational databases.
• It is ideal for applications where data accuracy and integrity are critical, such as banking and financial systems.
• Goal: Ensure reliable and consistent transaction processing.

BASE

• BASE stands for Basically Available, Soft State, and Eventual Consistency.
• It prioritizes high availability and scalability over immediate consistency.
• Data may not be immediately consistent across all nodes but becomes consistent over time (eventual consistency).
• It is commonly used in distributed NoSQL databases.
• It is suitable for applications that handle massive amounts of data and can tolerate temporary inconsistencies.
• Goal: Maximize availability and scalability in distributed systems.

77. Apache Airflow — Explain Operators, Sensors and DAGs.

Apache Airflow is an open-source workflow orchestration tool used to schedule, monitor and manage data pipelines as code.

• AG: pipeline as tasks + dependencies, no cycles.
• Operator: what a task does (PythonOperator, BashOperator, PostgresOperator).
• Sensor: waits for a condition before proceeding (FileSensor, ExternalTaskSensor).
• Backfilling: rerunning a DAG for past dates.
• Retries & SLAs: auto-retry on failure; SLA alerts if a task runs too long.
• XComs: passing small data between tasks.

78. What is dbt (Data Build Tool) and why has it become standard for transformations?

dbt is a transformation tool that lets data teams write the "T" in ELT as version-controlled, testable SQL models that run directly inside the data warehouse (Snowflake, BigQuery, Redshift, Databricks).

• Enforces staging → intermediate → mart layering.
• Built-in testing (uniqueness, not-null, referential integrity) directly on SQL models.
• Version-controlled, documented, with lineage tracking.

79. Explain Windowing in Stream Processing.

Windowing is the technique of grouping unbounded stream data into finite chunks so that aggregations (counts, sums, averages) can be computed over them.

• Tumbling Window: Fixed-size, non-overlapping windows (e.g., every 5 minutes, each event belongs to exactly one window).
• Sliding Window: Fixed-size, overlapping windows that advance by a smaller step than the window size (e.g., a 10-minute window sliding every 5 minutes).
• Session Window: Groups events by activity gaps — a session ends after a defined period of inactivity (e.g., a user session ends after 10 minutes with no events).

80. What are Watermarks? How do you handle late-arriving data?

A watermark is a timestamp threshold in event-time stream processing that tells the system "we don't expect to see data older than this anymore." Records that arrive after the watermark has passed are considered late.

• Grace Periods: Define how long the system waits before finalizing a window's result.
• Watermarks: Drop or side-output records that arrive after the watermark threshold.
• Append + Compact Pattern: Allow late-arriving records to update a previously finalized result rather than being discarded outright.
• Communicating Eventual Accuracy: Dashboards may show a number that ticks up slightly after the fact as late data trickles in — this trade-off should be communicated to stakeholders rather than hidden.

81. Explain Delivery Semantics: At-Most-Once, At-Least-Once and Exactly-Once.

Delivery semantics define how messages are delivered from a producer to a consumer in distributed systems and stream processing platforms such as Apache Kafka.

At-Most-Once

• A message is delivered zero or one time.
• Messages are never duplicated, but some messages may be lost if a failure occurs.
• It provides the lowest latency and highest throughput.
• It is suitable for applications where occasional data loss is acceptable.
• Goal: Deliver messages quickly without duplicates, even if some messages are lost.

At-Least-Once

• A message is delivered one or more times.
• Messages are not lost, but duplicates may occur if retries happen.
• Consumers should implement deduplication or idempotent processing.
• It provides higher reliability than At-Most-Once.
• Goal: Ensure every message is delivered, even if duplicates occur.

Exactly-Once

• A message is delivered exactly one time.
• Messages are neither lost nor duplicated.
• It requires additional coordination, transactional processing, or idempotent producers and consumers.
• It provides the highest data consistency but with additional overhead.
• Goal: Guarantee accurate message delivery without loss or duplication.

82. What is Indexing? When can it hurt performance?

An index is a data structure typically a B-tree, or a hash index for exact-match lookups that allows a database to find rows without scanning the entire table.

• Clustered Index: Determines the physical storage order of the table; a table can have only one.
• Non-Clustered Index: A separate structure pointing back to the row; a table can have many.
• Trade-off: Indexes speed up reads (SELECT, WHERE, JOIN) but slow down writes (INSERT, UPDATE, DELETE).

83. Compare File Formats: Parquet vs ORC vs Avro vs CSV/JSON.

Parquet, ORC, Avro, CSV, and JSON are commonly used file formats in big data and data engineering.

Parquet

• Parquet is a columnar storage file format.
• It provides high compression and efficient storage.
• It reads only the required columns, making analytical queries faster.
• It supports schema evolution and nested data structures.
• It is widely used in Apache Spark, Hive, and cloud data warehouses.
• Goal: Optimize storage and query performance for analytical workloads.

ORC (Optimized Row Columnar)

• ORC is also a columnar storage file format.
• It offers excellent compression and indexing capabilities.
• It is highly optimized for Hive and Hadoop ecosystems.
• It provides faster query execution for large datasets.
• It supports advanced optimizations such as predicate pushdown.
• Goal: Deliver high-performance storage and querying in Hadoop-based systems.

Avro

• Avro is a row-based binary file format.
• It stores both data and schema together, making schema evolution easier.
• It is optimized for data serialization and data exchange.
• It is commonly used with Apache Kafka and streaming applications.
• It is not as efficient as columnar formats for analytical queries.
• Goal: Efficiently serialize and exchange data across distributed systems.

CSV / JSON

• CSV and JSON are text-based file formats.
• CSV stores tabular data using comma-separated values, while JSON stores data as key-value pairs.
• They are easy to read and widely supported by different applications.
• They consume more storage and are slower to process than binary formats.
• They do not provide the compression or query optimizations available in Parquet or ORC.
• Goal: Enable simple data storage and exchange between different systems.

84. What is Data Governance? Explain PII, Masking and Encryption.

Data governance encompasses the policies, processes and controls that ensure data is accurate, secure, compliant and properly managed across its lifecycle.

• PII (Personally Identifiable Information): Data that can identify an individual (name, SSN, email, etc.) — requires special handling under regulations like GDPR, HIPAA and CCPA.
• Data Masking: Obscuring sensitive data while keeping it usable for testing/analytics — e.g., replacing digits in a credit card number with "X" except the last 4.
• Anonymization vs Pseudonymization: Anonymization irreversibly removes identifying information; pseudonymization replaces it with a reversible token.
• Encryption: At-rest and in-transit a standard baseline requirement.
• Access Control & Governance: Role-based access control (RBAC), audit logs and data catalogs (e.g., Collibra, Alation, AWS Glue Data Catalog) to track ownership and access.

85. What is Data Mesh?

Data Mesh is a decentralized approach to data architecture where, instead of one central team owning a monolithic lake/warehouse, individual domain teams own and serve their own data as a product.

• Domain-Oriented Ownership: Each business domain (e.g., "orders," "payments") owns its own data pipelines and quality.
• Data as a Product: Domain data is treated like a product — documented, discoverable and held to SLAs.
• Self-Serve Data Infrastructure Platform: A central platform team provides tooling so domains can build/serve data independently.
• Federated Computational Governance: Global standards (security, compliance) enforced consistently but applied in a decentralized way.