How to Debug and Monitor Cognite Transformations [Cognite Official]

To ensure the reliability and accuracy of your data transformations in Cognite Data Fusion (CDF), you need to be able to effectively debug and monitor them. Here's how:

1. Debugging Transformations

• Preview Transformation Results: Before running a transformation, use the "Preview" functionality in the CDF UI. This allows you to see a sample of the output data based on your transformation logic, without writing to the destination. This is crucial for verifying that your mapping and SQL queries are correct.

• Check Transformation Syntax: If you're using the SQL editor, ensure that your SQL syntax is valid. Use the formatter to help you checking the syntax.

• Review Error Messages: When a transformation fails, CDF provides error messages. These messages can help you identify the source of the problem, such as issues with data types, missing values, or incorrect logic.

• Use Cognite AI to explain the code: Cognite AI can help explaining the code and help you troubleshoot.

• Modularize Transformations: Break down complex transformations into smaller, more manageable parts. This makes it easier to isolate and debug specific sections of your code.
• Test with Sample Data: Use a small, representative sample of your data to test your transformation logic. This can speed up the debugging process and make it easier to identify issues, for example add a where in your code or limit the number of data you are selecting.
• Logging: While you don't directly control logging within the transformation SQL code itself, consider how the data being processed might indicate issues. For example, if certain values are consistently missing or out of range in the output, this could point to a problem in your transformation logic. You can always select additional data to show them in the Preview as column to troubleshoot.

2. Monitoring Transformations

• Transformation Status: CDF provides information about the status of transformation runs, including whether they are running, completed successfully, or failed. Monitor this status to track the progress of your transformations and identify any issues.

• Run History: Review the history of transformation runs to see how often they have been running, how long they take, and if there are any recurring failures.

• Email Notifications: Set up email notifications for transformation failures. This will alert you to any problems so you can take corrective action promptly. You can configure these notifications in the transformation settings within the CDF UI. 

• Data Workflows: Use Cognite Data Workflows to effectively manage your Transformations, and take advantage of built-in tooling for error handling and scheduling. Workflows provide orchestration and monitoring capabilities, allowing you to define dependencies between transformations, track their execution, and manage the overall data flow.

By combining these debugging and monitoring techniques, you can ensure that your data transformations in CDF are running smoothly and producing accurate, reliable data. Also refer to the built-in functions and help on writing SQL syntax, on this page https://docs.cognite.com/cdf/integration/guides/transformation/write_sql_queries

Examples

Error in the code, missing field

This error indicates that the isStep field is not present in the select sql code. This will be picked up by the Preview since isStep is a mandatory field.

Error in the code, syntax error

This error indicates that the tostring function does not exist and cannot be used in the code.

This error indicates that we are missing a , on line 6 to split the fields.