3

The purpose of the data warehouse is to serve as a single source of truth1 for your BI consumers. It provides you with the central place where the data and metadata for your reports and dashboards originate. If you deprive it of this role and create a multitude of data marts populated by your operational data (via staging tables or not), inevitably each of ...


2

Ok, in the Table A/Table B scenario you outline, we have three possibilities: Table A and Table B update/insert at the same time - existing logic works Table A XOR Table B updates - can locate record based on ColA, update only the records for the table received. Table A XOR Table B inserts - hold the record in staging until the other record arrives. Flag ...


2

A Postgres server is a lot of things, not just row-oriented access methods: (The image above is from the Postgres documentation.) Postgres source code is available for anyone to use under a very permissive license. To implement a DBMS that is "based on PostgreSQL" you don't have to rewrite it from scratch. Say, if you want to introduce a different ...


2

For schema comparisons, pgadmin4 has a Schema Diff feature (in beta as of 26 Jun 2020). https://www.pgadmin.org/


1

For future reference; the final conclusion for me as a result of chatting with bbaird. This scenario is very likely when you have an architecture like the 'future' architecture described here and the data model is not correct or there is an issue with the source data. The problem for us is that we do not have any data quality checking mechanisms in place ...


1

If you have an attribute with the same granularity of the Fact table (i.e.: "TransactionID") and it is usually used to filter one single Fact table, you don't have to create a separate table (aka Shared Dimension) for it. Instead, this attribute can live in the fact table itself. This is know as Degenerate Dimension You can find more about it here: ...


1

The problem with contrived examples like the one you provide is that whatever answer you find, it won't be helpful in a real life situation. "A number of distinct customers" is useless as a measure, because it doesn't represent anything a business would care about. Now, there could be more useful measures, such as "a number of distinct active customers per ...


1

As with other kinds of indexes, Projection Indexes are there to speed up certain specific queries and operations, (usually at the expense of slower inserts and updates but since this is for data warehousing, it shouldn't be much of a deal). Grouping together all the values of a single column speeds up aggregate operations involving that column. For example, ...


Only top voted, non community-wiki answers of a minimum length are eligible