Schema and Type Consistency

Type Mismatches That Break Everything

A type mismatch occurs when a column stores data in a format that does not match its logical type. A revenue column stored as text, a customer_id stored as a float, or a date stored as a string are all type mismatches. They are dangerous because they do not raise errors; they produce wrong results silently.

The Silent Corruption Problem

Consider a customer_id column stored as text in one table and as an integer in another. A join between them fails or produces unexpected results:

-- This join returns zero rows even though the IDs match
SELECT *
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
-- o.customer_id is TEXT: '00123'
-- c.customer_id is INTEGER: 123
-- '00123' = 123 is FALSE

The text value '00123' and the integer 123 represent the same customer, but the database does not know that. The join silently returns no matches. No error is raised. The analyst sees an empty result and may conclude there are no orders, when in fact every order failed to join.

Common Type Mismatches

Numbers stored as text: '123', '1,234.56', '$99.99'. These cannot be summed, averaged, or compared numerically without conversion. The dollar sign and comma are formatting characters that must be removed before casting.

Dates stored as text: '2024-03-15', '03/15/2024', 'March 15, 2024'. These cannot be sorted chronologically, filtered by range, or used in date arithmetic without parsing.

Identifiers stored as numbers: 123 vs '00123'. Leading zeros are significant in identifiers but meaningless in numbers. Casting '00123' to integer produces 123, which no longer matches the original identifier.

Booleans stored as text: 'true', 'TRUE', '1', 'Y', 'yes'. These require mapping to a proper boolean type before logical operations work correctly.

Detecting Type Mismatches

The first step is to inspect the actual schema and sample values.

-- Check column types
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders';

-- Sample values to spot formatting issues
SELECT customer_id, typeof(customer_id)
FROM orders
LIMIT 20;

The information_schema query reveals the declared type. The sample values reveal the actual content. A column declared as TEXT but containing only numeric strings is a type mismatch waiting to cause problems.

Why This Matters for Analysis

Type consistency is not a cosmetic concern. It determines whether joins succeed, whether aggregations produce correct results, and whether comparisons behave as expected. A single mismatched column can invalidate an entire analysis without producing a single error message.

The rule is simple: every column should have exactly one type, and that type should match the logical meaning of the data. Enforce this rule at the schema level with constraints, and validate it at the data level with profiling queries.

Profiling Queries for Type Detection

Before converting a column, profile its contents to understand what you are dealing with. A profiling query reveals the distribution of values, the presence of formatting characters, and the range of the data.

-- Profile a text column that should be numeric
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE revenue_text ~ '^[0-9]+$') AS pure_integer,
    COUNT(*) FILTER (WHERE revenue_text ~ '^[0-9,]+$') AS has_commas,
    COUNT(*) FILTER (WHERE revenue_text ~ '^\$[0-9,.]+$') AS has_dollar_sign,
    COUNT(*) FILTER (WHERE revenue_text ~ '^-') AS negative_values,
    MIN(LENGTH(revenue_text)) AS min_length,
    MAX(LENGTH(revenue_text)) AS max_length
FROM orders;

This single query tells you how many values are pure integers, how many have formatting characters, and how many are negative. That information determines the conversion strategy: if 95% of values are pure integers, a simple cast works. If 30% have dollar signs and commas, you need to strip those characters first. If any are negative, you need to decide whether negative revenue is valid (refunds) or an error.

Profiling before converting prevents the two most common conversion failures: attempting to cast a formatted number without stripping formatting characters, and accidentally converting a column that contains mixed types (numbers and text labels) into all NULLs.

Casting and Safe Type Conversion

Casting converts a value from one type to another. Safe casting handles conversion failures gracefully by returning NULL instead of raising an error. This is critical when converting messy real-world data where some values will not match the target type.

Basic Casting

-- Cast text to integer
SELECT CAST(customer_id AS INTEGER) AS customer_id_int
FROM orders;

-- Cast text to decimal
SELECT CAST(REPLACE(REPLACE(revenue_text, '$', ''), ',', '') AS DECIMAL(12,2)) AS revenue
FROM orders;

-- Cast text to date
SELECT CAST(order_date AS DATE) AS order_date_clean
FROM orders;

The REPLACE calls remove formatting characters ($ and ,) before casting to decimal. Without this cleanup, CAST('$1,234.56' AS DECIMAL) raises an error in most databases.

Safe Casting with Error Handling

When you cannot guarantee that every value will convert, use a safe casting pattern that returns NULL for failures.

-- Safe cast: return NULL if conversion fails
SELECT
    CASE
        WHEN customer_id ~ '^\d+$' THEN CAST(customer_id AS INTEGER)
        ELSE NULL
    END AS customer_id_int
FROM orders;

