Cloud Computing

Architecting an E2E Clinical Data Pipeline using Azure Stack, Power BI

Modern healthcare data engineering demands robust, secure, and scalable architectures to transform raw patient data into structured, actionable intelligence. In this post, I will walk you through a production-ready cloud architecture designed to ingest, transform, clean, and visualize clinical data using the Microsoft Azure data ecosystem.

The High-Level Architecture

1. Data Source: Raw text files are tracked in a git repository.

2. Ingestion & Raw Storage: An Azure Data Factory (ADF) pipeline pulls files via HTTP and dumps them into Azure Data Lake Storage (ADLS Gen2).

3. Data Transformation: ADF Data Flows process the files to clean missing parameters, perform schema mapping, and optimize attributes.

4. Structured Storage: The enriched data is loaded to an optimized Azure SQL Database table.

5. Visualization: Power BI Desktop establishes a connection to Azure SQL to render a live analytical dashboard for decision-makers

Phase 1: Environment Setup & Infrastructure Provisioning

Step 1: Establish the Version Control Repository

Before establishing cloud connections, upload your source clinical dataset (patients.csv) directly to a repository on GitHub. This acts as our persistent HTTP-accessible source layer.

Step 2: Creating the Resource Group in Azure

To simplify infrastructure life-cycle, log into the Azure Portal and provision a dedicated Resource Group. This acts as a single logical container holding every cloud service needed for this project.

Step 3 & 4: Provisioning the Storage and Processing Layer

Next, create the orchestration and raw storage engines:

1. Azure Data Factory V2: Create a workspace (pharmahub-adf) to build our pipelines.

2. Azure Data Lake Storage Gen2: Provision a standard-tier general-purpose v2 storage account (pharmahubdatalake) with locally redundant storage (LRS) to manage files efficiently.

Step 5 & 6: Deploying the Target Azure SQL Database

Deploy an Azure SQL Database (PharmaHUB) hosted on a new logical server (pharmaserver).

• Workload Environment: Development (Serverless compute tier to reduce cost).

• Networking Configuration: Enable Public endpoint access and ensure the rule "Allow Azure services and resources to access this server" is checked to grant Azure Data Factory an uninterrupted path to load data.

After deployment, open Azure Query editor and execute a test to confirm system accessibility:

SQL

SELECT * FROM sys.objects

Phase 2: Configuring Ingestion Pipelines in ADF

Step 7: Organizing the Data Lake Containers

Navigate into your storage account browser. To isolate raw landing zones from clean production zones, create two distinct containers {rawdata, processeddata}

Step 8: Building Linked Services in ADF Studio

Launch Azure Data Factory Studio. Go to the Manage hub to establish connection strings (Linked Services) to your data stores:

1. HTTP Source Connector: Create a source named Githttpsrc. Set the Base URL pointing to GitHub's raw address endpoint (https://raw.githubusercontent.com/) using Anonymous authentication.

2. ADLS Gen2 Sink Connector: Create linkserviceAzureDLS linking securely to pharmahubdatalake using an Account key verification strategy.

Step 10 & 11: Mapping Ingestion Datasets & Copy Activities

In ADF's Author tab, build out two unique datasets to interact with files:

• Source Dataset (ds_rawpatientdata_source): Linked to Githttpsrc, passing the Relative URL corresponding to your GitHub file path (.../main/Patients.csv).

• Sink Dataset (ds_rawpatient_adls): Linked to linkserviceAzureDLS, setting destination path to rawdata/patientrecords/Patients_record.csv with First row as header checked.

 

create a new pipeline called ps_dataingestion. Drag and drop a Copy Data activity into the canvas. Map the source dataset to your HTTP stream and the sink dataset to your raw data lake path.

Step 12: Pipeline Validation

Click Debug to execute the live copy sequence. Once completed, inspect your Azure Data Lake container path (rawdata/patientrecords/) to confirm the Patients_record.csv file landed perfectly. Click Publish All to save your pipeline changes.

Phase 3: Data Cleaning and Transformation

Step 13 & 14: Developing Data Flows for Transformation

Raw files often contain corrupted fields. In ADF Studio, create a new Data Flow named df_dataflow.

1. Source Stream (patientdatasource): Add an ingestion source linking directly to the data lake text file. Under the projection tab, allow schema drift and detect structural data types.

2. Filter Transformation (filternullterritory): Clinical metrics must be complete. Add a filter block utilizing column expressions to completely discard fields where territory_code returns missing or NULL characters.

3. Select Transformation (selectrequiredfield): Trim processing overhead by selecting only relevant columns from the filtered dataset, dropping unneeded diagnostic attributes.

4. Sink Dataflow Destination: Conclude the flow by mapping outputs directly into your processeddata destination bucket.

Step 15, 16 & 17: Pushing Processed Data to Azure SQL

Create an empty target table inside your SQL database to match your clean data flow output schema.

Create a dedicated pipeline that takes your cleaned file from processeddata and uses a final Copy Data activity to append rows directly into your production SQL table linked service. Execute Debug once more, verify row counts using standard SQL SELECT statements, and click Publish All.

Phase 4: Business Intelligence & Power BI Reporting

Step 21 & 22: Generating Clinical Dashboards

With our automated pipeline operational, we can build the consumption layer:

1. Open Power BI Desktop, start with a report, and select Get Data -> Azure SQL Database.

2. Provide your server credentials (*.database.windows.net) and select the clean patient table.

3. Choose your storage mode (DirectQuery or Import) to download the processed tables.

Once loaded, your column fields will appear in the right-hand panel, allowing you to instantly build charts tracking clinical demographics, geographical concentrations, and treatment progress timelines.

Conclusion

You have successfully designed a fully automated enterprise data pipeline! Clinical tracking data is now collected from remote sources, securely staged in a data lake, systematically transformed via visual transformations without code, safely recorded in a structured database, and visually delivered directly into modern reporting tools.