topic Re: Slowly changing dimension - PostgresQL - scalability issue in Talend Studio

Slowly changing dimension - PostgresQL - scalability issue

Anonymous — Fri, 19 Jun 2020 18:44:21 GMT

Hi,

I used the SCD component for Postgres to implement a slowly changing dimension and though it works, it only seems to run efficiently without any Java memory heap errors on small data sets. My dimension is roughly 30M records and when I try to run the job on a 10M size dimension with 50 incoming type 2 changes, the job will take hours to run only to eventually give a Java memory heap error.

So, my question is, is this SCD component only meant for small tables and just a quick convenient way to implement it for this specific purpose or is this supposed to work for big data sets as well? If not, is the way to go a custom made solution consisting of lookup and conditional splits for type 1 and 2 fields with more efficient bulk updates instead of row by row which the SCD component perhaps does?

Roland

Re: Slowly changing dimension - PostgresQL - scalability issue

vapukov — Sun, 21 Jun 2020 01:14:59 GMT

in my personal opinion, yes - SCD components could be used only for relatively small tables with a small number of changes.

But SCDELT, do all work on the target database with just 4 generated queries. The only one issue for SCDELT component - it require both tables (staging and target) to be in the same schema, this is not always useful, but could be managed by creating and delete staging table.

Re: Slowly changing dimension - PostgresQL - scalability issue

Anonymous — Mon, 22 Jun 2020 14:11:06 GMT

Hi,

Thanks for the reply. I'll look into that. The shortcoming of having both the staging and target in one schema is a strange one. I'll have to accommodate for that as I'm using a persistent staging area in a different schema currently.

Roland

Re: Slowly changing dimension - PostgresQL - scalability issue

vapukov — Mon, 22 Jun 2020 14:17:59 GMT

possibly it is a bug, when I try to use table name with schema job return error

you can test

Re: Slowly changing dimension - PostgresQL - scalability issue

Anonymous — Mon, 22 Jun 2020 14:26:28 GMT

Okay, I'll try something out.

Roland

Re: Slowly changing dimension - PostgresQL - scalability issue

Anonymous — Tue, 23 Jun 2020 17:50:47 GMT

I guess another condition I noticed is that all column names of the source table needs to be the same as the target dimension. I actually had a tmap object initially between the source and target that renamed the column names and changed some of the data types, but I suppose I'll have to first create a separate intermediate staging table for that.

Re: Slowly changing dimension - PostgresQL - scalability issue

Anonymous — Wed, 24 Jun 2020 13:48:21 GMT

Hi, I've looked into the ELT solution, but I don't understand how it works.

I created a separate simplified job to get a better understanding of the mechanics.

It consist of an incoming stg_acnt table (see screenshot) with 1 record and another dim_acnt table with 2 records (screenshot).
Below the create and insert statements as well.

CREATE TABLE test.stg_acnt
(acnt_id VARCHAR (50)
, prod_code VARCHAR (10)
, open_dt integer
, valid_from_dt TIMESTAMP
, valid_to_dt TIMESTAMP
);

INSERT INTO test.stg_acnt VALUES ('A511', 'BBB', 20200623, CURRENT_DATE, '2199-12-31 23:59:00');
COMMIT;

CREATE TABLE test.dim_acnt_scd
( dim_acnt_id SERIAL
, acnt_id VARCHAR (50)
, prod_code VARCHAR (10)
, open_dt integer
, valid_from_dt TIMESTAMP
, valid_to_dt TIMESTAMP
);

INSERT INTO test.dim_acnt_scd VALUES (1, 'A511', 'AAA', 20200602, '2020-06-02 00:00:00', '2199-12-31 23:59:00');
INSERT INTO test.dim_acnt_scd VALUES (2, 'B777', 'AAA', 20170515, '2017-05-15 00:00:00', '2199-12-31 23:59:00');
COMMIT;

I created the job as follows (see screenshots). Postgres DB input object to read in the staging table connected to the ELT object.

