Your question is very good: the Kimball mantra for dimensional modelling is to improve performance and to improve usability. But I don’t think it is outdated, or dogma- it is a reasonable, practical approach for many situations and platforms. The way relational DBs store data means there’s a balancing act to be struck between the … Read more
staging tables are just database tables containing your business data in some form or other. Staging is the process of preparing your business data, usually taken from some business application. For your average BI system you have to prepare the data before loading it. A staging table is essentially just a temporary table containing the … Read more
Use the serial type for automatically incrementing unique ids. If you plan to have more than two billion entries, use bigserial. serial is the PostgresSQL equivalent of MySQL’s AUTO_INCREMENT. PostgresSQL Documentation: Numeric Types
From our experience, data provenance includes only high level view of the system for business users, so they can roughly navigate where their data come from. It’s provided by variety of modeling tools or just simple custom tables and charts. Data lineage is a more specific term and includes two sides – business (data) lineage … Read more
The Open Source Data Warehousing does a great job at identifying OSS components that could be used to build a Data Warehouse stack: Infrastructure (servers, OS, databases), Integration Management (ETL, EAI, etc), Information Management (DW/Mart/ODS, OLap Servers, etc), Information Delivery (Portal, Dashboard, Analytics/OLAP Client, etc). Here is a summary: Open Source BI/DW Projects BI and … Read more
I’ve used PostgreSQL in an environment where we’re seeing 100K-2M new rows per day, most added to a single table. However, those rows tend to be reduced to samples and then deleted within a few days, so I can’t speak about long-term performance with more than ~100M rows. I’ve found that insert performance is quite … Read more
Data Warehouses have very little in common with NoSQL – the main similarity is that any two data warehouses can have very different philosopohies or conventions just like any two NoSQL systems can be nearly unrelated. The only concept they share is that they are both used to analyze large amounts of data. NoSQL solutions … Read more
Schema evolution can be (very) expensive. In order to figure out schema, you basically have to read all of your parquet files and reconcile/merge their schemas during reading time which can be expensive depending on how many files or/and how many columns in there in the dataset. Thus, since Spark 1.5, they switched off schema … Read more
I’ll see if I can do my best to answer your questions succinctly. 1.At what point is building a data warehouse an option worth considering? In other words, what telltale signs, metrics, or other criteria should I be looking out for that might indicate that a standard transactional environment is no longer sufficient? a. If … Read more
Using star schemas for a data warehouse system gets you several benefits and in most cases it is appropriate to use them for the top layer. You may also have an operational data store (ODS) – a normalised structure that holds ‘current state’ and facilitates operations such as data conformation. However there are reasonable situations … Read more