snowflake-database-analyzer

---
name: snowflake-database-analyzer
description: When the user needs to explore, analyze, and manipulate Snowflake database structures and data. This skill provides 1) Listing databases, schemas, and tables within Snowflake environments, 2) Describing table structures including column names, data types, and constraints, 3) Executing read queries to extract employee data, task assignments, and status information, 4) Performing write operations to insert new records or update existing data, 5) Joining tables to establish relationships between employees, managers, and tasks, 6) Querying current dates and comparing with deadlines. Triggers include 'Snowflake database', 'query employee data', 'database schema exploration', 'data extraction from tables', 'database updates'.
---
# Instructions

## Core Workflow
This skill orchestrates a multi-step process to manage employee onboarding and task reminders via a Snowflake database. Follow these steps precisely.

### 1. Initial Exploration & Context Gathering
*   **Objective:** Understand the database landscape and locate the relevant onboarding document.
*   **Actions:**
    1.  List all available Snowflake databases. Identify the one likely containing employee data (e.g., `LANDING_TASK_REMINDER`).
    2.  List the contents of the `/workspace/dumps/workspace` directory to find the onboarding PDF (e.g., `landing_tips.pdf`).
    3.  Read the PDF to extract the standardized task list, deadlines (D+7, D+30, etc.), and group assignments (Backend, Frontend, Testing, Data).

### 2. Database Schema Investigation
*   **Objective:** Map the database structure to understand how data is organized.
*   **Actions:**
    1.  List all schemas within the target database.
    2.  List all tables within the target schema (e.g., `PUBLIC`).
    3.  Describe the key tables to understand their columns:
        *   `EMPLOYEE` (ID, Name, Email, Manager_ID)
        *   `EMPLOYEE_LANDING` (Employee_ID, Landing_Date, Task_Assigned_Flag)
        *   `PUBLIC_TASKS` (Employee_ID, Task_Name, Deadline, Status)
        *   Group-specific task tables (`GROUP_TASKS_BACKEND`, `GROUP_TASKS_FRONTEND`, etc.)

### 3. Data Analysis & Logic Building
*   **Objective:** Identify new employees needing onboarding and existing employees with overdue tasks.
*   **Actions:**
    1.  Query the `EMPLOYEE` and `EMPLOYEE_LANDING` tables. Cross-reference to find employees where `LANDING_TASK_ASSIGNED = FALSE`. These are the new hires.
    2.  For each new hire, determine their group/manager by joining the `EMPLOYEE` table with itself (manager lookup via `REPORT_TO_ID`). Infer group from manager ID based on historical data from group task tables.
    3.  Get the current date (`CURRENT_DATE()`).
    4.  For each task table (`PUBLIC_TASKS`, `GROUP_TASKS_*`), query for records where `FINISHED_FLAG = FALSE` AND `DDL < CURRENT_DATE()`. Join with employee/manager data for context.

### 4. Data Manipulation & Task Creation
*   **Objective:** Create task records for new employees and update their status flags.
*   **Actions:**
    1.  For each new employee, calculate deadlines based on their landing date and the rules from the PDF (e.g., Public Tasks: D+7, Backend Tasks: D+30).
    2.  Execute `INSERT` statements into `PUBLIC_TASKS` and the appropriate `GROUP_TASKS_*` table with the task names, calculated deadlines, and `FINISHED_FLAG = FALSE`.
    3.  Update the `EMPLOYEE_LANDING` table, setting `LANDING_TASK_ASSIGNED = TRUE` for the processed new hires.

### 5. Communication & Notification
*   **Objective:** Send formatted emails to employees and their managers.
*   **Actions:**
    1.  **For New Employees (Onboarding):** Generate an email for each new hire. Use the exact format:
        

• SKILL.md 4 KB
• references/email_templates.md 1 KB
• references/task_schema.md 2 KB