Limitations of `sql_header`

### Contributions

- [X] I have read the contribution docs, and understand what's expected of me.

### Link to the page on docs.getdbt.com requiring updates

https://docs.getdbt.com/reference/resource-configs/sql_header

### What part(s) of the page would you like to see updated?

We should document a few known limitations of `sql_header`.

[This](https://stackoverflow.com/questions/70274718/how-to-declare-and-init-variable-in-a-dbt-model-in-sql-file-with-big-query-ad) Stack Overflow post points out that you [can't safely use `{{ ref() }}` or `{{ source() }}`](https://github.com/dbt-labs/dbt-core/issues/2793) in the [`sql_header`](https://docs.getdbt.com/reference/resource-configs/sql_header).

And `{{ this }}` also [doesn't work](https://github.com/dbt-labs/dbt-core/issues/7151) in a `sql_header` when using a custom [`generate_alias_name`](https://docs.getdbt.com/docs/build/custom-aliases#generate_alias_name).

### Additional information

Since `sql_header` is often used by users dbt-bigquery to `DECLARE` variables, it would be nice to show an example similar to (but not exactly like) [this](https://stackoverflow.com/a/74250350):

```sql
{% call set_sql_header(config) %}
DECLARE myDate VARCHAR DEFAULT '2021-01-01';
{% endcall %}

with order_bis as (

select
order_id

from
order
where
customer_date > myDate

)

select * from order_bis
```