This post is all about how-to build the proper data vault model to handle CLOB, BLOB, LOB, BIN, BINARY, IMAGE fields. Basically fields that are BINARY in nature. In this _very_ short post I cover where to put these fields in the Data Vault Model and why. In my new one-on-one coaching section I will teach you how to use these fields in ETL/ELT.
See my posting about my new one-on-one coaching, it is open to a limited number of signups for a 3 month program – and generally has a waiting list for the start of the next program. Contact me now at: firstname.lastname@example.org for more information.
These fields are thorny, problematic, and hard to deal with in a data warehousing sense, and many times people make mistakes in the Data Vault model and don’t place these fields in the right structures. Remember what I teach: that it all begins and ends with the data model architecture. Make a mistake in the architecture, and it will cost you dearly!
So, where do we put these fields?
These types of fields belong in their OWN separate Satellite – that’s right, all by themselves. You can add another column to the Satellite called a hash-key (if you want). Then, you can run the binary data through a hashing function and determine the key. The hashing key can help you with distinct clauses (removing duplicates from the staging table), and with comparisons to the “current” row in the satellite.
Why separate the fields?
The point is: separate, divide and conquer. It’s the only way to deal with these particular binary fields. They are long, ugly, and unparsable by standard SQL actions. Field separation is necessary so that standard structured content isn’t mixed with “unstructured” content. We have no way of knowing how long the fields are, directly comparing the fields to themselves and other rows, and manipulation of these fields typically requires special handling routines which end up being slow to execute.
You asked for it… so here it is: suppose I have a staging table:
Create Table STG_MY_PERSON_RECORD (
primary key (person_badge)
Ultimately what you need in the Data Vault model should look like this:
create table HUB_PERSON (
primary key (person_sqn)
create table SAT_PERSON_INFO (
primary key (person_sqn,person_info_load_date)
create table SAT_PERSON_IMAGE (
primary key (person_sqn,person_img_load_date)
What this will do is allow you to construct a hash key (make the length of the hash either 32, 64, 128 or 256) depending on how unique you want it to be, and how much “tolerance for misses” you can take. The smaller the hash key, the larger the tolerance (the more changes you MIGHT miss).
By separating the Satellite with the image, and the rest of the binary fields, you can effectively model the Data Vault and produce the proper architecture and handling mechanisms needed to run the ETL efficiently.
DON’T FORGET TO CONTACT ME about reserving your spot in my one-on-one coaching session opening up soon!
Hope this helps,