Star Schema, Semantic Models & Best Practices

The Production Warehouse — What Lives in the edw Schema

The SQL Warehouse in Microsoft Fabric is the source of truth. It's organized into three schemas, each serving a specific purpose:

The Three Schemas

Building the Semantic Model: sm_taxi_business_prod

A semantic model is the analytical layer on top of the warehouse. In the old Power BI terminology, it was called a "Dataset." In Microsoft Fabric, it's called a "Semantic Model."

What Is a Semantic Model?

A semantic model is a logical schema that:

• References tables in the SQL Warehouse (edw schema)
• Defines relationships between fact and dimension tables
• Adds measures via DAX (calculated fields, aggregations)
• Organizes columns into logical display folders
• Serves as the single source of truth for Power BI reports and Copilot queries

The Star Schema Design

The Star Schema is the most popular design for analytical databases. It has:

• One Fact table: cab_trips_f. Contains transactional data (trip_id, driver_id, fare_amount, etc.). Typically large (millions of rows).
• Multiple Dimension tables: com_drivers_d, com_date_d. Contain descriptive attributes (driver_name, day_name, etc.). Typically smaller (thousands of rows).
• Relationships: Fact table has foreign keys to dimensions. Enables efficient joins in Power BI DAX.

The Date Dimension — com_date_d

The date dimension is one of the most important tables in any warehouse. Without it, you can only filter by exact timestamp values. With it, you can slice by year, month, day of week, quarter, and custom holiday calendars.

Date Dimension Columns

Creating the Date Dimension

The date dimension can be created either via DAX in Power BI or via a PySpark notebook. Here's the SQL approach:

CREATE TABLE [edw].[com_date_d] (
    [date] DATE PRIMARY KEY,
    [year] INT,
    [month] INT,
    [month_name] VARCHAR(20),
    [day_name] VARCHAR(20),
    [day_name_short] VARCHAR(3),
    [day_of_week] INT,
    [quarter] INT,
    [is_weekend] BIT
);

INSERT INTO [edw].[com_date_d]
WITH date_range AS (
    SELECT CAST('2020-01-01' AS DATE) AS date_val
    UNION ALL
    SELECT DATEADD(DAY, 1, date_val)
    FROM date_range
    WHERE date_val < '2030-12-31'
)
SELECT
    date_val AS [date],
    YEAR(date_val) AS [year],
    MONTH(date_val) AS [month],
    FORMAT(date_val, 'MMMM') AS [month_name],
    FORMAT(date_val, 'dddd') AS [day_name],
    FORMAT(date_val, 'ddd') AS [day_name_short],
    DATEPART(WEEKDAY, date_val) AS [day_of_week],
    CEILING(MONTH(date_val) / 3.0) AS [quarter],
    CASE WHEN DATEPART(WEEKDAY, date_val) IN (1, 7) THEN 1 ELSE 0 END AS [is_weekend]
FROM date_range;

This generates one row per day from 2020-01-01 to 2030-12-31 (about 4,000 rows). Perfect for a dimension table.

Business Problems Solved — The Analytics Outcomes

Remember Part 1, where we identified the original business problems? Now let's show how they're solved with the complete warehouse and semantic model:

Problem 1: Trips & Revenue by Hour/Day/Week/Month/Year

Solution: cab_trips_f is joined to com_date_d. A simple Power BI report can now show:

• Total trips by month (GROUP BY month_name)
• Average fare by day of week (GROUP BY day_name, aggregating fare_amount)
• Revenue trend by quarter (GROUP BY year, quarter)

Problem 2: Trip Cancellation Analysis

Solution: The cab_trips_f fact table includes trip_status. A simple filter WHERE trip_status = 'CANCELLED' reveals all cancellations. Join to driver dimension to see which drivers have the highest cancellation rate.

Problem 3: Driver Performance Report

