What Data Engineering Is

Knowing SQL and Python lets you express a transformation. Designing a dependable data system also requires knowing which records that transformation should see, what they mean, when its result becomes visible, and who is responsible when a source or definition changes. Those are the questions this article connects.

From application records to reusable data

An order database supports checkout: create an order, update its status, and retrieve it quickly by key. Finance needs a daily account of payments and refunds. An analyst needs trends across products and countries. A recommendation service needs customer and item histories. All may use records from the same business, but their required grain, history, timing, and access rules differ. A source optimized for one purpose rarely supplies every other purpose directly.

Data engineering designs, builds, and operates the systems that make those records usable. A data pipeline is a sequence of steps that collects and prepares data. A data platform provides shared storage, processing, access, and operating tools for multiple pipelines and their users. Part of the work is moving records; another part is establishing what they mean and detecting when the result no longer meets its intended use.

A data product is the maintained dataset or interface a consumer relies on, together with its meaning and operating commitments. These three terms describe different concerns: the pipeline produces or updates a result, the platform supplies shared capabilities, and the product defines what a consumer can depend on. One platform can host many pipelines, and several pipelines can contribute to one product.

A Tuesday morning

Consider a fictional shop. The counts below illustrate three separate failures; they are not measurements from a company.

The head of finance opens the revenue dashboard. It says 41,200 orders yesterday. The source order report says 42,610. After confirming that both reports should count the same statuses and business day, she traces the difference. She asks which number is right. That question, why do two systems disagree, and which one do we believe?, is a recurring question in data engineering, and answering it means walking the path the number traveled:

1. The shop’s database recorded 42,610 orders. Fine so far.
2. A nightly job converted the local-day boundary to UTC using a fixed offset. A daylight-saving change made that offset wrong for part of the interval, excluding 430 orders. 42,180 remain. Local time itself is not the error; the incorrect boundary conversion is.
3. A transformation joined orders to customers to add the country. An inner join keeps only orders with a matching customer. It dropped 970 orders whose customer records had been deleted, about 2.3% of the 42,180 remaining orders. 41,210 remain.
4. The dashboard’s “orders” tile counts only orders with status paid, as it always has, but a new captured status appeared in March and nobody told the dashboard. 41,200.

Every step reported success, but the final count is 1,410 below the source count: 1,410 / 42,610 is about 3.3%. The problem is not a SQL syntax error. The systems disagree about the time boundary, which missing relationships are acceptable, and which statuses belong in the metric. A successful process tells us that its instructions ran; it does not establish that those instructions preserved the intended business meaning.

Where data comes from

A source system is a system from which a particular data flow obtains records. It is often an application that creates records as a side effect of doing its real job, such as the shop’s order database, but the term is relative to the flow: a warehouse can be the source for an export even though it did not create the original transactions. Sources differ in shape, in how they can be read, and in how they break.

In the table, CDC means change data capture: reading inserts, updates, and deletes rather than only the latest rows. An API is an interface a program calls to request data; SaaS means hosted software. CSV stores tabular text, JSON stores structured values, and SFTP transfers files over an encrypted connection. A schema describes fields and their types. Backpressure means controlling incoming work when processing cannot keep up. The categories overlap: an application event can arrive in a stream or in a file.

Source	Example	Shape	How you get it	What goes wrong
Operational database	the shop’s PostgreSQL, an ERP’s Oracle	tables; current state, sometimes history	query it, or read its change log (CDC)	overwritten values or deleted rows may be absent from a later snapshot
Application events	“user viewed product”, “checkout started”	one record per thing that happened	emitted by the app to a collector or a stream	schema changes without warning; duplicates; late arrival; wrong client clocks
Logs	web server, API gateway, error logs	text lines, semi-structured	an agent tails files and ships them	format drift; volume spikes; lost lines when buffers fill
Files from partners	a vendor’s daily CSV on SFTP, a bank statement	whatever they decided	fetch on a schedule	late, missing, truncated, renamed columns, encoding
SaaS APIs	payment processor, CRM, ad platforms	JSON over HTTP	page through the API	rate limits; restated history; each vendor’s own definitions
Streams	IoT sensors, clickstream, a Kafka topic from another team	unbounded sequence of events	subscribe	ordering, replay, backpressure

