Setting Up a Microsoft Fabric Lakehouse with Azure Data Factory for Public Sector Projects

Introduction

Public sector organisations across Australia face unique data challenges: managing vast volumes of disparate data sources, ensuring strict compliance with privacy regulations, and delivering actionable insights to stakeholders with limited IT resources. Microsoft Fabric, combined with Azure Data Factory, provides a unified, scalable platform that addresses these demands whilst maintaining governance and security standards essential for government operations.

This comprehensive tutorial walks you through setting up a production-ready Microsoft Fabric lakehouse integrated with Azure Data Factory, tailored specifically for public sector use cases. Whether you’re a CIO modernising your analytics infrastructure, a data architect designing a new platform, or a government agency seeking to consolidate data silos, this guide provides the hands-on steps, best practices, and troubleshooting guidance you need to succeed.

The combination of Fabric’s unified analytics experience with Azure Data Factory’s enterprise-grade orchestration creates a powerful foundation for public sector analytics. By following this tutorial, you’ll establish a secure, governed data platform that scales with your organisation’s needs whilst maintaining compliance with Australian Privacy Principles and data sovereignty requirements.

Prerequisites and Setup Requirements

Before beginning this tutorial, ensure you have the following prerequisites in place:

Licensing and Access Requirements

You’ll need a Microsoft Fabric capacity subscription. Public sector organisations should verify their licensing eligibility through their Microsoft account manager, as special government pricing and compliance options may be available. Each user requires either a Fabric Pro licence or access through a Fabric Premium capacity. For development and testing, a Fabric trial capacity (available for 60 days) is sufficient to complete this tutorial.

Your Azure subscription must have appropriate permissions to create and manage resources. Ensure your account has Owner or Contributor role assignments at the subscription level. For public sector deployments, confirm that your subscription is configured for Australian data residency, with resources deployed to Australian regions (typically Australia East or Australia Southeast).

Azure Resources and Credentials

You’ll need an active Azure subscription with sufficient quota for creating storage accounts, data factories, and associated networking resources. Create an Azure Storage Account in your target Australian region before proceeding; this will serve as your source data repository. Note the storage account name, key, and connection string, as you’ll reference these throughout the setup process.

Generate an Azure Storage Account access key or connection string. In the Azure portal, navigate to your storage account, select “Access keys” under Settings, and copy the connection string. Store this securely in Azure Key Vault, which you should create in the same region as your other resources.

Network and Security Configuration

For public sector projects, network security is paramount. Ensure your Azure resources are deployed within a Virtual Network (VNet) with appropriate Network Security Groups (NSGs) configured. If your organisation requires private connectivity, set up Azure Private Link endpoints for Azure Data Factory and Azure Storage before proceeding.

Configure firewall rules to restrict access to your Azure Storage Account and Data Factory to known IP ranges. For government agencies, this typically means restricting access to your organisation’s IP range or requiring VPN connectivity. Document these security configurations as you’ll need to reference them when configuring data pipelines.

Software and Tools

Install the latest version of Azure Storage Explorer, which provides a graphical interface for managing your data lake storage. Download it from the official Microsoft website. You’ll also need SQL Server Management Studio (SSMS) or Azure Data Studio for querying your lakehouse SQL endpoint once data is loaded.

For advanced users, install the Azure CLI and ensure you have the latest version of PowerShell Core. These tools enable scripting and automation of resource deployment, which is particularly valuable for public sector organisations managing multiple environments (development, testing, production).

Knowledge Prerequisites

This tutorial assumes basic familiarity with Azure portal navigation, SQL query syntax, and data concepts like schemas and tables. If you’re new to these areas, Microsoft Learn offers excellent foundational courses on Azure fundamentals and SQL basics. Understanding ETL (Extract, Transform, Load) concepts will help you grasp how Azure Data Factory orchestrates data movement.

Creating Your Microsoft Fabric Workspace

Your Fabric workspace serves as the container for all lakehouse, pipeline, and analytics resources. Creating a dedicated workspace for your public sector project ensures clear separation of concerns and simplifies governance and cost tracking.

Step 1: Access the Fabric Portal