Solution: Aggregate cab_trips_f by driver_id, joined to com_drivers_d. Metrics: trips_completed (COUNT), avg_fare, avg_rating, tenure_days. Slice by vehicle_type and licensing_status from the driver dimension.

Problem 4: Revenue Leakage Analysis

Solution: Compare the sum of fare_amount for completed trips vs cancelled trips. Pivot by date to see daily trends. Identify which drivers contribute to leakage.

Naming Conventions — A Complete Reference

Naming conventions are critical for maintainability. When a new team member joins, they should be able to look at a table name and immediately understand its purpose, type, and location.

Resource-Level Naming

Pipeline Activity Naming

Table & Column Naming

Pattern: nb_{schema}_{table}_{type}

• nb = Notebook prefix
• com = Schema name (common/shared)
• drivers = Table name
• d = Type (d=dimension, f=fact)

Audit Column Naming

Optimization Techniques Used

These techniques have proven their value at scale:

1. limit(1).count() for Existence Checks

Instead of count() which scans the entire table, use limit(1).count() which stops as soon as it finds the first row. For a 1-million-row table, this is 1000x faster.

2. Disable Sequential Run in ForEach

If your pipeline has a ForEach activity that processes multiple tables, disable "Sequential Run" in the settings. This processes tables in parallel, cutting pipeline execution time from hours to minutes.

3. Incremental Load + CDC Watermark

Only process new/changed records. Store last_pipeline_runtime in a config table. Read only records WHERE last_modified_at >= last_runtime. For large tables (100M+ rows), this reduces processing time from 30 minutes to 5 minutes.

4. Early Exit on Count=0

If the landing table has no new rows, exit the notebook immediately with notebookutils.notebook.exit(). This saves Spark compute and prevents unnecessary downstream processing.

5. Staging Table Pattern for JDBC Writes

Write to staging, then merge to production. This decouples the slow JDBC write from the fast SQL MERGE, preventing table locks and enabling safe retry logic.

6. Archive Before Overwrite

Before overwriting landing data, copy it to an archive table. If something goes wrong, you can query the archive without needing external backups.

7. Drop Table Before Overwrite (Landing Only)

When incremental copy returns 0 rows, Copy Activity writes blank strings to DateTime columns instead of NULLs. Next run fails on schema conflict. Solution: Drop the landing table before Copy Activity runs. Let it recreate with correct schema.

Challenges Faced & Solved (Real-World Lessons)

No production system is perfect. Here are the real challenges we encountered and how we solved them:

Challenge 1: DateTime Blank String Problem

The Issue: When the incremental copy from SQL Server returns 0 rows (no new data), Fabric's Copy Activity writes blank strings into DateTime columns instead of NULLs. The next day, when there IS new data, the Copy Activity fails because the schema changed: blank string vs DateTime.

The Solution: Drop the landing table before every Copy Activity run. Let the Copy Activity recreate it from scratch each time. This is safe because landing is just a temporary staging area.

CREATE PROCEDURE [dbo].[drop_landing_tables]
AS
BEGIN
    DROP TABLE IF EXISTS [cab_landing].[DRIVERSENTITY];
    DROP TABLE IF EXISTS [cab_landing].[TRIPSHEADERENTITY];
    DROP TABLE IF EXISTS [cab_landing].[TRIPSLINEENTITY];
END;

Challenge 2: Dependent Notebooks Running on Stale Transient Data

The Issue: nb_cab_trips_f (Level 2) depends on nb_com_trips_header_f (Level 1). If Level 1 has no new data and exits early, Level 2 still runs and picks up the OLD transient data from yesterday. This results in duplicate facts.

The Solution: Level 2 notebooks read the last_pipeline_runtime of their dependent Level 1 notebooks from the config table. They apply this watermark as a filter on transient data. If the watermark hasn't moved, the filter returns 0 rows, Level 2 exits cleanly.

# Get the last pipeline runtime for trips_header (dependency)
trips_header_control = landing_control_tbl.get("TRIPSHEADERENTITY")
last_runtime_header = get_runtime(
    trips_header_control.get("runtime"),
    trips_header_control.get("load_type")
)

