3 ms·

Sounds like essentially consolidating data in a database and visualizing it. Is there a difference between a database and a "data warehouse"?

pnut 4y ago
Yes, they have fundamentally different architectures to serve their respective use cases.
- CyberDildonics 4y ago
  What is the different architecture and what is the different use case? Databases are already general tools. You put data in and query it. At what point does it become a "data warehouse" ?
  - NortySpock 4y ago
    People argue over the definition of a data warehouse, but usually a data warehouse (1) optimized for bulk inserts and bulk read operations, not updates or deletes, nor for single row updates (2) stores data from multiple sources (3) often a "columnstore" columnar table format is used. This format usually compresses columns in a format that is trivial to decompress (so if a column only contains a handful of similar flags, the database will run-length-encode them to optimize a full column scan), and additionally if you limit the number of columns accessed you can substantially reduce the cost of the query (4) the data warehouse can easily shard or partition data If you want to technical file format examples, I suggest looking up how the Parquet file format compresses data, or how Microsoft SQL Server columnstore tables work under-the-hood.
  - wintogreen74 4y ago
    they're both general terms, but serve different purposes. Data Warehouses tend to focus on read-specific operations, more of an emphasis on historical and/or analytical problems than transactional ones. Subsequently they are likely to optimize data ingress, distributed queries and downplay referential integrity and consistency. The different workloads promote different compute models, but you're right that the line is blurry. Similarly you could ask "Excel has multiple tables (sheets) that you can join and query; is it a database"?
aidenn0 4y ago
A data warehouse is a specific use-case for a database. > Sounds like essentially consolidating data in a database and visualizing it Yes, and the database in which you consolidate the data is called a "data warehouse" In many cases the sources of data are ... not optimal for querying. (And I mean like "Spread across a dozen excel spreadsheets that people e-mail out new versions of when they make a change" by "not optimal")
- CyberDildonics 4y ago
  Consolidating data makes sense and taking it from sources you can't query makes sense, but once you put it into a source you can query, that sounds like a database. I'm not sure why there is a different term here.
  - adamc 4y ago
    The database is often arranged and optimized differently than one used to support operations, and a lot of things are often done to make what would otherwise be long-running, slow queries faster -- e.g., aggregating or otherwise summarizing data likely to be queried in reports. But sure, it's still backed by a database.
  - aidenn0 4y ago
    We have multiple names at different levels of specificity for things all the time. I have a laptop and a desktop, but they are both "computers"

pnut 4y ago

Yes, they have fundamentally different architectures to serve their respective use cases.

CyberDildonics 4y ago
What is the different architecture and what is the different use case? Databases are already general tools. You put data in and query it. At what point does it become a "data warehouse" ?
- NortySpock 4y ago
  People argue over the definition of a data warehouse, but usually a data warehouse (1) optimized for bulk inserts and bulk read operations, not updates or deletes, nor for single row updates (2) stores data from multiple sources (3) often a "columnstore" columnar table format is used. This format usually compresses columns in a format that is trivial to decompress (so if a column only contains a handful of similar flags, the database will run-length-encode them to optimize a full column scan), and additionally if you limit the number of columns accessed you can substantially reduce the cost of the query (4) the data warehouse can easily shard or partition data If you want to technical file format examples, I suggest looking up how the Parquet file format compresses data, or how Microsoft SQL Server columnstore tables work under-the-hood.
- wintogreen74 4y ago
  they're both general terms, but serve different purposes. Data Warehouses tend to focus on read-specific operations, more of an emphasis on historical and/or analytical problems than transactional ones. Subsequently they are likely to optimize data ingress, distributed queries and downplay referential integrity and consistency. The different workloads promote different compute models, but you're right that the line is blurry. Similarly you could ask "Excel has multiple tables (sheets) that you can join and query; is it a database"?

aidenn0 4y ago

A data warehouse is a specific use-case for a database. > Sounds like essentially consolidating data in a database and visualizing it Yes, and the database in which you consolidate the data is called a "data warehouse" In many cases the sources of data are ... not optimal for querying. (And I mean like "Spread across a dozen excel spreadsheets that people e-mail out new versions of when they make a change" by "not optimal")

CyberDildonics 4y ago
Consolidating data makes sense and taking it from sources you can't query makes sense, but once you put it into a source you can query, that sounds like a database. I'm not sure why there is a different term here.
- adamc 4y ago
  The database is often arranged and optimized differently than one used to support operations, and a lot of things are often done to make what would otherwise be long-running, slow queries faster -- e.g., aggregating or otherwise summarizing data likely to be queried in reports. But sure, it's still backed by a database.
- aidenn0 4y ago
  We have multiple names at different levels of specificity for things all the time. I have a laptop and a desktop, but they are both "computers"