Your Schema Is an API: Design It Like One

Shannon Lowder

Shannon Lowder

— 3 min read

A staging schema is a contract. It says: data arrives here, in this shape, from these sources, and the downstream systems can count on it being there.

Most of us don't design it like a contract. We design it like a scratch pad — add columns as needed, rename things when they conflict, drop tables when you're done with them. The downstream system finds out what happened when it breaks.

I want to convince you to think about your database schema the way a software developer thinks about a public API. The discipline is the same. The payoff is the same.

What Makes an API Good (Or Terrible)

A good API has a few key properties. It's stable — callers can depend on it not changing without warning. It's discoverable — you can figure out what it does from the names and structure alone. And it has clear boundaries — it does a specific thing well and doesn't try to do everything.

Compare that to a typical staging schema. Tables with names like Staging_Import_v2_FINAL and TempLoad_DO_NOT_DELETE. Columns that show up in three different tables with three different data types because different developers added them at different times. No foreign keys, no constraints, no documentation.

Every downstream process that reads from that schema is fragile, because the contract keeps changing out from under it.

Schema Design as Contract Design

The first principle: name things for what they mean, not for how they were created.

-- What most people write
CREATE TABLE staging.Import_CustomerData_v3 (
    Col1        NVARCHAR(255),
    Col2        NVARCHAR(255),
    ImportDate  DATETIME,
    Flag        INT
);

-- What a contract looks like
CREATE TABLE staging.Customer (
    CustomerId      INT             NOT NULL,
    CustomerName    NVARCHAR(200)   NOT NULL,
    Email           NVARCHAR(320)   NULL,
    LoadedAt        DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME(),
    SourceSystem    NVARCHAR(50)    NOT NULL
);

The second version tells you what it is. Six months from now, when someone joins the team and needs to understand the data flow, the first version requires an archaeologist. The second requires five minutes of reading.

Stability and Versioning

APIs version their breaking changes. Adding a nullable column is usually safe — callers can ignore it. Renaming a column, removing one, or changing a data type is a breaking change — callers need to know.

Apply the same logic to your schemas. A new nullable column in a staging table is usually safe. Renaming CustID to CustomerId will break every stored procedure and SSIS package that references it by name.

When you need to make a breaking change, have a plan. Either run both schemas in parallel during a transition window, or make the change deliberately with a coordinated update to all downstream consumers. Not a surprise on a Tuesday morning.

Schemas as Documentation

The best APIs don't need much external documentation because the interface itself is expressive. Your schema can do the same work.

Use NOT NULL where data is required. Use meaningful default values. Use check constraints to capture business rules the schema should enforce rather than leaving them to application code. Add extended properties in SQL Server to document non-obvious decisions.

-- Encode what you know as constraints
ALTER TABLE staging.Customer
ADD CONSTRAINT chk_Customer_Email
    CHECK (Email LIKE '%@%.%' OR Email IS NULL);

-- Document decisions that aren't obvious from the schema
EXEC sys.sp_addextendedproperty
    @name      = N'MS_Description',
    @value     = N'NULL = customer opted out. Empty string = unknown.',
    @level0type = N'SCHEMA', @level0name = N'staging',
    @level1type = N'TABLE',  @level1name = N'Customer',
    @level2type = N'COLUMN', @level2name = N'Email';

That extended property shows up in SSMS's column description panel. Low ceremony, but the next developer doesn't have to guess what NULL means in that column.

The Discipline Pays Off

None of this is complicated. It's just discipline — making deliberate decisions about names, types, constraints, and stability rather than letting the schema accumulate organically.

The payoff is a data model that downstream processes can rely on. Less debugging when things change. Faster onboarding for new developers. And a system that communicates its own intent instead of requiring tribal knowledge to interpret.

If you've found patterns that work well for schema design in a data warehouse context, I'd love to hear them. As always, I'm here to help.

Read more