The regular expression ^\d+$ checks that the string contains only digits. If it does, the cast succeeds. If not, NULL is returned. This pattern prevents the entire query from failing because of a few bad values.

Converting Booleans from Text

Boolean columns stored as text require a mapping step before casting.

SELECT
    CASE UPPER(TRIM(active_flag))
        WHEN 'TRUE' THEN TRUE
        WHEN 'FALSE' THEN FALSE
        WHEN '1' THEN TRUE
        WHEN '0' THEN FALSE
        WHEN 'Y' THEN TRUE
        WHEN 'N' THEN FALSE
        WHEN 'YES' THEN TRUE
        WHEN 'NO' THEN FALSE
        ELSE NULL
    END AS is_active
FROM customers;

The ELSE NULL catches any value not in the mapping. Those NULLs should be investigated: they represent either new variants that need mapping or data errors.

Preserving Leading Zeros in Identifiers

Identifiers like '00123' must remain text. Casting them to integer destroys the leading zeros and breaks joins with other tables that store the identifier as text.

-- Correct: keep as text, just trim whitespace
SELECT TRIM(customer_id) AS customer_id_clean
FROM orders;

-- Incorrect: destroys leading zeros
SELECT CAST(customer_id AS INTEGER) AS customer_id_clean
FROM orders;

The rule: if a value is an identifier (used for joining, not for arithmetic), keep it as text. If it is a measure (used for summing, averaging), convert it to a number.

Validating After Conversion

Always validate the results of a conversion. Check for NULLs that indicate conversion failures, and verify that the converted values make sense.

-- Count conversion failures
SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN customer_id_int IS NULL AND customer_id IS NOT NULL THEN 1 ELSE 0 END) AS failed_conversions
FROM (
    SELECT
        customer_id,
        CASE WHEN customer_id ~ '^\d+$' THEN CAST(customer_id AS INTEGER) ELSE NULL END AS customer_id_int
    FROM orders
) sub;

If failed_conversions is greater than zero, investigate the failing values. They may require a different conversion rule or may be genuine data errors that need correction at the source.

Handling Mixed-Type Columns

Sometimes a column contains both numbers and text labels. A quantity column might have values like '5', '10', and 'out of stock'. Casting the entire column to integer produces NULLs for the text values, losing the information that those rows represent a different state.

The correct approach is to split the column: one numeric column for the quantity and one text column for the status.

SELECT
    order_id,
    CASE WHEN quantity ~ '^\d+$' THEN CAST(quantity AS INTEGER) ELSE NULL END AS quantity_num,
    CASE WHEN quantity ~ '^\d+$' THEN NULL ELSE quantity END AS quantity_status
FROM order_items;

This pattern preserves all information. Rows with numeric quantities get a number and NULL status. Rows with text labels get NULL quantity and the text status. Downstream analysis can filter on quantity_status IS NULL to get only numeric rows, or use COALESCE(quantity_num, 0) to treat non-numeric rows as zero quantity.

Mixed-type columns are a schema design problem that should be fixed at the source. When you encounter one in an existing dataset, split it rather than forcing a conversion that loses information.

Unit Consistency and Standardization

Unit inconsistency occurs when the same measurement is recorded in different units across rows or tables. A weight column with values in kilograms in one row and pounds in another will produce wrong sums and averages. A distance column with values in miles and kilometers will make geographic analysis meaningless.

Detecting Unit Inconsistency

Unit inconsistency is hard to detect programmatically because the values are all valid numbers. You need domain knowledge or metadata to identify the problem.

-- Look for bimodal distributions that suggest mixed units
SELECT
    CASE
        WHEN weight < 50 THEN 'likely_kg'
        WHEN weight >= 50 AND weight < 200 THEN 'ambiguous'
        WHEN weight >= 200 THEN 'likely_lbs'
    END AS unit_guess,
    COUNT(*) AS row_count,
    AVG(weight) AS avg_weight,
    MIN(weight) AS min_weight,
    MAX(weight) AS max_weight
FROM products
GROUP BY unit_guess;

If the weight column has values clustered around 1-20 (kilograms) and 20-200 (pounds), the bimodal distribution reveals the unit mismatch. The thresholds are domain-specific; the pattern is the clue.

Standardizing to a Single Unit

Once the units are detected, convert all values to a single canonical unit. Choose the unit that is most common in the data or most standard in the domain.

-- Convert all weights to kilograms
SELECT
    product_id,
    weight,
    CASE
        WHEN weight_unit = 'lbs' THEN weight * 0.453592
        WHEN weight_unit = 'kg' THEN weight
        WHEN weight_unit IS NULL THEN NULL
        ELSE NULL
    END AS weight_kg
FROM products;

If the unit is not stored in a separate column, you must infer it from the value range or from the source system. This is less reliable and should be documented as an assumption.

