Data engineering Interview Questions and Answers

1. How do you perform capacity planning for a data pipeline?

Steps to perform capacity planning for a data pipeline

Analyze data volume, velocity, and variety.
Account for peak load scenarios.
Choose scalable storage solutions.
Test pipelines for stress and load.

2. How do you handle Data Skew in distributed systems?

Data Skew: Uneven distribution of data across partitions or nodes, causing performance bottlenecks.

Solutions:

Use hashing or range-based partitioning.
Balance data during preprocessing.
Use load balancing tools.

3. What is the difference between normalization and denormalization?

Normalization is the process of organizing data to reduce redundancy and improve consistency, like breaking tables into smaller ones.

Denormalization combines tables to optimize read performance, often at the cost of redundancy.

4. State the best practices for data pipeline security

The best practices to achieve security in data pipeline are:

Use encryption for data in transit (TLS) and at rest.
Implement access control and authentication mechanisms.
Mask or tokenize sensitive data.
Regularly audit and monitor pipelines for anomalies.

5. How do you decide between using SQL and NoSQL databases?

SQL is suitable for handling structured, relational data with ACID compliance. e.g., MySQL, PostgreSQL.

NoSQL is preferred for unstructured or semi-structured data, scalability, and flexibility. e.g., MongoDB, Cassandra.

6. How does Apache Kafka work?

Apache Kafka is a distributed event-streaming platform used for real-time data pipelines. It uses

Producers: Publish data to topics.
Consumers: Subscribe to topics to process data.
Brokers: Manage data storage and delivery

7. What are the differences between OLTP and OLAP?

OLTP – Online Transaction Processing

OLAP– Online Analytical Processing

OLTP: Optimized for transactional tasks like CRUD operations. Examples: Banking, e-commerce.

OLAP: Optimized for data analysis and querying large datasets. Examples: Business intelligence, reporting.

8. How does it Data Lake differ from a Data Warehouse?

Data Lake stores raw, unstructured and semi-structured data.

Example: Hadoop HDFS

Data Warehouse stores structured and processed data optimized for querying.

Example: Snowflake

Key Difference: Data lakes are schema-on-read; warehouses are schema-on-write.

9. What are the key components of a data pipeline?

Network requests and responses in Playwright are handled by methods like:

Data Ingestion: Collecting raw data from various sources.
Data Processing: Cleaning, transforming, and enriching data.
Data Storage: Saving data in warehouses or lakes.
Data Orchestration: Automating workflows using tools like Apache Airflow.
Data Monitoring: Ensuring pipeline reliability.

10. How does Apache Spark differ from Hadoop MapReduce?

Apache Spark: An open-source distributed computing system for fast, in-memory processing.

Difference: Spark processes data in-memory (faster), while Hadoop MapReduce uses disk-based processing.

11. How do you ensure data quality in a pipeline?

Validation Rules: Ensure completeness, consistency, and accuracy.
Data Profiling: Identify anomalies using tools like Apache Griffin.
Automated Testing: Unit and integration tests.
Monitoring Tools: Use systems like Great Expectations.

12. Explain the concept of Data Partitioning in distributed systems?

Data partitioning divides a dataset into smaller parts for parallel processing, improving performance. Methods include range-based, hash-based, and list-based partitioning.

13. What are the different types of NoSQL databases?

Types of NoSQL databases

Key-Value Stores: Redis, DynamoDB.
Document Stores: MongoDB, CouchDB.
Columnar Databases: Cassandra, HBase.
Graph Databases: Neo4j, ArangoDB.

14. What is the CAP theorem in distributed systems?

In a distributed system, the CAP theorem can achieve the following

Consistency: Every read receives the most recent write.
Availability: Every request receives a response.
Partition Tolerance: The system continues to function during network splits.

15. State the uses of distributed cache in data pipelines

A distributed cache stores frequently accessed data in-memory across multiple nodes, reducing latency.

Example: Redis and Memcached.

16. What is the purpose of a data orchestration tool?

Data orchestration tools manage workflows like Apache Airflow or Luigi by:

