[CT-3023] [Feature] compile --suppress-ephemeral-ctes

[benmosher]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

When linting with SQLFluff, it compiles the model to SQL (2x) and then linting is executed against the true SQL of the model, instead of the raw Jinja template.

The first compilation compiles the model as-is, and the second compilation is of a synthetic model built out of the Jinja blocks and expressions, and some tracking values in between. The result thereof is used to "trace" which parts of the compiled model come from what expressions, and to detect loops, etc.

When compiling a model with direct references to ephemeral models, the resulting injected CTEs violate some assumptions of the tracing and result in a failure to lint the model.

Proposal: for the compile command only, if a new flag --suppress-ephemeral-ctes is provided, the CTE injection does not happen and the expression referencing to the ephemeral model is replaced with a raw identifier.

This resulting SQL is valid syntax, and allows the tracing to work as it is presently implemented.

Describe alternatives you've considered

I attempted to strip out the resulting CTEs from both compilations within the Cloud SQLFluff templater, but this was fraught with typical regex peril.

When using SQLFluff's CLI templater today, I believe it works around this by altering the manifest nodes for any ephemeral models to appear to be materialized to table instead, during the mid-lint compilation.

Who will this benefit?

Cloud IDE usage of SQLFluff will immediately benefit from using this flag.

The SQLFluff maintainers may also be able to benefit from this flag in future versions of their CLI templater implementation.

Are you interested in contributing this feature?

yes! provided a proposed implementation in #8482

Anything else?

No response