A source is often owned by a product team or vendor with its own requirements. Agree on how changes will be communicated rather than assuming the interface will stay fixed. Also ask whether the source keeps current state, such as a customer’s present address, or events and history, such as dated address changes. An operational database can store either or both. If updates overwrite the only copy, later queries cannot recover the old values; history must have been retained in the source, a change log, a backup, or another permitted store.

The ecosystem includes people and different kinds of evidence

A data ecosystem includes the source applications, infrastructure, definitions, and people that generate, prepare, interpret, and use data. A retailer might combine order tables with customer reviews; an agricultural service might combine sensor readings with weather records. Each combination needs compatible time and identity rules, permitted use, and a clear decision it supports. A pipeline is one path through this wider ecosystem.

Structured data follows an explicit organization such as rows and typed columns. Semi-structured data, such as a JSON event, carries named fields while allowing variation. Unstructured content, such as a photograph or free-text review, lacks the task-specific tabular organization the consumer needs; it is not literally without structure. Data used for AI training can belong to any of these categories. Additional extraction or labeling may be needed before combining it with order records.

Why the source database is not always the analytical interface

Operational processing often favors short transactions and selective reads or writes. Analytical processing frequently scans many records, combines sources, and preserves history. Running both on the same system can be appropriate at modest load. Separating them becomes useful when analytical work competes with production traffic, consumers need different retention or access rules, or several applications must agree on a shared representation.

Separation introduces a new problem: the analytical copy can lag, omit changes, or interpret them differently. A replica may reduce query load but does not by itself provide a business model, retained historical meaning, or a consistent snapshot across independent sources. A new warehouse earns its place only if its benefits justify the additional synchronization and operational responsibilities.

Scale is therefore one design dimension alongside meaning, latency, availability, security, and cost. Distributed processing adds coordination, network transfer, and partial failures. A single database or local analytical engine can be the appropriate starting point when its capacity and operating model satisfy the requirements. The relevant question is which constraint requires a different architecture.

The lifecycle

The following lifecycle describes the responsibilities between a source and a decision. Systems can combine stages or revisit them: validation is useful both before and after transformation, and some applications read a source directly.

generate → collect → store raw → validate → transform → serve → consume → retire
   │          │          │           │           │          │        │         │
 source    ingestion   landing    quality     modeling   warehouse  BI/ML   retention
 systems              zone       checks      (SQL/Spark)  / mart   / apps   & deletion

• Collect (ingestion). Get the data out of the source and into your platform, without losing any and without harming the source. Batch (nightly copy) or streaming (continuous).
• Store raw. Retain an unmodified copy when permitted, for a defined purpose and retention period. It gives you a stable input for investigating or rebuilding a transformation. Re-extracting from the source is another option only if the required history is still available.
• Validate. Check that what arrived is what was expected, row counts, required fields, plausible values, before anything builds on it.
• Transform (modeling). Clean, deduplicate, join, aggregate, and reshape into tables that answer business questions. This is where agreed meanings become executable rules: what counts as an order, when a customer is “active”, and which currency revenue uses. Resolve ambiguous definitions with their accountable owners rather than letting a query silently decide them.
• Serve. Put the result somewhere fast enough and safe enough for the consumers: a warehouse for analysts, an API for an application, a feature table for a model.
• Consume. Dashboards, reports, models, and increasingly, data sent back into operational systems or used to answer questions in natural language.
• Retire. Apply the agreed retention and deletion policy, including its obligations and exceptions. Track derived copies as well as the original data.

Every stage can lose rows, duplicate rows, or change meaning. The Tuesday-morning story had one failure in collect (the timezone), one in transform (the join), and one in consume (the dashboard’s stale filter). A platform is “good” to the extent that each stage makes its failures visible to the next.

