Authenticating...
Skip to main content

Federating a Postgres Table into Redshift Guide

This guide is the step-by-step procedure for exposing a table from an application's Postgres database in our Redshift warehouse, and therefore in Metabase, using the federated-mirror pattern. It is the operational companion to the decision records that explain why the pattern exists: see ADR 0005 and ADR 0017.

Use it when you want analysts to be able to query an app table (e.g. an AdGem, AdSuite, or Offer-API table) in Metabase or in downstream dbt models.

TL;DR​

Set the grants up once per source and you rarely touch them again. Do that and adding a table is three code-only steps. Skip it and every new table also needs two easily-forgotten manual grants, on opposite sides of the federation boundary, each of which fails in a confusing rather than obvious way.

#StepRepoRuns asNeeded when
0One-time grant setup for the sourcesource Postgres + data-warehousesource DB owner / adminonce, ideally at federation time
1Grant the federation read user SELECT on the source tablesource app's Postgresapp DB owneronly if Step 0's source half is missing
2Create the materialized view in <source>_mirrorairflow-dags (*_raw)airbytealways
3Re-grant reader/transformer on the mirror schemadata-warehouseadminonly if Step 0's Redshift half is missing
4Add the dbt model and wire the refresh DAGairflow-dagsn/aalways
5Deploy, then run a Metabase schema syncn/an/aalways

No source has Step 0 configured yet. For AdSuite, AdGem, and Offer-API today you still need Steps 1 and 3 on every new table. Doing Step 0 for a source is the highest-leverage change available here, and it is what would have prevented CAM-1207 outright.

If the source database has never been federated before (no <source>_prod external schema exists yet), you have a larger first-time setup in front of you. See First-time source setup below.

How the pattern works​

A source app DB is exposed to Redshift through a federated external schema (<source>_prod, e.g. adgem_prod, adsuite_prod, offer_api_prod). We do not let analysts query that live, because every query would hit the production database. Instead we materialize a copy into a mirror schema (<source>_mirror) in the adgem_raw Redshift database, refresh it on a schedule, and a dbt model copies it into the analyst-facing adgem_analysis schema that Metabase reads.

There are three copies of the data (production table, materialized view, analysis table). That is intentional and matches the established pattern -- see ADR 0017 for the rationale (minimize load on the source DB, give dbt something it can read and mock, and keep analysts out of the raw database).

Two repos own different layers​

This split is the single most important thing to internalize, because the two forgettable grants live on opposite sides of it. The full breakdown is in the Liquibase Usage Guide; the short version:

  • data-warehouse owns the warehouse's structure: the external schema, the mirror schema, and the role grants. Migrations here run as admin.
  • airflow-dags owns the data objects: the materialized views, the dbt models, and the refresh DAG. Migrations here run as airbyte (*_raw) or airflow (*_analysis).

Set this up once per source​

The per-table grants exist for exactly one reason: the grants we use today are point-in-time. GRANT ... ON ALL TABLES IN SCHEMA covers the tables that exist when it runs and nothing added afterwards. Configure each side of the boundary once and Steps 1 and 3 disappear for every future table on that source.

Do this for any new source, and it is worth backfilling onto existing ones.

Source Postgres -- default privileges for the migration role​

ALTER DEFAULT PRIVILEGES
  FOR ROLE <role_that_creates_the_app_tables>
  IN SCHEMA public
  GRANT SELECT ON TABLES TO warehouse_readonly;

FOR ROLE is the part that matters. Postgres applies default privileges only to objects created by the role named in FOR ROLE, defaulting to the role that runs the statement. Run this as an admin and omit the clause and it silently covers only that admin's own future tables, never the ones the app's migrations create. It then looks configured and still fails on the next table. Name the role that actually runs the app's migrations.

This half sits on the app's RDS, outside our repos, so it is applied by hand (see the Tinker path in Step 1). Micah owns the Postgres side.

Redshift -- scoped permissions on the mirror schema​

GRANT SELECT FOR TABLES IN SCHEMA <source>_mirror TO ROLE reader;
GRANT SELECT FOR TABLES IN SCHEMA <source>_mirror TO ROLE transformer;

Note FOR TABLES IN, not ON ALL TABLES IN. That is Redshift's scoped permissions form, and it applies to all current and future objects in the schema.

