DWA layer

The DWA layer is where a published delivery becomes structured business knowledge. It handles data modelling, source-to-target mapping, transformations and relationship management.

QPI belongs here. It is not a fourth stage in the flow: data goes INGEST → DLS → DWA, and QPI is the framework's data quality engine, with checks placed on Published, Core and Business inside DWA. Its section is at the bottom of this page.

For the screen-by-screen guide, see Data warehouse automation.

The DWA layer is where raw data becomes structured business knowledge. It handles data modelling, source-to-target mapping, transformations, and relationship management.

The model chain

The chain is Published → Base → Core → DM. Stage was retired as a tier: the read-in step it used to name is now Published, loaded by the platform techniques described in Target platforms.

┌─────────────┐    ┌──────────────────┐    ┌──────────────────┐    ┌────────────┐
│  Published  │ ──►│ Base Model       │ ──►│ Core Model       │ ──►│  DM        │
│  (Read in)  │    │ (Integrate)      │    │ (Consume)        │    │ (Business) │
└─────────────┘    └──────────────────┘    └──────────────────┘    └────────────┘

Base Model (Ensemble / Integration)

• Purpose: Merge data from multiple sources using consistent business rules
• What happens here: Deduplication, business key matching, cross-source consolidation
• Configuration: baseModelName, baseModelSchema in Settings

Core Model (Integrated / Consumption)

• Purpose: Final, clean business entities ready for analytics and reporting
• What happens here: Publish unified Customer, Order, Product tables
• Configuration: coreModelName, coreModelSchema in Settings

Additionally, Datamart Models can be configured for specific analytical use cases via datamartModelNames and datamartModelSchemas in Settings.

Creating models

A model represents a target warehouse table. Each model has:

Property	Description
object	Unique model name
alias	Display name
description	Business purpose
objectType	Specific (single source) or Combined (multi-source)
loadingPattern	How data is loaded (see below)
attributes	Columns/fields with types and keys
relationships	Foreign keys to other models

Loading Patterns

Pattern	Behaviour	When to Use
full	Replace entire table each load	Small dimensions, reference data
incremental	Append only new/changed records	Large fact tables, event logs
transaction	Each row is an immutable event	Financial transactions, audit logs
dayspan	Partition by date range	Time-series data, daily snapshots
none	No automated loading	Manual or externally managed

Model attributes

Attributes define the columns in your model:

Property	Description
attribute	Column name
alias	Display name (optional)
dataType	Varchar, Integer, Decimal, Boolean, Time, Date, Timestamp
isBusinessKey	Y / N — is this part of the unique identifier?
businessKeyOrder	Priority in composite keys (1, 2, 3…)
description	Business definition

Business Keys

Business keys uniquely identify a record and are critical for deduplication and upsert operations.

Tips:

• Mark attributes as business keys with isBusinessKey: "Y"
• Use businessKeyOrder to define the priority in composite keys
• Drag rows to reorder key priority in the UI
• Map business keys before attributes — they must exist before dependent mappings

Example — Composite business key:

Customer model:
  #1  SystemCode    (businessKeyOrder: 1)
  #2  CustomerID    (businessKeyOrder: 2)

Mappings — source to target

Mappings connect sourcefile fields to model attributes. They are the core mechanism for data transformation in DWA.

Mapping Properties

Property	Description
source	Source field reference
target	Target model attribute
isKeyMapping	Yes = business key mapping
keyOrder	Key priority order
filter	SQL WHERE condition for this mapping
optionalCalculation	SQL expression for transformation
sortOrder	Sort priority (negative = ASC, positive = DESC)
useFieldNameAsValue	Yes = use the field name itself as the value
mappingNumber	Group identifier for multi-mapping scenarios

Mapping Types

Type	Visual Indicator	Description
Key Mapping	Teal, animated	Business key — maps to unique identifier
Calculation Mapping	Green	Includes an SQL transformation
Relationship Mapping	Purple	Maps to a foreign key relationship
Standard Mapping	Grey	Simple field-to-attribute link

Transformations

Optional Calculations

Apply SQL expressions to transform data during mapping:

