Databases Intermediate

What is Data Warehouse?

A centralized repository optimized for analytical queries that integrates data from multiple operational sources.

Data warehouses are optimized for read-heavy analytical workloads (OLAP) rather than transactional operations (OLTP). They use columnar storage, star/snowflake schemas, and materialized views for fast aggregation queries.

Popular solutions include Snowflake, BigQuery (Google), Redshift (AWS), and Azure Synapse. Data warehouses typically receive data via ETL/ELT processes and serve business intelligence tools, dashboards, and ad-hoc analysis.

Related Terms

CTE (Common Table Expression)

A temporary named result set defined within a SQL statement using the WITH clause, improving query readability and enabling recursion.

A PostgreSQL maintenance operation that reclaims storage from dead tuples and updates statistics for the query planner.

Database Constraint

Rules enforced by the database to maintain data integrity, including NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY.

A pattern where records are marked as deleted with a flag or timestamp rather than being physically removed from the database.

Stored Procedure

A precompiled collection of SQL statements stored in the database that can be executed as a single unit.

A situation where two or more transactions permanently block each other by each holding locks that the other needs.

View All Databases Terms →