Beyond Foreign Data Wrappers: building a data warehouse from heterogeneous external data sources
Time: 14:30 - 15:20
Room: Market Street
More and more companies, especially startups, tend to use external data providers for their business. While this approach reduces costs, it creates challenges for data management, reporting, and query performance.
Postgres FDWs allow us to build a framework to address the challenges of data consolidation simpler. However mapping hundreds of tables from the source system can become a daunting task.
In this talk we introduce a method for managing such third party data sources with FDWs including a program to create the DDL for all foreign tables for external data sources and code to manage FDWs in both production and testing environments and handling the operational issues without code DDL changes. Also we will show how the use of standard software engineering testing techniques can be utilized in these situations.
We also describe the use cases for these tools and techniques, based on the data warehouses built at several startups over the last several years.