Inconsistent instruction when generating instructions each time

[aj96]

When generating the ETL instruction I have noticed that each time the ETL instruction being generated are not the same as before. Mainly the difference is in the statement that are finally used to apply the changes in the cdc. Sometime some of tables will use MERGE statement to INSERT or UPDATE the objects in the TDWH or otherwise will have and INSERT for new objects and an UPDATE for old objects. Usually the statements that gets generated is a mix of these two but recently now the statements being generated doesn't have any MERGE statements.

Is there any reason why instructions differ? and how does compose decide which one to use?

This inconsistency is causing issue at times where the query that being generated can sometimes have issue and when testing if we are using one set of instruction and in production we use a different set of instruction would cause of concern for us as the tested instruction are different from the one that was deployed.  

Would anyone help me with this issue.

[TimGarrod]

Hi @aj96  - this maybe more of a question for support - but a couple of clarifying questions please...

1 - Are your versions of Compose the same in the different environments where you are seeing different code being generated?

2 - When you say you see different code - is that for different entities or the same entity?

3- Can you check and see if the MERGE V INSERT / UPDATE is between HUBS and SATELLITES?   We do process HUBS and SATS differently based on their nature (Type1 v Type2). 

Finally - can you post an example where the code is different loading the same entity and same physical table (HUB or SAT) ? 

Be aware that as new versions or service packs come out and new features are released for the various DW Env's - it is entirely possible that code is generated differently between versions of Compose.  Either for new Compose features /  enhancements (like back-dating support) or new DW features (like the upcoming MERGE feature release in Azure Synapse). 

[aj96]

Hi Tim,

Answer for you question

1. We are actually using the same Compose in the same server for the higher regions too.

2. Well it differed each time. On entity that had that merge query wouldn't have in the higher region and sometimes vice versa. But now when we generate we don't see any merge queries now. We had added couple more entities but the version is still the same.

3. These were all HUB tables.

I wanted to post the code but now we don't have that merge code generated in any of our regions.  Now they all are Update/Insert statements.

[jacksonback]

The inconsistency in the generated ETL instructions could be due to various factors, including changes in the data source, changes in the configuration or settings of your ETL tool, or updates to the ETL tool itself. Here are some potential reasons why the instructions may differ:

1. Data Changes: If there are changes in the data source, such as new columns, modified data types, or changes in primary keys, the ETL tool may generate different instructions to accommodate these changes.

2. ETL Tool Updates: Updates to the ETL tool itself, including bug fixes, performance improvements, or changes to the underlying algorithms, may result in different instructions being generated.

3. Configuration Changes: Changes in the configuration or settings of the ETL tool, such as different mappings, transformations, or rules, can influence the generated instructions.

4. Optimization: The ETL tool may dynamically adjust its optimization strategies based on factors such as data volume, complexity, or performance requirements, resulting in different instructions being generated.

5. Data Quality: Variations in data quality, such as missing values, duplicates, or inconsistencies, may trigger different handling mechanisms in the ETL tool, leading to different instructions.

To address the issue of inconsistency and ensure consistency between testing and production environments, consider the following steps:

1. Version Control: Maintain version control for your ETL processes and configurations to track changes and ensure consistency across environments.

2. Regression Testing: Perform comprehensive regression testing to validate ETL processes and instructions across different environments and configurations.

3. Documentation: Document the ETL processes, configurations, and changes to facilitate communication and collaboration among team members.

4. Monitoring and Alerting: Implement monitoring and alerting mechanisms to detect anomalies or discrepancies in ETL processes and instructions.

5. Review and Validation: Regularly review and validate ETL processes, instructions, and outputs to identify any inconsistencies or issues and take corrective actions as needed.

By following these best practices and addressing potential causes of inconsistency, you can ensure consistency and reliability in your ETL processes across different environments. Additionally, consulting with your ETL tool's documentation or support resources may provide insights into how the tool determines which instructions to use in different scenarios.