What Does a Data Engineer Do?

Learning Objectives

• Define the role of a data engineer and how it differs from related positions.
• Identify the core responsibilities, skills, and tools data engineers use.
• Build and test a basic Extract-Transform-Load (ETL) pipeline using Python and SQL.
• Recognize best practices, common pitfalls, and career pathways.
• See how Galaxy simplifies SQL collaboration within a data-engineering stack.

1. Introduction

Modern organizations generate oceans of data—clickstreams, payments, logs, IoT signals, and more. Turning that raw data into trusted, analytics-ready assets is the job of a data engineer. Think of them as architects and plumbers combined: they design the blueprint for how data should flow, then build the pipelines that keep it moving smoothly and safely.

2. What Is Data Engineering?

Data engineering is the discipline of designing, building, and maintaining systems that collect, store, and make data usable for downstream consumers such as analysts, data scientists, machine-learning engineers, and business applications. While an analyst focuses on answering questions, a data engineer ensures the data needed to answer those questions is reliable, timely, and scalable.

3. Core Responsibilities of a Data Engineer

3.1 Designing Data Architectures

Before writing a single line of code, data engineers map out where data will live (storage) and how it will flow between systems (ingestion paths). Decisions include:

• Choosing between relational (PostgreSQL, MySQL), columnar (Snowflake, BigQuery), or NoSQL stores (MongoDB, DynamoDB).
• Modeling data with schemas (star, snowflake, data vault) to meet performance and governance needs.
• Planning for compliance (GDPR, HIPAA) and security (encryption, access controls).

3.2 Building and Maintaining Data Pipelines

ETL (Extract-Transform-Load) or ELT (Extract-Load-Transform) processes move raw data into analytics-ready formats.

1. Extract: Pull data from sources—APIs, logs, OLTP databases.
2. Transform: Clean, validate, and reshape data (e.g., convert time zones, deduplicate records, enforce types).
3. Load: Push data into the target warehouse, lake, or mart.

Pipelines must be fault-tolerant and auditable. Engineers often orchestrate them with tools like Apache Airflow, Prefect, Dagster, or cloud-native schedulers.

3.3 Ensuring Data Quality and Governance

Bad data equals bad decisions. Data engineers implement:

• Validation Tests (null checks, uniqueness, schema drift detection).
• Monitoring & Alerting (e.g., Great Expectations, Monte Carlo).
• Documentation & Lineage so consumers know data provenance.

3.4 Performance Optimization and Scalability

A 10 GB table may query quickly today but lag tomorrow at 10 TB. Engineers optimize partitioning, indexing, and query design—and scale compute via distributed engines like Spark or Snowflake’s virtual warehouses.

3.5 Collaboration with Other Roles

Data engineers bridge many teams:

• Analytics/Data Scientists: Provide clean data sets.
• Software Engineers: Consume data via APIs or event streams.
• Security & Compliance: Enforce policies.
• Business Stakeholders: Translate requirements into technical specs.

Effective collaboration requires clear, version-controlled SQL and documentation—areas where Galaxy shines.

4. Key Skills and Tools

4.1 Programming Languages

• Python: De-facto standard for ETL scripting and orchestration libraries.
• SQL: Universal for querying, transforming, and validating data. Galaxy acts as a turbo-charged SQL IDE.
• Others: Scala (Spark), Java (Kafka), Go (high-performance services).

4.2 Storage Technologies

• Relational: PostgreSQL, MySQL.
• Cloud Warehouses: Snowflake, BigQuery, Redshift.
• Data Lakes: Amazon S3 + Presto/Trino, Delta Lake, Iceberg.
• NoSQL/Event Stores: Kafka, Kinesis, MongoDB.

4.3 Processing Frameworks

• Batch: Apache Spark, dbt.
• Streaming: Apache Flink, Kafka Streams.
• Vectorized Engines: DuckDB for quick local analytics.

4.4 Orchestration & Workflow Management

Orchestrators schedule tasks, manage dependencies, and monitor runs. Examples:

• Apache Airflow (most popular, Python-based DAGs).
• Prefect (developer-friendly, cloud or self-hosted).
• Dagster (typed assets, software-defined assets paradigm).

4.5 Cloud Platforms & DevOps Know-How

Infrastructure-as-code (Terraform, CloudFormation), containerization (Docker, Kubernetes), and CI/CD pipelines are increasingly part of a data engineer’s toolkit.

5. Hands-On: Building a Simple ETL Pipeline

