Ankit Chilkalwar

A startup tried to free up space in their database and ended up losing ₹10 crore in sales 🙂 All because of a single SQL query. The Query looked harmless: DELETE FROM orders WHERE order_date <'2024-01-01'. Looks completely normal, right? When you run a DELETE, the rows are not physically removed from disk. Instead, they are marked as deleted (dead tuples) due to PostgreSQL’s MVCC (Multi-Version Concurrency Control) architecture. What actually happened: • Millions of rows were “deleted”. • But they were still occupying disk space in the database. • During a major sales event, the system tried to insert new orders. • The database ran out of storage as the deleted rows aren't reclaimed. Result: Orders couldn't be written -> lost revenue. The fix: VACUUM PostgreSQL relies on VACUUM to: • Scan tables • Remove expired row versions • Reclaim space for reuse Without proper vacuuming, dead tuples pile up faster than the system can clean them, causing storage and performance issues. Understanding how your database actually works internally matters. Get deep dive on PostgreSQL’s MVCC internals here: https://lnkd.in/gBMCrd2z

37

2 Comments

Spark Advance Concepts – Deep Dive into Spark Internals & Optimization Excited to share my latest document on Spark Advance Concepts, where I’ve covered in-depth topics that every Data Engineer should understand beyond just writing Spark code. This guide includes: 🔹 Spark Cluster Runtime Architecture (YARN, Driver, Executors, Containers) 🔹 PySpark Internals – Python Wrapper, Java Wrapper, and PY4J Connection 🔹 Driver vs Application Driver Explained 🔹 Spark Submit & Deploy Modes (Cluster vs Client) 🔹 Spark SQL Engine & Catalyst Optimizer (Logical → Physical Plan → Code Gen) 🔹 Spark Memory Management (Heap, Off-Heap, Overhead, PySpark Memory) 🔹 Adaptive Query Execution (AQE) • Dynamic Shuffle Partition Coalescing • Dynamic Join Strategy Switching • Skew Join Optimization • Dynamic Partition Pruning 🔹 Cache vs Persist (with storage levels) 🔹 Repartition vs Coalesce 🔹 DataFrame & Join Hints (Broadcast, Merge, Shuffle Hash, etc.) On page 2–8, I’ve explained how a Spark application flows from submission to execution (Driver → Executors → Containers). Pages 12–14 break down the Spark SQL execution plan and memory allocation in detail . Later sections cover AQE, performance tuning, and optimization strategies used in real-world production workloads. This document is especially useful for: ✔️ Azure Data Engineers ✔️ Big Data Professionals ✔️ Interview Preparation (Mid/Senior Level) ✔️ Anyone wanting to truly understand Spark under the hood I believe understanding internals is what differentiates a developer from a performance-focused Data Engineer. Would love your feedback 🙌 If you find this helpful, feel free to connect and follow for more advanced data engineering content. #ApacheSpark #PySpark #BigData #DataEngineering #Azure #SparkSQL #AQE #PerformanceTuning #RaushanKumar

113

7 Comments