Currency and Time Zone Standardization

Financial data often mixes currencies. A revenue column with values in USD, EUR, and GBP cannot be summed without conversion.

-- Convert all revenue to USD
SELECT
    order_id,
    revenue,
    currency,
    CASE currency
        WHEN 'USD' THEN revenue
        WHEN 'EUR' THEN revenue * 1.08
        WHEN 'GBP' THEN revenue * 1.27
        ELSE NULL
    END AS revenue_usd
FROM orders;

Exchange rates change over time. For historical accuracy, use the rate effective on the transaction date, not today's rate. Store the rate and its effective date in a reference table and join on the date.

Time zones are similar. A timestamp column with values in UTC, EST, and PST cannot be compared directly. Convert all timestamps to UTC for storage and analysis:

-- Convert to UTC
SELECT
    event_id,
    event_timestamp AT TIME ZONE 'UTC' AS event_timestamp_utc
FROM events;

Documenting Unit Decisions

Every unit conversion is a decision that affects the data. Document:

1. The canonical unit chosen
2. The conversion factor or formula
3. The source of the conversion factor (e.g., "NIST standard", "company reference table")
4. Any assumptions made (e.g., "assumed all values over 200 are in pounds")

Without this documentation, a reviewer cannot verify that the conversions are correct, and a future analyst may unknowingly mix the converted values with raw values from another source.

Dimensional Consistency

When the same measurement appears in multiple tables, the units must match. A weight column in the products table (kilograms) and a weight column in the shipments table (pounds) cannot be compared or joined without conversion.

-- Standardize weight to kilograms across tables
SELECT product_id, weight AS weight_kg FROM products
UNION ALL
SELECT product_id, weight * 0.453592 AS weight_kg FROM shipments;

The same principle applies to currency, time zones, and any other measurement with multiple representations. Choose one canonical unit per measurement and convert all sources to that unit at the point of ingestion. Do not leave unit conversion to downstream analysts; it is a data quality responsibility, not an analysis decision.

Dimensional consistency also applies to granularity. A sales table with daily granularity and a targets table with monthly granularity cannot be joined directly. One must be aggregated or the other disaggregated before the join. Document the granularity of every table and ensure that joins operate at compatible levels.

Key Alignment and Schema Enforcement

Keys are the glue that holds a relational schema together. When keys are inconsistent across tables, joins fail, aggregations double-count, and the entire analysis becomes unreliable. Key alignment ensures that the same entity is represented identically in every table.

Primary Key Consistency

A primary key uniquely identifies a row in a table. The same entity should have the same primary key value in every table where it appears.

-- Check for duplicate primary keys
SELECT customer_id, COUNT(*) AS occurrences
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

-- Check for foreign keys with no matching primary key
SELECT o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

The first query finds duplicate primary keys, which violate the uniqueness constraint. The second finds orphaned foreign keys: orders that reference a customer who does not exist in the customers table. Both indicate key misalignment.

Foreign Key Type Alignment

Foreign keys must have the same type as the primary keys they reference. A customer_id that is INTEGER in the customers table and TEXT in the orders table will not join correctly.

-- Align foreign key type to match primary key
ALTER TABLE orders
ALTER COLUMN customer_id TYPE INTEGER
USING CAST(customer_id AS INTEGER);

This conversion should only be attempted after verifying that all values in the foreign key column can be safely cast to the target type. Use the safe casting pattern from the previous chapter to identify problematic values first.

Composite Keys

Some tables use composite keys: multiple columns that together uniquely identify a row. A order_items table might use (order_id, product_id) as its primary key.

-- Check for duplicate composite keys
SELECT order_id, product_id, COUNT(*) AS occurrences
FROM order_items
GROUP BY order_id, product_id
HAVING COUNT(*) > 1;

Composite keys must be treated as a unit. Joining on only one column of a composite key produces a Cartesian product if that column is not unique on its own.

Enforcing Consistency with Constraints

The most reliable way to maintain type and key consistency is to enforce it at the schema level with constraints.

-- Enforce uniqueness
ALTER TABLE customers ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id);

-- Enforce referential integrity
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Enforce type with a check constraint
ALTER TABLE products ADD CONSTRAINT chk_weight_positive
    CHECK (weight > 0);

Constraints prevent bad data from entering the database. A duplicate key, an orphaned foreign key, or a negative weight will be rejected at insert time rather than discovered months later during analysis.

Schema Documentation

Document the schema: every column's type, every key's definition, every constraint's purpose, and every unit and currency assumption. This documentation is the contract between the data producers and the data consumers. Without it, every analyst makes different assumptions, and the same question produces different answers from different people.

Store the schema documentation alongside the code that produces and consumes the data. Version it with the data. When the schema changes, update the documentation and communicate the change to all consumers.