Designing a Cost-Efficient Azure Data Lakehouse for Power BI

1. What “Cost-Efficient” Really Means for a Lakehouse

Before picking services, be clear what you’re optimising for. In a Power BI-centric lakehouse, cost-efficiency usually means:

• Predictable monthly spend – no nasty spikes from ad-hoc queries or refresh storms.
• Right latency for the business – not “fastest possible”, but “fast enough to make decisions”.
• Minimal duplication – no storing the same data in five places just to please different tools.
• Operational simplicity – fewer moving parts = fewer hours spent tuning and firefighting.

You’re balancing three levers:

1. Storage – how and where you store raw, curated, and semantic data.
2. Compute – how you transform and serve data to Power BI.
3. Network – how data moves between services and to end users.

Design choices in one area always affect the others. The rest of this article assumes:

• Azure Data Lake Storage Gen2 (ADLS) as the data lake.
• Azure Synapse, Fabric, or Databricks as the main compute engine (concepts are similar).
• Power BI as the primary consumption layer.

2. Storage Design: Cheap, Durable, and Query-Friendly

2.1 Tiered Zones in ADLS

A clean zone layout is the cheapest optimisation you can make:

• Bronze (Raw)

  • Land data with minimal transformation.
  • Keep original file structures and schemas.
  • Use for replay and debugging.

• Silver (Cleaned / Conformed)

  • Standardised types, keys, and basic quality rules.
  • Still fairly close to source granularity.

• Gold (Analytics / Semantic)

  • Star-schema style fact and dimension tables.
  • Optimised for Power BI consumption.

This structure:

• Reduces recompute (you don’t re-clean raw data every time).
• Allows selective retention (e.g. shorter retention in Bronze, longer in Silver/Gold).

2.2 File Formats and Partitioning

Storage is cheap, but bad layout explodes compute cost.

Prefer columnar formats for Silver and Gold:

• Use Parquet or Delta (if using Databricks/Fabric/Synapse with Delta support).
• Columnar formats reduce scan size, which directly cuts query compute.

Partitioning guidelines:

• Partition facts by date (e.g. year=2024/month=09/day=23) for time-based reporting.
• For high-volume, also consider a secondary partition (e.g. region or source system).
• Avoid over-partitioning (thousands of tiny files); it increases metadata overhead.

Example partitioned folder structure:

/adls-container
  /bronze
    /erp
      /sales
        /year=2024/month=09/day=23/...
  /silver
    /sales
      /year=2024/month=09/day=23/...
  /gold
    /sales_mart
      /fact_sales
        /year=2024/month=09/...
      /dim_customer

2.3 Lifecycle Management and Retention

Use lifecycle policies to move or delete data automatically:

• Move cold Bronze data to a cooler tier after X days.
• Keep Silver in hot tier for active queries; consider cool tier for older partitions.
• Keep Gold in hot tier; it’s what Power BI hits most.

Typical policy ideas:

• Bronze: hot for 30 days → cool for 365 days → archive/delete.
• Silver: hot for 90 days → cool afterwards.
• Gold: hot for 365+ days (depends on reporting requirements).

This alone can shave a meaningful portion off storage costs without touching performance-critical data.

3. Compute Strategy: Pay Only for Useful Work

Most Azure data lakehouse bills are driven by compute, not storage. The goal is to:

• Run transformations in batches as much as possible.
• Scale to zero when idle.
• Reserve interactive compute for Power BI’s direct workloads.

3.1 Batch vs Interactive Compute

Typical pattern:

• Batch compute (nightly/hourly):

  • Ingest and transform data into Silver and Gold.
  • Use scheduled pipelines (Synapse Pipelines, Data Factory, Fabric Data Pipelines).
  • Use small-to-medium clusters; scale up only for heavy jobs.

• Interactive compute:

  • Ad-hoc analysis and troubleshooting.
  • Limited to data engineers and power users.
  • Strictly controlled to avoid runaway costs.

3.2 Choosing the Right Engine Mode for Power BI

Your main choice is how Power BI connects to your lakehouse:

• Import mode (Power BI dataset caches data):

  • Pros: Fast for users, predictable query cost, good for complex DAX.
  • Cons: Data latency = refresh interval; large models can hit capacity limits.

• DirectQuery to SQL endpoints (Synapse serverless, Databricks SQL, Fabric Warehouse):

  • Pros: Near-real-time data, no dataset duplication.
  • Cons: Query cost on every user interaction, performance tuning required.

• Hybrid / Composite models:

  • Mix of Import for historical data + DirectQuery for latest data.
  • Good compromise for cost and freshness.

For cost-efficiency:

• Use Import for stable, high-volume historical data.
• Use DirectQuery only for:
  • Small, frequently changing tables (e.g. today’s transactions).
  • Scenarios where near-real-time is genuinely required.

Example Power BI composite model scenario:

• FactSales_History (Import, up to yesterday).
• FactSales_Today (DirectQuery to lakehouse SQL endpoint).

Sample DAX to combine them:

FactSales_All =
UNION (
    FactSales_History,
    FactSales_Today
)

3.3 Autoscaling and Scheduling Patterns

Key rules to avoid overpaying for compute:

• Schedule heavy ETL off-peak (night or early morning) when possible.
• Use smaller clusters with longer runtimes rather than huge spikes, unless SLAs demand otherwise.
• Enable autoscaling with sensible min/max nodes.
• Auto-stop clusters after short idle periods (e.g. 10–15 minutes).

