perf(solana): flatten stake_account_delegations split branch (~7x faster)

[jeff-dude]

Summary

• Model is already incremental but the split branch nested a redundant stake_actions × stake_call_DelegateStake self-join, producing a ~67M-row cross-product on top of two full DelegateStake history scans (14.4M rows each).
• Flattened the nested old_vote subquery into a single-level LEFT JOIN. NULL semantics preserved exactly via CASE WHEN src_del.account_stakeAccount IS NOT NULL THEN COALESCE(src_del.call_block_time, '2000-01-01') END.
• Pre-filtered the destination-side DelegateStake LEFT JOIN to the incremental window. Safe because the destination is created by the split, so any DelegateStake on it has call_block_time >= a.block_time >= cutoff.

Measured impact (EXPLAIN ANALYZE on prod data, 1-day incremental window)

Stage	Wall time	Credits	Cross-product rows	Output rows
Pre-filters only (intermediate)	1.11 min	1.188	67M	136,417
Flattened (this PR)	9.53s	0.751	336K	136,509

Baseline (no optimization) EXPLAIN ANALYZE was killed after running >30 min on large tier — itself confirms the original scan profile is the bottleneck.

Correctness — regression test

Side-by-side comparison of the original nested split branch vs the flattened single-level join on a 12-hour window (run via Dune large tier):

original_rows: 122,498
flattened_rows: 122,498
only_in_original: 0
only_in_flattened: 0

Identical result sets, zero diff in either direction.

The flattening's NULL handling is provably equivalent — when src_del matches, time fills with '2000-01-01' sentinel; when it doesn't match, the column falls through to dst_del or NULL (same as the original nested behavior).

What is intentionally NOT changed

• The destination output column block_time is still backdated to the source's historical delegation time, matching prod behavior. Downstream staking_solana_validator_stake_account_epochs_raw depends on this via vote.block_slot < epoch.block_slot.
• No incremental_predicates added on the merge target — split-inherited output rows carry historical block_time values, which would defeat a destination-side window predicate and re-insert duplicates.

Test plan

• CI passes (dbt slim ci for the solana subproject)
• Verify dbt_utils.unique_combination_of_columns data test still passes on staking_solana.stake_account_delegations
• Spot-check staking_solana_validator_stake_account_epochs_raw output against prod after this lands (downstream consumer)

🤖 Generated with Claude Code

[cursor]

PR Summary

Medium Risk
Rewrites the split delegation branch of an incremental dbt model; intended to be semantically equivalent but any join/NULL-handling change could subtly alter historical delegation timestamps or row counts.

Overview
Performance refactor of staking_solana_stake_account_delegations split handling. The model is restructured into direct_delegates and a flattened split_delegates CTE, replacing the prior nested old_vote subquery that created a large cross product and relied on an outer DISTINCT to dedupe.

The new split logic uses direct LEFT JOINs to source/destination stake_call_DelegateStake, preserves prior NULL/sentinel timestamp behavior via CASE/COALESCE, and adds an incremental-time predicate specifically on the destination-side join while keeping the final output schema and account_voteAccount is not null filter unchanged.

^{Reviewed by Cursor Bugbot for commit 9307dd5. Configure here.}

[jeff-dude]

CI side-by-side full-build comparison

Ran a temporary baseline-copy model alongside the optimized one in CI so both build from the same source-data snapshot:

Model	CI build time	Outcome
Optimized (this PR)	17.07s	CREATE TABLE (17,060,199 rows)
Original-SQL baseline (temp copy)	3,746.92s (62.4 min)	ERROR — DuneSQL query timeout

The baseline can't full-build inside DuneSQL's query timeout, so a same-source row-level diff isn't possible via CI. The optimization is approximately 220x+ faster on full rebuild (and the baseline never finished).

Correctness evidence:

• 12-hour side-by-side regression of nested-old_vote vs flattened SQL: 122,498 = 122,498 rows, zero diff in either direction.
• Logical proof of NULL-handling equivalence (see PR description).

Baseline copy reverted in the latest commit.