Data Lake On-Demand Extract Self-Service
Via a Teams/form request, the flow lets approved analysts request an extract of a curated dataset with filters and date range; it runs an approval, generates the extract from the Data Lake, drops it in a secure share with an expiring link, and logs the request. Provides governed self-service data extracts.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
This flow delivers governed self-service data extracts from Azure Data Lake. An approved analyst (via a Teams/Forms front end or any HTTP caller) submits a request for a curated dataset with a date range, filters, and output format. The flow enforces a dataset allow-list, routes the request to the data owner for approval, generates the extract from the lake, delivers an expiring secure link by email, and logs every request to Dataverse for audit.
Why it matters: Analysts constantly ask data engineers for ad-hoc extracts. A governed self-service path removes that ticket queue while keeping access controlled, approved, and fully audited.
Status: Built and shipped Off (demo). Going live requires only authorizing the four connections and setting the environment-variable values for the target tenant.
Use Case
Analysts need filtered extracts of curated lake data without raising an engineering ticket - but access must stay governed: only allow-listed datasets, owner-approved, time-boxed links, and a complete audit trail.
Flow Architecture
Extract Request Received
Request - When a HTTP request is received (webhook)Accepts a JSON payload (dataset, fromDate, toDate, filterCriteria, format, requestorName, requestorEmail, justification). Runs async (202 Accepted) while it waits on approval.
Initialize Trace & Config
Initialize variable (x9)Mints a correlation id (guid), loads the dataset allow-list, Data Lake account, curated-zone path, data owner email, link expiry, and secure-share base URL from env vars, captures the requestor email, and composes the dataset folder path.
Check Dataset Allowed
Conditioncontains(varAllowedDatasets, dataset) - gates the request against the allow-list. Not allowed routes to Log Denied Request (Dataverse) then Notify Denied Email (Outlook).
Start Extract Approval
Approvals - StartAndWaitForAnApprovalAssigns the request to the data owner and blocks until a decision.
Check Approval Outcome
Conditionequals(outcome,'Approve'). Reject routes to Log Rejected Request (Dataverse) then Notify Rejected Email (Outlook).
Generate & Deliver Extract
Azure Data Lake - ListFiles + ReadFile, Compose, Outlook - SendEmailV2On approval: lists curated files, reads the first file (stands in for a filtered ADF/extract job), composes the expiring secure-share link, and emails it to the requestor with a redaction note.
Log Approved Request
Microsoft Dataverse - CreateRecordWrites the audit row (status Approved, file count, delivery link, correlation id) to flowlibs_extractlog.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_AllowedDatasets | String | ["sales_curated","marketing_curated","finance_curated"] | JSON allow-list of requestable curated datasets. |
| flowlibs_LinkExpiryHours | String | 48 | Hours the delivered extract link stays valid. |
| flowlibs_RequestLogTable | String | flowlibs_extractlogs | Dataverse entity set used for the audit log. |
| flowlibs_DataLakeAccountName | String | flowlibsdatalake | Azure Data Lake account hosting the curated zone. |
| flowlibs_CuratedZonePath | String | curated | Base folder path of the curated zone. |
| flowlibs_DataOwnerEmail | String | you@yourcompany.com | Data owner who approves extract requests. |
| flowlibs_SecureShareBaseUrl | String | https://flowlibsdatalake.dfs.core.windows.net/extracts | Secure-share base URL for delivered extracts. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Azure Data Lake | shared_azuredatalake | ListFiles ReadFile |
| Approvals | shared_approvals | StartAndWaitForAnApproval |
| Office 365 Outlook | shared_office365 | SendEmailV2 |
| Microsoft Dataverse | shared_commondataserviceforapps | CreateRecord |
Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.
Customization Guide
Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.
- Row / size limits
- Cap extract size in the extract-generation step (or the downstream ADF job).
- Column policy
- Mask PII by requestor role before delivery.
- Format choice
- CSV / Parquet / Excel via the format payload field.
- Front end
- Swap the Request trigger for a Microsoft Forms or Teams adaptive-card submission; map fields to the same JSON shape.
- Real delivery
- Replace the composed link with a true Azure Blob SAS or SharePoint share when wiring to a storage target.
Key Expressions
The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.
EXPR.01Allow-list gate
Allows only datasets present in the env-var allow-list.
EXPR.02Approval outcome
Branches on the approval decision.
EXPR.03File count
Counts curated files in the dataset folder.
EXPR.04First curated file path
Builds the path to the first curated file to read.
EXPR.05Expiring delivery link
Composes the expiring secure-share URL.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.