-- Case normalisation
UPPER(CustomerName)

-- String manipulation
LEFT(OrderDate, 10)
TRIM(CustomerName)

-- Numeric calculations
ROUND(priceUSD / 1.08, 2)
Amount * 0.25

-- Conditional logic
CASE WHEN Status = 'A' THEN 'Active' ELSE 'Inactive' END

-- Date formatting
TO_DATE(TransactionDate, 'YYYY-MM-DD')

important

A calculation transforms its own source attribute and nothing else. There is no expression spanning two source columns, so a concatenation of first and last name belongs in the source query or in a separate model attribute. Several optionalCalculations can sit within one mapping group, each on its own attribute.

Calculations use the target database's SQL dialect. Field names are case-sensitive and must match the source exactly.

Filters

Apply row-level conditions to include/exclude records for a specific mapping:

-- Only non-null values
IS NOT NULL

-- Pattern matching
LIKE 'ACTIVE%'

-- Numeric comparisons
> 0

-- Equality
= 'SALE'

A filter applies to a single source field, and a mapping group can carry several composite filters. Different loading conditions belong in different mapping groups.

Sort Order

Controls record ordering for CDC and versioning scenarios:

Value	Direction	Use Case
Positive (e.g. 1)	Descending	Most recent first (typical for LoadDate)
Negative (e.g. -2)	Ascending	Oldest first
0	No sorting	Default

Use Field Name as Value: When useFieldNameAsValue: "Yes", the literal field name is used as the data value instead of the field's content. Useful for type indicators and enum-like columns.

Relationships (foreign keys)

Models can reference other models through relationships:

Property	Description
toObject	Target model
toAttribute	Target attribute (the referenced key)
relationType	1:N (one-to-many) or M:M (many-to-many)
relationRole	Whether this object is the parent or the child of the relationship, or many in a many-to-many. Left unset, it defaults to parent-child.

Visual indicators in the mapping diagram:

• Purple edges with REL badge for relationship mappings
• 1:N shown as purple dashed lines
• M:M shown as magenta dashed lines

Modelling scenarios

Multi-Source Customer Master

You have customer data in Salesforce, Marketo, and your ERP. Goal: unified Customer model.

Step 1 — Published (each source read in and normalised per attribute):

Salesforce:  Contact.FirstName → Customer.FirstName  (UPPER(FirstName))
Marketo:     Lead.first_name   → Customer.FirstName  (UPPER(first_name))
ERP:         CUST.FNAME        → Customer.FirstName  (UPPER(FNAME))

Step 2 — Base Model (integrate with dedup):

