Ability to create physical layer tables using a hash function #4403
Description
Description
Currently, some database engines, such as Postgres, have a limit on the length of object names. As a result, sqlmesh users are either heavily restricted in naming conventions or experience conflicts between production and development environments when creating tables.
Example model name: analytics_intermediate.int_booking_to_lesson_transitions_logs__extracted
In the logs, we see the full physical table name as:
sqlmesh__analytics_intermediate.analytics_intermediate__int_booking_to_lesson_transitions_logs__extracted__4174565263
but in Postgres it is created as:
sqlmesh__analytics_intermediate.analytics_intermediate__int_booking_to_lesson_transitions_logs_
which effectively causes every sqlmesh plan command to work with the same snapshot version.
This leads to several issues:
-
Data loss when running plan from any environment (prod/dev), as the same snapshot is reused.
-
Schema changes require table deletion — since the table is created using
CREATE TABLE IF NOT EXISTS, it keeps the existing schema unless dropped. Meanwhile, new inserts use the updated schema, leading to Postgres errors at that stage.
Potential Solution
A possible solution is to allow physical table names to be generated using a hash function. For example, if we applied the md5 hash function to the snapshot name, the resulting physical table path could look like:
"<physical_schema>"."e3b5abfa001550e395b664bd0abde058"
This approach would:
-
Let users control the length of physical table names.
-
Eliminate the issues described above.