Where Does Data Come From?
The first stage of the data engineering lifecycle, explained for people who build pipelines
A data engineer’s job is often described in one sentence: take data from source systems, do something useful with it, and serve it to the people and products that need it. Most of the attention goes to the middle of that sentence, the pipelines, tools and warehouses. But everything downstream depends on the first part. Before you can ingest data well, you need to understand where it lives, how it is created, and what quirks it carries.
This article walks through the main kinds of source systems you will meet as a data engineer and the ideas you need to work with them.
Why the source matters more than the tool
Moving data from system A to system B is getting easier every year. Managed connectors, cloud services and open-source tools now do much of the plumbing for you. What has not become easier is understanding system A itself. If you misunderstand how a source writes, updates or deletes its data, your pipeline can run perfectly and still produce wrong numbers.
The practical advice is simple. Read the documentation of every source you ingest from, and learn how it behaves. If the source is a relational database, learn how it writes data, how it commits transactions and how it handles queries. Those details decide which ingestion methods are safe and which will cause problems.
How data is created
At its most basic, data is a collection of facts with no organization or context. It is created in two broad ways.
Analog data is created in the physical world. A conversation, a handwritten note and a song played on a guitar are all analog data. Most of it is temporary. Once a conversation ends, its contents are usually lost unless someone records them.
Digital data is either converted from analog form or created natively by a digital system. When your phone turns your spoken words into a text message, analog data becomes digital. When you buy something online, the order, the card payment and the delivery details are born digital and saved straight into several databases.
As a data engineer you will mostly work with digital data, and it comes from everywhere: websites and mobile apps, card terminals, sensors on factory machines, stock exchanges and even telescopes.
Files: the format that never goes away
A file is simply a sequence of bytes stored on a disk. Applications write files all the time to store settings, events, logs, images and audio.
Files are also the most universal way of exchanging data between organizations. Data engineers would love to pull everything through clean programmatic interfaces, but much of the world still sends files. If you need data from a government agency, a supplier or a bank, there is a good chance it will arrive as an Excel or CSV file, sometimes as an email attachment.
The file formats you will see most often from source systems are Excel, CSV, plain text, JSON and XML. They vary in how much structure they have. Excel and CSV are usually structured, with rows and columns. JSON and XML are semistructured, with nested fields that may change from record to record. Plain text is unstructured. CSV can surprise you in all three ways, depending on how carefully it was produced. Later in the pipeline you will convert this data into analytics-friendly formats such as Parquet, ORC or Avro, but at the source you take what you are given.
APIs: simple in theory, messy in practice
An application programming interface (API) is a standard way for two systems to exchange data. A weather service, a payment provider or a CRM might each offer an API that returns data when you send a request.
In theory, APIs make ingestion easy. In practice, they often push complexity onto the data engineer. You have to deal with authentication, pagination through large result sets, rate limits that block you if you call too often, and changes to the response format. Even with managed services that automate API ingestion, data engineers still spend a lot of time building and maintaining custom API connections.
Application databases (OLTP systems)
An application database stores the current state of an application. A bank’s database that holds every customer’s account balance is a classic example. Each time a customer pays a bill or receives a transfer, the application updates the balance.
These databases are usually online transaction processing (OLTP) systems. An OLTP database reads and writes individual records very quickly and handles many users at the same time. A relational database can look up or update a single row in under a millisecond and handle thousands of reads and writes per second. Document databases can handle even higher write rates, although they may give up some consistency to do so. This makes OLTP databases a good backend for apps where thousands or millions of people act at the same moment.
People often call OLTP systems “transactional databases”. That name does not always mean the database supports full atomic transactions, which brings us to ACID.
ACID: the promises a database can make
ACID is a set of four guarantees that many databases offer.
Atomicity means that a group of changes either all succeed together or all fail together.
Consistency means that when you read a record, you get the most recently written version of it.
Isolation means that if two updates to the same record happen at the same time, the final result is the same as if they had run one after the other, in the order they were submitted.
Durability means that once data is committed, it will not be lost, even if the power fails.
These guarantees make an application developer’s life much easier, because the database always shows a consistent picture of the world. However, ACID is not required for every application. Some distributed databases relax these rules, for example by using eventual consistency, to gain speed and scale. In an eventually consistent database, a read straight after a write might return old data for a short time. As a data engineer, you need to know which consistency model each source uses. Otherwise you may ingest stale or half-finished data without realising it.
Atomic transactions: all or nothing
An atomic transaction is a set of changes that are committed as a single unit. The standard example is a bank transfer. Imagine moving 100 dollars from Account A to Account B. The database checks that Account A has enough money, subtracts 100 from A and adds 100 to B. Either both updates happen or neither does. If the system crashed after taking money from A but before adding it to B, the money would simply disappear. Atomic transactions prevent exactly that.
Why you shouldn’t run analytics on the OLTP database
Small companies often run their reports directly on the application database. This works for a while, but it does not scale. Analytical queries scan large amounts of data, and OLTP databases are not built for that. As data grows, these queries become slow, and they start competing for resources with the customer transactions that keep the business running. A heavy report at month-end can slow down the app for every user.
This is why data engineers need to understand how OLTP systems work inside. The goal is to connect them to analytics systems without hurting the performance of the production application. Things get more complicated with data applications, a term for products that mix transactional and analytical workloads, such as a SaaS tool that lets users update records and see live dashboards in the same screen.
OLAP systems: built for big questions
An online analytical processing (OLAP) system is the opposite of an OLTP system. It is designed to run large analytical queries efficiently and is poor at looking up individual records. Modern columnar databases, such as cloud data warehouses, are optimised to scan large blocks of data, often 100 MB or more per query. They often skip traditional indexes to scale better. If you tried to use one to look up thousands of single records every second, it would struggle badly unless a caching layer sat in front of it.
Get Jacobmoracha’s stories in your inbox
Join Medium for free to get updates from this writer.
The word “online” means the system is always listening for queries, which makes it suitable for interactive analysis.
You might wonder why OLAP systems belong in a discussion about sources. The reason is that data engineers often read from them too. A data warehouse might supply the training data for a machine learning model. It might also feed a reverse ETL workflow, where results calculated in the analytics system, such as a customer’s predicted lifetime value, are sent back into a CRM, a SaaS tool or the original application.
Change data capture: recording every change
Change data capture (CDC) is a way of extracting every change that happens in a database, whether it is an insert, an update or a delete, as a separate event. It is commonly used for two things: copying a database to another system in near real time, and creating a stream of events for other systems to process.
How CDC works depends on the database. Relational databases usually keep a log of changes on the database server, and CDC tools read that log to build a stream of events. Many cloud NoSQL databases can send their changes directly to a stream or a storage location. We will come back to the log in a moment, because it is the key to understanding CDC.
Logs: a record of what happened
A log captures information about events in a system. A web server logs every request it receives. Your laptop’s operating system logs events when it starts up and when an application crashes. Logs come from operating systems, applications, servers, containers, networks and IoT devices, and they are a valuable source for analysis, machine learning and automation.
Every useful log entry answers three questions. Who caused the event? That could be a person, a system or a service account, identified by something like a user ID or a browser’s user agent. What happened? That is the event itself and any related details. When did it happen? That is the timestamp.
Logs are stored in three main ways. Binary logs use a compact custom format that saves space and is fast to read and write; database logs are the standard example. Semistructured logs are written as text in a format like JSON. They are portable and machine-readable, but larger than binary logs, and turning them into useful data still often needs custom code. Plain-text logs simply store whatever the software printed to its console. There is no standard format, so extracting value from them can be difficult, although data scientists and machine learning engineers can still find useful signals in them.
Logs also differ in resolution, which is how much detail they capture. A database log records enough detail to rebuild the database exactly as it was at any point in time. A big data system, on the other hand, may only record that a certain type of commit happened, because logging every change would be impractical. Separately, the log level controls which events are recorded at all. Software can usually be set to log every event, or only errors.
Finally, logs can be batch or real time. Batch logs are written continuously to a file and collected later. For real-time uses, individual log entries can be sent to a messaging system such as Kafka or Pulsar as they happen.
Database logs: the secret behind recovery and CDC
Database logs deserve special attention. Most databases use a write-ahead log, a binary file in the database’s own format. When the database receives a request to insert or update data, it first writes the operation to this log, and only then confirms the request. This order gives an important guarantee: if the server crashes, it can restart, read the log and finish any work that was not completed.
For data engineers, this log is extremely useful. Because it records every change in order, a CDC tool can read it and turn each change into an event, without running heavy queries against the database tables. This is how tools like Debezium or AWS Database Migration Service capture changes from databases such as PostgreSQL and MySQL.
CRUD: the everyday pattern
CRUD stands for create, read, update and delete. These are the four basic operations of any persistent storage, and CRUD is the most common way applications store their state. Data must be created before it can be used. Once created, it can be read and updated, and eventually it may be deleted. You will see CRUD everywhere, especially in web applications, where REST APIs map these operations to HTTP requests.
There are two main ways to extract data from a CRUD database. The first is a snapshot: you copy the current state of the tables at a point in time. This is simple, but every change between two snapshots is lost. If a record changed five times during the day, a nightly snapshot only shows the last version. The second is CDC, which captures every operation and gives you a complete history, often in near real time.
Insert-only: keeping history in the table
The insert-only pattern takes a different approach. Instead of updating a record when something changes, the application inserts a new record with a timestamp. Imagine a table of customer addresses. Under CRUD, when a customer moves house you overwrite their old address. Under insert-only, you add a new row with the same customer ID and a new timestamp. To find the current address, you look for the most recent row for that customer.
In effect, the table becomes its own change log. This is useful when the application needs history, such as a bank that must show a customer’s past addresses. There is also an analytics version of this idea: the application keeps using normal CRUD tables, but the data pipeline inserts a new row into the analytics table every time a source record changes.
Insert-only has two drawbacks. First, tables can grow very large if data changes often, so teams sometimes delete old versions after a set date or keep only a maximum number of versions per record. Second, finding the current value is more expensive, because the database has to find the latest timestamp for each ID. If a single ID has thousands of versions, that lookup becomes slow.
Messages and streams: similar, but not the same
The terms message queue and streaming platform are often used as if they mean the same thing, but there is an important difference.
A message is a piece of data sent from one system to another. For example, one microservice might send a message to another, or a server might send a message to a serverless function. Messages usually travel through a message queue, and once a message has been delivered and handled, it is removed from the queue. Picture a smart thermostat. A sensor sends a message with the current temperature. A service reads it, decides whether the heating should turn on, and sends a message to the heating controller. Once the action is done, the messages are gone.
A stream, by contrast, is an append-only log of events, kept in order by timestamp or ID. In distributed systems events do not always arrive in exactly the right order, but the stream still keeps them as a sequence. Because records are kept for a long time, often weeks or months, you can do things with a stream that you cannot do with a queue. You can calculate aggregates across many events, and you can rewind to an earlier point and replay what happened.
Use a stream when you care about what happened across many events, not just the latest one. In practice the two often work together: streaming platforms are frequently used to pass messages, and messages are often collected into streams so they can be analyzed later. The temperature readings that switched the heating on and off can later be studied to understand temperature trends.
The four kinds of time
Time matters in all data ingestion, but it becomes especially important with streaming, where data is expected to arrive and be used continuously. There are four kinds of time to keep track of.
Event time is when the event actually happened in the source system. Ingestion time is when the event arrives somewhere: a message queue, a cache, object storage or a database. After ingestion, the data might be processed immediately, after a few minutes or hours, or not at all.
Process time is when the data is actually transformed.
Processing time is how long that transformation took, measured in seconds, minutes or hours.
The practical lesson is to record a timestamp at every stage, and to do it automatically. If you know when each event was created, ingested and processed, and how long processing took, you can track data as it moves through your pipelines and spot exactly where delays happen. Event time also decides which day or hour an event belongs to.
Note
Source systems are where every data pipeline begins. Some send files, some expose APIs, and some are busy application databases that must be handled with care. Some record their changes in logs that you can turn into streams, while others keep history inside the tables themselves. Some pass short-lived messages, and others keep long streams of events.
Before building any pipeline, it pays to ask a few questions about the source. How does it create data? Does it overwrite records or keep history? Can you read its change log, or only take snapshots? What consistency does it promise? Which timestamp tells you when each event really happened?