Scheduling tasks.
Handling dependencies.
Ensuring fault tolerance.
Providing monitoring and logging.

17. Explain the concept of Lambda Architecture.

Lambda Architecture combines:

Batch Layer: Processes large volumes of historical data (e.g., Hadoop).
Speed Layer: Handles real-time streaming data (e.g., Apache Storm).
Serving Layer: Combines results from both layers for querying.

18. If the data pipeline fails, and you need to identify the issue. How would you debug and resolve it?

Approach:

Check logs for error messages or exceptions.
Identify the failed component (e.g., ingestion, transformation, storage).
Validate data sources for schema changes or connectivity issues.
Rerun a small batch to reproduce the error.
Use monitoring tools (e.g., Prometheus, Datadog) to identify bottlenecks or failures.
Fix the root cause and test thoroughly before redeployment.

19. How to create a simple ETL pipeline?

Extract data from a CSV file.
Transform it by converting all column names to lowercase and filtering rows based on a condition.
Load the cleaned data into a database

Solution:

Use Python with libraries like pandas for transformation and SQLAlchemy for database loading.

20. If you are tasked with migrating a relational database to a cloud-based data warehouse. How would you approach this?

Assess the existing schema and data volume.
Choose a compatible cloud warehouse (e.g., Snowflake, BigQuery).
Design an ETL/ELT pipeline for data extraction, transformation, and loading.
Use tools like AWS DMS, Talend, or custom scripts for migration.
Validate data integrity and performance post-migration.

21. Write a SQL query to find the top 3 products by sales in each category.

Given a table sales_data(category, product, sales), retrieve the top 3 products by sales for each category.

Solution:

SELECT category, product, sales FROM ( SELECT category, product, sales, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank FROM sales_data ) subquery WHERE rank <= 3;

22. What are the advantages of columnar storage formats?

Efficient Compression: Similar data types are stored together, enabling better compression.
Faster Query Performance: Columnar storage reads only required columns, reducing I/O.
Optimized for Analytics: Ideal for aggregations and queries on large datasets.

23. How would you optimize a specific query on a large dataset which is running slowly?

Analyze the query execution plan to identify bottlenecks.
Use indexing on frequently queried columns.
Partition or cluster the table based on query patterns.
Avoid SELECT *. Retrieve only necessary columns.
Optimize joins using smaller, pre-aggregated datasets if possible.

24. State the important steps in creating a data pipeline using Apache Airflow.

Design a DAG that:

Downloads a file from an S3 bucket.
Processes the file using a Python script.
Uploads the transformed file back to another S3 bucket.

Solution:

Define the DAG using Airflow’s PythonOperator and S3Hook.

25. What is the difference between data replication and data sharding?

Data Replication: Copies the same data across multiple nodes for redundancy and fault tolerance.
Data Sharding: Splits data into smaller, distinct subsets across nodes to improve scalability and performance.

26. If a business user requests near real-time analytics, but the current pipeline is batch-based. How would you redesign it?

Integrate a streaming platform (e.g., Apache Kafka or AWS Kinesis) for real-time ingestion.
Use a stream processing framework (e.g., Apache Flink or Spark Streaming).
Design stateful aggregations and window-based processing.
Ensure the new architecture aligns with latency and scalability requirements.

27. How would you create a unified dataset that integrates multiple data sources with conflicting formats?

Understand the format and schema of each source.
Design a common schema or ontology.
Normalize and map data fields during the transformation stage.
Use tools like Apache NiFi or custom ETL scripts to process and harmonize data.

28. How do you handle unexpected data source’s schema changes which cause pipeline failures?

Implement schema validation tools like Apache Avro or Protobuf.
Set up alerts for schema changes.
Use schema evolution strategies, such as adding null-safe defaults for new fields.
Automate schema reconciliation or transformation steps in the pipeline.

29. How to optimize a slow Spark job.

A Spark job processing a large dataset is running slow.

Solution:

