[Bug] `clone` creating "view pointers" instead of "cloned tables" on 1.8 / "Keep on latest version"

### Is this a new bug in dbt-core?

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

### Current Behavior

On the latest dbt version ("Keep on latest version") in dbt Cloud - `dbt clone` is _sometimes_ creating view pointers of the target table instead of actually creating a cloned table.

Internal thread: https://dbt-labs.slack.com/archives/C05FWBP9X1U/p1717992405960779

### Expected Behavior

If the target table exist as a table - it should be cloned via `create or replace table db.ci.foo clone db.prod.foo;` instead of a view pointer `create or replace view db.ci.foo as (select * from db.prod.foo);`

### Steps To Reproduce

Not able to repro yet.

### Relevant log output

```shell
Internal thread above has a lot of logging.
```

### Environment

```markdown
- OS:
- Python:
- dbt: Keep on latest version
```

### Which database adapter are you using with dbt?

snowflake

### Additional Context

I believe this is a core thing as opposed to an adapter thing so filing this here.