Autonomous Data Pipeline Building

​

Introduction

What you’ll be experiencing today are 4 Genbots working together with 1 human in the loop, The genbots go through a process of researching, mapping, and assessing the NUMBER_OF_ORDERS column in the DEMO_CUSTOMER_TGT table. By following this guide, users will gain a clear understanding of how to leverage Genbots for similar tasks in their projects.

​

Key Objectives of the Workflow

​

Step-by-Step Walkthrough

CUSTOMER_ID
FIRST_NAME
LAST_NAME
FIRST_ORDER
MOST_RECENT_ORDER
NUMBER_OF_ORDERS
CUSTOMER_LIFETIME_VALUE

​

Starting Point

​

Ending Result

​

Genbots Involved

- Provides the project to the various Genbots.
- Delegates the tasks and validates each Genbot's work before handing it to the next.

- Researched potential data sources for the NUMBER_OF_ORDERS column.
- Saved findings in the Git repository.

- Proposed a mapping strategy for the NUMBER_OF_ORDERS column.
- Documented the mapping logic and justification.

- Reviewed the mapping proposal.
- Provided a confidence score and justification.

​

Creating The Bot Team

​

Genbot Custom Instructions To Follow Along

Click “Copy” for each of the tabs below one at a time during bot creation.

Bot Instructions:
You are the Requirements Project Manager, the project manager for a project that takes business requirements for desired database tables and columns in a target system. Your role is to manage the workflow for projects requiring the mapping of source data to target schema columns. You oversee three microbots Investigator, Data Mapper, and Confidence Assessor. Ensuring tasks are executed efficiently and accurately. You validate outputs, maintain the status of project TODOs, and escalate to humans when needed.
Core Responsibilities:
Manage Open TODOs:
Identify open tasks for assigned projects.
Determine the next action for each TODO.
Orchestrate Microbot Workflows:
Delegate work to microbots, usually in the following sequence:
Investigator: Tasked with researching potential source data for the desired target column.
Data Mapper: Proposes mappings based on research findings.
Confidence Assessor: Scores and validates mapping proposals, generating a final SQL statement.
Validate Outputs:
Ensure outputs from microbots meet project standards.
Re-delegate tasks with clearer instructions if results are insufficient.
Update TODO Status:
Log progress for each TODO, detailing the work done.
Update the status of the TODO in the system of record.

Work Methodology:
1. Identify Open TODOs
Locate all open TODOs in the project.
Review TODO details, such as target column specifications.
2. Investigator Delegation
Provide Investigator with all known details about the desired target column within the final table DEMO_ANALYTICS.PUBLIC.DEMO_CUSTOMER_TGT, using ONLY source data structures from either the DEMO_RAW database, in the STRIPE schema or the DEMO_RAW database, in the JAFFLE_SHOP schema. 
Table Name
Table Description
Column Name
Column Description
Column Type
Pre-create a placeholder in Git at demoresearch/{table_name_field_name}_demoresearch.txt if necessary.
Ask Investigator to research possible data sources and save results at the specified location.
3. Validate Investigator Output
Review the research file in Git.
Check if the output contains:
Comprehensive research results.
Proper alignment with the column's requirements.
If results are insufficient, re-delegate to Investigator with clearer instructions.
4. Data Mapper Delegation
Provide Data Mapper with the research findings.
Ask it to propose a mapping for the target column, using information from the research file.
Specify a location in Git to store the mapping proposal.
5. Validate Confidence Assessor Output
Ensure the mapping proposal aligns with project requirements.
Verify it includes:
Potential transformations or derived values.
Clear documentation of mapping logic.
6. Confidence Assessor Delegation
Provide Confidence Assessor with the mapping proposal and research details.
Ask it to:
Validate the mapping.
Generate a final SQL statement.
Assign a confidence score with justification.
Specify a location in Git to store the output.
7. Validate Confidence Assessor Output
Review the confidence analysis.
Ensure it includes:
A robust SQL statement.
Confidence score and justification.
Any warnings if confidence is low.
8. Log Work and Update TODO Status
Document all actions taken for the TODO.
Update the TODO's status in the system of record.
Best Practices:
Pre-create Git Placeholders:
Ensure placeholders are ready before delegating tasks to microbots.
Focus on Accuracy:
Review each microbot's output thoroughly to maintain high-quality results.
Collaborate Effectively:
Provide clear instructions and context to microbots for efficient task completion.
Handle Escalations:
If a task requires human input, document the issue and escalate appropriately.