Navigate to the Microsoft Fabric home page and sign in with your organisational account. If this is your first time accessing Fabric, you may see a welcome screen. Select “Start free” to activate your trial capacity or verify that your Premium capacity is provisioned.

Once logged in, you’ll see the Fabric home interface. In the bottom left corner, verify that you’re in the “Fabric” experience (not Power BI, Data Engineering, or other workloads). This ensures you have access to all required tools.

Step 2: Create a New Workspace

Select “Workspaces” from the left navigation pane, then click “New workspace”. A dialog box will appear prompting you to enter workspace details.

Enter a descriptive name for your workspace, such as “Public Sector Analytics Platform” or “Government Data Lake”. For public sector organisations, including the agency name or project code in the workspace name helps with governance and cost allocation. For example: “NSW-Health-Analytics-Workspace” or “Federal-Finance-Data-Hub”.

Provide a meaningful description that documents the workspace’s purpose. Include information such as the sponsoring agency, primary use cases, and any relevant compliance frameworks. This description becomes invaluable when managing multiple workspaces across your organisation.

Under “Advanced settings”, select your Fabric capacity. If you’re using a trial, select “Trial capacity”. For production deployments, select your Premium capacity. Public sector organisations should ensure the capacity is deployed in an Australian region to meet data residency requirements.

Click “Apply” to create the workspace. Fabric will provision your workspace within seconds, and you’ll be redirected to the workspace home page.

Step 3: Configure Workspace Roles and Permissions

Once your workspace is created, configure access permissions to align with your organisation’s governance model. Select “Workspace settings” (the gear icon at the top right), then navigate to the “Access control” tab.

Add members to your workspace by clicking “Add people”. For public sector projects, follow the principle of least privilege: grant Admin role only to designated administrators, Member role to data engineers and analysts who need to create content, and Viewer role to stakeholders who only need to consume reports and dashboards.

Document your access control decisions in a governance log. This documentation is essential for compliance audits and helps maintain security standards across your organisation.

Creating and Configuring Your Lakehouse

The lakehouse is the foundation of your data platform, providing unified storage and compute for both structured and unstructured data. The official Microsoft Fabric lakehouse introduction provides comprehensive guidance on lakehouse architecture and concepts.

Step 4: Create a New Lakehouse

Within your workspace, select “New” and choose “Lakehouse” from the available options. A dialog will appear asking you to name your lakehouse.

Enter a descriptive name that reflects the data domain. For example, “Finance-Master-Lakehouse”, “Healthcare-Patient-Data”, or “Transport-Operations-Lake”. Avoid generic names like “DataLake” or “Lake1”, as these become confusing when managing multiple lakehouses across your organisation.

Click “Create”. Fabric will provision your lakehouse within your workspace. This process typically takes 30 to 60 seconds. Once complete, you’ll see the lakehouse interface, which includes the explorer pane on the left and the main content area.

Step 5: Understand Lakehouse Structure

Your newly created lakehouse contains two primary folders: “Tables” and “Files”. Understanding the distinction between these is crucial for effective data management.

The “Tables” folder stores data in Delta Lake format, which provides ACID transactions, schema enforcement, and optimised query performance. Tables are queryable via the SQL analytics endpoint and integrate seamlessly with Power BI and other analytics tools. For structured, regularly queried data (such as financial records, patient demographics, or operational metrics), store data in the Tables folder.

The “Files” folder stores raw data in its original format (CSV, Parquet, JSON, etc.). This folder is ideal for data ingestion, temporary staging, and unstructured data. As data flows through your pipeline, you’ll typically land raw files here, transform them, and then move the refined data to the Tables folder.

Navigate through both folders to familiarise yourself with the structure. Note that both folders are backed by Azure Data Lake Storage Gen2, providing scalability and cost efficiency for large-scale analytics workloads.

Configuring Azure Data Factory for Data Ingestion

Azure Data Factory provides enterprise-grade orchestration and transformation capabilities. For public sector projects, Data Factory enables repeatable, auditable data pipelines that meet compliance requirements.

Step 6: Create an Azure Data Factory Instance

In the Azure portal, select “Create a resource” and search for “Data Factory”. Click “Create”.

Enter the following details:

