AI

NFT

Comment

Microsoft Fabric Interview Cheat Sheet

1. Slowly Changing Dimensions (SCD)

Type 1 — Overwrite History

Use when historical state does not matter.

Customer 123
Region = East

changes to

Customer 123
Region = West

Good for:

  • Corrections
  • Typos
  • Non-historical attributes
  • Contact details where history is not needed

Type 2 — Preserve History

Create a new dimension version.

CustomerKey  CustomerID  Region  EffectiveFrom  EffectiveTo   Current
1001         123         East    2025-01-01     2026-07-31    No
2045         123         West    2026-08-01     9999-12-31    Yes

Key interview statement:

I determine SCD behavior at the attribute level based on whether a change would alter interpretation of historical facts.

Do not Type-2 every attribute.

2. Migration Strategy

Key line:

I treat migration as workload modernization, not database relocation.

Recommended flow:

DISCOVER
   ↓
INVENTORY
   ↓
DEPENDENCY MAP
   ↓
CLASSIFY
   ↓
TARGET ARCHITECTURE
   ↓
MIGRATION WAVES
   ↓
PARALLEL VALIDATION
   ↓
CUTOVER
   ↓
DECOMMISSION

Classify workloads as:

RETAIN
REHOST
REFACTOR
RETIRE

Examples:

  • Retain: SQL Server supporting an operational application.
  • Rehost: Straightforward analytical warehouse tables.
  • Refactor: Stored procedures, SSIS, or ETL logic that should become Fabric pipelines, notebooks, SQL transformations, or other modernized orchestration.
  • Retire: Unused tables, reports, jobs, integrations, and legacy technical debt.

Important principle:

Do not automatically move OLTP workloads into Fabric.

3. Fabric Migration and Ingestion Patterns

Bulk + Incremental

Initial SQL history
       ↓
Bulk Load
       ↓
Fabric

Then

Changed rows only
       ↓
Incremental Load

Best for large migrations.

Watermark Pattern

Common for Salesforce:

SystemModstamp > LastSuccessfulWatermark

Persist the watermark only after the load succeeds.

CDC

Capture:

INSERT
UPDATE
DELETE

rather than repeatedly rescanning the entire source.

Mirroring

SQL Server
    ↓
Continuous replication
    ↓
OneLake

Good when:

  • SQL Server remains operational
  • Fabric needs near-current analytical data
  • You want to reduce custom extraction logic

OneLake Shortcut

Sometimes the best migration is no physical migration.

ADLS
  ↓
OneLake Shortcut
  ↓
Fabric

Avoid unnecessary copies.

Streaming

Events
 ↓
Eventstream
 ↓
Eventhouse / Real-Time Intelligence

Use only where low latency has real business value.

Hybrid Pattern

SQL Server ───── Mirroring / CDC ─────┐
                                      │
Salesforce ───── Incremental / CDC ───┼──> Fabric
                                      │
ADLS ─────────── Shortcut ────────────┘

Key interview statement:

Ingestion architecture is source-driven, while Gold architecture is business-driven.

4. Medallion Architecture

BRONZE
"What did the source say?"

       ↓

SILVER
"What is the trusted enterprise representation?"

       ↓

GOLD
"How does the business want to consume it?"

Bronze

Purpose:

  • Source fidelity
  • Traceability
  • Replayability
  • Minimal transformation

Examples:

  • Raw Salesforce Account
  • Raw Opportunity
  • Raw SQL Order
  • Raw API JSON
  • CDC events

Recommended metadata:

_source_system
_ingestion_timestamp
_batch_id
_source_modified_timestamp
_operation

Think:

RAW
TRACEABLE
REPLAYABLE
SOURCE-FAITHFUL

Silver

Purpose:

  • Clean
  • Validate
  • Standardize
  • Conform
  • Resolve business identity

Typical work:

Deduplication
Type correction
CDC MERGE
Validation
Quarantine
Business-key resolution
Reference-data mapping
SCD
Customer matching
Data quality

Example:

Salesforce: TX
SQL Server: Texas
Legacy: TEX

             ↓

Silver:
StateCode = TX
StateName = Texas

Gold

Purpose:

  • Business-ready consumption
  • Dimensional models
  • Curated marts
  • Aggregates
  • Semantic models

Instead of exposing Salesforce source structures:

Account
Opportunity
Product2
User
OpportunityLineItem

create:

