Search docs.molo17.com

    Data modeling

    Most pipelines copy a table to a table or a collection to a collection. Data modeling changes the shape on the way: a set of normalized SQL Server tables lands as one nested document, and a Couchbase document lands as rows in several tables.

    Available from Gluesync 2.3.2.

    The two directions

    Query entities Document models

    Direction

    Relational to document

    Document to relational

    What you write

    One SQL SELECT for the root and one for each child level. Joins and lookups are allowed.

    A model of levels: for each level the field of the document it reads, its key and the link to its parent.

    What you get

    One document per root key, with each child level as an array (MANY) or an object (ONE) inside it. A model with no child level gives flat documents or rows.

    One row per document in the root table, and the rows of each level in a table of its own, linked by the root key.

    Source

    SQL Server, with the MSSQL CDC agent

    Couchbase, with the Couchbase agent and its DCP change data capture

    Target

    Couchbase (flat and nested documents) and SQL Server (flat rows)

    SQL Server

    Created with

    The wizard of the Control Plane (flat entities), the REST API or the MCP server

    The REST API or the MCP server

    Page

    Query entities

    Document models

    Both directions keep the target in step with the source after the first snapshot. A change in any table a Query entity reads, or in any part of a document, reaches the target as a change of the whole document or of the rows of that document. The target converges to the current state of the source: intermediate states may be skipped, for example when two updates of one row land in the same window.

    When to use which

    • Your data lives in normalized SQL Server tables and an application, an API or a search index wants one document per business object, for example an order with its lines and its shipment: use a Query entity.

    • Your data lives in Couchbase documents with arrays and nested objects and you want to query or report on it with SQL: use a document model.

    • You only need a table copied as it is, or a collection copied as it is: use a regular entity, as in Pipeline concepts and task map. Data modeling adds nothing there.

    • You run data modeling configurations on Gluesync 1.x and want to move them: see Migrate Gluesync 1.x data modeling configurations.

    Words used on these pages

    Term Meaning

    Model

    The tree of levels that describes the documents or the rows, plus the options shared by all of them.

    Root

    The top level. It stands for the whole document, and for the row of the root table.

    Level

    One node of the tree. In a Query entity a level is a SQL statement. In a document model a level is a field of the document, such as an array of cards, that has a table of its own.

    Root key

    The columns that identify one document. In a Query entity they are columns of the first table of the root statement. In a document model they come from the document id, or from a column that stores it.

    Item key

    The columns that identify one item of a child level inside its parent.

    Parent link

    The columns of a child level that point to the key of its parent. The key of a child level always starts with them, so every level carries the root key.

    Cardinality

    MANY for an array of items, ONE for an object.

    What both directions have in common

    • Check before you save. Describe, preview and validate show the result without writing anything. They return issues with a stable code and a hint, so a model that cannot be maintained correctly is refused at save time, not found out during replication.

    • Keys are plain. Every key and parent link is a plain column or field, never an expression. This is what lets Gluesync find the document a change belongs to.

    • Data errors follow the write strategy. A document that cannot be built or written, for example because two rows share a key, is a data error. The target entity’s write strategy decides what happens: FAIL stops the entity, SKIP and UPSERT skip that document, count it and notify. See Write strategies.

    • Nothing else writes the same data. A Query entity owns its documents and a document model owns its rows. Do not let another entity or an application write them.

    • Systemic problems stop the entity. A change history that has been cleaned up before Gluesync read it, or a window that would delete more than the configured limit, stops the entity with a named code. Gluesync never skips changes silently.

    Where to go next