Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi @mopidesp - welcome to the forum!
Some great questions concerning Compose understanding. Apologies in advance for the long reply, but hopefully this is helpful!
- Full and CDC tasks.
- You cannot combine CDC and Full load into a single ETL Set today. Instead you would need 2 ETL sets – 1 for full load and 1 for CDC. These could be executed together in a workflow. Note that if you have dependencies between these (ie Category <Full> -> Subcategory (CDC) -> SubSubCategory (Full) ) you do not need to have 3 ETL sets execute. You can simply run the Full then CDC work loads. Compose will infer missing members (i.e. the subcat records in this example) and data will be “whole” after processing.
- Compose ETL sets are executing SET-based SQL so you are getting native parallelism from the underlying data warehouse engine. Having said that, if you have a few transactional type tables with very large records you may want to
- put them in their own ETL Set for initial load purposes.
- Use a table-driven filter and execute the ETL Set multiple times with specific ranges (e.g. date ranges for “transaction” tables or natural key ranges). When I say table driven filter – create a table to hold the data ranges and in the mapping filter use a condition like “TRANASCTION_DATE IN (SELECT DATES_TO_PROCESS FROM FilterTable ) “. You can execute the process and have a Post-Load ETL step to simply insert / set the new set of filters – then execute the process again until no more data to process.
- If you have existing history you want to load (e.g. an existing set of Type 2 data for dimensional / reference data) you should load from beginning of time to current or based on natural key ranges. For this you should map the FROM_DATE (FD by default) in the mapping with the historical data
- If you can run an initial load without having to worry about missing references or executing any data validations there is an option in Compose to “Optimize for Initial Load”. (Highlight the full load ETL Set – click Settings and under Advanced tab - ) Please check the documentation for restrictions when using this option.
- Multi-table v Single-table ETL
- There is a difference when Multi-table / Single table gets executed and their purpose. First understand the overall ETL flow (see high-level diagram below) –
- Compose performs data validations first – then loads data to a staging table TSTG. If performs this in parallel for all the mappings that are executing in a single ETL Set. Then multitable and single table etls kick in.
- Multi-table ETL’s should be used when you want to use data in multiple TSTG tables. These are serial processes and are executed after the load to TSTG occurs. For example I wish to perform some additional processing for customers have changes to both sales transactions and demographic data.
- Single-table ETL’s should be used when you want to use data in a single TSTG table. Single-table ETLs are executed in parallel (by using that component you signify to Compose that you only care about the specific table assigned to the Single-table ETL.) For example – I wish to update a Type 1 attribute in the customer hub for “last_order_date” based on incoming order changes
- This graphic depicts the multi-table and single-table steps in the ETL flow
- There is a difference when Multi-table / Single table gets executed and their purpose. First understand the overall ETL flow (see high-level diagram below) –
The first set of blue nodes are the loading to TSTG for 3 mappings – once completed the mult-table steps kick in. Once they are completed Single-table can be executed in parallel. After that Compose completes the processing of the data.
- CT Delete or Archive
- Compose will either simple DELETE or DELETE and ARCHIVE the data from the __Ct tables once it has processed the data.
- Configure Compose to DELETE or to ARCHIVE the data based on the landing zone configuration (see image below).
- If you wish to “replay” the events – you can move them from the archive table back into the __Ct table. A best practice is if you decide to replace a period of time – replay all the data from that point forward for the given natural key. For example,. If I want to replay data from 2 days ago for CustomerID XXXX – then I would move all CT records from 2 days ago to now back into the __CT tables for CustomerID XXXX. (Or all data from 2 days to now if I want to replay the entire data set).
- Note that on some platforms the archive tables are created with a primary key of header__change_seq (Replicate generated value that tells Compose the order of operations) and header__change_oper). If you “replay” these with ARCHIVE configured its possible (depending on DW platform) for the second archival to fail as the data already exists in the __ar (archive suffix) tables. In those cases when replaying it is possible to adjust the header__change_seq as you insert into the CT tables for replay. Just ensure that you maintain the order relative to the header__timestamp
- CDC Sets
- As you said – CDC requires the use of TABLE as a source as Compose will replace the table_name with table_name__ct and add the archival of data.
- The only other “limitation” I’m aware of for CDC is to be aware that any lookups defined in the ETL are NOT driver tables.
- Ie. If I have customers with a lookup to customer_type – a change to customer_type would not get picked up by the CDC process as customer_type is not a driving table.
- It may be better to normalize that model design instead – see this doc for details - Modeling Real-time Data Warehouses in Compose >.
a. Note that even if you have “full load” data sources not fed by Replicate, there are incremental design patterns that can be used if you know for example that the full load data set has sent you only changes. (For example an industry data feed that sends descriptive data for financial market instruments that have changed in the past 2 days).
If you wish for more info on this – please private message me on here. In some cases these design patterns can enable the use of “CDC” ETL sets for both FULL and CDC based data loads by mimicking the CT tables for full data sets. Which would solve your other question of combining CDC and FULL into a single process 😊
FYI – there are some additional white-papers in the documents and videos section of this forum for Compose for DW - https://community.qlik.com/t5/Qlik-Compose-for-Data-Warehouses-Documents/tkb-p/qlik-compose-warehous...
