Database Administration and Data Engineering Best Practices

01Purpose and scope

This manual supports the course Database Administration and Data Engineering. It is written as a working reference for people who keep database platforms healthy and for people who move, shape, and validate data so that other teams can use it.
Database administration focuses on the stability, security, performance, and recoverability of the data stores themselves. Data engineering focuses on the movement and transformation of data between sources, stores, and consumers. In small teams one person often does both, and in large organizations the work is split between platform, operations, and pipeline teams. This manual covers the shared ground between the two, along with the decisions where they meet.
The practices described here apply to relational databases, analytical warehouses, and the batch or streaming pipelines that load them. They are less relevant to application-level data modeling, machine learning feature stores, or data science experimentation, which have their own conventions. The details change depending on whether databases run on premises, in a managed cloud service, or in a hybrid arrangement, because the cloud provider usually takes responsibility for some tasks such as hardware replacement and minor patching while leaving others, like schema design and access control, with your team.

02Core principles

Treat data as owned. Every database and every dataset should have a named owner who can approve access requests, agree to retention rules, and answer questions about meaning. Administrators keep the system running, but the owner decides what the data is for.
Grant the least privilege needed for the task. Service accounts for pipelines should not be database owners, and analysts should not hold write permissions on production tables. Review grants on a schedule rather than only when someone asks.
Assume a backup is not a backup until it has been restored. Recoverability is a property you demonstrate with test restores into an isolated environment, timed against your stated objectives.
Make changes reproducible. Schema definitions, permissions, pipeline code, and configuration belong in version control and should be applied through reviewed, repeatable steps rather than manual edits in a production console.
Treat the schema as an interface. Column names, types, and table structures are contracts with downstream readers. Changing them has costs that fall on people who may not know a change is coming.
Prefer simple, observable pipelines over clever ones. A job that reruns safely and reports its own row counts is worth more than a faster job nobody can debug at two in the morning.

03Planning and preparation

Start with an inventory. List each database instance, its engine and version, its size and growth rate, the applications and pipelines that depend on it, its owner, and its support hours. Many teams discover during this exercise that some datasets have no clear owner or that a supposedly retired database is still receiving nightly loads.
Agree on service objectives before tuning anything. The recovery point objective describes how much data loss is acceptable, measured as elapsed time since the last recoverable state. The recovery time objective describes how long a restore may take. Freshness targets for pipelines and latency targets for queries are also worth writing down. These numbers come from the business and the data owners, not from the administrator alone, and they set the budget for backups, replicas, and compute.
Identify stakeholders early. For most platforms this includes data owners, application developers who write to the database, analysts who query it, a security contact who approves access and encryption choices, and someone responsible for compliance where personal or regulated data is involved. Record who must be consulted before a schema change, a major upgrade, or a retention change.
Plan environments. A typical setup includes development, test, and production, with a way to create masked or synthetic data for non-production use. Decide how environments are refreshed, how long they live, and who can copy production data into them. Copying production data into a lower environment is one of the most common sources of unplanned exposure.
Estimate capacity with real numbers. Use historical growth, planned load from new sources, and index or partition overhead. Set alert thresholds well before storage or memory runs out, because a full disk on a database server can stop writes entirely.
Check the tooling you already have before adding more. Most engines include built-in tools for backup, replication, auditing, and query statistics. Third-party schedulers, orchestration frameworks, and monitoring products can be valuable, but each one adds an operational surface that someone must maintain.

04Recommended workflow

