What a Mid-level } should know

Writes advanced analytical queries using window functions (ROW_NUMBER, LAG, LEAD, running totals) for trend analysis and ranking. Applies ClickHouse approximate algorithms like uniqHLL12 and quantileTDigest for fast estimations on large datasets. Builds cohort retention analyses at scale, leveraging arrays and higher-order functions.

Independently designs analytical data models with appropriate normalization levels. Implements materialized views and summary tables for recurring analysis patterns. Understands trade-offs between normalized and denormalized schemas for analytical workloads.

Designs indexes for analytical query patterns: composite indexes for multi-column filters, expression indexes for computed fields, and partial indexes for conditional aggregations. Analyzes query execution plans to identify missing indexes and index scan vs seek behavior. Understands index impact on ETL pipeline performance.

Writes complex analytical SQL with window functions (ROW_NUMBER, LAG, LEAD, running totals) for trend and cohort analysis in MySQL. Builds multi-step analysis pipelines using CTEs and temporary tables. Optimizes data extraction queries by analyzing EXPLAIN output and adding targeted indexes for analytical workloads.

Independently designs analytical queries and optimizes data extraction: writes complex CTEs and window functions for analytical workloads, understands execution plans for query tuning, uses EXPLAIN ANALYZE for bottleneck identification. Understands trade-offs between materialized views and live queries for analytical reporting.

Independently optimizes complex analytical queries: partition pruning for time-series analysis, query pushdown for distributed data sources, and efficient JOIN strategies for large table combinations. Uses query profilers to identify and resolve performance bottlenecks. Implements query caching strategies for recurring analytical patterns.

Independently builds Airflow DAGs for automated data extraction and cohort preparation pipelines. Implements data validation tasks with Great Expectations integration. Configures scheduling for recurring analytical data refreshes.

Independently builds analytical dashboards with advanced statistical visualizations and dynamic cohort analysis. Optimizes dashboard performance through query tuning and data extracts. Creates A/B test dashboards with significance indicators and confidence intervals.

Independently curates analytical dataset metadata in the catalog. Implements column-level descriptions and usage statistics tracking. Creates data dictionaries and glossary entries to improve discoverability for the analytics team.

Independently works with data contracts to ensure analytical dataset reliability. Defines schema expectations and data quality rules for analytical tables. Collaborates with data engineers on contract specifications for analytical use cases.

Independently uses lineage tools to trace analytical data flows and debug data quality issues. Implements lineage documentation for complex analytical pipelines. Performs impact analysis using lineage graphs before modifying shared datasets.

Builds automated validation pipelines using Great Expectations and dbt tests. Implements statistical anomaly detection for A/B testing datasets. Configures quality monitors in Airflow DAGs to catch upstream issues. Designs profiling reports with pandas-profiling and custom SQL checks.

Designs analytical schemas for specific business domains, choosing appropriate fact and dimension structures. Proposes new warehouse tables and views that improve query efficiency for recurring analysis patterns. Understands trade-offs between normalized and denormalized designs and selects the right approach based on analytical workload characteristics.

Independently builds dbt models for analytical datasets with proper testing and documentation. Implements Jinja macros for reusable transformation logic. Configures model materializations appropriate for analytical query patterns and data volume.

Implements efficient analytical pipelines with Pandas: multi-table join strategies, window functions with rolling/expanding, and time-series resampling for different granularities. Uses Polars for performance-critical transformations on large datasets. Creates parameterized analysis pipelines with proper error handling and data validation.

Builds SQL ETL pipelines for cohort extraction and analytical dataset preparation. Implements data cleaning transformations, handles missing values and outliers, and creates reusable ad-hoc data transformation templates.