Data Product tables¶

A great part of the tables in the Sidra Data Product are similar in structure and content to the ones stored in the Sidra Service. Please see the section related to Sidra Service metadata to check this. Even so, there is slight differences in certain tables or even new ones aimed at extracting the maximum functionality for the customer purposes in Data Products. Below, you can see a summary table with the existing tables for Data Products.

AssetTimeTravel table¶

This table is populated by Sidra with the information of Time Travel provided by Client Databricks when the data of the Assets is inserted in Client Databricks.

EntityPipeline table¶

The same extraction pipeline can be used to extract several Assets from different Entities. That can be configured by associating the same pipeline to several Entities using the EntityPipeline table.

PipelineExecutionProperties column¶

The pipelineExecutionProperties column in the EntityPipeline table of Data Products is being used to add a ConsolidationMode as an Overwrite parameter which will take action before any data load. A Databricks notebook will be in charge of doing the following functions while checking the parameter status:

• When ConsolidationMode is set up as Overwrite, the entire table will be overwritten.
• When ConsolidationMode is set up as Merge (default), the data will be merged if there is a Primary Key, otherwise the data will be appended.
• When ConsolidationMode is set up as Append, the data is appended.
• When other value is set up in this parameter, it will throw an error stating that the ConsolidationMode XXX is not valid.
• When the parameter does not exist or it is empty, it will take the default value of Merge.

ExtractPipelineExecution table¶

The ExtractPipelineExecution table is an execution tracking table that is only present in a Data Product.

If the Sync Behavior (see the Sync Behavior documentation) is LoadUpToLastValidDate, the Asset load status per pipeline will be checked from this table.

ExtractPipelineExecutionStatus table¶

The ExtractPipelineExecutionStatus table is a reference table to enumerate the different statuses involved along a Data Product pipeline execution.

ExtractPipelineExecutionStatuses table¶

The statuses for this table are updated by some Data Product pipeline activities.

Load tables¶

Tables group designated to check the loadings and their proper operation during the process.

LoadProcess table¶

This table tracks and logs the details of data loading processes.

LoadProcessTask table¶

This table stores information about monitoring of individual tasks within a data loading process.

LoadStatus table¶

LoadStatusEnum table¶

Pipeline table¶

The Pipeline table in Data Products has some additional fields compared to the fields that the Pipeline table has in Sidra Service:

• ExecutionParameters: This is an additional field used for including values for the parameters of the Data Product pipeline when the pipeline is executed by the Sync job.

PipelineProvider table¶

An association between the Pipeline and Provider can be added using the PipelineProvider table. This association is used by the Sync job to execute only those pipelines that are associated to enabled Providers, i.e. those Providers with false in the IsDisabled field.

PipelineSyncBehaviour table¶

PipelineSyncBehaviour is a table that defines the different type of behaviour that is going to be executed from the Sync job perspective. Sync job is the responsible to synchronize metadata and execute the pipelines associated with the Entities.

The LoadUpToLastValidDate Sync behavior works similarly to type LoadPendingUpToValidDate, but checking the ExtractPipelineExecution to work with multiple pipelines. This is the mandatory Sync behavior for loading an Entity with several pipelines. This is also the recommended Sync behavior for all new load pipelines. This Sync behaviour will retry not finished Assets by pipeline for a number of times by default (3), this can be changed through AppService configuration setting up the Sync.AssetRetries key.

The behaviors LoadPendingUpToValidDate or LoadUpToLastValidDate are required in order to avoid situations like the following:

• If there is an error in the Data Intake Process (Sidra Service ingestion, incremental loads) when some of the Entities are loaded but the failed Entities are not loaded. The overall status of the job is Succeeded, because this way we do not block the iterative batch process in the pipeline to load all Entities. When this issue happens, e.g. if there are a set of three Entities that are marked as Mandatory on the Data Product side (table EntityPipeline), the Sync job will not trigger the pipeline until all three Assets have been generated, which could take several days (e.g. three days, assuming that on day 3 we finally have the three mandatory Assets).

Sync behavior LoadAllNewDates is only capable of loading all completed date Assets on the date of execution of the Sync job, leaving the already loaded increments in Sidra Service without loading on the Data Product staging tables. For some business logic of Data Products, we may need to know exactly which days there were Assets that were not processed, in order to retrieve this missing data by the Data Product.

Sync behaviors LoadPendingUpToValidDate or LoadUpToLastValidDate loads any Asset BEFORE UP TO the most recent day in which there are available Assets for every mandatory Entity.

??? Info "Update the Sync behaviour of pipelines Update the Sync behaviour from the endpoint:

PUT /api/pipeline

StagingConfiguration table¶

Data Products contain StagingConfiguration table, which contains the configuration of the new table names in Staging that need to be created, as well as the queries to create these new tables.

The StagingConfiguration table is needed in order to parametrize these queries in the Databricks notebook.

TableImport table¶

This table stores the name of tables imported from the Data Storage Unit.

PipelineTemplateType table¶

This table stores the template definition types for Azure Data Factory pipelines.

PipelineTemplateTypes table¶

This table defines all the pipelines templates types.

TabularMetadata table¶

This table stores the metadata related with the tabular model, defining the structure and relationship of the data within it.

TabularPartition table¶

This table provides details about the partitions in the tabular model, as well as metadata like partitioning limits or update dates.

Measure table¶

This table contains information about measures used in the tabular model.

RoleMember table¶