This article explores how to use Snowflake\u2019s task<\/a> features to implement parent-child data workflows<\/strong>. We will walk through a real-world scenario in which a healthcare organization manages patient records using a sequence of tasks for data ingestion, transformation, enrichment, and reporting<\/strong>. By setting up parent-child tasks<\/strong>, we can automate and control these steps, ensuring that each task is executed only after the successful completion of its predecessor.<\/p>\n\n\n\n In Snowflake<\/a>, in order to automate and schedule the business process we will utilize the Task<\/a> feature. <\/strong>The Task infrastructure of Snowflake can run multiple types of resources such as:<\/p>\n\n\n In this article, we will be using multiple tasks to call stored procedures to establish parent \u2013 child task relationships. To coordinate these tasks, we will be utilizing the Task Graph<\/a> featured in snowflake to create sequences of tasks to implement a data pipeline for ingestion, transformation, enrichment and report generation.<\/p>\n\n\n\n The CREATE TASK<\/a> statement will be used to create new tasks. You can see the syntax by following the link to the documentation. There are a lot of settings, many of which we will not touch on in this article.<\/p>\n\n\n\n A task graph can be used to create what are commonly referred to as parent-child tasks. A parent \u2013 child task is the sequence of events where child task (dependent task<\/a>) only execute after the parent task (root task<\/a>) is completed it is job. It is commonly used in data engineering world to have data integrity within the tables. The relationship between the tasks are referred to as task dependencies<\/a>, which is shown in the following diagram.<\/p>\n\n\n\n <\/p>\n\n\n\n This will be the starting point (or the root of the graph) of the data pipeline workflow which can be executed to call Once the data ingesting and cleaning is completed from parent task After completing cleaning and transformation task with SP1 In this final task, we would generate summary report based on processed and transform data. The child task 3 The below figure showing a parent \u2013 child task data flow (task graph flow) within the healthcare environment. It starts with preprocessing task via sp1 (stored procedure 1) <\/p>\n\n\n\n In this section, we will perform step-by-step process for task (parent \u2013 child)<\/strong> implementation in Snowflake. We will start with creation of database which we have already covered in this article<\/a>. Afterwards, we will start with the development of 4 stored procedures<\/strong> along with the relationship reasoning of parent child concept<\/strong><\/a> <\/strong>within this article. We then progress to setup task graph<\/a> with parent-child built in feature from SQL to automate the store procedures, as a result, it will create out final output, We will build a healthcare database in Snowflake<\/a> at this stage. Start by creating a new database:<\/p>\n\n\n\n <\/p>\n\n\n\n The two tables represented in the following screenshot can be created and populated from this SQL file<\/a> if you want to follow along on Snowflake.<\/p>\n\n\n\n <\/p>\n\n\n\n We will proceed to create four stored procedures represented on the following diagram that will be used for various purposes when processing our data.<\/p>\n\n\n\n <\/p>\n\n\n\n Create the procedure by using following code:<\/p>\n\n\n\n In the above code, we will ensure timestamps are correctly formatted using a standard format ( Secondly, we will be removing NULL values from provider information is null because we want accurate reporting for healthcare compliance <\/p>\n\n\n\n Create the procedure by using following code:<\/p>\n\n\n\n Note: For demonstration purposes, this transformation overwrites the original <\/em> Create the procedure by using following code. This procedure enriches the data, which in this sample case is just updating the Create the procedure by using following code. This code will create the report table by creating or recreating the Daily_Reports object using the data that has been manipulated in the previous steps.:<\/p>\n\n\n\n After creating the stored procedures, the next step is to create the task for each store procedure to create a parent \u2013 child process. The stored procedures relationship within the parent-child concept<\/a> in Snowflake tasks, as applied to our content (the healthcare data processing workflow), is fundamentally about task dependencies<\/a> and the sequential execution<\/a> of stored procedures, where each task (child) depends on the successful completion of the previous task (parent).<\/p>\n\n\n\n In the context of a healthcare data pipeline, processing patient records in stages such as preprocessing, transformation, enrichment, and reporting is crucial. In a real-world scenario, these stages must be executed sequentially to ensure the integrity of the data at each step.<\/p>\n\n\n\n Therefore, snowflake\u2019s task graph<\/a> <\/strong>built with parent-child task offer a seamless way to automate these workflows. These tasks are executed sequentially, ensuring that each task completes before the next begins, thus preserving the logical flow of the data pipeline.<\/p>\n\n\n\n Here\u2019s how we break down the automation process using task graphs:<\/p>\n\n\n\n <\/p>\n\n\n\n The root task is responsible for the initial preprocessing of healthcare logs from stored procedure Create the task by using following code:<\/p>\n\n\n\n In the task creation, there are a few parameters that have been defined.<\/p>\n\n\n\n Specifies the virtual warehouse to execute the task. This user-managed warehouse provides the compute power needed to run the SQL in our procedure.<\/p>\n\n\n\n This defines how often the task should run \u2014 every 10 minutes. It ensures our data is cleaned regularly without needing manual intervention.<\/p>\n\n\n\n Invokes the stored procedure that standardizes date formats and removes invalid records (like null patient IDs) from our Healthcare_LOGS table.<\/p>\n\n\n\n What Happens if this Task Graph Takes Longer Than 10 Minutes?<\/strong><\/p>\n\n\n\n Snowflake tasks do not overlap by default. If the task hasn\u2019t finished before the next scheduled start, that run will be skipped. This helps avoid data conflicts, but repeated skips may cause processing delays. Hence it is good to monitor your processes to make sure that you have the interval time set to a reasonable level.<\/p>\n\n\n\n Once the preprocessing task is complete, the transformation task takes over. This task uses the results of the preprocessing step and categorizes the patient data from The transformation task is followed by the enrichment task ( Create the task by using following code:<\/p>\n\n\n\n The final step is to generate healthcare reports from stored procedure Create the task by using following code:<\/p>\n\n\n\n Before executing the sequence of tasks created above. Let\u2019s examine our <\/p>\n\n\n\n Next, navigate to the task list to see the list of tasks. Note that tasks are in suspended state by default. So we will need to enable them.<\/p>\n\n\n\n \n \n Once we click on graph section on DEMO_HEALTHCARE \/PUBLIC \/ HEALTHCARE_PREPROCESSING_TASK <\/strong>you can see it is the root task which we have developed above.<\/p>\n\n\n\n <\/p>\n\n\n\n In order to navigate to child task we can navigate to DEMO_HEALTHCARE \/PUBLIC \/ HEALTHCARE_TRANSFORMATION_TASK<\/strong><\/p>\n\n\n\n <\/p>\n\n\n\n After examining the pre-task execution, now, the next step is to restart it. Snowflake tasks are stopped by default when created and must be explicitly resumed to become active. Do this by executing the following script:<\/p>\n\n\n\n Now you can start the tasks as noted. Then after executing all the tasks, the next thing to do is to verify the task execution. Now let\u2019s proceed to monitor the task which we have executed. In Snowsight<\/a>, the snowflake web interface, you can easily visualize and monitor task graphs:<\/p>\n\n\n\n Navigate to the Tasks<\/strong> section in Snowsight.<\/p>\n\n\n\n <\/p>\n\n\n\n Here, you will see all active tasks and their respective statuses. For parent-child tasks, the interface shows the relationship between tasks in a tree structure, allowing you to view dependencies.<\/p>\n\n\n\n <\/p>\n\n\n\n You can view details like the last execution time<\/strong>, status<\/strong> (Success, Failed, or Skipped), and the scheduled interval<\/strong>.`<\/p>\n\n\n\n <\/p>\n\n\n\n Prior to the task graph status as \u201csucceeded\u201d, we have received failure as well, when executing the task with the following error.<\/p>\n\n\n\n <\/p>\n\n\n\n Note: This is an error I forced to occur by changing the stored procedure to reference H.PROVIDER_NAME, which does not exist in the table.<\/em><\/p>\n\n\n\n We double click on latest scheduled run time, it will open the task graph:<\/p>\n\n\n\n <\/p>\n\n\n\n As you have noticed, all the task’s status is \u201cSucceeded<\/strong>\u201d.<\/p>\n\n\n\n In the next step, we will check the data was populated for the report by the stored procedure SP4 <\/a>What Is a Task ? <\/h2>\n\n\n\n
\n
<\/a>What Is Parent \u2013 Child Task aka (Task dependencies)? <\/h2>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-1.png\")
<\/figure>\n\n\n\n<\/a>Parent Task <\/h3>\n\n\n\n
SP1<\/code> (stored procedure 1). In this article, SP1<\/code> PREPROCESS_HEALTHCARE_LOGS<\/code> will be used for pre-processing purpose to update the log table for removing null,<\/strong> so it can be ready for transformation in child task 2 HEALTHCARE_TRANSFORMATION_TASK<\/code>.<\/p>\n\n\n\n<\/a>Child Task 1<\/h3>\n\n\n\n
HEALTHCARE_PREPROCESSING_TASK<\/code>, the next task which is our child task 1 HEALTHCARE_TRANSFORMATION_TASK<\/code> to call SP2 (stored procedure 2). The SP2 TRANSFORM_HEALTHCARE_DATA<\/code> will basically perform transformation such as categorizing the patient event<\/strong>.<\/p>\n\n\n\nChild Task 2<\/h3>\n\n\n\n
PREPROCESS_HEALTHCARE_LOGS<\/code> and SP2 TRANSFORM_HEALTHCARE_DATA<\/code> respectively, our next action item is to add the data. Therefore, child task 2 HEALTHCARE_ENRICHMENT_TASK<\/code> will call SP3 (Stored Procedure 3). In Stored Procedure 3 SP3 ENRICH_HEALTHCARE_DATA<\/code> we will be adding provider details<\/strong>, providers are basically the one who provide healthcare services to patients such as medication, surgery and medical devices.<\/p>\n\n\n\n<\/a>Child Task 3<\/h3>\n\n\n\n
HEALTHCARE_REPORTING_TASK<\/code> will call stored procedure 4 SP4 GENERATE_HEALTHCARE_REPORTS<\/code> to produce final output<\/strong> of the healthcare data.<\/p>\n\n\n\n<\/a>Parent \u2013 Child Task Data Flow or Task Graph Flow<\/h2>\n\n\n\n
PREPROCESS_HEALTHCARE_LOGS<\/code> to clean and normalize data. Next, we will apply child task 1: transformation task with stored procedure: TRANSFORM_HEALTHCARE_DATA<\/code> (sp2) to categorize patient events for data analytics. Furthermore, we will implement child task 2 and 3 with sp3 ENRICH_HEALTHCARE_DATA<\/code> and sp4<\/code> GENERATE_HEALTHCARE_REPORTS<\/code> respectively, which will be utilized to insert additional data and producing final output for data analysis.<\/p>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-2.png\")
<\/figure>\n\n\n\n<\/a>Parent Child Task Implementation <\/h2>\n\n\n\n
Daily_Reports<\/code>.<\/strong><\/p>\n\n\n\n<\/a>Stored Procedures Creation <\/h3>\n\n\n\n
![\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/07\/word-image-103280-2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/https-www-red-gate-com-simple-talk-wp-content-up.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-4.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-5.png\")
<\/figure>\n\n\n\n<\/a>SP1 preprocess_healthcare_logs<\/h4>\n\n\n\n
CREATE OR REPLACE PROCEDURE PREPROCESS_HEALTHCARE_LOGS()\nRETURNS STRING\nLANGUAGE SQL\nAS\n$\nBEGIN\n-- Ensure timestamps are properly formatted\nUPDATE HEALTHCARE_LOGS\nSET EVENT_TIME = TO_TIMESTAMP(EVENT_TIME)\nWHERE EVENT_TIME IS NOT NULL;\n\n-- Remove incomplete records\nDELETE FROM HEALTHCARE_LOGS WHERE PROVIDER_ID IS NULL;\n\nRETURN 'Preprocessing Completed';\nEND;\n$;<\/pre><\/div>\n\n\n\n
YYYY-MM-DD HH24:MI:SS<\/code>), it transforms the EVENT_TIME<\/code> column into the appropriate TIMESTAMP<\/code> data type. This is crucial because date values are frequently entered as plain text by raw ingestion processes, which, if improperly formatted, can lead to problems when sorting, filtering, or time-based aggregations.<\/p>\n\n\n\n <\/a>SP2 transform_healthcare_data <\/h4>\n\n\n\n
CREATE OR REPLACE PROCEDURE transform_healthcare_data()\nRETURNS STRING\nLANGUAGE SQL\nAS\n$\nBEGIN\n -- Example: Categorize patient events based on type\n UPDATE Healthcare_LOGS\n SET PATIENT_EVENT = CASE\n WHEN PATIENT_EVENT LIKE '%Checkup%' THEN 'Routine Checkup'\n WHEN PATIENT_EVENT LIKE '%Surgery%' THEN 'Surgical Procedure'\n WHEN PATIENT_EVENT LIKE '%Emergency%' THEN 'Emergency Procedure'\n ELSE 'General Consultation'\n END;\n RETURN 'Transformation completed successfully';\nEND;\n$;<\/pre><\/div>\n\n\n\n
PATIENT_EVENT<\/code> value. In a production environment, it is advisable to write categorized values to a separate column to preserve the original data.<\/em><\/p>\n\n\n\n<\/a>SP3 enrich_healthcare_data<\/h4>\n\n\n\n
PROVIDER_ID<\/code> value to the same value (which basically validates that the Healthcare_LOGS.PROVIDER_ID<\/code> value is in the ProviderDirectory<\/code> table), but typically you might be pulling in additional columns.<\/p>\n\n\n\nCREATE OR REPLACE PROCEDURE enrich_healthcare_data()\nRETURNS STRING\nLANGUAGE SQL\nAS\n$\nBEGIN\n -- Example: Enrich data with provider details\n UPDATE Healthcare_LOGS AS H\n SET H.PROVIDER_ID = p.PROVIDER_ID\n FROM (SELECT PROVIDER_ID --, PROVIDER_NAME\n FROM Provider_Directory) p\n WHERE h.PROVIDER_ID = p.PROVIDER_ID;\n\n RETURN 'Enrichment completed successfully';\nEND;\n$;<\/pre><\/div>\n\n\n\n
<\/a>SP4 generate_healthcare_reports<\/h4>\n\n\n\n
CREATE OR REPLACE PROCEDURE generate_healthcare_reports()\nRETURNS STRING\nLANGUAGE SQL\nAS\n$\nBEGIN\n -- Example: Aggregate data to create daily reports\n CREATE OR REPLACE TABLE Daily_Reports AS\n SELECT\n DATE_TRUNC('day', EVENT_TIME) AS Report_Date,\n COUNT(*) AS Total_Events,\n COUNT(DISTINCT PATIENT_ID) AS Unique_Patients\n FROM Healthcare_LOGS\n GROUP BY DATE_TRUNC('day', EVENT_TIME);\n RETURN 'Reports generated successfully';\nEND;\n$;<\/pre><\/div>\n\n\n\n<\/a>Automating Stored Procedures via Task Graph (Parent \u2013 Child Task) <\/h3>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-6.png\")
<\/figure>\n\n\n\n<\/a>Define the Root Task (Preprocessing)<\/h4>\n\n\n\n
preprocess_healthcare_logs<\/code>. <\/strong><\/p>\n\n\n\nCREATE OR REPLACE TASK healthcare_preprocessing_task\nWAREHOUSE = 'compute_wh'\nSCHEDULE = '10 MINUTE'\nAS\nCALL preprocess_healthcare_logs();<\/pre><\/div>\n\n\n\n
WAREHOUSE = 'compute_wh'<\/pre><\/div>\n\n\n\n
SCHEDULE = '10 MINUTE'<\/pre><\/div>\n\n\n\n
CALL preprocess_healthcare_logs();<\/pre><\/div>\n\n\n\n
<\/a>Create the First Child Task (Transformation)<\/h4>\n\n\n\n
transform_healthcare_data<\/code> <\/strong><\/p>\n\n\n\nCREATE OR REPLACE TASK healthcare_transformation_task\nWAREHOUSE = 'compute_wh'\nAFTER healthcare_preprocessing_task\nAS\nCALL transform_healthcare_data();<\/pre><\/div>\n\n\n\n
<\/a>Create the Second Child Task (Enrichment)<\/h4>\n\n\n\n
enrich_healthcare_data<\/code>), which pulls in external data like provider details.<\/p>\n\n\n\nCREATE OR REPLACE TASK healthcare_enrichment_task\nWAREHOUSE = 'compute_wh'\nAFTER healthcare_transformation_task\nAS\nCALL enrich_healthcare _data();<\/pre><\/div>\n\n\n\n
<\/a>Create the Final Child Task (Reporting)<\/h4>\n\n\n\n
generate_healthcare_reports<\/code>.<\/strong> This task only runs after the data has been enriched.<\/p>\n\n\n\nCREATE OR REPLACE TASK healthcare_reporting_task\nWAREHOUSE = 'compute_wh'\nAFTER healthcare_enrichment_task\nAS\nCALL generate_healthcare_reports();<\/pre><\/div>\n\n\n\n
<\/a>Task Monitoring and Report Verification<\/h3>\n\n\n\n
healthcare_logs<\/code> table before making any updates.<\/p>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-7.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-8.png\")
<\/figure>\n\n\n\n
\n<\/p>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-9.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-10.png\")
<\/figure>\n\n\n\nSELECT SYSTEM$TASK_DEPENDENTS_ENABLE('DEMO_HEALTHCARE.PUBLIC.HEALTHCARE_PREPROCESSING_TASK');<\/pre><\/div>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-11.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-12.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-13.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-14.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-15.png\")
<\/figure>\n\n\n\ngenerate_healthcare_reports<\/code> via the healthcare_reporting_task<\/code> task. <\/strong>To do this, I will execute the following query.<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/word-image-106202-16.png\")
<\/figure>\n\n\n\n