Power BI Architecture for Data Engineers: Desktop vs Service, Semantic Models, Import vs DirectQuery vs Direct Lake, Gateways, Data Refresh, Workspaces, Licensing, and Where Data Engineering Meets BI

Table of Contents

• Power BI Through a Data Engineer’s Lens
• Power BI Desktop vs Power BI Service
• Semantic Models — The Heart of Power BI
• Storage Modes — Import, DirectQuery, and Direct Lake
• Import Mode — The Traditional Approach
• DirectQuery Mode — Query at Report Time
• Direct Lake Mode — The Fabric-Native Approach
• Import vs DirectQuery vs Direct Lake — The Complete Comparison
• On-Premises Data Gateway
• Data Refresh — Scheduled and On-Demand
• Workspaces — Organizing Content
• Licensing — Pro, PPU, Premium, and Fabric
• Where Data Engineering Meets Power BI
• Common Mistakes
• Interview Questions
• Wrapping Up

This is not a Power BI tutorial for analysts. This is Power BI through the lens of a data engineer — the person who builds the data pipelines, designs the star schemas, configures the refresh schedules, and troubleshoots why the dashboard shows yesterday’s numbers instead of today’s. Data engineers do not create fancy visualizations, but they build everything underneath them. Understanding Power BI’s architecture helps you build data platforms that analysts can actually use.

Analogy — A restaurant kitchen and dining room. As a data engineer, you are the kitchen staff. You source ingredients (raw data), prep them (bronze layer), cook the dishes (silver/gold layers), and plate them (star schema). The semantic model is the menu — it describes what dishes are available and how they are prepared. Power BI reports are the dining room where customers (business users) consume the dishes. Import mode is a buffet — all food is prepared ahead and sits ready (fast but might get cold). DirectQuery is cooking to order — freshest possible but slower. Direct Lake is a new hybrid — food prepared in the kitchen (Lakehouse) but served instantly through a pass-through window (no reheating needed).

Power BI Through a Data Engineer’s Lens

