A data warehouse is a database built for analysis rather than for running transactions. It holds integrated, historical data from many sources in structured tables, loaded in batches and modelled for reporting, so that large queries across subjects and years return consistent answers.
How a warehouse is organised
The classic description gives a warehouse four properties: it is subject-oriented, integrated across sources, time-variant and non-volatile, meaning that loaded history is kept and not overwritten. Many are modelled as fact tables and dimension tables. A fact table records events or measures at a stated grain, such as one row per company per day. A dimension table holds the descriptive attributes of the thing counted, keyed so that facts can join to it.
Raw data lands first in a staging area and is then modelled into these tables. Many cloud warehouses keep storage apart from the compute that queries it, and a data lakehouse goes further by keeping open files in object storage.
Loading vendor data
Files and API output arrive as the vendor made them and need a model. The steps are:
- Land the files unchanged, with their manifest.
- Load them into raw tables, one per source table.
- State the grain of each table and key it, then test the key.
- Parse JSON cells into columns or a native JSON type.
- Build a company dimension from the identifier columns and join facts to it.
Pipelines that keep a copy current are covered in the use case on keeping a warehouse in step with incremental API sync.
In Fokals data
Fokals is delivered direct, by REST API and as bulk files, which you load into a warehouse with its own loader. Every file carries the company ID and company name and, when the company or its parent is listed, the ticker, exchange, MIC, ISIN, LEI and FIGI. Datasets therefore join to each other on the company ID, and to market data on ISIN, FIGI or ticker with MIC. The data dictionary gives the grain of each table, such as Hiring Activity per company and closed UTC day. Daily and weekly rows are written once and never revised, so a load of them can append without tracking changes to old rows.
Related terms
- Data lakehouse: open files in object storage with a table layer on top.
- Bulk export: how vendor data usually arrives for a warehouse load.
- Data sharing: read access to a provider's tables in place, without a copy.
- Company identifier: the keys on which vendor data joins to your own tables.
Frequently asked questions
What is the difference between a data warehouse and a database?
A database is any organised store. A transactional database is tuned for many small reads and writes, such as recording an order. A data warehouse is tuned for analysis: fewer, larger queries over history gathered from many sources. Teams usually keep the two separate so that analysis does not slow the systems that record the business.
What is the difference between a data warehouse and a data lake?
A warehouse stores structured, modelled tables and enforces a schema when data is loaded. A data lake stores raw files of any shape in low-cost object storage and applies structure when the data is read. A lakehouse tries to give the lake's files the table guarantees of a warehouse.
How do I load vendor files into a data warehouse?
Land the files in a staging area, load them unchanged into raw tables with the manifest alongside, then model them: set the grain, key each table, parse JSON cells and join on the vendor's company identifier. Load closed periods once and write with upserts keyed on the record, so that a re-run changes nothing.