Data Analytics Platform: Azure Synapse, Power BI, Data Factory, and ML Integration

"blobTypes": ["blockBlob"], "prefixMatch": ["bronze/"] } } }``` ] }

## Phase 2: Azure Data Factory ETL Pipelines

![Phase 2: Azure Data Factory ETL Pipelines](/images/articles/deep-dive/2025-06-23-data-analytics-platform-synapse-power-bi-data-factory-ml-ctx-1.svg)

**Pipeline Definition (JSON):**





```json
{
  "name": "IngestSalesData",
  "properties": {
```sql
"activities": [
  {
    "name": "CopyFromSQL",
    "type": "Copy",
    "inputs": [
      {
        "referenceName": "SqlServerSalesTable",
        "type": "DatasetReference"
      }
    ],
    "outputs": [
      {
        "referenceName": "DataLakeBronze",
        "type": "DatasetReference"
      }
    ],
    "typeProperties": {
      "source": {
        "type": "SqlSource",
        "sqlReaderQuery": "SELECT * FROM Sales WHERE ModifiedDate > '@{pipeline().parameters.lastRunTime}'"
      },
      "sink": {
        "type": "ParquetSink",
        "storeSettings": {
          "type": "AzureBlobFSWriteSettings",
          "copyBehavior": "PreserveHierarchy"
        },
        "formatSettings": {
          "type": "ParquetWriteSettings"
        }
      },
      "enableStaging": false,
      "parallelCopies": 4
    }
  },
  {
    "name": "TriggerSynapseNotebook",
    "type": "SynapseNotebook",
    "dependsOn": [
      {
        "activity": "CopyFromSQL",
        "dependencyConditions": ["Succeeded"]
      }
    ],
    "typeProperties": {
      "notebook": {
        "referenceName": "TransformSalesData",
        "type": "NotebookReference"
      },
      "parameters": {
        "inputPath": "bronze/sales",
        "outputPath": "silver/sales"
      }
    }
  }
],
"parameters": {
  "lastRunTime": {
    "type": "String",
    "defaultValue": "2025-01-01T00:00:00Z"
  }
}```
  }
}

Azure CLI Deployment:

## Create Data Factory
az datafactory create \
  --resource-group rg-analytics-platform \
  --factory-name adf-analytics-platform \
  --location eastus





## Create linked service for SQL Server

![Create linked service for SQL Server](/images/articles/deep-dive/2025-06-23-data-analytics-platform-synapse-power-bi-data-factory-ml-ctx-2.svg)
az datafactory linked-service create \
  --resource-group rg-analytics-platform \
  --factory-name adf-analytics-platform \
  --name SqlServerLinkedService \
  --properties @linked-service-sql.json





## Create pipeline
az datafactory pipeline create \
  --resource-group rg-analytics-platform \
  --factory-name adf-analytics-platform \
  --name IngestSalesData \
  --pipeline @pipeline-ingest-sales.json

Phase 3: Azure Synapse Analytics Workspace

Bicep Deployment:

resource synapse 'Microsoft.Synapse/workspaces@2021-06-01' = {
  name: 'synapse-analytics-platform'
  location: location
  identity: {
```yaml
type: 'SystemAssigned'```
  }
  properties: {
```yaml
defaultDataLakeStorage: {
  accountUrl: 'https://${storageAccount.name}.dfs.core.windows.net'
  filesystem: 'synapse'
}
sqlAdministratorLogin: 'sqladmin'
sqlAdministratorLoginPassword: sqlAdminPassword
managedResourceGroupName: 'synapse-managed-rg'```
  }
}

resource sparkPool 'Microsoft.Synapse/workspaces/bigDataPools@2021-06-01' = {
  parent: synapse
  name: 'sparkpool'
  location: location
  properties: {
```yaml
nodeSize: 'Small'
nodeSizeFamily: 'MemoryOptimized'
autoScale: {
  enabled: true
  minNodeCount: 3
  maxNodeCount: 10
}
autoPause: {
  enabled: true
  delayInMinutes: 15
}
sparkVersion: '3.3'
libraryRequirements: {
  content: loadTextContent('requirements.txt')
  filename: 'requirements.txt'
}```
  }
}

resource dedicatedPool 'Microsoft.Synapse/workspaces/sqlPools@2021-06-01' = {
  parent: synapse
  name: 'sqldw'
  location: location
  sku: {
```yaml
name: 'DW100c'```
  }
  properties: {
```yaml
collation: 'SQL_Latin1_General_CP1_CI_AS'
maxSizeBytes: 263882790666240
createMode: 'Default'```
  }
}

Synapse Notebook (PySpark Data Transformation):

