What is the difference between a database and a data warehouse?

Question

What is the difference between a database and a data warehouse?

Aren't they the same thing, or at least written in the same thing (ie. Oracle RDBMS)?

Ritualism

Answer 1

Check out this for more information.

From a previous link:

Database

1. Used for Online Transactional Processing (OLTP) but can be used for other purposes such as Data Warehousing. This records the data from the user for history.
2. The tables and joins are complex since they are normalized (for RDMS). This is done to reduce redundant data and to save storage space.
3. Entity – Relational modeling techniques are used for RDMS database design.
4. Optimized for write operation.
5. Performance is low for analysis queries.

Data Warehouse

1. Used for Online Analytical Processing (OLAP). This reads the historical data for the Users for business decisions.
2. The Tables and joins are simple since they are de-normalized. This is done to reduce the response time for analytical queries.
3. Data – Modeling techniques are used for the Data Warehouse design.
4. Optimized for read operations.
5. High performance for analytical queries.
6. Is usually a Database.

It's important to note as well that Data Warehouses could be sourced from zero to many databases.

Phonetic

Data modeling is a generic term and does not only apply to data warehouses. (Perhaps in the second #3 they meant "dimensional modeling" as that is, in my experience, the most popular way to build them.) The last line doesn't make sense: how does a data warehouse "contain" a database? I might say a data warehouse is sourced from 0 to many (OLTP) databases. – Gadgeteer

@Mark - Where does it say databases are ONLY for OLTP? It is showing how databases relate to data warehouses. – Phonetic

@TheCloudlessSky: Point 1 of Database from your answer: "Used for Online Transactional Processing (OLTP)." No explanation that Databases can be used for other purposes; no explanation that data warehouses normally are databases; contrast between database and data warehouse sections implies that they are two different things, whereas normally the latter is a subset of the former. – Ki

This answer confuses "database" (a generic term) and an "OLTP database". The bullets for the "database" actually only apply to OLTP databases (before the edit that mentioned DW databases). – Alburg

@Alburg - This is a two year old answer. Edit it and fix it if you're concerned. – Phonetic

@DataMan - Mark this as correct answer. its a pretty good and fair response for your question. – Esbenshade

what is the difference between Data – Modeling techniques and Entity – Relational modeling techniques ? – Sigismundo

Answer 2

From a Non-Technical View: A database is constrained to a particular applications or set of applications.

A data warehouse is an enterprise level data repository. It's going to contain data from all/many segments of the business. It's going to share this information to provide a global picture of the business. It is also critical to integration between the different segments of the business.

From a Technical view: The word "Data Warehouse" has been given no recognized definition. Personally, I define a data warehouse as a collection of data-marts. Where each data-mart consists of one or more databases where the database is specific to a specific problem set (application, data-set or process).

Simply put a database is a component of a data-warehouse. There are many places to explore this concept, but because there is no "definition", you will find challenges with any answer you give.

Luminal

The Datamart is preferably an OLTP database? – Turfy

@CᴴᴀZ for what ? Datamart are created for the OLAP , Datamart is bassically a small DWH (for a small part of the business) . it is created for Analysis so if you need a OLTP System a Datamart is not your choice , if you need a Data-Model for Analysis (Start Schema or Snow flow) then you would prefer a Datamart. – Cissiee

Hey @Enrique, got your point: Datamart contributes to the OLAP. I took the cue of Datamarts being OLTP from here - SQL Server OLTP Datamart. – Turfy

Hi @CᴴᴀZ , I read the article and it would be just how to make a OLTP with a Datamart. ok . (but I think they said it like exception) Take acount that how they say over, OLTP Tables they have to be normalized in order not to have inconsistences . the normalization is not so complicated to do in a E-R schema but it´s much more complicate for Star-Schema or Snow-Flow thats. These Schemas are made to ease a read in the Database and not a transactional operations. Thats why using a Datamart like OLTP should be no t a good idea even if is posible. – Cissiee

Answer 3

A data warehouse is a TYPE of database.

In addition to what folks have already said, data warehouses tend to be OLAP, with indexes, etc. tuned for reading, not writing, and the data is de-normalized / transformed into forms that are easier to read & analyze.

Some folks have said "databases" are the same as OLTP -- this isn't true. OLTP, again, is a TYPE of database.

Other types of "databases": Text files, XML, Excel, CSV..., Flat Files :-)

Becky

This is the correct answer of them all. And a bit on Datamart: It is a logical subset of Data warehouse, generally based upon business functions. – Turfy

Answer 4

