[Bug] `tmp_relation_type: transient` does not persist the tmp relation, so Snowflake native lineage is still broken

### Is this a new bug?

- [x] I believe this is a new bug
- [x] I have searched the existing issues, and I could not find an existing issue for this bug

### Which packages are affected?

- [ ] dbt-adapters
- [ ] dbt-tests-adapter
- [ ] dbt-athena
- [ ] dbt-athena-community
- [ ] dbt-bigquery
- [ ] dbt-postgres
- [ ] dbt-redshift
- [x] dbt-snowflake
- [ ] dbt-spark

### Current Behavior

Setting `tmp_relation_type: transient` on an incremental model causes dbt to create the tmp relation as a transient table, as expected. However, the incremental materialization then drops that relation at the end of the build, the same as it does for `view` and `table`.

Because the object no longer exists, Snowflake excludes it from the lineage graph — [the lineage documentation states that deleted tables are not shown](https://docs.snowflake.com/en/user-guide/ui-snowsight-lineage#limitations-and-considerations). The incremental write records its source as `__dbt_tmp`, that node is not rendered, and the edge to the model's real upstream relation is therefore not reachable. The model appears as a root node in the graph.

This contradicts the documented purpose of the option. From [Snowflake configurations](https://docs.getdbt.com/reference/resource-configs/snowflake-configs#temporary-tables):

> `transient`: A transient table; persists in the catalog, enabling [Snowflake native lineage tracking](https://docs.snowflake.com/en/user-guide/ui-snowsight-lineage), while avoiding the 7-day fail-safe storage costs of permanent tables.

The concurrency warning in the same section also describes the relation as persisting:

> When `tmp_relation_type` is set to `transient`, the tmp relation becomes a real table that persists in the target schema under a deterministic name.

Observed in practice: the relation does not persist beyond the build, and native lineage remains broken.

### Expected Behavior

One of the following:

1. The materialization skips `drop_relation_if_exists(tmp_relation)` when `tmp_relation_type` resolves to `transient`, so the relation persists in the catalog and Snowflake can render the lineage edge — matching the documented behaviour; or
2. The documentation is corrected to remove the claim that this option persists the relation and enables native lineage tracking, and a different mechanism is offered for the lineage use case.
Option 1 appears to be the intent, given that `transient` exists as a distinct value from `table` specifically to trade storage cost for catalog visibility. If `transient` is dropped like the others, it is behaviourally equivalent to `table` for every purpose except session scoping, which would make the option redundant.

### Steps To Reproduce

1. Snowflake account on Enterprise Edition or higher (required for lineage).
2. Create a source-backed upstream model, `upstream_model.sql`:
```sql
{{ config(materialized='table') }}
select 1 as id, 'a' as payload, current_timestamp() as loaded_at
```

3. Create an incremental model, `downstream_incremental.sql`:
```sql
{{ config(
materialized='incremental',
tmp_relation_type='transient'
) }}

select id, payload, loaded_at
from {{ ref('upstream_model') }}

{% if is_incremental() %}
where loaded_at > (select coalesce(max(loaded_at), '1900-01-01') from {{ this }})
{% endif %}
```

4. `dbt run` twice, so the second run takes the incremental branch.
5. Confirm the tmp relation was created as transient — the debug log shows:
```
create or replace transient table ..downstream_incremental__dbt_tmp as (...)
```

6. Confirm it did not survive the build:
```sql
show tables like '%__DBT_TMP%' in schema .;
-- returns no rows

show tables history like '%__DBT_TMP%' in schema .;
-- returns the relation with a populated dropped_on timestamp
```

7. Query lineage upstream from the incremental model:
```sql
select distance, source_object_name, target_object_name, process
from table(snowflake.core.get_lineage(
'..DOWNSTREAM_INCREMENTAL', 'TABLE', 'UPSTREAM', 3));
```

No edge to `UPSTREAM_MODEL` is returned. Running the same model with `--full-refresh` produces a direct CTAS and the edge appears, confirming that the missing edge is specific to the incremental path.

### Relevant log output

```shell

```

### Environment

```markdown
- dbt Platform with "Stable Fusion"
```

### Additional Context

The relevant code appears to be the trailing unconditional drop in the Snowflake incremental materialization (`dbt/include/snowflake/macros/materializations/incremental.sql`):

```jinja
{% do drop_relation_if_exists(tmp_relation) %}
```

There is no branch on the resolved `tmp_relation_type` here, so the transient relation is dropped on the same path as the temporary view and temporary table. A guard on `tmp_relation_type == 'transient'` would be sufficient, though it needs to coexist with the existing full-refresh and view-to-table paths that also rely on this call.

**Why this matters beyond cosmetics.** Any incremental model on Snowflake currently terminates its own upstream lineage, because every incremental write routes through an ephemeral relation that Snowflake excludes from the graph — temporary tables and deleted tables are both excluded. This affects object-level and column-level lineage, and therefore tag propagation and masking-policy discovery, which are the governance workflows Snowflake's lineage feature is built to support. It is most visible in layered architectures where the incremental layer sits in the middle of the graph: breaking one edge disconnects the entire upstream subgraph from everything downstream of it.

`ACCESS_HISTORY.OBJECTS_MODIFIED` does still record `baseSources` resolving through the dropped relation to the underlying physical objects, so the raw metadata is not lost. But `baseSources` skips intermediate views entirely — [view columns are not listed as base sources](https://docs.snowflake.com/en/sql-reference/account-usage/access_history) — so reconstructing lineage from it yields the correct endpoints with the wrong topology. It cannot substitute for the graph.

The current workaround is to override the `drop_relation_if_exists` macro to skip tmp relations when `tmp_relation_type` is transient. That is an override of a dbt-core macro used well beyond incremental models, which makes it an uncomfortable thing to carry in a regulated production project.