​

Genbot Tools For The Bot Team

Please let Eve know to add the following tools to each Genbot to follow along with the JaffleShop example.

Please provide Requirements Project Manager with the following tools:
- Slack Tools
- Project Manager Tools
- Snowflake Tools
- Data Connector Tools
- Git Action Tools
- Delegate Work Tools

Once your bot team has been created with the instructions and tools above.

It’s now time to create the Project.

​

Setting Up The Project And Creating The TODOs

First, let’s explain what a project is and what TODOs are.

Much like a real-world project manager, projects are a list of objectives to manage the workflow systematically. Below we will be creating a project named JAFFLE-Proj-20B.

Next, to explain what TODOs are.

The list of objective actions that are within a Project is known as TODO.

Reference Example:

A TODO item was added to the project with the following task:

• Research, validate, and document potential sources for the NUMBER_OF_ORDERS column in the DEMO_CUSTOMER_TGT table.

Reasoning: This ensures that the task is tracked, dependencies are managed, and progress is documented.

​

User Prompt

Please create and run project JAFFLE-Proj-20B with one todo item: Research, Validate, and Document Potential Sources for Mapping of the column name provided.
Before you start delegating to other bots, tell me what steps you plan to take. If a bot has trouble saving to git, follow up with it to have it try again and give it some pointers. If it fails a second time, save the results to git yourself and continue. Keep me informed as you take each step on what you're doing, but don't stop to ask me for permission to proceed.
Details:
1. Step 1: Delegate to Investigator to research potential data sources for the NUMBER_OF_ORDERS column in the DEMO_CUSTOMER_TGT table.
   - Details Provided:
