[Feature] Cost Optimisation: zero-cost tests via inlining into table creation using Error(...) about dbt-core HOT 4 CLOSED

To make this a bit clearer, here is a standalone (non-dbt) bigquery example of what the sql generated might look like:

WITH 
result_table AS (
  -- dummy example for {{compiled_code}}
  SELECT x FROM UNNEST(GENERATE_ARRAY(1,9)) AS x -- change 9 to a 7 for success
),
  -- two example tests
test_x_lt8_rows AS (
  select * from result_table where not (x < 8)
),
test_x_unique_rows AS (
  select x from result_table GROUP BY x HAVING count(*) > 1 
),

test_summary AS (
  SELECT 'x_lt8' as test_name, count(*) AS n_rows FROM test_x_lt8_rows
  UNION ALL
  SELECT 'x_unique' as test_name, count(*) AS n_rows FROM test_x_unique_rows
)
SELECT * FROM result_table
WHERE TRUE IN (
    SELECT IF(COUNT(*) = 0, TRUE, ERROR(
          'Inlined test failures. Table not built. ' ||
          STRING_AGG('Test "' || test_name || '": ' || n_rows || ' rows (not 0).', '  ')
    )) FROM test_summary WHERE n_rows > 0
)

Note that the WHERE TRUE IN (SELECT...) syntax was the first way i found to force BigQuery to actually run the tests and raise an error when required. If you're not careful to put the error logic right in the required path of the query planner it will be clever and ignore it entirely.

from dbt-core.

Thanks for proposing this idea along with such a thoughtful write-up @d1manson ! 🤩

Although you targeted BigQuery for this proposal for multiple specific reasons, I moved it to the dbt-core repo so we could consider this type of approach in a general context.

Overall, this approach is not something we'd adopt in dbt-core or dbt-bigquery directly. But it's something that you could pursue via custom materializations, overriding certain macros, and/or alternative strategies. Then you could package up the logic for private or public use.

from dbt-core.

thanks @dbeatty10 for the definitive response, even if it's not quite what i'd hoped for!

I may have a go at building something, and will certainly report back if i succeed and can make it a public package. Though I'm not entirely sure at this point that it's possible using the existing api surface you mention....we'll see.

from dbt-core.

This is a cool idea, btw, even if not something dbt wants to support in core.

Note that BigQuery will execute the underlying result_table CTE multiple times and so this may or may not be a cost saving depending on whether that's cheaper than running the tests the regular way.

GoogleSQL only materializes the results of recursive CTEs, but doesn't materialize the results of non-recursive CTEs inside the WITH clause. If a non-recursive CTE is referenced in multiple places in a query, then the CTE is executed once for each reference. The WITH clause with non-recursive CTEs is useful primarily for readability.

from dbt-core.