multi-step-data-transformation-and-analytics-orchestration

# Skill: multi-step-data-transformation-and-analytics-orchestration ## 1. Capability Definition & Real Case * **Professional Definition**: The ability to architect and complete enterprise data-workflow and ELT tasks by configuring infrastructure-as-code (e.g., Airbyte via Terraform), orchestrating data movements across heterogeneous sources, and authoring complex transformation logic using modeling tools (e.g., DBT). This involves navigating project codebases, resolving connection-layer discrepancies, sequencing multi-step dependencies, tracking synchronous/asynchronous states, and verifying final artifact correctness. * **Dimension Hierarchy**: Data and ML Workflow Engineering->Enterprise Data Workflow Coding->multi-step-data-transformation-and-analytics-orchestration ### Real Case **[Case 1]** * **Initial Environment**: A project workspace containing DBT-style project files, warehouse interfaces, schema docs, and existing macros. The business requires a daily dashboard combining raw transaction tables, lead events, and status definitions. * **Real Question**: Implement the required project-level transformation so the daily sales activity output can be generated correctly from the existing project context using DBT. * **Real Trajectory**: The agent inspects upstream macros and intermediate models. It adds a staging file for cleanup. It drafts the final fact-table model joining intermediate models. It executes target query steps locally via dbt run, inspects partial outputs to fix a grain-explosion bug caused by a fan-out join, and finalizes the transformed pipeline. * **Real Answer**: A completed multi-layer DBT transformation pipeline producing the accurate daily aggregated sales artifacts out of raw warehouse layers. * **Why this demonstrates the capability**: The final answer emerges from sequential workflow orchestration managing cascading cumulative transformations and intermediate debugging schemas, proving advanced data analysis. --- **[Case 2]** * **Initial Environment**: A multi-source infrastructure project base comprising Airbyte Terraform manifests, a destination Snowflake cloud warehouse, and a source Postgres database credentials file with mismatched provider keys. * **Real Question**: Build an end-to-end pipeline loading Postgres data into Snowflake via Airbyte Terraform configuration, then authoring a SQL model to rank top performers. * **Real Trajectory**: The agent maps credential inputs logically against the Terraform provider docs. It drafts Terraform manifesting files, triggers an execution, and utilizes a monitoring utility to await Airbyte ingestion confirmation. Observing successful physical replication, it implements complex dimensional ranking window functions within DBT and confirms output matching. * **Real Answer**: A resilient configuration executing extraction, asynchronous monitoring, and complex data-modeling transformation uniformly integrated. * **Why this demonstrates the capability**: Tests the complete 'Extract, Load, Transform' lifecycle, demanding infrastructure schema audits alongside database engineering and ensuring multi-application tool synchronization. ## Pipeline Execution Instructions To synthesize data for this capability, you must strictly follow a 3-phase pipeline. **Do not hallucinate steps.** Read the corresponding reference file for each phase sequentially: 1. **Phase 1: Environment Exploration** Read the exploration guidelines to discover raw knowledge seeds: `references/EXPLORATION.md` 2. **Phase 2: Trajectory Selection** Once Phase 1 is complete, read the selection criteria to evaluate the trajectory: `references/SELECTION.md` 3. **Phase 3: Data Synthesis** Once a trajectory passes Phase 2, read the synthesis instructions to generate the final data: `references/SYNTHESIS.md`