1
: Define the request. Write down the purpose, the datasets or tables involved, the expected users, the required freshness or latency, and the date range of any historical data needed. A request without these details usually becomes a series of revisions later.
2
: Assess impact. Identify upstream sources, downstream consumers, reports, and scheduled jobs that touch the affected objects. Check whether the change alters a column type, removes a field, or changes a key, since these are the changes most likely to break consumers silently.
3
: Design the change. For schema work, write the migration as a versioned script that can be applied forward and, where possible, reversed. For pipelines, describe the source, the transformation rules, the target, the load pattern, and how reruns behave. Prefer idempotent loads, such as merges keyed on a business identifier, over appends that duplicate rows when a job restarts.
4
: Test in a non-production environment. Run the migration or pipeline against a representative subset or a masked copy of production data. Compare row counts, null rates, and aggregate totals against the expected results. Record run times so you can estimate the production window.
5
: Review and approve. Submit the change for peer review with the design, test results, rollback plan, and impact list attached. The reviewer should check permissions, performance effects such as locks or long index builds, and whether consumers have been notified. Approval rules differ by organization, and regulated environments may require formal sign-off from a named authority.
6
: Schedule the deployment. Choose a window that respects the service objectives and the load patterns of the database. Large index builds and table rewrites often need a quiet period. Confirm that a current backup or snapshot exists immediately before the change, and note the time it was taken.
7
: Deploy and verify. Apply the change using the reviewed procedure rather than ad hoc commands. Run the verification queries defined in the test plan, check that scheduled jobs complete, and watch error logs and resource metrics for at least one full business cycle where possible.
8
: Close and record. Update the runbook, data dictionary, and change log. Tell consumers what changed and when. Remove temporary grants, drop scratch tables, and confirm that rollback artifacts are stored where they can be found later.

05Quality checks, risk controls and compliance

Backups need scheduled restore tests. At a minimum, restore the most recent backup of each critical database into an isolated environment at a regular interval, run integrity checks, and measure the elapsed time. Keep the results. A restore that has never been exercised is a hypothesis, and the first real test is usually during an incident.
Data validation should run as part of pipelines and after loads. Useful checks include row count comparisons against source extracts, uniqueness of business keys, referential integrity where the engine does not enforce it, valid ranges for numeric fields, and freshness of the latest record. Failing checks should stop downstream publication or raise a visible flag, depending on how the data is consumed.
Access reviews should happen on a regular schedule. Compare current grants with the owner's approval records, remove accounts for departed staff and retired services, and rotate credentials that are shared or long-lived. Auditing of privileged actions should be enabled and the logs should be stored where administrators cannot alter them without detection.
Encryption in transit and at rest is expected for most production systems, and key management needs its own owner and procedure. Encryption does not replace access control, because an authorized but careless user can still expose data.
Personal and regulated data bring additional obligations. Retention periods, deletion requests, cross-border transfer rules, and breach notification duties depend on the jurisdiction and the sector involved. Confirm current requirements with your privacy office, legal counsel, or the authoritative regulator for your location, because rules change and this manual does not state them. Where the data falls under sector rules for health, finance, tax, or safety, this guidance does not replace qualified professional judgment, approved internal procedures, or the authorization of the accountable person.
Risk controls should be proportionate. Production changes that cannot be rolled back quickly need stronger review and a more cautious window. Emergency changes are sometimes unavoidable, but they should be logged, reviewed after the fact, and followed by a normal change to make the fix permanent.

06Collaboration and documentation

Good documentation is short, current, and located where people look for it. A runbook for each critical database should describe the architecture, the owners, the backup and restore procedure, the monitoring dashboards and their alert meanings, the escalation path, and the known limitations. Store runbooks in version control next to the configuration they describe.
A data dictionary records the meaning of tables and columns, the source of each field, units, allowed values, and the owner. Lineage notes show how a dataset is produced from its sources, which helps when a number looks wrong and someone needs to trace it back. Keep these notes close to the pipeline code so they change together.
Handoffs between administration and engineering are a frequent point of failure. Agree on who owns indexes, partitions, statistics maintenance, and scheduled jobs. A simple responsibility matrix, reviewed when teams change, prevents two groups from each assuming the other is handling a task.
Communication with consumers matters as much as the technical work. Announce planned outages and schema changes with enough notice for dependent teams to adjust. When a pipeline fails or delivers incorrect data, post a status note that says what is affected, what is known, and when the next update will come. Avoid promising a fix time you cannot control.

07Measures and continuous improvement