A pipeline manages state as well as records

A pipeline needs to remember what it has already covered. A file collector might retain a manifest of accepted files; a change-data-capture process might retain a source-log position; a transformation might record the input snapshot and code version used for an output. This progress state is part of the system. Restoring output without compatible progress can duplicate work, while restoring progress without output can skip work that still needs to be rebuilt.

Consider a load that commits destination rows and then loses its network connection before recording success. Retrying is reasonable, but an ordinary append can apply the same delivery twice. Idempotency means repeating the same logical operation preserves its intended effect. It requires an identity for that operation and a destination rule that recognizes repetition. A stable key helps only when it represents the correct entity or delivery and the write behavior enforces the intended rule.

Progress and publication also need a relationship. Advancing progress before durable publication risks skipping data after a crash. Publishing first can leave work to repeat after a crash. A transaction can couple these actions when they share a transactional boundary; otherwise the design needs a supported commit protocol, safe retries, or reconciliation of uncertain outcomes. A scheduler retry setting alone cannot supply that guarantee.

Publication is the point at which consumers may use a candidate result. Writing files into a directory and allowing readers to list them during the write can expose a partial result. Depending on the destination, a committed table snapshot, a transaction, or a version pointer can identify a complete publication. Such a mechanism establishes a visibility boundary; it does not prove the published values are correct.

Keep business time, arrival time, and knowledge separate

An order may occur at 23:58, reach the platform at 00:07, and be corrected two days later. Event time describes the business occurrence; arrival time describes observation by the platform; processing time describes when computation handles it. The reporting date and late-correction policy determine which result should change. Treating arrival date as business date silently changes the question.

A dashboard refreshed a minute ago can still contain data whose source coverage ended yesterday. Freshness must say which clock and which population it measures. The most recent event timestamp alone cannot establish completeness: one new event can arrive while an older partition is missing. A source boundary, expected-delivery record, or another completeness signal is needed to support the stated coverage.

Historical questions also differ. “What is the corrected total for Monday?” and “What total did we report on Tuesday?” may require different data. Reproducing the second can require retained inputs, transformation and reference-data versions, or the historical published result itself. Keeping raw files helps only within the permitted retention window and only when the other dependencies needed for the question are available.

What a data product is

For this article, a data product is a maintained dataset or interface intended for specific users and a defined use. A table name alone does not tell a consumer what its rows mean, when it updates, or whom to contact. The table below turns those questions into commitments.

Promise	Concretely	Without it
Defined	every column documented; the grain, meaning what one row represents, stated (“one row per day per currency”)	two analysts compute “orders” differently and both are “right”
Owned	a named team answers questions and fixes it when it breaks	users have no clear contact for questions or failures
Timely	a freshness commitment: “complete by 06:00 UTC”	the 09:00 meeting uses Tuesday’s data on Thursday
Complete and correct	tested against the source; reconciles with finance	3.3% missing and nobody knows
Stable	columns do not vanish or change meaning without notice and a migration path	every dashboard breaks on the same morning
Reproducible	can be rebuilt within the retention window using retained inputs, versioned code, and recorded dependencies and settings	“the number changed and we cannot say why”
Governed	who may see it is enforced; personal data is classified; retention is set	a regulator asks a question you cannot answer

For example, a daily paid-order table might include paid and captured orders, group them by UTC date and currency, exclude refunds, and be ready by 06:00 UTC. This is a specified operational measure, not automatically recognized revenue. Finance must own any accounting interpretation. A comparison with the source under matching rules supports one claim of consistency; it cannot establish that every real-world sale was recorded correctly or that the measure suits every business question.

The problems the field actually solves

Strip away the tools and seven problems recur often enough to serve as a working frame for this article. Many tools can be read as answers to one or more of them, but the list organizes the explanation here; it is not a complete classification of the field or of every technology.

