Shift Transformation Logic Left

“Shift transformation logic left” basically means: clean, type-cast, standardize, and derive fields upstream (in CTEs/subqueries), so the final SELECT is a thin projection of already-prepared columns. That gives you:

One source of truth for each derived field (no copy-pasted formulas).
Easier testing (you can SELECT * from any CTE).
Better chances for predicate pushdown, column pruning, and join order optimization by the planner.

Samples

Anti-pattern (transformations at the end)

SELECT
  o.order_id,
  o.customer_id,
  -- repeated/complex logic in final select
  CAST(o.order_ts AS DATE) AS order_date,
  (o.quantity * o.unit_price) - COALESCE(o.discount_amount, 0) AS net_amount,
  CASE
    WHEN (o.quantity * o.unit_price) - COALESCE(o.discount_amount, 0) >= 1000 THEN 'HIGH'
    WHEN (o.quantity * o.unit_price) - COALESCE(o.discount_amount, 0) >= 200  THEN 'MEDIUM'
    ELSE 'LOW'
  END AS revenue_band,
  CASE
    WHEN LOWER(TRIM(c.status)) IN ('active','actv') THEN 'ACTIVE'
    WHEN LOWER(TRIM(c.status)) IN ('churned','inactive') THEN 'INACTIVE'
    ELSE 'UNKNOWN'
  END AS customer_status
FROM raw.orders o
LEFT JOIN raw.customers c ON o.customer_id = c.customer_id
WHERE
  -- late filtering on dirty data
  o.order_ts IS NOT NULL
  AND o.quantity > 0;

Problems:

Recomputes net_amount inside a CASE.
Normalization/cleaning happens late.
Filters are applied after joins and without prior type/quality checks.

Best-practice version (shift logic left)

WITH base_orders AS (
  SELECT
    order_id,
    customer_id,
    order_ts,
    quantity,
    unit_price,
    discount_amount
  FROM raw.orders
  WHERE order_ts IS NOT NULL              -- early data quality filter
    AND quantity > 0
),

base_customers AS (
  SELECT
    customer_id,
    status
  FROM raw.customers
),

clean_orders AS (
  SELECT
    order_id,
    customer_id,
    CAST(order_ts AS DATE) AS order_date, -- early type casting
    quantity,
    unit_price,
    COALESCE(discount_amount, 0) AS discount_amount
  FROM base_orders
),

enriched_orders AS (
  SELECT
    co.order_id,
    co.customer_id,
    co.order_date,
    -- single, named source of truth for the metric
    (co.quantity * co.unit_price) - co.discount_amount AS net_amount
  FROM clean_orders co
),

normalized_customers AS (
  SELECT
    customer_id,
    CASE
      WHEN LOWER(TRIM(status)) IN ('active','actv') THEN 'ACTIVE'
      WHEN LOWER(TRIM(status)) IN ('churned','inactive') THEN 'INACTIVE'
      ELSE 'UNKNOWN'
    END AS customer_status
  FROM base_customers
),

banded_orders AS (
  SELECT
    eo.*,
    CASE
      WHEN eo.net_amount >= 1000 THEN 'HIGH'
      WHEN eo.net_amount >= 200  THEN 'MEDIUM'
      ELSE 'LOW'
    END AS revenue_band
  FROM enriched_orders eo
)

SELECT
  bo.order_id,
  bo.customer_id,
  bo.order_date,
  bo.net_amount,
  bo.revenue_band,
  nc.customer_status
FROM banded_orders bo
LEFT JOIN normalized_customers nc
  ON bo.customer_id = nc.customer_id;

What’s improved:

Early filters (order_ts IS NOT NULL, quantity > 0) reduce row counts before joins.
Early type casting (order_date) and normalization (customer_status) live in dedicated CTEs.
Derived metric once (net_amount in enriched_orders) then reused. The final SELECT does no math—it just projects.
Readable layers: raw → clean → enriched → business semantics (banding) → final.

A slightly more advanced pattern (dedupe + window logic shifted left)

WITH src AS (
SELECT
,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY _ingested_at DESC) AS rn
FROM raw.orders_delta
),
dedup AS (
SELECT * FROM src WHERE rn = 1
),
typed AS (
SELECT
order_id,
customer_id,
CAST(order_ts AS TIMESTAMP) AS order_ts,
CAST(unit_price AS NUMERIC) AS unit_price,
CAST(quantity AS INT) AS quantity,
COALESCE(discount_amount, 0) AS discount_amount
FROM dedup
),
metrics AS (
SELECT
,
(quantity * unit_price) – discount_amount AS net_amount
FROM typed
),
ranked AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) AS order_recency_rank
FROM metrics
)
SELECT
order_id,
customer_id,
order_ts,
net_amount,
order_recency_rank
FROM ranked
WHERE order_recency_rank <= 5; – final step: thin projection + simple filter

Quick checklist to apply this rule

Filters early: Null checks, valid ranges, and “keep rows” filters live in the first CTEs.
Types early: Cast to final types before joins/aggregations.
One definition per derived field: Name it once in a CTE; reuse it downstream.
Normalization early: TRIM/LOWER/COALESCE/CASE for categories goes into a “normalized_*” CTE.
Windows early: Do ROW_NUMBER, SUM OVER, etc., upstream so the final select is simple.
Final SELECT is boring: Only picks columns (and perhaps very light aliasing), no heavy expressions.