Empty tables / schema migrations

[jwhitaker-gridcog]

Hey guys,
I'm starting to play around with SQLMesh with the intention of using it for Clickhouse - I should have a PR up soon with some initial support.

One early thing I've hit is that I want to create some empty tables along with my SQLMesh-managed models, ideally I'd do this by directly writing the create table statement in a model. I want to do this because I'll set up an external load to dump raw data into those tables, and it's neat if I can describe their structure in my SQLMesh project.

Is this 1) possible? 2) a good idea or asking for trouble?

I can probably set up a custom materialization to achieve this, but I'm pretty new to SQLMesh so feels like I might be holding it wrong. :)

It feels a bit like most people must be currently doing this via an external tool to manage migrations, like Atlas, stitch, etc... and referencing in SQLMesh as an external model. This would work but I'm trying to avoid introducing multiple bits of tooling.

[tobymao]

@jwhitaker-gridcog we should have clickhouse ready in the next couple of weeks. we're working on it.

you could run a create table statement in a model in a pre statement, that should be fine.

[jwhitaker-gridcog]

Would that track column lineage and stuff though?

[jwhitaker-gridcog]

Also very excited to hear about clickhouse, interested to see how engine settings and stuff are surfaced. I'm tending towards using ch mat views to handle moving data around, in which case sqlmesh would just handle orchestration of ddl - is this likely to be possible?

Additionally, are you testing against Clickhouse Cloud? A few things I've run into work in single local ch, but not cloud - Create Table As Select is a big one, but there are a heap of smaller differences i think.