--- title: Effectivity Satellite description: An Effectivity Satellite stores the begin and end of the effectivity of a Hub's business keys, as the source delivers them, with their history. --- [Skip to content](https://knowledge.bigenius-x.com/create-an-effectivity-satellite#main-content) [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en) [![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/) Open main navigation Close main navigation - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new) - [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal) How can we help you? - There are no suggestions because the search field is empty. 1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en) 2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en) 3. [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type) # Effectivity Satellite **Applies to** Platform: Microsoft SQL Server · Snowflake · Snowflake dbt · Databricks · Databricks dbt · Fabric Lakehouse · Fabric Warehouse · Oracle Architecture: Data Vault An Effectivity Satellite stores the begin and end of the effectivity of a Hub's business keys, as the source delivers them, with their history. Use it when the source states from when to when a business object is in effect, for example the validity period of a loyalty card. After each load, the result view shows every version of these dates with its technical validity. ### Sources and relationships An Effectivity Satellite reads [Stages](https://knowledge.bigenius-x.com/create-a-stage?hsLang=en), [RawVault Composites](https://knowledge.bigenius-x.com/create-a-raw-vault-composite?hsLang=en) and [Store Entities](https://knowledge.bigenius-x.com/create-a-store-entity?hsLang=en) of a linked Data Store project, in one or more dataflows. It has exactly one relationship to a [Hub](https://knowledge.bigenius-x.com/create-a-hub?hsLang=en), and its foreign-key terms must be mapped. Instead of descriptive terms it carries the Default Terms `BG_Effectivity_Begin` and `BG_Effectivity_End`. [Current View](https://knowledge.bigenius-x.com/create-a-current-view?hsLang=en), [History View](https://knowledge.bigenius-x.com/create-a-history-view?hsLang=en), their Business Vault counterparts, BusinessVault Composite, [Vault Out](https://knowledge.bigenius-x.com/create-a-vault-out?hsLang=en) and the Business Vault types can read it. A PIT does not reference it. ### Create the model object - With the wizard: open the menu of a Stage, RawVault Composite or Store Entity, choose **Automate** and then **Create Effectivity Satellite**. Its steps are [Create Model Object](https://knowledge.bigenius-x.com/wizard-step-create-model-object?hsLang=en), [Create Dataflow](https://knowledge.bigenius-x.com/wizard-step-create-dataflow?hsLang=en), [Create Relations](https://knowledge.bigenius-x.com/wizard-step-create-relations?hsLang=en), [Map Foreign Key Terms](https://knowledge.bigenius-x.com/wizard-step-map-foreign-key-terms?hsLang=en) and [Add to View](https://knowledge.bigenius-x.com/wizard-step-add-to-view?hsLang=en). See [Create a Model Object with the wizard](https://knowledge.bigenius-x.com/create-a-model-object-with-the-wizard?hsLang=en). - Manually: [create a model object](https://knowledge.bigenius-x.com/create-a-model-object?hsLang=en) of type Effectivity Satellite in the Raw Vault layer, [add a relationship](https://knowledge.bigenius-x.com/add-a-relationship?hsLang=en) to the Hub, [create a dataflow set](https://knowledge.bigenius-x.com/create-a-data-flow-set?hsLang=en), [add the source model object](https://knowledge.bigenius-x.com/add-a-model-object-into-a-data-flow-set?hsLang=en) and [map the foreign-key terms](https://knowledge.bigenius-x.com/map-a-source-term-with-a-target-term?hsLang=en). After either way, map `BG_Effectivity_Begin` to the source term with the start date, and `BG_Effectivity_End` to the source term with the end date. ### Historization The row hash `BG_RowHash` covers `BG_Effectivity_Begin` and `BG_Effectivity_End`. The loader inserts a new version of a Hub key when its row hash differs from the current version. The new version starts at the load timestamp, and the previous version ends there. An unchanged row adds no version, and the loader never deletes or updates rows. [Calculate Effectivity End From Begin](https://knowledge.bigenius-x.com/calculate-effectivity-end-from-begin-model-object?hsLang=en) decides which `BG_Effectivity_End` the result view shows: - `false` (default): the end that the source delivered. - `true`, with `BG_Effectivity_End` mapped: the `BG_Effectivity_Begin` of the next row of the same Hub key, ordered by `BG_Effectivity_Begin`, and End of Universe for the last row. The calculated end replaces the mapped one, and validation reports warning ID-20004. See [BG\_Effectivity\_End](https://knowledge.bigenius-x.com/bg_effectivity_end?hsLang=en). ### Properties - [Calculate Effectivity End From Begin](https://knowledge.bigenius-x.com/calculate-effectivity-end-from-begin-model-object?hsLang=en): `false` (default) or `true`, see Historization. - [Implementation Type](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en): `Permanent` only. - [Foreign Key Mode](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en): whether the table gets a foreign key to the Hub table. With `Create Disabled`, the default, it is created and disabled. - [Load Caching](https://knowledge.bigenius-x.com/load-caching-model-object?hsLang=en): empty (default) or `Hashing`, which stores the hashing view in a table before the loader runs. - [Increment Management Type](https://knowledge.bigenius-x.com/increment-management-type-model-object?hsLang=en): `Auto` (default) or `User`, for an incremental load with [`BG_IncrementalFilter`](https://knowledge.bigenius-x.com/bg_incrementalfilter?hsLang=en). See [Configure an incremental load](https://knowledge.bigenius-x.com/configure-an-incremental-load?hsLang=en). - [Excluded from Delta Detection](https://knowledge.bigenius-x.com/excluded-from-delta-detection-model-object?hsLang=en): terms that are stored but do not start a new version. - [Deduplication Method](https://knowledge.bigenius-x.com/deduplication-method-model-object?hsLang=en) and [Sort Order within Partition](https://knowledge.bigenius-x.com/deduplication-partition-model-object?hsLang=en), per dataflow. - [Use Embryo Loader](https://knowledge.bigenius-x.com/embryo-loader?hsLang=en), on the relationship: inserts the Hub keys that the Hub does not hold yet. The project properties [Persist Validity](https://knowledge.bigenius-x.com/persist-validity-project?hsLang=en) and [Create Result Views](https://knowledge.bigenius-x.com/create-result-views-project?hsLang=en) also apply. ### Default Terms - [`Hub_HK`](https://knowledge.bigenius-x.com/hub_hk?hsLang=en): the key of the Hub row, calculated from the foreign-key terms. - [`BG_Effectivity_Begin`](https://knowledge.bigenius-x.com/bg-effectivity-begin?hsLang=en) and [`BG_Effectivity_End`](https://knowledge.bigenius-x.com/bg_effectivity_end?hsLang=en): the begin and end of the effectivity. - [`BG_ValidFromTimestamp`](https://knowledge.bigenius-x.com/bg_validfromtimestamp?hsLang=en): start of the version. With `Hub_HK` it forms the primary key. - [`BG_ValidToTimestamp`](https://knowledge.bigenius-x.com/bg_validtotimestamp?hsLang=en): end of the version. - [`BG_RowHash`](https://knowledge.bigenius-x.com/bg_rowhash?hsLang=en): hash of the effectivity terms, compared with the current version. - [`BG_IncrementalFilter`](https://knowledge.bigenius-x.com/bg_incrementalfilter?hsLang=en): source term for an incremental load; not stored in the table. - [`BG_LoadTimestamp`](https://knowledge.bigenius-x.com/bg_loadtimestamp?hsLang=en) and [`BG_SourceSystem`](https://knowledge.bigenius-x.com/bg_sourcesystem?hsLang=en): the load that inserted the row and its source system, empty unless you map it. - [`BG_DataQuality_ID`](https://knowledge.bigenius-x.com/bg-dataquality-id?hsLang=en), [`BG_ErrorCount`](https://knowledge.bigenius-x.com/bg_errorcount?hsLang=en) and [`BG_ErrorDescription`](https://knowledge.bigenius-x.com/bg_errordescription?hsLang=en): hidden terms of the data quality objects. ### Generated objects These are the names on Microsoft SQL Server for an Effectivity Satellite with one dataflow. `` stands for the name of the model object: | Role | Generated name | Purpose | | --- | --- | --- | | Source | `RDV_ESAT__Source` | Reads the sources | | Deduplication | `RDV_ESAT__Deduplication` | Removes duplicate rows | | Hashing | `RDV_ESAT__Hashing` | Calculates the hash keys | | HashingStorage | `RDV_ESAT__HashingTable` | Stores the hashed rows | | HashingLoader | `RDV_ESAT__HashingLoader` | Fills the hashing table | | IncrementSource | `RDV_ESAT__IncrementSource` | Finds the new increment | | Increment | `RDV_ESAT__Increment` | Stores the increments | | EmbryoLoader | `RDV_ESAT__EmbryoLoader` | Adds missing Hub keys | | Delta | `RDV_ESAT__Delta` | Selects new versions | | Loader | `RDV_ESAT__Loader` | Inserts the new versions | | Storage | `RDV_ESAT_` | Effectivity Satellite table | | Result | `RDV_ESAT__Result` | Query view with validity | - Loaders are stored procedures; the Effectivity Satellite table, the hashing table and the increment table are tables; all other objects are views. - The deduplication view exists only with a Deduplication Method other than `None`. - The hashing table and hashing loader exist only with Load Caching `Hashing`. - The increment table exists only with an incremental load, that is, when a dataflow maps `BG_IncrementalFilter`; the increment source view only with Increment Management Type `Auto`. - The embryo loader exists only when Use Embryo Loader is true on the relationship. It runs before the loader. - The result view exists only with Create Result Views; without it, Persist Validity must be true. - With several dataflows, all objects except the table and the result view exist once per dataflow, with the dataflow name in their names. - The table has no unknown-member row. With the default settings, a load runs this chain: ``` sources -> _Source -> _Hashing -> _Delta -> _Loader _Loader -> RDV_ESAT_ -> _Result _Result -> _Delta (current versions) ``` The delta view keeps the source rows whose `Hub_HK` and `BG_RowHash` do not match a current version in the result view. The loader inserts them with the load timestamp as `BG_ValidFromTimestamp`. `_Deduplication` sits between `_Source` and `_Hashing`, `_HashingTable` after `_Hashing`, and `_Increment` filters `_Source`. ### Example The Effectivity Satellite `load_loyaltycard_esat` on Microsoft SQL Server reads the Stage `LOYALTYCARD` and has a relationship to the Hub `common_loyaltycard`. It maps `BG_Effectivity_Begin` to `VALID_FROM` and `BG_Effectivity_End` to `VALID_TO`. Calculate Effectivity End From Begin and Persist Validity are false. The loads deliver these cards: - Load 1 on 2026-10-28 08:00: cards 501 and 502, both valid from 2026-01-01 to 2026-12-31. - Load 2 on 2026-10-29 08:00: the same, and the new card 503, valid from 2026-10-29 to 2027-10-28. - Load 3 on 2026-10-30 08:00: card 501 is renewed from 2027-01-01 to 2027-12-31, and card 502 ends on 2026-10-30. - Load 4 on 2026-10-31 08:00: the same rows as load 3. ### Check the result Query the result view with the business key of the Hub, here in the schema `p2`: ``` SELECT h.LOYALTYCARD_ID, s.BG_Effectivity_Begin, s.BG_Effectivity_End, s.BG_ValidFromTimestamp, s.BG_ValidToTimestamp FROM p2.RDV_ESAT_load_loyaltycard_esat_Result AS s JOIN p2.RDV_HUB_common_loyaltycard_Result AS h ON h.Hub_HK = s.Hub_HK ORDER BY h.LOYALTYCARD_ID, s.BG_ValidFromTimestamp; ``` This is the result after load 4, with the column names and timestamps shortened: ``` CARD Begin End ValidFrom ValidTo 501 2026-01-01 2026-12-31 2026-10-28 08:00 2026-10-30 08:00 501 2027-01-01 2027-12-31 2026-10-30 08:00 9999-12-31 00:00 502 2026-01-01 2026-12-31 2026-10-28 08:00 2026-10-30 08:00 502 2026-01-01 2026-10-30 2026-10-30 08:00 9999-12-31 00:00 503 2026-10-29 2027-10-28 2026-10-29 08:00 9999-12-31 00:00 ``` - Load 3 changed the dates of cards 501 and 502. Each got a second version, and the first one ended at the load timestamp. - Loads 2 and 4 added no version for unchanged cards. ### Platform differences - Persist Validity is true by default on Snowflake, Snowflake dbt, Databricks, Databricks dbt, Fabric Lakehouse and Fabric Warehouse, and false on Microsoft SQL Server and Oracle. With true, the table stores `BG_ValidToTimestamp`. - The data types of the effectivity terms differ per platform, see [BG\_Effectivity\_Begin](https://knowledge.bigenius-x.com/bg-effectivity-begin?hsLang=en). - Snowflake: names are in capitals, and the loader is a stored procedure. A load control task calls it. The dataflow properties [Use Stream](https://knowledge.bigenius-x.com/use-stream-model-object?hsLang=en) and [Warehouse Placeholder Variable Name](https://knowledge.bigenius-x.com/warehouse-placeholder-variable-name-dataflow?hsLang=en) apply. - Databricks and Fabric Lakehouse: table and view names are in lowercase, and the loader is the notebook `RDV_ESAT__Loader`. The properties [File Format](https://knowledge.bigenius-x.com/file-format-model-object?hsLang=en), [Optimization Method](https://knowledge.bigenius-x.com/optimization-method-model-object?hsLang=en), [Optimization Method Columns](https://knowledge.bigenius-x.com/optimization-method-columns-model-object?hsLang=en) and [Use Sequential Dataflow Execution](https://knowledge.bigenius-x.com/use-sequential-dataflow-execution-model-object?hsLang=en) apply. Fabric Lakehouse allows only Foreign Key Mode `None`. - Databricks dbt and Snowflake dbt: the Effectivity Satellite becomes the dbt models `rdv_esat__source` and `rdv_esat_`. The model `rdv_esat__result` exists only when Create Result Views is true; the default on dbt is false. Load Caching, Increment Management Type and Use Embryo Loader are not available. - Fabric Warehouse: the same objects as on Microsoft SQL Server, deployed by notebooks. - Oracle: names are in capitals, and the loader is a PL/SQL package. [Partition Clause](https://knowledge.bigenius-x.com/partition-clause-and-table-storage-model-object?hsLang=en) adds its text to the `CREATE TABLE` statement. Table Compression, the tablespaces and the statistics properties also apply. - Foreign Key Mode `Create Enforced` exists only on Microsoft SQL Server and Oracle. ### Limitations - An Effectivity Satellite has exactly one relationship, to a Hub. - An unmapped `BG_Effectivity_Begin` raises a validation error. - A term other than the Default Terms, business keys and foreign keys raises a validation error. Business keys raise a validation warning. - Custom Singleton Values, Version Valid From Term and Hash Group Size are not available. ### Release differences - Generator 2.2: Increment Management Type, Use Embryo Loader and Persist Validity are available. - Generator 2.4: Excluded from Delta Detection is available. ### Related articles - Previous: [Hub](https://knowledge.bigenius-x.com/create-a-hub?hsLang=en) - Next: [Configure an incremental load](https://knowledge.bigenius-x.com/configure-an-incremental-load?hsLang=en) - Reference: [BG\_Effectivity\_Begin](https://knowledge.bigenius-x.com/bg-effectivity-begin?hsLang=en), [BG\_Effectivity\_End](https://knowledge.bigenius-x.com/bg_effectivity_end?hsLang=en), [Calculate Effectivity End From Begin (Model Object)](https://knowledge.bigenius-x.com/calculate-effectivity-end-from-begin-model-object?hsLang=en), [Hub\_HK](https://knowledge.bigenius-x.com/hub_hk?hsLang=en), [BG\_RowHash](https://knowledge.bigenius-x.com/bg_rowhash?hsLang=en), [Persist Validity (Project)](https://knowledge.bigenius-x.com/persist-validity-project?hsLang=en), [Create Result Views (Project)](https://knowledge.bigenius-x.com/create-result-views-project?hsLang=en), [Excluded from Delta Detection (Model Object)](https://knowledge.bigenius-x.com/excluded-from-delta-detection-model-object?hsLang=en), [Foreign Key Mode (Model Object)](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en) - See also: [Effectivity Link Satellite](https://knowledge.bigenius-x.com/create-an-effectivity-link-satellite?hsLang=en), [Satellite](https://knowledge.bigenius-x.com/create-a-satellite?hsLang=en), [Change detection and historization](https://knowledge.bigenius-x.com/change-detection-and-historization?hsLang=en), [Embryo Loader](https://knowledge.bigenius-x.com/embryo-loader?hsLang=en), [Create a Model Object with the wizard](https://knowledge.bigenius-x.com/create-a-model-object-with-the-wizard?hsLang=en) - [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content) - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines) - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working) - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account) - [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en) - [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en) - [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content) - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration) - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions) - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features) - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects) - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches) - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections) - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview) - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps) - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms) - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping) - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships) - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality) - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes) - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling) - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts) - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings) - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace) - [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content) - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse) - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse) - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks) - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake) - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server) - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle) - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts) - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders) - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment) - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment) - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline) - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment) - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment) - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control) - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow) - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type) - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties) - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms) - [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content) - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations) - [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content) - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches) - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases) - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules) - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules) - [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en) - [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content) - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application) - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application) - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators) - [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content) - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs) - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits) - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) - [Customer Portal](https://knowledge.bigenius-x.com/customer-portal) - [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login) [![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com) All rights reserved © 2025, biGENIUS AG