Working with Microsoft Fabric?
├─ Need storage layer?
│   ├─ Semi-structured / schema evolution → Lakehouse
│   ├─ Full T-SQL / stored procs / DML → Warehouse
│   └─ Unsure → Start with Lakehouse (more flexible)
├─ Need data transformation?
│   ├─ Simple data copy → Pipeline Copy Activity
│   ├─ Light transforms (300+ built-in) → Dataflow Gen2
│   └─ Complex logic / ML / custom code → Spark Notebook
├─ Need orchestration?
│   └─ Pipeline with activities + dependencies
├─ Need reporting layer?
│   └─ Semantic Model with DirectLake mode
└─ Need conversational analytics?
    └─ See: fabric-data-agent skill

Lakehouse/
├── Tables/          # Managed Delta tables (structured, queryable)
├── Files/           # Unmanaged files (raw CSV, Parquet, JSON staging)
└── SQL endpoint     # Auto-generated read-only T-SQL access to Tables/

-- Query via SQL endpoint (read-only — SELECT, SHOW, DESCRIBE)
SELECT COUNT(*) AS total_events
FROM raw_events
WHERE event_date >= '2025-01-01';

-- Schema discovery
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'raw_events';

# Write Delta table
df.write.format("delta").mode("overwrite").saveAsTable("bronze.raw_events")

# Merge / upsert pattern (Silver layer)
from delta.tables import DeltaTable
target = DeltaTable.forName(spark, "silver.customers")
target.alias("t").merge(
    source_df.alias("s"), "t.customer_id = s.customer_id"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

# Time travel
spark.read.format("delta").option("versionAsOf", 5).table("bronze.raw_events")

-- Create fact table
CREATE TABLE fact_sales (
    sale_id BIGINT IDENTITY(1,1),
    product_key INT NOT NULL,
    customer_key INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(18,2) NOT NULL
);

-- Stored procedure for incremental load
CREATE PROCEDURE usp_load_daily_sales @run_date DATE
AS
BEGIN
    INSERT INTO fact_sales (product_key, customer_key, sale_date, amount)
    SELECT p.product_key, c.customer_key, s.sale_date, s.amount
    FROM staging.raw_sales s
    JOIN dim_product p ON s.product_id = p.product_id
    JOIN dim_customer c ON s.customer_id = c.customer_id
    WHERE s.sale_date = @run_date;
END;

1. Check for existing sessions FIRST (reuse idle sessions)
2. Create only if none exist (cold start: 3-6+ minutes)
3. State machine: not_started → starting → idle (ready) → busy → dead
4. Never close sessions unless explicitly requested (reuse saves time)
5. Use timestamped session names for traceability

┌─────────────┐     ┌───────────────┐     ┌──────────────┐
│ Copy (ADLS   │────→│ Notebook      │────→│ Notebook     │
│ → Bronze LH) │     │ (Bronze→Silver)│     │ (Silver→Gold)│
└─────────────┘     └───────────────┘     └──────────────┘
                         depends_on            depends_on

-- Year-over-year growth
YoY Growth % =
VAR CurrentYear = [Total Sales]
VAR PriorYear = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN DIVIDE(CurrentYear - PriorYear, PriorYear, 0)

-- Running total
Running Total = CALCULATE([Total Sales], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])))