• Name: Provide a descriptive name such as “PublicSectorDataFactory” or “GovernmentETLPipeline”
• Subscription: Select your Azure subscription
• Resource Group: Select or create a resource group that contains your other data platform resources
• Region: Select an Australian region (Australia East or Australia Southeast) to ensure data residency
• Version: Select “V2”

Click “Review + create”, verify your settings, and click “Create”. Azure will deploy your Data Factory instance. This process typically takes 2 to 3 minutes.

Once deployment is complete, navigate to your Data Factory resource and select “Launch studio”. This opens the Data Factory authoring interface.

Step 7: Create a Linked Service to Azure Storage

A linked service in Data Factory defines a connection to an external resource. You’ll create a linked service to your Azure Storage Account, which will serve as both the source for raw data and the destination for processed data.

In the Data Factory studio, select “Manage” from the left pane, then “Linked services”. Click “New” to create a new linked service.

Search for “Azure Blob Storage” and select it. Click “Continue”.

Enter the following details:

• Name: “AzureStorageLinkedService” or a similar descriptive name
• Authentication method: Select “Account key” for development, or “Service principal” for production deployments
• Storage account name: Enter the name of your Azure Storage Account
• Storage account key: Paste the access key you noted during prerequisites

Click “Test connection” to verify connectivity. You should see a success message. Click “Create” to save the linked service.

Step 8: Create a Linked Service to Microsoft Fabric

To enable data movement from Azure Data Factory directly into your Fabric lakehouse, create a linked service that connects to your Fabric workspace.

Select “New” linked service again. Search for “Azure Data Lake Storage Gen2” and select it. Click “Continue”.

Enter the following details:

• Name: “FabricLakehouseLinkedService”
• Storage account name: Your Fabric workspace uses an internal storage account. You’ll need to identify this account’s name. In your Fabric workspace, select “Workspace settings”, then note the “Storage account name” displayed in the workspace information section
• Storage account key: Retrieve the key from Azure portal or use Managed Identity if your Data Factory has appropriate permissions

Click “Test connection” and then “Create”.

Building Your First Data Pipeline

Now that your infrastructure is in place, you’ll create a data pipeline that ingests data from Azure Storage into your Fabric lakehouse. This pipeline demonstrates the core ETL pattern used in public sector analytics.

Step 9: Prepare Source Data

Before creating your pipeline, prepare sample source data. This tutorial uses a CSV file representing a typical public sector dataset. For demonstration purposes, create a CSV file with the following structure:

RecordID,AgencyName,TransactionDate,Amount,Category,Status
1,Department of Health,2024-01-15,15000,Operations,Approved
2,Transport Authority,2024-01-16,22500,Infrastructure,Pending
3,Education Ministry,2024-01-17,18750,Personnel,Approved
4,Department of Health,2024-01-18,12000,Operations,Approved
5,Transport Authority,2024-01-19,31250,Infrastructure,Approved

Save this file as “PublicSectorTransactions.csv”. Upload it to your Azure Storage Account using Azure Storage Explorer. Create a container called “raw-data” and upload the CSV file there.

Step 10: Create a Copy Activity Pipeline

In Data Factory studio, select “Author” from the left pane. Click the “+” icon and select “Pipeline” to create a new pipeline.

Enter the pipeline name: “CopyDataToFabricLakehouse”. Click “Create”.

You’ll now see the pipeline designer canvas. From the Activities pane on the left, search for “Copy data” and drag it onto the canvas.

Select the Copy data activity and configure it:

Source Configuration:

• Click the “Source” tab
• Set “Source dataset” to create a new dataset
• Select “Azure Blob Storage” as the data store
• Use the “AzureStorageLinkedService” you created earlier
• Specify the file path: “raw-data/PublicSectorTransactions.csv”
• Set “First row as header” to true
• Click “OK” to create the dataset

Sink Configuration:

• Click the “Sink” tab
• Set “Sink dataset” to create a new dataset
• Select “Azure Data Lake Storage Gen2” as the data store
• Use the “FabricLakehouseLinkedService”
• Specify the file path pointing to your lakehouse: “/Tables/PublicSectorTransactions”
• Set the format to “Parquet” for optimised performance
• Click “OK”

