[CT-2850] [Feature] Raise a warning when --state path == --target-path

[dbeatty10]

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

Mentioned by @aranke here and here:

Something else to note here is that you cannot use the same target directory for both the source and target

You get a very weird error that source and target states are the same, and so it's a no-op.

I ran into this during local development, so think this warning needs to be surfaced loudly.

This happens because

dbt now (over)writes manifest.json during parsing.
[…]
during the subsequent run (step 4) in the reproduction case, it overwrites target/manifest.json, then reads it back in to detect changes, and finds no changes.

from @jtcohen6 in #7790 (comment).

Describe alternatives you've considered

Simple solution that will work:

1. Write output to tmp directory during dbt run
2. Copy tmp directory to the output directory at the end of the dbt run

Who will this benefit?

Anyone using state in any form (including deferral, clone) reusing the same artifact directory.

Are you interested in contributing this feature?

Yes

Anything else?

There are 3 possible solutions here, from easiest to hardest:

1. Raise a warning when the input and output artifact directories are the same
2. Write to tmp directory as mentioned above
3. Investigate root cause and fix underlying issue

[graciegoheen]

From refinement:

• raise a warning or error (let's do what the warehouse would do) when the source manifest is the same as the target manifest

[jtcohen6]

Lots of places we could try to catch this. The most obvious to me (but it's Jinja) is inside the clone materialization, as soon as we know that target_relation == defer_relation:

dbt-core/core/dbt/include/global_project/macros/materializations/models/clone/clone.sql

Lines 34 to 37 in 8350dfe

	-- as a general rule, data platforms that can clone tables can also do atomic 'create or replace'
	{% call statement('main') %}
	{{ create_or_replace_clone(target_relation, defer_relation) }}
	{% endcall %}

[jtcohen6]

I retitled the issue based on further conversation in:

• [CT-2643] [Regression] state:modified selection method not behaving correctly #7790 (comment)

I think we want a warning in the more general case where --state path == --target-path. The only legitimate use case for this is dbt retry, and even then it's fair to warn users that this is not idempotent — subsequent retries will be different, based on whatever was the last retryable invocation.

Separately, I do think we want create_or_replace_clone to debug log + no-op if target_relation == defer_relation, rather than trying to run either

create or replace table my_db.my_schema.my_table clone my_db.my_schema.my_table;
create or replace view my_db.my_schema.my_table as select * from my_db.my_schema.my_table;

Acceptance criteria:

• Raise a warning whenever --state path == --target-path
• Update logic of create_or_replace_clone as described