Liquibase Usage Guide

We run Liquibase migrations against our Redshift data warehouse from two separate repositories. Each repo owns a different layer of the warehouse and authenticates as a different user. This guide explains which repo a migration belongs in and which credentials it will run as.

TL;DR

Migration kind	Repo	Database(s)	Runs as
Create/alter a database, schema, or role; grant or revoke permissions; define a federated external schema	data-warehouse	all	admin
Create/alter a table or materialized view	airflow-dags	*_raw	airbyte
Create/alter a table or materialized view	airflow-dags	*_analysis	airflow
Fix bad data (INSERT/UPDATE/DELETE)	airflow-dags	*_analysis only	airflow

If you need admin-level privileges to run the change by hand, it belongs in data-warehouse. Otherwise it belongs in airflow-dags.

Repository responsibilities

data-warehouse — structural ownership

data-warehouse owns the warehouse's structure:

• Schemas (CREATE SCHEMA, including federated CREATE EXTERNAL SCHEMA)
• Role definitions and grants (GRANT/REVOKE against schemas, tables, system views)
• Anything else that requires the admin user

Migrations here run as the admin warehouse user against the admin schema and target every warehouse database (adaction_*, adgem_*, servicehub_*, cosmic_*, og_*).

airflow-dags — data object ownership

airflow-dags owns the data objects that live inside the schemas data-warehouse provisions:

• Tables
• Materialized views (including prod_mirror.* views built on the federated schemas defined in data-warehouse)
• Data manipulation migrations (see below)

Migrations here run against adaction_*, adgem_*, and servicehub_* only. The user is chosen at runtime from the database name:

• Databases ending in _raw run as airbyte
• Databases ending in _analysis run as airflow

Data manipulation migrations

We occasionally need a one-off Liquibase changeset to correct bad data — for example, backfilling a column or deleting orphaned rows.

Two rules:

1. Only in airflow-dags. data-warehouse is for structure, not data.
2. Only against _analysis databases. The _raw databases are written by Airbyte. Hand-editing them risks conflicting with the next sync and masking upstream data quality issues. Fix the data downstream in _analysis, where the airflow user owns the rows.

If a fix appears to require touching a _raw database, stop and raise it with the data team — the right answer is usually a fix in the upstream source or in the Airbyte connector configuration.

Granting read access to an Airbyte-owned schema

Airbyte loads each CRM/source into a schema inside a *_raw database (for example adaction_raw.hubspot) and grants nobody read access by default. When a new pipeline needs to read one of these schemas — the dbt transformer role for the transform, the reader role for analysts — the grant belongs in data-warehouse (it needs admin), as a changeset on the matching changelog-<database>_raw.yaml.

The naive "just GRANT SELECT" version looks right and fails in three ways we learned the hard way (Redshift, adaction_raw.hubspot). The canonical pattern handles all three:

- changeSet:
    id: <n>
    author: <you>
    labels: permissions
    context: <schema>
    comment: read permissions on airbyte-loaded <schema> for reader and transformer
    changes:
      - sql:
          sql: CREATE SCHEMA IF NOT EXISTS <schema>
      - sql:
          sql: ALTER SCHEMA <schema> OWNER TO airbyte
      - sql:
          sql: GRANT USAGE ON SCHEMA <schema> TO ROLE reader
      - sql:
          sql: GRANT USAGE ON SCHEMA <schema> TO ROLE transformer
      - sql:
          sql: GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO ROLE reader
      - sql:
          sql: GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO ROLE transformer
      - sql:
          sql: ALTER DEFAULT PRIVILEGES FOR USER airbyte IN SCHEMA <schema> GRANT SELECT ON TABLES TO ROLE reader
      - sql:
          sql: ALTER DEFAULT PRIVILEGES FOR USER airbyte IN SCHEMA <schema> GRANT SELECT ON TABLES TO ROLE transformer
    rollback:
      # REVOKE only — never DROP the schema (see gotcha 3)
      - sql:
          sql: ALTER DEFAULT PRIVILEGES FOR USER airbyte IN SCHEMA <schema> REVOKE SELECT ON TABLES FROM ROLE reader
      - sql:
          sql: ALTER DEFAULT PRIVILEGES FOR USER airbyte IN SCHEMA <schema> REVOKE SELECT ON TABLES FROM ROLE transformer
      - sql:
          sql: REVOKE SELECT ON ALL TABLES IN SCHEMA <schema> FROM ROLE reader
      - sql:
          sql: REVOKE SELECT ON ALL TABLES IN SCHEMA <schema> FROM ROLE transformer
      - sql:
          sql: REVOKE USAGE ON SCHEMA <schema> FROM ROLE reader
      - sql:
          sql: REVOKE USAGE ON SCHEMA <schema> FROM ROLE transformer

