ETL: Postgres Foreign Tables

[soulia]

Hi -

This topic pertains to using Postgres foreign tables to assist the ETL process. Postgres has a nice feature for referencing tables in another database - foreign tables. In this case, I'm using the foreign table feature to reference my OLTP tables from within my data warehouse for ETL. The OLTP tables have the usual FKs and PKs. When I create the OLAP (data warehouse) foreign tables that link to the OLTP tables, I can also add FKs and PKs to these foreign tables. My question is a "best practice" question. As usual, the OLTP tables have complex FKs and PKs. Of course, I don't want to duplicate these in the data warehouse. For example, an OLTP "customer" table has about 20 FKs. My customer dimension, built off a foreign table that references the external customer table, has none - only a PK - which I think is one of the goals of the data warehouse. What design considerations should I be aware of in the ETL process? Can I just ETL away on the foreign tables assuming that the underlying data is just fine and not worry about how the foreign tables are keyed (since they are linked to "good" data)? Duplicating the FKs and PKs in the foreign tables doesn't make sense: dual maintenance, etc. Try to explain this in such a way that I may convince my DBA that this is a good practice.

Thanks!

[nick_white]

Hi - my understanding is that Foreign tables have no storage, they are basically just Views on to data in another DB. I'm assuming you are talking about adding FKs to just the foreign tables in which case I can't see the point. The data is read-only so this wont impose any referential integrity that doesn't already exist in the actual source tables - and if you created an FK in the Foreign table that didn't exist in the real table and the data broke that relationship what would you expect to be able to do about it?

If you are talking about about creating FKs between Foreign tables and the data warehouse then that sounds like a really bad idea. A DW should only have the indexes necessary for loading data and query performance purposes - enforcing a relationship back to the source is probably not possible and even if it was it would be a bad idea. I assume this is not what you are suggesting but if it is, what is the purpose/benefit you see from doing it?

If you are trying to explain this to your DBA I would make two points:
1. Foreign tables are like Views, and you would never create FKs on Views - you'd always use the FKs on the underlying tables
2. A dimensional model is fundamentally different to a 3NF transactional model. If your DBA is trying to impose 3NF database design standards on a dimensional model then they need to go away and educate themselves on dimensional modelling

Hope this is some help?

[soulia]

Thanks, Nick. Excellent response. I just wanted to make sure I wasn't missing something. I'm using the foreign tables for my ETL process, extracting the customer and address tables from the OLTP into a customer dimension, for example. The customer and address are the read-only foreign tables. I wasn't planning on creating any FKs between the foreign tables and the DW.

Now, on to trying to explain the date dimension to the DBAs ...

[Sponsored content]