--- title: "FMC loading windows & source data management" canonical: "https://docs.vaultspeed.com/space/VPD/3081633856/FMC%20loading%20windows%20%26%20source%20data%20management" format: markdown --- > Macro (toc) This document describes the different source data delivery and change types, how they interact with the FMC, and what settings in VaultSpeed modify this interaction. The main focus is on how loading windows are used and how data ends up in VaultSpeed. This document is structured such that all options are sorted from most ideal to less desirable. # Incremental Load using Modification Flag & Date An Incremental load using modification flag & date, or '**CDC source**', provides a history of all changes that happened in the source and what type of change it is. Each source object contains a change timestamp and change type field. In VaultSpeed, the source Change Data Capture type is set to ‘Incremental Load using Modification Flag & Date’. If `FMC_DYNAMIC_LOADING_WINDOW` is enabled, then the incremental load will load all data with a CDC timestamp between the start of the load and the end timestamp of the window of the last successful load. So if a load has failed or is not run, the next load will load more data. For the first incremental load, this will be the start date of the flow since this is the end date of the initial load. If `OBJECT_SPECIFIC_LOADING_WINDOW` is also enabled, the same loading window logic will be applied. But instead of having one window for the entire source, there is a separate window for each object. If the tasks in the flow are not grouped, then the success and failure are recorded at the source object level. This means that if a load fails for only one object, then only the window of that object will be increased in the next load. The successfully loaded objects will keep their smaller window. If `CDC_BASED_LOADING_WINDOW` is enabled, then the start date is still determined by the end of the previous load, but the end date is now determined by the maximum CDC timestamp of the source object. So as long as the records arrive in the correct order, this will ensure that all records always get loaded, even when they arrive late (i.e., records arrive after a load but with a CDC timestamp from before the load). Note that it can still be useful to have a small overlap between the loading windows (`FMC_OVERLAPPING_LOADING_WINDOWS`) to catch issues due to the synchronization of clocks between servers or small ordering issues due to latency. ## Local Most change data capture tools not only record the changes in a source but can also log them in another system. Ideally, the CDC tool transfers the changes from the source to the landing zone of the Data Vault. If the data arrives in files instead of directly to the Database, it is possible in some target technologies to define the INI and CDC tables as foreign tables that read from those files through the DDL options. In VaultSpeed, the source parameter `REMOTE_JOURNALING_TABLES` should be set to N. In this case, `FMC_GENERATE_SRC_LOADING_SCRIPTS` is not needed and has no effect. ## Remote The CDC data is not transferred to the Data Warehouse by the CDC system but is instead located on a remote system. In VaultSpeed, the source parameter `REMOTE_JOURNALING_TABLES` is set to Y. There are currently two methods of transferring the CDC data to the target with VaultSpeed, using a Database Link or using the FMC source loading. ### Database Link Parameters: - CREATE_REMOTE_DELTA_VIEW = Y - USE_REMOTE_CDC_DBLINK = Y - REMOTE_CDC_DBLINK_NAME - SCHEMA_CDCR, SCHEMA_MTDR, SCHEMA_DTVR In this scenario, VaultSpeed will generate delta views and a loading window table which should be deployed to the source database. The first ELT mapping will then select from those views through a Database link. This database link should be created by the user, and its name should be entered into the parameter. Example syntax: ``` SELECT * FROM "REMOTE_CDC_DBLINK_NAME"."SCHEMA_CDCR"."CDC_TABLE" ``` Not all target technologies support Database links and are also limited in which source technologies they can connect to. `CDC_BASED_LOADING_WINDOW` Is not yet supported for this scenario. ### FMC source loading This is only available for the Airflow FMC at the moment. parameters: - CREATE_REMOTE_DELTA_VIEW = N - USE_REMOTE_CDC_DBLINK = N - FMC_GENERATE_SRC_LOADING_SCRIPTS = Y - FMC_BATCH_SIZE - SCHEMA_CDCR - SCHEMA_INI_R In this scenario, the loading of the data is done by Airflow. The VaultSpeed plugin contains a custom operator which uses JDBC connections to transfer the data in batches from the source to the target. Depending on the different CDC types, VaultSpeed will automatically see whether to transfer ALL of the data from the source to the target or whether we only transfer the data in a given loading window. This is not suitable for huge amounts of data. To get the most optimal transfer speed, some tuning of the batch size might be required. The batch size parameter is used for creating an Airflow variable, so this can be changed later in Airflow itself. # Modification sequence source A modification sequence source provides the changed data along with a number indicating the order in which the changes happened. In VaultSpeed, the source/object CDC type is set to ‘Incremental load using Modification Sequence’ or ‘Full Load using Modification Sequence with Delete Management’. The name of the attribute containing the sequence can be set by modifying the parameter `CDC_SEQUENCE` or by setting the attribute to the “trans indicator” type in the source editor. This attribute will have the data type numeric in the Data Vault, meaning it can be an integer or a decimal. For this CDC type, the parameters `FMC_DYNAMIC_LOADING_WINDOW`, `OBJECT_SPECIFIC_LOADING_WINDOW`, and `CDC_BASED_LOADING_WINDOW` all have to be set to `Y`. This means that we will keep track of the last value of the sequence for each object when we load it. In the following loads, we will load all records with a sequence higher than that value. `FMC_OVERLAPPING_LOADING_WINDOWS` can not be used for this type of object. # Modification date source A Modification date source provides the source data together with a timestamp field indicating when it was last changed. In VaultSpeed, the source CDC type is set to ‘Incremental Load using Modification Date’ or ‘Full load using Modification Date with Delete Management’. ## Local If the source data arrives at the target through an external process, the FMC_GENERATE_SRC_LOADING_SCRIPTS parameter should be set to N. `FMC_DYNAMIC_LOADING_WINDOW`, `OBJECT_SPECIFIC_LOADING_WINDOW`, and `CDC_BASED_LOADING_WINDOW` behave just like in the case of a CDC source. # Time zone conversions VaultSpeed has some parameters that can help you handle different time zone settings between your source and target environments or even the FMC server. This only applies when your systems deliver you the dates without time zones. `CONVERT_SOURCE_TIMEZONE` this parameter will cast the source timezone to the target timezone during the source loading process (loading the data from the source to the target database). Your loading windows will be automatically adjusted to accommodate for the desired time zone. `CONVERT_FMC_TIMEZONE` this parameter will cast the timezone from the FMC server to the one of the target database, so you can get consistent loading windows between what you see in the FMC and your target environment. If you want to cast these time zones, you should indicate what the actual time zone is for source, target, and FMC. These can be set using the `SOURCE_TIMEZONE`, `TARGET_TIMEZONE`, and `FMC_TIMEZONE` parameters.