OLAP vs OLTP and OLAP Cube

OLTP (Online Transaction Processing) systems are designed to handle high volumes of concurrent transactional operations such as inserts, updates, and deletes. They are write-optimized, support ACID properties, and typically use normalized schemas to avoid data redundancy(3NF). Examples include banking systems, e-commerce order processing, and healthcare applications.

OLAP (Online Analytical Processing) systems are designed for analytical workloads and business reporting. They are read-optimized and store large volumes of historical data for complex queries involving aggregations, joins, and trend analysis. OLAP systems commonly use denormalized star or snowflake schemas to improve query performance. Examples include data warehouses such as Snowflake, Redshift, BigQuery, and Databricks.

Note: In summary, OLTP answers "What is happening now?" while OLAP answers "What happened over time and why?"

Must mentioned while explaining OLTP systems:

• ACID transactions
• Low latency
• Fast inserts/updates/deletes
• High concurrency

OLAP Cube

An OLAP Cube is a multidimensional, pre-aggregated representation of business data designed to support fast analytical queries. It organizes measures such as revenue or profit across dimensions like time, product, and region, enabling operations such as slice, dice, roll-up, and drill-down. Cubes are commonly built on top of a star schema and were widely used in traditional BI systems to accelerate reporting workloads.

SQL Grouping Sets, Cube & Rollup

Grouping set, cube and rollup are the extensions of group by clause, they allow multi level aggregations in a single query.

The ROLLUP, generates the hierarchical aggregations given columns from left to right, for example:
GROUP BY ROLLUP(continent, country, state), this will aggregate: (continent, country, state), (continent, country), (continent). The total rows output are n+1, where n are the number of columns present in group by

The CUBE will generate all the possible combinations of the the given columns, order does not matter. The total rows output are 2^n, where n are the number of columns present in group by

The GROUPING SETS give you more control over the aggregations to generate where we can define, we can cherry pick from the combinations of GROUP BY CUBE

ETL vs ELT

ETL transforms data before loading it into the target system, whereas ELT loads raw data first and performs transformations inside the data warehouse or lakehouse. ETL is common in traditional environments, while ELT is the preferred approach in modern cloud data platforms.

In modern data engineering, ELT is more common because cloud warehouses and lakehouses provide scalable compute. We typically load raw data into a Bronze layer and then transform it into Silver and Gold layers, enabling reprocessing, lineage, and better auditability.

Batch vs Streaming

Batch processing collects data over a period of time and processes it at scheduled intervals, such as hourly, daily, weekly, or monthly. It is suitable when real-time insights are not required. Examples include financial reporting, payroll processing, and risk assessment.

Streaming processing, on the other hand, processes data continuously as it arrives, typically through platforms like Apache Kafka, Azure Event Hubs, or AWS Kinesis. It is used when near real-time insights or actions are required, such as fraud detection, real-time recommendations, clickstream analytics, and IoT monitoring.

• The choice between batch and streaming depends on several factors:
  1. Data freshness requirements: How quickly does the business need the data?
  2. Latency requirements: Can the business wait hours, or does it need results within seconds?
  3. Cost and complexity: Batch systems are generally simpler and cheaper, while streaming systems require more infrastructure and operational effort.
  4. Business use case: Reporting and analytics often use batch processing, whereas fraud detection and monitoring systems typically use streaming.

In summary, batch processing focuses on processing data at scheduled intervals, while streaming processing focuses on processing data continuously with minimal latency. The decision depends primarily on business SLAs and how fresh the data needs to be.