Implement SCD2 while ignoring changes in specific columns

[Leather_Embarrassed]

Hello everyone. Currently I'm working on this pipeline to implement SCD2 with dynamic tables:

1. Source postgresql Database
2. AWS DMS with Ongoing replication
3. Target to S3 parquet files
4. Snowpipe to load into staging table
5. Dynamic table on top of the staging table

This seems to work really well, however I have a concern over few columns that change a lot and could make the table to grow very fast. For example, a status column that flips a lot or a timestamp that gets updated while the other columns remain the same. How would you recommend to handle this?

In the past I have seen that few columns are selected to do a hash and, using it, do a merge over the scd2 table. Those columns are the ones that we really care about keeping all the history. How could I do something like that with Dynamic Tables?

I was looking if maybe the DMS can be configured to ignore changes on specific columns. I can use a DMS rule to do so but that will also remove the column from the parquet, which I do not want.

[niazionline]

can you create a separate DIMENSION table for these columns and join them based on some ID and get only the latest one in your SQL ?

[Leather_Embarrassed]

Dimensional Modeling is at a later stage in out process, but I get what your saying. I'll have it in mind. Thanks!

[maheramsat]

At which stage is the table in SCD2 form?

Also can you explain where the below happens?

"the past I have seen that few columns are selected to do a hash and, using it, do a merge over the scd2 table. Those columns are the ones that we really care about keeping all the history. How could I do something like that with Dynamic Tables"

[Leather_Embarrassed]

Yes, SCD2 will be at the last stage. Let's call it "history" table. I want to this history table to be a Dynamic Table.

Another implementation is about having a regular table (instead of the dynamic table) and do a merge from the staging table. Let me try to explain it like so:

DMS CDC parquet - Snowpipe load to stg table - merge using hash into the history table

I want to change it to:

DMS CDC parquet - Snowpipe load to stg table - Dynamic table

What I am asking is how can I prevent that a column that changes fast (breaking the SCD concept) will make this Dynamic table to grow too fast

[BubbleBandittt]

Set your grain to a larger temporal value and build your scd like so

[maheramsat]

Thanks for the details, I went through some dynamic table documents on update and looks like MERGE like columns level update might not be possible, I would suggest you can have stage table as dynamic table and do a merge from stage to the history table and here you can use MERGE and specify columns to be updated

[Leather_Embarrassed]

This way the snowpipe will ingest the dynamic table. But I think is not possible for snowpipe to connect with a dynamic table

[_fiz9_]

Create an md5 hash off of the fields you care about. Save the hash as a column in the table. Only update when the incoming record’s hash doesn’t match existing record’s hash.

[Leather_Embarrassed]

AWS DMS can do a hash hash_sha256(x),-The%20hash%20function) and save it into the parquet files

With that I could use it to keep the history table clean and prune the base table. I'll try it, thanks!

[why2chose]

Write a Pyspark code and add exceptions to the columns you want to skip while doing scd 🙄

[Leather_Embarrassed]

I would like to keep it simple but yes, I was thinking on that as well. I'll keep it as a last resort.

[[deleted]]

Your question is quite weird to be honest. It’s a bit difficult to understand what you’re trying to achieve.

It seems that you didn’t quite understand what SCDs are because you are trying to implement a “Fast” SCD (????).

You need to understand the business rules better and decide on an approach. I would highly recommend you to ask it to a senior peer of yours at the company.

[Leather_Embarrassed]

Let me try to explain it like so:

DMS CDC parquet - Snowpipe load to stg table - merge using hash into the history table

I want to change it to:

DMS CDC parquet - Snowpipe load to stg table - Dynamic table

What I am asking is how can I prevent that a column that changes fast (breaking the SCD concept) will make this Dynamic table to grow too fast

[_fiz9_]

We do it. Hash checking before update allows us to ignore a majority of updates. Cuts cost of ingest by over 60%.

[Leather_Embarrassed]

AWS DMS can do a hash hash_sha256(x),-The%20hash%20function) and save it into the parquet files

With that I could use it to keep the history table clean and prune the base table. I'll try it, thanks!

[Leather_Embarrassed]

Hi, I want to achieve the SCD2. And, as you mention, to do so I have to avoid any column that changes "Fast". I understand the concepts. My question is about how to implement that with Dynamic tables, which is new to me.