Unit
- Data lifecycle overview: Data moves through acquisition, storage, processing, analysis, and archival. Database administrators maintain storage systems, enforce retention policies, and monitor access patterns, while data engineers design transformation workflows that convert raw inputs into structured outputs for analytics or application consumption.
- Role separation: A database administrator (DBA) focuses on database health, backup schedules, user management, and schema changes. A data engineer builds and operates data pipelines, integrates APIs, and prepares datasets for machine learning and reporting. Both roles rely heavily on SQL but apply it in different contexts.
- Core principles: Data environments must balance performance, availability, consistency, and cost. Professionals define service-level objectives for uptime, latency, and throughput, then automate monitoring and alerting to meet these targets as data volumes grow.
- OLTP vs OLAP: Transactional systems (OLTP) record daily operations and require fast writes and consistent reads. Analytical systems (OLAP) support complex aggregations over historical data. Choosing between them depends on query patterns and freshness requirements.
- Database types: Relational databases organize data into tables with fixed schemas. NoSQL databases such as document stores and key-value stores offer schema flexibility and horizontal scaling. Data lakes store raw files in their native format for later processing.
- Deployment options: On-premises installations allow complete control over hardware, configuration, and security. Cloud managed services reduce maintenance effort but require skills in identity management, networking, and cost governance.
- Business alignment: Data directly affects revenue, risk, and customer experience. A robust data platform enables evidence-based decisions, personalization, and operational efficiency.
- Skill development: Successful practitioners continuously update their knowledge of SQL standards, database engine internals, cloud services, and data governance regulations.
- ETL and ELT patterns: Data engineers move data between systems with batch or streaming pipelines. ETL transforms data before loading, while ELT loads raw data first and transforms it inside the warehouse. Choosing a pattern affects storage cost, freshness, and the time developers spend reshaping data for analysts.
- Backup and recovery strategies: DBAs design backups using full, differential, and transaction log increments. Recovery objectives define how much data loss is acceptable and how quickly systems must return. Testing restore procedures regularly is essential because an untested backup can fail when needed most.
- Indexing fundamentals: Indexes accelerate read queries by allowing the database to locate rows without scanning every page. DBAs evaluate query plans and create indexes that support frequent filter, join, and sort operations. Excessive indexing slows writes and consumes storage, so each index must justify its cost.
- Data governance and security: Access controls, encryption, and audit logging protect data at rest and in transit. Compliance mandates such as GDPR or HIPAA require clear retention schedules and documented data lineage. Both DBAs and data engineers must implement policies that align with legal and business requirements.
Code Example
SQL
-- List top engineering salaries
SELECT emp_name, salary
FROM employees
WHERE department_id = 'ENG'
ORDER BY salary DESC
LIMIT 10;This query filters employees by department and sorts the result set to show the ten highest salaries. The LIMIT clause controls the row count.
Quick Revision
- Database administration preserves data availability and integrity, while data engineering designs movement and transformation.
- OLTP, OLAP, and data lakes serve different data consumption patterns.
- Cloud deployment shifts operational focus from hardware to configuration and automation.
- Core responsibilities include performance, security, backup, and lifecycle management.