Send Customized Emails Using Fabric Data Pipelines

[tharunkumarRTK]

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. 

  

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

  

  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;

  

  Email Template 

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

  

  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 

  

  @activity('GetEmployees').output.value 

  Structuring the HTML 

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

  

  Sending the Email 

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

  

  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 

  

  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 

  

  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 

  

  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!