## Cell 1: Import libraries
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, to_date, year, month, sum, avg
from pyspark.sql.window import Window





spark = SparkSession.builder.appName("SalesTransformation").getOrCreate()

## Cell 2: Read from Bronze layer
input_path = "abfss://bronze@stadatalake123456.dfs.core.windows.net/sales/"
df = spark.read.parquet(input_path)





## Cell 3: Data quality checks
print(f"Initial row count: {df.count()}")
print("Schema:")
df.printSchema()





## Check for nulls
null_counts = df.select([
```text
sum(col(c).isNull().cast("int")).alias(c) for c in df.columns```
])
null_counts.show()





## Cell 4: Data transformations
df_clean = df \
```text
.filter(col("Amount") > 0) \
.withColumn("OrderDate", to_date(col("OrderDate"))) \
.withColumn("Year", year(col("OrderDate"))) \
.withColumn("Month", month(col("OrderDate"))) \
.dropDuplicates(["OrderId"])

Cell 5: Aggregations

monthly_sales = df_clean.groupBy("Year", "Month", "ProductCategory") \

.agg(
    sum("Amount").alias("TotalSales"),
    avg("Amount").alias("AvgOrderValue"),
    count("OrderId").alias("OrderCount")
) \
.orderBy("Year", "Month", "ProductCategory")

Cell 6: Window functions for trends

window_spec = Window.partitionBy("ProductCategory").orderBy("Year", "Month")

sales_with_trends = monthly_sales.withColumn(

"PrevMonthSales",
lag("TotalSales", 1).over(window_spec)```
).withColumn(
```text
"SalesGrowth",
(col("TotalSales") - col("PrevMonthSales")) / col("PrevMonthSales") * 100```
)

## Cell 7: Write to Silver layer
output_path = "abfss://silver@stadatalake123456.dfs.core.windows.net/sales/"
sales_with_trends.write.mode("overwrite").partitionBy("Year", "Month").parquet(output_path)





print(f"Processed {sales_with_trends.count()} records to Silver layer")

Phase 4: Dedicated SQL Pool (Data Warehouse)

Star Schema Design:

-- Dimension: Date
CREATE TABLE dbo.DimDate (
```text
DateKey INT PRIMARY KEY,
Date DATE NOT NULL,
Year INT NOT NULL,
Quarter INT NOT NULL,
Month INT NOT NULL,
MonthName VARCHAR(20) NOT NULL,
DayOfWeek INT NOT NULL,
DayName VARCHAR(20) NOT NULL,
IsWeekend BIT NOT NULL,
IsHoliday BIT NOT NULL```
)
WITH (DISTRIBUTION = REPLICATE, CLUSTERED COLUMNSTORE INDEX);

-- Dimension: Product
CREATE TABLE dbo.DimProduct (
```text
ProductKey INT IDENTITY(1,1) PRIMARY KEY,
ProductId VARCHAR(50) NOT NULL,
ProductName VARCHAR(200) NOT NULL,
Category VARCHAR(100) NOT NULL,
SubCategory VARCHAR(100),
UnitPrice DECIMAL(18, 2) NOT NULL,
EffectiveDate DATE NOT NULL,
EndDate DATE```
)
WITH (DISTRIBUTION = REPLICATE, CLUSTERED COLUMNSTORE INDEX);

-- Dimension: Customer
CREATE TABLE dbo.DimCustomer (
```text
CustomerKey INT IDENTITY(1,1) PRIMARY KEY,
CustomerId VARCHAR(50) NOT NULL,
CustomerName VARCHAR(200) NOT NULL,
Segment VARCHAR(50),
Region VARCHAR(100),
Country VARCHAR(100)```
)
WITH (DISTRIBUTION = REPLICATE, CLUSTERED COLUMNSTORE INDEX);

-- Fact: Sales
CREATE TABLE dbo.FactSales (
```text
SalesKey BIGINT IDENTITY(1,1),
DateKey INT NOT NULL,
ProductKey INT NOT NULL,
CustomerKey INT NOT NULL,
OrderId VARCHAR(100) NOT NULL,
Quantity INT NOT NULL,
UnitPrice DECIMAL(18, 2) NOT NULL,


TotalAmount DECIMAL(18, 2) NOT NULL,
Discount DECIMAL(18, 2),
NetAmount DECIMAL(18, 2) NOT NULL,
CONSTRAINT PK_FactSales PRIMARY KEY NONCLUSTERED (SalesKey) NOT ENFORCED,
CONSTRAINT FK_FactSales_Date FOREIGN KEY (DateKey) REFERENCES dbo.DimDate(DateKey) NOT ENFORCED,
CONSTRAINT FK_FactSales_Product FOREIGN KEY (ProductKey) REFERENCES dbo.DimProduct(ProductKey) NOT ENFORCED,
CONSTRAINT FK_FactSales_Customer FOREIGN KEY (CustomerKey) REFERENCES dbo.DimCustomer(CustomerKey) NOT ENFORCED```
)
WITH (
```text
DISTRIBUTION = HASH(ProductKey),
CLUSTERED COLUMNSTORE INDEX```
);

