Day 22 - Audit your DB schema

1

It’s time to turn an eye toward your database schema.

Please spend 20 minutes reading through yours carefully.

Some things you might look for:

  • Inconsistent column names.
  • Missing indices for columns you frequently query by.
  • Missing unique indices to ensure uniqueness.
  • Missing null constraints.
  • Missing foreign key constraints.
  • Maybe even install bullet to detect N+1 queries in activerecord and mongoid .

Can you think of any other best practices that are missing from this list? Did you find an issue I missed? Please share it on the forum.

Enjoy!

2

The database I spend the most time in these days is Snowflake, the cloud based data warehouse. It still has a schema, but doesn’t have indices or constraints in the same way that MySQL or PostgreSQL does.

Yesterday, I needed to fix some data quality problems anyway. The prompt pushed me an extra bit to write dbt tests as I was making the fixes. There are built-in tests to emulate null or foreign key constraints, but there are also singular tests that can be specific to particular tables and columns. They are implemented as a query that returns any “failing” rows, which were ideal for the changes I needed to make.