Project.Core_Export Meta

by

Purpose

evw.project_core_export_meta is a serverless-friendly export view that prepares Project objects for downstream document-style consumers (e.g. Cosmos DB, AI/ML pipelines, Graph preparation), without relying on unsupported Synapse On-Demand constructs such as stacked / correlated FOR JSON queries.

It combines:

  • Core Project identity and naming from cvw.project_core_details
  • Meta Codes as a pre-built JSON payload (codesJson)
  • Meta Dates as a pre-built JSON payload (datesJson)
  • Last-change timestamps for incremental processing (LAST_CODES_DATE, LAST_DATES_DATE)

This view is designed as a Stage-1 export: it yields tabular rows with embedded JSON strings ready to be wrapped into final documents (Stage 2) and pushed to Cosmos DB or similar stores.

Source views

LayerViewRole
Corecvw.project_core_detailsOne row per Project object
Meta Codescvw.project_meta_codesAttribute/value rows for codes (JSON stored in VALUE_METADATA)
Meta Datescvw.project_meta_datesAttribute/value rows for dates (string stored in CODE_VALUE)

Output columns

ColumnMeaning
OBJECT_SEQUnique object identifier (Company + object code), used as the stable id for document stores
COMPANY_IDCompany identifier (from OBJECT_CLASS)
PROJECT_IDProject identifier (from OBJECT_CODE)
OBJECT_NAMEProject name
codesJsonJSON string representing all code attributes for the project
LAST_CODES_DATELatest CREATE_DATE found in meta codes rows for the project
datesJsonJSON string representing all date attributes for the project
LAST_DATES_DATELatest CREATE_DATE found in meta dates rows for the project

JSON shapes

codesJson

codesJson is built from cvw.project_meta_codes rows and outputs an array of single-key objects. Example:

[  {"programme":[{"CODE":"PRG-ENERGY","DESC":"Energy Programme"}]},  {"pru":[{"CODE":"4102","DESC":"Energy & Environment"}]}]

Where each element is:

  • key = ATTRIBUTE
  • value = VALUE_METADATA (already a JSON array)

datesJson

datesJson is built from cvw.project_meta_dates rows and outputs an array of single-key objects. Example:

[  {"start":"2025-09-01"},  {"end":"2026-06-30"}]

Where each element is:

  • key = ATTRIBUTE
  • value = CODE_VALUE (string)

Note: both payloads are intentionally schema-flexible: new attributes automatically appear without changes to this view.

How to use it

Validate a single project

Sql.Database(#"DataMart_Host",#"DataMart_Endpoint", 
[Query="EXEC get.myview 
@token = '<mytoken>', 
@datamart = 'project', 
@viewname = 'core_export', 
@version = 'meta', 
@viewSchema = 'evw',
@whereClause = PROJECT_ID = ''P-100234''
"])

Incremental processing guidance

Use LAST_CODES_DATE and LAST_DATES_DATE to detect changes since the last run.

Typical patterns:

  • Export rows where either last-date exceeds a watermark
  • Or compute a single “last updated” timestamp in Stage 2/pipeline

Example:

SELECT *
FROM evw.project_core_export_meta
WHERE LAST_CODES_DATE > @Watermark
OR LAST_DATES_DATE > @Watermark;

Design notes and constraints

  • This view avoids correlated FOR JSON subqueries because these can fail in serverless distributed execution.
  • It intentionally outputs JSON as strings (codesJson, datesJson), which can be later embedded using JSON_QUERY.
  • codesJson relies on VALUE_METADATA being valid JSON. If invalid JSON appears upstream;; treat that as a data quality issue (or harden with upstream validation).
  • Date values are treated as strings (CODE_VALUE) to avoid accidental type casting issues during export.

Leave a Comment