refactor & streamline now that Nested CTES are GA!

[dataders]

here's the issues so far that I think can be addressed

### Tasks
- [ ] #260
- [x] support bespoke data tests
- [x] ditch current janky test implementation that requires results be materialized before reporting results
- [x] make sure dbt-synapse and dbt-sqlserver are not caught off guard by this change
- [ ] #168
- [ ] #180
- [ ] #185

[prdpsvs]

Addressed all these issues in 1.9.2