DimCustomer
DimProduct
DimSalesRep
DimDate
FactSales
FactPipeline
FactPipelineSnapshot

Key statement:

Bronze is source-shaped. Gold is business-shaped.

5. Lakehouse vs Warehouse vs Eventhouse

Capability Lakehouse Warehouse Eventhouse
Primary purpose Data engineering BI / relational analytics Real-time/event analytics
Main engine Spark + SQL T-SQL KQL
Typical data Structured + semi/unstructured Structured Events/time series/logs
Typical role Bronze/Silver Gold Real-time telemetry

Easy memory aid:

Lakehouse = engineering
Warehouse = relational analytics
Eventhouse = what is happening right now

6. OneLake, Delta, and Lakehouse

OneLake

OneLake is the Fabric storage plane.

Delta

Delta is a table format.

Parquet data files
       +
Delta transaction log
       =
Delta Table

A Lakehouse organizes this inside OneLake:

OneLake
   │
   └── Lakehouse
         │
         ├── Files
         │
         └── Tables
              │
              ├── *.parquet
              └── _delta_log

Lakehouse users typically work with:

Spark
DataFrames
Delta
Files
Notebooks

7. Warehouse and OneLake

Fabric Warehouse also uses OneLake and Delta/Parquet underneath.

The distinction is not:

Delta vs SQL

It is:

                   OneLake
                       │
                  Delta/Parquet
                       │
             ┌─────────┴─────────┐
             │                   │
         Lakehouse           Warehouse
             │                   │
           Spark          Distributed SQL
             │                   │
       Engineering            T-SQL

Warehouse abstracts much of the physical storage management.

Developers think:

CREATE TABLE
INSERT
UPDATE
DELETE
SELECT

rather than:

Parquet files
Delta partitions
Spark DataFrames

Strong interview statement:

Lakehouse versus Warehouse is less about where the bytes are stored and more about the compute engine, metadata layer, transaction model, and development experience placed over OneLake.

8. Direct Lake

Direct Lake allows Power BI semantic models to consume Fabric data without the traditional full Import-copy pattern.

OneLake Delta
     ↓
Direct Lake
     ↓
VertiPaq
     ↓
Power BI

Good interview statement:

Direct Lake gives me the performance characteristics of VertiPaq while reducing the need to maintain another traditional imported copy of Fabric data.

9. Fabric Data Security

Key principle:

I use defense in depth. Security is not just Power BI RLS; it starts at identity and continues through Fabric workspace, item, OneLake data, SQL, semantic-model, and governance layers.

Microsoft Entra ID
       ↓
Tenant policies
       ↓
Workspace
       ↓
Fabric Item
       ↓
OneLake data permissions
       ↓
Table / Folder
       ↓
Row / Column
       ↓
Semantic Model
       ↓
Power BI Consumer

10. Entra ID

Use:

  • Users
  • Security groups
  • Service principals
  • Managed identities
  • MFA
  • Conditional Access

Best practice:

Prefer assigning access through Entra groups instead of managing individual user permissions.

11. Workspace Roles

Know:

Admin
Member
Contributor
Viewer

Important principle:

Keep consumers out of administrative workspace roles and grant the narrowest item/data permissions required.

Do not make everyone Contributor.

12. OneLake Security

Think at these levels:

Folder
Table
Row
Column

Example:

Finance Analysts
       ↓
FactRevenue
DimDate
DimCustomer

but not:

SSN
DOB
BankAccount

13. Row-Level Security

East Region Manager
       ↓
East records only

West Region Manager
       ↓
West records only

Use where access is based on:

  • Geography
  • Business unit
  • Department
  • Customer portfolio
  • Security role

14. Column-Level Security

Useful for PII.

DimCustomer

CustomerKey
CustomerName
State
Email
DOB
SSN

General analyst:

CustomerKey
CustomerName
State

Authorized compliance user:

CustomerKey
CustomerName
State
Email
DOB
SSN

15. Object/Table Security

FactSales       Allowed
DimProduct      Allowed
DimCustomer     Allowed

CustomerPII     Denied
EmployeePayroll Denied

16. Warehouse Security

For SQL-centric consumers:

GRANT SELECT
DENY SELECT
GRANT EXECUTE

Use Fabric item permissions and SQL permissions together where appropriate.

17. PII and Medallion

Bronze

Most restrictive.

Bronze may contain:

  • SSN
  • Email
  • Phone
  • DOB
  • Address
  • Credit information

