Loading…
Data Forge: The Lost Metrics
Recover a fictional company’s damaged analytics layer. Define the metrics before rebuilding the models, then show how each reported number traces back to an explicit grain and tested transformation.
A warehouse migration has left the business with misaligned foreign keys, incomplete fact tables, and several competing definitions of revenue, average order value, and active customers.
Your job is to restore the analytics layer without treating the first query that runs as the answer. You must decide what each metric measures, model the underlying events at a defensible grain, and make the final definitions reusable.
This is a conceptual modeling brief, not a downloadable source package. The project asks you to define and defend the target grain before implementing the star.
Write a metric contract for average order value. State the qualifying order population, whether discounts and refunds are included, the date used for reporting, the numerator, the denominator, and the grain of the model that supplies each input. Then explain why AVG(order_total) may disagree with SUM(net_revenue) / COUNT(DISTINCT order_id).
Definitions for revenue, average order value, active customers, and repeat rate, including grain, filters, dates, and calculation rules.
A documented star centered on fact_order_line with conformed date, customer, product, and channel dimensions.
A serving model that aggregates compatible measures at one row per customer and month.
Checks for keys, relationships, accepted values, and totals that trace from the fact model into the final mart.
A concise explanation of ambiguous requirements, selected definitions, trade-offs, and limitations.
Enroll in Analytics Engineering Mastery for the complete implementation workspace, worked solution, contextual tutor, production-style curriculum, and human-reviewed capstone.
Measures aggregate from a declared grain and reconcile to their documented inputs.
Statuses, dates, filters, refund handling, and customer populations are visible and defensible.
Tests protect primary keys, relationships, accepted values, and business rules.
Staging, intermediate logic, facts, dimensions, and marts have distinct responsibilities.
A reviewer can understand what changed, why it changed, and what the final numbers do not claim.