# Glossary This glossary explains the core concepts of Yres. Technical identifiers (schema, table, procedure and column names) are shown exactly as they appear in the code — they often still contain `IRIS`, because the product was formerly called **IRIS**. In running text we use the product name **Yres**. :::tip Going deeper The load engine, SCD2 history and the seven load types are covered in detail in [Yres explained](https://yres.eu/en/wiki/yres-uitgelegd) and [Load types](https://yres.eu/en/wiki/concepten/load-types). ::: ## Product and organization | Term | Meaning | |---|---| | **Yres** | The Azure data platform (data warehouse automation). Formerly **IRIS**. | | **IRIS** | Old product name of Yres. Still appears in resource names (`IRIS_DWH`, `kv-iris-…`), internal URLs and code identifiers. | | **Plainwater** | The company behind Yres; develops and hosts the control plane. | | **Organization** | Isolated space in which a customer uses Yres; contains one or more environments. The first environment is always `dev`. | | **Environment** | Isolated version of an organization. Each environment has its own ADF factory and its own `IRIS_DWH` database. The first environment is always `dev`; depending on the license also `test`, `acceptance`, `prod`. | | **DTAP** | Development, Test, Acceptance, Production — the pattern of separated environments along which changes are promoted. The `Change` schemas in `IRIS_DWH` support this change management. | | **Project** | Categorizes work within an environment; contains changes and has a due date. Only available for organizations with multiple environments. | | **Change** | Set of edits to the data warehouse, to be released and installed to other environments (DTAP). | | **Scripted object** | Custom SQL object (table, stored procedure, function) not generated by Yres, managed under a change. | ## The two planes | Term | Meaning | |---|---| | **Control plane** | Plainwater's multi-tenant SaaS web app (one deployment for all customers). Manages organizations, users, environments and billing in a PostgreSQL database. **Never holds customer data** — it provisions and drives each data plane via the Azure Management, ADF, Key Vault and Azure DevOps APIs and a direct SQL connection. | | **Data plane** | One ADF factory + one `IRIS_DWH` Azure SQL database **per organization × environment**, running inside the customer's **own Azure tenant**. This is where the data actually moves. This is where the load engine lives. | ## Sources and metadata | Term | Meaning | |---|---| | **Data source** | Connected source from which data is loaded (database, application, OData/REST, file server). Created via the "Create source" wizard. | | **SourceType** | The source type that determines the load behavior (e.g. `MSSQL_ADF`, `DB2`, `POSTGRESQL`, `MYSQL`, `ODATA`, `SALESFORCE`, `SAP_BDC`). For each `sourceType`, `fxExtractor` generates the correct extract command and the delta WHERE clause. | | **ADF** | Azure Data Factory — the orchestration layer. Yres generates the pipelines to it; ADF copies bytes from source to `STAGE` and then calls SQL. ADF contains no load logic. | | **Integration Runtime (IR)** | ADF compute that reaches the source. Default `AutoResolveIntegrationRuntime` (cloud) for cloud-reachable sources; a self-hosted IR (`pwccIntegrationRuntimeLinked`) for on-prem/firewalled databases and file servers. | | **Dictionary** | Stored source metadata (tables, columns, data types, keys, relations) in `LoadManagement.Dictionary`. Populated by "Refresh metadata" and required before adding tables. **File and REST sources skip this step.** | | **`vwExtractor`** | The contract view `[LoadManagement].[vwExtractor]` that ADF reads to determine *what* needs to be loaded. It is a thin wrapper: `SELECT * FROM [LoadManagement].[fxExtractor](NULL, NULL)`. | | **`fxExtractor`** | The table-valued function `[LoadManagement].[fxExtractor](@LoadFilter, @LoadType)` that holds **all** extractor logic. Each returned row = one table to load, with all the parameters ADF passes downstream (load type, `DeltaScript`, target schemas, file/parsing options, pagination). | ## Layers in the data warehouse | Term | Meaning | |---|---| | **STAGE** | Staging schema where ADF lands the raw source extract (schema name via setting `SchemaStage`, default `STAGE`). This is where the framework columns `ETL_Date`, `KeyHash` and `RowHash` are computed. | | **HIS** | History layer with the full SCD2 history (schema name via setting `SchemaHIS`). The HIS schema name is **not** hard-coded; in verifiable environments `SchemaHIS` resolves to `ODS`. | | **ODS** | Operational Data Store. Also the default value of `SchemaHIS` — in practice the HIS layer lands in the `ODS` schema. | | **Expose** | The reporting/expose layer (`Exposed`, `Expose`, user schemas). Contains the reporting-oriented views/tables plus RBAC, built by `spMaterializeViews`. | | **Data Lake** | Optional Parquet landing in Azure Data Lake Gen2. Yres writes a [change feed](https://yres.eu/en/wiki/concepten/lake-feed) per run holding only the mutations (`YresAction` = `I`/`U`/`D`, plus `KeyHash`/`RowHash`/`YresDateStart`), partitioned on Source/Schema/Target/Year/Month. | | **Lake feed** | The append-only mutation stream in the Data Lake: one Parquet file per load run holding that run's inserts, updates and deletes. See [Lake feed](https://yres.eu/en/wiki/concepten/lake-feed). | :::note STAGE → HIS → Expose The fixed data spine is: source → ADF Copy → `STAGE` → `spLoadDWH` → `spHIS_InsertAndUpdate` → `HIS` (SCD2) → `spMaterializeViews` → Expose. ADF moves only bytes; all load logic lives in SQL. ::: ## SCD2 history and hashing | Term | Meaning | |---|---| | **SCD2** | Slowly Changing Dimension type 2 — the historization model of the HIS layer. On a change, the old version is retained as a closed row and a new current version is inserted. | | **`KeyHash`** | `varbinary(66)` hash (`HASHBYTES('SHA2_512', …)`) over the **key columns**, as a persisted computed column on STAGE. The match join is `STAGE.Keyhash = HIS.Keyhash AND HIS.isCurrent = 1`. | | **`RowHash`** | `varbinary(66)` hash (`HASHBYTES('SHA2_512', …)`) over the **rowhash-marked columns**. Change detection is `STAGE.Rowhash <> HIS.Rowhash`. A column with `Rowhash = 0` is excluded from change detection. | | **`ETL_Date`** | Load timestamp of the row; one `ETL_Date` per run, set at the start of the source pipeline. | | **`ETL_EndDate`** | End date of a row version. For the current (open) version this is the sentinel **`2999-01-01 00:00:00`**. When a row is closed, `ETL_EndDate` is set to the `ETL_Date` of the new version. | | **`isCurrent`** | `bit` flag: `1` = current version, `0` = closed/historical. Always `1` or `0`, never `NULL`. There is exactly one current row per key. | | **`Delta`** | `nvarchar(1)` flag on the HIS row: `'N'` = new, `'D'` = changed. | | **`_RowID`** | IDENTITY surrogate column per HIS table. The name is the target table name with non-alphanumeric characters removed (casing is preserved), plus `_RowID` (e.g. `AX_dbo_Cust` → `AXdboCust_RowID`). | | **Surrogate key** | Optional Yres-generated integer key for the natural key, stored in `[LoadManagement].[SurrogateKeys]`. Driven by `fxGetSurrogate(@Target)` and the setting `DefaultSurrogate`. | | **Delta (watermark)** | The change-column value that DELTA/DELTAIMAGE/ADDITIONAL keep as a watermark. After the load, `UsedTables.LatestRecord = MAX()` is updated, so the next run only fetches newer records. | ## Load engine | Term | Meaning | |---|---| | **Load type** | Determines what happens to the existing HIS data. There are **seven** types: FULL, DELTA, DELTAIMAGE, IMAGE, OVERWRITE, RELOAD, ADDITIONAL. Set per table in `LoadManagement.UsedTables.LoadType`, overridable per run. See [Load types](https://yres.eu/en/wiki/concepten/load-types). | | **`spLoadDWH`** | The entry point ADF calls after STAGE is populated. First runs an arbitration gate that deliberately fails the load while a `RUNNING` archiving run (LoadType `ARCHIVING`) exists for the target, then calls `spHIS_InsertAndUpdate` (the old `spUpdateETL_EndDate` call is commented out; end-dating now happens inside `spHIS_InsertAndUpdate`). | | **`spHIS_InsertAndUpdate`** | The SCD2 merge engine that processes STAGE → HIS. Handles six load types explicitly (`DELTA, DELTAIMAGE, IMAGE, OVERWRITE, RELOAD, ADDITIONAL`); `FULL` is the implicit default path. | | **`spMaterializeViews`** | After the load, refreshes the materialized/persisted views in the Expose layer. | | **Persisted view** | Stored result of a view query; reloaded in `level` order. | | **Master pipeline** | ADF pipeline configured from the frontend with conditional flow (on success / failure / completion). Available from v1.53. | | **Dynamic Workflow YRES** | The generic load pipeline that runs on metadata (parameters: Source, Schema, Table, LoadType). On older versions it is still called `Dynamic Workflow IRIS`. | ## Monitoring and logging | Term | Meaning | |---|---| | **`spWriteLoadStatus`** | The central load-status logger (`[Monitoring].[spWriteLoadStatus]`). Writes `LS_Pipeline` (at the start of a workflow/load), always `LS_Trans`, and sets the `LoadLog` status to RUNNING/SUCCEEDED/FAILED. | | **`PLANNED` / `SKIPPED`** | The two statuses that **`spPrepareWorkload`** (not `spWriteLoadStatus`) sets in `LoadLog` before the load even starts: `PLANNED` = this table load is actually picked up by the `ForEach` loop; `SKIPPED` = a not-yet-finished (`PLANNED`/`RUNNING`) load for the same table already existed, so this request is logged but not executed. See [Monitoring & logging](https://yres.eu/en/wiki/referentie/monitoring-logging). | | **`LoadLog`** | `[LoadManagement].[LoadLog]` — one durable row per table load with the status (`LoadStatus`: PLANNED → RUNNING → SUCCEEDED/FAILED, or PLANNED → SKIPPED), start/end time, error message and a JSON snapshot of the config. Forms the join backbone of `vwMonitor`. | | **`LS_Pipeline` / `LS_Trans`** | `[Monitoring].[LS_Pipeline]` = one row per pipeline/workflow run; `[Monitoring].[LS_Trans]` = one row per step (the fine-grained timeline). | | **`vwLoads`** | The canonical per-pipeline load timeline `[Monitoring].[vwLoads]` (start + runtime per step, row counts, status). | | **`vwMonitor`** | Broader monitor view `[Monitoring].[vwMonitor]` that also includes materialized views and Power BI refreshes. | :::note No `vwLoadMonitor` The view `vwLoadMonitor` does **not** exist in the live database. Use `vwLoads` (pipeline timeline) or `vwMonitor` (broader). See the [SQL reference](https://yres.eu/en/wiki/referentie/sql-interaction). ::: ## Settings, security and infrastructure | Term | Meaning | |---|---| | **Setting** | Configuration value in `[Config].[Settings]`, read via `[Config].[fxGetSetting](@SettingName)`. Determines schema names, storage type, pagination, scaling, and so on. | | **`SchemaHIS` / `SchemaStage`** | Settings that determine the HIS and STAGE schema names (default `ODS` and `STAGE` respectively), resolved per table by `Config.fxGetSchemaName`. | | **`DefaultStore`** | Setting for row- (read/write) or column-oriented (queries) storage of STAGE. Default `ROW`. | | **Service tier** | The Azure SQL database tier (settings `DefaultServiceTier`/`HighServiceTier`). Yres can automatically scale up and down around a load. | | **Surrogate key** | See [SCD2 history and hashing](#scd2-history-and-hashing). | | **SSO** | Single Sign-On via Azure; enforceable per user. | | **RBAC** | Role-based access control; roles defined as CRUD per permission type. The database role `Yres_dbreader` is built by `spGenerateDbreader`. | | **Key Vault** | Azure Key Vault where all credentials live. The frontend never stores secrets; source credentials go to the customer's Key Vault (`adf-{sourceName}-…`) and the linked service references them. |