A data warehouse is a centralized repository that stores, consolidates, and retrieves large volumes of structured data from multiple sources. It supports coherent analysis across an organization by organizing related information for querying and decision-making. section 11.2.2 section 11.5
A warehouse commonly organizes information into:
- Databases, which contain organized collections of data. section 11.2.2
- Schemas, which group tables by topic or analytical purpose. section 1.2.2 section 11.2.2
- Tables, which represent records in rows and attributes in columns. section 1.2.2
- Views, which provide curated query results assembled from one or more underlying tables. section 1.2.2
- Layered data, which can preserve source-like structures before combining information for analytical needs. section 11.2.2
Analysts usually retrieve warehouse data with SQL or a warehouse-specific SQL dialect. SQL knowledge transfers across organizations, although functions such as date manipulation can differ between warehouse technologies. section 1.2.2
SELECT #1
state,
SUM(population) AS total_population
FROM city_populations
GROUP BY state
city_populations %>% #2
group_by(state) %>%
summarise(total_population = sum(population))
city_populations.groupby('state')['population'].sum() #3
The same grouped population calculation appears in SQL, R, and Python, illustrating related ways to aggregate warehouse-style tabular data. section 1.2.2
Choosing an infrastructure
A storage proposal can compare Snowflake, MongoDB, Redshift, BigQuery, and Azure by cost, scalability, ease of use, data types, and anticipated analytical needs. The recommendation can also assess whether a data lake is appropriate alongside a warehouse. section 11.2.4 section 11.2.2
