Which responsibilities move into dbt?
Move appropriate relational transformations into models while assigning extraction, scheduling, secrets, file transfer and external actions to their actual owners. A dbt model describes a transformation on its configured platform. Replacing a PowerCenter workload also requires a decision about everything that starts the model, supplies its inputs and publishes its result.
Inventory the source mapping and the workflow that surrounds it. Include reusable transformations, lookups, update strategy, parameters, reject paths, connection context and task dependencies. Record which behavior is expressed as SQL logic and which depends on execution state or another system. A list of transformation names does not yet establish that every represented behavior has a target owner.
Use a Snowflake analytical target as the bounded reference in this guide. Adapter-specific incremental behavior and materialization choices must be checked for that target and the versions actually selected. The ETL vs ELT guide explains the processing boundary; this page focuses on the more specific mapping and workflow decisions behind a PowerCenter transition.
- Transformation owner: the model that produces the governed rows and columns.
- Control owner: the component responsible for dependency scheduling, retries, publication and external actions.
- 01
Model calculation
The responsibility produces governed rows at a declared grain.
Represent suitable logic in models with explicit assertions.Example: a join and aggregate with a versioned reference dataset.
- 02
Workflow control
The responsibility schedules, retries or decides publication.
Assign an orchestration and operating owner around the model run.Example: an upstream failure stops a daily consumer release.
- 03
External state or I/O
Connectors, files, rejects or actions sit outside model output.
Select a component and a recovery contract for that behavior.Example: correcting a rejected record before a controlled replay.
Illustrative Snowflake/dbt reference. It is a responsibility map, not a claim that a translator replaces every PowerCenter feature.
What state should an incremental run leave behind?
Define the business key, ordering signal, update rule and delete disposition before selecting an incremental strategy. A repeated key can represent a new version, a duplicate delivery or a legitimate separate record. The target must apply the declared state transition rather than infer meaning from arrival order.
The illustrative fixture carries four ordered events: customer 101 becomes active at version 2; customer 202 is deleted at version 2. Under the stated current-active-record contract, the accepted result is one row: 101, active, version 2. An append-only output retains four events, which can be useful history but is a different data product.
dbt documents incremental models, optional unique keys and strategy-specific behavior. Its incremental guidance warns that repeated keys can make a model fail depending on the database and strategy. Define a separate uniqueness assertion and handle multiple candidate events for the same key deliberately. A key setting alone does not specify event ordering, implement every delete policy or settle the interpretation of a reject.
Illustrative contract: highest ordered version per key determines current state; deleted keys are excluded from the current-active table.
- 01
101 · v1 · new - 02
101 · v2 · active - 03
202 · v1 · active - 04
202 · v2 · deleted
Accepted current state
1 active row
Customer 101, version 2, active.
Event history
4 events
A different, useful product with a different grain.
Delete disposition
202 excluded
The declared current-active contract applies the tombstone.
A configured unique key does not by itself test input uniqueness, choose the correct version or define every delete path.
How should the mapping and workflow be decomposed?
Classify responsibilities before choosing target components. Keep a source reference, intended behavior and acceptance case for each disposition. This makes it possible to find a missing responsibility even when every dbt model has built successfully.
Keep surrogate-key stability, parameters and reusable logic in this map wherever consumers rely on them. The data engineering service provides a broader pipeline scope; a PowerCenter-specific assessment should still identify the exact source behavior being replaced.
Assess lookup semantics and surrogate-key stability alongside transformations. Record which reference version an event should use and whether a regenerated key changes downstream joins. If a source mapping keeps state across rows or interacts with an external file, name that responsibility separately from the model's SQL. The data contracts guide helps make the target grain, key and publication rules explicit.
| Dimension | Target responsibility | Acceptance question |
|---|---|---|
| Relational mapping logic | A model or an explicit sequence of models with declared grain. | Do joins, filters, expressions and aggregates produce the expected rows? |
| Lookup and reference state | An explicit reference dataset, version and effective-time rule. | Does a historical record use the reference value that applies to its business date? |
| Updates and deletes | A key/order contract and target-specific state application design. | Do repeated deliveries, later versions and deletes leave the accepted current state? |
| Rejects and recovery | A controlled exception store and an owned correction/replay path. | Can a rejected record be explained and corrected without duplicating accepted output? |
| Workflow and publication | Orchestration and consumer publication outside or around the model run. | Can an upstream failure or a failed assertion prevent a stale result being published as current? |
How do model tests connect to accepted business output?
Use assertions that expose meaningful failure cases: key uniqueness, required values, relationship integrity and the actual business rule. Keep those checks close to the model contract and compare them with accepted source output. A technically valid schema can still contain the wrong current state or an incomplete reporting period.
Include a repeated delivery, two versions of one key, a delete, a late correction and a rejected record in the controlled fixture. Separate the event/history product from the current-state product, then check the appropriate denominator for each. A count of four input events is not a failed count when the accepted current table contains one active record.
dbt's data-test documentation describes assertions that return failing records. Define what a failure means for publication and who resolves it. An assertion is useful evidence only when the operating workflow treats its result consistently. Include the freshness and completeness of model inputs alongside the correctness of individual rows.
Connect each data assertion to a publication consequence. A uniqueness test, a relationship test and a business-total comparison catch different problems; retain the failing records and the accepted population used in each denominator. The handoff map shows why model state, successful assertions and consumer acknowledgement need separate references. Include input completeness so that correct rows from an incomplete delivery are not mistaken for an accepted current release.
- Input deliveryDelivery identified
Retain event identity, key, version and reference-data boundary.
- Model and assertionsState rule applied
Order updates and deletes under the accepted product contract.
- Consumer publicationReplay interval retained
Keep the input history and exception disposition needed to resume.
- Input deliveryInput population specified
Separate current-state, event-history and rejected populations.
- Model and assertionsCandidate state committed
Record materialization/strategy and meaningful assertion results.
- Consumer publicationAccepted version named
Connect output state to the run and its comparison evidence.
- Input deliveryDependencies satisfied
Define credentials, scheduling and input freshness responsibility.
- Model and assertionsPublication decided
A green model run alone does not publish stale input as current.
- Consumer publicationAcknowledgement stored
Record the consumed version and the restart owner.
Illustrative state map, not an executed dbt incremental strategy. Actual adapter behavior and publication controls still need environment-specific evidence.
What happens after a partial workflow failure?
Record which model state was committed, which assertions completed and which consumer publication was acknowledged. A restart needs an explicit starting point and an understood effect on previously accepted data. Re-running the entire workflow is a recovery choice whose consequences must be specified.
For the current-state fixture, replaying the same accepted event versions should leave the same accepted current table under the declared rule. A history table may instead retain a delivery identity and a separate event identity. Keep those keys distinct so retry safety is not achieved by silently discarding meaningful business changes.
The workflow below separates model execution from the publication decision. Include connector recovery, exception correction and any downstream file delivery in the handoff. The pipeline reliability guide connects that state record to consumer publication and restored-state evidence.
- 01Decompose
Map each source responsibility to a model or another owner.
Keep: mapping/workflow pair and target assumptions.
- 02Apply state rules
Represent ordered updates, deletes, rejects and corrections.
Keep: accepted state fixture and key/order policy.
- 03Assert the output
Check data rules, completeness and input freshness.
Keep: failing records and publication disposition.
- 04Publish and resume
Record committed state, consumer acknowledgement and restart point.
Keep: run identity, checkpoint and operating handoff.
Keep history, current state and exceptions distinct. A green model run is incomplete evidence when the consumer publication or reject disposition is unknown.
How should effort and operating economics be assessed?
Assess the behaviors and operating responsibilities being replaced, not just the number of mappings. A short mapping with dynamic lookup state, external files and difficult recovery can carry more risk than several straightforward relational models. Reconcile license, warehouse, orchestration and operating assumptions separately.
Profile source input volume, change rate, concurrency, run frequency and the accepted publication window. On the target, record the configured materialization and incremental strategy with the actual workload observations. A small synthetic example demonstrates semantics; it cannot establish runtime, conversion coverage or the cost of the complete estate.
Include parallel operation, retained source responsibilities, exception handling and staff/run capacity in the plan. Use the modernization assessment to frame the target boundary and dependencies before extending a representative workload into a wider conversion estimate.
Evaluate a migration accelerator on a representative mapping/workflow pair that includes a lookup, an incremental state rule and a rejected path where they exist. Retain generated candidates, unsupported constructs, tool/adapter versions and manual dispositions. The boundary pack below adds late versions, a delete and a missing key; a result for that bounded sample should not be presented as coverage for every mapping in the estate.
Illustrative rule: highest ordered version wins per key; deleted keys are excluded from current-active output; a missing business key enters an explicit exception population.
- 01
Out-of-order delivery
Input / condition101/v2/active, then 101/v1/new
Expected contract101 remains active at v2
Evidence to retainCompare accepted version, not just the number of arrived events.
- 02
Late pre-delete event
Input / condition202/v2/deleted, then 202/v1/active
Expected contract202 remains outside current-active output
Evidence to retainRetain the delete/version state; arrival order must not resurrect the key.
- 03
Missing business key
Input / conditionNULL key / v1 / active
Expected contract1 exception; 0 accepted current rows from this input
Evidence to retainKeep the rejected record and its effect on publication completeness explicit.
These rules are the fixture's contract. A unique-key setting alone does not implement ordering, rejection, tombstone retention or every delete path.
What should the first migration brief include?
Bring one complete mapping/workflow pair, a source-feature inventory, representative inputs and accepted outputs, the reference target, and a state-transition contract. Identify the consumer, its freshness requirement and the point where the output becomes available to that consumer. Include one failure path as well as the happy path.
Use synthetic keys and records for the shared fixture. State which source features the example represents and which remain unexamined. The resulting decomposition can support an implementation decision without presenting a generic mapping count as proof of universal conversion.
If the larger programme also moves a warehouse or a Hadoop estate, use the Teradata to Snowflake guide or Cloudera to Databricks guide for those separate responsibilities. Keep the workflow and destination assumptions attached to the workload that owns them.
Questions and trade-offs
Does dbt replace every part of a PowerCenter workflow?
A complete disposition assigns suitable SQL transformations to models and gives ingestion, orchestration, credentials, external actions and publication their own owners. Check the actual source features and target platform. The guide does not assume one tool owns every responsibility.
Keep an explicit component map for the target warehouse, model execution, ingestion, orchestration, exceptions and consumer publication. That map makes an unowned file movement or task condition visible before a seemingly complete model conversion is accepted.
Does setting unique_key prove that the input is unique?
No. dbt's incremental guidance warns that repeated keys can make a model fail depending on the database and strategy. Retain a separate uniqueness assertion, define the ordering of multiple events for a key and check the selected adapter and incremental strategy.
Include out-of-order versions and null business keys in a controlled comparison. Retain tombstone/version information for the declared delete rule. The illustrative boundary pack specifies the expected state; it does not claim the selected adapter automatically implements that state machine.
How should deletes and rejected records be handled?
State the intended data product first. A current-active-record table, an event history and an exception store have different rules. Define the delete disposition, retained evidence, correction path and replay behavior, then check the actual target implementation against that contract.
Name the rejected population, reason, correction owner and replay identity. Define whether missing or rejected input prevents a consumer publication. Without that consequence, a workflow can produce individually valid rows while silently presenting an incomplete business period as current.
Can the worked fixture be used as a conversion benchmark?
It demonstrates a small state-transition contract with invented data. It does not measure a source estate, target runtime or conversion coverage. Use a representative, permitted workload and recorded conditions for an actual performance or effort comparison.
For a real comparison, keep source-feature coverage, target adapter/strategy, data snapshot, run conditions and accepted consumer result with the observation. Report semantic evidence separately from performance or effort evidence; the fixture only demonstrates its stated state contract.
How should lookups, reusable logic and surrogate keys move?
Record the lookup's reference version, matching/default rules and any state it keeps. Reusable transformations need a defined target interface; surrogate keys need a stable identity policy where downstream consumers depend on them. Choose a target implementation for those behaviors rather than translating their names.
Compare a meaningful no-match, changed-reference or historical-effective-date case. A matching happy-path join does not establish that all reference and key behaviors have been preserved.
What is needed to scope a PowerCenter-to-dbt project?
Bring complete mapping/workflow pairs, the represented source features, connectors, parameters, reference data, run dependencies, rejects and accepted outputs. Name the target warehouse and adapter, freshness needs, consumer and publication boundary. Include volume/change-rate observations and a failure path.
Estimate model work, control/I/O work, comparison evidence and operating handoff separately. Mapping count alone can conceal the behavior that determines effort; no fixed duration or automated conversion percentage follows from the guide.
How should full-refresh and incremental results be compared?
Define the same accepted population, business boundary and reference data for both paths. Compare keys, versions, deletes, meaningful values and exception counts; then examine how retries and late events affect the incremental result. Retain the configured strategy and adapter with those observations.
A full refresh and an incremental run may represent different input boundaries if the source changed between them. Align that reference before interpreting a difference as a transformation defect.
What should a migration accelerator demonstrate before selection?
Ask it to disposition the actual features in a permitted mapping/workflow sample. Retain emitted candidates, unsupported constructs, assumptions and the responsibilities remaining outside its model output. Evaluate target results with independent assertions and consumer comparisons.
Check current maintenance, supported versions and support boundaries too. A generated model, an old repository or a demonstration that parses a mapping does not establish complete workflow replacement, production support or accepted business output.