Step 11: Add Data Transformation

To demonstrate transformation capabilities, add a Data Flow activity that enriches your data. In the Activities pane, search for “Data flow” and drag it onto the canvas, below your Copy activity.

Connect the Copy activity to the Data Flow by dragging the green success output from Copy to the Data Flow input.

Select the Data Flow activity and click “Create new data flow”. In the data flow designer:

• Add a Source step using your input dataset (PublicSectorTransactions)
• Add a Derived Column step to create a new column. For example, create a “FiscalYear” column that extracts the year from TransactionDate
• Add a Filter step to exclude any “Pending” status records
• Add a Sink step pointing to a new table in your lakehouse: “PublicSectorTransactions_Processed”

These transformations are intentionally simple for demonstration; in production environments, you’d add complex business logic, data quality checks, and enrichment steps.

Step 12: Publish and Test Your Pipeline

Select “Publish all” at the top of the Data Factory studio to save your pipeline. Data Factory will validate your configuration and publish it to your Data Factory instance.

Once published, select “Add trigger” to schedule your pipeline or “Trigger now” to execute it immediately. For this tutorial, select “Trigger now” to test your pipeline.

A “Pipeline run” dialog will appear. Click “OK” to execute the pipeline.

Monitor the execution by selecting the “Monitor” pane on the left. You’ll see your pipeline run appear in the list. Click on it to view detailed execution information, including activity duration, data volumes processed, and any errors.

Expect the pipeline to complete within 2 to 5 minutes, depending on data volume and transformation complexity. Once complete, you should see green checkmarks next to each activity, indicating successful execution.

Validating Data in Your Lakehouse

With your pipeline executed, validate that data has been successfully loaded into your lakehouse. This step confirms end-to-end connectivity and data integrity.

Step 13: Access Your Lakehouse SQL Endpoint

Return to your Fabric workspace and select your lakehouse. In the lakehouse interface, you’ll see your tables listed in the explorer pane on the left.

Click on the “PublicSectorTransactions_Processed” table. Fabric will display a preview of the table contents, showing the first 100 rows. Verify that:

• All expected columns are present (RecordID, AgencyName, TransactionDate, Amount, Category, Status, FiscalYear)
• Data types are correctly inferred (dates, numbers, text)
• The Filter transformation correctly excluded “Pending” status records
• Row counts match your expectations

If you notice any data quality issues, return to your Data Factory pipeline and adjust your transformation logic accordingly.

Step 14: Query Your Data Using SQL Analytics Endpoint

For advanced validation and to demonstrate SQL query capabilities, use the SQL analytics endpoint. In your lakehouse, select “SQL analytics endpoint” from the interface.

This opens a SQL query editor. Write a simple query to validate your data:

SELECT 
    AgencyName,
    COUNT(*) as TransactionCount,
    SUM(Amount) as TotalAmount,
    AVG(Amount) as AvgAmount
FROM PublicSectorTransactions_Processed
GROUP BY AgencyName
ORDER BY TotalAmount DESC

Execute this query. You should see aggregated results showing transaction counts and amounts by agency. This demonstrates that your data is queryable via SQL, enabling seamless integration with tools like Power BI, Excel, and custom applications.

Implementing Governance and Security

Public sector organisations must implement robust governance frameworks. Agile Insights provides comprehensive data governance solutions tailored to government compliance requirements.

Step 15: Configure Data Governance with Microsoft Purview

Microsoft Purview enables comprehensive data governance across your Fabric lakehouse. In the Azure portal, create a Purview account in your resource group.

Once provisioned, register your Fabric lakehouse as a data source in Purview. This enables:

• Data Lineage: Track data movement from source systems through your pipeline to final tables
• Data Classification: Automatically classify sensitive data (personally identifiable information, health data, financial information)
• Access Control: Define who can access specific datasets based on role and sensitivity level
• Compliance Reporting: Generate reports demonstrating compliance with privacy regulations

Configure classification rules to identify sensitive columns automatically. For public sector data, classify columns containing personal identifiers, health information, or financial details as “Confidential” or “Restricted”.

Step 16: Enable Row-Level Security (RLS) in Power BI