-- Load data from Silver layer
COPY INTO dbo.FactSales (DateKey, ProductKey, CustomerKey, OrderId, Quantity, UnitPrice, TotalAmount, NetAmount)
FROM 'https://stadatalake123456.dfs.core.windows.net/silver/sales/'
WITH (
```text
FILE_TYPE = 'PARQUET',
CREDENTIAL = (IDENTITY = 'Managed Identity')```
);

Performance Optimization:

-- Create statistics
CREATE STATISTICS stat_sales_date ON dbo.FactSales(DateKey);
CREATE STATISTICS stat_sales_product ON dbo.FactSales(ProductKey);
CREATE STATISTICS stat_sales_customer ON dbo.FactSales(CustomerKey);

-- Materialized view for common aggregations
CREATE MATERIALIZED VIEW dbo.MV_MonthlySalesSummary
WITH (DISTRIBUTION = HASH(ProductKey))
AS
SELECT 
```text
d.Year,
d.Month,
p.Category,
p.SubCategory,
SUM(f.NetAmount) AS TotalSales,
SUM(f.Quantity) AS TotalQuantity,
COUNT(DISTINCT f.OrderId) AS OrderCount```
FROM dbo.FactSales f
INNER JOIN dbo.DimDate d ON f.DateKey = d.DateKey
INNER JOIN dbo.DimProduct p ON f.ProductKey = p.ProductKey
GROUP BY d.Year, d.Month, p.Category, p.SubCategory;

Phase 5: Azure Machine Learning Integration

Train Model in Synapse Spark:

from azureml.core import Workspace, Dataset, Experiment, Model
from azureml.train.automl import AutoMLConfig
from pyspark.sql import SparkSession

## Connect to Azure ML workspace
ws = Workspace.from_config()





## Load data from Synapse
spark = SparkSession.builder.getOrCreate()
df = spark.read.parquet("abfss://silver@stadatalake123456.dfs.core.windows.net/sales/")
pandas_df = df.toPandas()





## Feature engineering
X = pandas_df[['ProductCategory', 'Region', 'Month', 'DayOfWeek', 'PrevMonthSales']]
y = pandas_df['TotalSales']





## AutoML configuration
automl_config = AutoMLConfig(
```text
task='regression',
primary_metric='normalized_root_mean_squared_error',
training_data=X,
label_column_name='TotalSales',
n_cross_validations=5,
enable_early_stopping=True,
experiment_timeout_hours=1,
max_concurrent_iterations=4```
)





## Run experiment
experiment = Experiment(ws, 'sales-forecast')
run = experiment.submit(automl_config, show_output=True)





## Register best model
best_run, fitted_model = run.get_output()
model = best_run.register_model(
```text
model_name='sales-forecast-model',
model_path='outputs/model.pkl',
tags={'type': 'automl', 'framework': 'sklearn'}```
)

Deploy Model as Synapse SQL Function:

-- Create external table for model predictions
CREATE EXTERNAL TABLE [dbo].[SalesForecast]
(
```text
ProductCategory VARCHAR(100),
Region VARCHAR(100),
ForecastMonth INT,
PredictedSales DECIMAL(18, 2)```
)
WITH (
```text
LOCATION = '/forecasts/',
DATA_SOURCE = DataLakeSource,
FILE_FORMAT = ParquetFormat```
);

-- Scoring stored procedure
CREATE PROCEDURE dbo.ScoreSalesForecast
AS
BEGIN
```sql
-- Call Azure ML endpoint via REST API
DECLARE @url NVARCHAR(MAX) = 'https://<workspace>.azureml.net/api/v1/service/<endpoint>/score';
DECLARE @headers NVARCHAR(MAX) = '{"Authorization": "Bearer <token>"}';
DECLARE @payload NVARCHAR(MAX) = (
    SELECT 
        ProductCategory,
        Region,
        MONTH(DATEADD(MONTH, 1, GETDATE())) AS ForecastMonth
    FROM dbo.DimProduct
    CROSS JOIN (SELECT DISTINCT Region FROM dbo.DimCustomer) c
    FOR JSON AUTO
);

-- Execute prediction (pseudo-code, requires Azure ML SDK)
-- Results stored in SalesForecast external table```
END;

