Data Warehouse Architecture

The Wasteology data warehouse is a PostgreSQL + dbt conformed-star model for waste-management analytics. Five source systems land through a staged DAG — stage → intermediate → mart → reporting — into conformed dimensions, per-source facts, a profitability mart, and a MetricFlow semantic layer. A build-time governance layer re-applies manual corrections on every rebuild.

The warehouse integrates cieTrade (operational system of record), the WDP master-data layer, Enevo IoT sensors, QuickBooks Online (financial), and the SOF market-classification reference. There are 76 active models (77 model files; int_cietrade__account_locations is kept disabled), 5 seeds, 11 macros, and 9 singular tests.

This page reflects the conformed-star refactor

Model names here are the current conformed-star schema (dim_customer, dim_material, fct_weight_measured, …). Pre-refactor names (dim_account, fct_weight, cietrade_raw, dbt_prod) no longer exist. Source of truth is the dbt repo's lat.md/ knowledge graph.

---

Layer model

The DAG has four model layers plus a semantic layer. Each layer has a single responsibility and a default materialization set in dbt_project.yml; deviations are deliberate and documented below.

Layer	Path	Role	Default materialization
Stage	models/stage/	Source-aligned cleanup — cast, normalize, nullify sentinels. No business logic.	view
Intermediate	models/intermediate/	Joins, dedup, fallback chains, hierarchy resolution, reconciliation.	table
Mart	models/mart/	Conformed dimensions + per-source fact tables (the conformed star).	table
Reporting	models/reporting/	Presentation layer above the star — the profitability mart.	table
Semantic	models/semantic_models/	MetricFlow time spine + semantic-model entries over facts and dims.	n/a

The end-to-end flow — sources → stage → intermediate → mart → reporting → semantic, with governance feeding the facts — is shown in the architecture infographic at the top of this page.

---

Conformed-star posture

Descriptive text columns derivable from a conformed dimension are dropped from facts; FK ids and grain-piece references are retained. Consumers join facts to dims for names. This is the load-bearing rule behind the fact/dim split.

• dim_customer is a single-FK dimension at location grain — facts join on location_id only, with parent_account_id / account_id riding along as denormalized attributes. relationships tests are configured on location_id only.
• dim_date uses integer YYYYMMDD keys (service_date_id → date_id) to avoid type-coercion joins. date_value exists for direct comparisons but is not a join key.
• Hierarchy resolution: int_cietrade__billing_sheet_charges joins int_wdp__hierarchy_resolved on (account_id, address_id). The resolver is a superset of int_wdp__account_hierarchy — it unions WDP master rows with synthetic rows for not-yet-onboarded cieTrade pairs, guaranteeing a match. is_wdp_mapped flags fully-onboarded customers.
• dim_vendor is expense-side only (account_role IN ('Vendor','Customer,Vendor')); the rebate counter-party surfaces as supplier_account_id on fct_rebate, not vendor_id.

---

Source systems

Five external systems land into dedicated PostgreSQL schemas, each on its own cadence and loader. Stage models read only from these schemas; nothing in the warehouse reads a source system directly.

Source	Schema	Loader	Cadence
cieTrade	cietrade	Prefect — API → Postgres	hourly to several times daily
WDP master	wdp_ref	wdp-ref-sync Prefect (Sling SDK from SQL Server, truncate + reload)	daily 6 AM UTC
Enevo IoT	enevo_normalized	Prefect ETL — API → flat service_events	daily
QuickBooks Online	qbo	qbo-daily-extract Prefect flow	daily
SOF (market)	sof_model	Standard Operational Framework — manually migrated (loader TBD)	on demand
dbt seeds	wdp_ref	dbt seed	on demand
Governance	wdp	Hand-provisioned via psql (never dbt)	on write

cieTrade — the operational system of record: billing, service records, dispatch, trading (rebate), and receivables/payables. Key raw tables: accounts, account_locations, accounts_receivable, billing_sheets, billing_sheet_charges, service_records, service_record_expenses, dispatch_jobs, trading_inquiry, posted_payables, worksheets, system_log. Source freshness warns at 24 hours.

WDP master — account hierarchy (parent → account → location) plus reference data. The daily truncate-reload is a parity trap: hierarchy and carbon-factor overrides resolved by dim_customer / int_wdp__effective_carbon_factors reflect whatever is in wdp_ref right now. wdp_ref also holds the dbt-seeded reference CSVs.

