Stored procedures perform database operations based on SQL statements. Beyond traditional queries, stored procedures can also handle complex transactions or loads. The Celigo platform can work as an orchestration tool to precisely execute stored procedures in the optimal time.
Using database connections in the Celigo platform, you can seamlessly leverage the potential of SQL stored procedures in ELT (Extract, Load, Transform) flows.
Example: Consider a scenario in a NetSuite-Snowflake integration with many flows where you want to:
- first, extract and load the data from NetSuite to Snowflake tables
- then, transform the data to create a data warehouse in Snowflake
Since the integration has many flows, create an execution plan to run the flows. That is, schedule a run of the first flow, then daisy chain the rest of the flows. The initial flows will extract and load the data, and the last flow will call a stored procedure to transform the data as required for the data warehouse.
In the example scenario, when all the flows to extract data from NetSuite and load it into Snowflake tables have been completed, the flow to transform data is run. This last flow will export the loaded data from Snowflake and execute the stored procedure to transform data using the CALL statement that you provide in the SQL Query setting of your export.
You won't see the transformed records in the export Preview; but, you can preview any message that is returned and check whether the SQL CALL statement is successfully executed. After the flow is run, you can view the transformed records in Snowflake.
Similarly, you can consider calling stored procedures in your flows to optimize performance and efficiency.