Comparing and Contrasting Different Data Systems
Comparing and Contrasting Different Data Systems
Explore how data moves from operational source systems into analytical repositories, how warehouses, marts, lakes, and lakehouses differ, how cloud infrastructure changes responsibility, and how historical data changes are controlled.
Data Processing: OLTP vs OLAP
This section introduces two primary processing patterns. OLTP supports large numbers of real-time database transactions, while OLAP supports complex analysis on large databases without negatively affecting transactional systems.
OLTP
Online transactional processing handles operational activity such as inserts, deletions, and queries in real time. An ATM is a familiar example of an OLTP system.
Real timeTransactionsOperationalOLAP
Online analytical processing is designed for high-volume, multidimensional analysis and can support summaries such as counts and totals across very large datasets.
AnalysisLarge datasetsReporting| Question | OLTP | OLAP |
|---|---|---|
| Main purpose | Process operational transactions | Analyze data |
| Typical example | ATM balance, deposit, withdrawal | Analyze large volumes of transaction history |
| Analyst caution | Poor production queries can slow systems or alter data | Designed to support analytical workloads |
If you work directly in a production transactional system, queries require care. A warehouse or data lake is more likely to support analytical work.
Source Systems & Data Lineage
A source system is the system of record for a given data element—the trusted source of truth from which that information originates. Understanding lineage means knowing where data came from and how it reached the dataset you are analyzing.
Source Systems → Analytical Repository → Reporting
Different business systems can be authoritative for different data elements. Follow the arrows to see how their data can be brought together for downstream reporting.
The boxes on the left are separate business systems, not sequential processing steps. Each may be the system of record for particular data elements. Their data can flow into a downstream analytical repository and then into reporting. If a value in a report needs to be verified, data lineage helps the analyst trace it back toward its original source.
Organizations often have separate accounting, payroll, HR, CRM, sales, production, and logistics systems. A data analyst should identify the original source before relying on downstream copies.
Data Warehouses, Data Marts & Schemas
A data warehouse unifies company data from multiple source systems for reporting and decision-making. A data mart is a subset dedicated to a particular department or group, with access controlled around that group's needs.
Unified reporting data
Fact Table
Holds facts about a business process or event—often measurements or metrics—and keys that connect to dimensions. The 's Fact_ProductSales example includes product, date/time, customer, salesperson, and TotalQty.
Dimension Table
Holds descriptive or categorical attributes that give context to facts, such as customer, salesperson, product, and date details.
Example — Fact Table and Dimension Tables
The fact table records the measurable business event. Keys connect each sale to descriptive dimension tables that explain what, when, who, and which salesperson.
| Fact_ProductSales | |||||
|---|---|---|---|---|---|
| ProductKey | DateKey | CustomerKey | SalesPersonKey | TotalQty | SalesAmount |
| 101 | 20260901 | 501 | 21 | 2 | $1,200 |
| 102 | 20260901 | 502 | 22 | 1 | $750 |
| 101 | 20260902 | 503 | 21 | 3 | $1,800 |
| Dim_Product | ||
|---|---|---|
| ProductKey | ProductName | Category |
| 101 | Road Bike | Bikes |
| 102 | Helmet | Accessories |
| Dim_Date | ||
|---|---|---|
| DateKey | Date | Month |
| 20260901 | Sep 1, 2026 | September |
| 20260902 | Sep 2, 2026 | September |
| Dim_Customer | ||
|---|---|---|
| CustomerKey | CustomerName | Region |
| 501 | Aisha | Central |
| 502 | Daniel | North |
| 503 | Mei | South |
| Dim_SalesPerson | ||
|---|---|---|
| SalesPersonKey | Name | Team |
| 21 | Alex | Retail |
| 22 | Priya | Corporate |
In the first fact row, ProductKey 101 points to Road Bike, DateKey 20260901 points to Sep 1, 2026, CustomerKey 501 points to Aisha, and SalesPersonKey 21 points to Alex. The measurable facts for that event are TotalQty = 2 and SalesAmount = $1,200.
Practice with the Example SQLite Database
Download the SQLite database containing Fact_ProductSales, Dim_Product, Dim_Date, Dim_Customer, and Dim_SalesPerson. The sample rows match the tables shown above.
↓ Download Example SQLite Database .sqliteData warehousing commonly uses star and snowflake schemas. Both connect fact and dimension tables using keys, but organize dimensions differently.
Star Schema vs Snowflake Schema
Both schemas center analysis around facts and dimensions. A star schema generally uses denormalized dimensions for simplicity and performance. A snowflake schema can normalize dimensions into additional related dimension tables to reduce redundancy.
Practice with Example SQLite Schemas
Download both database designs and inspect how the same sales data is modeled differently. The Star Schema keeps descriptive attributes inside its main dimension tables, while the Snowflake Schema normalizes Category, Month, Territory, and Team into additional related dimension tables.
| Feature | Star | Snowflake |
|---|---|---|
| Center | Single fact table | Single fact table |
| Dimensions | Joined directly to fact | Dimensions may connect to other dimensions |
| Dimension design | Generally denormalized | Normalized to reduce redundancy |
| Visual appearance | Star-like | Branching snowflake-like structure |
Data Lakes & Data Lakehouses
When data does not yet fit a rigid warehouse structure, organizations can use a data lake. A data lakehouse is a hybrid intended to combine useful characteristics of data lakes and data warehouses.
Data Lake
A flexible repository for large quantities of structured, semi-structured, and unstructured data. It can act as a “catch all” while data waits for cleaning or analysis.
Schema-on-readFlexible storageData Warehouse
Uses a pre-designed structure for reporting and decision-making, with rigor around what enters the warehouse and how it relates.
Schema-on-writeReportingData Lakehouse
Combines warehouse and lake characteristics, aiming for flexibility and scalable analytical use while supporting BI, analytics, machine learning, and data science.
HybridAnalytics + MLRepository Continuum
flexible holding
hybrid
structured reporting
Infrastructure: On-Premise & Cloud
Infrastructure affects how analysts connect to data. On-premise environments are controlled by the organization; cloud environments are hosted through third-party providers over the internet.
On-Premise
The organization hosts and controls its own environment and architecture, whether servers are physically onsite or in a datacenter it controls.
Public Cloud
A cloud service provider offers shared resources to multiple tenants, commonly through subscription or pay-as-you-go models.
Private Cloud
Cloud infrastructure exclusively owned and managed by an organization, providing greater control over privacy and security.
Community Cloud
Several organizations share a hosted or private cloud around common requirements such as security or standardization.
Hybrid Cloud
Combines public, private, community, and/or on-premise elements to meet different workload needs.
Multicloud
Uses services from multiple cloud service providers.
IaaS, PaaS, SaaS & Containerization
Cloud service models differ mainly in how responsibility is divided between the provider and the customer. Containerization packages an application with the libraries and dependencies it needs for easier deployment and scaling.
Hands-On Examples
Open these browser-based examples to connect each service model with a practical environment.
IaaS Example — Incus
Explore remotely hosted Linux virtual machines and instances. Use this as a hands-on infrastructure example.
Open Incus Try It ↗PaaS Example — Google Colab
Open a hosted notebook environment where you can write and execute code without managing the underlying servers.
Open Google Colab ↗SaaS Example — Google Drive
Use a finished cloud application through the browser to store, manage, access, and share files.
Open Google Drive ↗Container Example — Killercoda
Use interactive browser playgrounds to explore container and cloud-native technologies without a local installation.
Open Killercoda Playgrounds ↗Compare how much infrastructure you directly manage in each example: infrastructure-focused environments expose more underlying resources, while SaaS presents the finished application to the user.
Packages an application together with required libraries and packages so it can be deployed through a network more consistently and scaled more easily. Containerization can be used alongside IaaS, PaaS, or SaaS environments.
How Data Changes: SCD & TCL
Data warehouses must decide how to preserve changes in dimension data. Slowly changing dimensions (SCDs) define common history strategies, while Transaction Control Language (TCL) controls database transactions generated by data modifications.
SCD Table Examples — Product Name Changes
Assume product 101 changes its name from Red Valley Bike to Vibrant Valley Bike. The three SCD types store that change differently.
SCD Type 1 — Overwrite
The existing value is replaced. The old product name is not retained.
| Before Change | ||
|---|---|---|
| ProductKey | ProductName | Category |
| 101 | Red Valley Bike | Bikes |
| After Change | ||
|---|---|---|
| ProductKey | ProductName | Category |
| 101 | Vibrant Valley Bike | Bikes |
SCD Type 2 — Add a New Row
The historical row is retained and a new row represents the new version. Effective dates can identify when each version applied.
| ProductKey | ProductName | StartDate | EndDate | Current |
|---|---|---|---|---|
| 101 | Red Valley Bike | 2025-01-01 | 2026-08-31 | No |
| 101 | Vibrant Valley Bike | 2026-09-01 | — | Yes |
SCD Type 3 — Add a Previous-Value Column
The current and previous values are kept in the same record. This preserves limited history.
| ProductKey | CurrentProductName | PreviousProductName | Category |
|---|---|---|---|
| 101 | Vibrant Valley Bike | Red Valley Bike | Bikes |
Type 1: replace the old value. Type 2: preserve the old row and add a new row. Type 3: keep the current and previous values in columns of the same row.
| Type | Method | History |
|---|---|---|
| Type 1 | Overwrite existing value | Previous value is not retained |
| Type 2 | Add a new row for the new value | Historical rows are retained, commonly with effective dates |
| Type 3 | Add a column for previous/current value | Limited history is retained in one record |
Transaction Control Language
BEGIN TRANSACTION UPDATE ToyProducts SET ProductName = N'Toy Soldier' WHERE ProductName = 'Toy Soldeir' -- Validate the change with a SELECT COMMIT -- make the transaction changes available -- or ROLLBACK -- undo uncommitted changes SAVEPOINT -- create a point that can be rolled back to
An open transaction can lock records. COMMIT confirms changes, ROLLBACK reverses uncommitted work, and SAVEPOINT provides an intermediate rollback point.
Summary
The connects three ideas: how data is processed and stored, where infrastructure runs, and how changes are managed over time.
Knowledge Check — 20 Questions
Test the core concepts from the . Select your answers, check your score, then reveal the answer key for review.