Phase 6: Power BI Integration

DirectQuery Connection:

// Measures.dax

// Total Sales
TotalSales = SUM(FactSales[NetAmount])

// Sales vs Target
SalesVsTarget = 
VAR CurrentSales = [TotalSales]
VAR TargetSales = [TotalTarget]
RETURN
```text
DIVIDE(CurrentSales - TargetSales, TargetSales, 0)

// Year-over-Year Growth YoYGrowth = VAR CurrentYearSales = [TotalSales] VAR PreviousYearSales =

CALCULATE(
    [TotalSales],
    DATEADD(DimDate[Date], -1, YEAR)
)```
RETURN
```text
DIVIDE(CurrentYearSales - PreviousYearSales, PreviousYearSales, 0)

// Running Total RunningTotal = CALCULATE(

[TotalSales],
FILTER(
    ALLSELECTED(DimDate[Date]),
    DimDate[Date] <= MAX(DimDate[Date])
)```
)

Power BI Deployment (PowerShell):

## Install Power BI Management module
Install-Module -Name MicrosoftPowerBIMgmt -Force





## Connect to Power BI service
Connect-PowerBIServiceAccount





## Publish report
Publish-PowerBIFile -Path "C:\Reports\SalesDashboard.pbix" -WorkspaceId "workspace-guid" -ConflictAction CreateOrOverwrite





## Configure scheduled refresh
$refreshSchedule = @{
```text
days = @("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
times = @("06:00", "18:00")
enabled = $true```
}
Set-PowerBIDatasetRefreshSchedule -DatasetId "dataset-guid" -RefreshSchedule $refreshSchedule

Expected output:

Package installed successfully.

Phase 7: Real-Time Streaming Analytics

Event Hubs Ingestion:

## Create Event Hubs namespace
az eventhubs namespace create \
  --name eh-analytics-stream \
  --resource-group rg-analytics-platform \
  --location eastus \
  --sku Standard





## Create event hub
az eventhubs eventhub create \
  --name clickstream \
  --namespace-name eh-analytics-stream \
  --resource-group rg-analytics-platform \
  --partition-count 4 \
  --message-retention 1

Stream Analytics Job:

-- Input: Event Hubs
-- Output: Synapse SQL Pool

SELECT 
```text
System.Timestamp() AS WindowEnd,
UserId,
ProductId,
COUNT(*) AS ClickCount,
AVG(TimeOnPage) AS AvgTimeOnPage```
INTO
```text
[SynapseOutput]```
FROM
```text
[EventHubInput] TIMESTAMP BY EventTime```
GROUP BY
```text
UserId,
ProductId,
TumblingWindow(minute, 5)```
HAVING
```text
COUNT(*) > 10;

## Monitoring & Optimization

**Synapse Query Performance:**





```sql
-- Identify slow queries
SELECT 
```text
request_id,
session_id,
start_time,
total_elapsed_time,
command,
status```
FROM sys.dm_pdw_exec_requests
WHERE total_elapsed_time > 60000 -- queries > 1 minute
ORDER BY total_elapsed_time DESC;

-- Table distribution analysis
SELECT 
```text
t.name AS TableName,
tp.distribution_policy_desc AS Distribution,
COUNT(DISTINCT p.partition_number) AS PartitionCount```
FROM sys.tables t
INNER JOIN sys.pdw_table_distribution_properties tp ON t.object_id = tp.object_id
INNER JOIN sys.partitions p ON t.object_id = p.object_id
GROUP BY t.name, tp.distribution_policy_desc;

KQL Monitoring Queries:

// Data Factory pipeline failures
ADFPipelineRun
| where TimeGenerated > ago(24h)
| where Status == "Failed"
| summarize FailureCount = count() by PipelineName, bin(TimeGenerated, 1h)
| render timechart

// Synapse Spark pool utilization
SynapseBigDataPoolApplicationsEnded
| where TimeGenerated > ago(7d)
| summarize 
```text
AvgCores = avg(AllocatedCores),
AvgMemoryGB = avg(AllocatedMemory) / 1024
by bin(TimeGenerated, 1h)```
| render timechart

// Power BI refresh duration
PowerBIDatasetsWorkspace
| where OperationName == "RefreshDataset"
| extend DurationMinutes = DurationMs / 60000
| summarize P95Duration = percentile(DurationMinutes, 95) by DatasetName
| order by P95Duration desc

Best Practices

