To replace the null or not replace the null?

that is my question. When you have nulls in a databse, do you replace them with “unknown” or other values? If so when and why? We’ve been discussing internal.

A few notes on it we’ve found so far

Kimball

Kimball outlines 3 main areas where you’ll run into nulls

and advocates for replacing null Dimensions

Putting a null in these fields can be confusing to the user, as it will appear as a blank on reports and pull-down menus, and require special query syntax to find. Instead, we recommend substituting an appropriately descriptive string, like “Unknown” or “Not provided.”

But I think most BI products (atleast Chartio) handle nulls fine these days - we don’t just have it “appear as a blank” in menus.

Fishtown

In How we Structure our DBT Projects the Fishtown team alludes to actually creating more nulls!

  • Light cleansing, such as replacing empty string with NULL values, has occurred.

How about you? Any frameworks for making the call here?