# Only read transient data newer than the header's last runtime
com_trips_header_f_transient = spark.read.table("lh_taxi_business.cab_transient.com_trips_header_f") \
    .filter(col("last_modified_at") >= lit(str(last_runtime_header)))

# If no data, exit early
if com_trips_header_f_transient.limit(1).count() == 0:
    notebookutils.notebook.exit({'message': "No new data since last runtime"})

Challenge 3: JDBC Token Expiry

The Issue: Fabric managed identity tokens expire after ~1 hour. For very large datasets, a single JDBC write could exceed the token's validity period, causing the write to fail halfway.

The Solution: Request a fresh token at the start of each notebook run. The mssparkutils.credentials.getToken() call in nb_constants_and_functions happens every time, ensuring the token is always fresh.

Best Practices Summary

Based on building this project to production scale, here are the essential best practices:

Data Architecture

• Always separate Dev and Prod workspaces. Dev workspace for testing, Prod for reporting. Different data access policies.
• Use read-only accounts for gateway connections. If an account is compromised, attackers can only read, not modify data.
• Never hardcode table names in pipelines. Use config tables to store table names, schemas, and watermarks. Makes the pipeline reusable across environments.
• Always watermark your incremental loads. Store last_pipeline_runtime in a config table. Only process new data. Cuts processing time from O(n) to O(delta).

Data Quality

• Archive before overwrite. Data is always recoverable without external backups.
• Use integration_id for idempotent upserts. Guarantees no duplicate keys, no data loss.
• Implement count checks. Validate landing row counts match source. Catch unexpected data changes early.
• Always include audit columns. data_source, w_insert_date, w_modified_date, etl_created_by allow tracing any data back to its source.

Code & Deployment

• Write notebooks as reusable templates. Copy existing notebook, change 3 lines. 20 minutes to production.
• Use lower_snake_case for all warehouse column names. Consistency across team. Better for Power BI naming conventions.
• Document the column rename CSV. This is the critical metadata file. Keep it updated.
• Use meaningful variable names. com_drivers_d_landing is clear. df1 is not.

Future Enhancements — The Road Ahead

This project is production-ready today. Here's what Phase 2 looks like:

Where to Publish — Complete Publishing Strategy for Maximum Reach

You've built an impressive end-to-end data platform. Now it's time to share it with the world. Here's a strategic publishing plan that will maximize visibility and recruiter reach:

Platform 1: LinkedIn Articles + Posts (Essential)

LinkedIn is where recruiters at FAANG and top-tier tech companies spend their time.

• Strategy: Publish each part as a LinkedIn Article. Create a teaser post for each article: "I just published Part X of my Microsoft Fabric series. Here's the #1 thing I learned..."
• Hashtags: #MicrosoftFabric #DataEngineering #Azure #PySpark #DeltaLake #DataEngineer #Fabric
• Engagement: Tag Microsoft Fabric team account, Fabric MVPs, and executives at your target companies
• Timeline: Publish weekly (one part every Thursday)

Platform 2: Towards Data Science / Medium (High Impact)

Towards Data Science (TDS) is the most-read data publication on Medium with 600K+ followers.

• Strategy: Submit each part as a TDS article. TDS editors will review and feature the best ones.
• Why: Medium articles rank very well on Google for technical keywords. TDS features get 10-50K views each.
• How: Go to TDS.com → Submit → paste your blog post → follow their guidelines
• Expected reach: 10K-20K views per article for quality technical content

Platform 3: Dev.to (Fast Growth)

Dev.to has 1M+ developer readers and excellent Google SEO.

• Strategy: Republish each part on Dev.to. Use tags: #fabric #dataengineering #pyspark #azure #python
• Why: Dev.to posts often rank on Google page 1 for technical queries within days
• Timeline: Publish day-after LinkedIn (Friday)

