Explain your process for handling schema evolution in a long-term data warehouse environment.
Write a PostgreSQL query that reports the latest deployed schema version for every warehouse table and identifies unresolved breaking changes. Schema versions with a NULL deployed_at must be ignored.
table_name, version_number, deployed_at, open_breaking_changes, and schema_statusNEVER_DEPLOYED when no deployed version exists, NEEDS_REVIEW when the latest deployed version has unresolved breaking changes, otherwise STABLEtable_name ascending| Column | Type | Description |
|---|---|---|
| table_idPK | INTEGER | Warehouse table identifier |
| table_name | VARCHAR(80) | Warehouse table name |
| Column | Type | Description |
|---|---|---|
| version_idPK | INTEGER | Schema version identifier |
| table_id | INTEGER | Referenced warehouse table |
| version_number | INTEGER | Sequential schema version number |
| deployed_at | DATE | Date the version was deployed; NULL means not deployed |
| Column | Type | Description |
|---|---|---|
| change_idPK | INTEGER | Column change identifier |
| table_id | INTEGER | Affected warehouse table |
| version_id | INTEGER | Schema version containing the change |
| change_type | VARCHAR(40) | Type of column change |
| is_breaking | BOOLEAN | Whether the change can break consumers |
| resolved_at | DATE | Date the issue was resolved |
| change_description | VARCHAR(200) | Short description of the change |