dbt-labs / dbt-labs/metricflow
Support quoted identifiers in SQL rendering
- Dominant language
- Python
- Stars
- 1.8k
- Forks
- 202
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 14
Description
## Summary
MetricFlow currently renders all column names and aliases as bare (unquoted) SQL identifiers. The only protection against reserved keyword collisions is a hardcoded blocklist in `metricflow_semantic_interfaces/validations/reserved_keywords.py`, validated at manifest-load time.
This approach has fundamental limitations and should be replaced with proper quoted-identifier support in the SQL renderer.
## The problem with the blocklist approach
The blocklist (`reserved_keywords.py`) is intentionally non-exhaustive — it only contains keywords that are reserved across *all* supported engines simultaneously (Redshift, Postgres, BigQuery, Snowflake intersection). This means:
- Common, natural dimension names like `order`, `user`, `value`, `date`, `type`, `status`, `name` pass semantic validation without error
- Those names then produce invalid SQL at query time (e.g. `SELECT ... AS order FROM ...`), giving users a cryptic engine error with no pointer back to the offending definition
- Every new engine added to the supported set potentially expands the set of dangerous names with no way to retroactively catch them
The code acknowledges this gap explicitly:
```python
# metricflow_semantics/specs/where_filter/where_filter_spec.py:47
# where_sql may become dialect specific if we introduce quoted identifiers later.
```
## Proposed fix
Quote all column names and aliases in SQL output using the appropriate engine-specific quote character:
| Engine | Quote character |
|--------|----------------|
| Snowflake, Redshift, Postgres, DuckDB | `"` |
| BigQuery (standard SQL) | `` ` `` |
| Trino | `"` |
This is the standard approach taken by most SQL generators (SQLAlchemy, dbt's own adapter layer, etc.) and eliminates this entire class of problem without requiring an exhaustive keyword list.
## Affected code
- `metricflow/sql/render/expr_renderer.py` — `visit_column_reference_expr` and `visit_column_alias_reference_expr` render bare identifiers
- `metricflow/sql/render/sql_plan_renderer.py` — `_render_select_columns_section` renders column aliases unquoted
- Each engine renderer (`big_query.py`, `trino.py`, etc.) would need to supply its quote character
## Relationship to the existing blocklist
Once quoting is in place, `ReservedKeywordsRule` can be relaxed or removed for the identifier-collision case, since quoted identifiers sidestep keyword conflicts entirely. It may still be useful to keep a reduced list for table/schema names (which appear in `node_relation` and are harder to quote safely across all contexts).
Contributor guide
Assessment
This issue has not been assessed yet.