Store Composite

A Store Composite prepares data in the Store layer with joins, filters, expressions and aggregations on Stages or other Store Composites. Use it for an intermediate result that a Store Entity or another Store Composite reads. By default it is a view; with Implementation Type Cached it is a table that each load empties and refills.

Sources and relationships

A Store Composite reads Stages and other Store Composites in exactly one dataflow. It has no relationships. A Store Entity or another Store Composite reads its result view.

Create a Store Composite

• With the wizard: open the menu of a Stage or Store Composite, choose Automate and then Create Store Composite. Its steps are Create Model Object, Create Dataflow, Map Selected Terms and Add to View. See Create a Model Object with the wizard.
• Manually: create a model object of type Store Composite in the Data Store column, create a dataflow set, add the source model objects and map the terms.

Then add joins, a where clause or expressions to the dataflow sets as the composite needs.

Properties

• Implementation Type: Virtual (default) creates views only, so every query reads the current Stage data. Cached adds a table that the loader empties and refills on every load.
• Deduplication Method: None (default), Distinct or Partition, with Sort Order within Partition. A method other than None adds a deduplication view.

Default Terms

• BG_LoadTimestamp: with Cached, the loader writes the timestamp of the load. With Virtual, the term is NULL unless you map it.
• BG_SourceSystem: the source system of the row; empty unless you map it.

Generated objects

These are the names on Microsoft SQL Server for a Store Composite large_branch:

• The loader is a stored procedure; DS_SC_large_branch is a table; all other objects are views.
• The deduplication view exists only with a Deduplication Method other than None.
• The loader and the table exist only with Cached.

The dependencies are:

Virtual: sources -> _Source -> _Deduplication -> _Result
Cached:  sources -> _Source -> _Deduplication -> _Loader
         _Loader -> DS_SC_large_branch -> _Result

Example

The Store Composite large_branch on Microsoft SQL Server keeps only the large branches of the Stage branch. It has the default Implementation Type Virtual and maps the business terms of the Stage; BG_LoadTimestamp is not mapped. Its dataflow set has this where clause, in which s1 is the alias of the Stage:

s1.MARKET_SIZE = 'large'

The generated source view ends with:

FROM [<database name>].[stg].[STG_ST_branch_Result] AS [s1]
WHERE [s1].[MARKET_SIZE] = 'large'

<database name> is the Stage database you set before deployment, and stg is the Stage schema of the example. The Stage branch holds three branches after load 1 and two after load 2:

• Load 1: 1 Basel large, 2 Bern small, 3 Zurich large.
• Load 2: Zurich is deleted, and Bern changes to large.

Check the result

Query the result view, here in the schema ds:

SELECT BRANCH_ID, BRANCH_NAME, MARKET_SIZE
FROM ds.DS_SC_large_branch_Result
ORDER BY BRANCH_ID;

After load 1:

BRANCH_ID  BRANCH_NAME  MARKET_SIZE
1          Basel        large
3          Zurich       large

After load 2 of the Stage; a Virtual Store Composite has no loader:

BRANCH_ID  BRANCH_NAME  MARKET_SIZE
1          Basel        large
2          Bern         large

• Bern appears and Zurich disappears as soon as the Stage is reloaded, because the views read the result view of the Stage.
• With Cached, the result changes only after the loader of the Store Composite has run.

Platform differences

• Snowflake: names are in capitals, and the table of a Cached Store Composite is a transient table. The load control task DS_SC_<model object name>_TASK calls its loader. The dataflow properties Use Stream and Warehouse Placeholder Variable Name apply.
• Databricks and Fabric Lakehouse: the loader is the notebook DS_SC_<model object name>_Loader. The properties File Format, Optimization Method and Optimization Method Columns apply.
• Fabric Warehouse: the same objects as on Microsoft SQL Server.
• Oracle: names are in capitals, and the loader is a PL/SQL package. Physical properties such as Partition Clause, Table Compression and tablespaces apply.

Limitations

• A Store Composite has exactly one dataflow and no relationships.
• An aggregation must be defined in all dataflow sets or in none.
• A Store Composite keeps no history; it has no validity terms.

Related articles

• Previous: Stage
• Next: Store Entity
• Reference: Implementation Type (Model Object), Deduplication Method (Dataflow), Sort Order within Partition (Dataflow), BG_LoadTimestamp, BG_SourceSystem
• See also: Create a Model Object with the wizard, Model a Data Store Solution