Roles · Data Engineer · Mid-level
25 core skills, 64 in total. Expectations per skill, and what changes at the next level.
This page lists what a Mid-level } is expected to know and do, skill by skill. Core skills are the ones a manager and peers assess in a review cycle; the rest count only in self-assessment. Main areas: Database Management, Cloud & Infrastructure, Data Engineering.
Grouped by area. The label on the right is the expected depth: Awareness, Working, Advanced or Expert.
Designs Cassandra data models optimized for query-driven access patterns. Implements efficient batch operations and manages TTL-based data lifecycle. Tunes read/write consistency levels to balance latency and durability.
Configures backup for data pipeline artifacts: intermediate data versioning in S3, point-in-time recovery in PostgreSQL. Implements rollback mechanisms for ETL processes.
Designs ClickHouse tables for analytical pipelines: engine selection (MergeTree, AggregatingMergeTree, ReplacingMergeTree), partitioning by date, materialized views for pre-aggregation. Optimizes insertion through batch inserts.
Designs dimensional models: Kimball methodology (conformed dimensions, bus matrix), Data Vault (hubs, links, satellites). Applies SCD Type 2 with effective dates. Models semi-structured data.
Designs indexing strategy for ETL sources: partial indexes for active records, covering indexes for frequent extractions. Understands read/write performance trade-offs in OLTP sources.
Designs schema evolution for data pipelines: backward-compatible migrations, expand-contract for zero-downtime, versioning through Flyway/Alembic. Handles schema drift in sources.
Optimizes extraction from PostgreSQL: COPY for bulk export, cursor-based pagination, partitioned tables. Configures logical replication for CDC. Designs staging tables for ETL.
Optimizes extraction and transformation: predicate pushdown, partition pruning, choosing between JOIN and subquery. Profiles SQL queries in Airflow through query tags. Optimizes Spark SQL execution plans.
Configures and manages database replication for data pipelines: sets up read replicas for ETL offloading, handles schema migrations across replicated environments, and implements change data capture (CDC). Understands consistency trade-offs and designs data flows accounting for replication lag.
Configures network connectivity for data infrastructure: VPC peering for cross-account access, PrivateLink for managed services, security groups for data pipeline components. Diagnoses connection issues.
Designs Airflow DAGs: dynamic task generation, XCom for data passing, TaskGroups for organization. Uses sensors, hooks for external system integration. Configures connections and variables.
Independently implements Spark data pipelines: optimizes shuffle operations and partitioning strategies, implements Structured Streaming for real-time ETL, manages Delta Lake tables with ACID transactions. Tunes Spark configurations for memory, parallelism, and cost efficiency.
Independently implements data pipelines with Dagster/Prefect. Optimizes performance. Ensures data quality.
Configures data catalog: integration with metadata sources (Hive, Glue, dbt), automated harvesting. Creates business glossary. Tags data for classification (PII, financial).
Creates data contracts: YAML/JSON schema definitions, quality checks, SLA metrics. Integrates contract validation into CI/CD. Configures alerting on contract violations.
Independently designs ETL pipelines across data lake zones with schema evolution support. Optimizes storage costs using lifecycle policies, compaction, and tiered storage. Implements data quality gates between medallion layers with automated validation.
Configures automated lineage collection: Airflow/dbt/Spark integration with lineage system. Uses lineage for debugging data quality issues. Visualizes dependencies in DataHub/OpenMetadata.
Configures data quality framework: Great Expectations/Soda for automated checks, custom expectations, alerting on failures. Monitors data freshness and volume anomalies.
Designs DWH components: dimensional modeling per Kimball, SCD Types (1, 2, 3), aggregate tables. Configures incremental loading. Optimizes performance through distribution keys and sort keys.
Designs dbt project: custom macros, incremental models, snapshots for SCD Type 2. Configures environments (dev/staging/prod). Optimizes models through materialization selection.
Independently implements data pipelines with Delta Lake/Apache Iceberg. Optimizes performance. Ensures data quality.
Optimizes processing through pandas/Polars: chunked reading for large files, category dtype for memory, vectorized operations instead of iterrows. Migrates to Polars for performance-critical tasks.
Designs SQL transformations: stored procedures for complex ETL, parameterized queries, temp tables for intermediate computations. Optimizes execution plans. Manages transaction control.
Builds real-time ETL pipelines with Kafka Streams for data transformation and enrichment. Implements exactly-once semantics and monitors consumer lag across processing stages.
Adds custom metrics to applications (counter, gauge, histogram). Writes PromQL queries for dashboards. Creates Grafana dashboards. Configures basic alerts (high error rate, high latency).
Not assessed by the team, but part of the self-assessment and the development plan.
64 skills get a higher expectation or become core when moving from Mid-level to Senior. The biggest jumps first.
} in the open competency matrix: 64 skills across 5 levels. The matrix is free for individuals and stays free.