Track indicators that reflect the service objectives rather than activity counts. Useful measures include the percentage of backups that complete without error, the percentage of scheduled restore tests that meet the recovery time objective, query latency at the median and high percentiles for key workloads, pipeline freshness against the agreed target, the number of failed or rerun jobs per week, and the share of data quality checks that pass on first run. Set targets with the owners of each service.
Review incidents after they are resolved. A short written review should describe the timeline, the detection method, the cause, the recovery steps, and the changes that will reduce the chance or impact of recurrence. Keep these reviews blameless so that people report problems early.
Look for trends. Rising load time on a nightly job, growing lock wait counts, or increasing null rates in a source field often appear weeks before they cause an outage. A monthly or quarterly review of these trends, held with data owners, is a practical habit.
Limits apply. Metrics can be gamed, and a high pass rate on easy checks does not prove the data is right. Combine numbers with spot checks by people who understand the business meaning of the data, and revisit the metrics themselves when the workload changes.

08Common mistakes and how to prevent them

Backups that have never been restored are the most dangerous mistake. Prevent it by scheduling restore tests with recorded outcomes and treating a failed test as a priority defect.
Broad administrative rights granted for convenience tend to persist. Use role-based grants, time-limited elevation where the platform supports it, and a periodic review that asks whether each privileged account still needs its access.
Schema changes deployed without notifying consumers cause breakage that appears in reports days later. Maintain a list of known consumers for each critical table and include them in the impact assessment.
Pipelines that append rather than merge produce duplicates after a restart. Design loads to be idempotent, and test a forced rerun before going live.
Production data copied into development environments without masking creates privacy exposure and weak test coverage. Use masked or synthetic data, and control who can perform the copy.
Disk and memory growth noticed only when the system fails is avoidable. Set alerts at sensible thresholds, review capacity trends, and plan purges or archives according to the retention rules the owner has approved.
Dropping old columns or tables immediately after a migration removes the safety net. Keep deprecated objects for a defined period, confirm no reads occur through monitoring, and then remove them under change control.
Documentation written once and never updated misleads people during incidents. Assign each runbook an owner and review it after every significant change or incident.

09Practical checklist

Confirm the database owner, the data owner, and the escalation contact are recorded in the runbook.
Verify the most recent backup of each critical database has been restored successfully in an isolated environment within the review period.
Check that recovery point and recovery time objectives are written down and agreed with the data owners.
Review privileged and service account grants against current approvals and remove any that are no longer needed.
Confirm that migrations and pipeline changes are in version control and have a tested rollback or rerun plan.
Run row count, key uniqueness, and freshness checks after each load and confirm that failures are visible to someone who can act on them.
Verify that copies of production data in non-production environments are masked or synthetic, or that an approved exception is on file.
Confirm that consumers of any changed table have been notified and that the data dictionary reflects the new structure.
Review capacity trends and alert thresholds for storage, memory, connections, and replication lag.
Confirm current retention and privacy obligations with the appropriate authority for your jurisdiction before changing how long data is kept.

10Frequently asked questions

★Learning sources

Micro Degree: Database Administration and Data Engineering
Micro Degree: Data Analytics Applications
Micro Degree: Azure Cloud Fundamentals
Micro Degree: Google Cloud Platform Basics

?Frequently asked questions

Who should own the backup schedule when administration and engineering are separate teams?

The administration team usually configures and monitors backups, but the schedule should reflect the recovery objectives agreed with the data owners. Put the responsibility in the runbook so that pipeline teams know when a backup window might affect their jobs. Review the split whenever the team structure changes.

How often should restore tests be run?

The right frequency depends on how critical the database is and how quickly its contents change. Many teams schedule tests for their most important systems on a regular cycle and test less critical systems less often. Record the elapsed time and outcome each time so the trend is visible.

Can a managed cloud database remove the need for administration?

A managed service reduces some tasks, such as hardware maintenance and certain patching, but it does not remove responsibility for schema design, access control, data quality, or recovery testing. You still need to confirm what the provider covers and what your team must do. Check the provider documentation for current details.

What should I do if I find personal data in a development environment?

Stop further copying, notify the data owner and your privacy or security contact, and follow the internal incident procedure. Do not delete evidence until the responsible person has approved the next steps. Confirm the applicable notification requirements with legal or compliance staff, since they depend on the jurisdiction and the data involved.