SELECT e.*\nFROM employees e\nLEFT JOIN payroll p ON e.emp_id = p.emp_id\nWHERE p.emp_id IS NULL;<\/code><\/pre>\n\n\n\nThis returns employees who exist in employees but not in payroll.<\/p>\n\n\n\n
Note:<\/strong> Interview questions for data engineer roles often include topics on databases, ETL processes, big data tools, cloud platforms, and data modelling.<\/p>\n\n\n\n<\/span>Data Engineer Interview Questions for Experienced<\/span><\/h2>\n\n\n\nThese data engineer interview questions and answers are designed for experienced professionals.<\/p>\n\n\n\n
<\/span>8. How would you handle schema evolution in a data pipeline?<\/span><\/h3>\n\n\n\nI would use schema registries like Apache Avro or Confluent to track versions. Backward compatibility is important, so I would allow new fields with defaults and avoid deleting existing ones. Testing changes in staging before production is a must.<\/p>\n\n\n\n
<\/span>9. How do you handle data skew in distributed systems?<\/span><\/h3>\n\n\n\nData skew happens when some partitions have more data than others. I can fix it by salting keys, using custom partitioning, or rebalancing data before processing. Monitoring is key to spotting skew early.<\/p>\n\n\n\n
<\/span>10. Describe differences between batch processing and real-time processing.<\/span><\/h3>\n\n\n\nBatch processing handles large data sets in scheduled intervals, using tools like Apache Spark. Real-time processing works on streams as they arrive, with tools like Apache Flink or Kafka Streams. Batch is good for historical analysis. Real-time is best for instant actions.<\/p>\n\n\n\n
<\/span>11. Explain how you would design a scalable data pipeline.<\/span><\/h3>\n\n\n\nI would design it with modular components \u2013 data ingestion, processing, and storage. Using distributed systems like Kafka and Spark helps with scalability. I would also add monitoring, retries, and alerts to handle failures gracefully.<\/p>\n\n\n\n
<\/span>12. What is the CAP theorem and its relevance to distributed systems?<\/span><\/h3>\n\n\n\nThe CAP theorem says a distributed system can only guarantee two of Consistency, Availability, and Partition Tolerance at the same time. In practice, systems trade off based on use case. For example, Cassandra prioritizes availability and partition tolerance.<\/p>\n\n\n\n
<\/span>13. What is the toughest part of being a data engineer, and how have you handled it?<\/span><\/h3>\n\n\n\nFor me, the toughest part is balancing quick delivery with long-term maintainability. I have learned to communicate timelines clearly and push back when a rushed fix might cause future problems. Good documentation and clean design have saved me many times.<\/p>\n\n\n\n
<\/span>Python Interview Questions for Data Engineer<\/span><\/h2>\n\n\n\nLet’s go through the commonly asked Python data engineer interview questions and answers.<\/p>\n\n\n\n
<\/span>14. How would you implement an incremental update in an ETL pipeline?<\/span><\/h3>\n\n\n\nTrack a high-water mark (e.g., last_updated).<\/p>\n\n\n\n
Pull only rows where updated_at > watermark.<\/p>\n\n\n\n
Write idempotent upserts.<\/p>\n\n\n\n
On Spark\/Delta\/Iceberg, use MERGE INTO with a unique key.<\/p>\n\n\n\n
For deletes, consume CDC logs (Debezium\/Kafka) and apply tombstones.<\/p>\n\n\n\n
Store the new watermark after a successful run.<\/p>\n\n\n\n
Add retries and exactly-once semantics via transactional sinks.<\/p>\n\n\n\n
<\/span>15. Which Python libraries do you use for data processing?<\/span><\/h3>\n\n\n\n\n- Core: pandas, NumPy.<\/li>\n\n\n\n
- Big data: PySpark, Polars, Dask.<\/li>\n\n\n\n
- Files\/formats: pyarrow, fastparquet, orjson.<\/li>\n\n\n\n
- Streams: confluent-kafka, faust.<\/li>\n\n\n\n
- Databases: sqlalchemy, psycopg2, pyodbc.<\/li>\n\n\n\n
- Cloud: boto3, google-cloud-bigquery, azure-storage-blob.<\/li>\n\n\n\n
- Scheduling: Airflow, Prefect, Dagster.<\/li>\n<\/ul>\n\n\n\n
<\/span>16. How would you automate a data pipeline using Python or PySpark?<\/span><\/h3>\n\n\n\nDefine tasks as small, stateless functions.<\/p>\n\n\n\n
Orchestrate with Airflow DAGs or Prefect flows.<\/p>\n\n\n\n
Add retries, timeouts, and SLA alerts.<\/p>\n\n\n\n
Use task-level caching and checkpoints.<\/p>\n\n\n\n
Package code as a Docker image.<\/p>\n\n\n\n
For Spark jobs, submit via spark-submit from the scheduler, pass configs per env, and write metrics to Prometheus or CloudWatch.<\/p>\n\n\n\n
Version code and schemas together.<\/p>\n\n\n\n
<\/span>17. What Python tools or libraries do you use for validation and profiling?<\/span><\/h3>\n\n\n\nValidation: Great Expectations, Pandera (DataFrame schemas), Pydantic for configs.<\/p>\n\n\n\n
Spark: Deequ (via PyDeequ). Monitoring rules with Soda Core.<\/p>\n\n\n\n
Profiling: ydata-profiling (pandas-profiling), skimpy, sweetviz. I add row-count checks, null thresholds, domain rules, and schema drift alerts.<\/p>\n\n\n\n
<\/span>18. How would you handle duplicate records in Python processing?<\/span><\/h3>\n\n\n\nPandas: use keys plus a tie-breaker.<\/p>\n\n\n\n
df = (df.sort_values('updated_at')\n .drop_duplicates(subset=['id'], keep='last'))<\/code><\/pre>\n\n\n\nPySpark: pick the latest per key with a window.<\/p>\n\n\n\n
from pyspark.sql import functions as F, Window\nw = Window.partitionBy('id').orderBy(F.col('updated_at').desc())\ndedup = df.withColumn('rn', F.row_number().over(w)).filter('rn = 1').drop('rn')<\/code><\/pre>\n\n\n\nFor streams, keep a TTL cache of seen keys or use stateful dedup in Spark\/Flink.<\/p>\n\n\n\n
Note:<\/strong> Data engineer python interview questions are very common and often focus on coding efficiency, data manipulation, and integrating Python with big data tools.<\/p>\n\n\n\n<\/span>SQL Interview Questions for Data Engineer<\/span><\/h2>\n\n\n\nSQL interview questions for data engineer are often scenario-based. So, here are some important SQL scenario based interview questions for data engineer to help you prepare.<\/p>\n\n\n\n
<\/span>19. You need to calculate the running total of sales for each customer in chronological order. How would you write this SQL query?<\/span><\/h3>\n\n\n\nUse the SUM() window function with PARTITION BY and ORDER BY:<\/p>\n\n\n\n
SELECT\n customer_id,\n order_date,\n amount,\n SUM(amount) OVER (\n PARTITION BY customer_id\n ORDER BY order_date\n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n ) AS running_total\nFROM sales;<\/code><\/pre>\n\n\n\nThis query gives a cumulative sum of sales per customer over time.<\/p>\n\n\n\n
<\/span>20. A customer data table contains possible duplicate entries based on email and phone number. How would you write a SQL query to detect and flag these duplicates?<\/span><\/h3>\n\n\n\nNormalize, find dup keys, then mark extra rows.<\/p>\n\n\n\n
WITH norm AS (\n SELECT id,\n LOWER(TRIM(email)) AS email_n,\n REGEXP_REPLACE(phone, '\\D', '') AS phone_n,\n updated_at\n FROM customers\n),\nranked AS (\n SELECT *,\n ROW_NUMBER() OVER (\n PARTITION BY email_n, phone_n\n ORDER BY updated_at DESC\n ) AS rn\n FROM norm\n)\nSELECT *\nFROM ranked\nWHERE rn > 1; -- these are duplicates to review<\/code><\/pre>\n\n\n\nTo list keys with dup counts:<\/p>\n\n\n\n
SELECT email_n, phone_n, COUNT(*) AS cnt\nFROM norm\nGROUP BY email_n, phone_n\nHAVING COUNT(*) > 1;<\/code><\/pre>\n\n\n\n<\/span>21. A report query on a sales table with 500 million records is taking more than 5 minutes to run. How would you optimize it for faster performance?<\/span><\/h3>\n\n\n\n\n- Filter early on partitioned columns (e.g., sale_date).<\/li>\n\n\n\n
- Create covering indexes on join\/filter cols.<\/li>\n\n\n\n
- Avoid SELECT *. Project only needed fields.<\/li>\n\n\n\n
- Rewrite joins to cut row explosion.<\/li>\n\n\n\n
- Pre-aggregate to daily\/monthly summary tables.<\/li>\n\n\n\n
- Use EXPLAIN to spot scans, missing stats, bad join order.<\/li>\n<\/ul>\n\n\n\n
Example:<\/p>\n\n\n\n
-- Partitioned table + covering index\nCREATE INDEX ix_sales_cust_date ON sales(customer_id, sale_date, region);\n\n-- Pre-agg\nCREATE MATERIALIZED VIEW mv_sales_daily AS\nSELECT sale_date, region, SUM(amount) amt\nFROM sales\nGROUP BY sale_date, region;<\/code><\/pre>\n\n\n\n<\/span>Big Data Engineer Interview Questions<\/span><\/h2>\n\n\n\nYou might also come across big data engineer interview questions that cover data processing frameworks and handling large-scale data challenges.<\/p>\n\n\n\n
<\/span>22. What is Apache Spark and how does it differ from Hadoop MapReduce?<\/span><\/h3>\n\n\n\nApache Spark is an open-source distributed processing framework that handles large-scale data processing in memory. It supports batch, streaming, machine learning, and graph processing.<\/p>\n\n\n\n
Hadoop MapReduce processes data in stages using disk I\/O between each stage, making it slower. Spark keeps most operations in memory, which makes it faster for iterative tasks and interactive queries.<\/p>\n\n\n\n
<\/span>23. What is Apache Kafka and how do you use it?<\/span><\/h3>\n\n\n\nApache Kafka is a distributed event streaming platform used for real-time data pipelines and messaging. It stores streams of records in topics, and consumers read them in order. As a data engineer, I might use Kafka to collect logs from multiple servers and stream them into Spark or Flink for processing.<\/p>\n\n\n\n
<\/span>24. What is the Lambda architecture and when do you use it?<\/span><\/h3>\n\n\n\nThe Lambda architecture combines batch and real-time processing. The batch layer handles large historical data for accuracy, while the speed layer processes new data instantly for low-latency results. It is useful in analytics systems where both real-time insights and historical accuracy are important, such as fraud detection or recommendation engines.<\/p>\n\n\n\n
<\/span>Data Engineering Manager Interview Questions<\/span><\/h2>\n\n\n\nThese are the interview questions data engineers often face when applying for managerial-level roles.<\/p>\n\n\n\n
<\/span>25. How do you manage conflicts in your team?<\/span><\/h3>\n\n\n\nI address conflicts early by having one-on-one discussions to understand each perspective. Then, I bring the team together to focus on the shared goal. I aim for solutions that balance project needs and team harmony.<\/p>\n\n\n\n
<\/span>26. How do you prioritize tasks in a data engineering project?<\/span><\/h3>\n\n\n\nI start by identifying business-critical deliverables and dependencies. Tasks affecting multiple downstream processes get higher priority. I also keep buffer time for unexpected challenges and adjust priorities based on changing requirements.<\/p>\n\n\n\n
<\/span>27. How do you stay current with trends and best practices?<\/span><\/h3>\n\n\n\nI follow data engineering forums, attend webinars, and read documentation for new tools. I also encourage my team to share learnings from conferences or courses. Hands-on experimentation is key to truly understanding new approaches.<\/p>\n\n\n\n
<\/span>Data Engineer Coding Questions<\/span><\/h2>\n\n\n\nNow, let’s look at python coding interview questions for data engineer roles that test your problem-solving and scripting skills.<\/p>\n\n\n\n
<\/span>28. How do you find the top 3 highest salaries in a table using SQL?<\/span><\/h3>\n\n\n\n