source table: "stg_acnt"
table: "dim_acnt_scd"
surrogate key: dim_acnt_id
creation: auto increment
Source key: acnt_id
SCD type 2 field: prod_code
SCD type 1: open_dt
start date: valid_from_dt
end date: valid_to_dt

Based on account id 'A511' and a change in product code which I classified as SCD Type 2,I expected one record to be added to the dim_acnt_scd table, which did not happen.

Roland

tDBInput.png
tDBSCDELT_1 part1.png
tDBSCDELT_1 part2.png
test_dim_acnt_scd.png
test_stg_acnt.png

Re: Slowly changing dimension - PostgresQL - scalability issue

vapukov — Wed, 24 Jun 2020 14:24:26 GMT

The world is not ideal 🙂

For better understanding how it works with the Talend way, you can enable statements logging on PostgreSQL and check actual queries which Talend generate

for you, you will see:

columns valid_from and valid_to - located and managed in the target table
Talend will use as valid from current timestamp, and as valid_to - null

really there inconsistency and incompatibility of approaches and technologies between ETL and ELT components, this is why often people prefer to write their own sql code, load all into staging table than just run 3-4 queries

regards, Vlad

Re: Slowly changing dimension - PostgresQL - scalability issue

Anonymous — Wed, 24 Jun 2020 14:28:19 GMT

It certainly isn't!

Where do I enable the logging statements? I don't see those options under the basic and debug run.

Re: Slowly changing dimension - PostgresQL - scalability issue

vapukov — Wed, 24 Jun 2020 14:31:47 GMT

this is PostgreSQL option, you need to enable it on PostgreSQL server, section logging of postgresql.conf:

logging_collector = on
log_statement = 'all'                   # none, ddl, mod, all

Re: Slowly changing dimension - PostgresQL - scalability issue

Anonymous — Wed, 24 Jun 2020 15:04:55 GMT

Thanks. I had initially look at the code where the update and insert statements strings were created, but this is definitely better. Apparently for this simple example, it created the following insert statements for the type 2 change (see below).

The first query I can relate to more as the product code is being compared between the two tables, but then there is the condition

d."valid_to_dt" = '2020-06-24 09:45:06.601' which is applied to the existing record in the dimension table, which is odd since for an existing record I've put the valid_to_dt by default to 2199 or so and therefore this query does not result in anything. The second query is also weird since there is a where clause of the source key being null.

INSERT INTO "test"."dim_acnt_scd"("acnt_id", "open_dt", "prod_code", "valid_from_dt", "valid_to_dt")
SELECT t."acnt_id", t."open_dt", t."prod_code", '2020-06-24 09:45:06.601', NULL
FROM "test"."stg_acnt" t, "test"."dim_acnt_scd" d
WHERE d."acnt_id" = t."acnt_id" AND
( COALESCE(d."prod_code"<> t."prod_code", (d."prod_code" is null and not t."prod_code" is null) or (not d."prod_code" is null and t."prod_code" is null)))
AND d."valid_to_dt" = '2020-06-24 09:45:06.601'

Re: Slowly changing dimension - PostgresQL - scalability issue

vapukov — Wed, 24 Jun 2020 15:10:37 GMT

Yes, it mean - you need to have empty target table for proper ELT work

Re: Slowly changing dimension - PostgresQL - scalability issue

Anonymous — Wed, 24 Jun 2020 15:10:38 GMT

Okay, I now that the insert statement is preceded by an update statement that sets the valid_to_date.

UPDATE "test"."dim_acnt_scd" d
SET "valid_to_dt" = '2020-06-24 09:45:06.601'
FROM "test"."stg_acnt" t
WHERE d."acnt_id" = t."acnt_id"
AND ( COALESCE(d."prod_code"<> t."prod_code", (d."prod_code" is null and not t."prod_code" is null) or (not d."prod_code" is null and t."prod_code" is NULL)))
AND d."valid_to_dt" IS NULL

It also requires the existing valid_to_dt to be null instead of 2199.

Re: Slowly changing dimension - PostgresQL - scalability issue

Anonymous — Wed, 24 Jun 2020 15:13:34 GMT

The simple example is working now after I updated the valid_to_dt field to null. Great...now the other one