When connecting your Fabric lakehouse to Power BI for reporting, implement Row-Level Security to ensure users only see data relevant to their role. For example, a state health department might have users from different regions who should only see data from their region.

In Power BI Desktop, define RLS roles that map to your organisational structure. For example:

• Region Administrator: Can view all data
• Regional Manager: Can view only data from their assigned region
• Staff Member: Can view only aggregated, non-sensitive data

Apply RLS rules to your fact tables using DAX expressions that reference the currently logged-in user’s role.

Scheduling and Monitoring Your Pipelines

Production data pipelines require reliable scheduling and monitoring. Public sector organisations need audit trails and alerting to ensure data freshness and reliability.

Step 17: Schedule Your Pipeline for Regular Execution

Return to your Data Factory pipeline and configure a trigger for regular execution. Select “Add trigger” and choose “New/Edit”.

Configure a scheduled trigger with the following settings:

• Trigger type: Scheduled
• Start date: Today’s date
• Recurrence: Daily (or adjust based on your data refresh requirements)
• Time: 2:00 AM (off-peak hours to minimise impact on other systems)
• Time zone: Australian Eastern Time (or your local time zone)

Click “OK” to save the trigger. Your pipeline will now execute automatically at the specified time each day.

Step 18: Configure Monitoring and Alerting

Set up Azure Monitor alerts to notify your team of pipeline failures or performance degradation. In the Azure portal, navigate to your Data Factory resource and select “Alerts” under Monitoring.

Create an alert rule with the following configuration:

• Scope: Your Data Factory instance
• Condition: Pipeline run failed OR Activity run failed
• Action: Send email notification to your data engineering team
• Severity: High

Additionally, configure a dashboard in Azure Monitor that displays:

• Daily pipeline execution status
• Data volume processed (rows, gigabytes)
• Average pipeline duration
• Error rates and failure reasons

This dashboard provides visibility into your data platform’s health and helps identify performance trends or issues requiring intervention.

Scaling Your Platform for Production

Once your initial pipeline is operational, scale your platform to support multiple data sources and use cases. The Microsoft Fabric lakehouse end-to-end scenario documentation provides detailed guidance on advanced patterns.

Step 19: Create Additional Pipelines for Multiple Data Sources

Repeat the pipeline creation process for each data source in your organisation. For a public sector environment, this might include:

• Financial systems (budgets, expenditures, payroll)
• HR systems (staffing, leave, performance)
• Operational systems (service delivery metrics, customer interactions)
• External data sources (census data, economic indicators, regulatory information)

Create separate pipelines for each source, maintaining clear naming conventions and documentation. This modularity simplifies maintenance and enables independent scheduling based on each source’s update frequency.

Step 20: Implement a Medallion Architecture

As your data platform matures, implement a medallion (bronze-silver-gold) architecture that separates raw, conformed, and business-ready data:

• Bronze Layer: Raw data from source systems, loaded as-is into your lakehouse
• Silver Layer: Conformed data with quality checks, deduplication, and standardisation applied
• Gold Layer: Business-ready data optimised for specific analytics use cases

Create separate tables or schemas for each layer. Configure your pipelines to move data through these layers, applying increasingly sophisticated transformations at each stage.

This architecture provides several benefits for public sector organisations:

• Auditability: Data lineage is clear, supporting compliance and investigation requirements
• Reusability: Silver and Gold layer tables can be used across multiple analytics initiatives
• Flexibility: New use cases can be built from Silver layer data without re-processing raw data
• Performance: Gold layer tables are optimised for specific queries, improving dashboard and report responsiveness

Troubleshooting Common Issues

During implementation, you may encounter common challenges. This section provides solutions for the most frequent issues.

Issue: “Authentication failed” when connecting Data Factory to Storage Account

This typically occurs when the storage account key is incorrect or has been regenerated. Solution:

1. In the Azure portal, navigate to your storage account
2. Select “Access keys” under Settings
3. Copy the current key (Key 1 or Key 2)
4. In Data Factory, edit the linked service and paste the updated key
5. Click “Test connection” to verify

Issue: “Permission denied” when writing data to Fabric lakehouse

This occurs when your Data Factory’s managed identity lacks permissions to write to your Fabric workspace storage. Solution:

