dbeatty10
commented
1 year ago This is an intriguing idea @b-per ! 💡
Database / schema migrations
The manual steps you described are basically like database / schema migrations in Django, Rails, and other similar frameworks. Incremental models already do similar operations as those migrations (i.e., adding columns like you described), but don't do others (like replacing NULLs with some default value).
Things to solve for
The piece of the design that seems most impactful is how to specify the value to use for the new column(s). There's a couple awkward things to solve for:
- There can be more than one column that is applicable
- Specifying which column(s) need default values
- This is essentially a one-time backfill per column and then never needed again
Specifying default values for not null constraints
Idea 1
One option would be to introduce a default_values dictionary that maps column names to a SQL expression to be used as the default value instead of NULLs.
It could look similar this:
models/dim_customers.sql
{{
config(
materialized='incremental',
unique_key='id',
on_schema_change='append_new_columns',
default_values={
"some_string_column": "'foobar'",
"some_timestamp_column": "cast('0000-01-01' as timestamp)",
}
)
}}Idea 2
An alternative could be to have the default value added to the configuration of the constraint itself, like this:
models/_models.yml
models:
- name: dim_customers
config:
contract:
enforced: true
columns:
- name: some_string_column
data_type: varchar
constraints:
- type: not_null
- default_value: "foobar"
- name: some_timestamp_column
data_type: timestamp
constraints:
- type: not_null
- default_value: "cast('0000-01-01' as timestamp)"Idea 3
Full-blown support for general database migrations. Such a feature would only be relevant to models materialized as incremental because ephemeral, table, and view don't need migrations.
Summary
I prefer idea 2 over idea 1 since there's already a full listing of the column names when a contract is enforced. It solves for the first two things listed in the "things to solve for" section. But neither idea solves for the one-time nature of these migrations. To solve for that one, we'd need idea 3. That typically involves storing state, which we've historically shied away from.
What do you think of those first two options versus each other @b-per? Did you have something different in mind?
b-per
github-actions[bot]
Is this your first time submitting a feature request?
Describe the feature
When adding a new column to an incremental model (set with
append_new_columns) with a not null constraint, the new rows are checked to see if the new column is NULL but the table itself doesn't get its overall metadata/DDL updated (which is normal as there are now NULL values for previous rows).This is good as data is checked as configured, but the table attributes in the Warehouse then doesn't reflect the fact the new column is verified to be not null.
The feature suggested would be to:
not nullconstraintDescribe alternatives you've considered
Alternative 1
It is currently possible to achieve the same outcome by manually:
updatestatements to update the historical rows with NULL valuesalterstatement on the table to update the column as beingnot nullBut this approach requires writing DDL directly when the suggested feature would handle this automatically.
Alternative 2
Another approach is to run a
--full-refreshbut this might not be possible for huge tables.Who will this benefit?
People wanting to leverage the new constraints capabilities and handling large amount of data stored in incremental models.
Are you interested in contributing this feature?
If need be
Anything else?
No response