Platform 4: Hashnode (SEO + Authority)

Hashnode is the SEO-first developer blogging platform.

• Strategy: Use Hashnode's custom domain feature to publish on your own domain (e.g., rohitram.dev/fabric-series)
• Why: Posts automatically get backlinks from Hashnode's CDN. Boosts your Google authority.
• Canonical URL: Set Hashnode as canonical, link back to your main site

Platform 5: Microsoft Tech Community (Strategic)

This is where Fabric product managers and Microsoft recruiters read.

• Platform: blogs.microsoft.com or techcommunity.microsoft.com
• Why: Microsoft's own platform. Posts get amplified by Microsoft's social channels. This is the #1 way to get noticed by FAANG/Microsoft recruiters specifically.
• Process: Contact Microsoft Fabric team for guest author access

Platform 6: GitHub Repository (Portfolio Piece)

Create a public GitHub repo with all notebooks, SQL files, and links to the blog series.

• README.md: Overview of the series. Link to all 8 blog posts. Code walkthrough.
• Files: Clean, documented notebooks. SQL scripts. Architecture diagrams.
• Why: Recruiters check GitHub profiles. A polished repo with linked blog posts is a portfolio differentiator.

Platform 7: Personal Website / Portfolio (Your Hub)

This is your canonical hub. All other platforms link back here.

• Options:
  • Hashnode custom domain (easiest)
  • GitHub Pages (free, technical)
  • Vercel (modern, fast)
• Content: All 8 blog posts, links to LinkedIn/Medium/Dev.to versions, featured projects
• Why: This is your brand. Google builds author authority around your name + Microsoft Fabric

The Google SEO Secret

This series will rank on Google for technical keywords. Here's why:

1. Long-form technical content: 2500-3500 words per post. Google rewards depth.
2. Specific technical keywords: "DeltaTable.merge Fabric", "JDBC write Fabric Warehouse", "Medallion Architecture Fabric". Real engineers search for these exact phrases.
3. Code examples: Google understands code blocks. Your code snippets will appear in search results.
4. Series structure: Internal linking between 8 posts builds topical authority. Google recognizes you as an expert in "Microsoft Fabric Data Engineering."
5. Cross-platform publishing: Backlinks from Medium, Dev.to, LinkedIn boost domain authority. More backlinks = higher Google ranking.
6. Regular publishing: Google rewards consistent creators. Publish every week for 8 weeks. Strong signal.

Series Wrap-Up: From Architecture to Delivery

We've completed the full data engineering lifecycle:

What This Project Demonstrates

• Part 1: Understanding the medallion pattern and why it matters
• Part 2-3: Data source integration using gateways and incremental copy
• Part 4: Building the foundational workspace, lakehouse, and warehouse
• Part 5: Orchestrating the landing pipeline with watermarks and error handling
• Part 6: Curating data with metadata-driven patterns and business rules
• Part 7: PySpark transformations, DeltaTable upserts, and warehouse writes
• Part 8 (This part): Designing a Star Schema semantic model for analytics

The Production System

The patterns and architecture described in this series are used at enterprise scale by companies like Uber, Lyft, Airbnb, and Databricks. The medallion architecture, CDC watermarks, DeltaTable semantics, and Star Schema modeling are industry standards.

If you're a data engineer evaluating this project: the code is real. The challenges are real. The solutions are battle-tested at production scale.

Final Thoughts

Data engineering is fundamentally about building systems that are:

• Reliable: Idempotent upserts, error handling, audit trails
• Scalable: Metadata-driven pipelines, reusable notebooks, incremental processing
• Maintainable: Clear naming conventions, documented metadata, version control
• Observable: Watermarks, config tables, data quality checks

This project embodies all four principles. It's a template you can copy, scale, and adapt to any data platform.

Rohit Ram

Senior Data Analyst · LinkedIn

Building production-grade data platforms on Microsoft Fabric. This series documents a real project — every SQL query, every design decision, every challenge encountered and solved.