Job search
Analytics Engineering Portfolio: Show Model Contracts, Versions, and Metric Changes
A synthetic model migration preserves a legacy consumer while reconciling a new metric, with actual local SQLite failures and bounded acceptance records.
An analytics engineering portfolio should show how a modeled dataset changes without silently breaking its consumers. Present the model’s grain and contract, its versioned dependencies, the business definition being changed, and the checks and decisions required before downstream users migrate. A dashboard can illustrate consumption, but it cannot substitute for that transformation evidence.
LandOffer publishes this guide and offers job-search tools. When you browse recent roles on LandOffer.ai, look for responsibilities involving shared models, metric definitions and coordinated changes. The business and candidate case below is entirely fictional. We executed new SQL transformations with local SQLite, not dbt, MetricFlow, a warehouse or a dashboard. No private data, deployment or stakeholder approval was accessed.

Choose a change that exposes analytical responsibility
A useful case starts where a business definition and a shared data interface meet. Explain what one row represents, what the columns mean, which inputs feed the model and who relies on its output. Then introduce a specific change and show how the intended consumers respond.
Keep this distinct from ingestion engineering. Successfully receiving records or retrying a failed transfer does not establish that a transformation preserves its intended grain. Likewise, a correct chart does not explain whether two consumers use the same definition or whether a change preserves an older reporting obligation.
Your personal contribution should be visible in the model and migration decisions. Identify which logic you wrote, which checks you executed, which business interpretation you proposed and which decisions belonged to someone else. An independent project can demonstrate that reasoning while remaining clearly labeled as a local prototype.
Describe adoption and impact only when actual evidence establishes them. A passing fixture is useful technical evidence. It is not proof that a finance team accepted the definition, an existing dashboard migrated or a company made a better decision. Preserve those missing steps in the finished case rather than hiding them behind “trusted source of truth.”
Define a contract without treating it as semantic approval
dbt’s model-contract documentation, inspected with v2 selected, describes checking a model’s column names and data types. It distinguishes this shape from data tests and documents materialization and platform restrictions. Views do not receive the same constraint support as tables, and a declared constraint is not necessarily enforced by every supported platform.
In your case, document the intended grain and uniqueness checks alongside the shape. Do not assume that declaring a primary key in a warehouse proves uniqueness. Also explain business meaning separately: a column can retain its name and integer type while a changed calculation alters every consumer’s interpretation.
The local contract checks in this article use SQLite’s own schema inspection and enforced constraints. They are not a dbt preflight check. We saved the SQLite version, SQL, fixture and results so the reader can see exactly what ran. The same assertions would need corresponding implementation and verification in an actual dbt and adapter environment.
Worked asset 1: a complete versioned merchandise-value case
Fictional candidate Tessa prepares an independent portfolio case for fictional Maple Harbor Goods. Neither represents an actual applicant or business. The dataset contains four invented orders, five lines and three refunds, with synthetic identifiers such as DEMO-O1. All amounts are integer USD cents; there are no real customer records or transactions.
The original model reports gross merchandise value for paid orders. A proposed new definition reports retained merchandise value after the recorded refunds. Both exclude tax and shipping. The change is an illustrative reporting choice, not an accounting-standard interpretation. The business owner has not actually approved either definition.
Here is the completed model record:
| Contract item | Version 1 | Version 2 |
|---|---|---|
| Local relation | order_merch_v1 | order_merch_v2 |
| Grain | One row per paid order_id | Same paid-order grain; fully refunded orders remain with zero value |
| Declared columns | order_id TEXT, currency TEXT, gross_merch_cents INTEGER | order_id TEXT, currency TEXT, retained_merch_cents INTEGER |
| Key and null rule | Unique, non-null order_id; all columns non-null | Same; negative modeled merchandise amount rejected |
| Calculation | Sum line amounts for each paid order | Aggregate lines and refunds separately by order, then subtract the refund total |
| Local dependency | Orders joined to order-level line totals | Orders joined to line totals and separately aggregated refund totals |
| Consumer | Legacy gross-merchandise summary reads v1 explicitly | Proposed retained-merchandise summary reads v2 explicitly |
| Acceptance boundary | Local fixture checked; real report owner absent | Local fixture checked; business definition and consumer-owner acceptance pending |
The input and output reconciliation is also complete. DEMO-O1 has two lines totaling 2,000 cents and two refunds totaling 500; v2 returns 1,500. DEMO-O2 has a 1,500-cent line and a matching 1,500-cent refund; v2 retains the order with zero. DEMO-O4 contributes 700 with no refund. Unpaid DEMO-O3, with a 900-cent line, is excluded from both versions.
Thus v1 returns 4,200 cents and v2 returns 2,200. Their 2,000-cent difference equals the fixture’s recorded refunds. That difference is a definition reconciliation, not lost sales, a measured business decline or an improvement caused by the candidate.
Tessa’s fictional contribution record identifies the field contract, SQL revision, dependency map, negative checks and migration note as her project work. For this article, the actual SQL and Python harness were AI-generated and executed locally. No human candidate’s authorship or workplace achievement was observed. Readers should describe only the work they actually performed and reviewed.
The finished portfolio summary is: “Built local SQL versions of a synthetic paid-order model, preserved the old consumer, and reconciled a proposed refund-adjusted definition with bounded schema, content and migration checks.” It leaves business approval and platform deployment out of the claim.
Make each consumer’s model version explicit
dbt’s model-version guidance distinguishes model versions from repository version control. It documents explicit version references and explains that an unpinned reference resolves to the declared latest version. A migration window lets consumers move deliberately rather than inheriting every breaking change immediately.
For example, a dbt consumer can reference a specific model version with ref('order_merch', v=1) or v=2. Those expressions illustrate documented dependency syntax; we did not compile or run them. Our executed SQLite consumers instead reference the separate local relations by name. Simply adding a _v2 filename does not create a verified dbt versioned model.
Keep a dependency inventory with the consumer, expected fields, definition, owner and intended version. Include systems outside the transformation project if they actually exist. A direct SQL report may depend on a relation without appearing in the modeling tool’s graph, so its owner and query need a separate migration record. Do not claim you migrated every consumer because an internal graph has no remaining references.
The selected v2 documentation also discusses latest-version pointer views and alias collisions. An actual project needs its matching release settings and relation names checked. This local example does not test that dbt feature; it uses an ordinary SQLite view only to expose a stale consumer failure.
Preserve the failed migration as evidence
A useful portfolio includes the error and the decision it changed. Separate a schema failure from a calculation failure and from an unapproved business interpretation. Fixing one category does not settle the others.
Worked asset 2: executed failures and a completed downstream decision
We ran both original SQL models in an in-memory SQLite 3.53.4 database. Each produced three paid-order rows. Local schema inspection matched the declared column names and types, while separate row assertions matched all three expected results in each version. These checks did not invoke dbt contract enforcement. Attempts to insert a duplicate key, a null key and text into the integer amount column were rejected by SQLite’s local constraints. These observations concern this engine and fixture, not a warehouse adapter’s behavior.
The retained-value revision also preserves a deliberately incorrect alternative. Joining raw order lines directly to raw refunds multiplies matches for DEMO-O1: its two lines meet its two refunds. The erroneous query returns 3,000 cents for that order and 3,700 overall, instead of the required 1,500 and 2,200. Pre-aggregating each input to order grain before joining gives the expected result for this fixture.
The completed failure and acceptance ledger is:
| Check or consumer | Actual local observation | Completed decision and remaining boundary |
|---|---|---|
| Raw line-to-refund join | Total 3,700; expected 2,200 | Reject this SQL revision. Keep its 1,500-cent excess and failing order as reproducible evidence. |
| Legacy gross consumer against a latest view of v2 | Error: no such column, gross_merch_cents | Do not switch the existing consumer to that view. Keep its explicit v1 relation. |
| Rename retained value back to the old gross column | Query runs but returns 2,200 where legacy expectation is 4,200 | Reject the silent semantic substitution even though the query succeeds. |
| Legacy consumer pinned to v1 | Returns the expected 4,200 cents | Local regression check passes; actual legacy report owner has not reviewed anything. |
| New consumer pinned to v2 | Returns the expected 2,200 cents | Local proposed-definition check passes; real business and consumer acceptance remain pending. |
This ledger resolves the technical choices: retain separate versions, preserve the legacy reference, reject the multiplying join and avoid disguising a new metric under the old name. It does not resolve business adoption. No production report was changed and no stakeholder gave approval.
The saved fixture includes the original inputs, each SQL file, output rows, error messages and code hashes. Source tables remained unchanged. Three paid orders are a small test population: they do not establish behavior for arbitrary currencies, late refunds, duplicated source events or refunds exceeding merchandise value.
The migration note leaves late refunds, duplicate source events, multiple currencies and excessive refunds unresolved; each needs a defined rule and corresponding evidence before widening the model’s claim. It names the business owner as the future definition reviewer and the consumer owner as the future acceptance reviewer, without inventing their identities or a signed decision. Version 1 should not be described as retired until the actual consumers have moved and the relevant retention decision is recorded.

