What a Senior } should know

Applies algorithmic thinking to analytics pipeline optimization: query execution plan analysis for complex aggregations, incremental computation algorithms for large dataset processing, partitioning strategies based on data distribution patterns. Designs efficient data transformation algorithms for warehouse workloads.

Designs code quality standards for analytics codebases: dbt model organization, SQL style guides, data transformation testing patterns. Refactors complex SQL pipelines for readability and performance. Implements automated linting for dbt projects and warehouse-specific SQL dialects.

Selects optimal data structures for analytics platforms: materialized views, pre-aggregation tables, columnar partitioning strategies for warehouse queries. Optimizes dbt model DAG structure for incremental processing. Designs dimension/fact table schemas for query performance across large analytical workloads.

Integrates Kafka streams as data sources for analytical models. Configures consumer applications for capturing events into the warehouse staging layer. Works with Schema Registry for ensuring schema compatibility in downstream models.

Configures Elasticsearch as a search engine for the data catalog and discovery platform. Indexes dbt model metadata, table and column descriptions for full-text search by analysts.

Develops internal data APIs on FastAPI for accessing analytics platform metrics and metadata. Creates endpoints for programmatic access to dbt artifacts, data quality check results, and the model catalog.

Uses Redis for caching heavy analytical query results and lookup tables. Implements TTL strategies to ensure freshness of cached metrics in BI dashboards. Configures invalidation on dbt model updates.

Designs analytical tables in ClickHouse: engine selection (MergeTree, AggregatingMergeTree), partition keys for time-series data. Configures materialized views for real-time metric aggregations.

Designs complex analytical models: wide tables for BI tools, activity schema for event analytics, bridge tables for many-to-many relationships. Optimizes the dbt DAG: eliminates circular dependencies, minimizes compute through proper layering.

Optimizes indexing at the analytical warehouse level: clustering keys in Snowflake, partition pruning in BigQuery, sort keys in Redshift. Architects indexing strategy for accelerating typical BI queries.

Architects database systems with Database Migrations for high-load systems. Optimizes performance, configures replication and sharding.

Optimizes PostgreSQL as a data source for the analytics platform: configures logical replication for CDC, partitioning for historical data. Designs schemas for efficient data extraction into dbt models.

Performs deep optimization of analytical queries: refactoring the dbt DAG to reduce compute costs, configuring warehouse-specific optimizations (Snowflake warehouse sizing, BigQuery slot allocation). Implements query tagging for cost attribution.

Architects the data API documentation system for the analytics platform: OpenAPI specifications for internal endpoints, auto-generation of documentation from dbt artifacts. Implements standards for describing data semantics in APIs.

Develops a reusable framework for analytics platform API integrations: generic extractors configured via YAML, schema inference, automatic data type mapping. Designs error handling and alerting for API sources.

Architects the AWS analytics platform: Redshift Serverless for cost optimization, Lake Formation for data governance, Step Functions for ELT orchestration. Configures cross-account data access for different teams.

Architects a containerized environment for the analytics platform: multi-stage builds for dbt projects, image optimization for CI/CD, dbt package caching. Configures containerization for custom data connectors and quality tools.

Architects the full CI/CD pipeline for the analytics platform: staging deploy on PR, production deploy on merge, automated regression tests. Configures dbt Cloud integration or custom GitHub Actions for blue-green model deployment.

Architects analytics platform integration tests: end-to-end verification from sources to BI dashboards, data reconciliation between warehouse layers. Automates dbt testing with real data in the staging environment.

Architects the unit testing strategy for dbt projects: dbt unit tests for critical transformations, pytest for custom Python extractors. Implements test fixtures and factories for generating test data covering real analytical scenarios.

Architects data systems with Apache Airflow. Optimizes for big data. Implements data governance and quality frameworks.

Defines BI development standards: semantic layer / LookML / Tableau data models for metric consistency, dashboard templates for typical business tasks. Optimizes dbt model and BI interaction through extract-based or live connection approaches.

Architects data systems with Dagster / Prefect. Optimizes for big data. Implements data governance and quality frameworks.

Architects data systems with Data Catalog. Optimizes for big data. Implements data governance and quality frameworks.

Architects data systems with Data Contracts. Optimizes for big data. Implements data governance and quality frameworks.

Architects data systems with Data Lake Architecture. Optimizes for big data. Implements data governance and quality frameworks.

Architects data systems with Data Lineage. Optimizes for big data. Implements data governance and quality frameworks.

Architects the data quality strategy for the analytics platform: multi-layer testing (source → staging → marts), anomaly detection via dbt + elementary, automated alerting. Integrates quality checks into the CI/CD pipeline.

Architects data systems with Data Warehouse Design. Optimizes for big data. Implements data governance and quality frameworks.

Architects data systems with dbt. Optimizes for big data. Implements data governance and quality frameworks.

Architects Python pipelines for data that is difficult to process with pure SQL: NLP text processing, geocoding, complex regex parsing. Optimizes pandas/polars for processing millions of rows: chunked reading, lazy evaluation in polars.

Architects optimal SQL transformations for the analytical warehouse: decomposing complex logic into intermediate models, warehouse-specific optimizations (Snowflake QUALIFY, BigQuery STRUCT). Creates reusable dbt macros for common patterns.

Optimizes Copilot workflow for analytics tasks: generating complex SQL transformations, dbt macros, Jinja templates. Configures Copilot workspace with dbt project context for more accurate suggestions. Combines with ChatGPT for model design.

Architects observability for the analytics platform: dbt Cloud/Elementary for model monitoring, structured logging for custom extractors, alerting on data anomalies. Integrates dbt run metadata with Datadog/Grafana.

Conducts architectural reviews of dbt projects: evaluates model design, correct layering (staging/intermediate/marts), reusability. Reviews impact analysis for changes to shared models. Mentors through detailed review explanations.

Architects the analytics platform documentation system: integrating dbt docs with the data catalog, automated freshness and lineage documentation, business term glossary. Implements documentation-as-code with CI validation for description completeness.

Architects Git workflows for the analytics team: branching strategy for dbt (trunk-based vs gitflow), protected branches with required CI checks. Configures Git hooks for sqlfluff linting and dbt compile validation before commit.