Duplicate rows are the enemy of every data integration developer. They can cause job failures and – even worse – wrong results. Somtimes it is more complex than expected to avoid them. In this blog post, let’s compare different approaches.
Aggregation Filters in Oracle 26ai
The FILTER clause in SQL allows an easy way of adding WHERE conditions to aggregation functions. There were other ways to implement this before, but this syntax extension is easier to understand. In Oracle databases, it is available since version 23.26.1.
JOIN TO ONE and Star Schemas
Oracle’s new JOIN TO ONE syntax offers several benefits for queries in star schemas. Let’s take a look at some typical use cases involving facts and dimensions, and see how the new join option can help reduce complexity and avoid incorrect results.
JOIN TO ONE and Constraints
The new JOIN TO ONE syntax extension seems to be very useful for queries on star schemas. But before we can use it, we have to prove how they can be combined with different types of constraint definitions.
Partition by Expression
In the latest version of the Oracle database, it is now possible to define the partition key of a partitioned table using an expression. This was already possible previously, but only with a small workaround.
Implementing Multi-Temporality with Data Vault (Part 3)
In the last part of this blog series, we will look at how to load Slowly Changing Dimension (type SCD1 and SCD2) from the Current Views we created in our multi-temporal Data Vault schema.
Implementing Multi-Temporality with Data Vault (Part 2)
Loading multi-temporal data into Data Vault is quite simple, a explained in the previous blog post of this series. But how can we extract the current data in a simple and convenient way to populate Data Marts?
Implementing Multi-Temporality with Data Vault (Part 1)
Data change tracking is a key concept in any data warehouse and many other analytical systems. Rather than explaining the same concepts over and over again, I decided to write a blog series on this topic. This might also be of interest to others – perhaps even to you.
Playing around with Calendar Functions in Oracle 26ai
Date calculations are sometimes a bit tricky. A set of new calendar functions in Oracle 26ai helps to simplify data arithmetic in SQL.
QUALIFY: The WHERE Condition for Analytic Functions
Filtering analytic functions was not straight-forward in Oracle and most other databases. Oracle 26ai now supports the QUALIFY clause in SQL. It works like a WHERE condition, but can be used for analytic functions.
Continue reading