Lesson 1: Meet the Legacy Pipeline We Will Migrate

Why we start with the old pipeline, not with dbt

Every migration starts with a pipeline that nobody fully understands any more. Before you write a single dbt model you need to know what the old code really does, which is not always what its comments or its author say it does. In this level we take apart the pipeline we will migrate across the whole course. Think of it as a house survey before a renovation: you find the load-bearing walls before you knock anything down.

Assumed platform: SQL Server, with the job scheduled by SQL Server Agent. The company is a fictional wholesaler, Harbour Supplies. The pipeline runs every night and builds a small sales mart that finance and sales dashboards read every morning.

The procedure, step by step

Here is a trimmed version of dbo.usp_LoadSalesMart. Read it slowly and treat each comment as a question you will have to answer during the migration.

CREATE PROCEDURE dbo.usp_LoadSalesMart
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    -- STEP 1: stage "yesterday's" order lines into a temp table
    SELECT o.order_id, o.customer_id, o.order_date,
           ol.product_id, ol.qty, ol.unit_price
    INTO   #orders
    FROM   raw.orders o
    JOIN   raw.order_lines ol ON ol.order_id = o.order_id
    WHERE  o.order_date >= DATEADD(day, -1, CAST(GETDATE() AS date)) -- window depends on WHEN it runs
      AND  o.order_date >= '2019-04-01';  -- hard-coded: "ERP go-live", nobody remembers why it's still here

    -- STEP 2: current customers only
    SELECT customer_id, customer_name, postcode, region
    INTO   #customers
    FROM   raw.customers
    WHERE  is_deleted = 0;

    -- STEP 3: a cursor fills missing regions one row at a time
    DECLARE @id int, @pc varchar(10);
    DECLARE c CURSOR FOR SELECT customer_id, postcode FROM #customers WHERE region IS NULL;
    OPEN c; FETCH NEXT FROM c INTO @id, @pc;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      UPDATE #customers
      SET region = (SELECT TOP 1 region FROM ref.postcode_region
                    WHERE @pc LIKE prefix + '%')   -- TOP 1 with no ORDER BY: which match wins?
      WHERE customer_id = @id;
      FETCH NEXT FROM c INTO @id, @pc;
    END
    CLOSE c; DEALLOCATE c;

    -- STEP 4: upsert the customer dimension (overwrites history)
    MERGE mart.dim_customer AS t
    USING #customers AS s ON t.customer_id = s.customer_id
    WHEN MATCHED THEN UPDATE SET t.customer_name = s.customer_name, t.region = s.region
    WHEN NOT MATCHED THEN INSERT (customer_id, customer_name, region)
                          VALUES (s.customer_id, s.customer_name, s.region);

    -- STEP 5: delete-then-insert yesterday's facts (no explicit transaction!)
    DELETE FROM mart.fact_sales
    WHERE order_date >= DATEADD(day, -1, CAST(GETDATE() AS date));
    INSERT INTO mart.fact_sales (order_id, order_date, customer_key, product_id, revenue)
    SELECT o.order_id, o.order_date, d.customer_key, o.product_id, o.qty * o.unit_price
    FROM   #orders o
    JOIN   mart.dim_customer d ON d.customer_id = o.customer_id  -- inner join silently drops unknown customers
    JOIN   raw.products p      ON p.product_id = o.product_id
    WHERE  p.is_active = 1;                                       -- business rule hidden in a filter

    -- STEP 6: side effects other systems rely on
    TRUNCATE TABLE reporting.sales_summary_cache;  -- the dashboard rebuilds this on first open
    UPDATE etl.load_control SET last_success = GETDATE() WHERE job = 'sales_mart';
  END TRY
  BEGIN CATCH
    INSERT INTO etl.error_log (msg, logged_at) VALUES (ERROR_MESSAGE(), GETDATE());
    -- no THROW: the caller (the Agent job) is never told anything failed
  END CATCH
END

The Agent job Nightly Sales Mart has a single step, EXEC dbo.usp_LoadSalesMart, a daily schedule at 02:00 and retry attempts set to 0.

Draw the dependency diagram

