15. Skip Copy Activity When No Data in ADF | Lookup + IF Condition Incremental Load

Welcome to Session 15 of the Azure Data Factory Beginner Course 🚀 -------------------------------------------------- 📺 Azure Data Factory Beginner Playlist: Azure Data Factory – Beginner (From Scratch) -------------------------------------------------- In this session, you will learn how to SKIP the Copy Activity in Azure Data Factory (ADF) when no new or incremental data exists in the source. This is a critical real-time and enterprise design pattern that avoids unnecessary pipeline executions, reduces Azure cost, and keeps pipelines clean and predictable. -------------------------------------------------- 🔹 Problem Statement: -------------------------------------------------- By default, Copy Activity runs every time: ❌ Even when ZERO records are available ❌ Integration Runtime still starts ❌ Source & target connections still open ❌ Logs and metrics are generated ❌ Azure cost is still incurred 🚫 Zero records ≠ Zero cost CREATE TABLE Source_Orders ( OrderID INT, CustomerName VARCHAR(100), OrderAmount DECIMAL(10,2), LastModifiedDate DATETIME ); INSERT INTO Source_Orders VALUES (1, 'Ravi', 1000, '2026-01-01 10:00:00'), (2, 'Anita', 1500, '2026-01-02 11:00:00'), (3, 'John', 2000, '2026-01-03 09:30:00'); CREATE TABLE Target_Orders ( OrderID INT, CustomerName VARCHAR(100), OrderAmount DECIMAL(10,2), LastModifiedDate DATETIME ); Step1: SELECT ISNULL(MAX(LastModifiedDate), '1900-01-01 00:00:00') AS MaxLastModifiedDate FROM Target_Orders; Step2: SELECT COUNT(1) AS IncrementalCount FROM Source_Orders WHERE LastModifiedDate '@{activity('Lookup1').output.firstRow.MaxLastModifiedDate}' step3: @greater( activity('Lookup2').output.firstRow.IncrementalCount, 0 ) step4: SELECT COUNT(1) AS IncrementalCount FROM Source_Orders WHERE LastModifiedDate '@{activity('Lookup1').output.firstRow.MaxLastModifiedDate}' Before Run3: INSERT INTO Source_Orders VALUES (4, 'Mallaiah', 3000, '2026-01-04 08:15:00'); UPDATE Source_Orders SET OrderAmount = 1800, LastModifiedDate = '2026-01-04 09:00:00' WHERE OrderID = 2; -------------------------------------------------- 🔹 Solution Covered in This Video: -------------------------------------------------- We implement a mandatory validation pattern using: 1️⃣ Lookup1 – Fetch last processed watermark value 2️⃣ Lookup2 – Check incremental record count 3️⃣ IF Condition – Decision gate 4️⃣ Copy Activity – Runs ONLY when data exists 👉 No metadata table is used in this design. -------------------------------------------------- 🔹 Topics Covered in This Video: -------------------------------------------------- Why Copy Activity should NOT run for zero records Hidden cost of zero-row copy activity Why Lookup2 + IF Condition is mandatory Real-time enterprise reasoning (90% vs 10% scenarios) Incremental load day-wise explanation (Day1, Day2, Day3) Architecture overview Hands-on demo using Azure SQL DB Strong comparison with wrong pipeline designs When (and when NOT) to skip this pattern Benefits of this approach Best practices for production pipelines -------------------------------------------------- Why This Pattern Is Important: -------------------------------------------------- Saves Azure cost 💰 Faster pipeline execution ⚡ Clean logs & monitoring 📊 Predictable pipeline behavior Enterprise-grade design 🏭 -------------------------------------------------- What You Will Learn Next (Session 16): -------------------------------------------------- Incremental Load using File-Based Watermark (JSON file instead of database table) -------------------------------------------------- 🎓 Cloudpandith Training Institute – Azure Data Engineering -------------------------------------------------- Learn Azure Data Engineering from real-time industry experts with strong focus on practical implementation and live project scenarios. 🌐 Course Details & Syllabus: https://www.cloudpandith.com 🧪 Azure Data Engineer Demo: Every Saturday | 10:00 AM IST 📲 WhatsApp for Course Details: https://wa.me/918904424822 📅 Book 1-to-1 Discussion with Trainer: https://meetpro.club/mallaiahsomula 📍 About the Trainer: https://goo.gl/maps/9jGub6NfLH2jmVeGA 📞 Contact: 8904424822 ------------------------------------ 📚 Free Practice Resources (Optional) ------------------------------------ MCQs are discussed in the video. If you want MCQs in document format (FREE), you may optionally join: WhatsApp Group: https://chat.whatsapp.com/Gow6Qxi77FN... Telegram Group: https://t.me/+PGnxzlaFpvo1MWJl Joining groups is optional. Everything required to understand this pattern is already covered in the video. 👍 Like | Share | Subscribe for more Azure Data Factory & Data Engineering sessions