Senior Data Engineer

What it does

This skill assists senior data engineering teams by analyzing and optimizing complex data pipelines. It generates configuration code for orchestration tools like Airflow, Prefect, and Dagster; validates data quality through profiling and anomaly detection; and provides actionable recommendations to improve the performance of SQL and Spark queries. The tool supports both generating pipeline configurations and validating existing ones.

When to use it

• Generating initial pipeline configurations for moving data between systems (e.g., PostgreSQL to Snowflake).
• Validating the quality of datasets against defined schemas, including detecting anomalies.
• Profiling datasets to understand their characteristics and identify potential issues.
• Optimizing slow-running SQL or Spark queries within a data warehouse environment.
• Estimating query costs for resource planning and optimization purposes.

Key capabilities

• Pipeline Generation: Generates code for Airflow, Prefect, and Dagster pipelines.
• Data Quality Validation: Validates data against schemas and detects anomalies using techniques like Great Expectations.
• Data Profiling: Provides insights into dataset characteristics.
• SQL/Spark Optimization: Analyzes and suggests improvements for SQL and Spark query performance.
• Cost Estimation: Estimates the cost of executing queries in environments like Snowflake and BigQuery.

Example prompts

• "Generate an Airflow DAG to incrementally load data from PostgreSQL into Snowflake, using tables 'orders' and 'customers'."
• "Validate the quality of data.csv against the schema defined in schema.json, and detect any anomalies."
• "Analyze this SQL query (query.sql) for performance bottlenecks and suggest optimizations for a Snowflake warehouse."

Tips & gotchas

• The skill utilizes command-line tools (e.g., pipeline_orchestrator.py, data_quality_validator.py). Ensure these scripts are accessible within the agent's environment.
• Subcommands support --json for machine-readable output and --output to write results to files.
• The skill is designed for senior data engineering tasks; familiarity with concepts like Airflow DAGs, SQL query optimization, and Spark is beneficial.