Schema evolution showcase

Data is ever changing. A couple of examples you will definitely face with while dealing with data pipelines:

– adding new attributes, removing old ones
– changing data types: int -> float, resizeing text
– renaming columns (change mapping)

Got a couple of new events with an attribute exceeding current maximum length in database.

Simple approach is to resize problem column:

ALTER TABLE hevo.wheely_prod_orders ALTER COLUMN stops TYPE VARCHAR(4096) ;

A lot of ELT tools can do it automatically without DE attention.

In my case it's tricky as I am using Materialized Views:

> SQL Error [500310] [0A000]: Amazon Invalid operation: cannot alter type of a column used by a materialized view

As a result I had to drop dependent objects, resize columns, then re-create objects once again

DROP MATERIALIZED VIEW flatten.flt_orders CASCADE ;
DROP MATERIALIZED VIEW dbt_test.flt_orders CASCADE ;
DROP MATERIALIZED VIEW ci.flt_orders CASCADE ;

ALTER TABLE hevo.wheely_prod_orders ALTER COLUMN stops TYPE VARCHAR(4096) ; 

dbt run -m flt_orders+1 --target prod
dbt run -m flt_orders+1 --target dev
dbt run -m flt_orders+1 --target ci

#pipelines #schemaevolution