Can anyone tell me what the implications are when attempting to use a regular database as a data warehouse?
I understand a data warehouse is known for storing data in a more structured manner however what's the implication of using a standard database to achieve the same result? Can we not just create a regular database table with structured data as it would reside in a data warehouse?
Data structure is not the issue - optimization is.
OLTP databases like SQLS are optimized to reliably record transactions. They store data as records, and extensively use disk I/O.
BI databases like Redshift or Teradata are optimized to query data. They store data as columns, and often are in-memory only (no disk I/O).
As a result, traditional databases are better at getting data in, while BI databases are better at getting data out (both platforms are trying to mitigate their weaknesses, so the difference is blurring).
Practically speaking, you can use regular databases like SQLS to build a data warehouse without any problems, unless your needs are special:
You can also combine both platforms. Import, process, integrate and store data in a regular database, and then convert it into a star schema (dimensional model) and publish it to a BI database (i.e, keep normalized data in SQLS and publish star schema to Redshift).
If you intend to import data into BI tools like Tableau or PowerBI, then you can safely use any traditional database, because they rely on their internal engines and using BI database won't give you any advantages.
data warehouses also will have redundant or duplicate data in them, not really what you are looking for in a regular database