Enevo IoT — container fill-level sensor events, normalized to a flat service_events table (fill before/after, weight, volume). Grain: id + container_slot_id. Enevo is the sole feed for fct_weight_sensor; sensor services are excluded from fct_weight_scheduled to prevent double counting.

QuickBooks Online — financial ledger extracted daily. Feeds the finance-reconciliation intermediates only — there is no QBO mart yet. Reconciliation logic lives in models/intermediate/finance/ (AR/AP) and models/intermediate/qbo/ (crosswalks).

SOF — Standard Operational Framework market-classification reference: (zip, state, waste_stream) → Franchise / Open Market. Consumed by int_report__service_mkt_designation to stamp market_designation onto the profitability mart.

---

Conformed dimensions (8)

The conformed star has eight dimensions in models/mart/dim_conformed/. Facts carry FK ids; descriptive text lives here.

Dimension	Grain / notes
dim_customer	Location grain, single FK. Facts join on location_id; parent_account_id / account_id are denormalized attributes.
dim_material	Carbon factors per material + vintage. Renamed from dim_carbon_factor. Keeps its historical carbon_factor_id key because every fact and consumer references it.
dim_date	Integer YYYYMMDD keys. Rolling window: floor 2020-01-01 → current_date + 5 years (dim_date_start_date, dim_date_future_years vars).
dim_equipment	Equipment types (seeded reference).
dim_service_catalog	Service catalog (seeded reference). Added in the refactor.
dim_service_schedule	Service schedule dimension.
dim_source_system	Registry of source systems; FK target for every fact's source_system_id.
dim_vendor	Expense-side only (account_role IN ('Vendor','Customer,Vendor')).

Naming note

dim_material.carbon_factor_id deliberately keeps its historical name (it would be material_id under the naming rule) because every fact and downstream consumer already references it.

---

Base facts (6)

Six base facts carry a uniform identity contract and the build-time governance flags. Weight is split into four per-source facts so tonnage is traceable to its measurement method.

Domain	Fact	Materialization	Surrogate key
Finance	fct_billing	table	billing_sk
Finance	fct_rebate	table	rebate_sk
Weight	fct_weight_measured	table	weight_measured_sk
Weight	fct_weight_scheduled	table	weight_scheduled_sk
Weight	fct_weight_rebate	view over fct_rebate	weight_rebate_sk
Weight	fct_weight_sensor	table	weight_sensor_sk

Source-identifier contract

Every fact carries a uniform identity block so the union surface stays consistent. Read *_pk and *_sk together.

Column	Meaning
<fact>_sk	md5 surrogate; always not_null + unique. Finance = hash of (source_pk, source_system_id); weight = (source_pk, source_system_id, service_type_id).
source_pk	native source PK cast to text (concatenated for sensor).
source_pk_desc	names the native id (e.g. 'item_id', 'service_event_id|container_slot_id').
source_system_id	FK to dim_source_system; resolved from the wdp_source_systems seed, never hardcoded.
source_service_id	cieTrade service FK on weight facts.
service_date_id	YYYYMMDD integer key joined to dim_date.date_id.

The uniform <fact>_sk is what lets one pair of governance tables serve every fact (see Omnibus keying).

Source-system codes

The wdp_source_systems seed is the registry. Never hardcode the integer ids in fact SQL — resolve source_system_id from the seed.

id	code	Used by
1	CT	cieTrade billing → fct_billing, fct_weight_scheduled, fct_weight_measured
3	CTR	cieTrade rebate → fct_rebate, fct_weight_rebate
4	ENVO	Enevo sensor → fct_weight_sensor

Service-type literals

service_type_id is a hardcoded literal per fact — a contract, not a passthrough. It is a dimension component on the unioned weight surface and part of the weight-fact surrogate keys.

Value	Meaning	Carrier
1	Recurring (billing-derived volumetric)	fct_weight_scheduled
2	On Call	fct_weight_measured, fct_weight_rebate
4	Sensor	fct_weight_sensor

is_completed semantics

Every fact carries a not_null is_completed boolean, but the underlying source state that flips it differs per source. The flag is the conformed signal; the raw status string is not.

Fact	Definition
fct_weight_measured	status = 'COMPLETED' (dispatch jobs)
fct_weight_scheduled	status = 'POSTED' (billing sheet)
fct_weight_rebate	status = 'INVOICED' (trading inquiry)
fct_weight_sensor	literal true (Enevo pre-validated)
fct_billing	status = 'POSTED'
fct_rebate	status = 'INVOICED'

---

Governance layer — overrides & soft deletions

