Beyond Foreign Data Wrappers: building a data warehouse from heterogeneous external data sources
summary
A practical recipe for building a Postgres-backed warehousing proxy that tests and loads heterogeneous third‑party sources: a dummy FDW handler lets CI validate schemas and view/type casts without real data; a generate_foreign_tables tool (Postgres/MySQL/SQL Server via Ruby) scaffolds foreign tables and diffs schema changes; materialized views mirror foreign tables to avoid repeated network pulls, with dependency-aware parallel refreshes (≈280 foreign tables, full refresh ~1–2 hours on a 6–8 hour cadence); and PGTap unit tests verify structure and query results.
AI-written summary · about
chapters (11)
- 0:00Intro
- 3:45Demo
- 10:15The magic code
- 12:40Running the code
- 19:00Data types
- 25:05Finding errors faster
- 31:20About type theory
- 33:55Generate foreign tables
- 38:45Unit tests
- 44:22Multi nested structure
- 47:26lard tables
related talks
46mWriting a Foreign Data Wrapper
34mJoining Heterogeneous Databases is a reality, not a Myth
51mUsing Postgres FDW with a sharded PostgreSQL Database
54mFederating Queries Using postgres_fdw
52mSharding PostgreSQL using Foreign Data Wrappers
39mPostgreSQL: Joining Heterogeneous Databases is a reality, not a Myth
25mRuby and PostgreSQL, a love story
48mData Horizons With Postgres