Scenario

You need to ingest daily orders from a production PostgreSQL database, cleanse them, and load them into Snowflake for analytics. Below is a minimal, educational example.

Step 1 – Extract

import pandas as pd
import sqlalchemy as sa

SRC_CONN = sa.create_engine("postgresql://analytics:******@prod-db:5432/shop")
query = "SELECT * FROM public.orders WHERE created_at >= CURRENT_DATE - INTERVAL '1 day'"
orders = pd.read_sql(query, SRC_CONN)
print(f"Fetched {len(orders):,} new orders")


Step 2 – Transform

# 1. Ensure timestamps are UTC
orders['created_at'] = pd.to_datetime(orders['created_at']).dt.tz_localize('UTC')

# 2. Remove cancelled orders
orders = orders[orders['status'] != 'cancelled']

# 3. Rename columns to snake_case
orders.rename(columns={'CreatedAt': 'created_at', 'UserID': 'user_id'}, inplace=True)


Step 3 – Load

import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas

SF_CONN = snowflake.connector.connect(
user='analytics',
password='******',
account='company_xyz',
warehouse='TRANSFORMING_WH',
database='ANALYTICS',
schema='PUBLIC'
)
write_pandas(SF_CONN, orders, 'STG_ORDERS')
print("Loaded to Snowflake ✅")


Validating in Galaxy

After loading, open Galaxy’s blazing-fast editor, connect to Snowflake, and run:

SELECT COUNT(*) AS orders_last_day
FROM analytics.public.stg_orders
WHERE created_at >= DATEADD('day', -1, CURRENT_TIMESTAMP());


Because Galaxy stores query history, you can endorse this validation query and share it with your team. Anyone rerunning it uses the same logic—preventing silent drift.

Cron + Orchestration Example

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime, timedelta

# DAG definition
def extract_transform_load():
# Place the ETL code shown earlier here
pass

def validate_with_galaxy():
# Example: trigger Galaxy via its CLI or API to run sanity-check SQL
pass

default_args = {
'owner': 'data-eng',
'retries': 2,
'retry_delay': timedelta(minutes=5)
}

dag = DAG('daily_orders_etl', start_date=datetime(2023, 1, 1), schedule_interval='@daily', default_args=default_args)

PythonOperator(task_id='run_etl', python_callable=extract_transform_load, dag=dag) >> \
PythonOperator(task_id='validate', python_callable=validate_with_galaxy, dag=dag)


6. Best Practices & Common Pitfalls

• Version Everything: SQL, configs, and data models live in Git. Galaxy’s GitHub integration automates PRs for query changes.
• Test Early & Often: Unit-test transformations with dbt or PyTest-SQL-style helpers.
• Monitor Data, Not Just Pipelines: A green Airflow DAG can still load garbage. Implement data-quality checks.
• Document: Auto-generate lineage diagrams and keep your semantic layer in sync.
• Avoid Over-Optimization: Premature sharding or exotic tech can slow teams; start simple and scale sensibly.

7. Real-World Applications of Data Engineering

1. Product Analytics: Track user behavior and feed experimentation platforms.
2. Machine Learning: Provide feature stores and training datasets.
3. Operational Dashboards: Real-time alerts for logistics or fraud detection.
4. Embedded Analytics: SaaS vendors embed metrics directly into their apps; Galaxy’s forthcoming API endpoints make this seamless.

8. Practice Exercises

1. Reverse ETL: Write an ELT that pushes aggregated LTV metrics back into your OLTP database. Validate counts using Galaxy.
2. Schema Evolution: Simulate a new column (discount_code) arriving in the orders source. Update transformation logic and add a data-quality test.
3. Query Optimization: Take a 50-line analytical SQL query and use Galaxy’s AI copilot to rewrite it with CTEs and proper partition pruning.

9. Key Takeaways

• Data engineers design data architectures, build pipelines, and safeguard quality.
• Proficiency in Python, SQL, orchestration, and cloud services is essential.
• Tools like Galaxy accelerate collaboration, code review, and AI-assisted SQL.
• Start small, enforce testing, and document: scalable systems follow naturally.

10. Next Steps

Ready to dive deeper?

• Clone the sample ETL repo linked below and deploy it with Airflow on Docker.
• Sign up for the Galaxy beta, connect your warehouse, and import your repo’s SQL to explore AI refactoring.
• Study Designing Data-Intensive Applications by Martin Kleppmann for architectural fundamentals.

Last updated: 2024-05-20