The simplest way to explain it would be to say that a data warehouse consists of more than just a database. A database is an collection of data organized in some way, but a data warehouse is organized specifically to "facilitate reporting and analysis". This however is not the entire story as data warehousing also contains "the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system".

Data Warehouse

Frizzly

Answer 5

Data Warehouse vs Database: A data warehouse is specially designed for data analytics, which involves reading large amounts of data to understand relationships and trends across the data. A database is used to capture and store data, such as recording details of a transaction.

Data Warehouse: Suitable workloads - Analytics, reporting, big data. Data source - Data collected and normalized from many sources. Data capture - Bulk write operations typically on a predetermined batch schedule. Data normalization - Denormalized schemas, such as the Star schema or Snowflake schema. Data storage - Optimized for simplicity of access and high-speed query. performance using columnar storage. Data access - Optimized to minimize I/O and maximize data throughput.

Transactional Database: Suitable workloads - Transaction processing. Data source - Data captured as-is from a single source, such as a transactional system. Data capture - Optimized for continuous write operations as new data is available to maximize transaction throughput. Data normalization - Highly normalized, static schemas. Data storage - Optimized for high throughout write operations to a single row-oriented physical block. Data access - High volumes of small read operations.

Ethiopian

Answer 6

DataBase :- OLTP(online transaction process)

• It is current data, up-to-date detailed data, flat relational isolated data.
• Entity relationship is used to design the database
• DB size 100MB-GB simple transaction or quires

Datawarehouse

• OLAP(Online Analytical process)
• It is about Historical data Star schema,snow flexed schema and galaxy
• schema is used to design the data warehouse
• DB size 100GB-TB Improved query performance foundation for DATA MINING DATA VISUALIZATION
• Enables users to gain a deeper understanding and knowledge about various aspects of their corporate data through fast, consistent, interactive access to a wide variety of possible views of the data

Theriault

Answer 7

Any data storage for application generally uses the database. It could be relational database or no sql databases which are currently trending.

Data warehouse is also database. We can call data warehouse database as specialized data storage for the analytical reporting purposes for the company. This data used for key business decision.

The organized data helps is reporting and taking business decision effectively.

Aristotelianism

Answer 8

Database:

Used for Online Transactional Processing (OLTP).

• Transaction-oriented.
• Application oriented.
• Current data.
• Detailed data.
• Scalable data.
• Many Users, Administrators / Operational.
• Execution time: short.

Data Warehouse:

Used for Online Analytical Processing (OLAP).

• Oriented analysis.
• Subject oriented.
• Historical data.
• Aggregated data.
• Static data.
• Not many users, manager.
• Execution time: long.

Gabriel

Answer 9

A Data Warehousing (DW) is process for collecting and managing data from varied sources to provide meaningful business insights. A Data warehouse is typically used to connect and analyze business data from heterogeneous sources. The data warehouse is the core of the BI system which is built for data analysis and reporting.

Comitia

Answer 10

Source for the Data warehouse can be cluster of Databases, because databases are used for Online Transaction process like keeping the current records..but in Data warehouse it stores historical data which are for Online analytical process.

Grogram

Answer 11

A Data Warehouse is a type of Data Structure usually housed on a Database. The Data Warehouse refers the the data model and what type of data is stored there - data that is modeled (data model) to server an analytical purpose.

A Database can be classified as any structure that houses data. Traditionally that would be an RDBMS like Oracle, SQL Server, or MySQL. However a Database can also be a NoSQL Database like Apache Cassandra, or an columnar MPP like AWS RedShift.

You see a database is simply a place to store data; a data warehouse is a specific way to store data and serves a specific purpose, which is to serve analytical queries.

OLTP vs OLAP does not tell you the difference between a DW and a Database, both OLTP and OLAP reside on databases. They just store data in a different fashion (different data model methodologies) and serve different purposes (OLTP - record transactions, optimized for updates; OLAP - analyze information, optimized for reads).

Vasomotor

Answer 12

See in simple words : Dataware --> Huge data using for Analytical/storage/ copy and Analysis . Database --> CRUD operation with Frequently used data .

Dataware house is Kind of storage which u are not using on daily basis & Database is something which your dealing frequently .

Eg. If we are asking statement of bank then it gives us for last 3/4/6/more months bcoz it is in database. If you want more than that it stores on Dataware house.

Romy

Answer 13

Example: A house is worth $100,000, and it is appreciating at $1000 per year.

To keep track of the current house value, you would use a database as the value would change every year.

Three years later, you would be able to see the value of the house which is $103,000.

To keep track of the historical house value, you would use a data warehouse as the value of the house should be

$100,000 on year 0, 
$101,000 on year 1, 
$102,000 on year 2, 
$103,000 on year 3. 

Shull