Type Review Framework

Concepts covered: Data Types, Constraints

A column's type is the cheapest quality control a schema has, applied by the database to every write with no pipeline, test or reviewer, and the most expensive thing to change once the table is large. A type review before the first deploy asks every column the same questions.

The per-column review

Money or anything reconciled against another system: DECIMAL with stated precision and scale, or BIGINT minor units beside a currency column. FLOAT is a finding.
Text: does the length come from a standard or a known format, and does this platform reject or truncate past it? Is it a code that wants CHAR?
Time: past instant (TIMESTAMPTZ), future appointment (local time plus IANA zone), calendar day (DATE) or duration? A naive TIMESTAMP needs a written source for its zone.
Constrained values: will the set grow or carry labels? Lookup table if so, CHECK if only validity matters, ENUM only for sets fixed by definition.
Booleans: NOT NULL with a default, no 2 flags that cannot both be true, and a nullable timestamp for a flag that records an event.
Keys: BIGINT from a sequence, or native uuid holding time-ordered values. INT primary keys and CHAR(36) ids are findings.

A type shapes; a constraint rejects

The type decides what a stored value looks like, a weaker promise than it sounds. Inserting 19.995 into a PostgreSQL NUMERIC(10,2) column stores 20.00 with no error, because the engine rounds to the declared scale on write; MySQL in strict mode does the same and emits a note the driver discards. A negative amount fits DECIMAL(10,2), and so does a NULL. Rejection comes from constraints: NOT NULL for a fact that must exist, CHECK (amount >= 0) for a sign the business forbids, a foreign key for a code that must exist in a lookup table. A reviewed column names both its type and the constraints that make it a validator.

CREATE TABLE invoice_lines (
  invoice_line_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  invoice_id      BIGINT NOT NULL REFERENCES invoices(invoice_id),
  quantity        INTEGER NOT NULL CHECK (quantity > 0),
  unit_price      DECIMAL(12,4) NOT NULL CHECK (unit_price >= 0),
  line_total      DECIMAL(12,2) NOT NULL CHECK (line_total >= 0),
  currency_code   CHAR(3) NOT NULL REFERENCES currencies(code),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

What a wrong type costs to fix

Platform	Metadata-only change	Rewrite or rebuild
PostgreSQL	Widening VARCHAR(n), VARCHAR to TEXT	INT to BIGINT, FLOAT to NUMERIC: full rewrite under an exclusive lock, plus every index
MySQL	Appending an ENUM value, widening VARCHAR within the same length-prefix size	Most others copy the table; gh-ost or pt-online-schema-change avoid downtime
Snowflake	Widening VARCHAR, raising NUMBER precision	Scale or type family: add a column, backfill, swap names
BigQuery	INT64 to NUMERIC, NUMERIC to BIGNUMERIC or FLOAT64, lengthening STRING(L)	Narrowing or cross-family: CREATE OR REPLACE TABLE AS SELECT

The rewrite column is where the review pays for itself. An id INT primary key on payment_events holds 2,147,483,647 values; at 1,000 inserts per second that is 24.9 days of continuous writes, which a growing platform reaches within a few years of normal traffic. The fix rewrites the table and then every table whose foreign key references it, in dependency order, each under its own lock. Declared BIGINT on the first day, the column costs 4 extra bytes per row and nothing else.

Do

Declare money as DECIMAL or BIGINT minor units, and write down the rounding rule beside it
Take string lengths from a standard or a known format, and check how the platform treats overflow
Store past events as TIMESTAMPTZ and future appointments as local time plus an IANA zone name
Model a growing value set as a lookup table with a foreign key
Pair every type with the NOT NULL, CHECK and FK constraints that reject bad rows

Avoid

Let an ORM default pick FLOAT for a column anyone will reconcile
Size a column from a sample and assume the longest value has been seen
Subtract 2 naive timestamps and call the result elapsed time
Add a third boolean flag to a row that already cannot keep 2 consistent
Choose INT for a key or CHAR(36) for a UUID on a table that will grow

Review this column from a proposed payments table that will serve customers in 40 countries: amount DECIMAL(10,2). Which finding is the one to raise?

DECIMAL arithmetic is slow at this volume; switch to FLOAT
The scale is fine, but the column needs NOT NULL and CHECK (amount >= 0), and 10 digits caps a payment at 99,999,999.99, which a rupiah invoice can exceed
Replace it with amount_cents BIGINT and drop the idea of a currency column
Change it to DECIMAL(10,4) to hold fractional cents

About this lesson

This section is part of the Schema Types lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.