Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

tharunkumarRTK

Send Customized Emails Using Fabric Data Pipelines

tharunkumarRTK_0-1750340750524.png

 

 

Send Customized Emails Using Fabric Data Pipelines 

Imagine this: You’re a data engineer, and you’ve been asked to implement an automated mechanism to notify employees whose weekly working hours fall below a defined threshold say, 35 hours. The ask is simple: 

  • Every week, after the data load completes, an email should be sent to each such employee. 
  • The email should be personalized and neatly formatted. 
  • Additionally, there must be a way to log and track which employees were notified. 

    Sounds like something that can be done using Power Automate or another workflow tool, right? 

    But here’s the twist, you’re a data engineer, not an automation expert.  
    Wouldn’t it be great if you could do all of this within your existing skillset, inside a tool you already know like Fabric Data Pipelines? 

    Well, let’s do just that! 

    The Data Source 

    Every week, the employee aattendance system exports a flat .csv file with working hours of employees. 

     

    tharunkumarRTK_1-1750340799427.png

     

    A pre-existing Fabric (FDF) pipeline ingests this file and loads it into a Delta table within a Fabric Lakehouse. 

    tharunkumarRTK_2-1750340819754.png

     

    Filter the Data 

    Now, we want to add logic to the same pipeline to identify only those employees who have logged less than 35 hours in the current week. 

    To do this, I added a Lookup activity in the pipeline, which runs the following SQL: 

    SELECT  
      EmployeeId,  
      EmployeeName,  
      SUM(WorkingHours) AS TotalWorkingHoursOfTheWeek 
    FROM  
      employeeworkinghours 
    WHERE  
      Date > (SELECT DATEADD(DAY, -7, GETDATE())) 
    GROUP BY  
      EmployeeId,  
      EmployeeName 

    HAVING  
      SUM(WorkingHours) < 35;

    tharunkumarRTK_3-1750340840703.png

     

     

    Email Template 

    Here’s a sample of the email we’re going to send: 

    tharunkumarRTK_4-1750340854488.png

     

     

    Take note of the placeholders like employee name, hours worked and date these will be dynamically injected from the dataset. 

    This is base html required to create such email 


     

    Now for each employee who did not met the working hours threshold limit, we need to create HTML before sending the email. We can use ‘for each’ iterator and place the list of records retrieved from the lookup activity 

    tharunkumarRTK_5-1750340904324.png

    @activity('GetEmployees').output.value 

    Structuring the HTML 

    To manage the email content dynamically, I created email body in a pipeline variable, 

    tharunkumarRTK_6-1750340948691.png

     

     

    Sending the Email 

    With the final HTML ready, I added an Outlook activity to the pipeline and configured it to: 

    tharunkumarRTK_7-1750340958541.png

     

    I hand coded the ‘To’ email, as this pipeline is created for demo purpose. You need to replace it with the actual employee email address. As a best practice, instead of sending these emails from individual email id you can consider creating a shared mail box and put that mail in the “Send as” section. You will find “Send as”, “CC” and “Importance” options under: settings > advanced section. 

    Now the pipeline looks like this 

    tharunkumarRTK_8-1750340981301.png

     

    Logging Sent Notifications 

    To maintain an audit trail, I created a simple logging table in the Lakehouse and used a copy activity to insert a record for every email sent. 

    To create such table, you can use the below SQL statement 

    tharunkumarRTK_9-1750340990350.png

     

    SELECT  
      EmployeeId,  
      EmployeeName,  
      GETDATE() AS EmailSentOn 
    FROM  
      employeeworkinghours 
    WHERE  
      Date > (SELECT DATEADD(DAY, -7, GETDATE())) 
    GROUP BY  
      EmployeeId,  
      EmployeeName 
    HAVING  
      SUM(WorkingHours) < 35; 

     

    Boom! You’ve now delivered exactly what your manager asked for 

    tharunkumarRTK_10-1750341024618.png

     

     

    Final Thoughts 

    Of course, there are other ways to approach this using Power Automate, Paginated Reports (with mail merge), or third-party services. But this blog shows an approach using only Fabric Data Pipelines. 

    I’d love to hear how you would approach this. Have you built something similar? Any suggestions or improvements to this method? 

    Thanks for reading 
    Happy Learning!