Filtering on lookups and CDC ETL

[bilzekek]

Hi experts,

I have a question related to filtering on lookups in cases where you use CDC ETL.

Lets say you have a Replicate task with store changes on Table A, and another replicate task with store changes on Table B.

In Compose, table A is used as main table for an Entity defined and from Table B you want to join (Lookup) some data that you want to use for filtering/removing some of the rows of Table A (that you plan to use as a Fact table in a Data mart). To do this, Table B is added as another Entity in the Model step of Compose and the lookup is then defined in the Data mart fact table.

Am I correct in assuming that Compose will not be able to handle the the following scenarios with CDC ETL:

• A change is occuring only to Table B that should result in filtering out a row in the Fact table.
• Later, That change in Table B is the reversed and Table B is updated again. Now the row should be present in the Fact table again.

The only way to update Fact table correctly is to do full rebuild of datamart (but you can do CDC ETL up till DWH level) ?

[Nanda_Ravindra]

@bilzekek I believe the only way to update the Fact table correctly is to do a full rebuild of DataMart, but I am checking with the internal team on this.  We will get back to you on this.

Thanks,

Nanda

[Nanda_Ravindra · Accepted solution]

@bilzekek ,  Just confirming that,the only way to update the Fact table correctly is to do a full rebuild of Data Mart. However, talking to our internal team, there are a couple of a work-around for this...

Option #1 :

Use the filter column in the fact table. (e.g if you are filtering an order by its shipperName) - you could put shipperName in the fact table.

Option #2:

Yyou could use shipperName in an expression - eg. CASE WHEN ${shipperName} is null then 1 else 0 end. Just by using the $shipperName in an expression, Compose will figure out changes to shippers.

Option#1  would be the simplest way to do this.

If you use the option#2, the case statement can include any filter columns  and you get code like below (see highlighted statement)

If you need assistance with this, please open a case with support and we will assist you in implementing this.

Thanks,

Nanda

[bilzekek]

Hi, OK will look into that thanks.