Schema Evolution

Key Characteristics

• ▶Add columns without rewriting existing data files
• ▶Remove or rename columns with transparent handling for older data
• ▶Change column data types with validation and conversion rules
• ▶Maintain query compatibility across schema versions
• ▶Track schema history for regulatory and debugging purposes
• ▶Apply defaults to new columns when reading historical data

Why It Matters

• ▶Eliminates downtime and disruption from schema changes
• ▶Reduces data engineering overhead for model updates and new requirements
• ▶Supports agile analytics development with rapid iteration on schemas
• ▶Enables clean column additions without manual null-filling operations
• ▶Simplifies compliance changes like adding audit columns without data migration
• ▶Improves data quality by enforcing nullable constraints only on new data

Example

`
-- Version 1: Original schema
CREATE TABLE events (
  event_id INT,
  user_id INT,
  timestamp TIMESTAMP
)

-- Time passes, new requirement arrives
-- Version 2: Add column (no rewrite)
ALTER TABLE events ADD COLUMN event_type STRING;

-- Queries work against current schema
SELECT * FROM events WHERE event_type = 'login';

-- Queries against old snapshot see NULL for new column
SELECT * FROM events VERSION AS OF 'v1'
-- event_type column returns NULL for all rows

-- Version 3: Add required column with default
ALTER TABLE events ADD COLUMN region STRING DEFAULT 'US';
`

Coginiti Perspective

When underlying table schemas evolve, Coginiti's semantic layer provides a stability buffer. SMDL entity definitions map physical columns to business dimensions and measures, so adding a column to a source table does not break existing semantic queries. The semantic model can be updated to expose new columns when ready, and CoginitiScript's #+test blocks can validate that schema changes do not introduce data quality regressions before the semantic model is updated.

Related Concepts