| id | delivery-wiki-pattern-catalog | ||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| title | Pattern catalog: which delivery problem maps to which OSDU Delivery shape | ||||||||||||||||||||||||||||||
| type | map | ||||||||||||||||||||||||||||||
| summary | Problem-first index: where a lookup belongs, one kind or interfaces, which route, retrieval or explorer, tests, orphans, and the page for each. | ||||||||||||||||||||||||||||||
| keywords |
|
||||||||||||||||||||||||||||||
| sourceRefs |
|
||||||||||||||||||||||||||||||
| referenceRefs |
|
||||||||||||||||||||||||||||||
| related |
|
||||||||||||||||||||||||||||||
| updated | 2026-10-30 |
Start here when the question is "how do I deliver X" or "where does Y belong" rather than "what does key Z do". Each row names a recurring delivery problem, the shape OSDU Delivery solves it with, and the page that documents that shape. Key semantics live on the reference pages; this page only points. Problems that are SQLFlow's (landing files, incremental ingestion, upserts, scheduling, secrets) are answered by SQLFlow's pattern catalog, and the first section here only shows where they meet the OSDU flows.
The examples use one estate throughout: a well database exported as files (welldb), landed into pre tables, keyed into
OsduData.silver.* by ing flows, and delivered as wellbores and well logs to the partitions dev and test.
| Problem | Shape | Page |
|---|---|---|
| Source files must reach OSDU | A file flow lands them, an ing flow keys them, a delivery flow reads the keyed table; three flows, ordered by lineage |
getting started, OSDU documents |
| The source is another database, not files | An ing flow straight from it into the keyed table; the delivery flow does not care how the table was filled |
SQLFlow: foreign databases |
| A delivery must only see rows that changed | Keep the ing flow's systemColumns.updatedDate on: UpdatedDate_DW is the delivery's incremental window |
SQLFlow: ing flow, delivery flow, change detection |
| Every delivered record must point back at its file and row | Nothing to add: the file flow's FileName_DW and RowNumber_DW ride through to the ledger (source.systemColumns renames them; a renamed provenance column the table lacks is refused) |
delivery flow, the ledger |
| A record has child rows (a log's curves) | source.datasets with a join on the key, repeated in the mapping with $forEach |
delivery flow, mapping values |
| A large table should page fast and spread over nodes | source.record.primaryKey (the ing flow's identityColumn); a delivery of 2,000 rows or more is split over nodes by default (parallelism.nodes, 0025) |
delivery flow, submissions |
| One flow should serve several slices of one table (one log source each) | parameters plus source.record.scope, each run naming its value, or several values to read their slices in one run (0024) |
delivery flow |
The most common design question. A mapping can look a value up in six places; pick by where the truth lives and how big it is.
| The lookup is | Shape | Page |
|---|---|---|
| OSDU reference data the partition holds (units, curve types, sampling domains) | A cache flow type with kind: "osdu:wks:reference-data--UnitOfMeasure:*", read in the mapping with ref or $cache and $findBy |
reference data cache, cache flow, lookups |
| Your own table, maintained outside OSDU (how the source spells a unit, as the partition's code) | Files, a pre flow, an ing flow to OsduData.silver.UnitAlias, a cache flow type with table:, then replace: $cache.UnitAlias in the mapping |
lookup table cache, modifiers |
| A handful of fixed pairs, reviewed in git | A dictionary document (dictionaries/sampling-domain.yaml), held by a cache flow type with dictionary: |
dictionary |
| Only one mapping needs it, and it is a few values | An inline table: replace: { DEPTH: Depth } in the mapping itself, no cache at all |
modifiers |
| The distinct values OSDU records actually hold (cleaned names, the keys behind each) | A dimension flow builds it; a cache flow type with dimension: and dimensionFlow: holds it for mappings |
dimension, cache flow, dimensions guide |
| Master data far too large to cache (every wellbore of a partition) | A searches entry and $search with $findBy: asked of the platform as each record renders, never captured |
lookups |
| Problem | Shape | Page |
|---|---|---|
| A lookup table holds a key and one value | replace: $cache.<Type> matches the key and takes the one field beside it; with more fields, name one with field: |
modifiers |
| A value may be spelled several ways, or be missing from the table | $coalesce: the first alternative that gives a value (the table, then the partition's records, then the value as it stands) |
mapping values |
| A reference should go out even before the partition holds its record | $unverified: true on the node building it: written, and recorded as unverified |
mapping values, preflight |
| Several properties need the same looked-up record (a log's wellbore and its access groups) | A lookups entry, matched once per row and read with $lookup |
lookups |
| A changed cached value must be reviewed before it reaches records | onChange: approve on the cache flow or the type: affected records wait for approval on the Cache page |
partition cache, cache flow |
| A delivery must render against a fixed cache, not the newest | render.cacheVersion: <version label> (a flow of one partition only) |
delivery flow |
| A delivery must render against records delivered just before it (units, then the logs naming them) | cache.before on the pipeline that needs them current, or cache.after on the one delivering them (auto works the types out); no refresh between the two |
delivery flow, partition cache, 0017 |
| Problem | Shape | Page |
|---|---|---|
| Start a mapping from an OSDU schema | Put the template in a templates folder on the mapping's path (sqlflow template capture --out, or Download on the Templates page's Browse OSDU tab), sync the repository, then the mapping builder |
templates, writing a mapping |
| Two projects of one repository fill different versions of one kind | A templates folder in each project: a document reads the nearest folder holding the kind, and may name its kind alone |
templates |
| A kind of one's own, or one extended for a project | sqlflow template import <file> --kind <kind> --out <folder>/<file>.json, committed with the mappings; the next sync uses it |
templates, sqlflow template |
| A custom copy of a canonical kind that also needs what only the canonical record holds (a log's DDMS curve files) | Link the custom template to the canonical kind (x-osdu-inheriting-from-kind), key both mappings alike, and take the sections with $parent; a storage flow delivers the custom records after the canonical flow |
linked records, the guide, mapping $parent, 0019 |
| The canonical record should point at the custom record beside it, as the custom one points at its parent | $child: <custom kind> in the canonical mapping, on a property of ExtensionProperties or a tag (the canonical schema stays as OSDU publishes it); the first delivery goes out without the link, and the custom record landing makes the canonical flow's next run add it |
linked records, the guide, mapping $child, 0023 |
| One record is built from several sources, each changing at its own time (a log's header, its LAS file, a grade) | An enrichment: the mapping's enrich: says what each adds, the flow binds its rows (source.datasets.<name>), and the record with everything added goes out once per change |
enrichments, enrichment mappings, 0036 |
| A record must list files or records of another kind its rows name (a dataset per LAS file) | An enrichment whose mapping fills that kind adds one per row, under an id kept for good, and the record links it with $link: <name> |
records an enrichment adds, 0036 |
| A property only applies to some rows | $when: <condition> on the node |
mapping values, expressions |
| An empty value should leave the property out rather than hold the record | $required: false |
mapping values |
| A value needs computing (concatenation, arithmetic, a choice) | $expr |
expressions |
| A reference id is built from a value | The ref modifier ({$param.dataPartition}:<group>--<Entity>:{$value}:, the entity type the template's property points to), or id with a template of your own |
modifiers |
| The record's OSDU id must be the source's own code, not a hash | dataset.idFrom: key |
mapping |
| Find a record later by a wellbore name or UWI | dataset.identity: [wellbore_name, wellbore_uwi]: the ledger indexes each |
mapping, the ledger |
| Know which rows will be refused before running | sqlflow values on the flow: the scope's rows (the first 10,000, or all with --max-rows 0) held to the template's rules |
values |
| See one record exactly as it would be sent | sqlflow preview, or the flow's Preview tab |
preview |
| Hold a record that breaks its schema instead of sending it | target.validation.mode: enforce |
preflight |
| Hold a record that names an id OSDU does not hold | target.verifyReferences: storage |
delivery flow, preflight |
| Problem | Shape | Page |
|---|---|---|
| A source delivers one kind | The single form: source.record and render.mapping |
delivery flow |
| A source delivers several kinds that refer to each other (wellbores, then their well logs) | One document with interfaces:, one per kind, each with its own record table, mapping and ledger identity; order from after: and the template's relationships |
interfaces, multi-kind source |
| A record refers to one that is not delivered yet | Nothing to add: it waits (status Waiting) and goes back to pending when the other lands |
interfaces, record lifecycle |
| Separate single-kind flows should become one source without delivering again | interfaces.<name>.ledger names the old flow whose ledger the interface keeps |
interfaces |
| A run should stop when failures pile up, not hold record after record | failWhen |
delivery flow |
The route is target.protocol (or, in a source with interfaces, follows from what each interface declares).
| What a record is | Route | Page |
|---|---|---|
| A record with no files (master data, most reference data) | storage: the storage service, batched upserts |
routes |
| A record with files it lists as datasets | file: each file uploaded to a signed location, registered as a dataset, then the record |
routes |
| A dataset record and its files through the Dataset service | dataset |
routes |
| Records the platform's ingestion workflow must load | manifest: one manifest per batch, the workflow run polled, the records read back |
routes |
| A record whose DDMS keeps bulk data (a well log and its curves) | ddms: the record through the DDMS serving its entity type, then its bulk data, in a session when large |
DDMS, bulk data guide |
| Files and DDMS bulk data on one record | fileAndDdms; with a manifest, manifestAndDdms |
routes |
| A named workflow does the loading | workflow, declared stage by stage |
routes |
| Rows of the Production DDMS core service, not OSDU records | dspdm |
DDMS shapes and services |
| Energistics objects into the Reservoir DDMS | etp |
DDMS shapes and services |
| Problem | Shape | Page |
|---|---|---|
One flow delivers to dev and to test |
partitions: [dev, test]; a run names one (--set partition=test, or the GUI's choice), and each partition keeps its own ledger and cache |
partitions |
| Every partition the estate registers, without listing them | No partitions and no data-partition-id header; the deployment names its partitions in the control plane's configuration (Osdu:Partitions), or a workstation registers them with sqlflow partition add |
partitions, partition |
| A flow that delivered to one partition starts naming partitions | partitions: [{ name: dev, keepLedger: true }, test]: dev keeps the old ledger, so nothing is sent again |
partitions |
| The legal tag or groups differ per partition | Leave them to the kind's defaults (${env:OSDU_LEGAL_TAG}, ${env:OSDU_ACL_OWNER}, ${env:OSDU_ACL_VIEWER}) and give each partition its values in the control plane's configuration (Osdu:Partitions:<partition>:Properties:<NAME>) |
partitions, config, environment variables |
| Values must come from one place, not every node's environment | sqlflow config set <NAME> --value ... [--partition ...]: the control plane supplies them to every delivery, retrieval, cache, assertion, dimension, inventory, removal and download run it queues |
config, control plane |
| Problem | Shape | Page |
|---|---|---|
| Only send what renders differently | The default: change.detect: renderedHash, change.onUnchanged: skip |
change detection |
| An older source version must never overwrite a newer one | source.lastModified names the row's business version |
delivery flow |
| A mapping edit should reach every delivered record | Nothing to add: the next run sees that its mapping, template or parameters changed, reads every row of its scope once, and sends what renders differently; replan forces such a read |
change detection |
| A record is held or failed; the cause is fixed | sqlflow records issues, then records release (or the Issues tab) |
records, operations runbook |
| A record must be sent again although nothing changed | A redelivery from the record's page or the API | removal and reversal, operations runbook |
| A bad run must be undone | sqlflow records reverse --run <id> (or --operation reverse): what it created is removed, what it updated gets its earlier version back |
removal and reversal |
| A delivery stopped halfway and left datasets behind | --operation undo; deliver and drain runs also end with the same sweep |
removal and reversal |
| The ledger and the ingestion tables disagree | --operation sync: records what the ledger lacks, flags rows that changed unseen, reports rows that are gone; sends nothing |
removal and reversal |
| Records the source deleted must leave OSDU, and the ingestion loads incrementally | A removal flow on its own schedule: keys reads the source's complete key set whole (its files or its table), and what the ledger holds that it lacks is taken out once, through the delivery flow's removal path |
removal flow, 0022 |
| The source keeps a list of the keys it deleted | The same removal flow with removed: (a table or files, each key with removedAt); a key whose row came back after that moment stays |
removal flow |
| What OSDU holds may have drifted from what was sent | --operation verify; verify.reconcile: true has the next deliver run send drifted or missing records again |
running an OSDU flow, delivery flow |
| OSDU already holds records at the ids a new deployment makes from their keys (its ledger is gone, or another instance delivers) | The first run holds them; an adoption asked of them all (the flow's Delivery tab, or sqlflow records adopt) has the next run take each over at the version OSDU holds |
record lifecycle, 0021 |
| A flow, or its file, is renamed | Nothing to add: the sync moves its ledger when only the name changed; a flow renamed and edited at once waits on the Orphaned ledgers page for a person to hand its ledger over | renaming a flow, 0028 |
| A run's legal tag is no longer valid | The deliver run checks it first and stops; fix the tag or the configuration | protocols |
| The target answers a status that retrying will not fix | reliability.skipStatusCodes: hold instead of retrying (400, 403, 404, 405, 409, 413, 415 and 422 already hold) |
delivery flow, record lifecycle |
| Problem | Shape | Page |
|---|---|---|
| Look at what OSDU holds for a type or one record, now | The explorer (GUI, any signed-in user), read live from search and storage through the partition's own connection, with no flow needed | explorer, 0020 |
| OSDU records must land in tables for reporting | A retrieval flow writes JSON Lines files; a SQLFlow file flow loads them, ordered after it by lineage | retrieval, retrieving records |
| Each record of a kind as a file of its own, named after its id, on a local disk or in Azure Storage | A download flow: the record's metadata as storage holds it, rewritten only when OSDU holds a newer version | download, 0038 |
| Prove what landed: counts, values, references, schema, legal tags, bulk data | An assertion flow, with a report per run (JUnit for CI) | assertion, data quality tests |
| Prove that everything the ledger says it delivered is in OSDU | An assertion with the delivered subject |
assertion |
| Find ids in OSDU no ledger accounts for, and remove them | An inventory flow; removal: in the document allows removing the findings an operator picks |
inventory, inventory removals, finding orphans |
| Find the records OSDU holds more than once, and remove the less complete copies | A duplicate check of an inventory, by a key of the properties the copies hold alike; the fullest copy of each group is kept and the others become a selection a removal takes | finding duplicate records, 0027 |
| Let people find records by the source's own values | Search terms: the source columns the mappings read, searched in the explorer | search terms |
| Let people pick from the values records hold | A dimension flow, its values and search filters | dimensions guide |
| Problem | Shape | Page |
|---|---|---|
| Run the chain on a schedule | Join the pre, ing and OSDU flows to one schedule; a fire runs them in lineage order, and the schedule's values supply a flow's parameters |
SQLFlow: schedule, delivery flow |
| Be told when a delivery fails | SQLFlow's notifications, for every OSDU kind; lifecycle: development while building |
notifications |
| Nodes inside a customer network | A worker with SQLFLOW_CATALOG_DB (a login on the osdu schema alone) and the flows' references in its own environment |
worker, deployment |
Ask an assistant about the estate, or a ! question about the data an approved AI context opens |
osdu-delivery-mcp: metadata, and a ! question's read-only query |
MCP server |
| See why a flow runs after another | sqlflow lineage <folder> --explain <flow>, or the lineage graph |
lineage |
| Schedules deliver records that refer to each other's | Nothing waits across schedules: the Order page shows which runs best before which; chain them with after: (one repository's) or move a clock |
the OSDU order, 0016 |
| Find a run's requests in the platform's logs | Nothing to add: every request of a run carries correlation-id: osdu-delivery-<operation>-<run id>, with the repository's code after osdu-delivery once an osdu-delivery.yaml at its root names one; telemetry.operation gives the flow's operation |
request correlation, repository document, 0037 |
| Find the run behind an id in the platform's logs | Nothing to add: paste the value or the id into the search or the Runs page, or ask delivery_run; any part of a split delivery leads to its whole execution |
request correlation |
| See what fills the database and what may be removed | SQLFlow's Maintenance page: every area of the osdu schema kept, safe to remove or optional; work in progress and staging clean themselves, and the optional areas (attempts, activity logs, assertion runs) are removed only once an admin sets a limit or cleans up |
what the osdu schema stores, 0029 |
| Trap | Page |
|---|---|
| OSDU documents refuse unknown keys (SQLFlow's ignore them): a typo fails validation, which is the point | OSDU documents |
sqlflow validate on a flow reads the mapping it pins and the dictionaries it names, but not the template or the cache; sqlflow check holds the mapping against the template and the partition's cache |
check |
A mapping's template.version is the template's hash, and another release of the same kind can give another one; left out, the nearest template folder holding the kind settles it, and a folder holding two versions of the kind refuses to guess |
templates |
The flow's source.record.key and the mapping's dataset.key must name the same columns in the same order |
delivery flow |
Changing dataset.system or dataset.key derives new delivery keys: with the default idFrom, every row becomes a new record with a new OSDU id, beside the ones already delivered. A change that gives an existing record another id is held instead of sent |
mapping |
| A mapping that reads the cache needs a cache version of the partition before the first run | preflight, partition cache |
| Lineage shows fewer cache reads than a mapping makes until a template folder on its path holds its template | lineage |
The ingestion database must allow snapshot isolation, or set source.incremental.isolation: readCommitted |
delivery flow |