Data engineering in the AI era

The checkout writes the order to the shop's own PostgreSQL database with the status pending. When the card payment clears, 2 minutes later, the app updates the same row to paid. The row is shaped for the app: the amount is in pence, the time is written without a time zone, and the city is exactly what the customer typed. Every pipelineData PipelineA set of steps that moves data from where it is made to where it is used, cleaning and reshaping it on the way, run on a schedule or as data arrives. starts from data like this, made for some other job.

Copying the whole table every hour would be slow and would load the shop's database. Instead, change data captureChange Data CaptureCopying only the rows that were added, changed or deleted in a source database, read from its change log, instead of copying the whole table again. reads the log PostgreSQL already keeps of every change, through logical replication, and passes on each change once, in order: first the insert, then the update. Lakeflow Connect has a managed PostgreSQL connector for this, in Public Preview, which needs PostgreSQL 13 or later.

The connector writes the order to a bronze table in the lakehouseLakehouseOne store for all of an organisation's data: cheap files in cloud storage, as in a data lake, with the tables, transactions and fast queries of a data warehouse on top., with the values untouched. With history tracking on, which Databricks calls SCD type 2, the pending version is kept and closed, and the paid version is marked as current. The table is stored in Delta Lake, an open table formatOpen Table FormatA published way of storing a table as plain data files plus a log of changes, so many engines can read and safely write it. Delta Lake and Apache Iceberg are the best known., so every write is a new version of the table that can still be read. Bronze, silver and gold are the layers of the medallion architectureMedallion ArchitectureOrganising a lakehouse in 3 layers of rising quality: bronze for raw data, silver for cleaned and joined data, and gold for tables shaped for reports and AI., and bronze is the raw one.

A pipeline builds the silver table from the current rows of bronze. Pence become pounds with the currency in its own column, the local time becomes UTC, and the city is trimmed and capitalised. The email is left out, because no report needs it. Each rule written as an expectation is checked on every row, and a row that breaks one is dropped. Cleaning after loading, inside the lakehouse, is what ELTELTLoading raw data into the warehouse or lakehouse first and transforming it there, the reverse of the older ETL order, which transformed data before loading it. means. A schedule runs it every few minutes, after the bronze load, which is orchestrationOrchestrationRunning the steps of data pipelines in the right order and at the right time, retrying failures and alerting someone when a step breaks..

Expectations check rows that arrive. They cannot notice rows that never arrive. So the whole table is watched too: data quality monitoring learns when the table is usually updated and how many rows a day usually brings, and flags a table that is late or short. Rules agreed with the team that owns the source are a data contractData ContractAn agreement between the team that produces data and the teams that use it, written down and checked, that fixes its columns, meaning and freshness., and a renamed column that breaks them is schema driftSchema DriftWhen the shape of incoming data changes without warning, such as a renamed or new column, and breaks or silently corrupts the pipelines that read it.. Unity Catalog records, with no extra work, which tables and columns each one was built from. That record is lineageData LineageThe record of where a piece of data came from and everything it passed through, from source tables to the dashboards, models and AI answers built on it., and all of this is data qualityData QualityHow far data can be trusted: complete, correct, on time and in the expected shape. Pipelines check it with rules and stop or flag rows that fail..

The order is now 1 of 312 paid orders that make up Leeds's revenue for 14 October. What "revenue" means is written once, as a metric view in Unity Catalog: the sum of paid amounts, which can be split by city and day. A dashboard, a spreadsheet and an AI assistant all ask the metric view, so they all get the same figure. A shared definition like this is a semantic layerSemantic LayerOne shared definition of business terms and metrics, such as revenue or active customer, that reports, dashboards and AI assistants all read instead of writing their own.. Synonyms tell an assistant which words people use for it.

"What did Leeds take yesterday?" A Genie Agent, which Databricks used to call a Genie Space, turns the question into SQL, a pattern called text-to-SQLText-to-SQLA model turning a question in plain words into a database query, so people can ask the data directly. It is only as right as its knowledge of the tables and their meaning., and answers with the query and its result. The word "take" matches the synonym "takings", so it asks the agreed revenue rather than guessing which column holds money. Its queries can only read, never change data. Because lineage was recorded, the figure can be traced back through every table to order 88231.

Not all data is rows. The returns policy is a 6-page PDF with a table of exceptions, and the team has just changed it: customers now have 30 days to return an item, not 14. Nothing about the change reaches the shop's assistant unless a pipeline carries it there.

The file is saved to a Unity Catalog volume. Auto Loader notices the new file and processes it once, with no list of which files are new. A PDF is not text yet, so a parser reads it: on Databricks the ai_parse_document function returns the text, the tables and the layout, so a figure in the table of exceptions stays next to its heading. This is the same first step as for the order, a pipelineData PipelineA set of steps that moves data from where it is made to where it is used, cleaning and reshaping it on the way, run on a schedule or as data arrives. bringing data in.

A whole policy is too long to send with every question, and a search that returns the whole file has not found much. So the text is cut into passagesChunkingCutting documents into smaller passages before they are embedded for retrieval, so a search returns the part that answers a question rather than a whole file. of a few hundred tokens, each tagged with the document, version and heading it came from. Passage 6 is the one about returns. Databricks has a function for this, ai_prep_search, still in Beta; many teams write their own.

An embedding model turns each passage into an embeddingEmbeddingA list of numbers that stands for the meaning of a piece of text, an image or other data. Things with similar meaning get similar numbers, so a computer can find them., a long list of numbers where passages about the same thing land close together. OpenAI's text-embedding-3-small, for example, gives 1,536 numbers per passage. Databricks can compute the embeddings with a model you choose, or the table can hold ones you made yourself. Keeping them in step with the text is an embedding pipelineEmbedding PipelineThe pipeline that turns source documents into searchable vectors: it reads new and changed content, chunks it, embeds each chunk and keeps the vector index in step with the source..

The passages go into a vector indexVector DatabaseA database built to store embeddings and quickly find the ones closest to a query, which is how AI apps search by meaning. that finds the closest in meaning to a question. A Delta Sync index keeps itself in step with its source table, reading only what changed, the same idea as change data capture for the order. When version 3 replaced version 2, the old passages were deleted from the table, and the sync deleted them from the index too. Hybrid search adds keyword matching, which helps when the exact words matter, such as "30 days".

"Can I return shoes I bought 3 weeks ago?" The question is embedded the same way, the index returns passage 6, and the model answers from it and cites it. This is RAGRAGA way to answer from your own documents. A search finds the passages that match the question, and they are added to the prompt before the model writes.. Had the pipeline not run, the index would have returned the old passage, and the assistant would have said no, politely and confidently. The model was the same either way. Only the data changed.