dbt Constraints: Progress Update Demo

Hey folks, this is sung speaking here and I'm about to give a progress update and demo of D B T constraints.

And so taking a step back, the problem I'm solving for is something I did similarly with Bitcoin and Snowflake custom macros to essentially enforce data types that look like this.

Where I wanna make sure an ID is always an integer. A this column named color is always a string, et cetera.

And right now through those macros, it gets the job done in the short term, but I wanted something more native to the experience.

One because people want it. And then two, it's because having it a native experience with really strong energetic defensive code is the best way to deliver this experience as a whole for people.

And so let me just give you a quick demo just so you can taste and see how it feels and then we'll evolve the conversation from there.

And so step one is, I have a couple examples here. I'm just gonna show you this one and then I'm gonna run a couple others and then we'll explore that as we go along.

But overall, I have a simple kind of artificial table here. I materialize it as a view and I'm selecting some things.

The next thing I do is I define something in my schema files similar to what I do with documentation and tests today.

And the key things to note here are I set this constraints enabled to true and then I make sure my, I list out data types and I list out constraints.

Keep in mind data types are mandatory for all columns and scope and then constraints are optional as well as checks because checks that really depends on the type of database that you're working with.

And so overall I'm gonna run a command that looks like this. And what this is gonna do is it's gonna make sure examples one and three, which I haven't showed you yet, error out, well example two works as expected.

You're gonna see a couple errors and then you're gonna see some conditional compiler errors. And so what this communicates to me is in plain language it's, hey, only the sequel table materialization the support for constraints.

And we know it errors out because this materialization is a view. Yep, it's a view. Yep. Errors out as expected data type values must be defined for all columns.

I notice, hey, date day it says there's nothing there. Let's check the schema. And it's like, yep, I commented that out.

So that's blank. But you notice here, constraint example two succeeded. Why is that? It's because if I go to it right now, it's a table.

It's literally the same thing. I just adjusted a couple things and I made sure everything was filled in here. Now you're probably also wondering what is the DDL for constraining example two actually look like it looks something like this on the right hand side or essentially I create a ID and teacher color string, all that fun stuff and that's all I have to do.

And I'm probably wondering why is Python out of the picture in this case? Well it's hard. <laugh> is one thing and then two is how most people will reason about bringing data types as native to their D B T experience is through a SQL table materialization.

But what's beautiful about this bullet request is going forward once the configs are settled from a D B T core standpoint to provide these healthy guardrails, other adapters can make their own specific materializations just mixing and matching these parameters as expected.

Okay. And then, ooh, yeah, this is to make sure this is a list. Let me actually fix that real quick.

Mm, Okay, cool. Now that's about it <laugh> when it comes to the main demo. The second thing is how exactly is all of this working?

I'll actually show you here in the pull request. I'm gonna refresh this real quick, but I'm not gonna go through every single file.

But the king things to note are here within parse where I add a constraints and a check config to the overall node config experience or I should really say the column config within the node config experience experience.

And then I have this big function here that does like 80% of the work where I essentially inject this constraints validator and I go through everything within the the manifest.

And then if it's a table and if constraints enabled, it's true, then it goes through all these checks and make sure that things are in sql.

Well, one, that it's a table two that all data types are filled in three conditional error messages. So it doesn't just show you error message blanks and kind of confuse the DBT user and it just prints out, you know, your neighborhood friendly error message.

The next thing to note is, hey, how is that DDL actually working? And that's where you look at something that looks like this, where we have global macros and then essentially I'm doing the bulk of the work here where I make sure I get the constraints enabled defaults to false.

If it doesn't see it, it takes in the columns and scope from constraints schema and then it sets some variables and then it creates that conditional DDL as expected similar to what you saw to what you see in examples that look like this.

Okay, I'm gonna go back here. The next thing we want to care about is The create table as, or I simply inject that DL over here.

But you notice that's not gonna be universal for all adapters. So that's why I created a simple example macro here that overrides the default create table as materialization.

And I literally just injected there. I commented out this code so I could just prove that the global one liner works and it does, which is really cool.

Now keep in mind in this example I'm using Snowflake, but Snowflake doesn't allow checks cuz if I add this in, I'll, I'll even show you if I add that in, it should error out as expected because snowflake doesn't enable checks at this point in time while other databases like Postgres do, which makes sense cuz the traditional database has things like constraints and checks.

Okay, Cool. Let me think here. Ooh, actually this is what I want to show you here. Team run and then this is where I wanna show you it arrows out strengths.

Example is an example. This should be a good one and it should gave me a database error that this is funky to shaza.

It's like what the heck is all this? And then if I go to strengths example two, you'll see syntax, everything looks kosher, but understandably Snowflake doesn't apply that.

Okay? But if I remove it, things should be good to go. I'm gonna try one more time. Suzanne, you notice how here, remove that as it auto compiled that.

We're good to go from there. And so I'm gonna go back over here. You can look at everything within this poor request here to understand the exact changes and scope.

But overall, that's pretty much what I wanted to show you. In summary, we saw simple demo two, we saw some of the code powering this and some of those considerations.

Three, you have an understanding of just the conditional error message. I guess my open ask is when it comes to the simple demo that you just saw, is this the kind of experience that you want?

Is this something that you'd feel enthusiastic to use or does this feel like clutter? Do these error messages up here?

Is the format something that really resonates and that's intuitive to reason about? Or is it feel messy or is it just right?

Let me know. All right, I'll see you later. Bye.