Ewance
← All challenges Data Architect

Designing a Data Warehouse for a Renewable Energy Firm

Duration
3 weeks
Level
Expert
Type
Design
Team
1 to 5 people

The situation

EcoGrid has 10 wind farms and 5 solar farms, each with dozens of turbines/panels. They report quarterly to regulators and need to optimize maintenance schedules.

Your task

Design a star schema data warehouse and ETL process to integrate disparate data sources for analytics and reporting.

The brief

What you do

  1. You are given sample data from three sources: energy production logs, weather data, and equipment maintenance records.
  2. Your task is to: (1) design a star schema with fact and dimension tables, (2) write SQL scripts to create the schema and load transformed data, (3) write analytical SQL queries for: monthly energy output per farm, average downtime per turbine, and correlation between weather and output, and (4) produce a dashboard mockup.
  3. Constraints: use PostgreSQL, no BI tools.

Success looks like

Success means a normalized star schema that supports the required queries.

What you will practice

  • Design star schemas for data warehousing
  • Implement ETL processes using SQL
  • Write complex analytical SQL queries (window functions, aggregations)
  • Understand trade-offs in dimensional modeling

A suggested way in

  1. Identify grain of fact tables early (e.g., per turbine per hour)
  2. Use slowly changing dimensions for equipment attributes
  3. Consider indexing strategies for query performance

Skills you will practice

Data warehousing, Star schema, ETL, SQL, Dimensional modeling

How it is assessed

  • Schema Design35%

    Correctness and completeness of the star schema.

  • ETL Implementation25%

    Effectiveness of SQL scripts for data transformation and loading.

  • Analytical Queries25%

    Accuracy and efficiency of the three analytical queries.

  • Documentation15%

    Clarity of report and dashboard mockup.

Resources

  • Google SheetsTool

    Quickly explore sample data and prototype transformations.

  • LucidchartTool

    Design star schema diagrams with drag-and-drop ease.

  • dbtTool

    Transform data in warehouse using SQL and version control.

  • TableauTool

    Build interactive dashboards for analytical queries.

  • NotionTool

    Organize requirements, design decisions, and documentation.

  • GitHub CopilotTool

    Assist in writing SQL scripts and documentation.

  • Google SlidesTool

    Create professional presentation for final report.

Sample Ewance certificate of completion, verified by LearnCoin
Sample certificate
Your proof

Finish it. Earn this.

A verified certificate with your name. Anyone can check it.

Time to practice.

Solve this challenge
Related challenges

More like this.