Transforming data with SQL

On Thursday, October 29, the third model session moved to the wh_sales warehouse, where the hub's star schema would live. Keiko had written T-SQL for years against StockLine, but always to read data. Marisol wanted her to write the code that builds tables: cleaning products and stores, keeping history when a store changes region, and loading the fact table each night. "In a warehouse, SQL is not just for questions," she said. "It is how the tables get made."

Where T-SQL runs in Fabric

Two Fabric items accept T-SQL. A warehouse is fully read and write: tables can be created, loaded, updated and deleted with T-SQL, and several tables can change in one transaction. A lakehouse's SQL analytics endpoint is read-only for table data, but it supports views, inline table-valued functions and stored procedures that read data, and it can grant SQL permissions on them. Both store tables as Delta in OneLake, and a warehouse can read lakehouse tables in the same workspace with three-part names, such as lh_curated.dbo.products, so a warehouse procedure can pull cleaned data from the lakehouse and write the star schema.

Cleaning with views first

Marisol's habit was to write cleaning logic as a view before turning it into a table. A view is just a saved query, so it can be changed in seconds and checked against the source, and only when the logic is right does it become a table that a procedure loads. Keiko's first view cleaned StockLine's product table: trimming SKUs, replacing blank category names with "Unassigned", converting the pack size from text to a whole number, and keeping only active products.

CREATE VIEW stg.v_products AS
SELECT
    UPPER(LTRIM(RTRIM(p.sku)))                     AS Sku,
    p.description                                  AS ProductName,
    COALESCE(NULLIF(LTRIM(RTRIM(p.category)), ''), 'Unassigned') AS Category,
    TRY_CAST(p.pack_size AS int)                   AS PackSize,
    p.modified_at                                  AS ModifiedAt
FROM lh_curated.dbo.stockline_products AS p
WHERE p.is_active = 1;

Three functions do most of the cleaning work. COALESCE returns the first non-null value in its list, so it replaces nulls with a default. NULLIF turns a value into a null when it equals something, here an empty string, so that COALESCE can catch it too. TRY_CAST converts a value and returns null instead of failing when it cannot, which is safer than CAST on messy data; Keiko added a check afterwards that counted the nulls TRY_CAST produced, and found 14 products whose pack size had been typed as "12 pk".

Building tables with CTAS

CREATE TABLE AS SELECT, or CTAS, creates a new table from a query's result in one statement. It is the quickest way to materialize a view, and it is efficient in the warehouse because it writes the new table's files in parallel. A common pattern for full rebuilds is to create the new version of a table with CTAS under a temporary name, check it, and then swap it in with a rename, so that readers never see a half-built table.

CREATE TABLE dbo.dim_product_new AS
SELECT ROW_NUMBER() OVER (ORDER BY Sku) AS ProductKey, Sku, ProductName, Category, PackSize
FROM stg.v_products;

-- after checking row counts and nulls:
EXEC sp_rename 'dbo.dim_product', 'dim_product_old';
EXEC sp_rename 'dbo.dim_product_new', 'dim_product';

The ProductKey column is a surrogate key: a meaningless whole number that identifies each row of the dimension, separate from the business key, the SKU. Lesson 5 explains why star schemas use surrogate keys. Rebuilding with ROW_NUMBER, though, gives products new keys whenever the order of SKUs changes, which would break the fact table's links; for dimensions that grow, the key must be assigned once and kept. Keiko changed the procedure to keep existing keys and number only new SKUs from the current maximum plus one.

Slowly changing dimensions

Dimension attributes change. A store moves from the West region to a new Prairie region; a product is moved to a different category; a customer changes Rewards tier. A slowly changing dimension, or SCD, is the rule for what the dimension does when that happens, and the DP-600 outline names the two that matter most.

Type 1 overwrites the old value. It is simple, and right when the old value was a mistake or when nobody needs history: a corrected product name should simply replace the misspelled one. Type 2 keeps history: the current row is closed by setting an end date and a current flag to false, and a new row with a new surrogate key is added with the new value. Facts recorded before the change keep pointing at the old row, so a report of last year's sales by region still shows the old region, which is what the business usually expects.

The hub needed Type 2 for exactly that case. On November 1, 2026, Store 15 and three others were moving from the West region to a new Prairie region, and Grace wanted last quarter's margin reported under the regions as they were then. Keiko wrote the change as two statements in one transaction, so that the dimension could never be left with the old row closed and no new row added.

BEGIN TRANSACTION;