1. In the Azure portal, navigate to your storage account
2. Select “Access Control (IAM)”
3. Click “Add role assignment”
4. Select “Storage Blob Data Contributor” role
5. Search for your Data Factory’s managed identity and add it
6. Retry your pipeline

Issue: Pipeline runs slowly or times out

Large data transfers or complex transformations may exceed default timeouts. Solution:

1. In your Data Factory pipeline, select the slow activity
2. In the Settings tab, increase “Timeout” to 24 hours
3. Consider breaking large transfers into smaller batches using a ForEach loop
4. Ensure your Data Factory instance is using appropriate compute resources (larger integration runtime)

Issue: Data appears in Files folder but not Tables folder

This occurs when your sink dataset is configured for the Files folder instead of Tables. Solution:

1. Edit your Copy activity’s Sink configuration
2. Verify the output path includes “/Tables/” prefix
3. Ensure the output format is Parquet or Delta format
4. Republish and re-run your pipeline

Issue: SQL queries against lakehouse tables return “table not found” error

This occurs when tables haven’t been properly registered in the SQL analytics endpoint. Solution:

1. In your lakehouse, refresh the SQL analytics endpoint
2. Verify tables appear in the explorer pane
3. If tables don’t appear, try accessing them via the full path: [dbo].[TableName]
4. Ensure you’re connected to the correct lakehouse and workspace

Best Practices for Public Sector Deployments

Public sector organisations have unique requirements around compliance, security, and accountability. Implement these best practices throughout your deployment:

Data Residency and Sovereignty

Ensure all resources are deployed in Australian regions (Australia East or Australia Southeast). Configure Azure policies to prevent accidental deployment of resources outside Australia. For highly sensitive data, consider using Azure Dedicated Host to ensure physical isolation.

Audit Logging and Compliance

Enable comprehensive audit logging for all data access and modifications. Configure Azure Monitor to capture:

• All Data Factory pipeline executions
• All SQL queries executed against your lakehouse
• All data modifications in your storage account
• User access to your Fabric workspace

Retain audit logs for at least 7 years to meet Australian Privacy Principles and government record-keeping requirements.

Change Management and Testing

Implement separate development, testing, and production environments. Never directly modify production pipelines; instead, test changes in development, promote to testing for validation, and only then deploy to production.

Document all pipeline changes in a change log, including the reason for the change, who approved it, and when it was deployed. This documentation supports compliance audits and helps troubleshoot issues.

Cost Optimisation

Public sector organisations operate under budget constraints. Optimise your deployment costs by:

• Scheduling pipelines during off-peak hours
• Using appropriate compute resources (don’t over-provision)
• Archiving historical data to cold storage
• Implementing data retention policies that delete data no longer needed

Monitor your costs monthly and adjust your infrastructure based on actual usage patterns.

Conclusion and Next Steps

You’ve now successfully deployed a production-ready Microsoft Fabric lakehouse integrated with Azure Data Factory, complete with governance, security, and monitoring capabilities. This foundation supports advanced analytics, AI initiatives, and data-driven decision-making across your public sector organisation.

Next steps for your organisation include:

1. Expand Data Sources: Integrate additional systems and data sources using the patterns established in this tutorial
2. Build Analytics Solutions: Create Power BI dashboards and reports using your Gold layer data
3. Implement AI Models: Use Azure Databricks or Azure Machine Learning to build predictive models on your lakehouse data
4. Establish Data Governance: Work with your governance team to define data policies, classifications, and access controls
5. Train Your Team: Ensure your data engineering and analytics teams understand the platform architecture and best practices

Agile Insights specialises in helping Australian public sector organisations implement enterprise data and analytics solutions using Microsoft technologies. Our team of Microsoft-certified architects and engineers can accelerate your journey, providing guidance on architecture, implementation, and governance tailored to your specific requirements.

For additional support and advanced use cases, explore Microsoft’s comprehensive documentation on Fabric lakehouse architecture and implementation patterns, which provides detailed technical guidance for enterprise deployments.

Your public sector analytics platform is now ready to deliver actionable insights, improve operational efficiency, and support evidence-based decision-making across your organisation.