SQL Model Blueprinting bug?

[kylelundstedt]

Hi all,

This issue references a discussion in the Tobiko Slack. @izeigerman and @georgesittas were super helpful in getting me up to speed on using the new SQL model blueprinting features. This new feature has already reduced my lines of code by an order of magnitude; so cool!

However, I did run into a couple of issues for which @georgesittas suggested I open an issue. So, I've attached a zipped sqlmesh project folder that tries to illustrate the two problems.

First, as shown in models/test_1.sql, in the list of table names in the blueprints section of the model definition, the table in line 7 includes spaces in the table name, and the table in line 8 uses a reserved word in SQL (e.g., "transaction").

I've tried putting single quotes and double quotes around those table names, but to no avail. I get errors like the following:

'name' property cannot be a string value: raw."my table space". Please use the identifier syntax instead, e.g. foo.bar instead of 'foo.bar'. Line 2, Col: 28.

Second, as shown in models/test_2.sql, in the list of table names in the blueprints section of the model definition, one of the variables includes values with a colon - e.g., range_value := "A1:B4" in line 5.

In this case, sqlmesh throws the following error:
a BinderException: Binder Error: Invalid range ''J1:M3261''
Again, I tried both single and double quotes to no avail.

Hopefully, the attached example (using just sqlmesh and DuckDB) is enough to reproduce the problems.

sqlmesh_bug.zip

[georgesittas]

@kylelundstedt your project should work against the main branch now. These are the models I used for my testing, based on what you shared:

• model1.sql

MODEL (
  name raw.@{table_name},
  kind FULL,
  blueprints (
    (table_name := my_table_1),
    (table_name := 'my table space'),
    (table_name := 'transaction'),
  )
);

SELECT * FROM read_csv(@'seeds/@table_name.csv')

• model2.sql

MODEL (
  name raw.@{table_name},
  kind FULL,
  blueprints (
    (table_name := test, sheet_name := 'Lookups', range_value := 'A1:B4'),
  )
);

SELECT * FROM read_xlsx(@'seeds/@{table_name}.xlsx', sheet := @sheet_name, range := @range_value)

Plan succeeded end-to-end:

D from raw."my table space";
┌───────┬─────────┬───────┐
│  id   │  name   │ value │
│ int64 │ varchar │ int64 │
├───────┼─────────┼───────┤
│     1 │ item1   │   100 │
│     2 │ item2   │   200 │
│     3 │ item3   │   300 │
└───────┴─────────┴───────┘
D from raw.my_table_1;
┌───────┬─────────┬───────┐
│  id   │  name   │ value │
│ int64 │ varchar │ int64 │
├───────┼─────────┼───────┤
│     1 │ item1   │   100 │
│     2 │ item2   │   200 │
│     3 │ item3   │   300 │
└───────┴─────────┴───────┘
D from raw.test;
┌─────────┬─────────┐
│ Column1 │ Column2 │
│ varchar │ double  │
├─────────┼─────────┤
│ A       │     1.0 │
│ B       │     2.0 │
│ C       │     3.0 │
└─────────┴─────────┘
D from raw."transaction";
┌───────┬─────────┬───────┐
│  id   │  name   │ value │
│ int64 │ varchar │ int64 │
├───────┼─────────┼───────┤
│     1 │ item1   │   100 │
│     2 │ item2   │   200 │
│     3 │ item3   │   300 │
└───────┴─────────┴───────┘

[kylelundstedt]

Thanks @georgesittas!