Reliability seams

Daybreak's pipeline now extracts, moves, stores, transforms, and processes data at any scale it will realistically see (b2-b8). It works - on good days. This session is about the bad days: the pipeline running unattended when a source silently changes shape, or a batch arrives half-corrupt. Building a pipeline that survives those moments is the last engineering skill before the capstone, and it is where you hand the running system to the operations world.

A pipeline is a chain of handoffs, and every handoff is a seam under tension. The stages themselves rarely fail - your GROUP BY does not spontaneously break. What breaks is the assumption at a seam: that the source still has the columns you expect, that the data is the type it claims, that a run either fully finishes or fully rolls back. Name the seams and you know exactly where to build your defenses.

Good pipelines expect bad input. Instead of trusting the feed and crashing when it lies, you build checks into the pipe: assert the shape is what you agreed, cast defensively so bad values become nulls rather than exceptions, route unparseable rows to a quarantine table, and make every run safe to repeat. The goal is a pipeline that degrades gracefully - it flags the problem and keeps the good data flowing.

-- simulate a messy feed: real orders plus two malformed rows
CREATE OR REPLACE TABLE raw_feed AS
SELECT order_id, order_date FROM orders
UNION ALL
VALUES (9001, 'not-a-date'), (9002, '2026-13-40');

-- TRY_CAST returns NULL on failure instead of throwing - the good rows survive
SELECT count(*)                                       AS total_rows,
       count(TRY_CAST(order_date AS DATE))            AS good_dates,
       count(*) - count(TRY_CAST(order_date AS DATE)) AS quarantined
FROM raw_feed;

This seam is also the boundary of the course. Data engineering builds the resilient pipeline and emits the signals (row counts, quarantine counts, a clear pass or fail). DataOps operates it: schedules the runs, monitors the signals, alerts a human when something drifts, and tests changes in CI before they ship. Build versus operate - two crafts meeting at one line.

One script, one seam defended. A messy feed arrives with two malformed rows. You will cast defensively, filter the unparseable rows into a clean table, and count what was quarantined - proving a bad row does not kill the run, it just gets set aside.

-- 1) a messy incoming feed: real orders + two malformed rows
CREATE OR REPLACE TABLE raw_feed AS
SELECT order_id, customer_id, order_date, status, channel FROM orders
UNION ALL
VALUES (9001, 99, 'not-a-date',  'completed', 'web'),
       (9002,  3, '2026-13-40',  'completed', 'app');

-- 2) defensive transform: keep only rows whose date parses
CREATE OR REPLACE TABLE clean_orders AS
SELECT order_id, customer_id,
       TRY_CAST(order_date AS DATE) AS order_date,
       status, channel
FROM raw_feed
WHERE TRY_CAST(order_date AS DATE) IS NOT NULL;

-- 3) prove the bad rows were quarantined, not fatal
SELECT (SELECT count(*) FROM raw_feed)      AS rows_in,
       (SELECT count(*) FROM clean_orders)  AS rows_kept,
       (SELECT count(*) FROM raw_feed)
         - (SELECT count(*) FROM clean_orders) AS quarantined;

Two hands-on checks and one boundary question. Each editor starts from Daybreak's raw source. Write the guard, run it, read the result - this is the defensive loop you will use for every pipeline.

-- naming the columns asserts they exist, and the counts check for null keys
SELECT count(*)                                    AS total,
       count(*) FILTER (WHERE order_id IS NULL)    AS null_order_id,
       count(*) FILTER (WHERE customer_id IS NULL) AS null_customer_id,
       count(*) FILTER (WHERE order_date IS NULL)  AS null_order_date
FROM orders;

-- simulate a re-delivered batch: two duplicate order_ids with a newer date
CREATE OR REPLACE TABLE dup_feed AS
SELECT order_id, order_date, status FROM orders
UNION ALL
SELECT order_id, '2026-06-30', 'refunded' FROM orders WHERE order_id IN (1001, 1002);

-- keep only the latest row per order_id
WITH ranked AS (
  SELECT order_id, order_date, status,
         row_number() OVER (PARTITION BY order_id
                            ORDER BY order_date DESC) AS rn
  FROM dup_feed
)
SELECT order_id, order_date, status
FROM ranked
WHERE rn = 1
ORDER BY order_id
LIMIT 10;

• Pick one pipeline you own. Name its three seams (source-to-ingest, ingest-to-transform, transform-to-serve) and which failure mode each is most exposed to.
• Add a TRY_CAST quarantine step to Demo 1 for a second column (say a numeric field), and report both quarantine counts.
• Draft a one-paragraph data contract for a source you depend on: its columns, their meanings, and how fresh you need it.
• For a pipeline at work, ask: could I re-run today's job right now without double-counting? If not, that step is not idempotent yet.
• List which of your current reliability tasks are really DataOps, and note them as candidates for the learn-dataops course.

This session teaches the reliability undercurrent of Reis & Housley's Fundamentals of Data Engineering and the source-reliability material from the DeepLearning.AI Data Engineering Professional Certificate, run on a live engine. Certificates, cloud labs, and videos stay on the official platforms. This page covers: