Data types determine storage size, query performance, and data integrity. Wrong type choices cause: (1) Index failures from implicit conversion (2) Precision loss in financial calculations (3) Timezone bugs in global apps (4) Storage bloat from oversized types.

Payment Gateway — DECIMAL Precision Requirement

A fintech startup discovered their FLOAT-based amount column was causing $0.01 discrepancies on 0.1% of transactions, flagged by the payment network audit. Migrating to DECIMAL(19,4) with explicit CAST in all calculations eliminated the errors.

ALTER TABLE transactions ADD COLUMN amount_exact DECIMAL(19,4);

UPDATE transactions SET amount_exact = CAST(amount_float AS DECIMAL(19,4));

-- Verify: find rows where FLOAT and DECIMAL differ by more than $0.001

SELECT COUNT(*) FROM transactions

WHERE ABS(amount_float - amount_exact::FLOAT) > 0.001;

Payment networks require sub-cent precision. FLOAT errors accumulate across millions of transactions — a $0.01 error rate at 10M txn/day = $100K/day in reconciliation discrepancies. DECIMAL compliance is a regulatory requirement for PCI-DSS certified payment processors.

All

Global SaaS — Timezone Migration DATETIME → TIMESTAMPTZ

A SaaS platform expanding from US-only to global experienced "orders in the future" bugs when EC2 instances in different regions had different local times. Migrating all DATETIME columns to TIMESTAMPTZ and storing UTC fixed the issue.

-- PostgreSQL migration: convert naive timestamps to UTC

ALTER TABLE orders ALTER COLUMN created_at TYPE TIMESTAMPTZ

USING created_at AT TIME ZONE 'America/Los_Angeles';

-- Now all legacy records are stored as UTC

-- Display in any timezone at query time:

SELECT order_id,

created_at AT TIME ZONE customer_tz AS local_time

FROM orders JOIN customers USING (customer_id);

DST transitions cause silent bugs: a "nightly job at midnight" runs at the wrong time twice per year. TIMESTAMPTZ with named timezone handling eliminates this permanently. Critical for any app with global users or distributed infrastructure.

All

Data Warehouse ETL — TRY_CAST Quarantine Pattern

A retail analytics pipeline ingesting 50M rows/day from 200 stores was frequently crashing due to one malformed amount field per batch. The TRY_CAST + quality score pattern isolated bad rows to a quarantine table, keeping the pipeline running 24/7.

-- Quarantine rows with bad data instead of crashing

INSERT INTO sales_quarantine

SELECT *, 'INVALID_AMOUNT' AS rejection_reason

FROM sales_staging

WHERE TRY_CAST(raw_amount AS DECIMAL(19,4)) IS NULL;

INSERT INTO sales_fact (amount, sale_date, store_id)

SELECT CAST(raw_amount AS DECIMAL(19,4)), sale_date::DATE, store_id::INTEGER

FROM sales_staging

WHERE TRY_CAST(raw_amount AS DECIMAL(19,4)) IS NOT NULL;

A crashed ETL pipeline means stale dashboards, missed alerts, and analyst downtime. The quarantine pattern keeps 99.9% of good data flowing while surfacing the 0.1% bad rows for human review — meeting SLA requirements without manual intervention.

All