DEV Community: Sid Redjini

A Practical FinOps Pipeline for Azure SQL: From Azure CLI to Power BI Without Log Analytics

Sid Redjini — Thu, 24 Jul 2025 16:47:31 +0000

Azure SQL Metrics Export – A FinOps Pipeline Without Log Analytics

As a FinOps consultant working with mid-sized and large organizations, I often land in complex environments where access to production systems, logs, or advanced monitoring tools is limited ; especially for external contractors. Despite that, I’m expected to produce actionable insights fast.

This article explains how I built a self-contained metrics pipeline — using only Azure CLI and Python ; to collect, transform, and prepare Azure SQL performance data for analysis, without relying on premium monitoring features or costly third-party tools.

This method is part of a broader effort to evaluate whether DTU-based databases could be migrated to more cost-effective vCore serverless models : a scenario many organizations face when scaling cloud usage.

In this article, I also share my hands-on experience with performance rationalization projects, where these metrics have been used to justify migrating SQL databases from DTU pricing to the vCore serverless model. The specific choice of metrics and aggregation levels discussed throughout reflects real-world scenarios where cost optimization decisions are based on a blend of average usage patterns and performance peaks.

This guide will show you how to:

Collect Azure SQL performance metrics via the Azure Monitor API using Bash scripts.
Transform and structure this data using Python into a CSV aligned with Azure billing reports.
Build a cost-aware monitoring strategy integrated with tools like Power BI, even without Log Analytics.

You can find the full source code on GitHub:

sidmechant/azure-sql-metrics-finops

1. Why This Approach
2. Architecture Overview
3. Bash Script: Exporting Metrics
4. Sample Output (Raw JSON)
5. Python Script: Transforming Metrics
6. Practical Extensions
7. Conclusion

1. Why This Approach

Why These Metrics Matter in FinOps

The selection of specific metrics in this guide is driven by real-world FinOps objectives:

CPU Percent helps evaluate compute saturation and identify chronic underutilization or periodic spikes.

DTU Consumption Percent provides a normalized measure of the overall capacity consumption in DTU-based databases, useful when comparing across different service tiers.

Storage Percent tracks how close databases are to their storage limits, which can influence decisions on scaling or changing service tiers.

Log Write Percent and Workers Percent are valuable for detecting backend bottlenecks and query concurrency issues, often overlooked in cost reports.

Deadlock Count serves as a performance health indicator, signaling contention issues that may require scaling up or query optimization.

This carefully selected set of metrics supports actionable FinOps decisions such as:

Justifying a shift from DTU to vCore or serverless models based on consistent underutilization.
Detecting high-peak workloads that may be better suited for scaling up or elasticity mechanisms like serverless compute.
Aligning technical performance data with billing reports for transparent reporting to stakeholders.

No additional costs: It uses native Azure CLI APIs, avoiding Log Analytics and other premium services.

Multi-subscription support: The script collects data across multiple Azure subscriptions in a single run.

Customizable output: Flexible configuration for metrics selection, aggregation type, and data retention period.

Integration-ready: The data output format aligns with Azure billing reports, simplifying cross-analysis between usage and costs.

This is not just a cost-avoidance strategy , it's also about autonomy. In large organizations, access requests can take days or weeks to be approved. By building my own collection pipeline through native CLI APIs, I removed dependency on internal platform teams while ensuring full reproducibility of the data used in decision-making.

This approach is ideal for FinOps practitioners who want to monitor real usage patterns to guide cost optimization efforts.

2. Architecture Overview

Here is the lightweight FinOps pipeline I used for this project:

This structure allows for:

Platform-agnostic data collection (Bash / Azure CLI)
Reproducible transformation via Python
Output that matches billing data structure
Easy integration into tools like Power BI

3. Bash Script: Exporting Metrics

View the Bash script on GitHub:

scripts/export_metrics.sh

This script collects Azure SQL Database metrics across multiple subscriptions while handling API rate limits and excluding irrelevant databases (e.g., vCore or 'master'). It is designed to be flexible, adaptable, and efficient for large-scale environments.

Key Features

Multi-subscription handling: Automatically iterates through all active Azure subscriptions using the Azure CLI, ensuring visibility across environments (e.g., development, staging, production). This is crucial in FinOps to avoid isolated analysis limited to a single subscription.

Targeted database selection: Filters out irrelevant databases like master and optionally excludes databases running on vCore models. This prevents wasting API calls on databases where DTU-specific metrics are not applicable.