What data engineers DO in Power BI:

  BUILD the data foundations:
    - Design star schemas (fact and dimension tables)
    - Build data pipelines that feed Power BI (ADF, Databricks, Fabric)
    - Configure storage mode (Import vs DirectQuery vs Direct Lake)
    - Set up data refresh schedules
    - Manage gateway connections for on-premises sources
    - Optimize Delta tables for Direct Lake performance

  SUPPORT the analytics team:
    - Troubleshoot refresh failures
    - Optimize slow queries (add aggregation tables, fix star schema)
    - Set up row-level security (RLS) in semantic models
    - Manage workspace permissions and deployment pipelines
    - Monitor capacity and throttling

  DO NOT typically do:
    - Build report visuals (that is the analyst/BI developer's job)
    - Write complex DAX measures (though you should understand DAX basics)
    - Design dashboard layouts
    - Create paginated reports

Power BI Desktop vs Power BI Service

Power BI Desktop (Windows application):
  - Free, standalone Windows app (download from Microsoft)
  - Where you BUILD semantic models and reports
  - Connect to data sources, transform data (Power Query), create models
  - Design report pages with visuals
  - Output: .pbix file (contains model + report + data)
  - Cannot share reports -- must publish to the Service

Power BI Service (cloud platform):
  - Web-based platform at app.powerbi.com (or within Fabric portal)
  - Where you PUBLISH, SHARE, and CONSUME reports
  - Hosts semantic models, reports, dashboards, and dataflows
  - Manages scheduled refresh, gateways, and security
  - Provides workspaces, apps, and sharing mechanisms
  - Where deployment pipelines promote content across environments

The workflow:
  Desktop: Build model + report → Publish → Service: Share and consume
  Fabric:  Build model in browser (web modeling) → Direct Lake → Reports

Semantic Models — The Heart of Power BI

A semantic model (formerly called a “dataset”) is the data layer that sits between your data sources and the reports. It defines tables, relationships, measures, and security rules. Every report queries a semantic model, never the raw data source directly.

Analogy — A cookbook recipe card. The raw ingredients (your data lake tables) do not mean anything to a diner. The semantic model is the recipe card that describes how to combine ingredients into a dish: “Take the orders fact table, join it with the customers dimension on customer_id, and calculate total_revenue as SUM(amount).” The report visual is the plated dish — it reads the recipe card and presents the result.

What a semantic model contains:

  Tables:
    - Fact tables (orders, transactions, events)
    - Dimension tables (customers, products, dates, regions)
    - Imported from data sources or referenced via DirectQuery/Direct Lake

  Relationships:
    - One-to-many relationships between dimensions and facts
    - Defines how tables join (like a star schema)
    - Active vs inactive relationships

  Measures (DAX):
    - Calculated values: Total Revenue = SUM(Orders[Amount])
    - Time intelligence: Revenue YTD = TOTALYTD(SUM(Orders[Amount]), 'Date'[Date])
    - Conditional: High Value Orders = CALCULATE(COUNT(Orders[ID]), Orders[Amount] > 1000)

  Calculated Columns:
    - Row-level computations stored in the model
    - Example: Profit Margin = Orders[Revenue] - Orders[Cost]

  Row-Level Security (RLS):
    - Filter rules that restrict data by user
    - Example: Ontario sales reps only see Ontario data

Storage Modes — Import, DirectQuery, and Direct Lake

The storage mode determines WHERE the data lives when a report queries it. This is the most critical architecture decision a data engineer makes for Power BI.

Import Mode — The Traditional Approach

Import mode copies data from the source into the Power BI semantic model. The data is compressed, stored in-memory using the VertiPaq engine, and refreshed on a schedule.

How Import works:

  1. Data source (SQL Server, ADLS, Excel, API)
       |
  2. Power Query transforms the data (clean, filter, join)
       |
  3. Data is compressed and loaded into the VertiPaq in-memory engine
       |
  4. Data stored inside the .pbix file (Desktop) or semantic model (Service)
       |
  5. Report queries are answered from the in-memory copy (fast)
       |
  6. Scheduled refresh pulls fresh data from the source (e.g., daily at 6 AM)

  Pros:
    - Fastest query performance (data is in memory, pre-compressed)
    - Full modeling flexibility (calculated columns, tables, all DAX functions)
    - Works with ANY data source (400+ connectors)
    - Works with any Power BI license (Pro, PPU, Premium, Fabric)

  Cons:
    - Data is a snapshot (stale between refreshes)
    - Refresh can be slow for large datasets (must reimport everything)
    - .pbix file grows with data size (1 GB limit for Pro, 10 GB for Premium)
    - Scheduled refresh limited to 8/day (Pro) or 48/day (Premium/Fabric)
    - Requires gateway for on-premises sources

DirectQuery Mode — Query at Report Time

DirectQuery does not copy data. Every report interaction sends a query to the source database in real time.

How DirectQuery works:

  1. Data source (SQL Server, Azure SQL, Synapse, Snowflake)
       |
  2. Semantic model stores only the schema (no data copied)
       |
  3. User clicks a visual → Power BI generates a SQL query
       |
  4. Query sent to source database → results returned → visual rendered
       |
  5. Every interaction = another query to the source

  Pros:
    - Always fresh (queries hit the live source)
    - No data duplication (source is the single truth)
    - No refresh needed (no import process)
    - Works with very large datasets (no size limit in Power BI)
    - Source-enforced security passes through

  Cons:
    - Slower than Import (network latency + source query time)
    - Limited DAX functionality (some functions not supported)
    - Puts load on the source database (concurrent report users = concurrent queries)
    - Not all data sources support DirectQuery
    - Cannot use calculated columns in the traditional way
    - Performance depends on source database optimization

Direct Lake Mode — The Fabric-Native Approach

Direct Lake is the newest storage mode, exclusive to Microsoft Fabric. It reads Delta Parquet files directly from OneLake into memory — no import refresh process, no DirectQuery roundtrip to a SQL endpoint.

How Direct Lake works:

  1. Data stored as Delta tables in a Fabric Lakehouse or Warehouse
       |
  2. Semantic model configured in Direct Lake mode
       |
  3. User opens a report → Power BI reads Delta Parquet files directly from OneLake
       |
  4. Data loaded into the VertiPaq engine on-demand (not pre-imported)
       |
  5. Subsequent queries use the in-memory cache (fast, like Import)
       |
  6. When source Delta tables change, the model picks up changes automatically
       (no scheduled refresh needed -- "framing" detects new files)

  Pros:
    - Import-level performance (VertiPaq engine, in-memory)
    - Near-real-time freshness (no scheduled refresh)
    - No data duplication (reads from OneLake directly)
    - No gateway needed (Fabric-native)
    - Ideal for large datasets in Fabric Lakehouses

  Cons:
    - Requires Microsoft Fabric (not available in legacy Power BI)
    - Requires data in Delta format in OneLake
    - Per-SKU guardrails (row limits, column limits per table)
    - Limited DAX compared to Import (no calculated columns, no calculated tables)
    - Can fall back to DirectQuery if guardrails are exceeded (slower)
    - Requires proper Delta table maintenance (OPTIMIZE, VACUUM)

  When Direct Lake falls back to DirectQuery:
    - Table exceeds the row/column guardrails for your Fabric SKU
    - Delta table has too many small files (not optimized)
    - V-order not applied to Parquet files
    - You can control fallback behavior with DirectLakeBehavior property

Import vs DirectQuery vs Direct Lake — The Complete Comparison

Decision guide:

  Use IMPORT when:
    - Dataset is small to medium (< 1 GB compressed)
    - Full DAX modeling is needed (calculated columns, calc tables)
    - Data freshness of hours is acceptable
    - Source is non-Fabric (on-prem SQL, Excel, APIs)

  Use DIRECTQUERY when:
    - Data must be real-time (live dashboards, trading floors)
    - Source-enforced security is required
    - Dataset is too large to import
    - Source database is well-optimized for query workloads

  Use DIRECT LAKE when:
    - Data lives in a Fabric Lakehouse or Warehouse (Delta format)
    - You want Import-level speed without import refresh
    - Dataset is large (many GBs to TBs)
    - Near-real-time freshness is needed
    - You are building a Fabric-native analytics platform

On-Premises Data Gateway

The gateway is a bridge between Power BI Service and on-premises data sources. It is needed when your data source is not in the cloud.

Two types of gateways:

  On-Premises Data Gateway (standard mode):
    - Installed on a Windows server in your network
    - Shared by multiple users and multiple datasets
    - Supports scheduled refresh and DirectQuery
    - Managed by IT admins
    - Can be clustered for high availability (2+ servers)

  On-Premises Data Gateway (personal mode):
    - Installed on a developer's machine
    - Only that user can use it
    - Only supports scheduled refresh (no DirectQuery)
    - Not for production use

  When do you need a gateway?
    - On-premises SQL Server, Oracle, file shares → YES
    - Azure SQL Database, Azure Synapse, ADLS Gen2 → NO (cloud-to-cloud)
    - Fabric Lakehouse/Warehouse → NO (Fabric-native)
    - Excel files on SharePoint Online → NO (cloud source)
    - Excel files on a network drive → YES (on-premises)

  Gateway is NOT needed for Fabric Direct Lake mode.

Data Refresh — Scheduled and On-Demand

Refresh types:

  Scheduled Refresh (Import mode):
    - Configured in Power BI Service (dataset settings)
    - Pro: up to 8 refreshes per day
    - Premium/Fabric: up to 48 refreshes per day
    - Full refresh: reimports all data
    - Incremental refresh: only imports new/changed data (based on date range)

  On-Demand Refresh:
    - Manual click: dataset → Refresh Now
    - API-triggered: Power BI REST API or Fabric API
    - Useful for: after a pipeline run, after a data load completes

  Automatic Refresh (Direct Lake):
    - No scheduled refresh needed
    - Power BI detects new Delta files in OneLake automatically
    - "Framing" process: model creates a new frame pointing to latest files
    - Users see fresh data within minutes of pipeline completion

  Incremental Refresh (Import mode optimization):
    - Only refresh recent data (e.g., last 30 days)
    - Historical data is not reimported (already in model)
    - Reduces refresh time from hours to minutes
    - Configure via Power Query date range parameters
    - Available in Pro (with limitations) and Premium/Fabric (full feature)

  For data engineers:
    - After your ADF/Databricks pipeline completes, trigger a Power BI refresh
    - Use the Power BI REST API: POST /datasets/{id}/refreshes
    - Or configure the pipeline to send a webhook that triggers refresh

Workspaces — Organizing Content

Workspace structure:

  Workspace = a container for semantic models, reports, dashboards, dataflows

  Typical setup for data engineering teams:

    DataPlatform-Dev (workspace)
      ├── Sales Semantic Model (Direct Lake → dev Lakehouse)
      ├── Sales Report
      └── Pipeline monitoring dashboard

    DataPlatform-Test (workspace)
      ├── Sales Semantic Model (Direct Lake → test Lakehouse)
      └── Sales Report

    DataPlatform-Prod (workspace)
      ├── Sales Semantic Model (Direct Lake → prod Lakehouse)
      ├── Sales Report
      └── Executive Dashboard

  Workspace roles:
    Admin:       full control (delete workspace, manage access)
    Member:      publish content, edit content, manage permissions
    Contributor: edit content, publish, but cannot manage access
    Viewer:      view reports only (most business users)

  For deployment:
    Use Fabric Deployment Pipelines to promote content:
    Dev workspace → Test workspace → Prod workspace

Licensing — Pro, PPU, Premium, and Fabric

Power BI licensing (2026):

  Power BI Pro ($10/user/month):
    - Publish and share reports
    - 1 GB dataset size limit
    - 8 scheduled refreshes per day
    - P2P sharing (Pro users share with Pro users)
    - No Direct Lake, no deployment pipelines

  Power BI Premium Per User (PPU) ($20/user/month):
    - All Pro features
    - 10 GB dataset size limit
    - 48 refreshes per day
    - AI features, paginated reports
    - Deployment pipelines
    - Still per-user (everyone needs PPU to view)

  Power BI Premium Per Capacity (P SKUs):
    - Dedicated capacity (P1, P2, P3, P4)
    - Unlimited viewers (only authors need Pro)
    - Large dataset support (up to 400 GB)
    - XMLA endpoint, deployment pipelines
    - Being replaced by Fabric F SKUs

  Microsoft Fabric (F SKUs):
    - Includes Power BI Premium features
    - F2, F4, F8, F16, F32, F64, F128, F256, F512, F1024
    - Direct Lake mode available (F SKUs only)
    - All Fabric workloads (Lakehouse, Warehouse, Notebooks, Pipelines)
    - Pay-per-capacity (not per-user for viewing)
    - Viewers need only a free Power BI account

  For data engineers:
    - If your org uses Fabric → F SKU (Direct Lake, all features)
    - If no Fabric → Pro or PPU (Import/DirectQuery)
    - F64+ recommended for enterprise Direct Lake workloads

Where Data Engineering Meets Power BI

The handoff between data engineering and Power BI:

  Data Engineer's responsibility:
    1. Build the data pipeline (ADF, Databricks, Fabric)
    2. Create the gold layer tables (star schema in Lakehouse/Warehouse)
    3. Optimize Delta tables (OPTIMIZE, VACUUM, V-order for Direct Lake)
    4. Set up the semantic model (choose storage mode, define relationships)
    5. Configure refresh schedule or Direct Lake framing
    6. Set up row-level security (RLS) roles
    7. Deploy via deployment pipelines (dev → test → prod)

  BI Developer/Analyst's responsibility:
    1. Connect to the semantic model
    2. Write DAX measures for business logic
    3. Build report pages with visuals
    4. Design the user experience (filters, drill-through, bookmarks)
    5. Publish and share via Power BI apps

  The boundary:
    Data Engineer builds the model and hands it off.
    BI Developer builds reports on top of the model.
    Both collaborate on schema design and measure definitions.

  For Fabric Direct Lake:
    Data Engineer: builds gold layer tables in Lakehouse
    → Semantic model reads directly from gold tables
    → BI Developer connects to the semantic model
    → No ETL between Lakehouse and Power BI (that is the magic of Direct Lake)

Common Mistakes

1. Choosing DirectQuery to avoid refresh scheduling. DirectQuery sounds convenient — no refresh needed, always live data. But every report interaction sends a query to the source database. 20 users opening a dashboard with 10 visuals = 200 concurrent queries hitting your database. If the source is not optimized for this, dashboards are painfully slow. Import is faster for 90% of use cases. Direct Lake is better for Fabric workloads.

2. Not optimizing Delta tables for Direct Lake. Direct Lake reads Delta Parquet files directly. If your Delta tables have thousands of small files, the model loads slowly and may fall back to DirectQuery. Run OPTIMIZE regularly, apply V-order (which Fabric does by default), and use VACUUM to clean up old files.

3. Building reports directly on bronze or silver tables. Reports should connect to gold layer tables that have clean, aggregated, star-schema data. Connecting to raw bronze tables means Power BI does heavy transformations at query time, resulting in slow reports and complex DAX. Build the star schema in your pipeline, not in Power Query.

4. Using the personal gateway for production. The personal gateway runs on a developer’s laptop. When the laptop is off, scheduled refreshes fail. Use the standard on-premises gateway installed on a dedicated server with high availability clustering.

5. Not setting up incremental refresh for large Import models. A 5 GB Import model that does a full refresh daily takes 30+ minutes and times out on Pro licenses. Incremental refresh only reimports recent data (last 30 days) while keeping historical data intact. Configure it for any model over 1 GB.

6. Giving everyone Admin role on workspaces. Workspace Admins can delete the workspace, remove members, and modify all content. Most users should be Viewers. Content creators should be Contributors. Only the platform team should be Admins.

7. Not understanding the licensing model. Publishing a report to a Pro workspace means every viewer needs Pro ($10/month per person). With Fabric F SKUs or Premium capacity, viewers only need a free account. For dashboards with 500+ viewers, capacity licensing is dramatically cheaper than per-user licensing.

8. Ignoring the data engineer’s role in Power BI. Some teams expect analysts to handle everything from raw data to reports. This creates brittle, unmaintainable solutions. Data engineers should own the pipeline, the star schema, and the semantic model configuration. Analysts should focus on DAX measures and visualizations.

Interview Questions

Q: What is a Power BI semantic model and what does it contain? A: A semantic model (formerly dataset) is the data layer between data sources and reports. It contains tables (fact and dimension), relationships (star schema joins), measures (DAX calculations like Total Revenue = SUM(Amount)), calculated columns, and row-level security rules. Reports query the semantic model, never the raw data source directly. The semantic model abstracts the complexity of the data layer so report authors can drag and drop fields without writing SQL.

Q: What are the three Power BI storage modes and when would you use each? A: Import mode copies data into Power BI’s in-memory VertiPaq engine for the fastest query performance, but data is a snapshot between scheduled refreshes. Use it for small-medium datasets where hourly staleness is acceptable. DirectQuery keeps data at the source and queries it at report time for always-fresh results, but performance depends on the source database. Use it for real-time requirements or source-enforced security. Direct Lake reads Delta files from OneLake directly into memory without an import process, combining Import speed with near-real-time freshness. Use it for Fabric Lakehouse data at scale.

Q: What is Direct Lake mode and how does it differ from Import and DirectQuery? A: Direct Lake is exclusive to Microsoft Fabric. It reads Delta Parquet files directly from OneLake into the VertiPaq engine on-demand, without a traditional import refresh. Data freshness is near-real-time because the model detects new Delta files automatically. Unlike Import, there is no scheduled refresh and no data duplication. Unlike DirectQuery, queries are answered from the in-memory engine, not the source database. The tradeoff is per-SKU guardrails on rows and columns, and limited DAX support (no calculated columns or calculated tables).

Q: When does a data engineer need to set up an on-premises gateway? A: A gateway is needed when Power BI Service (cloud) needs to access data sources inside your organization’s network, such as on-premises SQL Server, Oracle databases, or file shares. Cloud-to-cloud connections (Azure SQL, ADLS Gen2, Snowflake) do not need a gateway. Fabric Direct Lake mode does not need a gateway because data is already in OneLake. Always use the standard gateway (not personal mode) for production, installed on a dedicated server with clustering for high availability.

Q: How does data refresh work across the three storage modes? A: Import mode requires scheduled refresh (up to 8/day on Pro, 48/day on Premium/Fabric) or on-demand refresh via the REST API. The entire dataset or incrementally refreshed partitions are reimported from the source. DirectQuery requires no refresh because queries hit the live source. Direct Lake requires no scheduled refresh because it detects new Delta files automatically through a process called framing. For data engineers using Import mode, the best practice is to trigger a Power BI refresh via the REST API after the data pipeline completes.

Q: What is the data engineer’s role vs the BI developer’s role in Power BI? A: The data engineer builds the pipeline (ADF, Databricks, Fabric), creates the gold layer star schema, optimizes Delta tables, configures the semantic model (storage mode, relationships, RLS), sets up refresh schedules, and manages deployment pipelines. The BI developer connects to the semantic model, writes DAX measures for business logic, builds report visuals, and designs the user experience. The boundary is the semantic model: data engineers build it, BI developers consume it. Both collaborate on schema design and measure definitions.

Q: What Power BI licensing do you need for Direct Lake? A: Direct Lake requires a Fabric F SKU capacity (F2 or higher). It is not available with Power BI Pro or PPU alone. The F SKU determines the guardrails for Direct Lake (maximum rows and columns per table). F64 or higher is recommended for enterprise workloads. With Fabric capacity, report viewers only need a free Power BI account, making it cost-effective for large audiences. Authors still need at least a Pro license to publish content.

Wrapping Up

Power BI architecture for data engineers centers on three decisions: where the data lives (Import, DirectQuery, or Direct Lake), how it stays fresh (scheduled refresh, live queries, or automatic framing), and how it is secured and shared (workspaces, RLS, licensing). The trend in 2026 is clear — organizations on Fabric are moving to Direct Lake, which eliminates the import refresh process and reads directly from the gold layer Delta tables you already build.

In the next post, we will cover every Power BI chart type, when to use each, slicers, drill-through, bookmarks, and the visualizations that data engineers should understand even if they do not build them.

Related posts: – Power BI Direct Lake in Fabric – Fabric Lakehouse Deep Dive – Microsoft Fabric Overview – Star Schema and Normalization – Data File Formats