Error: Unsupported expression type Model when altering table using Athena

[Stefan-Dienst]

Hi,

I am trying out SQLMesh, but ran into issues when using AWS Athena as a SQL engine. While following the quick start guide I get the error Error: Unsupported expression type Model when adding a new column.

I am using Version 0.205.0.

To reproduce

I did the following:

uv init
uv add sqlmesh
uv add "sqlmesh[athena]"
uv run sqlmesh init

Then I chose the following:

──────────────────────────────
Welcome to SQLMesh!
──────────────────────────────

What type of project do you want to set up?

    [1]  DEFAULT - Create SQLMesh example project models and files
    [2]  dbt     - You have an existing dbt project and want to run it with SQLMesh
    [3]  EMPTY   - Create a SQLMesh configuration file and project directories only

Enter a number: 1

──────────────────────────────

Choose your SQL engine:

    [1]  DuckDB
    [2]  Snowflake
    [3]  Databricks
    [4]  BigQuery
    [5]  MotherDuck
    [6]  ClickHouse
    [7]  Redshift
    [8]  Spark
    [9]  Trino
    [10] Azure SQL
    [11] MSSQL
    [12] Postgres
    [13] GCP Postgres
    [14] MySQL
    [15] Athena
    [16] RisingWave

Enter a number: 15

──────────────────────────────

Choose your SQLMesh CLI experience:

    [1]  DEFAULT - See and control every detail
    [2]  FLOW    - Automatically run changes and show summary output

Enter a number: 1

──────────────────────────────

Your SQLMesh project is ready!

Next steps:
• Run command in CLI to install your SQL engine's Python dependencies: pip install "sqlmesh[athena]"
• Update your gateway connection settings (e.g., username/password) in the project configuration file:
    /home/stefan/Documents/playgrounds/sqlmesh/athena-issue/config.yaml
• Run command in CLI: sqlmesh plan
• (Optional) Explain a plan: sqlmesh plan --explain

Quickstart guide:
https://sqlmesh.readthedocs.io/en/stable/quickstart/cli/

Need help?
• Docs:   https://sqlmesh.readthedocs.io
• Slack:  https://www.tobikodata.com/slack
• GitHub: https://github.com/TobikoData/sqlmesh/issues

Afterwards I edited the config.yaml to include the following fields:

      type: athena
      work_group: primary
      s3_staging_dir: s3://bucket/sqlmesh/output/
      s3_warehouse_location: s3://bucket/sqlmesh/

After getting an AWS session I ran uv run sqlmesh plan, which worked as expected:

======================================================================
Successfully Ran 1 tests against duckdb in 0.12 seconds.
----------------------------------------------------------------------
[WARNING] The athena engine is not recommended for storing SQLMesh state in production deployments. Please see
https://sqlmesh.readthedocs.io/en/stable/guides/configuration/#state-connection for a list of recommended engines
and more information.

`prod` environment will be initialized

Models:
└── Added:
    ├── sqlmesh_example.full_model
    ├── sqlmesh_example.incremental_model
    └── sqlmesh_example.seed_model
Models needing backfill:
├── sqlmesh_example.full_model: [full refresh]
├── sqlmesh_example.incremental_model: [2020-01-01 - 2025-07-29]
└── sqlmesh_example.seed_model: [full refresh]
Apply - Backfill Tables [y/n]: y

Updating physical layer ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 3/3 • 0:00:25

✔ Physical layer updated

[1/1] sqlmesh_example.seed_model          [insert seed file]                 3.23s
[1/1] sqlmesh_example.incremental_model   [insert 2020-01-01 - 2025-07-29]   15.69s
[1/1] sqlmesh_example.full_model          [full refresh, audits ✔1]          20.45s
Executing model batches ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 3/3 • 0:00:39

✔ Model batches executed

Updating virtual layer  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 3/3 • 0:00:08

✔ Virtual layer updated

But then when I changed the incremental_model.sql to have the new column:

MODEL (
  name sqlmesh_example.incremental_model,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column event_date
  ),
  start '2020-01-01',
  cron '@daily',
  grain (id, event_date)
);

SELECT
  id,
  item_id,
  'z' AS new_column, -- Added column
  event_date,