• Volume. The required data or processing exceeds the current capacity or deadline. Reduce unnecessary scans, use suitable formats, and assess additional capacity or distributed processing against the workload.
• Reliability. It needs predictable runs, automatic recovery for expected failures, and escalation when recovery needs a person. Answer: task scheduling and coordination, retries, and idempotent design: repeating the same logical operation preserves its intended effects within the stated destination boundary.
• Correctness. Duplicates, dropped rows, wrong time zones, definitions that drift. Answer: modeling discipline, tests and assertions.
• Latency. How fresh does it need to be: a day, an hour, a second? Shorter deadlines can require different ingestion, state management, and recovery designs; there is no fixed complexity multiplier. Answer: choose deliberately, stream only where it pays.
• Change. Sources change shape; requirements change; the team changes. Answer: raw zones, contracts, version control, documentation.
• Security and privacy. Combining data from several sources can expose sensitive relationships, so access must match purpose. Answer: access control, classification, encryption, deletion.
• Cost. Charges depend on the service: stored data, scanned data, compute time, and transfer can all contribute. Answer: file sizes, partitioning, right-sizing, and looking at the bill.

These problems also arise with a single 50 GB database. More machines may address a capacity limit, but they do not resolve inconsistent definitions or unannounced source changes. Identify the actual failure before choosing a tool.

Batch and streaming, briefly

Batch processing handles a bounded collection, such as an exported file or one day’s orders. Stream processing handles a continuing flow; it may process individual events or small groups and does not guarantee a particular delay. Hourly reporting may need only a scheduled batch. An alert that must react within seconds needs a path designed for that deadline. Both approaches must handle duplicates and late data; streaming also makes continuously maintained state and recovery important.

Choose the least complex approach that meets the agreed freshness and recovery requirements. Ask what action the user will take when new data arrives, and how much delay that action can tolerate.

Who does what

Job titles vary. The roles below describe responsibilities that need an owner. A feature is an input to a model; point-in-time-correct training data uses only information available at the prediction time. Orchestration means scheduling tasks and coordinating their dependencies.

Role	Owns	Typical day
Data engineer	ingestion, storage, transformation infrastructure, reliability	a source changed shape; a job is slow; a new feed needs to land
Analytics engineer	the modeled layer: warehouse tables, metric definitions, tests, docs	maintaining a named active-customer definition and its scope; reviewing SQL
Platform engineer	the runtime: clusters, orchestration, deployment, access, cost	upgrading the orchestrator; making a new team self-sufficient
Analyst	questions, dashboards, interpretation	“why did conversion drop in Japan?”
Data scientist / ML engineer	models; the features they need; serving predictions	needs a clean, point-in-time-correct training table, which is a data engineering problem
DBA / source-system engineer	the operational databases and apps that produce the data	asks you to stop running that query at 09:00
Data product owner / steward	definitions, priorities, access decisions for a domain	agrees with finance how refunds affect each metric

Analysts and data scientists can both formulate questions, build models, and interpret results; their emphasis and responsibilities vary by team. One person may cover several roles, or responsibilities may be split across teams. Agree on who owns source changes, metric definitions, pipeline failures, and consumer support. Two technically correct pipelines can still disagree if their teams use different definitions of the same metric.

The modern platform at a glance

Here is one possible arrangement. The product names are examples, not a required shopping list. A data lake keeps files for different uses; a warehouse provides managed analytical tables and queries; a lakehouse adds table-management capabilities over lake storage. The roles can overlap. Parquet is a file format; Iceberg manages table metadata and snapshots across files. They sit at different layers and can be used together.

SOURCES            INGESTION                STORAGE & PROCESSING                    SERVING
─────────          ─────────────────        ────────────────────────────────        ──────────────────
databases    ──►   batch / CDC        ──►   ┌─ data lake ────────────────────┐      warehouse / lakehouse
                   [Airbyte, Fivetran,      │ object storage (S3/GCS/ADLS)   │ ──►  [Snowflake, BigQuery,
events       ──►    Debezium]               │ files: Parquet; tables: Iceberg│       Databricks, Redshift]
                                            │                                │              │