Use .repartition() to optimize partitioning.
Cache intermediate DataFrames if reused.
Avoid shuffling operations like wide transformations.
Use broadcast joins for smaller datasets.
Enable Spark UI to debug performance issues.

30. Design a schema for a NoSQL database.

Design a schema in MongoDB for a blogging platform in which each blog has a title, author, content, tags, and comments.

To design a schema for a NoSQL database, different approaches are needed based on the type of NoSQL database used.

Document Stores (e.g., MongoDB):

Organize the data into collections with JSON-like documents and embed the related data within a document for faster reads.

Key-Value Stores (e.g., Redis):

Use unique keys to map to values with simple structure which is ideal for caching and quick lookups.

Column-Family Stores (e.g., Cassandra):

Organize data into rows and columns within tables and distribute the data for horizontal scalability.

Graph Stores (e.g., Neo4j):

Nodes and relationships represent entities and connections which is Ideal for traversing relationships like social networks.

31. How to monitor and alert on data pipeline failures.

To monitor pipeline performance and failures, use monitoring tools like:

Apache Airflow,
Datadog,
Prometheus,
AWS Cloud watch

The various metrics available to track the performance of data pipeline are:

Execution time
Data throughput
Error rates
Latency

Set up alerts via email based on threshold violations and define retry policies to handle transient issues. To recover from failure, implement retries with exponential backoff and maintain checkpoints for resuming processing.

32. State the steps to Set up a Data Lake.

To ingest raw, structured, and semi-structured data into a Data Lake.
Use AWS S3 for storage and tools like AWS Glue for cataloging.
Use Parquet or ORC formats for better query performance.

33. How to Handle schema evolution in a pipeline.

If a data source introduces new columns, Update your pipeline to handle this without failures. Use a schema evolution framework like Apache Avro or Protobuf. To track schema versions and changes, use a schema registry. Implement transformations to map old schema versions to new ones. Use ETL jobs for migrating historical data to the new schema

34. State about Processing streaming data in real-time.

To consume messages from a Kafka topic, filter messages containing specific keywords, and write them to a database. Use frameworks and cloud services like:

Apache Kafka,
Apache Flink,
Apache Spark Streaming,
AWS Kinesis,
Azure Stream Analytics.

35. How to Automate data validation checks.

Create a script to validate that:

No missing values exist in critical columns.
Column data types are correct.

To perform automated validation of data quality rules, use tools like Great Expectations and Deequ. Define validation rules for data types, null values, ranges, and uniqueness.

36. if Data latency is increasing in a critical pipeline, How would you diagnose and fix this?

Monitor each stage of the pipeline to identify bottlenecks.
Check for hardware or network constraints.
Optimize transformation scripts (e.g., reduce I/O operations).
Scale resources dynamically using distributed systems.
Use caching or incremental processing to reduce redundancy.

37. If a data analyst complains about inconsistent results in a dashboard, How would you investigate?

Compare raw data against processed data to identify discrepancies.
Check for duplicate records or incomplete transformations.
Validate that time zones, date formats, and units are consistent.
Audit the pipeline for missed updates or overwrites.

38. Frame your architecture to build a pipeline that handles a high-velocity data stream while maintaining reliability.

Ingestion: Use a distributed system like Kafka for durability and scalability.
Processing: Use Apache Flink or Spark Streaming for real-time processing.
Storage: Write data to scalable storage like Amazon S3 or HBase.
Monitoring: Use tools like Grafana to monitor performance and errors.

39. What steps would you take to scale a pipeline to handle 10x the current data volume ?

Identify pipeline bottlenecks through profiling.

Migrate to distributed systems (e.g., Apache Spark or Hadoop) if not already in use. Optimize data partitioning and parallelism.
Leverage cloud-native solutions for dynamic scaling (e.g., AWS EMR or Google Dataflow).
Implement data compression to reduce storage and transfer overhead.

40. How do you handle duplicate data in a dataset?

Use unique constraints in relational databases to prevent duplicates.

Deduplicate data programmatically using tools like Python or SQL

SELECT DISTINCT * FROM table_name; Use hashing or surrogate keys to identify and remove duplicates.

