Skip to main content

Exporting with materialized views

For dashboards, SQL users, other pipelines and ML, MDMongoose publishes master data into Unity Catalog. You decide what is published with Export nodes: each one turns a branch of the hierarchy into a materialized view, a view, or a CSV / Excel file on a volume — the same table the Export dialog downloads. How it works inside: Architecture › Export.

Exports

Create an Export node​

  1. Go to Model › Exports and click Add Child. Choose the type Export.

  2. Fill in the properties:

    PropertyExample
    NameCustomer golden records
    Export TypeMaterialized view
    Object Namecustomer_golden_records (leave empty to derive it from the name)
    Root NodeMaster Data › Customer
    Child Depth1 — the Master Data node and its golden records
    Balancednone
    Include Properties, Include UIDson
  3. Click Add child. MDMongoose checks the Object Name — it must be new in mdm_export (or already belong to this node) and must not be one of the system objects — then:

    • a View is created at once;
    • a Materialized view, CSV or Excel export starts the mdm_export job; the object or file appears when the run finishes (the Last Run property shows the result).

The node's Location shows the full name of the view or the path of the file, and the Definition group shows the generated SQL.

The demo has three Export nodes to start from: Customer golden records (materialized view), Product hierarchy (CSV) (all six product levels, balanced, ; as delimiter) and Countries (view) — click Run Export on the last one to create the view.

Choosing the Export Type​

Export TypeGood forFreshnessWhere
Materialized viewDashboards, Genie, joins in other pipelines, large branchesrefreshed by mdm_export (after loads, golden refreshes and Run Export); incremental where possible<catalog>.mdm_export.<name>
ViewAlways-current data for ad-hoc SQL, small brancheslive — every query reads MDMNodes<catalog>.mdm_export.<name>
CSVFiles for other systems, SFTP drops, Excel userswritten by mdm_export/Volumes/<catalog>/mdm_core/exports/<name>.csv
ExcelBusiness users; one sheet, or one sheet per levelwritten by mdm_export/Volumes/<catalog>/mdm_core/exports/<name>.xlsx

Edits made in the app are in MDMNodes at once. A view shows them immediately; a materialized view or a file shows them after the next mdm_export run — click Run Export on the Export node to refresh it now.

Columns​

One row per deepest node of the branch; per level L<n>_UID, L<n>_Name, L<n>_Types and the properties of the NodeTypes found on that level:

SELECT L1_UID AS golden_uid, L1_Name AS customer, L1_VAT_ID, L1_Country, L1_City, L1_Match_Score
FROM workspace.mdm_export.customer_golden_records
WHERE L1_Country = 'Germany';
  • Enum and Link values are names (Germany, not a UID); arrays are comma-separated text.
  • Number, Float, Boolean and Timestamp properties keep their SQL types.
  • With Balanced, a branch that ends early repeats its last node on the lower levels; Balanced Flag adds a Balanced column that is true on those rows.
  • A new property or NodeType on a level becomes a new column at the next refresh (views: at the next save or Run Export).

System objects​

The pipeline also keeps these, for every deployment:

ObjectContents
nodesevery live node, one row per node
node_edgesevery ancestor → descendant pair with its distance — subtrees without recursion
node_indexUID → path and name (the app's lookup index)
node_historySCD 2 history of every node
-- every product below the "Garden" division
SELECT n.uid, n.name
FROM workspace.mdm_export.node_edges e
JOIN workspace.mdm_export.nodes n ON n.uid = e.descendant_uid
WHERE e.ancestor_uid = 'MDM-2CH';

-- how a golden record changed over time
SELECT * FROM workspace.mdm_export.node_history
WHERE uid = 'MDM-277T' ORDER BY __START_AT;

Configuring the export​

When it refreshes​

The mdm_export job (files, then the pipeline with the materialized views) runs:

  • after every load — mdm_load and the scheduled mdm_refresh start it,
  • after every golden-record refresh (mdm_action),
  • when an Export node is saved, or Run Export is clicked,
  • once from mdm_init.

To refresh on your own schedule, add a Run job task for mdm_export to your job, or start it by hand from Jobs & Pipelines. Its parameter uid limits the file exports to one Export node (all by default).

Changing or removing an export​

  • Edit the Export node and save: a view is replaced at once; materialized views and files at the next run.
  • Changing the Object Name of a view drops the old view. To switch a view to a materialized view (or back), use a new Object Name.
  • Delete the Export node: a view is dropped at once; a materialized view is no longer declared by the pipeline and is removed by its next update; files stay on the volume until you delete them.

Who can do what​

  • Creating an Export node needs Manager on Model › Exports (it adds a child) and at least Reader on the Root Node.
  • Editing one needs Modifier; Run Export needs Modifier (its Required Level).
  • The app's service principal needs CREATE TABLE on mdm_export for views — mdm_init grants it.

Giving consumers access​

Grant access with Unity Catalog, as for any table:

GRANT USE CATALOG ON CATALOG workspace TO `analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA workspace.mdm_export TO `analysts`;
-- files
GRANT READ VOLUME ON VOLUME workspace.mdm_core.exports TO `analysts`;

Consumers should read mdm_export, not MDMNodes: the objects are stable, named for their purpose and leave out deleted nodes. Unity Catalog grants on them are separate from the Steward permissions inside the app — an export publishes the whole branch to everyone who may read the schema.

Changing the target​

The pipeline's catalog and schema come from the bundle (resources/pipelines.yml, variable catalog). Deploy with another catalog to publish elsewhere.