logs         ──►   streaming         ──►    │ processing: SQL, Spark, Flink  │      ┌───────┼────────┐
                   [Kafka, Kinesis,         │ transformation: dbt            │      BI      apps     ML / AI
files, APIs  ──►    Pub/Sub, Fluent Bit]    └────────────────────────────────┘   [Tableau, [reverse  [features,
                                                                                  Power BI]  ETL]     RAG]

CROSS-CUTTING:  orchestration [Airflow, Dagster] · quality [Great Expectations, dbt tests] · catalog & lineage
                [DataHub, OpenMetadata, OpenLineage] · security & privacy · infrastructure as code [Terraform] · cost

In the serving layer, BI means reports and dashboards; reverse ETL sends prepared data back into business applications; RAG retrieves documents to help a language model answer a question. A catalog helps users find datasets, while lineage records where data came from and how it was transformed. The diagram illustrates three design choices:

• Storage and compute are separate. In this design, files can remain in object storage while compatible engines process them. Compatibility, permissions, and total operating cost still need checking; not every platform uses this arrangement.
• Transformation is code. Shared definitions can live in tested, version-controlled SQL or a governed semantic model, rather than being reimplemented independently in every dashboard.
• The cross-cutting row is where trust comes from. Orchestration, quality, lineage, and access are not features added at the end; they are the difference between a pile of tables and a platform.

Read a product by its responsibilities and boundaries

Product categories help locate responsibilities, but their boundaries overlap. A warehouse can support ingestion, transformation, governance, and machine-learning workloads. A lakehouse platform can provide managed SQL as well as general processing. Compare the actual data path, metadata authority, compute allocation, and identity checks rather than assuming a category determines every capability.

For example, Snowflake describes database storage, compute, and cloud services as architectural layers. Databricks distinguishes control and compute planes; on AWS, classic compute and serverless compute have different hosting boundaries. Microsoft Fabric combines analytics workloads within a SaaS platform built around OneLake. These descriptions suggest different questions about isolation, access paths, management, and cost; they do not establish a universal winner.

Within a platform, distinguish work on records from work that directs it. The data path reads, transforms, and serves data. Control functions schedule jobs, select resources, authorize actions, and record operational state. Metadata describes schemas, locations, table versions, and dependencies. These are useful analytical distinctions, not a promise that every vendor physically separates them in the same way. Metadata can be sensitive, and an unavailable catalog or authorization service can stop access to otherwise intact data files.

An orchestration system coordinates dependencies and execution; it does not automatically validate business meaning. A table format identifies files and committed versions; it is not a processing engine. A catalog may manage discovery, table metadata, permissions, or several of these functions, depending on the product. Knowing these distinctions makes it possible to ask which component actually owns a guarantee and which components merely rely on it.

Product knowledge matters at the interfaces. Ask how a connector discovers deletes, how a table exposes a committed version, how an engine resolves permissions, and whether a consumer reads the latest source state or a separately refreshed copy. Open file formats can improve portability, while catalog APIs, enabled table features, policies, and operational tooling can still constrain a move. A common file extension is not evidence that two systems interpret every operation identically.

How data fails quietly: the catalog

A pipeline can finish successfully while producing incomplete, outdated, or misinterpreted data. The examples below show what to inspect and which checks can reveal the problem.

Failure	How it looks	How to detect or prevent it
Dropped rows	Expected records disappear after a join or filter.	Compare counts and expected keys before and after transformations; investigate unmatched records.
Duplicated rows	Retries, repeated files, or joins count the same record more than intended.	Define the expected row identity; check duplicates and make repeated loads preserve the intended result.
Wrong time	Orders appear under the wrong business date or disappear at a date boundary.	Define the business time zone; derive UTC boundaries using its rules and test clock-change dates.
Stale data	A report responds normally but still shows an older period.	Check source coverage and update time against the agreed freshness target; label stale results or stop serving them as required.
Schema drift	A source changes a field name or type, causing failure or unexpected missing values.	Validate the expected structure, communicate changes, and retain permitted source copies for investigation or reprocessing.
Definition drift	Reports intended to use the same active-user definition implement different rules.	Agree on the metric definition, owner, and version; check that reports use the intended rule.
Silent partial failure	Only part of an expected delivery reaches the output, but the job reports success.	Compare expected files, records, or source totals with what arrived; expose a completed, validated output rather than a partial write.
Restated history	A source corrects older records, but downstream results still use the old values.	Identify changed periods and rebuild affected results from permitted corrected inputs, while recording the versions used.