Typical access:

  • Data engineering
  • Approved service identities
  • Limited support users

Silver

Standardize and reduce exposure.

Potential controls:

  • Masking
  • Tokenization
  • Hashing
  • Redaction
  • Data minimization

Gold

Expose only what analytics requires.

Avoid:

FactSales
CustomerName
SSN
DOB
Email
Amount

Prefer:

FactSales
CustomerKey
ProductKey
DateKey
Amount

Key statement:

My strongest PII control is not propagating data that consumers do not need.

18. Purview and Governance

Use Microsoft Purview concepts for:

  • Discovery
  • Classification
  • Sensitivity labels
  • Governance
  • Data lineage

Examples:

Public
Internal
Confidential
Highly Confidential
PII
Financial

Policy should follow classification rather than individual table names.

19. Security Migration Is Separate from Data Migration

Do not assume source controls automatically transfer.

Examples that may need explicit recreation:

RLS
Column permissions
Dynamic masking
Purview labels
Object permissions

Excellent interview statement:

I treat security migration as a separate workstream from data migration. I inventory the source controls and explicitly map them to equivalent Fabric controls rather than assuming they propagate.

20. Security Across Engines

The same underlying OneLake data may be accessible through multiple engines.

OneLake Delta
    ├── Spark
    ├── SQL
    └── Power BI / Semantic Models

Key principle:

Do not secure only the visualization layer when users can access the same underlying data through Spark or SQL.

21. Audit and Monitoring

Be prepared to discuss:

Who accessed data?
Who changed permissions?
Who exported data?
Who created a share?
Who executed privileged operations?

Security should include:

  • Audit logging
  • Access reviews
  • Privilege review
  • Monitoring
  • Alerting
  • Separation of duties

22. 90-Second Migration Answer

I'd start with discovery rather than migration. I'd inventory the SQL estate, Salesforce objects, ETL, reports, security and downstream dependencies and classify each workload as retain, retire, rehost or refactor.

Then I'd define the target architecture and keep operational workloads separate from analytical workloads. Salesforce and SQL Server can remain systems of record while Fabric becomes the analytical platform using a Bronze/Silver/Gold approach.

I'd establish data ownership and attribute-level system-of-record rules, particularly around shared entities like customer and account. I'd also decide where Type 1 versus Type 2 history is required.

I'd migrate by business domain in waves rather than big bang. Each wave would include ingestion, transformation, security, reconciliation and parallel reporting against the legacy environment. Only after row-level, aggregate-level and business KPI validation would I cut consumers over.

Finally, I'd decommission legacy assets only after dependency monitoring proves they're no longer being used. My objective wouldn't just be moving SQL Server into Fabric; it would be simplifying the architecture while creating a governed analytical platform.

23. 60-Second Fabric Security Answer

I'd use defense in depth. Identity starts in Entra ID with group-based access, MFA and Conditional Access. Within Fabric, I'd keep workspace roles restrictive and use item permissions and OneLake security roles for data access.

I'd classify sensitive data with Purview and apply least privilege at table, folder, row and column levels. Bronze would be especially restricted because it contains source-faithful data and potentially the largest concentration of PII. Silver would standardize and minimize sensitive information, and Gold would expose only the attributes required for analytics.

For warehouses I'd also use SQL-level permissions where appropriate, and for Power BI I'd apply semantic-model security as another consumption layer.

Finally, security migration would be explicit. I wouldn't assume SQL Server or Salesforce permissions automatically move with the data. I'd inventory existing security controls, map them into Fabric, validate them using separate user personas, and continuously audit access.

24. Rapid-Fire Interview Lines to Remember

  • Migration: "Workload modernization, not database relocation."
  • Medallion: "Bronze is source-shaped; Gold is business-shaped."
  • SCD: "I decide history requirements at the attribute level."
  • Lakehouse: "Engineering-oriented abstraction over OneLake Delta storage."
  • Warehouse: "Relational SQL experience over OneLake Delta storage."
  • Eventhouse: "High-volume real-time/event analytics using KQL."
  • Direct Lake: "Power BI over OneLake without the traditional full Import-copy cycle."
  • Security: "Defense in depth from Entra identity through OneLake, SQL, semantic models, and governance."
  • PII: "The strongest control is not propagating sensitive data consumers don't need."
  • Security migration: "Never assume source-system security automatically follows the data."

Built With

Share this project:

Updates

Submission history