Variables are not rendered correctly for Python config in 0.158.2 #3868
Description
Context
I am using SQLMesh 0.158.2, after migrating from 0.138.0, and I have a model like this:
MODEL (
name @{silver}.client_x.insurance_plan,
kind INCREMENTAL_BY_UNIQUE_KEY (
unique_key id
),
grain (
id
),
audits (
COMPARE_ROW_COUNTS(
model_name := @{silver}.client_x.insurance_plan,
source_table := bronze.client_x.insurance_plan,
threshold := 0.10
),
UNIQUE_VALUES(columns := (
id
))
),
cron '@daily',
column_descriptions (
id = "Unique key generated internally for the insurance plan",
external_plan_id = "Null for X, but ideally the external plan id",
organization_id = "Reference to the insurer organization",
plan_type = "Classification of the insurance plan",
member_id = "Member ID for the insurance plan",
group_number = "Group number for the insurance plan",
coverage_area_id = "Surrogate reference to the coverage location",
plan_name = "Name of the insurance plan",
created_timestamp = "Creation timestamp in external system",
updated_timestamp = "Last update timestamp in external system"
)
);
In my config.py, I define these variables @{silver} as such:
gateway_variables_dictionary = {
"gold": ("GOLD" if environment == Environment.PRODUCTION else "DEV_SQLMESH"),
"GOLD": ("GOLD" if environment == Environment.PRODUCTION else "DEV_SQLMESH"),
"silver": ("SILVER" if environment == Environment.PRODUCTION else "DEV_SQLMESH"),
"SILVER": ("SILVER" if environment == Environment.PRODUCTION else "DEV_SQLMESH"),
"bronze": ("BRONZE" if environment == Environment.PRODUCTION else "DEV_SQLMESH"),
"BRONZE": ("BRONZE" if environment == Environment.PRODUCTION else "DEV_SQLMESH"),
}
The logs show this model does not compile:
sqlmesh.utils.concurrency.NodeExecutionFailedError: Execution failed for node ('"DEV_SQLMESH"."CLIENT_Y"."ADDRESS"', ((946684800000, 1739923200000), 0))
2025-02-19 13:42:11,872 - MainThread - sqlmesh.core.scheduler - INFO - Execution failed for node ('"DEV_SQLMESH"."CLIENT_X"."INSURANCE_PLAN"', ((946684800000, 1739923200000), 0)) (scheduler.py:337)
Traceback (most recent call last):
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/sqlmesh/utils/concurrency.py", line 69, in _process_node
self.fn(node)
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/sqlmesh/core/scheduler.py", line 456, in evaluate_node
self.evaluate(snapshot, start, end, execution_time, deployability_index, batch_idx)
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/sqlmesh/core/scheduler.py", line 196, in evaluate
audit_results = self.snapshot_evaluator.audit(
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/sqlmesh/core/snapshot/evaluator.py", line 515, in audit
self._audit(
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/sqlmesh/core/snapshot/evaluator.py", line 1034, in _audit
count, *_ = adapter.fetchone(
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/sqlmesh/core/engine_adapter/base.py", line 1925, in fetchone
self.execute(
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/sqlmesh/core/engine_adapter/base.py", line 2084, in execute
self._execute(sql, **kwargs)
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/sqlmesh/core/engine_adapter/base.py", line 2090, in _execute
self.cursor.execute(sql, **kwargs)
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/snowflake/connector/cursor.py", line 1103, in execute
Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 283, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 338, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/Users/gesus/Repos/core/adonis-core/.venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 214, in default_errorhandler
raise error_class(
snowflake.connector.errors.ProgrammingError: 002003 (02000): 01ba80a2-020c-57a2-0002-f5c702713ae2: SQL compilation error:
Database '"@{SILVER}"' does not exist or not authorized.
Running a sqlmesh render <your variable's value>.client_x.insurance_plan gives:
SELECT
"IP"."ID" AS "ID",
NULL AS "EXTERNAL_PLAN_ID",
"IP"."ORGANIZATION_ID" AS "ORGANIZATION_ID",
NULL AS "PLAN_TYPE",
NULL AS "MEMBER_ID",
NULL AS "GROUP_NUMBER",
NULL AS "COVERAGE_AREA_ID",
"IP"."PLAN_NAME" AS "PLAN_NAME",
"IP"."CREATED_TIMESTAMP" AS "CREATED_TIMESTAMP",
"IP"."CREATED_TIMESTAMP" AS "UPDATED_TIMESTAMP"
FROM "BRONZE"."client_x"."INSURANCE_PLAN" AS "IP" /* BRONZE.client_x.INSURANCE_PLAN */
which makes sense.
Finally, @georgesittas suggested I run:
from sqlmesh import Context
ctx = Context()
model = ctx.get_model("dev_sqlmesh.client_x.insurance_plan")
print(model.python_env)
Which gives: {}
What Should Happen
@{silver} should compile out correctly at load time and run time.
What Happens Instead (error)
The variables is not loaded at run time and my models do not work.