OLTP vs OLAP, dimensional modeling, SCDs, lakehouse
Practise these as flashcards. One or two lines each; the ones you miss come back first.
Start freeOLTP systems are designed for fast, reliable transaction processing, like inserting or updating records, while OLAP systems are optimized for complex queries and analytics over large datasets. OLTP focuses on data integrity and concurrency, whereas OLAP focuses on query performance and aggregation.
Normalization reduces data redundancy and ensures data integrity by organizing data into related tables. This makes updates and inserts more reliable and prevents anomalies.
Facts are numeric measurements or events, like sales amount, while dimensions are descriptive attributes, like product or date, that provide context for the facts.
A star schema is a data modeling approach where a central fact table is surrounded by denormalized dimension tables, making queries simple and fast for analytics.
I would choose a snowflake schema if dimension tables are very large or have hierarchical relationships that benefit from normalization, which can save storage and improve maintainability. However, this can make queries more complex and slower due to additional joins.
The grain defines the level of detail for each row in a fact table, such as one row per product sale per day. Setting the grain clearly is crucial because it determines what questions the table can answer and prevents ambiguity.
A Slowly Changing Dimension tracks changes in dimension attributes over time, such as a customer's address. It's important because businesses often need to analyze historical data as it was at different points in time.
SCD Type 1 overwrites old attribute values with new ones, so history is lost. SCD Type 2 creates a new record for each change, preserving the previous values and enabling historical analysis.
Data Vault consists of Hubs, which store unique business keys; Links, which represent relationships between hubs; and Satellites, which store descriptive attributes and track changes over time.
By organizing data into bronze (raw), silver (cleaned), and gold (business-ready) layers, the medallion architecture isolates data quality issues and allows for incremental improvements, making analytics more reliable and maintainable.
I compare incoming records with current dimension records on key attributes. If a change is detected, I insert a new row with updated values and effective dates, and mark the old row as expired.
Dimensional modeling denormalizes data, reducing the number of joins needed for queries and allowing for efficient aggregations, which speeds up reporting and analytics workloads.