Go through the procedure statement by statement and write down what each one reads and writes. Temp tables count as nodes even though they disappear: a local #temp table created inside a procedure is dropped automatically when the procedure finishes, so you will never find it in the database afterwards. The only record of it is the code.

raw.orders ──┐
             ├─► #orders ───────────────────────────┐
raw.order_lines┘                                   │
                                                   ▼
raw.customers ─► #customers ─(cursor)─► mart.dim_customer ─► mart.fact_sales
ref.postcode_region ─┘                          raw.products ──┘

side effects: reporting.sales_summary_cache (truncated)
              etl.load_control (updated)    etl.error_log (on failure)

Gotcha: the order is the logic. Step 4 has to run before Step 5, because the fact insert joins to dim_customer to find each customer's key. Nothing in the code says so. The dependency only works because the statements happen to run from top to bottom. When you split this into separate models later, every one of these implicit orderings has to become an explicit dependency.

List what it hides

These are the things a migration must keep on purpose or drop on purpose, never by accident:

• Time depends on the clock. "Yesterday" comes from GETDATE(). If the job is rerun at 00:30 after a failure, the window moves forward and the failed day is never loaded. A night when the job was disabled is simply missing.
• Hard-coded dates and filters. '2019-04-01' and p.is_active = 1 are business rules. Sales of products that have since been discontinued vanish from revenue. Ask finance whether that is intended.
• Silent row loss. The inner join to dim_customer drops orders whose customer is deleted or missing.
• Swallowed errors. Errors caught by a CATCH block are not returned to the caller. Unless the block re-raises them (THROW or RAISERROR), the Agent job reports success.
• Partial writes. Step 5 is not wrapped in a transaction, so if the INSERT fails after the DELETE, yesterday's facts are gone and the job still shows green.
• Non-determinism. TOP 1 with no ORDER BY in the cursor can pick a different region from one run to the next.
• Side effects. Truncating the cache and updating load_control are contracts with other systems.

Pitfall: MERGE and duplicates. If raw.customers ever holds two live rows for one customer_id, the MERGE fails with error 8672, "attempted to UPDATE or DELETE the same row more than once". Here that error lands quietly in etl.error_log.

To see what the scheduler believes happened, query the job history. Then compare it with the error log:

SELECT h.run_date, h.run_time, h.run_status, h.message  -- run_status: 0 = failed, 1 = succeeded
FROM   msdb.dbo.sysjobhistory h
JOIN   msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE  j.name = N'Nightly Sales Mart'
ORDER  BY h.run_date DESC, h.run_time DESC;

SELECT * FROM etl.error_log ORDER BY logged_at DESC;     -- the real failure history

If the job history shows success on the same nights that etl.error_log has entries, you have proved that errors are being swallowed.

Write the one-page reference

This page is the course's contract. Every later level checks its work against it.

# Sales Mart pipeline: reference (as found)
Purpose:    Daily revenue by customer, region and product for finance & sales
Schedule:   SQL Server Agent, daily 02:00, 0 retries
Inputs:     raw.orders, raw.order_lines, raw.customers, raw.products, ref.postcode_region
Outputs:    mart.dim_customer (overwritten, no history), mart.fact_sales (rolling 1-day reload)
Consumers:  Finance morning dashboard, sales region report, reporting.sales_summary_cache
Known failures:
  - Rerun after midnight skipped a whole day of sales (window tied to GETDATE)
  - Duplicate customers -> MERGE error 8672, logged only; dashboards stale for days
  - INSERT failed after DELETE -> day of sales missing, job showed success
Open questions: Is excluding inactive products intended? Why 2019-04-01?

The failure history usually comes from people, not logs. Talk to whoever gets the 8 a.m. phone call when the numbers look wrong.

Key takeaways

• Trace every statement's inputs and outputs, and draw temp tables as nodes. They are dropped when the procedure ends, so only the code shows them.
• Statement order hides dependencies, such as the dimension having to load before the fact. Write them down.
• Look for clock-based windows, hard-coded dates, filters that encode business rules, silent inner-join loss and non-deterministic TOP 1.
• A CATCH block that only logs means "green" jobs can be broken. Compare msdb job history with the error log.
• Record side effects and consumers. They are contracts the new pipeline must honour or retire on purpose.
• The one-page reference is the baseline that the migration will be checked against.