FROM
  sqlmesh_example.seed_model
WHERE
  event_date BETWEEN @start_date AND @end_date

and run uv run sqlmesh plan dev I get:

======================================================================
Successfully Ran 1 tests against duckdb in 0.04 seconds.
----------------------------------------------------------------------
[WARNING] The athena engine is not recommended for storing SQLMesh state in production deployments. Please see
https://sqlmesh.readthedocs.io/en/stable/guides/configuration/#state-connection for a list of recommended engines
and more information.

New environment `dev` will be created from `prod`


Differences from the `prod` environment:

Models:
├── Directly Modified:
│   └── sqlmesh_example__dev.incremental_model
└── Indirectly Modified:
    └── sqlmesh_example__dev.full_model
Error: Unsupported expression type Model

I tried the same with the full_model.sql and using table_format: iceberg for all models, but always got the same error when adding a new column.

[izeigerman]

@Stefan-Dienst thanks for reporting. Any chance you can try this with just DuckDB? I'm curious whether the problem is specific to Athena.

[williebsweet]

@izeigerman We have an issue that might be related to this.

We've been successfully using SQLMesh 0.187.5 with Athena, but after upgrading to 0.206.0 we started getting sqlglot.errors.ParseError: Invalid expression / Unexpected token errors.

One that stood out was a qualify statement that, until the upgrade, had successfully transpiled to Athena syntax without issue. I wonder if this is related to some of the sqlglot version upgrades that have been added to versions > 0.187.5

[erindru]

Thanks for providing the steps to reproduce @Stefan-Dienst , I can repro this locally.

@izeigerman it doesnt do it for duckdb - just Athena. I think it's related to tobymao/sqlglot#5314 where Athena got refactored and how we monkeypatch dialects in SQLMesh.

Quicker steps to reproduce:

>>> from sqlmesh.core.dialect import parse_one
>>> parse_one("MODEL (name foo)").sql(dialect="duckdb") #works
'MODEL (\nname foo\n)' 
>>> parse_one("MODEL (name foo)").sql(dialect="athena") #fails
Traceback (most recent call last):
...SNIP...
ValueError: Unsupported expression type Model

It only fails when there is a diff, I guess because that's the only time the d.Model node is actually rendered.

[erindru]

Hey @williebsweet i'd be curious if the changes in this PR fix it for you or if there has been other regressions as well

[Stefan-Dienst]

Thanks for the quick responses.

I have repeated the steps with uv add sqlmesh==0.187.5 and for this version it worked. So I will use this in the meantime to try out SQLMesh.

[williebsweet]

Hey @williebsweet i'd be curious if the changes in this PR fix it for you or if there has been other regressions as well

@erindru Unfortunately, no. I am still getting the qualify (sqlglot.errors.ParseError: Invalid expression / Unexpected token) error.

[georgesittas]

@williebsweet just to double-check: if you run sqlmesh clean and use this PR's logic, does it still error out? Can you please share the stack trace or a more detailed error message so that we can debug this more effectively?

What you're saying re: qualify seems off, because issues related to qualify should be swallowed or warn only (unless you've marked the corresponding linter rule as an error). So I wonder if this is a caching issue.

[williebsweet]

@georgesittas sqlmesh clean didn't fix it. Still getting the following when running %context . in my notebook:

