Viswanath Thatha

🚀 Data Engineering & SQL/PySpark Interview Questions with Answers Over the past few weeks, I’ve been compiling a list of real-world interview questions that cover: ✔ SQL (queries, window functions, duplicates, business logic) ✔ PySpark (transformations, aggregations, ETL scenarios) ✔ Data Engineering case studies I’ve put together 20+ questions along with their answers (SQL + PySpark/Python where applicable) into a PDF for easy reference. 📌 Some sample questions inside the PDF: 🔑 SQL Interview Questions 1️⃣ Find employees who earn more than their managers 2️⃣ Find the second highest salary using Window Functions 3️⃣ Delete duplicate emails (without window functions) 4️⃣ From student table → select best of 3 marks and take average 5️⃣ Select names starting with ‘A’ 6️⃣ Difference between Rank, Row_Number, and Dense_Rank (with example) 7️⃣ Return Top 3 products with highest quantity sold in last 6 months 8️⃣ List employees earning more than their manager 9️⃣ Identify duplicate email addresses in users table 🔟 List employees not assigned to any project 1️⃣1️⃣ Find people with same name across different countries 1️⃣2️⃣ Retrieve unique handshakes using window functions 1️⃣3️⃣ Retrieve employees in alternating order of Male/Female sorted by age 1️⃣4️⃣ Find customers who haven’t placed any orders but have Pending shipping status 🔑 PySpark / Python Questions 1️⃣5️⃣ Add +1 to the last element of a list 1️⃣6️⃣ Find duplicates in a Python list and print them 1️⃣7️⃣ PySpark: Calculate total salary paid per department per month 🔑 Scenario-Based SQL / Business Questions 1️⃣8️⃣ Get manager id, manager name, number of employees, and avg salary of employees reporting to each manager 1️⃣9️⃣ Find customers who ordered both Keyboard and Mouse 2️⃣0️⃣ Find customers who ordered both Keyboard and Mouse (with customer details) 💡 This resource is useful for: Freshers preparing for Data/SQL interviews Professionals brushing up for Data Engineering roles Anyone looking to sharpen problem-solving with SQL/PySpark 📄 I’ve attached the full PDF with answers below. Hope it helps others in their preparation 🚀 👉 Question for you: Which of these areas do you find most challenging – Window Functions, PySpark, or Business Logic Queries? #DataEngineer #SQL #PySpark #BigData #InterviewPreparation

113

2 Comments

💡 PySpark Interview Problem – #day18 🎯 Topic: Optimize Data Output Size using Partition + File Format + Compression Awareness 🚀 Scenario-Based Question (Not Just Code): You have a PySpark DataFrame with 300 million rows of transaction data, each containing: transaction_id user_id transaction_date amount category Your task: ✅ Write this data to cloud storage (e.g., S3, GCS, or HDFS) in a way that: Minimizes total file size Speeds up query read time later Keeps things scalable for future incremental loads 🎯 Your Deliverables: What file format will you choose and why? What compression format and codec? How will you partition and bucket the data (if any)? What’s your final .write() statement in PySpark? ✅ Optimized Solution (Thought Process): 1️⃣ File Format: Choose Parquet – it’s columnar, compresses well, and is optimized for Spark and analytics engines like Athena, Presto, BigQuery. 2️⃣ Compression Codec: Use Snappy – fast, splittable, balances compression ratio and speed. Good for analytics. 💡 (ZSTD is better compression but slower, used in storage-heavy cold data layers.) 3️⃣ Partitioning Strategy: Partition by: transaction_date (most common filter field) Bucket by: user_id (for JOIN performance if needed) This helps with data skipping and efficient filtering. 4️⃣ Final PySpark Code: df.write \ .partitionBy("transaction_date") \ .option("compression", "snappy") \ .format("parquet") \ .mode("overwrite") \ .save("s3://your-bucket/transactions/") 🧠 Optional: For bucketing (if using Hive table): df.write \ .bucketBy(16, "user_id") \ .sortBy("user_id") \ .saveAsTable("analytics.transactions_bucketed") 🧠 Why this challenge is different and powerful: It tests your storage + performance + pipeline optimization knowledge It’s not about syntax — it’s about being a real-world data engineer Every big tech company asks this at system design / scenario rounds 🏢 Companies Asked: Netflix #PySpark #BigData #DataEngineering #Parquet #Compression #FileOptimization #SparkInterview #CloudStorage #Partitioning #100DaysOfPySpark #DataPipelineDesign

110

15 Comments

