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
- You are given sample data from three sources: energy production logs, weather data, and equipment maintenance records.
- 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.
- 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
- Identify grain of fact tables early (e.g., per turbine per hour)
- Use slowly changing dimensions for equipment attributes
- 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.
Finish it. Earn this.
A verified certificate with your name. Anyone can check it.
Time to practice.
Solve this challengeMore like this.
- Software EngineerGreedy Delivery-Slot Assignment for a Munich Grocery Startup
- Software EngineerDesign a Verified UART Receiver and Loopback for an FPGA Sensor Hub
- Database AdministratorCut a 42-Minute Trino Query to Under Five Minutes
- AI Solutions ArchitectBuild an OWL Ontology for a Pharma R&D Knowledge Base
-
Greedy Delivery-Slot Assignment for a Munich Grocery Startup Software Engineer Using the provided order history sample, rider roster, and manual dispatch baseline, design and implement a greedy slot-assignment algorithm in Python greedy algorithmsalgorithm analysispython Open challenge → -
Design a Verified UART Receiver and Loopback for an FPGA Sensor Hub Software Engineer Propose and deliver a UART receiver for Helax's sensor hub that meets the functional requirements in the requirements brief (the 'uart-requirements-br veriloguartfpga Open challenge → -
Cut a 42-Minute Trino Query to Under Five Minutes Database Administrator Using the slow query, the EXPLAIN ANALYZE readout, the Iceberg table schemas, and the 30-day execution-metrics dataset provided, design an optimizatio trinosql optimizationiceberg Open challenge → -
Build an OWL Ontology for a Pharma R&D Knowledge Base AI Solutions Architect You receive a CSV-form starter knowledge base (around 4,000 compounds, 600 targets, 1,200 assays) and a list of 12 competency questions the scientists ontology designowlknowledge representation Open challenge →