Geek Logbook

Tech sea log book

Recent Posts

Categories

#50projectsIn50days Airflow Amazon Web Services AWS Azure Batch CAP Coders At Work cron Data Databases Data Warehouse Django Edsger W. Dijkstra Excel Git Google Hadoop Hellerstein Hive Javascript Knowledge Transfer MVC Network Address Translation Networking Notes on Structured programming PHP Power Bi Powershell Public Subnet Python Redash Social Skills Software Developer Spark Spark: The Definitive Guide SQL SSH SurrogateKeys Testing The Elements of Programming Style Traversy Media Understanding Distributed Systems whale-alert Yaml

Archives

Month: March 2025

Handling Schema Changes in a Data Warehouse

When building and maintaining a Data Warehouse (DWH), handling schema changes without breaking existing processes is a crucial challenge for data engineers. As new requirements emerge, we often need to add new fields, modify existing structures, or adjust data models while ensuring smooth operation for reporting and analytics. This blog post explores best practices and
Read More

Delta Lake vs. Traditional Data Lakes: Key Differences and Vendor Options

Introduction As data-driven organizations scale their analytics and machine learning workloads, the limitations of traditional data lakes become more apparent. Delta Lake is an open-source storage layer that enhances data lakes with ACID transactions, schema enforcement, and time travel, making them more reliable for big data workloads. In this post, we will explore how Delta
Read More

Why OLTP Systems Don’t Retain Historical Changes

Online Transaction Processing (OLTP) systems are designed for high-speed transactions and efficient data management. However, one of their characteristics is that they do not retain historical changes by default. In this post, we will explore why this happens and provide an example to illustrate the concept. OLTP Systems: Focused on Current Data OLTP databases are
Read More

Understanding Slowly Changing Dimensions (SCD) in Data Warehousing

When dealing with data warehouses, handling changes in dimension data over time is crucial. Unlike operational databases where updates are straightforward, data warehouses require preserving historical data for accurate analysis. This is where Slowly Changing Dimensions (SCDs) come into play. What are Slowly Changing Dimensions (SCD)? SCDs are a technique in data warehousing used to
Read More

Modes and Examples of KPIs in Data Analysis Expressions (DAX)

Last Year Comparison When analyzing sales performance, it is often useful to compare the current year’s sales with the same period in the previous year. To do this, we create several calculated measures: DAX expressions to calculate similar measures for Units instead of Revenue: Year-to-Date (YTD) Comparison Year-to-date (YTD) measures track performance from the beginning
Read More

Understanding the Relationship Between Database Replication and the CAP Theorem

Introduction Database replication is a fundamental strategy in distributed systems that ensures data is duplicated across multiple nodes. However, when designing a replicated database, one must consider the CAP theorem, which defines the fundamental trade-offs in distributed computing. In this post, we will explore how the CAP theorem applies to database replication and what trade-offs
Read More

Understanding Surrogate Keys in Databases

When designing relational databases, one crucial decision is how to uniquely identify each record in a table. This is where surrogate keys come into play. Unlike natural keys, which derive from existing attributes in the data, surrogate keys are system-generated identifiers, typically using numeric sequences or UUIDs. What is a Surrogate Key? A surrogate key
Read More

Understanding Pagination vs. Batch Processing in Data Handling

When working with large datasets, developers often face the challenge of efficiently extracting, processing, and managing data. Two commonly used techniques for handling such data efficiently are pagination and batch processing. While both methods aim to optimize memory usage and performance, they serve different purposes and are implemented differently. What is Pagination? Pagination is a
Read More

Tracking Daily File Size Changes in SQL

When working with databases that store file metadata, it’s often useful to track how file sizes change over time. If you have a table with the following structure: You may want to analyze the day-to-day changes in file size. This can help in monitoring storage usage, detecting anomalies, or understanding file growth trends. SQL Query
Read More