What a Mid-level } should know

Writes complex analytical queries using ClickHouse-specific functions: arrayJoin, windowFunnel, retention. Optimizes queries through proper ORDER BY key selection and PREWHERE usage for filtering.

Designs dbt models by layer: staging for raw data cleansing, intermediate for business logic, marts for consumers. Applies dimensional modeling (Kimball) for analytical marts. Implements SCD Type 2 for historical dimensions.

Analyzes query execution plans to determine necessary indexes in data sources. Creates composite indexes for typical analytical patterns: date filtering + dimension. Understands the trade-off between read and write speed.

Independently designs schemas and optimizes queries with Database Migrations. Understands indexing and execution plans. Uses ORM effectively.

Creates complex analytical queries with CTEs, window functions, and subqueries in PostgreSQL. Uses EXPLAIN ANALYZE for profiling queries on large tables. Works with PostgreSQL-specific types: JSONB, ARRAY, INTERVAL.

Optimizes dbt models and SQL queries: rewrites subqueries as CTEs, eliminates redundant JOINs, uses incremental strategies for heavy models. Analyzes query profiles in Snowflake/BigQuery to identify bottlenecks.

Independently builds Airflow DAGs for ELT pipelines with dbt operators and data quality checks. Configures retry policies, SLAs, and alerting for transformation jobs. Optimizes task parallelism and resource pools.

Designs analytical dashboards with correct business logic: metric calculation at the BI vs dbt level, parameterized reports, cross-filtering. Optimizes dashboard performance through proper data modeling in the mart layer.

Independently implements data pipelines with Dagster / Prefect. Optimizes performance. Ensures data quality.

Independently maintains data catalog entries for transformation layer. Configures automated metadata extraction from dbt docs and lineage graphs. Implements tagging taxonomies and data classification for governed self-service access.

Independently defines data contracts for transformation layer outputs using dbt contracts and schema tests. Implements automated contract validation in CI/CD pipelines. Negotiates contract changes with upstream data producers.

Independently builds analytics data products on top of data lake layers using dbt and Spark SQL. Optimizes query performance through intelligent partitioning and Z-ordering. Ensures data quality with Great Expectations checks at zone boundaries.

Independently implements data lineage tracking across dbt transformation layer. Configures column-level lineage with dbt metadata and external lineage tools (OpenLineage, DataHub). Automates impact analysis for schema changes.

Configures comprehensive dbt testing: custom generic tests, dbt expectations package for statistical checks, freshness tests for sources. Implements data quality dashboards for monitoring quality metrics.

Designs dimensional models and semantic layers that serve multiple downstream consumers. Builds reusable dbt packages with proper materialization strategies, incremental models, and well-documented data marts. Implements slowly changing dimensions and manages schema evolution without breaking existing analytics pipelines.

Independently builds dbt transformation pipelines with incremental models, snapshots, and custom macros. Implements data quality tests with dbt-expectations and dbt-utils packages. Configures materializations and optimizes model performance.

Applies pandas/polars for complex data preprocessing: merging heterogeneous sources, pivot tables, time series processing. Uses polars to accelerate local processing of large files before loading into the warehouse.

Develops complex SQL transformations in dbt: window functions for metric calculation, CTE chains for multi-step business logic, Jinja macros for DRY approach. Implements incremental models with merge strategy for optimization.