• Medallion Architecture: Organize data in Bronze (raw), Silver (cleansed), Gold (aggregated) layers
• Incremental Loads: Use watermarks for efficient incremental data ingestion
• Data Partitioning: Partition large tables by date for query performance
• Materialized Views: Pre-aggregate common queries for faster dashboard loads
• Cost Optimization: Pause Synapse pools when not in use; use serverless SQL for ad-hoc queries
• Data Governance: Implement Purview for lineage, classification, and access policies

Troubleshooting

Issue: Synapse query times out
Solution: Check table distribution; create statistics; use result set caching; scale up DWU

Issue: Power BI refresh fails
Solution: Verify Synapse SQL pool is running; check firewall rules; validate credentials

Issue: Data Factory pipeline slow
Solution: Increase parallelCopies; enable staging; optimize source query with indexes

Architecture Decision and Tradeoffs

When designing integrated solutions solutions with Azure + Power Platform, consider these key architectural trade-offs:

Recommendation: Start with the managed approach for most workloads and move to custom only when specific requirements demand it.

Validation and Versioning

• Last validated: April 2026
• Validate examples against your tenant, region, and SKU constraints before production rollout.
• Keep module, CLI, and SDK versions pinned in automation pipelines and review quarterly.

Security and Governance Considerations

• Apply least-privilege access using RBAC roles and just-in-time elevation for admin tasks.
• Store secrets in managed secret stores and avoid embedding credentials in scripts or source files.
• Enable audit logging, data protection policies, and periodic access reviews for regulated workloads.

Cost and Performance Notes

• Define budgets and alerts, then monitor usage and cost trends continuously after go-live.
• Baseline performance with synthetic and real-user checks before and after major changes.
• Scale resources with measured thresholds and revisit sizing after usage pattern changes.

Official Microsoft References

• https://learn.microsoft.com/azure/architecture/
• https://learn.microsoft.com/azure/well-architected/
• https://learn.microsoft.com/power-platform/guidance/

Public Examples from Official Sources

• These examples are sourced from official public Microsoft documentation and sample repositories.
• Documentation examples: https://learn.microsoft.com/azure/well-architected/
• Sample repositories: https://github.com/Azure/ArchitectureCenter
• Prefer adapting these examples to your tenant, subscriptions, and governance requirements before production use.

Key Takeaways

• Azure Synapse unifies data warehousing and big data analytics
• Power BI provides interactive self-service business intelligence
• Data Factory orchestrates complex ETL workflows at scale
• Azure ML integration enables predictive analytics within data pipelines
• Real-time streaming complements batch processing for complete analytics

Next Steps

• Implement data quality validation with Great Expectations
• Add data versioning with Delta Lake on Synapse
• Deploy Synapse Link for real-time operational analytics
• Explore Power BI embedded analytics for application integration

Additional Resources

• Azure Synapse Analytics
• Data Factory Documentation
• Power BI Documentation
• Azure Machine Learning

Extended Architecture Patterns

Domain Decomposition

Segment data products by business domain (Sales, Finance, SupplyChain, CustomerExperience). Each domain publishes certified semantic model + contract; cross-domain joins happen through shared conformed dimensions (Date, Product, Geography) to minimize coupling.

Tagging & Cost Attribution

Mandatory tags: Environment, Domain, DataTier, Owner, CostCenter, Sensitivity. Enforced via Azure Policy (deny effect) to ensure downstream cost dashboards and governance filters remain accurate.

Semantic Modeling Enhancements

• Shared “thin” Power BI reports consuming certified datasets reduce duplication.
• Calculation groups consolidate time intelligence (YTD/QTD/MTD) and currency conversions.
• Hybrid tables (import history + DirectQuery recent) enable near real-time KPIs.

Performance Engineering Summary

Observability & Telemetry

Inject a CorrelationId in pipeline parameters → propagate through Spark logs → set SQL session context (SET CONTEXT_INFO) → include in Power BI queries (custom connector header) for end-to-end traceability.

FinOps Optimization Actions

Daily anomaly detection compares spend vs trailing 30‑day mean; alert at >2× deviation.

ML Operationalization & Drift Handling

Extended Troubleshooting Scenarios

Governance Operating Cadence

• Weekly Ops: pipeline health, quality exceptions, cost anomalies.
• Monthly Data Council: certify datasets, approve contract changes, retire unused assets.
• Quarterly Strategy: modernization backlog (Fabric adoption, streaming expansion, ML roadmap).

Best Practices Cheat Sheet

Conclusion

An effective analytics platform behaves as a managed product—architecturally layered, contract-driven, observable, cost-aware, and ML-enabled. By institutionalizing these practices early you convert reactive firefighting into proactive evolution, enabling rapid, trustworthy insights at scale while maintaining compliance and financial efficiency.

Ready to unlock insights from your data?