07 SQL highlights: what the code does
The model in jcars_stg.sql starts from the seed with WITH source AS (SELECT * FROM {{ ref("Jcars_logistics_dataset") }}) and selects every column through one cleaning rule.
Blank records and placeholders become NULL
Empty strings and fake values such as N/A, unknown, #DATE! or TBD are not real data. NULLIF() converts a value to NULL when it matches, and a CASE branch does the same for longer lists of placeholders. Money columns list their own error strings, such as #VALUE!, missing and not available.
NULLIF(NULLIF(TRIM(INITCAP(customer_name)), 'N/A'), '') AS customer_name,
CASE
WHEN ORDER_DATE IS NULL
OR LOWER(TRIM(TO_VARCHAR(ORDER_DATE))) IN
('null', 'not sure', '#date!', '', 'none')
THEN NULL
...
END AS cleaned_order_date
Trimming and casing
Every text column passes through TRIM(), so leading and trailing spaces never create false categories. Matching is done on LOWER(TRIM(column)), so KAKAMEGA, kakamega and Kakamega are treated alike. INITCAP() then gives a single display casing, and UPPER() is used for IDs. In the raw file, 35 sales-rep names also carried double spaces, such as Mercy Atieno, which the standardisation step removes.
Deduplication and a safe key
An order can only be counted once if its key is written one way. The model normalises order_id with TRIM(UPPER(REGEXP_REPLACE(order_id, '-', ''))), so LCL-1001 and lcl1001 become the same value. Profiling the raw file found no fully identical rows, but it did find 19 blank or N/A IDs and three IDs reused by different orders (ord1020, CAR1086 and ord1174). Because IDs follow the order of the source rows, all 22 were restored from that sequence and every Order ID in the cleaned file is now unique. The check that guards this is simple:
-- duplicate check on the cleaned key (expect zero rows)
SELECT order_id, COUNT(*) AS n
FROM {{ ref('jcars_stg') }}
GROUP BY order_id
HAVING COUNT(*) > 1;
-- keep one row per order if duplicates ever appear
SELECT *
FROM {{ ref('jcars_stg') }}
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY cleaned_order_date DESC) = 1;
Consistency through standardisation
Free-typed categories are mapped to one label with CASE WHEN LOWER(TRIM(x)) IN (...). This collapses typos, abbreviations and mixed casing into a short, fixed list. Where the pattern is simpler, nested REGEXP_REPLACE() calls do the same job, for example Dsl to Diesel or A/T to Automatic.
CASE
WHEN LOWER(TRIM(customer_type)) IN ('ngo', 'n.g.o') THEN 'NGO'
WHEN LOWER(TRIM(customer_type)) IN ('corp', 'corporate', 'corporateorate') THEN 'Corporate'
WHEN LOWER(TRIM(customer_type)) IN ('car dealer', 'dealer') THEN 'Dealer'
WHEN LOWER(TRIM(customer_type)) IN ('govt', 'government') THEN 'Government'
WHEN LOWER(TRIM(customer_type)) IN ('n/a', '') THEN NULL
ELSE INITCAP(TRIM(customer_type))
END AS customer_type
Correct data types
The seed loads every column as text, so each field is converted on purpose. The TRY_ functions return NULL instead of failing the whole run when a value cannot be parsed. Money has currency words and commas stripped with REGEXP_REPLACE, and a value ending in M is multiplied by 1,000,000, so 9.14M becomes 9,140,000. Discounts written as 7%, 0.07 or 12 percent all end up as one decimal fraction.
CASE
WHEN UPPER(TRIM(unit_selling_price)) LIKE '%M%'
THEN TRY_TO_DECIMAL(REGEXP_REPLACE(TRIM(unit_selling_price), '[^0-9.]', ''), 38, 4) * 1000000
ELSE TRY_TO_NUMERIC(REGEXP_REPLACE(TRIM(unit_selling_price), '[^0-9]', ''))
END AS unit_selling_price
Mixed date formats
Dates are the messiest field. The model detects each pattern with REGEXP_LIKE and parses it accordingly: Excel serial numbers through DATEADD from 1899-12-30, slash dates through COALESCE(TRY_TO_DATE(...), TRY_TO_DATE(...)), years mis-typed as 0025, and swapped month and day such as 2026-13-04.
WHEN REGEXP_LIKE(TRIM(TO_VARCHAR(ORDER_DATE)), '^[0-9]{1,6}$')
THEN DATEADD(DAY, TRY_TO_NUMBER(TRIM(TO_VARCHAR(ORDER_DATE)))::INTEGER, DATE '1899-12-30')
Outliers
Impossible values are rejected rather than averaged in. Ages of -5 and 0, the vehicle year 2032, and discounts outside a sensible range are turned into NULL or rescaled, so they cannot distort averages and totals.