data-engineer_skill

Overview

This skill teaches practical data warehouse design with a focus on star schema, dimensional modeling, fact and dimension table patterns, and enterprise best practices. It includes complete schema examples, SCD strategies, aggregate patterns, bridge tables, and data quality checks to accelerate reliable analytics delivery. Ideal for analysts and engineers designing scalable analytical databases.

How this skill works

The skill walks through canonical schema definitions for fact and dimension tables, index recommendations, and conformed dimensions to enable consistent joins across facts. It documents implementations for slowly changing dimensions (Types 1, 2, and 3), many-to-many bridge tables, and materialized aggregates for performance. It also includes SQL snippets for common data quality metrics and refresh workflows for aggregate views.

When to use it

• Designing a new analytics schema for reporting or BI tools
• Standardizing dimensions across multiple fact tables in an enterprise
• Implementing history capture for changing master data using SCD patterns
• Optimizing query performance with aggregate/materialized views
• Modeling many-to-many relationships between business entities

Best practices

• Use conformed dimensions (e.g., dim_date) to ensure consistent time joins across facts
• Index foreign keys on fact tables to improve join performance
• Choose SCD type based on audit needs: Type 1 for simple overwrite, Type 2 for full history, Type 3 for limited historical attributes
• Pre-aggregate high-cardinality queries into materialized views and refresh on a schedule
• Validate load quality with automated metrics: null checks, negative values, distinct counts, and last load timestamp

Example use cases

• Retail sales warehouse: fact_sales joined to dim_date, dim_product, dim_customer, dim_store for daily reporting
• Customer history tracking: implement Type 2 SCD to preserve past customer attributes for lifetime value analysis
• Product taxonomy: use bridge_product_category to represent products in multiple categories and aggregate category metrics
• Performance tuning: create sales_summary_daily materialized view to serve dashboard queries instead of scanning fact tables
• Data quality monitoring: scheduled queries that surface nulls, negative sales, and load timing anomalies