Three gotchas this pattern handles

1. The schema doesn't exist in the staging database. The deploy action migrates a *_raw_staging database first as a dry-run, then the real *_raw database. Airbyte only writes to *_raw, never to *_raw_staging, so the schema it created (for example hubspot in adaction_raw) simply isn't present in adaction_raw_staging. The dry-run hits the missing schema and every GRANT in the changeset errors out before the real deploy ever runs. This is what caught us — not the source being unsynced. CREATE SCHEMA IF NOT EXISTS makes the dry-run pass by creating the schema in staging, and is a no-op against the real *_raw database where Airbyte already made it.

2. Whoever creates the schema owns it, and that has to be airbyte. Wherever CREATE SCHEMA IF NOT EXISTS actually fires (the staging database, and the real one if the source hasn't synced), the admin user ends up owning the schema. Airbyte then can't create or replace its tables there, and ALTER DEFAULT PRIVILEGES FOR USER airbyte silently grants nothing because airbyte isn't the object owner. ALTER SCHEMA <schema> OWNER TO airbyte hands ownership to airbyte so the sync works and the default privileges actually attach. It's a no-op where Airbyte already owns the schema, so it's safe either way.

3. Airbyte recreates its tables, so static grants decay — and you must not drop the schema. GRANT SELECT ON ALL TABLES only covers tables that exist at deploy time. Airbyte drops and recreates stream tables on schema changes and some sync modes, so the next sync produces tables the roles can't read. The ALTER DEFAULT PRIVILEGES FOR USER airbyte lines fix this: any table airbyte creates afterward is granted automatically. For the same reason the rollback is REVOKE-only — never DROP SCHEMA. The schema holds live source data owned by airbyte; rolling back a permissions change must never destroy it.

This is the same shape as the earlier datadog grant; the HubSpot rollout took two follow-up fixes (create-schema, then owner-transfer) on top of the initial grant before it deployed green, which is why all three steps are now the standard recipe.

Credentials

All secrets live in the Data Engineering 1Password vault and are loaded by the GitHub Actions workflows via the OP_DATA_SERVICE_ACCOUNT_TOKEN service account.

Secret	Used by	Warehouse user
WAREHOUSE_JDBC_ENDPOINT	both repos	n/a (endpoint URL)
WAREHOUSE_ADMIN_USER	data-warehouse	admin
WAREHOUSE_RAW_USER	airflow-dags (*_raw databases)	airbyte
WAREHOUSE_ANALYSIS_USER	airflow-dags (*_analysis databases)	airflow

Deployment

Both repos deploy on push to main:

• data-warehouse: .github/workflows/deploy-db.yml runs every changelog under migrations/ against its matching database.
• airflow-dags: .github/workflows/deploy.yml calls db-migration.yml once per database and selects the airbyte or airflow credentials based on the database name.

Order matters across repos: a federated external schema added in data-warehouse must be deployed before any airflow-dags materialized view that reads from it.

Related ADRs

• ADR 0005: Federated Queries in Redshift — why we use federated external schemas (defined in data-warehouse).
• ADR 0017: Federated Table Materialized Views — the prod_mirror.* matviews owned by airflow-dags.
• ADR 0032: Redshift Permissions Management Tooling — how roles and grants are modeled, all of which land in data-warehouse.

Quick decision flow

1. Does the change need admin privileges (new schema, new role, new grant, new federated external schema)? → data-warehouse.
2. Is it a table or materialized view? → airflow-dags, in the matching _raw or _analysis database.
3. Is it a data fix (INSERT/UPDATE/DELETE)? → airflow-dags, against an _analysis database only.