0

I want to be database agnostic but let's say we're inserting data into SQL Server which is to be used as a data warehouse.

There are MILLIONS of email records from an email platform that wasn't too well though out technically. It's data is not normalized.

So for instance one import might be 7 million records where one important field is a subject line (for testing purposes) and that subject line is "Happy Mother's Day One and All, we love Long Texts!" for all 7 million records.

Now add that "7 million of the exact same long text" to about maybe 50 different subject lines. The benefits of normalization couldn't be clearer with this example. Convert the highly redundant long texts to integers, and then store a small dimensional table translating them back to text. Right?

I'm just wondering the exact implementation of this.

I'm going to use an ETL tool (one or another) but I suppose there are multiple ways of doing it. I guess I would attempt to translate the "subject" into an int based on the small dimensional table, and if no match is found, I would take the distinct new records and append them with an auto-incrementing table? Just wondering if this is a common task.

Normalizing smelly data before insertion into a data warehouse. I don't mean cleaning, auditing, profiling --- I mean literal database normalization to shrink the data volume.

1
  • You might eventually, in intelligent fashion, denormalize the data before ingestion into a BI/ front-end tool. But that's mostly joining data to other dimensions in a star schema situation. It's not ingesting TO BE OR NOT TO BE THAT IS HTE QUESITON WHETHER TIS NOBLER IN THE MIND about 7 million times. No intelligent BI system does that. It converts it to an int first typically. Esp in situations where say text is changeable.
    – user45867
    Apr 29, 2021 at 20:26

2 Answers 2

2

The whole process you described is implemented in the database engine itself for the columnstore compression.
DB engine will scan data for repeating text, find duplicates and create dictionary tables. In the row data it will keep only minimal references to dictionary.
Be sure to keep data batches big enough to trigger this compression process during insert - follow this guidance: Columnstore indexes - Data loading guidance

1
  • 2
    This is a good observation, but I wish it worked so well in practice. String columns are still a problem for columnstore. Replacing text with a dimension table is still a valuable technique, mainly for performance reasons linked to internal implementation details.
    – Paul White
    Apr 30, 2021 at 8:53
2

Finding if an in-coming string already exists in the dimension table will be slow. What you're trying to do is map an arbitrarily long string to an integer surrogate key. This sounds like a hash to me.

As the values are written to the staging table calculate a hash of the string and store it also. This hash will be used as the surrogate key in the dimension. Copy the hash and other values to the fact table. Merge the hash and long string into the dimension table.

There are a few possible problems here. One is that hashes will be randomly distributed over the possible range. There will be gaps, negative numbers and no sense that smaller numbers existed before bigger numbers. Since this is an internal surrogate it really doesn't matter. But to humans it often does.

Second problem is that there could be hash collisions where two distinct strings produce the same hash value. With a naive implementation this would result in corrupt data - the values read do not match those written. Depending on how the hash is calculated the probability of collision could be vanishingly small. If the consequences are minor you may be able to live with this. If major you may have to write extra code to work around the issue.

2
  • Interesting thoughts. In this case is the staging table effectively an incremental/ temporary table of sorts? I guess it depends on if I'm trying to simply save storage space, processing speed, or downstream processing.
    – user45867
    Apr 30, 2021 at 15:19
  • The staging table is where the source file / CSV / JSON / whatever is loaded into a relational table. The question mentioned dimension so I inferred a data warehouse and batch ETL. Typically the staging table would be emptied on each batch run. However hashing would work just as well in OLTP or streaming contexts. May 1, 2021 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.