💎 Databricks & PySpark Series | Day 59 𝐃𝐚𝐭𝐚𝐛𝐫𝐢𝐜𝐤𝐬 𝐢𝐬𝐧'𝐭 𝐣𝐮𝐬𝐭 𝐚𝐛𝐨𝐮𝐭 𝐒𝐩𝐚𝐫𝐤 𝐚𝐧𝐝 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠 𝐚𝐧𝐲𝐦𝐨𝐫𝐞. 𝐈𝐭'𝐬 𝐚𝐥𝐬𝐨 𝐛𝐞𝐜𝐨𝐦𝐢𝐧𝐠 𝐚 𝐩𝐥𝐚𝐭𝐟𝐨𝐫𝐦 𝐟𝐨𝐫 𝐛𝐮𝐢𝐥𝐝𝐢𝐧𝐠 𝐀𝐈 𝐚𝐩𝐩𝐥𝐢𝐜𝐚𝐭𝐢𝐨𝐧𝐬. In Day 58, I explored Agent Bricks and how Databricks is bringing together enterprise data, AI agents, tools, and evaluation. Today is day 59 of this series and I am posting this! I explored how 𝐑𝐀𝐆 (𝐑𝐞𝐭𝐫𝐢𝐞𝐯𝐚𝐥-𝐀𝐮𝐠𝐦𝐞𝐧𝐭𝐞𝐝 𝐆𝐞𝐧𝐞𝐫𝐚𝐭𝐢𝐨𝐧) fits into the Databricks ecosystem. ❓ Why RAG? An LLM can generate an answer, but it may not know our latest enterprise information. That's where retrieval becomes important. A simplified flow: 𝐔𝐬𝐞𝐫 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧->𝐑𝐞𝐭𝐫𝐢𝐞𝐯𝐞 𝐑𝐞𝐥𝐞𝐯𝐚𝐧𝐭 𝐃𝐚𝐭𝐚->𝐏𝐚𝐬𝐬 𝐂𝐨𝐧𝐭𝐞𝐱𝐭 𝐭𝐨 𝐋𝐋𝐌->𝐆𝐞𝐧𝐞𝐫𝐚𝐭𝐞 𝐑𝐞𝐬𝐩𝐨𝐧𝐬𝐞 In Databricks, this connects nicely with the broader data and AI platform. For example: 𝐃𝐚𝐭𝐚𝐛𝐫𝐢𝐜𝐤𝐬 𝐋𝐚𝐤𝐞𝐡𝐨𝐮𝐬𝐞 -> Enterprise data 𝐕𝐞𝐜𝐭𝐨𝐫 𝐒𝐞𝐚𝐫𝐜𝐡 -> Retrieve relevant information 𝐌𝐨𝐬𝐚𝐢𝐜 𝐀𝐈 -> Build and deploy AI applications 𝐌𝐨𝐬𝐚𝐢𝐜 𝐀𝐈 𝐀𝐠𝐞𝐧𝐭𝐬 -> Allow AI agents to use data and tools A simple example: Suppose an employee asks: "What's our latest customer refund policy?" Instead of relying only on the LLM's knowledge, the application can retrieve the relevant company documentation, provide that context to the model, and generate a response based on the retrieved information. That's where I see the value of having 𝐝𝐚𝐭𝐚 𝐚𝐧𝐝 𝐀𝐈 𝐜𝐥𝐨𝐬𝐞𝐥𝐲 𝐜𝐨𝐧𝐧𝐞𝐜𝐭𝐞𝐝. To note: 𝐃𝐚𝐭𝐚𝐛𝐫𝐢𝐜𝐤𝐬 -> 𝐃𝐚𝐭𝐚 -> 𝐑𝐞𝐭𝐫𝐢𝐞𝐯𝐚𝐥 -> 𝐑𝐀𝐆 ->𝐀𝐠𝐞𝐧𝐭𝐬 -> 𝐀𝐈 𝐀𝐩𝐩𝐥𝐢𝐜𝐚𝐭𝐢𝐨𝐧𝐬 The more I explore Databricks, the more I see how the platform is connecting traditional data engineering with modern AI. ⭐ Still learning and exploring. On to Day 60 Probably the last day of this series!! ❣️ Follow Apurva Gawande for more such posts and technical series!

8

📌 Apache Spark Series - Day 1 Why Apache Spark over Hadoop MapReduce? For a long time, Hadoop MapReduce was the king of Big Data. But it had a major bottleneck: Disk I/O. In MapReduce, after every map and reduce step, intermediate results are written to disk. Reading from disk again and again makes processing "slow and time-consuming" especially for large datasets and iterative workloads. ⚡Apache Spark solves this problem. Spark performs data processing in memory, meaning intermediate results are stored in RAM instead of disk. This significantly reduces disk I/O and makes Spark much faster than MapReduce. Key advantages of Spark over MapReduce: • In-memory computation → faster execution • Efficient for iterative workloads (ML, analytics) • Simple and high-level APIs (DataFrame, SQL) • Supports batch, streaming, and SQL in one engine Because of these advantages, Spark is widely used today for large-scale data processing and analytics. 🚀 This post is part of my Apache Spark learning series, where I share concepts as I learn them. #ApacheSpark #BigData #DataEngineering #LearningInPublic #Hadoop #SparkSeries

8

