Data engineering starts with the decision, not the tool. Before choosing a pipeline, warehouse, or orchestrator, understand the business context, data characteristics, constraints, and the people who depend on the output.
A data engineer designs, builds, operates, and evolves the systems that move data from where it is created (operational sources) to where it is needed (analytics, ML, AI, products, and decisions). The role sits between data producers (software engineers, DevOps, external partners) and data consumers (analysts, data scientists, ML engineers, business leaders).
Without reliable data pipelines, analysts query stale or incorrect data, ML models train on garbage, dashboards show contradictory numbers, and leadership loses trust in data-driven decisions. The data engineer ensures dependable flow: not just movement, but quality, governance, timeliness, and cost control.
OLTP systems are the operational backbone of applications. They handle day-to-day transactions: bank transfers, e-commerce orders, user registrations, inventory updates. Think PostgreSQL, MySQL, SQL Server, Oracle: systems optimized for fast, concurrent, small-transaction CRUD (Create, Read, Update, Delete) operations.
OLAP systems serve analytical workloads: aggregations, trends, comparisons, reports, and machine learning features. They process large volumes of historical data to answer questions like "What was total revenue by region last quarter?" Think Snowflake, BigQuery, Redshift, ClickHouse: systems optimized for scanning billions of rows across a few columns.
| Dimension | OLTP | OLAP |
|---|---|---|
| Purpose | Run the business | Analyze the business |
| Storage layout | Row-based | Columnar |
| Schema | Normalized (3NF) | Denormalized / Star |
| Query pattern | Point lookups, small transactions | Full-table scans, aggregations |
| Latency | Milliseconds | Seconds to minutes |
| Data volume | GBs (current state) | TBs to PBs (historical) |
| Users | Applications, microservices | Analysts, data scientists, BI tools |
| Examples | PostgreSQL, MySQL, SQL Server | Snowflake, BigQuery, Redshift, ClickHouse |
Data engineering is fundamentally about moving data from OLTP systems (where it is created) to OLAP systems (where it is analyzed). This is the core pipeline: extract from operational sources, transform to make it trustworthy, and load into analytical storage. Every tool, technique, and architecture decision serves this journey.
SQL is the universal language of data. Every database, warehouse, lakehouse, and most processing engines speak SQL. Data engineers use SQL for extraction, transformation (dbt is SQL-first), quality checks, profiling, and debugging. You need fluency in:
Data profiling is the systematic examination of data to understand its structure, content, quality, and relationships. It is the diagnostic step before building any pipeline, like a doctor examining a patient before prescribing treatment.
Building a pipeline without profiling leads to silent failures: unexpected NULLs crash transformations, wrong data types cause casting errors, undiscovered duplicates inflate metrics, and missing values create misleading dashboards. Profile first. Always.
Select a scenario to see which data engineering approach fits best.
Latency: Next-day is fine (batch). Volume: Moderate, aggregated. Approach: Scheduled ELT into a warehouse with a star schema model and BI tool on top. Focus on data quality, freshness SLAs, and consistent metric definitions. dbt is ideal for the transformation layer.
| Consumer | Needs | Data shape | Latency |
|---|---|---|---|
| Business analyst | Reports, KPIs, ad-hoc queries | Star schema, aggregates | Daily / hourly |
| Data scientist | Features, training data, experiments | Wide tables, history | Batch is often fine |
| ML engineer | Feature store, model serving | Key-value, versioned | Near real-time |
| Product team | Embedded analytics, recommendations | APIs, pre-computed | Low latency |
| Data engineer | Monitoring, lineage, quality | Metadata, logs | Real-time alerts |
The same underlying data may need to be served in multiple shapes, at different latencies, through different interfaces. This is why the pipeline architecture matters: a well-designed system serves all consumers without building separate pipelines for each.
1. What is the primary purpose of an OLTP system?
2. Which is the best first question when starting a data engineering project?
3. Why is data profiling done before building a pipeline?
4. Where does the data engineer sit within the data team?
5. An analyst needs last quarter's revenue by region on a Monday morning dashboard. What approach fits?