dbt Core 2.0: jaffle-shop validation #734

Sub-issue of #660. Found validating the official jaffle-shop project (ClickHouse profile) against dbt-core 2.0: dbt seed + dbt build work on dbt-clickhouse v1 but fail on v2. Root-causing the difference surfaced two previously untracked bugs (this issue) plus two already-tracked gaps (references at the bottom).

The headline: v2 could silently lose seed data while reporting success. Any statement the engine's SQL lexer cannot tokenize was dropped without executing and without any error — and the ClickHouse seed INSERT is such a statement whenever the CSV data contains a single quote. The generic silent drop is fixed on dbt-core main (merged, no public PR) and the ClickHouse-side half (lexer routing so ClickHouse SQL actually tokenizes, plus the unit-test fix) is merged in dbt-labs/dbt#16153: seed, run and build pass on main. The last seed-data gap (a ? in CSV data inserted doubled) is closed by dbt-labs/dbt#16156 (row below).

Status legend: ✅ implemented — merged and working on dbt-core 2.0 origin/main · 🟡 partial — partly working on origin/main; Notes say what works vs what fails · 🚧 in progress — implemented in an open PR (linked in Notes) · 🔴 not started — not working on origin/main · Combined status "A-🚧->B": the feature is at A on origin/main today; an open PR is driving it to B (🟡 partially working / ✅ fully working)

Tracking note: the two seed data bugs above (' loss, ? doubling) are deliberately tracked HERE and not in the seed materialization issue (#709, closed) — an exception to the one-canonical-row rule. #709 stays as-is; both are fixed on main.

Repro

Seed data loss (no longer reproduces on main; kept so the original symptom is recognisable):

# raw_products.csv contains "kevin parker's favorite!"
dbt seed --full-refresh --vars '{"load_source_data": true}'   # reported Succeeded
# raw.raw_products had 0 rows (v1: 10); no INSERT appeared in debug logs

Minimal case was: any seed CSV with a ' in a string value → table created, 0 rows, exit 0. Downstream symptom that exposed it: dbt_utils_expression_is_true_orders_order_items_subtotal_subtotal failed with 61,465 bad rows (order items joined against the empty products table lose their prices).

# build failure (no longer reproduces on `main`; tracked in #710)
dbt build
#     Failed [  0.04s] unit_test default_dbt_test__audit.test_supply_costs_sum_correctly (models/marts/order_items.yml:126:11)
#     Failed [  0.58s] unit_test default_dbt_test__audit.test_does_location_opened_at_trunc_to_date (models/staging/stg_locations.yml:12:11)
# [error] [InvalidConfig (dbt1005)]: Invalid column name(s): 'order_id', 'product_id', 'supply_cost' in row 1 of unit test fixture for expected output. Accepted columns for expected output are: []
# [error] [InvalidConfig (dbt1005)]: Invalid column name(s): 'location_id', 'location_name', 'opened_date', 'tax_rate' in row 1 of unit test fixture for expected output. Accepted columns for expected output are: []
# → 20 downstream nodes skipped (stg_locations, locations, order_items, orders, customers + their tests)
# Summary: 43 total | 21 success | 2 error | 20 skipped

Related findings already tracked elsewhere

• dbt build unit-test failures (test_supply_costs_sum_correctly, test_does_location_opened_at_trunc_to_date): dbt1005 Invalid column name(s) … Accepted columns for expected output are: [] — the unit-test expected-schema probe creates a session-scoped TEMPORARY table invisible in system.columns. Tracked in dbt Core 2.0 parity: Tests / catalog / docs #710; fixed in [ClickHouse] dbt unit tests + correct splitting of statements containing backslash escapes dbt-labs/dbt#16153. Failed regardless of which binary produced the seeds, and skipped all downstream marts.
• Seed column-type divergence: v2 infers Int64/Float64/DateTime64(6) where v1 gives Int32/Float32/DateTime. Explicitly deferred, tracked in dbt Core 2.0 parity: future work (explicitly deferred items) #731 (and noted in dbt Core 2.0 parity: Materialization seed #709). Not the cause of the jaffle-shop failures.