---------------------------------------------------------------------------
_RemoteTraceback                          Traceback (most recent call last)
_RemoteTraceback: 
"""
Traceback (most recent call last):
  File "/Users/user/.local/share/uv/python/cpython-3.10.16-macos-aarch64-none/lib/python3.10/concurrent/futures/process.py", line 246, in _process_worker
    r = call_item.fn(*call_item.args, **call_item.kwargs)
  File "/Users/user/Documents/GitHub/sqlmesh/sqlmesh/core/loader.py", line 139, in load_sql_models
    expressions = parse(file.read(), default_dialect=_defaults["dialect"])
  File "/Users/user/Documents/GitHub/sqlmesh/sqlmesh/core/dialect.py", line 1003, in parse
    expressions.extend(parse_sql_chunk(chunk))
  File "/Users/user/Documents/GitHub/sqlmesh/sqlmesh/core/dialect.py", line 947, in parse_sql_chunk
    parser.parse(chunk, sql) if into is None else parser.parse_into(into, chunk, sql)
  File "/Users/user/Documents/GitHub/ml-mono/projects/analytics/.venv/lib/python3.10/site-packages/sqlglot/dialects/athena.py", line 128, in parse
    return self._trino_parser.parse(raw_tokens, sql)
  File "/Users/user/Documents/GitHub/ml-mono/projects/analytics/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1609, in parse
    return self._parse(
  File "/Users/user/Documents/GitHub/ml-mono/projects/analytics/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1681, in _parse
    self.raise_error("Invalid expression / Unexpected token")
  File "/Users/user/Documents/GitHub/ml-mono/projects/analytics/.venv/lib/python3.10/site-packages/sqlglot/parser.py", line 1722, in raise_error
    raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 122, Col: 18.
  d_at,
  etl_sync_updated_at,
  action_id,
  event_custom_properties
from abc.posthog_events
qualify row_number() over (partition by id, event_name, user_id order by event_timestamp desc nulls last) = 1 -- gets 
"""

The above exception was the direct cause of the following exception:

ParseError                                Traceback (most recent call last)
File ~/Documents/GitHub/sqlmesh/sqlmesh/core/loader.py:624, in SqlMeshLoader._load_sql_models(self, macros, jinja_macros, audits, signals, cache, gateway, loading_default_kwargs)
    623 try:
--> 624     loaded = future.result()
    625     for model in loaded or cache.get(path):

File ~/.local/share/uv/python/cpython-3.10.16-macos-aarch64-none/lib/python3.10/concurrent/futures/_base.py:451, in Future.result(self, timeout)
    450 elif self._state == FINISHED:
--> 451     return self.__get_result()
    453 self._condition.wait(timeout)

File ~/.local/share/uv/python/cpython-3.10.16-macos-aarch64-none/lib/python3.10/concurrent/futures/_base.py:403, in Future.__get_result(self)
    402 try:
--> 403     raise self._exception
    404 finally:
    405     # Break a reference cycle with the exception in self._exception

ParseError: Invalid expression / Unexpected token. Line 122, Col: 18.
  d_at,
  etl_sync_updated_at,
  action_id,
  event_custom_properties
from abc.posthog_events
qualify row_number() over (partition by id, event_name, user_id order by event_timestamp desc nulls last) = 1 -- gets 

During handling of the above exception, another exception occurred:

ConfigError                               Traceback (most recent call last)
Cell In[1], line 3

File ~/Documents/GitHub/ml-mono/projects/analytics/.venv/lib/python3.10/site-packages/IPython/core/interactiveshell.py:2482, in InteractiveShell.run_line_magic(self, magic_name, line, _stack_depth)
   2480     kwargs['local_ns'] = self.get_local_scope(stack_depth)
   2481 with self.builtin_trap:
-> 2482     result = fn(*args, **kwargs)
   2484 # The code below prevents the output from being displayed
   2485 # when using magics with decorator @output_can_be_silenced
   2486 # when the last Python token in the expression is a ';'.
   2487 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File ~/Documents/GitHub/sqlmesh/sqlmesh/magics.py:197, in SQLMeshMagics.context(self, line)
    194 remove_excess_logs(log_file_dir, log_limit)
    196 try:
--> 197     context = Context(paths=args.paths, config=configs, gateway=args.gateway)
    198     self._shell.user_ns["context"] = context
    199 except Exception:

File ~/Documents/GitHub/sqlmesh/sqlmesh/core/context.py:447, in GenericContext.__init__(self, notification_targets, state_sync, paths, config, gateway, concurrent_tasks, loader, load, users)
    444 self._register_notification_targets()
    446 if load:
--> 447     self.load()

File ~/Documents/GitHub/sqlmesh/sqlmesh/core/context.py:605, in GenericContext.load(self, update_schemas)
    602 """Load all files in the context's path."""
    603 load_start_ts = time.perf_counter()
--> 605 loaded_projects = [loader.load() for loader in self._loaders]
    607 self.dag = DAG()
    608 self._standalone_audits.clear()

File ~/Documents/GitHub/sqlmesh/sqlmesh/core/context.py:605, in <listcomp>(.0)
    602 """Load all files in the context's path."""
    603 load_start_ts = time.perf_counter()
