Write to an application's API

Status

Variants

Create records in an application

Send the rows to an application's API as new records, tickets, events or list entries.

Publish a dataset to an analytics service

Publish or refresh the whole result as a dataset or data source on a hosted analytics or reporting service.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Background

When to use it

In Bruin, writes to an application's API are not transactional: a run that fails partway may already have written some records, and Bruin cannot roll them back.^[1] Plan every write to an application for a run that stops halfway, and know before the first run how you would find the records it has already written. (Meridian's view) In Bruin, the target API decides which write strategies work and which fields a record can be matched on.^[1] Bruin's append strategy always creates a new record, so re-running it duplicates rows unless each run is scoped to new rows.^[1] When rows are meant to be created once, give each run only the rows the application has not yet seen. (Meridian's view)

In the Hypertext Transfer Protocol (HTTP), status code 429 Too Many Requests means the user has sent too many requests in a given amount of time.^[3] An HTTP 429 response may include a Retry-After header saying how long to wait before making a new request.^[3] Bruin sends rows in bulk through the API's batch endpoints, within the API's rate limits.^[1] In Bruin, the default reject mode, fail, sends every valid row and then fails the run, listing the rows the API rejected.^[1] In Bruin, systemic failures such as failed authentication or a malformed request always abort the run, whatever the reject mode.^[1] Read the rejected rows after every run, and decide for each one whether the row or the target needs fixing. (Meridian's view)

EasyMorph's Export to Power BI action appends rows to a push dataset in the Power BI service, and the limitations of the service's API apply.^[4] When Tableau Prep Builder publishes to a data source it published before, it refreshes the data source and keeps its data modelling, such as calculated fields.^[5] If Tableau Prep Builder cannot refresh a published data source, it replaces the data source, data modelling included.^[5] Before publishing a result to an analytics service, find out whether a run adds rows, refreshes the dataset in place or replaces it. (Meridian's view)

The method

Create, read, update and delete (CRUD) are the four basic operations of persistent storage.^[6] The term CRUD was likely first popularised in 1983 by James Martin in his book Managing the data-base environment.^[6] In databases, each CRUD letter can be mapped to a standard Structured Query Language (SQL) statement: INSERT, SELECT, UPDATE and DELETE.^[6] In APIs built on representational state transfer (REST), each CRUD letter may be mapped to an HTTP method: POST to create, GET to read, PUT to replace or PATCH to modify, and DELETE to delete.^[6] In HTTP, POST is not limited to creating resources and may also be used for process-oriented operations that go beyond CRUD.^[6]

HTTP calls a request method idempotent when several identical requests with it have the same intended effect on the server as a single such request.^[7] Of the request methods HTTP defines, PUT and DELETE are among those that are idempotent.^[7] An HTTP client can retry an idempotent request after a lost connection, knowing the repeat has the same intended effect even if the original request succeeded.^[7] An HTTP client should not automatically retry a request with a non-idempotent method unless it knows the request is in fact idempotent or that the original request was never applied.^[7] Guard the writes that create records most carefully, and prefer a write that updates a record matched on a unique key wherever the application offers one. (Meridian's view)

With the @dlt.destination decorator, any function that takes two arguments becomes a dlt destination.^[2] The batch_size argument of dlt's destination decorator sets how many items are batched together and sent in each call of the function.^[2] In dlt, a destination function that raises an exception is retried 5 times before dlt gives up.^[2] When a failed dlt run is repeated, the destination restarts from the whole batch that failed, so part of a batch committed before the failure can be duplicated.^[2] For systems that are not transactional and do not tolerate duplicated data, a dlt destination can use a batch of size 1.^[2] dlt's max_parallel_load_jobs argument sets how many load jobs run in parallel, for example for a destination that allows only five connections at a time.^[2]

Bruin names five write strategies: merge (update the matching record or create it), update, append, delete and replace.^[1] In Bruin, merge, update and delete need two things to find an existing record: a field on the destination side to match on, and a source column holding the value.^[1] In Bruin, a source row usually affects a single remote record, but matching on a non-unique field can update or delete every record that matches.^[1] Match on a field the application keeps unique, and try the match on a handful of rows before a full run. (Meridian's view)

Compared with the alternatives

In Bruin, a warehouse destination stages data, runs SQL and swaps tables, while a destination that writes to an application's API has no staging table, no SQL and no atomic swap.^[1]

Sources

1. [1]Reverse ETL | Bruin CLIdocumentation
2. [2]Reverse ETL | dlt Docsdocumentation
3. [3]Additional HTTP Status Codesstandard
4. [4]EXPORT TO POWER BI [EasyMorph Help]documentation
5. [5]Save and Share Tableau Prep Work - Tableaudocumentation
6. [6]Create, read, update and delete - Wikipediasecondary source
7. [7]RFC 9110: HTTP Semanticsstandard