Support --empty flag for schema-only dry runs

[MichelleArk]

resolves #8980

Problem

Solution

• BaseRelation changes
  • Adds an optional limit to BaseRelation
  • Implements BaseRelation.render_limited: depending on the value of self.limit, this method templates sql to wrap a rendered relation in an 'empty' select statement. Further reasoning in the spike report
  • Updates BaseRelation.__str__ to use self.render_limited if self.limit is set
• Adds --empty flag to run and build commands.
  • This flag configuration is used to determine the value for resolve_limit on the BaseResolver
  • BaseResolver plumbs its resolve_limit to the Relation instance it is creating, which is responsible for rendering itself given an optional limit parameter.

Checklist

• I have read the contributing guide and understand what's expected of me
• I have run this code in development and it appears to resolve the stated issue
• This PR includes tests, or tests are not required/relevant for this PR
• This PR has no interface changes (e.g. macros, cli, logs, json artifacts, config files, adapter interface, etc) or this PR has already received feedback and approval from Product or DX
• This PR includes type annotations for new and modified functions

[github-actions]

Thank you for your pull request! We could not find a changelog entry for this change. For details on how to document a change, see the contributing guide.

[MichelleArk]

Spike Report

The crux of this issue is in resolving ref and source calls to a subquery that wraps the relation identifier to limit its read to 0 rows. We'd want to do this in a way that is overridable by adapter, because each adapter may have a slightly different syntax for achieving this. There are a couple options I've identified:

1. Try to make use of the existing adapter-dispatchable get_empty_subquery_sql macro, which consolidates this adapter-specific syntax.
   • However, relation rendering (Relation.render) currently occurs without a jinja context, and it would be pretty circular to provide a context to a Relation since the ref and source resolvers themselves are part the context. So the only way I can imagine doing this would be to override ref and source macros in the global_project, but this adds a good amount of complexity and will be cumbersome to maintain (changes to ref/source will need to consider the override, potentially making changes across both)
2. Implement a default empty_subquery_template in python on the BaseRelation, which is overridable across adapters (albeit in python).
   • This has the downside of duplication across the get_empty_subquery_sql macro and the python template. But on the flip side, there should only be one correct way of writing this template so it doesn't need to be all the way in user-space (jinja), as long as its overwritable by adapter maintainers (in their BaseRelation subclass). I've spiked this as a class attribute but could easily modify it to be a method on BaseRelation if thats preferred.

I've opted for (2) in this spike and would recommend it for its relative simplicity and given there is some precedence for doing a small amount of sql templating in the adapter python module.

[colin-rogers-dbt]

a couple questions but generally LGTM

[aranke]

Does using a limit of sys.maxsize instead of None make the code a little cleaner?

Then we might not even need resolve_limit.

[MichelleArk]

Does using a limit of sys.maxsize instead of None make the code a little cleaner?

I can see how that could clean this up but it would mean that every rendered relation would be wrapped in a (select * limit max_size) - whether we are using the --empty flag or not (we could add some handling for this but then the code doesn't get much cleaner). I think this functionality should be limited to when --empty is provided to avoid changing the compiled sql under the hood on existing surface area (all ref/source calls) to keep the risk of this change low.

sys.maxsize would also be less predictable from a user-perspective, so I don't think it's worth the more elegant code path here

[aranke]

Discussed offline, 🚢

[mwstanleyft]

Adapters need to be provided the ability to override the SQL statement here at minimum, because using a LIMIT clause in this way in BigQuery means that users will still be billed the full cost as if they had run their model for real and they will be very confused by this. BigQuery does provide some facility to avoid this but it would require either overriding the SQL statement here, or overriding the behaviour of --empty completely.

[github-christophe-oudar]

I just happened to land that PR randomly looking at the latest commits and I think I've an interesting opened discussion that correlate with your intent here:
#8560

That feature is a great step toward easily dry run.
I've set up a similar custom approach with 2 macros in my codebase

{% macro limit_ci() -%}
{% if target.name == 'ci' -%}
LIMIT 0
{%- endif %}
{%- endmacro %}

{% macro limit_dev(rows = 100) -%}
{% if target.name == 'dev' -%}
LIMIT {{rows}}
{%- endif %}
{%- endmacro %}

Yet it's very manual (but working fairly well because developers love copy pasting existing models).

However I would like to do the following on CI:

• Run a dry run (with that empty approach)
• Run models that have unit tests

It feels like to do so properly, having render method that is overridable through a macro would open to a lot of custom solutions 🙌

[FishtownBuildBot]

Opened a new issue in dbt-labs/docs.getdbt.com: dbt-labs/docs.getdbt.com#4572

[jtcohen6]

@github-christophe-oudar That's exactly the motivation behind this --empty feature: to support unit testing in CI, without building any "real" data. We do see extensions for limiting/sampling data in development as well — check out this issue:

• [CT-2967] [Feature] run dbt in --sample mode #8378

@mwstanleyft In my experience, while limit 0 is not sufficient on BigQuery, where false limit 0 avoids scanning any data and billing for bytes processed. There have been a one or two edge cases where this doesn't work (Christophe has found a few of them!), but these have tended to be bugs on BigQuery's side, and generally within more complex DML statements, not simple select / CTA.

[MichelleArk]

@mwstanleyft -- thanks for highlighting this issue for adapters in general and BigQuery specifically. For BigQuery, I believe the default implementation of render_limited in this PR for --empty should be cost-effective because of the where false clause that is included in the limit 0 case of the sql generated in render_limited:

No limit => full scan

Limit => full scan

Limit + where false => no scan

Actually - it seems like BigQuery scans 0 bytes if a limit 0 is provided:

Regardless though - adapter implementations can overwrite the default behaviour (in python, not in user-space) of render_limited to modify/opt-out of this behaviour.

We'll definitely keep this issue in mind as we continue to refine sampling / limiting beyond this 'empty' use case.

[github-christophe-oudar]

I finally tried this feature and found one major caveat:
I suspect, like me, a lot of dbt users are using FROM {{ source('schema', 'table') }} my_table_alias
Then using --empty will result in
FROM (select * from `db`.`schema`.`table` where false limit 0) _dbt_limit_subq my_table_alias
Which obviously won't work.

There are at least few solutions that could be explored to solve that problem:

• update source macro to support an alias parameter
• update source macro to support adding or not a default alias _dbt_limit_subq
• set no alias (but then I think some DBs requires an alias and then it would fail for them with default setup)

[seub]

I finally tried this feature and found one major caveat: I suspect, like me, a lot of dbt users are using FROM {{ source('schema', 'table') }} my_table_alias Then using --empty will result in FROM (select * from `db`.`schema`.`table` where false limit 0) _dbt_limit_subq my_table_alias Which obviously won't work.

Hey @github-christophe-oudar, I'm curious about your issue but I'm not following, can you clarify? Why would --empty add _dbt_limit_subq in your query?

[github-christophe-oudar]

@seub it's because of dbt-labs/dbt-adapters#124, it's fixed since then