UPDATE d SET ValidTo = '2026-10-31', IsCurrent = 0
FROM dbo.dim_store AS d
JOIN stg.v_stores  AS s ON s.StoreId = d.StoreId
WHERE d.IsCurrent = 1 AND s.Region <> d.Region;

INSERT INTO dbo.dim_store (StoreKey, StoreId, StoreName, Region, ValidFrom, ValidTo, IsCurrent)
SELECT (SELECT MAX(StoreKey) FROM dbo.dim_store) + ROW_NUMBER() OVER (ORDER BY s.StoreId),
       s.StoreId, s.StoreName, s.Region, '2026-11-01', '9999-12-31', 1
FROM stg.v_stores AS s
WHERE NOT EXISTS (SELECT 1 FROM dbo.dim_store AS d
                  WHERE d.StoreId = s.StoreId AND d.IsCurrent = 1);

COMMIT TRANSACTION;

The insert's NOT EXISTS test is what makes the two statements work together: after the update, a store whose region changed no longer has a current row, so it qualifies for a new one, and a store that did not change still has its current row and is skipped. The same logic is easy to check on a few rows in Python before running it against the warehouse.

# Apply a Type 2 change to a tiny store dimension
dim = [
    {"key": 101, "store": 15, "region": "West", "from": "2019-04-01", "to": "9999-12-31", "current": True},
    {"key": 102, "store": 16, "region": "West", "from": "2019-04-01", "to": "9999-12-31", "current": True},
]
source = {15: "Prairie", 16: "West"}        # store 15 moves, store 16 does not
change_date, day_before = "2026-11-01", "2026-10-31"

for row in dim:                              # step 1: close rows whose region changed
    if row["current"] and source[row["store"]] != row["region"]:
        row["to"], row["current"] = day_before, False

next_key = max(r["key"] for r in dim) + 1
for store, region in source.items():         # step 2: add a row where none is current
    if not any(r["store"] == store and r["current"] for r in dim):
        dim.append({"key": next_key, "store": store, "region": region,
                    "from": change_date, "to": "9999-12-31", "current": True})
        next_key += 1

for r in dim:
    print(r["key"], r["store"], r["region"], r["from"], r["to"], r["current"])

101 15 West 2019-04-01 2026-10-31 False
102 16 West 2019-04-01 9999-12-31 True
103 15 Prairie 2026-11-01 9999-12-31 True

Joining, aggregating and de-aggregating

Other transformations in the outline come up constantly. Joins combine tables, and the choice between an inner join, which keeps only matching rows, and a left join, which keeps every row from the first table, decides whether unmatched sales disappear or appear with a null product. Keiko always ran both and compared the counts: in the first fact load, 312 sales lines had SKUs missing from the product dimension, and an inner join would have dropped them silently. The fix was an "unknown product" row in dim_product with key -1, so those sales stayed in the totals and showed up in a data quality report.

Aggregation reduces detail with GROUP BY, for example from sales lines to daily sales by store and product, which can make a table hundreds of times smaller for reports that never need line detail. De-aggregation goes the other way, spreading a monthly target across days or stores by a weighting, and it needs care so that the parts add back exactly to the whole. Denormalizing copies attributes into one wider table, such as putting the category name into the product dimension rather than keeping a separate category table, which is exactly what a star schema's dimensions do.

Stored procedures and scheduling

Once each step worked, Keiko wrapped them in stored procedures: one for each dimension and one for the nightly fact load. A pipeline then runs the procedures in order after Ines's copy activities finish, with a failure path that sends a message to the team channel. Keeping the logic in procedures, rather than in dozens of pipeline activities, means it can be read in one place, versioned through the warehouse's Git integration, and tested in the Test workspace before deployment.

Performance in the warehouse

The warehouse engine handles most queries well without tuning, but a few habits help. Select only the columns needed, because the files are columnar and unread columns cost nothing. Filter early, especially on dates. Load in large batches with CTAS, INSERT ... SELECT or COPY INTO rather than many single-row inserts, which create many small files and log entries. And check the query insights views, which keep a history of recent queries with their durations, to find the slow ones before users do.

Testing belongs in the same habit. Before each procedure ran in Test, Keiko wrote three checks that she now runs after every load: the fact table's row count for the day matches the copied source rows, no fact row has a null key, and the total sales amount for the day matches StockLine's own end-of-day total to the cent. If any check fails, the procedure raises an error and the pipeline stops before the semantic model is refreshed, so store managers see yesterday's correct numbers rather than today's wrong ones.