What a Mid-level } should know

Designs materialized views in ClickHouse to pre-aggregate metrics for dashboard performance. Uses AggregatingMergeTree and SummingMergeTree engines to maintain real-time rollups. Writes complex queries with GROUP BY, HAVING, and nested subqueries to power interactive BI reports with sub-second response times.

Independently designs dimensional models for BI reporting. Implements slowly changing dimensions (SCD Type 1/2), conformed dimensions, and aggregate tables. Optimizes data models for dashboard query performance with proper indexing and partitioning.

Designs indexing strategies for analytical workloads: covering indexes for dashboard queries, partial indexes for filtered reports, and columnstore indexes for OLAP patterns. Understands query execution plans and can recommend index changes to DBAs. Balances index maintenance overhead with query performance gains.

Builds optimized reporting views and materialized summary tables in MySQL for BI dashboards. Tunes aggregation queries using composite indexes and query execution plans. Configures live and extract connections in Tableau and Power BI with proper MySQL driver settings for reliable scheduled refreshes.

Independently designs analytical schemas and optimizes complex queries: writes performant multi-table JOINs with window functions, understands query execution plans for optimization, implements materialized views for report acceleration. Understands trade-offs between normalized schemas and analytical denormalization for BI workloads.

Independently designs and optimizes analytical queries: window functions for running calculations, CTEs for query readability and reuse, and query decomposition for complex reports. Analyzes execution plans to choose between nested loops, hash joins, and merge joins. Optimizes materialized views for dashboard performance.

Independently configures Airflow DAGs for scheduled report generation and dashboard data refresh. Implements data quality sensors to validate source data before BI layer updates. Troubleshoots pipeline failures affecting reporting.

Independently designs interactive dashboards in Tableau and Power BI with calculated fields and LOD expressions. Optimizes query performance for large datasets. Implements self-service BI layers enabling business users to explore KPIs autonomously.

Independently manages BI layer metadata in the data catalog. Configures metric definitions, KPI hierarchies, and dashboard-to-source mappings. Implements data freshness indicators and quality badges for reporting datasets.

Independently manages data contracts for BI consumption layer. Defines metric contracts specifying aggregation rules, granularity, and freshness SLAs. Implements contract-based data validation before dashboard publication.

Independently traces data lineage from BI dashboards to source systems. Uses lineage tools to perform impact analysis before data source changes. Implements lineage-based documentation for metric calculation transparency.

Implements automated data quality checks in BI pipelines using SQL and dbt tests. Configures freshness and completeness monitors for Tableau/Power BI dashboards. Builds validation layers in ClickHouse and BigQuery to catch schema drift. Creates quality scorecards and alerting for key metrics.

Designs star and snowflake schemas optimized for BI reporting workloads. Creates aggregate tables and materialized views that significantly improve dashboard query performance. Proposes schema changes to the warehouse team based on reporting requirements and collaborates on dimensional modeling decisions for new data domains.

Independently writes dbt models for BI reporting layer: metric definitions, aggregate tables, and dimensional models. Implements schema tests and data freshness checks. Configures dbt exposures to document downstream dashboard dependencies.

Implements efficient BI data pipelines with Pandas: multi-source data merging, complex aggregation chains, and time-series analysis for trend detection. Optimizes memory usage with proper dtype selection and chunked reading for large files. Creates reusable data transformation functions for recurring analytics tasks.

Builds ETL pipelines that populate dimensional models for BI reporting. Implements SCD Type 1/2 loads, manages surrogate keys, and ensures referential integrity across fact and dimension tables in the warehouse.