Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
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:
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.