Deloitte / EY Pyspark interview questions for Data Engineers 1. Can you explain the differences between a DataFrame and an RDD in PySpark? 2. What techniques would you use to optimize the performance of PySpark code? 3. How does the Catalyst Optimizer contribute to query execution in PySpark? 4. Which serialization formats are commonly used in PySpark, and why? 5. How do you address skewed data issues in PySpark? 6. Could you describe how memory management is handled in PySpark? 7. What are the different types of joins in PySpark, and how do you implement them? 8. What is the purpose of the 'broadcast) function in PySpark, and when should it be used? 9. How do you define and use User-Defined Functions (UDFs) in PySpark? 10. What is lazy evaluation in PySpark, and how does it affect job execution? 11. What are the steps to create a DataFrame in PySpark? 12. Could you explain the concept of Resilient Distributed Datasets (RDD) in PySpark? 13. What are actions and transformations in PySpark, and how do they differ? 14. How do you manage and handle null values in PySpark DataFrames? 15. What is a partition in PySpark, and how do you control partitioning for better performance? 16. Can you explain the difference between narrow and wide transformations in PySpark? 17. How does PySpark infer schemas, and what are the implications of this? 18. What role does SparkContext play in a PySpark application? 19. How do you perform aggregations in PySpark, and what are the key considerations? 20. What strategies do you use for caching data in PySpark to improve performance? Follow Me Venkat Sai Kalyanam for more such content 😌 #deloitte #ey #interview #questions #interviewquestions #deloitteinterview #eyinterview #fresher #experienced #jobs

61

🚨 𝗕𝗶𝗴 𝟰 𝗣𝘆𝗦𝗽𝗮𝗿𝗸 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗦𝗲𝗰𝗿𝗲𝘁𝘀 – ₹𝟮𝟰–𝟯𝟬+ 𝗟𝗣𝗔 𝗖𝗧𝗖 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀 𝗥𝗲𝘃𝗲𝗮𝗹𝗲𝗱! If you’re preparing for EY, Deloitte, PwC, KPMG or any global data-heavy role, this list will 10x your prep! 🧑‍💻✨ 🔥 20 PySpark Interview Questions You MUST Know 1️⃣ DataFrame vs RDD in PySpark 2️⃣ Optimizing PySpark code for production pipelines 3️⃣ Catalyst Optimizer – how it really works 4️⃣ Serialization formats in PySpark 5️⃣ Handling skewed data at scale 6️⃣ Memory management in PySpark 7️⃣ Joins in PySpark & when to use them 8️⃣ broadcast() – real world usage 9️⃣ User-Defined Functions (UDFs) 🔟 Lazy evaluation impact 1️⃣1️⃣ Creating a DataFrame 1️⃣2️⃣ What is an RDD? 1️⃣3️⃣ Transformations vs Actions 1️⃣4️⃣ Handling null values 1️⃣5️⃣ Partitions & optimization 1️⃣6️⃣ Narrow vs wide transformations 1️⃣7️⃣ Schema inference 1️⃣8️⃣ Role of SparkContext 1️⃣9️⃣ Efficient aggregations 2️⃣0️⃣ When & how to cache or persist data 📌 Free PDF with detailed expert answers to all these questions 👉 Comment “EY PDF” & I’ll DM it to you 🔖 Save this post for your next PySpark interview prep 👥 Tag a friend preparing for Big 4 interviews 💡 Let’s grow together in the Data Engineering community! #PySpark #BigData #DataEngineering #Spark #CareerGrowth #Big4 #EY

114

13 Comments

🎯 Crack Data Engineering Interviews with These 12 Must-Know Concepts 🧠 Whether it’s PySpark, SQL, ADF, or Snowflake — these concepts appear everywhere. Perfect for product companies & real-world problem solving! --- 💻 🔥 12 Core Concepts to Master (Across the Stack): 🔹 PySpark 1️⃣ Lazy vs Eager Evaluation – why PySpark doesn’t compute until an action is called 2️⃣ Partitioning, Shuffling, and why Spark jobs slow down unexpectedly 🔹 SQL 3️⃣ Window Functions: ROW_NUMBER, LEAD, LAG, etc. 4️⃣ Query optimization using Execution Plans, Indexes & EXISTS vs IN 🔹 Azure Data Factory (ADF) 5️⃣ Difference between Lookup and Get Metadata 6️⃣ Event-based vs Schedule triggers – when to use which 🔹 Databricks / Delta Lake 7️⃣ Difference between coalesce() and repartition() 8️⃣ Delta Lake features: Merge (UPSERT), Time Travel, Schema Enforcement 🔹 Snowflake 9️⃣ How micro-partitions and automatic clustering impact query performance 🔟 What is zero-copy cloning, and why it saves time and cost 🔹 MongoDB 1️⃣1️⃣ Embedded vs Referenced documents – when to use which 1️⃣2️⃣ Aggregation pipeline stages and performance tips --- 💡 Pro Tip: Don’t prepare topic-by

