#70 Bug: 3 dbt data-tests fail to compile on ClickHouse (invalid SQL), so they never run

[Anonymous]

Originally created by: grynn-in

Summary

Three dbt data-tests contain SQL that ClickHouse cannot execute — they error at compile/run time regardless of the data, so they can never pass. Surfaced on a fresh deploy's dbt build (and on a D365-only rebuild).

Failing tests (from target/run_results.json)

Test	ClickHouse error	Root cause
tests/assert_reciprocal_converges.sql	code 47 Unknown expression or function identifier 'final_iteration'	references a column final_iteration that isn't in scope (epm_gold.gold_allocation_results)
tests/assert_tier_total_equals_pool.sql	code 184 Aggregate function max(pool_amount) is found inside another aggregate function	nested aggregate (sum(... max(pool_amount) ...)) — illegal in ClickHouse
tests/assert_ownership_uses_effective_date.sql	code 403 INVALID_JOIN_ON_EXPRESSION ... join expression contains column from left and right table	non-equi JOIN ON (date-range >=/<= across both tables); ClickHouse needs SET allow_experimental_join_condition=1 or a rewrite

Repro

cd dbt_project && dbt build   # (or the deploy.sh Step 5 dbt_init)
# ... Failure in test assert_reciprocal_converges / assert_tier_total_equals_pool / assert_ownership_uses_effective_date

Impact

These are correctness guards for the allocation/consolidation engine that currently provide zero coverage — they error rather than validate. Build summary shows them as ERROR (not assertion fail).

Note (separate)

tests/assert_dynamic_step_count.sql also fails, but as a genuine assertion (Got 1 result, configured to fail if != 0), not a compile error — likely a real data/logic issue or a demo-data expectation; tracking only the 3 compile failures here.

Found while smoke-testing a fresh one-click deploy on macOS (ClickHouse 24.8).

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed