Description
This isn't urgent or anything as I switched back to lowercase gateway names.
tl;dr if you provide a gateway name in uppercase, everything "appears" to work, but internally SQLMesh isn't loading external types properly, and will return UNKNOWN for all of the data types.
For this example we'll create a lowercase gateway to prove it works, then change it to an uppercase gateway. You can also just create the uppercase gateway from the get-go, you get the same results.
Create an Azure SQL database named SQLMESH_TEST with a simple table:
CREATE TABLE dbo.Source (
[Id] INT IDENTITY (1, 1) CONSTRAINT PK_Source PRIMARY KEY,
[SomeDate] DATETIMEOFFSET NOT NULL,
[SomeString] NVARCHAR(5) NOT NULL
)
And simple model:
MODEL (
name [dbo].[Test Model],
kind INCREMENTAL_BY_UNIQUE_KEY (
unique_key ([Id])
),
cron '0 */4 * * *',
enabled True
);
SELECT s.Id,
s.SomeDate,
s.SomeString
FROM dbo.Source s
;
config.yaml:
# --- Gateway Connection ---
gateways:
azuresql:
connection:
type: azuresql
host: {{ env_var('MY_HOST') }}
user: {{ env_var('MY_USER') }}
password: {{ env_var('MY_PASS') }}
database: "SQLMESH_TEST"
default_gateway: azuresql
model_defaults:
dialect: tsql,normalization_strategy=case_sensitive
start: 2025-07-01 # Start date for backfill history
cron: '@daily' # Run models daily at 12am UTC (can override per model)
linter:
enabled: true
rules:
- ambiguousorinvalidcolumn
- invalidselectstarexpansion
Everything runs:
(.venv) PS C:\Temp\sqlmesh> sqlmesh plan
`prod` environment will be initialized
Models:
└── Added:
└── dbo."Test Model"
Models needing backfill:
└── dbo."Test Model": [2025-07-01 00:00:00 - 2025-07-01 23:59:59]
Apply - Backfill Tables [y/n]: y
Updating physical layer ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 1/1 • 0:00:03
✔ Physical layer updated
[1/1] dbo."Test Model" [insert/update rows] 3.30s
Executing model batches ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 1/1 • 0:00:03
✔ Model batches executed
Updating virtual layer ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 1/1 • 0:00:01
✔ Virtual layer updated
(.venv) PS C:\Temp\sqlmesh> sqlmesh create_external_models
(.venv) PS C:\Temp\sqlmesh> type external_models.yaml
- name: '[SQLMESH_TEST].[dbo].[Source]'
columns:
Id: INTEGER
SomeDate: DATETIMEOFFSET
SomeString: NVARCHAR(5)
gateway: azuresql
Load it in Python:
from ruamel.yaml import YAML
from pathlib import Path
from sqlmesh.core.config import load_configs
from sqlmesh.core.context import Context
yaml = YAML()
contents = yaml.load(Path("config.yaml"))
configs = load_configs(None, Context.CONFIG_TYPE, ".")
context = Context(
paths=".",
config=configs,
gateway="azuresql",
load=True,
)
Followed by manual inspection. See that it has a SqlModel, ExternalModel, and proper data types.
print(repr(context.models))
>>> mappingproxy({'"SQLMESH_TEST"."dbo"."Test Model"': SqlModel<[dbo].[Test Model]: Test Model.sql>, '"SQLMESH_TEST"."dbo"."Source"': ExternalModel<[SQLMESH_TEST].[dbo].[Source]: external_models.yaml>})
context.models['"SQLMESH_TEST"."dbo"."Test Model"'].columns_to_types
>>> {'Id': DataType(this=Type.INT, nested=False), 'SomeDate': DataType(this=Type.TIMESTAMPTZ, nested=False), 'SomeString': DataType(
>>> this=Type.NVARCHAR,
>>> expressions=[
>>> DataTypeParam(
>>> this=Literal(this=5, is_string=False))],
>>> nested=False)}
Now we're going to just change the gateway name in config.yaml to uppercase (e.g. in two places, don't change the type one of course!)
gateways:
AZURESQL:
connection:
# For more information on configuring the connection to your execution engine, visit:
# https://sqlmesh.readthedocs.io/en/stable/reference/configuration/#connection
# https://sqlmesh.readthedocs.io/en/stable/integrations/engines/azuresql/#connection-options
type: azuresql
host: {{ env_var('MY_HOST') }}
user: {{ env_var('MY_USER') }}
password: {{ env_var('MY_PASS') }}
database: "SQLMESH_TEST"
default_gateway: AZURESQL
model_defaults:
dialect: tsql,normalization_strategy=case_sensitive
start: 2025-07-01 # Start date for backfill history
cron: '@daily' # Run models daily at 12am UTC (can override per model)
linter:
enabled: true
rules:
- ambiguousorinvalidcolumn
- invalidselectstarexpansion
When running plan, there's some weirdness with the display here suddenly lowercasing the FROM clause, I'm not sure why, but that wasn't the problem (but is still gross, maybe you can fix it, hehe!)
But it appears to work, the external models get updated. You can also remove the old stuff from external_models first, it makes no difference to the issue (I tried).
(.venv) PS C:\Temp\sqlmesh> sqlmesh plan
Differences from the `prod` environment:
Models:
├── Removed:
│ └── dbo.Source
└── Directly Modified:
└── dbo."Test Model"
---
+++
@@ -12,7 +12,7 @@
)
)
SELECT
- [s].[Id] AS [Id],
- [s].[SomeDate] AS [SomeDate],
- [s].[SomeString] AS [SomeString]
-FROM [SQLMESH_TEST].[dbo].[Source] AS [s]
+ [s].[id] AS [id],
+ [s].[somedate] AS [somedate],
+ [s].[somestring] AS [somestring]
+FROM [sqlmesh_test].[dbo].[source] AS [s]
Directly Modified: dbo."Test Model" (Breaking)
Apply - Virtual Update [y/n]: y
SKIP: No physical layer updates to perform
SKIP: No model batches to execute
Updating virtual layer ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 1/1 • 0:00:01
✔ Virtual layer updated
(.venv) PS C:\Temp\sqlmesh> sqlmesh create_external_models
(.venv) PS C:\Temp\sqlmesh> type external_models.yaml
- name: '[SQLMESH_TEST].[dbo].[Source]'
columns:
Id: INTEGER
SomeDate: DATETIMEOFFSET
SomeString: NVARCHAR(5)
gateway: azuresql
- name: '[SQLMESH_TEST].[dbo].[Source]'
columns:
Id: INTEGER
SomeDate: DATETIMEOFFSET
SomeString: NVARCHAR(5)
gateway: AZURESQL
Re-run the Python from above with the uppercase gateway name and you'll see:
from ruamel.yaml import YAML
from pathlib import Path
from sqlmesh.core.config import load_configs
from sqlmesh.core.context import Context
yaml = YAML()
contents = yaml.load(Path("config.yaml"))
configs = load_configs(None, Context.CONFIG_TYPE, ".")
context = Context(
paths=".",
config=configs,
gateway="AZURESQL",
load=True,
)
>>> print(repr(context.models))
mappingproxy({'"SQLMESH_TEST"."dbo"."Test Model"': SqlModel<[dbo].[Test Model]: Test Model.sql>})
>>> context.models['"SQLMESH_TEST"."dbo"."Test Model"'].columns_to_types
{'id': DataType(this=Type.UNKNOWN), 'somedate': DataType(this=Type.UNKNOWN), 'somestring': DataType(this=Type.UNKNOWN)}
The external models have gone missing, and the data types are now all unknown.