Choose checks and responses that fit the use. A missing settlement file may require blocking a finance report; a late dashboard refresh may call for a visible stale-data label and an alert. Agree on the tolerance and owner rather than stopping every pipeline at an arbitrary percentage difference. Reconciliation compares results with a reference. Counts are one check; keys, amounts, and definitions may also need comparison at the relevant level. Matching counts alone can hide missing records offset by extra ones.

What the work is actually like

The balance of work varies with the team and platform. Three activities recur:

• Understanding sources and meaning. What does this column mean? Why are there three status codes for “paid”? Who decides what a refund is? Reading other people’s schemas, talking to the people who own them, and writing down what you learned.
• Building and changing pipelines. The SQL, the Python, the configuration. How much of it is new construction and how much is changing existing pipelines depends on the team and the stage of the platform.
• Operating and investigating. Why is it slow, why is it wrong, why did it not run. The Tuesday-morning question, in its thousand variants.

SQL, data modeling, Python, and an understanding of storage and processing systems support this work. So does explaining why two numbers differ, what evidence supports each number, and what remains uncertain. A developer may own the pipeline while a business steward owns the definition; the design must connect those responsibilities rather than silently transferring both to whoever writes the query.

Four misconceptions to drop early

• “It is about big data.” A small dataset can also have missing records, inconsistent definitions, and access problems. The hard problems are correctness, change, and ownership, at any size.
• “Real-time is better.” Real-time delivery is useful when a timely action needs it. It also brings operating requirements that should be justified by that use, rather than by the label alone.
• “The tool will fix it.” A warehouse, a catalog, or an orchestrator does nothing about a metric with no owner. Tools amplify practices; they do not replace them.
• “Just make the dashboard right.” A dashboard depends on upstream source coverage, transformations, and metric rules. Correcting the display may be enough for a presentation defect; repairing missing or misinterpreted records requires tracing the relevant upstream path.

What must be decided before choosing a stack

For the shop, begin by stating the consumer question: daily paid orders by business date and currency, with late changes revising a named reporting window. Identify the authoritative source for each field, the supported source boundary, the permissible delay, who may read the result, and who can approve a definition change. These decisions make technology requirements concrete.

A daily reporting deadline may permit scheduled extraction and SQL transformation. A fraud response needed before an order is accepted changes the source integration, latency budget, state, and failure response. A historical financial report adds requirements for versioned evidence and controlled correction. The same business can need all three paths without forcing them into the same processing model.

A useful architecture explains both the normal path and what remains true when part of it fails. If collection stops, how will consumers recognize stale coverage? If a customer row is missing, does an order disappear or remain with an unknown attribute? If a definition changes, is history restated or preserved under an older version? These are the decisions that make a collection of tools into a dependable data system.

References and scope

Product architecture descriptions were checked on October 3, 2026. Consult the documentation for the deployment, edition, and version being evaluated; this article does not compare measured performance or current prices.

• Google SRE Workbook: Data Processing Pipelines discusses pipeline requirements, freshness, correctness, and operation as a service.
• Snowflake documentation: Snowflake key concepts and architecture describes Snowflake’s database storage, compute, and cloud services layers.
• Databricks on AWS documentation: High-level architecture describes the control plane and compute plane, including classic and serverless compute on AWS.
• Microsoft Fabric documentation: What is Microsoft Fabric describes Fabric as a SaaS analytics platform built around OneLake.

The shop, counts, and design scenarios here are illustrative. The vendor documents above support the corresponding product descriptions, not a claim that one product is best.