--> 605 loaded_projects = [loader.load() for loader in self._loaders]
    607 self.dag = DAG()
    608 self._standalone_audits.clear()

File ~/Documents/GitHub/sqlmesh/sqlmesh/core/loader.py:234, in Loader.load(self)
    231     else:
    232         standalone_audits[name] = audit
--> 234 models = self._load_models(
    235     macros,
    236     jinja_macros,
    237     self.context.selected_gateway,
    238     audits,
    239     signals,
    240 )
    242 metrics = self._load_metrics()
    244 requirements, excluded_requirements = self._load_requirements()

File ~/Documents/GitHub/sqlmesh/sqlmesh/core/loader.py:544, in SqlMeshLoader._load_models(self, macros, jinja_macros, gateway, audits, signals)
    538 """
    539 Loads all of the models within the model directory with their associated
    540 audits into a Dict and creates the dag
    541 """
    542 cache = SqlMeshLoader._Cache(self, self.config_path)
--> 544 sql_models = self._load_sql_models(macros, jinja_macros, audits, signals, cache, gateway)
    545 external_models = self._load_external_models(audits, cache, gateway)
    546 python_models = self._load_python_models(macros, jinja_macros, audits, signals)

File ~/Documents/GitHub/sqlmesh/sqlmesh/core/loader.py:637, in SqlMeshLoader._load_sql_models(self, macros, jinja_macros, audits, signals, cache, gateway, loading_default_kwargs)
    635                         models[model.fqn] = model
    636             except Exception as ex:
--> 637                 raise ConfigError(self._failed_to_load_model_error(path, ex), path)
    639 return models

ConfigError: Failed to load model from file '/Users/user/Documents/GitHub/ml-mono/projects/analytics/models/staging/abc/stg_posthog_events.sql':

  Invalid expression / Unexpected token. Line 122, Col: 18.
    d_at,
    etl_sync_updated_at,
    action_id,
    event_custom_properties
  from abc.posthog_events
  qualify row_number() over (partition by id, event_name, user_id order by event_timestamp desc nulls last) = 1 -- gets

[erindru]

Hey @williebsweet , can you please open an independent issue for this? Please include:

• your model definition including the full query
• the stack trace
• the expected rendered output

Because, from what I can tell, Athena doesn't seem to support QUALIFY to begin with. It's not mentioned in the SELECT docs so it may have been working by accident because SQLGlot is generally lenient and there is an eliminate_qualify transform to rewrite QUALIFY into a nested select.

Given the following table and data (example lifted from the snowflake docs):

create table qt (i int, p string, o int)
    location 's3://bucket/erin/qt'
    tblproperties ('table_type' ='ICEBERG');
  
insert into qt (i, p, o) values
    (1, 'A', 1),
    (2, 'A', 2),
    (3, 'B', 1),
    (4, 'B', 2);

The following query fails on Athena:

select i, p, o
from qt
qualify row_number() over (partition by p order by o) = 1; 

# line 3:9: mismatched input 'row_number'. Expecting: '(', ',', 'CROSS', 'EXCEPT', 'FETCH',

But it works fine if it gets rewritten to eliminate the QUALIFY clause:

with base as (    
    select i, p, o,
    row_number() OVER (partition by p order by o) as rownum
    from qt
) select * from base where rownum=1

[williebsweet]

@erindru Yeah - Athena doesn't have qualify natively. But SQLMesh has supported our use of it with Athena since we started. TBH, I view this as a regression since it went from working to no longer working.

I guess the real question is whether it was intended or a byproduct of other changes.

[erindru]

The original behaviour was a bug. The SQLGlot parsers for a given dialect are intended to only accept SQL that is valid for that dialect.

When we refactored the Athena parser in SQLGlot, it had the effect of tightening up the acceptable inputs. If you find SQL that works in Athena and can't be parsed by SQLGlot with the input dialect set to athena - that's a bug. But if we previously accepted input that is invalid in Athena - and now we don't - that is not a bug, that's a bug fix.

I think if you set dialect: hive on your model, it will continue working in the original way. SQLGlot will parse the model as hive and then when it runs queries against Athena, it will transpile it to Athena/Trino