Repository navigation
TypeError when using --include-ctes with DATE, DATETIME, or TIMESTAMP columns in sqlmesh create_test #5550
Description
Hi team!
When creating unit tests, if a column has a data type of DATE, DATETIME, or TIMESTAMP, the command
sqlmesh create_test {model} --include-ctes --query {} throws the following error:
TypeError: list indices must be integers or slices, not str
Without the --include-ctes option, it works perfectly (I believe there are no structural issues or incorrect column types). However, when using --include-ctes, it only works if those columns are cast to STRING. (I’m using the BigQuery engine with the default DuckDB test engine.)
Here is a simple code example based on the SQLMesh documentation.
model_unit_test.sql
MODEL (
name sqlmesh_example.filtered_cte_view,
kind VIEW,
audits (
NOT_NULL(columns = [id])
),
ignored_rules ['ambiguousorinvalidcolumn']
);
WITH filtered_seed_cte AS (
SELECT
id,
item_id,
event_date
FROM sqlmesh_example.seed_model
WHERE
item_id = 1
)
SELECT
item_id,
COUNT(DISTINCT id) AS num_orders
FROM filtered_seed_cte
GROUP BY
item_id
seed_model.sql
MODEL (
name sqlmesh_example.seed_model,
kind SEED (
path '../seeds/seed_data.csv'
),
audits (
unique_values(columns := (
id
))
)
)
Command for creating the unit test:
sqlmesh create_test "sqlmesh_example.filtered_cte_view" \
--include-ctes \
--query "sqlmesh_example.seed_model" \
"SELECT
4 AS id,
1 AS item_id
UNION ALL
SELECT
5,
1
UNION ALL
SELECT
7,
2
UNION ALL
SELECT
8,
2 " \
--overwrite
Could someone from the SQLMesh team take a look at this issue?
Context: https://tobiko-data.slack.com/archives/C044BRE5W4S/p1760719851382139
Activity
brucearctor commentedon Oct 23, 2025
Confirmed addressed, Thanks @treysp !