Business Key: CustomerID (mapped from each source's unique ID)
Filter: IS NOT NULL (exclude test/incomplete records)
Loading Pattern: incremental

Step 3 — Core Model (final dimension):

Business Key: CustomerID (composite if needed)
ValidFrom: LoadDate (validFrom=1, sortOrder=1 for DESC)
Loading Pattern: incremental with CDC

Early-Arriving Data (Master Not Yet Loaded)

Transactions routinely arrive before the master data they point at — an order for a customer who is not in the customer extract yet, a ticket for an asset registered in a system that runs on a slower schedule. The transaction is not held back, rejected, or parked for repair.

How it works:

1. Relationships are carried by the business key, not by a surrogate resolved at load time. The child record stores the parent's business key, so the reference is complete the moment the child lands.
2. The parent model gets a record for that key immediately, carrying the key and nothing else. The relationship resolves; only the descriptive attributes are missing.
3. When the master source loads, its attributes attach to the same business key. The key-only record becomes a full record — same key, same relationships, now with its attributes.
4. Because the core is historised, the fill-in is a new version rather than an overwrite. When the master data became known stays visible and auditable.

Day 1   Order 5591 → Customer "C-4417"     Customer C-4417: key only, no attributes
Day 3   Customer master loads               Customer C-4417: name, segment, address
                                            Order 5591 unchanged — it was never wrong

No manual intervention: the child load is not re-run, no reconciliation job resolves keys afterwards, and there is no placeholder record to find and clean up later.

Question	Answer
Does the transaction load fail or wait?	Neither. It loads, with its relationship intact
Do I re-run anything when the master arrives?	No — the next master load completes the record
How do I see what is still pending?	A record carrying a business key and no attributes
Enforced foreign keys?	The key-only parent row is what keeps the child load legal on targets that enforce foreign keys — see target platforms

note

A QPI check written as "zero orphans" will report pending keys as a failure while the master is still outstanding. Where early-arriving data is normal, scope the check to keys older than the master source's own schedule rather than to any unresolved key at all.

Hierarchical JSON with Nested Arrays

Source: API returning customers with embedded order arrays.

Sourcefile structure:

Level 0 (OBJECT): Root
  ├── customer.id        (keyFieldIndicator=1)
  ├── customer.name
  └── customer.loadDate  (validFrom=1)

Level 1 (LIST): orders   (useForSplittingRecords=1)
  ├── order.orderId
  ├── order.amount
  └── order.status

Mapping strategy:

• customer.id → Customer.CustomerKey (isKeyMapping=Yes)
• customer.name → Customer.Name
• customer.loadDate → sort by DESC for latest version
• order.orderId → Order.OrderKey (isKeyMapping=Yes)
• order.status → filter != 'CANCELLED'
• customer.id → Order.CustomerID (relationship mapping)

Calculated Fields

Source: Raw sales data needing business calculations.

Source Field	Target Attribute	Calculation
amount	TaxAmount	amount * 0.10
priceUSD	PriceEUR	ROUND(priceUSD / 1.08, 2)
transactionType	TransactionType	filter: = 'SALE'
rawDate	TransactionDate	TO_DATE(rawDate, 'YYYY-MM-DD')

SCD Type 2 (Slowly Changing Dimension)

Track historical changes to dimension records.

All fields and relationships track history by default, which makes them SCD Type 2 compatible.

Result: Each change creates a new version. The sort order ensures the latest version is identifiable.

Sensitive Data Handling

Handle PII and governance requirements in your model.

Field	Configuration	Effect
ssn	sensitive: 1, excludeFromProfiling: 1	Masked in profiles, flagged as PII
creditCard	sensitive: 1, excludeField: 1	Excluded from target entirely
email	sensitive: 1, fieldDomain: "PII"	Tracked for governance compliance
internalId	excludeField: 1	Skipped — not needed downstream

Batch editing

When you need to apply the same configuration to many fields, use the Batch Edit Panel:

• Select multiple attributes with checkboxes
• Apply filter, calculation, sort order, or field name settings in bulk
• Useful for applying a common transformation across a large sourcefile

Mapping diagram

The visual mapping diagram provides a drag-and-drop canvas for creating and reviewing mappings:

Views & Features

Views:

• Tree View — traditional hierarchical editor for detailed field-by-field work
• Diagram View — React Flow-based canvas showing the full mapping picture

Diagram Features:

• Colour-coded edges by mapping type (key, calculation, relationship, standard)
• Dependency graph — orange borders highlight fields involved in calculation formulas
• Search & filter — find fields by name, path, or data type with auto-zoom
• Export — download as PNG (1920×1080) or SVG vector with timestamp
• Mini-map — overview of the entire mapping structure

DWA best practices

Model Design

• Start with business keys — define unique identifiers before adding attributes
• Use composite keys sparingly — 2–3 fields max for maintainability
• Write clear descriptions — they serve as documentation for downstream consumers
• Choose loading patterns carefully — full for small tables, incremental for large ones

Mapping Strategy

• Map keys first, attributes second — business keys must exist before dependent mappings
• Use calculations for normalisation — standardise formats (dates, casing) at the mapping level
• Apply filters at the mapping level — not in the export WHERE clause — for model-specific exclusions
• Group related mappings — use mappingNumber to organise multi-mapping scenarios

Performance & Governance

• Avoid over-mapping — only map fields that are actually needed in the model
• Use excludeField — remove noise columns before they enter the modelling pipeline
• Profile selectively — exclude high-volume, well-understood fields from profiling
• Flag sensitive fields early — mark PII in the sourcefile structure, not as an afterthought
• Assign field domains — use classification categories for compliance tracking
• Document relationships — add descriptions to explain why models are linked

---

Quality monitoring (QPI)

QPI is the framework's data quality engine. It runs SQL controls against data in DWA and reports what it finds — it does not block a load and cannot stop downstream work.

Checks can be placed on Published, Core (Integrated) and DM (Business). They are free-standing and can be triggered by the orchestration after Additional Tasks, when the source flows they depend on are complete, or chained one after another. They are created and administered in the DataOps Console, under QPI Monitor and QPI Administration — see Data quality.

It tracks mapping coverage, field-level governance, data profiling and pipeline completeness — one place to look for data quality.

Platform-level metrics

The dashboard displays five key counters that give an instant pulse check:

Metric	What It Measures	Why It Matters
Systems	Total connected source systems	Are all expected sources registered?
Source Files	Total sourcefile definitions	Are all expected data feeds defined?
Fields	Total fields across all sourcefiles	Is the data contract complete?
Mappings	Total source-to-model mappings	Are sources connected to the warehouse?
Models	Total warehouse models	Is the target schema fully defined?

A sudden drop in any counter may indicate a configuration issue or accidental deletion.

Mapping coverage

The most important quality indicator. Mapping coverage measures what percentage of a sourcefile's fields are mapped to a model.

Calculation: coverage = (mappedFieldCount / fieldCount) × 100

Coverage	Status	Visual	Meaning
80–100%	Healthy	Green	Most fields are mapped and modelled
40–79%	Warning	Yellow	Significant unmapped fields — review needed
0–39%	Critical	Red	Mostly unmapped — likely incomplete setup

Where it appears:

• Data Lineage Flow — edges between sourcefiles and models are colour-coded by coverage
• Sourcefile Nodes — progress bars show coverage percentage per file
• Animated edges — connections below 80% coverage are animated to draw attention

Connection health

Each source system's connection type and export status is monitored:

Indicator	Values	What to Watch
Connection Type	DB, API, File, Custom, Manual	Missing connections show as "undefined"
Has Exports	Yes / No	Systems without exports aren't producing data
Export Count	Number of configured exports	Expect at least one per active system

In the Architecture Diagram, systems with exports connect via solid arrows through the Ingest Agent, while systems without exports connect with dashed arrows (direct transfer).

Field-level governance

Every field in a sourcefile carries governance flags that serve as quality indicators:

Flag	Purpose	Monitor For
keyFieldIndicator	Marks primary/business keys	Missing keys = no deduplication possible
sensitive	Flags PII/sensitive data	Untagged PII = compliance risk
excludeField	Removes field from pipeline	Over-exclusion may drop needed data
excludeFromProfiling	Skips quality checks	Too many exclusions = blind spots
validFrom	SCD Type 2 versioning field	Missing = no historical tracking
fieldDomain	Governance classification	Unclassified fields = governance gap

These flags are not only indicators — they are instructions. The generator reads them when it builds the target tables: keyFieldIndicator becomes a primary key on the publish table and a unique constraint on the core table, fieldDomain becomes a category tag on the column, sensitive becomes a sensitivity tag and, on platforms that support it, a column mask. A field classified once on the source is classified everywhere the platform carries it, including on the normalised child tables that inherit its key.

That is why an unclassified field is a governance gap rather than a documentation gap: nothing downstream will invent the classification for you, and nothing will apply it retroactively to tables already created.

→ What lands in the target environment

Data profiling

What profiling validates

When enableProfiler is active on a sourcefile, the system runs continuous quality checks against the data contract:

• Data type conformance (are integers actually integers?)
• Null/empty field ratios
• Value distribution patterns
• Schema drift detection (new or missing columns)

Configuration:

• Enable per sourcefile via enableProfiler: 1
• Exclude specific fields with excludeFromProfiling: 1
• Results are written to the Profile Zone in the DLS pipeline

Trade-offs:

• Profiling increases compute resource usage
• Disable for high-volume, well-understood, stable sources
• Always enable for new integrations until the data contract is validated

Model relationships

The Data Lineage Flow tracks how models connect to each other:

Relationship Type	Visual	What to Monitor
1:N (One-to-Many)	Purple dashed lines	Missing = isolated models with no joins
M:M (Many-to-Many)	Magenta dashed lines	Excessive = possible modelling issue

Additional Tasks (extended monitoring)

Configure pre- and post-processing tasks

For monitoring needs beyond built-in indicators, configure Additional Tasks:

Property	Description
Name	Task identifier
Type	pre (before main processing) or post (after)
RunCommand	Executable command to run
Dependency[]	Other tasks that must complete first
SortOrder	Execution sequence

Use cases:

• Data freshness checks — verify that data arrived within expected time windows
• Row count validation — compare actual vs expected record counts
• Schema drift detection — alert when source structure changes unexpectedly
• Threshold alerts — flag when metrics exceed acceptable ranges
• Post-load aggregation — compute summary statistics after each load

Dashboards and visualisations

Architecture Diagram

The end-to-end pipeline visualisation showing all four layers:

Data Sources (grey) → Ingest (teal) → DLS (blue) → DWA (purple)

What to monitor:

• Are all expected systems present?
• Do all systems have connections and exports?
• Is the full pipeline chain intact (no broken links)?

Data Lineage Flow

A detailed view showing how data flows from systems through sourcefiles to models:

Systems → Sourcefiles → Models → Related Models

Three-column layout:

• Left: Source systems with sourcefile counts
• Center: Sourcefiles with coverage progress bars
• Right: Target models with attribute counts and relationships

What to monitor:

• Red/yellow edges indicating low mapping coverage
• Animated edges drawing attention to incomplete mappings
• Orphaned models (no incoming mappings)
• Orphaned sourcefiles (no outgoing mappings)

Source Detail Panel

Click any system in the Architecture Diagram to see:

• Connection type and status
• All sourcefiles for that system
• Field counts per sourcefile
• Mapping counts per sourcefile
• Expandable path structure with field details

Monitoring checklists

Daily Checks

Check	Where	What to Look For
Platform counters	Dashboard Stats	Unexpected drops in any counter
Mapping coverage	Data Lineage Flow	New red/yellow edges
System connections	Architecture Diagram	Missing or broken connections
Export status	Architecture Diagram	Systems without exports (dashed lines)

Weekly Checks

Check	Where	What to Look For
Governance coverage	Field Editor	Fields missing sensitive or fieldDomain flags
Profiling results	Profile Zone	Anomalies in data type conformance or null ratios
Model completeness	Model Editor	Models with few or no attributes
Relationship health	Data Lineage Flow	Orphaned or disconnected models

On New Integration

Step	Action
1	Verify system appears in Architecture Diagram
2	Confirm connection type is correct
3	Check exports are configured and scheduled
4	Enable enableProfiler on all new sourcefiles
5	Mark sensitive fields with sensitive: 1
6	Assign fieldDomain classifications
7	Create mappings and verify coverage reaches the target for the sourcefile type (see Coverage Targets)
8	Validate profiling results after first load

QPI best practices

Coverage Targets

Sourcefile Type	Target Coverage	Rationale
Core business data (customers, orders)	90–100%	Critical for analytics
Reference/dimension data	80–100%	Needed for joins and lookups
Event/log data	60–80%	Many fields may be metadata/noise
Staging/temporary sources	40–60%	May be partially relevant

Profiling Strategy

• Enable by default for all new sourcefiles
• Disable selectively for stable, high-volume sources after validation
• Never disable for sources with PII — compliance requires ongoing validation
• Exclude specific fields rather than disabling profiling entirely

Governance Priorities

1. Tag PII first — sensitive: 1 on all personally identifiable fields
2. Classify fields — assign fieldDomain for compliance tracking
3. Define business keys — every model needs at least one
4. Document relationships — add descriptions to explain why models are linked
5. Review exclusions — make sure excludeField isn't hiding needed data

---

Next steps

• Data warehouse automation — the field-by-field configuration guide
• What lands in the target environment — the constraints, comments and tags the generator emits
• Differences between target platforms — what each engine enforces and cannot do