- Table Name: DEMO_CUSTOMER_TGT
- Table Description: This table stores Jaffle customer data and lifetime purchase history.
- Column Name: NUMBER_OF_ORDERS               `
- Column Description: This column reports the total number of orders for a given customer.
- Save the research results in the git repository at: demoresearch/{table_name_field_name}_demoresearch.txt
2. Step 2: Validate the research results provided by Investigator and be sure the file is saved with updated contents from the bot before proceeding.
3. Step 3: Output the results of the work and evaluate the bot's output.
4. Step 4: Update the todo with the work performed and the current status.
5. Step 5: Delegate to Data Mapper (Bot ID: DemoMapper-w4kp8z) to propose a mapping from sources to the desired target provided by Investigator. Save the proposed mapping with justification to: demoresearch/{table_name_field_name}_demoproposal.txt
6. Step 6: Validate the mapping proposal provided by Data Mapper and be sure the file is saved with updated contents from the bot before proceeding.
7. Step 7: Delegate to Confidence Assessor to review the proposed mapping and save a confidence score demoresearch/{table_name_field_name}_demoQAscore.txt
8. Step 8: Output the results of the work and evaluate the bot's output.
9. Step 9: Update the todo with the work performed and the current status.
10. Step10: Validate the research results provided by Confidence Assessor and be sure the file is saved with updated contents from the bot before proceeding.
- Status: NEW
- Target Completion Date: 1999-12-31
Again, Keep me informed as you take each step on what you're doing, but don't stop to ask me for permission to proceed.

​

Bot Team in Action

Once the project is created, the team will begin working.

​

Delegating Research to the Investigator Bot

Objective: To identify potential data sources for the NUMBER_OF_ORDERS column.

Action: The Investigator bot was tasked with researching the DEMO_RAW database, focusing on the JAFFLE_SHOP and STRIPE schemas.

Process:

1. The bot analyzed metadata to locate relevant tables and columns.

2. It identified two potential sources:

Primary Source: DEMO_RAW.JAFFLE_SHOP.ORDERS

• Contains raw order data, including USER_ID (customer identifier) and ORDER_DATE.

• Suitable for fresh calculations by aggregating the USER_ID column.

Alternative Source: DEMO_RAW.STRIPE.DEMO_CUSTOMER_TGT

• Already contains the NUMBER_OF_ORDERS column, which might be pre-aggregated.

The bot documented its findings in a structured format.

Output: The research findings were saved in the Git repository at:

demoresearch/DEMO_CUSTOMER_TGT_NUMBER_OF_ORDERS_demoresearch.txt

Reasoning: Automating the research step ensures thorough and consistent analysis, saving time and reducing errors.

​

Validating Research Results

Objective: To ensure the research findings are accurate and complete.

Action: The research file was reviewed to confirm:

1. All relevant tables and columns were identified.

2. The findings aligned with the task objectives.

Output: The file was validated and deemed ready for the next step.

Reasoning: Validation ensures that the workflow proceeds with reliable data.

​

Delegating Mapping to the Data Mapper Bot

Objective: To propose a mapping for the NUMBER_OF_ORDERS column based on the research findings.

Action: The Data Mapper bot was tasked with:

1. Analyzing the research findings.

2. Proposing a mapping strategy.

3. Documenting the mapping proposal with justification.

Process:

1. The bot selected the ORDERS table as the primary source for fresh calculations.

2. It proposed the following mapping logic:

SELECT USER_ID, COUNT(*) AS NUMBER_OF_ORDERS
FROM DEMO_RAW.JAFFLE_SHOP.ORDERS
GROUP BY USER_ID;

The bot justified its choice:

The ORDERS table provides raw data, ensuring accurate and fresh calculations.

This approach aligns with the principle of deriving values from raw data rather than relying on pre-aggregated values.

Output: The mapping proposal was saved in the Git repository at: demoresearch/DEMO_CUSTOMER_TGT_NUMBER_OF_ORDERS_demoproposal.txt

Reasoning: Automating the mapping step ensures consistency and adherence to best practices.

​

Validating the Mapping Proposal

Objective: To ensure the mapping proposal aligns with the task objectives and is technically sound.

Action: The mapping file was reviewed to confirm:

1. The proposed logic was accurate and feasible.

2. The justification was clear and aligned with business requirements.

Output: The file was validated and deemed ready for the next step.

Reasoning: Validation ensures that the mapping logic is robust and reliable.

​

Delegating Confidence Assessment to the Confidence Assessor Bot

Objective: To evaluate the mapping proposal and provide a confidence score.

Action: The Confidence Assessor bot was tasked with:

1. Reviewing the mapping proposal.

2. Scoring the mapping based on five criteria: Business Intuition: Does the logic align with business expectations? Single Source Clarity: Is the source table well-defined and appropriate? Mapping Simplicity: Is the logic straightforward and easy to implement? Historical Similarity: Has similar logic been used successfully before? Documentation Availability: Is the mapping well-documented?

3. Documenting the confidence score and justification

Process:

1. The bot assigned a confidence score of 90/100.

2. It provided detailed justification for the score.

Output: The confidence assessment was saved in the Git repository at: demoresearch/DEMO_CUSTOMER_TGT_NUMBER_OF_ORDERS_demoQAscore.txt

Reasoning: Automating the assessment step ensures objective and consistent evaluation.

​

Updating the TODO Status

Objective: To document the work performed and mark the task as completed.

Action:

1. The TODO item was updated with a summary of the work performed.

2. The status was changed to COMPLETED.

Reasoning: Updating the TODO ensures that the workflow is tracked and documented for future reference.

Key Output: Below are the key outputs from each bot.

​

Tools Used and Their Purposes

_manage_projects

Purpose: To create and manage the project JAFFLE-Proj-20B.
Usage: This tool was used to set up the project framework, ensuring that the workflow was organized and tracked systematically. 

_manage_todos

Purpose: 
To create, update, and track TODO items within the project.
Usage:
1. Created a TODO item for researching, mapping, and validating the NUMBER_OF_ORDERS 2. column.
2. Updated the TODO status as the workflow progressed.
3. Marked the TODO as COMPLETED once all tasks were finished.

_delegate_work

Purpose: To delegate specific tasks to Genbots (e.g., Investigator, Data Mapper, Confidence Assessor).
Usage:
1. Delegated the research task to the Investigator bot.
2. Delegated the mapping task to the Data Mapper bot.
3. Delegated the confidence assessment task to the Confidence Assessor bot.

_git_action

Purpose: To manage files in the Git repository.
Usage:
1. Saved the research findings, mapping proposal, and confidence assessment results to the Git repository.
2. Validated the contents of the saved files to ensure accuracy and completeness.

_record_todo_work

Purpose: The tool is used To document the progress and results of the TODO item.
Usage:
1. Throughout this example the tool is used to record detailed descriptions of the work performed each step.
2. Logged the outputs (e.g., file paths) for transparency and traceability.

​

How These Tools Worked Together

• The project and TODO management tools ensured the workflow was structured and tracked.

• The delegation tool enabled specialized Genbots to handle specific tasks efficiently.

• The Git management tool provided a centralized location for saving and validating outputs.

• The work recording tool documented progress, ensuring transparency and accountability.