Transformations

How Stackless Transformation Models clean, reshape, test, publish, and refresh governed datasets.

What is a Transformation Model?

A Transformation Model is a managed dbt-backed dataset in Stackless. It defines how one or more warehouse relations become a clean, reusable table or view for analytics.

Transformation Models are the right place for business logic that should be reused across dashboards and Agent workflows. Examples include cleaned order facts, enriched customers, ad performance by channel, daily revenue, retention cohorts, or location-level rollups.

[picture 1. Transformation Model detail page with contract, preview, lineage, and status]

Why transformations matter

Raw source tables often disagree with how the business thinks. A source might call the same thing by different names, store nested data, keep deleted records, duplicate updates, or expose financial values in source-specific units.

Transformation Models make that logic explicit and reusable. Dashboards and Semantic Models should use transformed relations whenever a metric depends on business rules.

Draft, preview, publish

Transformation Models move through a managed lifecycle:

Draft the model privately.
Preview the model to validate SQL, sample output, cost, or materialized rows.
Review the publish plan and dependencies.
Publish the model to the shared managed area.
Refresh the model when upstream data changes or when a scheduled policy requires it.

Publishing is explicit. The Agent should not treat dashboard approval as automatic model publish approval.

Preview modes

Stackless supports multiple preview levels:

Mode	Purpose
`compile`	Validate that the managed spec parses and SQL compiles.
`estimate`	Add a warehouse execution estimate before materializing anything.
`view_preview`	Create a private preview relation and inspect sample rows.
`sample_build`	Run a bounded materialized check for a safer data sample.
`full_build`	Run the full model when a complete materialization is required.

Start with the cheapest preview that answers the question. Escalate only when you need sample data, publish evidence, or full materialization.

Managed operations

Transformation Models use structured operations. Common operation types include:

Operation	Use it for
Filter	Keep rows that match business rules.
Derive and cast	Rename fields, cast types, and calculate row-level fields.
Join enrichment	Add fields from another parent relation.
Aggregation	Build rollups at a defined grain.
Union	Combine matching branches into one dataset.
Flatten	Expand nested or semi-structured data.
Dedupe	Keep the latest or first record per entity.
Window	Add lag, lead, running totals, moving averages, or z-scores.
Pivot	Turn known row values into declared output columns.
Unpivot	Turn wide columns into a long label/value shape.

Requests that require recursive CTEs, dynamic pivots, arbitrary macros, hooks, or unsupported advanced window functions may need a raw dbt implementation path instead of a managed Transformation Model.

Model contract

A good Transformation Model declares its contract:

Parent relations.
Dimensions.
Measures.
Joins.
Grain.
Tests.
Operation chain.
Materialization.
Refresh policy.

This contract helps the Agent reason about the model, helps users review changes, and helps downstream dashboards avoid hidden assumptions.

Tests

Common tests include:

not_null for required fields.
unique for primary keys.
unique_combination for composite grain.
accepted_values for controlled categories.

Tests should protect the reporting contract. If a dashboard relies on one row per customer per day, that grain should be tested.

Materialization and refresh

Transformation Models can materialize as views, tables, or supported incremental models. Refresh policy can be on demand, hourly, daily, or weekly depending on the model and environment.

Use tables when downstream performance, repeated dashboard usage, or semantic model stability requires a persisted relation. Use views when the logic is lightweight and freshness is more important than materialized storage.

Lineage and downstream impact

Before changing a published Transformation Model, inspect downstream dependents:

Other Transformation Models.
Semantic Models.
Dashboards.
Agent artifacts created from the model.

If a published transformation changes shape, downstream Semantic Models and dashboards may need preview, refresh, or publish work before the user-facing report is fixed.

Agent prompts for transformations

Useful prompts:

"Find the Transformation Model behind this dashboard metric."
"Create a Transformation Model that joins orders to customers and deduplicates by latest order update."
"Preview this model and show sample rows."
"Does this model have tests for its grain?"
"Trace downstream dashboards that depend on this model."
"Refresh this model and downstream dependents if needed."