Data Engineering Fundamentals, Part 4: Data Warehousing and Why Snowflake Stands Out
This is Part 4 of a 5-part series on data engineering fundamentals. Part 3 covered ETL/ELT concepts. Part 5, the final post, covers data visualisation tools.
What a data warehouse actually is?
A data warehouse is a system optimized for reading and analyzing large volumes of structured data, as opposed to an operational database (OLTP) optimized for fast, small, transactional reads and writes. Where OLTP systems power an app's checkout process one order at a time, an OLAP-style warehouse is built to scan millions of rows and answer "what were our total sales by region last quarter" quickly.OLTP system will be always write optimised whereas OLAP read optimised.
Core data warehousing concepts
OLAP vs OLTP. Online Analytical Processing systems are designed for complex queries across large datasets, online Transaction Processing systems are designed for fast, frequent, small transactions. Data engineers typically move data from OLTP sources into an OLAP-style warehouse.
Star schemas and dimensional modeling. A fact table holding measurable events (sales, clicks, transactions) surrounded by dimension tables holding descriptive context (customers, products, dates). This structure has outlasted decades of tool changes because it makes analytical queries fast and intuitive.
Slowly Changing Dimensions (SCDs). When a dimension's attributes change over time, a customer's address, a product's category, you need a strategy for how to handle that history. Type 1 overwrites the old value, Type 2 keeps a full history with effective dates. Knowing which to use where is a core data modeling decision.
Partitioning and clustering. Large tables perform poorly if the engine has to scan the entire table for every query. Partitioning or clustering data by a commonly filtered column, like a date, lets the engine skip scanning irrelevant data entirely.
Why Snowflake specifically?
Separation of storage and compute. Snowflake's architecture separates storage from compute entirely, meaning you can scale query processing power up or down independently of how much data you're storing, and multiple teams can run their own virtual warehouses against the same data without competing for resources.
Virtual warehouses. Instead of one shared cluster for every workload, Snowflake lets you spin up separate, independently sized compute clusters for BI traffic, batch ETL, and ad hoc analysis, each billed separately and each auto-suspending when idle.
Zero-copy cloning. Need a full copy of a production database for testing? Snowflake can clone it instantly without duplicating the underlying storage, you only pay for data that changes afterward. This is one of the most genuinely useful features for building safe dev and test environments quickly.
Time Travel. Snowflake lets you query or restore data as it existed at a past point in time, within your account's retention window, useful for recovering from an accidental bad update or deletion without needing a full backup restore.
Native support for semi-structured data. Snowflake can query JSON, Avro, and Parquet data natively alongside structured tables, which matters increasingly as more source systems, especially SaaS APIs, deliver data in JSON rather than clean rows and columns.
How this connects back to the earlier parts of this series?
Everything from Part 1's SQL concepts and Part 3's ELT patterns lands here. You write SQL against Snowflake, your ELT tool pushes transformation logic down to run inside it, and the star schema and SCD concepts determine how you actually structure the tables your BI tools will eventually query, which is exactly where Part 5 picks up.
Where to go from here?
Once your data is modeled and sitting cleanly in a warehouse like Snowflake, the final step is making it usable for actual humans to explore and understand. Part 5 covers data visualization concepts and how to choose between Tableau, Power BI, and Qlik.
Comments
Post a Comment