Silo Operations Analytics

Executive summary

An end-to-end analytics pipeline built on PostgreSQL with dbt, joining three source systems that share no data. Eighteen models across staging, intermediate, and mart layers, a slowly changing dimension snapshot, one incremental model, and 54 automated tests. The lineage graph and column-level catalog are published and browsable.

Business context

The dataset is themed around the television series Silo, but the problem it models is ordinary: four departments each keep their own records and none of them can answer a question that crosses a boundary. Supply needs occupancy per level per month to allocate rations. Mechanical needs to see generator degradation before something fails. Works needs headcount against establishment. Three of those four questions require knowing what was true in the past, not what is true now.

Data and preparation

The source data was generated with eleven deliberate defects modelled on real pipeline failures: duplicate rows from failed replication, four casings of the same status value, empty strings that pass null checks, sensor faults recording negative output, department codes absent from the reference table, and 767 readings arriving up to nine days after the timestamp they carry. Staging handles each one explicitly rather than filtering the evidence away.

Method

Sources are declared with descriptions, primary-key tests, and freshness thresholds. Staging cleans one raw table per model with no joins. Intermediate models carry the joins, date spines, and aggregation so the marts stay legible. A snapshot preserves job history the source system overwrites in place. The incremental model's lookback window was sized against a measured worst-case sync lag rather than guessed, and verified idempotent across repeat runs.

Results, limits, and next step

Four marts answer the four stakeholder questions, and the generator model surfaces a measurable 3.3 percent output decline over three years with a per-unit crossing date for maintenance planning. The most useful finding was a failure: every structural test passed while the occupancy model reported zero population change across three years, because residents were joined to their current dwelling rather than where they lived at the time. Rebuilding it against reconstructed relocation history corrected it. Tests confirm shape, not meaning. The next step is scheduled orchestration and continuous integration on pull requests.