Prefer it over ALTER DEFAULT PRIVILEGES on the Redshift side. Redshift's default privileges are creator-scoped the same way Postgres's are -- they apply to "objects that are created in the future by the specified user" -- and the mirror MVs are created by airbyte while this migration runs as admin. A default-privileges statement would therefore have to name FOR USER airbyte to have any effect at all. Scoped permissions are creator-agnostic, which removes that failure mode instead of documenting around it.

Add it as a changeset in data-warehouse migrations/changelog-adgem_raw.yaml, alongside the existing mirror-schema grants. USAGE ON SCHEMA is still a separate, one-time grant.

Keep running Step 3 until you have seen this work once on a real source. AWS does not state whether materialized views count as tables for scoped FOR TABLES, and every mirror object is an MV, so confirm the first new table picked up reader without a re-grant:

SELECT relation_name, identity_name, privilege_type
FROM svv_relation_privileges
WHERE namespace_name = '<source>_mirror';

A redundant re-grant costs nothing; a missing one is a failed build.

The two per-table grants (when Step 0 is missing)​

Without the one-time setup above, a mirrored table needs two separate SELECT grants, on opposite sides of the federation boundary. Both are easy to miss because a point-in-time GRANT ... ON ALL TABLES IN SCHEMA only covers tables that existed when it ran -- it does not auto-cover tables added later.

#GrantWhere it livesSkip it and...
1GRANT SELECT ON <table> TO <federation_read_user> (e.g. warehouse_readonly)the source app's Postgres (not in any repo today)CREATE MATERIALIZED VIEW fails on deploy with permission denied for table <table>. The refresh DAG then fails every cycle because the MV was never created.
2GRANT SELECT ON ALL TABLES IN SCHEMA <source>_mirror TO ROLE reader (and transformer)data-warehouse changelog-adgem_raw.yamlthe dbt build fails with Relation <table> does not exist. Cross-database, Redshift hides an ungranted relation rather than reporting "permission denied."

The trap: copying an existing mirror entry looks like a code-only change, but it silently depends on both grants. Grant 1 is not codified anywhere, and Grant 2 must be re-run for the new table. This is exactly what turned CAM-1207 into a multi-day incident.

Steps​

Step 1 (source) -- grant the federation read user SELECT​

Skip this if the source has the one-time default privileges in place. No source does today.

Run this against the source app's Postgres, as a role that can grant on the table (the app's DB owner):

GRANT SELECT ON public.<table> TO <federation_read_user>;

The federation read user is whatever role backs the <source>_prod external schema's SECRET_ARN (for AdSuite/AdGem/Offer-API this is warehouse_readonly).

If you do not have direct RDS / Secrets Manager access, use Laravel Tinker over AWS SSM Session Manager -- the app instance can reach its own DB:

# SSM into the app instance, then:
cd /var/app/current
php artisan tinker
\DB::statement("GRANT SELECT ON public.<table> TO <federation_read_user>");

This is the same path Micah documented for creating a read-access role: Creating a Read Access Role in a Postgres Database Using Tinker. For an existing federation user you only need the GRANT above; the full runbook (including the password_encryption = 'md5' step Redshift federation requires) only applies when creating a new federation user.

Step 2 (airflow-dags) -- create the materialized view​

Add a changeset to migrations/changelog-adgem_raw.yaml:

- changeSet:
    id: <next>
    author: <you>
    labels: materialized_view
    comment: >
      create <source>_mirror.<table> materialized view (TICKET-123).
      Columns are listed explicitly: an MV freezes its column list at creation,
      so adding a column in the source later requires a recreate changeset.
    changes:
      - sql:
          sql: >
            CREATE MATERIALIZED VIEW <source>_mirror.<table> AS
            SELECT <col1>, <col2>, created_at, updated_at
            FROM <source>_prod.<table>
    rollback:
      - sql:
          sql: DROP MATERIALIZED VIEW IF EXISTS <source>_mirror.<table>

List columns explicitly. It makes the contract visible and signals that a future source column-add needs a recreate changeset. (You can SELECT * to match older entries, but it does not save you anything: an MV freezes its column list at creation either way.)

Step 3 (data-warehouse) -- re-grant the roles on the mirror schema​

Skip this if the mirror schema has scoped permissions in place and you have confirmed they cover MVs. No mirror schema does today.

Add a changeset to migrations/changelog-adgem_raw.yaml in data-warehouse. The grant is idempotent and re-running it picks up the new MV:

