How to manage schema migrations in Redshift with Airflow

Why use Airflow for schema migrations?

Investigation of other solutions

Prerequisites

Redshift Limitations

ALTER TABLE example_table
ADD COLUMN IF NOT EXISTS start_time timestamp;
SELECT EXISTS (
SELECT count(*) as column_count FROM pg_table_def
WHERE schema='public' AND tablename='example_table' AND "column" IN ('start_time')
GROUP BY tablename
HAVING column_count = 1
) AS pg_table_def_check

Airflow DAG

default_args = {
'owner': 'airflow',
'depends_on_past': False,
'start_date': days_ago(2),
'email': ['airflow@example.com'],
'email_on_failure': False,
'email_on_retry': False,
'retries': 0
}
dag = DAG(
'schema_migration',
default_args=default_args,
description='A Redshift Schema Migration DAG',
schedule_interval="@once",
)
schema_migrator = BashOperator(
task_id='schema_migrator',
bash_command='cd /path/to/local/airflow; python3 -m path.to.airflow.task.schema_migrator',
dag=dag,
)

Airflow Migrate Schemas Task

↳migrate_schemas
↳ sql_scripts
↳ pg_table_def_checks
↳ 001_example_table_pg_table_def_check.sql
↳ alter_table_script
↳ 001_example_table_alter_add_columns.sql

Redshift Transactional Blocks

Conclusion

--

--

--

Software engineer in Ireland with an interest in technology, sociology, politics and writing about them and where they intersect

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to Merge Two Sorted Arrays?

How to follow and embed our Latin America 2017 meeting

Preventing Systemic Failure: Bulkheads in Microservice Architectures

Composition with structures and method forwarding in Go

Drupal 8/9 migration Traversing backward on an XML file

From human to binary: A basic guide through the compilation process in C.

A Modern Enterprise Architecture Approach — Chapter 1

A Modern Enterprise Architecture Approach — Chapter 1 Transform the enterprise with Mobility, Cloud, IoT & Big Data by Dr Mehmet Yildiz on ILLUMINATION Book Chapters — Medium.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Bríd Moynihan

Bríd Moynihan

Software engineer in Ireland with an interest in technology, sociology, politics and writing about them and where they intersect

More from Medium

Concurrency & Parallel Programming in Python

Query Plan in Postgres

A Query’s Journey Through PostgreSQL

Locating gaps in enumerated rows using the SQL lag window function (MySQL edition)