Custom metric selection: Allows users to define exactly which metrics to collect via the METRICS variable. For my own analyses, I specifically select cpu_percent, dtu_consumption_percent, and storage_percent because they provide a holistic view of compute and storage pressure. I also include log_write_percent, workers_percent, and deadlock to assess query workload saturation and contention issues. This approach is tailored to justify decisions like shifting from DTU to vCore (especially serverless), based on actual workload patterns.

Granularity flexibility: Supports different time intervals via the INTERVAL parameter. Common choices are PT5M (5 minutes), PT30M (30 minutes), PT1H (1 hour), or P1D (1 day). The choice of interval is driven by the expected variability of workload. For highly transactional databases, I recommend PT5M for peak detection; for cost baseline reports, PT1H or P1D are sufficient.

Aggregation customization: Enables retrieval of multiple aggregation types such as Average, Maximum, and Minimum to provide a more comprehensive view of performance trends. This helps distinguish between chronic underutilization (average) and risk peaks (maximum). For example, databases averaging 25% CPU but peaking at 90% may require different scaling decisions.

API error handling and rate-limiting protection: Incorporates checks to exclude databases with no available metrics and introduces pauses between API calls to respect Azure Monitor rate limits. For environments exceeding 500 databases, I recommend extending sleep intervals and optionally batching requests per resource group.

Core configuration example:

--aggregation "Average" "Maximum" "Minimum"

It is important to explicitly define aggregation types; otherwise, Azure defaults to Average only, potentially masking performance outliers.

Retention and date flexibility:

START_DATE="$(date -u -d '-7 days' +"%Y-%m-%dT%H:%M:%SZ")"
END_DATE="$(date -u +"%Y-%m-%dT%H:%M:%SZ")"

Dynamic date calculation enables rolling window reporting, useful for weekly optimization reviews.

This Bash script represents the first component of a lightweight monitoring pipeline, facilitating data-driven FinOps analysis without additional Azure monitoring costs.

4. Sample Output (Raw JSON)

5. Python Script: Transforming Metrics

View the Python script on GitHub:

scripts/transform_metrics.py

Once metrics are collected, a Python script converts the raw JSON into a structured CSV, transforming scattered time series data into a Power BI-compatible format.

Key elements

Parses resource identifiers into structured fields (subscription, server, database).
Pivots metrics so each metric becomes a dedicated column.
Aligns column names to match Azure billing exports.

Example transformation step:

This pipeline produces a structured output easily consumable in Power BI for reporting and cost analysis.

6. Practical Extensions

Azure Automation: Schedule recurring data collection weekly or monthly.
Blob Storage Integration: Archive historical performance data for trend analysis.
Power BI Reports: Create dashboards aligning technical performance with financial costs.
Custom Metrics: Modify the Bash script to include specific metrics relevant to your workloads.

Example customization:

METRICS="cpu_percent,storage_percent,workers_percent"
INTERVAL="PT1H"

7. Conclusion

This article focused on a core building block of any effective FinOps practice: establishing independent, cost-aware access to usage data. Without reliable metrics — and without being tied to expensive tools or internal platform dependencies — optimization efforts often remain superficial.

By using a lightweight combination of Azure CLI and Python, I was able to implement a solution that scales across subscriptions, respects enterprise constraints, and delivers visibility where it’s most needed — at the intersection of performance and cost.

This approach is not just about collecting data; it's about regaining control. It allowed me to identify low-usage DTU-based databases, highlight peak performance risks, and prepare clear migration paths to vCore serverless — all backed by evidence.

In the next article, I’ll show how this data connects to Azure billing exports, how I created actionable KPIs, and how it all comes together in Power BI to support FinOps decision-making with clarity and impact.

References

Author: Sid Ahmed Redjini · FinOps Consultant

Thanks for the support. I will edit this with more details https://dev.to/sid_rdj_bc998504b31f86326/why-your-azure-sql-dtu-database-might-be-charging-you-for-more-than-24-hours-a-day-5ddg

Sid Redjini — Wed, 16 Jul 2025 09:57:50 +0000

Why Your Azure SQL DTU Database Might Be Charging You for More Than 24 Hours a Day

Sid Redjini — Wed, 09 Jul 2025 15:09:21 +0000

In cloud cost optimization, assumptions can be expensive. Azure’s Database Transaction Unit (DTU) pricing model for SQL Databases is often considered a simple solution for provisioning compute, memory, and IOPS as a bundled service. Yet, behind its simplicity hides a subtle — and surprisingly costly — inefficiency.