- changeSet:
    id: <next>
    author: <you>
    labels: permissions
    comment: re-grant reader and transformer on <source>_mirror after adding <table> (TICKET-123)
    changes:
      - sql:
          sql: GRANT USAGE ON SCHEMA <source>_mirror TO ROLE reader
      - sql:
          sql: GRANT SELECT ON ALL TABLES IN SCHEMA <source>_mirror TO ROLE reader
      - sql:
          sql: GRANT USAGE ON SCHEMA <source>_mirror TO ROLE transformer
      - sql:
          sql: GRANT SELECT ON ALL TABLES IN SCHEMA <source>_mirror TO ROLE transformer

dbt reads the mirror via the reader role. Grant transformer as well to stay consistent with the existing mirror schemas.

Step 4 (airflow-dags) -- add the dbt model and wire the DAG​

  • Model: dbt/<project>/models/<schema>/<table>.sql:

    {{ config(schema='<schema>', materialized='table') }}
    SELECT * FROM {{ source('<source>_mirror', '<table>') }}

  • Source: declare the mirror table in source.yml.

  • Docs: document the model in schema.yml (description, meta.owners, tags) so Metabase and DataHub pick up descriptions.

  • DAG: add the table to the refresh DAG's tables = [...] list and to the dbt --select command. Example from adsuite_to_ag_dag.py:

    tables = ["iterations", "strategies", "apps", "app_kpi_targets"]
    connector.refresh_materialized_views("adsuite_mirror", tables)
    # ...
    command="run --select iterations --select strategies --select adsuite_apps --select app_kpi_targets",

  • Watch for dbt model-name collisions. Model resource names are globally unique across the dbt project, so if the obvious name is taken (e.g. apps already exists as AdGem's catalog model) the file cannot just be <table>.sql. Either way the file is named <source>_<table>.sql; the choice is what the physical table ends up called. Two options, and they trade off differently.

    Option A: alias back to the clean name. Keep the schema as the disambiguator, so the physical table is <schema>.<table>.

    {{ config(schema='<schema>', alias='<table>', materialized='table') }}
    SELECT * FROM {{ source('<source>_mirror', '<table>') }}

    Aliasing is safe: the dbt-to-Metabase doc sync keys on the physical relation (the alias), not the resource name, so descriptions still attach. This is what the existing models do. See adsuite_apps -- resource adsuite_apps, alias='apps', materialized as the clean adsuite.apps, alongside adsuite_campaigns and adsuite_users.

    Option B: rename the table to something unambiguous. Drop the alias and give the physical table a distinct name that says what the entity is.

    {{ config(schema='<schema>', alias='advertiser_apps', materialized='table') }}
    SELECT * FROM {{ source('<source>_mirror', 'apps') }}

    The case for this: schema-level disambiguation only helps someone who is reading the schema. AdGem apps is a publisher entity and AdSuite apps is an advertiser entity, and in Metabase's table picker both surface as "Apps" with nothing to tell them apart. A distinct physical name is self-describing wherever it appears. If you take this option, document both tables in DataHub so the distinction is written down somewhere other than the name.

    Option A keeps the naming consistent with what is already built; Option B is clearer at the point of discovery. If there is no collision at all, skip the alias and name the file <table> directly.

Step 5 -- deploy, then refresh Metabase​

Both repos auto-deploy their Liquibase on merge to main (when the changelog changed). Order matters: deploy the airflow-dags MV (Step 2) first, then the data-warehouse grant (Step 3). The grant is a point-in-time GRANT ... ON ALL TABLES IN SCHEMA, so it only covers the new MV if the MV already exists when it runs -- deploy it before the MV and it silently misses the new table, and Liquibase will not re-run the (already-applied) changeset. Between the two deploys there is a short window where the refresh DAG's dbt step cannot read the new MV; it self-heals once the grant lands, or pause the DAG across the two deploys. (Note this is the opposite of Grant 1, the source grant, which must be in place before the MV deploys. This whole ordering constraint is an artifact of the per-table grant: with scoped permissions on the mirror schema there is no Step 3 to order and no gap to bridge.) After the DAG builds the analysis table, run a Metabase schema sync on adgem_analysis so the new table appears in the query builder; the daily dbt-to-Metabase doc sync then attaches descriptions.

Verify​

  • Source grant landed -- the CREATE MATERIALIZED VIEW deploy succeeded (no permission denied), and the DAG's refresh_materialized_views task is green.
  • Mirror MV is readable by reader (Metabase database "Adgem Raw"): 
    SELECT relation_name, identity_name, privilege_type
    FROM svv_relation_privileges
    WHERE namespace_name = '<source>_mirror';
    The new table should appear with reader / SELECT alongside the others.
  • Built and populated (Metabase database "Adgem Analysis"): 
    SELECT count(*) FROM <schema>.<table>;
  • DAG run is green end to end (both refresh_materialized_views and the dbt task).

Troubleshooting​

SymptomCauseFix
Deploy fails: permission denied for table <table> on CREATE MATERIALIZED VIEWGrant 1 (source) missingRun Step 1, then re-run the failed deploy so the changeset applies.
dbt build fails: Relation <table> does not exist (and the relation clearly exists)Grant 2 (reader on <source>_mirror) missingRun Step 3. Cross-database, Redshift reports a missing grant as "does not exist."
The whole adgem_raw Liquibase pipeline is stuck, blocking unrelated migrationsAn earlier changeset failed (usually Grant 1) and retries every deployFix the underlying grant, then re-deploy. A never-applied changeset can be deleted (no databasechangelog record to roll back); an applied one must be reversed with a new changeset.
refresh_materialized_views fails: Materialized view <table> not foundThe MV create failed earlier (see Grant 1) so the MV does not existFix Grant 1 and let the create changeset apply before the next refresh.
Refresh fails after a source schema change: column ... does not existThe source table's columns changed; the MV's column list is frozen at creationAdd a recreate changeset (DROP + CREATE) in airflow-dags, then re-grant in data-warehouse (see CAM-1043).
Table is built but not visible in MetabaseMetabase has not synced its schemaRun a Metabase schema sync on adgem_analysis.

Gotchas​

  • Two grants, two repos. The number-one cause of "I followed the pattern but it doesn't work." Source grant on the app's RDS; role grant in data-warehouse. The durable fix is the one-time setup, not remembering harder.
  • Point-in-time grants. GRANT ... ON ALL TABLES IN SCHEMA does not cover future tables, so without Step 0 each new table needs its own re-grant. This is the root cause of every grant-related failure in this guide.
  • Creator-scoped default privileges. If you do reach for ALTER DEFAULT PRIVILEGES on either side, it only affects objects created by the role it names (the current role if unnamed). Getting that wrong is worse than not doing it, because the setup looks complete and still fails. See Step 0.
  • Cross-database "does not exist". When dbt reads adgem_raw.<source>_mirror.<table> from adgem_analysis, a missing grant surfaces as "relation does not exist," not "permission denied."
  • MV column list is frozen at creation. A source column add/drop/resize needs a DROP+CREATE recreate changeset, not just a refresh.
  • mv_tbl__ faux tables. Redshift backs each MV with a hidden mv_tbl__-prefixed table. Never query those directly; query the analysis table in adgem_analysis.

First-time source setup​

Everything above assumes the source already has a <source>_prod external schema. Bringing a brand-new source database into the warehouse is a larger, mostly one-time job and touches CDK and Secrets Manager:

  1. Read-only DB user on the source Postgres (warehouse_readonly or equivalent), with password_encryption = 'md5' (Redshift federation requires MD5). See Micah's Tinker read-access runbook.
  2. Secret + IAM (data-warehouse CDK): a prod/<Source>DB/warehouse_readonly secret, plus the source RDS KMS key and the secret ARN on the warehouse IAM policy.
  3. External schema (data-warehouse Liquibase): CREATE EXTERNAL SCHEMA IF NOT EXISTS <source>_prod FROM POSTGRES DATABASE '<db>' SCHEMA 'public' IAM_ROLE default SECRET_ARN '<arn>' URI '<rds-endpoint>'.
  4. Mirror schema (data-warehouse Liquibase): CREATE SCHEMA IF NOT EXISTS <source>_mirror plus the reader/transformer grants.
  5. The one-time grant setup -- do Step 0 here, while you are already in both sides. This is the cheapest moment to do it and the reason the per-table grants keep biting us is that it was skipped on every existing source.

After that, adding each table is Steps 2, 4, and 5 above.

Worked examples​

  • CAM-1207 -- mirrored AdSuite apps + app_kpi_targets. Both grants were missing and were discovered one outage at a time; the canonical cautionary tale for this guide.
  • CAM-1043 -- recreate prod_mirror.apps after a source column was dropped (the frozen-column-list gotcha).
  • AGPI-1637 / AGPI-1742 -- straightforward additions (failed_webhooks, offer_metrics) that show the happy-path shape of the change.