Time Entries & Utilization Dashboard ⚙️ Built an end-to-end BI solution — SQL data prep, a Snowflake semantic model, a full DAX business logic layer, and role-based security — around a single question: where is our workforce spending time? 📊 The Problem The source data spanned multiple service entities (field service requests, work orders, bookings, time entries) with inconsistencies that needed resolving before any reporting could be trusted. 🧹 Data Preparation So I started with SQL — querying, standardizing, and cleaning the data sourced from Dataverse through the organization's Microsoft Fabric environment — before it ever reached Power BI. 🧩 Semantic Model On top of that, I built a Snowflake-schema semantic model around the core business flow: Field Service Request → Work Order → Booking / Time Entry Booking = planned work Time Entry = actual recorded work DateTable + disconnected helper tables support dynamic matrix and productivity reporting ⚡ DAX & Business Logic The DAX layer is where the real business logic lives — dynamic measures for utilization, planned vs. actual hours, and work order completion, built on rules like: 187 available hours per employee per month Productive time = time entries tied to a Work Order Vacation entries explicitly excluded from productivity calculations Employee identity resolved through the actual bookable resource field 🖥️ Dashboard Design Two report pages, built to move from summary to detail: 1️⃣ Executive Overview KPI cards (tickets, utilization, work orders, bookings, planned/actual hours) Productivity mix breakdown Work order status Planned-vs-actual trend by month Actual hours by employee Full employee/month matrix 2️⃣ Resource Performance Employee selector driving employee-specific KPIs Activity breakdown by work type Capacity-vs-actual-work comparison built on the 187-hour logic Productivity composition view for the selected employee and period 🔐 Security Security was a core requirement, not an afterthought: Row-Level Security (RLS) restricts regional heads to their own region's data Object-Level Security (OLS) restricts individual employees to their own records 🚀 The Result Achieved 100% trustworthy score centralizing SQL-based data prep, semantic modeling, DAX logic, and Power BI reporting into one governed pipeline took a reporting process that used to eat half a day and brought it down to minutes — while giving management a live view into utilization, productivity, and workload that didn't exist before. ⚠️ Note: all names, hours, and figures shown in any associated screenshots have been modified/anonymized for confidentiality. 🛠️ Stack: Power BI · SQL · Microsoft Fabric · Dataverse · Snowflake Semantic Model · DAX · Power Query · RLS · OLS #PowerBI #DataAnalytics #BusinessIntelligence #DAX #SQL #DataModeling #MicrosoftFabric #Snowflake #DataEngineering #Dataverse #RowLevelSecurity #BIData #DataVisualization #Analytics #TechCareers

6

2 Comments

Post 2: PySpark & Apache Spark Title: PySpark in the Spotlight: Key Topics for Data Engineering Interviews My technical interviews have consistently delved deep into PySpark and Apache Spark. Here’s a breakdown of the concepts that are crucial for any data engineer to master. 🔧 Core Spark Concepts & Architecture 1. What is the difference between narrow and wide transformations? 2. Explain lazy evaluation in Spark and how it helps optimize jobs. 3. What are actions vs. transformations? Give examples of each. 4. What is the difference between groupByKey() and reduceByKey()? 💻 PySpark Query Writing & Problem Solving 5. A common problem involves analyzing streaming data. For example, with these tables: artist (id, artist_name) album (id, artist_id, album_title, year_released) stream (id, artist_id, album_id, song_title, date, number_of_streams, pay_per_stream) The Challenge: Find each artist’s most streamed album using PySpark. This tests your skills in joins, aggregations, and window functions (like row_number() and rank()). 6. Write a PySpark query to find employees whose salary is higher than their manager’s, requiring a clever self-join. ⚡ Performance Optimization & Debugging 7. How do you handle data skewness? Have you used salting techniques? 8. Explain the difference between coalesce() and repartition(). 9. What is the difference between cache() and persist()? 10. A PySpark job is running slowly. What is your strategy for debugging and optimization? Mastering these areas is essential for building efficient, large-scale data processing pipelines. #PySpark #ApacheSpark #DataEngineering #BigData #DataBricks #ETL #DataPipeline #InterviewPrep #TechJobs #DataSkills

16