Support not-null constraints as part of primary key definition

[katieclaiborne]

Describe the feature

I'd like for a model with a unique test and a not-null constraint applied to the same column to satisfy the primary key rule.

Describe alternatives you've considered

In our project, we've considered overriding the primary_key_test_macros variables to define a uniqueness test alone as valid. This would allow developers to replace not-null tests with constraints, but it wouldn't validate that they have.

    primary_key_test_macros: [
      ["data_platform.test_unique"],
      ["data_platform.test_unique_combination_of_columns"]
    ]

Additional context

We use BigQuery, so the not-null constraint is the only one available to us.

Who will this benefit?

Teams looking to leverage constraints without duplicating existing tests or sacrificing test coverage.

Are you interested in contributing this feature?

I'd love to contribute, but would need a hand getting started.

I've poked around a bit to understand how the primary_key_test_macros variable is currently used, and I've inspected how constraints appear in the project manifest. The tricky bit seems to be that column-level constraints appear as part of the relevant model node, while tests appear as their own nodes, with dependencies on (test) macro and model nodes.

[dave-connors-3]

Hey @katieclaiborne! This is a really great idea!

I think we've intentionally stayed away from too much column-level madness, but I think this could be a good excuse to investigate how to bring those concepts into the package!

To start, we could pull in all column info as a JSON string from the graph object in stg_nodes, to at least pull in the relevant data. Then, we could write another CTE in the int_model_test_summary that flattens and joins that column data back, and checks whether the set of tests and constraints on the column match the full set of tests and constraints you'd like to consider as acceptable.

I can see a couple places where it could get tough!

1. Not all warehouses are build the same when it comes to dealing with JSON -- would likely require some tricky macro dispatching to make this cross-warehouse compatible!
2. The logic could get a little weird if we have multiple definitions of "primary key tested" -- in your example, I assume you'd still like non-contracted (and therefore constrained) models to still pass the litmus test if they had a not_null and unique test applied, even without the constraint. We may need to come up with a nice mechanism to define the acceptance criteria across tests and constraints that isn't too hairy

I'm happy to do a little early research and report back!

[katieclaiborne]

That'd be great! Thanks, Dave.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[dave-connors-3]

@katieclaiborne i am definitely willing to keep this open if you want! i think with the new column-level model, we might have some ability to add this in. Any interest in a contribution?

[katieclaiborne]

Yes! Thank you, Dave.

[katieclaiborne7]

@dave-connors-3, hello from the other side!

Could you offer a bit of guidance on how you would approach this? I have some time on my hands, and would be game to give it a try.