User-authored manual overrides and soft deletions of mart facts, re-applied automatically at build time with no procedural re-apply step. Every base fact projects is_overridden / is_deleted. The layer is what lets an analyst durably correct a fact value or hide a row: the correction persists across every rebuild because it is a build-time join, keyed against the uniform surrogate-key contract.

Where it lives

The layer spans hand-provisioned PostgreSQL tables, dbt staging views, and dbt macros. The tables are never managed by dbt.

Piece	Location
Tables wdp.fact_overrides / wdp.fact_deletions	scripts/governance/*.sql — provisioned via psql
Source + staging views (is_active-filtered)	models/stage/wdp_gov/
Apply macros	macros/apply_overrides.sql, macros/apply_deletions.sql
Existence check	macros/validate_governance_layer.sql (wired on-run-start)

The write path flows: an analyst calls a psql write function (fn_create_override / fn_create_deletion) → rows land in the hand-provisioned, is_active-filtered wdp.fact_overrides / wdp.fact_deletions tables → the models/stage/wdp_gov/ staging views expose them → the apply_overrides() / apply_deletions() macros join them onto each fact at build time on fact_sk → every base fact projects is_overridden / is_deleted.

Omnibus keying

One pair of tables serves every fact. Rows are keyed on (target_fact, fact_sk), where fact_sk is the fact's <fact>_sk. This is why the uniform surrogate-key contract matters — without it, overrides would need per-fact tables.

wdp.fact_overrides adds override_field to the key (one row per overridden field); wdp.fact_deletions keys on the fact SK alone. Both use partial unique indexes filtered to active rows.

Integration patterns

How a fact wires in depends on whether an overridable field feeds a derived column in the same model. Three patterns cover every fact.

• Projection-level (fct_billing, fct_rebate) — wrap each overridable column in overridden_column('field','src.col') (a coalesce(override, source)), join the pivoted overrides + apply_deletions() on the SK. Used when overridable fields don't feed other in-fact calculations.
• Governed-CTE-before-derivation (fct_weight_measured / _scheduled / _sensor) — a governed CTE applies the weight override before final computes emissions, so disposal_emission / total_emission follow the corrected weight. Required whenever an overridable field feeds a derived column.
• Inherited (fct_weight_rebate) — a view over fct_rebate that passes is_overridden / is_deleted through. Governance is keyed on rebate_sk, not weight_rebate_sk.

Single-source whitelist

The per-fact whitelist of overridable fields is single-sourced in var('governance_overridable_fields') in dbt_project.yml. Both the apply_overrides() macro and the whitelist test read it — add or remove a field once and both update.

Fact	Overridable fields
fct_billing	sales_amount, equipment_id, carbon_factor_id
fct_rebate	total_cost, total_sale, carbon_factor_id
fct_weight_measured / _scheduled / _sensor	weight_in_tons, weight_in_lbs

psql write path carries a manual mirror

scripts/governance/03_fn_create_override.sql carries a manual mirror of this whitelist because it is psql-deployed and cannot read a dbt var. It must be kept in sync by hand. An override row whose field isn't whitelisted is silently inert — the assert_override_field_in_whitelist test is the only signal that it exists.

Rules & write path

Deletion is a flag, never a filter. Governed facts stay unfiltered and project is_deleted; excluding deleted rows is a downstream decision. is_overridden / is_deleted are not_null on every governed fact.

The write path is a set of security invoker functions — fn_create_override, fn_remove_override, fn_create_deletion, fn_restore_deletion. The caller supplies original / audit-snapshot values (strategy A): no mart read, no GUC, no cross-schema grants. Audit columns (original_value, deletion snapshot) are caller-asserted and never read by the apply macros.

validate_governance_layer(strict=false) runs on-run-start as a read-only existence check for the wdp objects, logging [WARN] for any missing object during rollout. The QA plan calls for flipping it to strict=true once every environment is provisioned.

---

Carbon-factor resolution

The warehouse resolves a carbon factor per (account, material, service date) with the rule custom over default, resolved once in int_wdp__effective_carbon_factors. Facts no longer join the raw factor seed or re-coalesce — they join the resolver once and read the resolved columns, then emissions flow through shared macros.

• The resolver is a true effective table: one row per (account_id, material_name, default vintage), carrying the resolved factor with the account's enabled override coalesced over the default per column. Two id columns are never collapsed: carbon_factor_id points to the default vintage (dim_material, always set); account_carbon_factor_id points to the custom override row (set only when an enabled override exists). has_custom_factor = (account_carbon_factor_id is not null). co2_mile is never customizable.
• Non-temporal overrides — wdp_ref.account_carbon_factors carries no validity window; an override is the account's truth across every default vintage of that material. The resolver replicates the override once per default vintage (inheriting that vintage's dates) purely to preserve the no-overlap / no-fan-out invariant. You cannot express "this account's custom factor applies only from 2024 onward."
• The account_id = -1 sentinel — the resolver emits a default-only row per vintage under account_id = -1. Facts join on coalesce(account_id, -1), so a row with an unresolved (NULL) account still resolves to the default factor.
• Emissions macros — disposal_emission / transportation_emission / total_emission are shared across weight facts as macros in macros/, taking the already-resolved carbon_factor / co2_mile. fct_weight_scheduled passes the per-service transport scale services_per_month (equipment_quantity is deliberately excluded — int_wdp__service_details already folds it in). default_haul_miles (default 20) is the haul distance until per-account overrides land.

---

Profitability mart

The reporting/profitability/ layer rolls billing dollars to service grain and overlays a comparability classifier (apple / orange / lemon) plus a market designation (Franchise / Open Market). Design: specs/profitability-mart.md.

Fact	Grain
fct_billing_service_month	source_service_id × service_month
fct_billing_service_sku_month	same dollars split by sku_type + market_designation

The comparability engine and market designation are produced by the intermediate/report/ models — int_report__comparability, int_report__service_mkt_designation, and int_report__service_month_grid. Market designation is stamped from the SOF (zip, state, waste_stream) classification.

---

Semantic layer

A MetricFlow time spine plus 14 semantic-model entries (6 facts + 8 conformed dims surfaced as semantic dimensions) map 41 of the 48 legacy Power BI DAX measures onto the per-source facts. Reference: docs/SEMANTIC_LAYER.md.

No unioned weight surface

Cross-fact rollups (e.g. total_weight_tons) exist only as MetricFlow derived metrics — there is no unioned weight relation in the warehouse. This matters for BI connection choices: Power BI DirectQuery issues SQL against relations, not MetricFlow metrics, so a cross-fact rollup forces a 4-way UNION of the weight facts per visual. Import vs DirectQuery (and whether to materialize a unioned surface or use the dbt Semantic Layer connector) is an unmade design decision that gates performance.

---

Data-quality & tests

The data-quality contract is generic yml tests (the floor) plus 9 singular tests in tests/ that encode real prior bugs. Severity follows a split: hard contracts error; coverage/drift warns.

Test class	Severity	Rationale
unique / not_null on PKs and surrogate keys	error	hard contract
not_null on potentially-sentinel date columns	warn	sentinel nullification produces legitimate nulls
relationships FK → dim (null-guarded)	warn	fact rows missing the dim shouldn't fail the build
accepted_values on text dimensions	warn	new values should surface for triage, not break the build

Key singular invariants: governance whitelist + no override/deletion overlap; hierarchy and carbon-factor match-rate coverage; the weight double-count guards (sensor excluded from scheduled, sensor uniqueness); and carbon-factor range non-overlap so each (material, service_date) lookup matches exactly one row.

Validation status

The facts are built but not yet parity-validated against the legacy Azure-SQL rpt_*_new report tables. The QA strategy (specs/qa-strategy.md) reframes parity as a classification — every difference sorts into a reshaping artifact, an intended correction, or a genuine regression, and parity passes when the regression bin is empty.

---

CI/CD

CI lives in GitHub Actions (.github/workflows/) — GitHub is the live remote, not the ADO mirror. dbt build/deploy workflows authenticate to dbt Cloud (project 70403103958798, host qo890.us1.dbt.com) via the DBT_CLOUD_SERVICE_TOKEN secret.

Workflow	Trigger	What it does
dbt_ci_cd.yml	PRs + push to main	On PRs: dbt build --select state:modified+ against the defer environment (3× retry). On main: full dbt build, then POST to the production dbt Cloud job to deploy.
dbt_nightly.yml	daily 02:00 UTC + manual	Full dbt build, dbt source freshness, and two Python quality checks (currently no-op behind || echo — the scripts don't exist yet).
adw-issue-trigger.yml	GitHub issue labeled adw	Claude Code implements the change on a branch and opens a draft PR. Instructed not to run dbt (no Cloud auth in that job). Dormant without ANTHROPIC_API_KEY.
static-validation.yml	PRs	NEW hermetic gate, no secrets. Two jobs: dbt parse (offline manifest build — catches broken ref()/source(), malformed Jinja, and the arguments:-nesting rule) and lat check (validates the lat.md graph). Protects forks and survives dbt Cloud outages.