39

🚀 Cracking Data Engineering Interviews Just Got Easier! 🚀 I’ve compiled the complete Celebal Company L1 Interview Q&A – covering Azure Data Factory, PySpark, Databricks, SQL, Delta Lake, ADF triggers, schema drift handling, query optimization, caching vs persistence, late data handling, fact table tuning, time travel in Delta Lake, and much more! This document is a goldmine for Data Engineers, especially if you’re preparing for Celebal Company, but it also helps for interviews at PwC, Deloitte, Tata Consultancy Services, Infosys, Accenture, Capgemini, Cognizant, Wipro, EY, KPMG India, Tech Mahindra, HCLTech, and Microsoft Azure roles. 🔥 What you’ll learn inside: ✔ Azure Data Factory real-world scenarios (metadata-driven pipelines, triggers, schema drift) ✔ SQL optimization (indexes, fact table tuning, window functions, UNION vs UNION ALL) ✔ PySpark must-know coding exercises ✔ Delta Lake features (time travel, Z-ordering, schema evolution) ✔ Spark concepts (cache vs persist, checkpointing, actions vs transformations) ✔ Common interview puzzles asked in L1 rounds 💡 Whether you’re a Fresher, Junior, or Senior Data Engineer, this resource will boost your confidence and success rate in interviews. 👉 Save this post, share with friends, and get ready to crack your next Data Engineering interview like a pro! 🔑 For More Content, follow Sachin Patil #AzureDataEngineer #DataEngineering #CelebalInterview #AzureDataFactory #Databricks #PySpark #DeltaLake #SQLInterview #BigData #CloudComputing #ADFInterview #SparkOptimization #DataEngineerCareer #TechJobs #CareerGrowth #MicrosoftAzure #DataPipeline #InterviewPreparation #DataEngineerCommunity #JobSeeker #ITCareers

54

🆚 Hadoop vs. Databricks: What Every Admin Should Know Before Switching Thinking of switching from Hadoop to Databricks? As a Hadoop Admin, here’s what you really need to know before making the move 👇 ⸻ 🏗️ 1. Architecture Shift >>Hadoop: On-prem, HDFS, batch processing >>Databricks: Cloud-native, Delta Lake, real-time + ML-ready ➡️ No more managing NameNodes or block replication manually 😌 ⸻ 💾 2. Storage >>Hadoop = HDFS >>Databricks = Object storage (S3, ADLS) + Delta Lake ➡️ Say goodbye to manual disk balancing. ⸻ ⚙️ 3. Cluster Management >>Hadoop: Static YARN configs >>Databricks: Auto-scaling, serverless clusters ➡️ You manage jobs, not servers. ⸻ 🔐 4. Security & Access >>Hadoop: Kerberos, Ranger/Sentry nightmares 😵 >> Databricks: Unity Catalog, RBAC, UI-based controls ➡️ Admins finally get clean, fine-grained access control. ⸻ 🚀 5. Performance & Use Cases >>Hadoop: Strong for ETL, but struggles with ML >>Databricks: Optimized for streaming + ML pipelines ➡️ Built-in support for Spark, Python, SQL, and notebooks. ⸻ 💸 6. Cost Model >>Hadoop: Hardware + ops cost >>Databricks: Pay-per-usage (watch out for idle clusters!) ➡️ New challenge: FinOps becomes part of your skill set. ⸻ 🧠 7. Skills That Transfer ✔️ Linux ✔️ Spark ✔️ Hive ✔️ Troubleshooting mindset ➡️ Your admin experience still matters — but the focus shifts from infrastructure to pipelines. ⸻ TL;DR: Switching to Databricks isn’t just a platform change — it’s a mindset shift. From “keeping the cluster alive” to “delivering faster data outcomes.” ⚡ 💬 Already made the switch or thinking about it? Share your experience below 👇 #HadoopAdmin #Databricks #AzureDatabricks #BigData #CloudComputing #DeltaLake #DataEngineering #CloudMigration #TechLeadership #Spark #DataOps #DigitalTransformation #HDFS #Hive #YARN #DataPlatform #FinOps #CloudNative

26

2 Comments