Example daily pipeline pattern:

1. Trigger at 02:00.
2. Spin up compute (cluster or SQL pool).
3. Ingest and transform Bronze → Silver → Gold.
4. Process Power BI dataset refresh.
5. Shut down compute.

4. Network Design: Don’t Pay to Move Data Twice

Network costs are often invisible until the bill arrives. Focus on:

• Data locality – keep services in the same region.
• Minimising cross-region traffic.
• Avoiding unnecessary egress to the public internet.

4.1 Keep Everything in One Region

Wherever possible:

• Place ADLS, compute (Synapse/Databricks/Fabric), and Power BI workspace in the same region.
• Avoid cross-region replication for analytics workloads unless you have a clear compliance need.

This reduces:

• Latency for Power BI queries.
• Cross-region data transfer charges.

4.2 Private Endpoints and Gateways

For corporate networks with strict security:

• Use Private Endpoints for ADLS and compute.
• Use an On-premises data gateway only when Power BI needs to reach on-prem sources.

From a cost perspective:

• Private endpoints add a small fixed cost but can reduce egress and improve security.
• Gateways are more about connectivity than cost, but misconfigured routing can cause traffic to bounce between regions or on-prem, which indirectly increases network and compute usage.

4.3 Power BI Dataflows vs Direct Lake Access

You have two main ways to get lake data into Power BI:

1. Power BI Dataflows (or Fabric Dataflows Gen2):

   • Pros: No-code/low-code, reusable across datasets.
   • Cons: Another compute layer; potential duplication of transforms.

2. Direct access to curated tables (via SQL endpoints or Direct Lake in Fabric):

   • Pros: Single transformation layer; less duplication.
   • Cons: Requires stronger data engineering discipline.

For cost-efficiency, prefer:

• Doing heavy transformations once in the lakehouse (Silver/Gold).
• Using Power BI Dataflows only for light reshaping or business-specific calculations.

Example Power Query snippet for a Dataflow that only does light shaping on a curated table:

let
    Source = Sql.Database("az-synapse-sql", "lakehouse_db"),
    SalesTable = Source{[Schema="dbo", Item="vw_fact_sales"]}[Data],
    KeepColumns = Table.SelectColumns(SalesTable, {"SalesDate", "CustomerKey", "Amount"}),
    FilterRecent = Table.SelectRows(KeepColumns, each [SalesDate] >= Date.AddDays(Date.From(DateTime.LocalNow()), -365))
in
    FilterRecent

All heavy lifting (joining, type casting, deduplication) should already be done in vw_fact_sales.

5. Practical Lakehouse Architecture for Power BI

Let’s put it together into a concrete, cost-aware architecture.

5.1 Reference Architecture Components

• Storage: ADLS Gen2 with Bronze/Silver/Gold zones.
• Ingestion: Data Factory / Synapse Pipelines / Fabric Pipelines.
• Transformations: Spark (Databricks/Fabric), Synapse SQL, or a mix.
• Serving Layer:
  • SQL endpoints or warehouses for Power BI DirectQuery.
  • Gold Parquet/Delta tables for Import mode.
• Power BI:
  • Datasets in Import or composite mode.
  • Dataflows for light shaping only.

5.2 Example ETL Pattern (SQL-Based)

Assume Synapse serverless SQL as the transformation engine.

Step 1 – Bronze to Silver (cleaning)

CREATE OR ALTER VIEW silver.sales AS
SELECT
    TRY_CONVERT(date, sales_date)      AS SalesDate,
    TRY_CONVERT(int, customer_id)      AS CustomerKey,
    TRY_CONVERT(decimal(18,2), amount) AS Amount,
    source_file_name                   AS SourceFile
FROM
    OPENROWSET(
        BULK 'bronze/erp/sales/year=2024/*/*.csv',
        DATA_SOURCE = 'adls_ds',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS [rows]
WHERE
    TRY_CONVERT(date, sales_date) IS NOT NULL
    AND TRY_CONVERT(decimal(18,2), amount) IS NOT NULL;

Step 2 – Silver to Gold (star schema)

CREATE OR ALTER VIEW gold.fact_sales AS
SELECT
    s.SalesDate,
    c.CustomerKey,
    c.CustomerGroup,
    s.Amount
FROM
    silver.sales s
    JOIN gold.dim_customer c
        ON s.CustomerKey = c.CustomerKey;

Power BI then:

• Imports gold.fact_sales and gold.dim_customer into a model.
• Optionally uses DirectQuery for a small gold.fact_sales_today view.

5.3 Governance and Cost Controls

To keep the architecture sustainable:

• Tag resources by environment (dev/test/prod) and cost centre.
• Use budgets and alerts per subscription or resource group.
• Regularly review:
  • Top queries hitting your SQL endpoints.
  • Underused datasets and workspaces.
  • Stale data in Bronze and Silver.

6. One Concrete Takeaway: Design Around Power BI Refresh

Most of your cost and performance issues will show up during Power BI refresh windows. Design your lakehouse backward from that moment:

1. Decide how fresh reports need to be.
2. Choose Import vs DirectQuery vs composite accordingly.
3. Size and schedule ETL compute to finish just before refresh.
4. Store data in query-friendly formats (Parquet/Delta, partitioned) in Gold.

If you get the refresh path right, everything else—storage tiers, compute sizing, and network layout—will naturally fall into a cost-efficient shape.