[CT-3153] [implementation] limit the size of fixtures for unit testing

[graciegoheen]

Housekeeping

• I am a maintainer of dbt-core

Short description

From #8652 and #8290

We should add a limit to how large the unit testing fixtures can be, to avoid performance issues and ensure folks are using unit tests properly.

We should have a way to handle when folks are using seeds as their fixtures, and their seeds are larger than the fixture limit - if your seed file is larger than the size limit for fixture, you must define a fixture for your seed.

Acceptance criteria

• error message if fixture is greater in size than 500 rows
• error message if you use a seed as a fixture, and the seeds is greater in size than 500 rows
• any number of columns is allowed
• add tests that all data warehouses can support this max, if not, set the max lower for the given warehouse

note: it's ok if folks run into a warehouse constraint (like query length) for now, we will address that issue in a future ticket if necessary

Impact to Other Teams

None

Will backports be required?

No

Context

No response

[graciegoheen]

Notes from product/dx refinement:

• performance implications depend on whether we take the CTE approach or "temporary" table strategy [CT-3033] [Spike] Explore support multiple unit test materialization strategies: CTE vs 'seed'-based #8499
  • will we run into warehouse constraints if using the CTE approach?
  • query length constraint?
• limit of 500 rows for a given fixture