Make the metric change understandable outside SQL
Write the definition in the same terms used by the proposed consumer. Here that means paid orders, merchandise only, integer USD cents, recorded refunds and fully refunded orders retained with zero. Do not call the result revenue without explaining its scope or imply that the exercise establishes a financial reporting policy.
dbt’s Semantic Layer configuration reference distinguishes current model-embedded configuration from legacy standalone YAML and directs readers to the specification matching their environment. The current reference lists latest-spec support for dbt platform’s v1 Latest track, dbt v2 and v1.12. Copying old measures-based examples into a different release can obscure what you actually configured.
This portfolio does not claim a running Semantic Layer or MetricFlow query. Its metric-definition record is a local document linked to SQL consumers. Present that document as a local definition record, with the absent platform implementation visible. For an implemented project, include the actual release, configuration, consumer query and verification results instead of substituting a generic architecture diagram.
Present a reviewable portfolio bundle
Give the hiring reviewer a short path through the case: business change, model contracts, dependency map, before-and-after rows, failed revision and downstream decision record. Keep the dashboard, if any, as a consumer illustration rather than the main evidence. Do not expose private warehouse links, credentials or customer data to make a demonstration appear realistic.
Choose a bounded claim for the application. You can show local transformation reasoning and migration tests without claiming production reliability or company-wide standardization. A reviewer should be able to see why the metric changed, how the model preserved its grain and which acceptance steps remain outside your evidence.
Before sharing, verify that each version, amount, dependency and acceptance claim points to its executed artifact or remains explicitly pending with the appropriate decision owner. Explore LandOffer.ai to organize applications while keeping the portfolio’s model, version and metric claims precise.
Sources and Further Reading
- dbt: Model contracts, v2-selected documentation, current full public body read October 21, 2026; names/types, materialization and platform-enforcement limits.
- dbt: Model versions, v2-selected documentation, current full public body read October 21, 2026; dependency pinning, migration and version-specific pointer behavior.
- dbt: Semantic Layer configurations, current full public reference read October 21, 2026; latest versus legacy configuration and environment support.