41. If a third-party API you rely on for data ingestion introduces rate limits, causing delays in your pipeline. What would you do?

Batch Requests: Group API calls where possible to reduce the number of requests.
Implement Throttling: Use retry logic with exponential backoff to stay within limits.
Parallelize Safely: Distribute requests across multiple accounts or time intervals.
Cache Results: Cache frequently accessed data to minimize API dependency.

42. When would you use a surrogate key?

A surrogate key is a unique, system-generated identifier used as a primary key in a table. It is often used

when:

The natural key is large, complex, or subject to change.
You want to decouple database identifiers from business logic.
Example: Auto-incremented IDs in SQL tables.

43. What is the difference between horizontal and vertical scaling in databases?

Horizontal Scaling: Adding more machines to distribute the load (e.g., sharding in MongoDB). Suitable for large-scale distributed systems.

Vertical Scaling: Adding more resources (CPU, RAM) to a single machine. Easier but limited by hardware constraints.

44. How would you plan and execute the migration from on-premises Hadoop to a cloud-based platform ?

Assessment: Analyze existing Hadoop setup (data volume, workflows, applications).
Select Cloud Platform: Choose based on requirements (e.g., AWS EMR, Google Dataproc).
Data Migration: Use tools like AWS S3 Transfer or DistCp for moving data.
Pipeline Migration: Recreate workflows using cloud-native tools.
Validation: Ensure data accuracy and pipeline performance.
Monitor: Set up cloud monitoring tools to track performance post-migration.

45. If a third-party API relies on data ingestion that introduces rate limits, causing delays in your pipeline. What would you do?

Batch Requests: Group API calls where possible to reduce the number of requests.
Implement Throttling: Use retry logic with exponential backoff to stay within limits.
Parallelize Safely: Distribute requests across multiple accounts or time intervals.
Cache Results: Cache frequently accessed data to minimize API dependency.

46. If a data pipeline aggregates data every 5 minutes, latency has increased significantly. How would you troubleshoot and resolve this?

Check Source Systems: Ensure timely data arrival from sources.
Optimize Aggregation: Reduce expensive transformations and pre-aggregate data where possible.
Review Infrastructure: Add resources to underperforming nodes.
Parallel Processing: Use distributed processing to split workload.
Monitor: Use logs and metrics to identify the bottleneck stage.

47. How would you handle a client requesting a data dashboard to show insights based on data stored in multiple sources ?

Understand Requirements: Gather client expectations for metrics and visualizations.
Data Integration: Use an ETL/ELT process to unify data into a data warehouse.
Choose a Visualization Tool: Select tools like Power BI, Tableau, or Looker.
Optimize Queries: Create aggregated tables or materialized views for faster loading.
Iterate: Gather feedback and refine the dashboard.

48. What would you do if a third-party API relying on data ingestion introduces rate limits that cause delays in your pipeline ?

Batch Requests: Group API calls where possible to reduce the number of requests.
Implement Throttling: Use retry logic with exponential backoff to stay within limits.
Parallelize Safely: Distribute requests across multiple accounts or time intervals.
Cache Results: Cache frequently accessed data to minimize API dependency.

49. If your data processing jobs are becoming expensive on the cloud. How would you optimize costs?

Data Partitioning: Reduce the amount of data processed per job.
Storage Optimization: Use cost-efficient storage formats like Parquet or ORC.
Instance Scaling: Use spot instances or reserved capacity for compute-heavy tasks.
Job Scheduling: Run non-critical jobs during off-peak hours for discounted rates.
Monitor Usage: Leverage cloud-native tools to track and optimize resource consumption.

50. What are the common data formats used in data engineering?

Text-based formats: CSV, JSON, XML (easy to read, less efficient for large-scale processing).
Binary formats: Parquet, ORC, Avro (optimized for performance, support compression and schema evolution).
Key-Value formats: HBase, DynamoDB (ideal for real-time lookups).
Graph formats: GraphML, Neo4j (for representing relationships and networks

Request more information

Data engineering Interview Questions and Answers