As a FinOps consultant analyzing production environments, I uncovered a pattern of overbilling in Azure SQL’s DTU model. Specifically, I observed multiple cases where a single database was charged for more than 24 hours in a single day. This article documents the root cause, demonstrates how to detect it, and recommends migration paths toward better cost control.

1. Understanding the DTU Model
2. The Anomaly: More Than 24 Hours Charged in a Day
3. Why This Happens: Technical Breakdown
4. Visualization in Power BI: Detecting the Drift
5. Implications and Recommendations
Conclusion

1. Understanding the DTU Model

Azure DTUs combine CPU, RAM, and I/O into a single opaque metric. Customers select a tier (e.g., S0 to S12), each with a fixed DTU value. Billing is typically expressed as a cost per hour, for example:

S7 (800 DTUs) = 1.3777 €/hour

S4 (200 DTUs) = 0.3445 €/hour

While Microsoft states that any database “active for a fraction of an hour is billed for the full hour,” in practice this behavior is more nuanced — especially when service tiers change during the same billing period.

A key nuance is this:

Azure does not apply the same hourly billing principle to tier changes as it does to resource creation or deletion.

When you create or delete a SQL Database, Azure applies hourly rounding — even a few minutes of activity are billed as one full hour. However, when a tier change occurs (e.g., from S4 to S7), the pricing engine converts usage to a base unit of 10 DTU per day rather than by hour. This means overlapping or sequential tier allocations within the same day can be computed independently and summed in a way that exceeds 24 hours, even though no restart or redeployment took place. This is a subtle but important divergence from standard VM billing logic.

2. The Anomaly: More Than 24 Hours Charged in a Day

In one case study, a production SQL database (ResourceName = A418) was billed as follows on April 1st:

Despite a calendar day being 24 hours long, Azure computed a total of 26.02 billed hours. This discrepancy can accumulate unnoticed in environments that scale tiers dynamically.

Interestingly, one might expect Azure to round each tier usage to the nearest full hour — similar to how VM creation or deletion incurs a one-hour charge — especially since running a VM for five minutes still incurs a full-hour cost. However, when we recompute the estimated hours billed based on the real EffectiveCost and hourly prices without rounding, we match Azure's billed result precisely. This proves that Azure does not apply rounding in tier changes: it uses exact usage duration internally, then sums them for the day — which can lead to over 24 hours billed.

Below is the actual Power BI output for a single day of activity for the same resource:

This table confirms that billing logic does not round up to the next full hour, but calculates an exact fractional hour total per tier, which is then aggregated — often surpassing the daily limit of 24h.

3. Why This Happens: Technical Breakdown

This overbilling stems from two structural behaviors in the DTU model:

Each service tier change generates a separate billing line. Even within the same day, Azure treats each tier period as an independent unit.

Azure does not reconcile overlapping allocations. Instead, it applies a conversion ratio based on the 10 DTU/day unit, regardless of time overlap.

In many environments, these tier switches are triggered by internal scripts or autoscaling logic based on Azure Monitor metrics (e.g., spikes in dtu_consumption_percent). This mechanism allows quick scaling between S4, S7, or S9 in the same day — but introduces unintended cost multiplication.

4. Visualization in Power BI: Detecting the Drift

To quantify this anomaly, I created a custom DAX measure:

EstimatedBilledHours = EffectiveCost / HourlyRatePerTier

This measure converts the billed cost back into implied usage time, tier by tier. When aggregated per resource per day, it reveals total hours billed.

This method reveals clear overbilling patterns, especially when correlated with metrics like dtu_consumption_percent or cpu_percent, which often show 0% activity for multiple hours.

5. Implications and Recommendations

This behavior can result in significant waste:

Tier changes generate unoptimized billing granularity

Databases with spiky workloads are penalized with high DTU tiers

Usage at 0% DTU/CPU is still charged at full rate

Conclusion

While Azure’s DTU model appears predictable, it carries hidden complexity that can lead to silent overbilling. By monitoring EffectiveCost / HourlyRate, tracking tier changes, and visualizing billed time per day, we uncovered a FinOps opportunity to optimize cost — without sacrificing performance.

If you rely on the DTU model in production, it may be time to ask: How many hours is your database actually billing you each day?

Sid Ahmed Redjini, FinOps Consultant at FinOps & Co

Specializing in Azure cost optimization, Power BI analysis, and FinOps automation.

Let's connect: LinkedIn | GitHub