feature request / tracking issue: parameterized views #6181

Hey guys,

A few issues/PRs have been raised around parameterized views / duckdb macros / table-value-functions:
#5786 #4002 .

I am going to have a shot at implementing this, just touching base first.

Proposal:

Models of kind VIEW can take a parameters argument:

model (
  name = my_parameterized_view,
  kind = VIEW (
    parameters ( discount_rate Float64 )
  )
)

select
  sum(total * (1 / (1 + discount_rate)**t )) as discounted_total
from cashflows
group by all;

ViewKind in sqlmesh has a parameterized attribute that tracks this. (Materialized views work and represented internally in exactly the same way).

This then renders as an engine-specific parameterized view / etc, e.g. duckdb

create macro my_parameterized_view(discount_rate Float64)
as
select
  sum(total * (1 / (1 + discount_rate)**t )) as discounted_total
from cashflows
group by all;

or clickhouse

create view my_parameterized_view
as
select
  sum(total * (1 / (1 + {discount_rate:Float64})**t )) as discounted_total
from cashflows
group by all;

I intend to implement both Clickhouse and DuckDB - these are quite different so I'm hopeful this will prevent any engine-specific assumptions from creeping in to the general stuff.

Known gnarliness:

• parameter typing (required, optional, not represented at all) differs widely between different DBs
• default value support differs widely between different DBs
• parameter name resolution / reference style differs widely between different DBs (e.g. see above, DuckDB refers to it as an identifier, Clickhouse refers to it as a parameter placeholder)
• initial spiking shows there will be some small sqlglot work required at least

Unknown but suspected gnarliness

• I'm not sure if sqlglot has the ability to translate between the different representations of this feature at the engine level, and personally I think it would be folly to try - this might imply some more engine-specific logic in sqlmesh as a result.
• I suspect there are engines around that don't even put these things in a database/schema scope - i don't intend to try and fix this problem, but if such things exist they will probably block this from working in those engines

any feedback on the above before I start?