Define metrics in MetricFlow and enforce schema contracts

Step 1: Read the MetricFlow guide

Open materials/metricflow-guide.md. MetricFlow is a semantic layer framework for dbt. It lets you define a metric once -- the formula, the filters, the aggregation -- so every consumer gets the same calculation.

The key concept: a MetricFlow definition is a contract. When you define "availability" in MetricFlow, every dashboard, ad hoc query, and downstream report that queries "availability" uses your formula. If the formula is wrong, every consumer inherits the error. Nobody gets a different number. Nobody can fix it locally. The definition is the single source of truth -- and if the source is wrong, everything downstream is wrong.

Read the guide through the Verification section. The common pitfalls section is worth attention -- especially the first one: "formula that compiles but calculates wrong."

Step 2: Clarify metric definitions with Katrine

Before writing any MetricFlow configuration, you need to know exactly what Katrine means by "availability" and "capacity factor." These are business definitions, not technical ones.

Message Katrine. Ask her to define these metrics precisely. She'll respond with structured clarity:

Availability: the turbine is "available" when it's capable of generating power. Scheduled maintenance doesn't count as downtime -- she plans for that. Unscheduled outages do count. The formula: (total hours minus unscheduled downtime hours) divided by total hours.

Capacity factor: actual energy output divided by maximum possible output if the turbine ran at rated capacity the whole time.

Notice the availability definition. Scheduled maintenance is excluded. This is not a technical detail -- it changes the number by several percentage points. If you include scheduled maintenance as downtime, every farm looks worse than it actually is.

Katrine makes this distinction deliberately: "When I report 92% availability, the farm owners know that means 92% of the time the turbine was ready to generate. If I included scheduled maintenance, every farm would look worse than it is and the owners would panic."

Step 3: Define metrics in MetricFlow

Direct Claude to define "availability" and "capacity factor" in MetricFlow. Feed Claude Katrine's definitions and the maintenance logs data structure.

Check what Claude produces against Katrine's exact words. AI commonly gets metric definitions wrong in a specific way: the formula compiles, the query returns a number, but the number doesn't match the business definition. For availability, check whether Claude's definition excludes scheduled maintenance from the downtime calculation. If the formula is (total_hours - all_downtime) / total_hours instead of (total_hours - unscheduled_downtime) / total_hours, it contradicts what Katrine said.

The difference is not academic. With the wrong formula, a farm with 92% real availability gets reported as 86%. The number is plausible. Nobody questions a plausible number. They just make business decisions based on it.

If your Claude session has been running a long time with SCD design discussion, staging model code, and dimension building filling the context, this is a good moment to evaluate whether to start a fresh session. Context degradation shows up as Claude contradicting conventions it followed earlier, or addressing a subset of requirements after previously addressing all of them. If you notice either pattern, consolidate your key context (CLAUDE.md, the metric definitions, the relevant schema) and start fresh.

Step 4: Implement dbt model contracts

Now enforce schema contracts via dbt model contracts. These go in your schema.yml file -- specifying column types and constraints that break the build if violated.

Direct Claude to add model contracts to the fact table and the SCD dimension. AI commonly generates overly strict constraints -- setting not_null on columns that legitimately contain nulls. In the SCADA data, nacelle_temp_c has sensor dropout (~2% nulls) and pitch_angle has occasional nulls. A not_null constraint on these columns produces false build failures.

Review each constraint Claude generates. For every not_null, ask: can this column legitimately be null? For every type enforcement, ask: does the source data always conform?

Step 5: Fix false build failures

Run dbt build with contracts enforced. If any tests fail because of over-constrained columns, remove the offending constraints and run again.

The goal is a schema contract that catches real violations without rejecting valid data. A column documented as FLOAT that silently accepts strings from a source change should break the build. A nullable sensor column that occasionally drops out should not.

Step 6: Verify the metric output

Query "availability" for Farm DK-01, Q1 2024:

SELECT farm_id,
       ROUND((total_hours - unscheduled_downtime_hours) / total_hours * 100, 1) as availability_excl_scheduled,
       ROUND((total_hours - all_downtime_hours) / total_hours * 100, 1) as availability_incl_all
FROM (
    SELECT farm_id,
           SUM(total_hours) as total_hours,
           SUM(CASE WHEN maintenance_type = 'unscheduled' THEN duration_hours ELSE 0 END) as unscheduled_downtime_hours,
           SUM(duration_hours) as all_downtime_hours
    FROM maintenance_summary
    WHERE farm_id = 'DK-01' AND quarter = 'Q1 2024'
    GROUP BY farm_id
);

Compare the MetricFlow result to a hand calculation that includes scheduled maintenance. The two numbers should be different. The MetricFlow result (excluding scheduled maintenance) should be higher -- around 91-94%. The hand calculation including all maintenance should be lower -- around 85-88%. If the two numbers are the same, the MetricFlow definition didn't correctly exclude scheduled maintenance.