Databases, SQL, Warehousing, Data Quality, and Data Governance

A table represents a defined type of entity or event. A patient table, encounter table, medication administration table, and observation table should not be interpreted as interchangeable lists. Each has a grain: what one row represents.

Grain is the first question to ask before calculation. If one row represents a patient in one table and one laboratory result in another, joining them can create multiple rows per patient. Failure to recognize grain is a common cause of inflated counts.

Keys connect records. A primary key uniquely identifies a row within a table. Foreign keys refer to identifiers in related tables. In clinical systems, patient, encounter, order, result, and medication identifiers help preserve relationships across events.

Normalization reduces unnecessary duplication in transactional systems. Instead of storing the same patient demographics on every lab result, normalized designs separate entities and reference them through keys. This improves consistency but makes reporting require joins.

Database structure reflects application design, not necessarily clinical intuition. A nurse may think of a wound assessment as one event, while the database stores header information, individual wound rows, measurements, interventions, and signatures in separate tables.

SQL expresses a question against structured data. SELECT identifies what to return, FROM identifies the source, WHERE restricts rows, JOIN combines related tables, GROUP BY creates aggregation groups, and functions such as COUNT or AVG summarize values.

A syntactically valid query can still be clinically wrong. A query may run successfully while using the wrong denominator, including duplicate encounters, excluding null values incorrectly, or joining on the wrong key. Technical execution is not clinical validation.

Filters encode definitions. A report for “current residents” depends on what counts as current: census status, discharge timestamp, unit assignment, payer type, or snapshot date. Informatics professionals should make those assumptions visible.

Null is not automatically zero. A missing value may mean not assessed, not applicable, not interfaced, unknown, or absent. Converting all nulls to zero can create false certainty.

SQL literacy improves collaboration even when someone else writes the query. An informatics nurse should be able to ask: What is the grain? Which tables are joined? What is the denominator? How are duplicates handled? What does null mean? Which timestamp is used?

Operational databases prioritize reliable transactions. The EHR must record orders, assessments, administrations, and updates quickly and consistently while many users work simultaneously.

Data warehouses prioritize analysis. Warehouses reorganize and integrate data so analysts can query across time, systems, and subject areas without placing heavy analytic load on production systems.

Dimensional models can make analytics easier to understand. Fact tables often represent events or measures, while dimension tables describe patients, dates, locations, clinicians, or other context. This is different from the highly normalized structure common in transactional databases.

Data lakes and lakehouse architectures can store broader forms of data. These approaches may hold raw or semi-structured information at greater scale. Their flexibility increases the importance of catalogs, quality controls, lineage, and access governance.

A single “source of truth” is usually a governed definition, not one magical database. Different systems may legitimately own different facts. The organization needs authoritative definitions, provenance, and rules for resolving conflicts.

ETL means extract, transform, load; ELT changes the order so transformation occurs after loading into the target analytic platform. The choice depends on architecture, scale, tooling, governance, and performance.

Transformations are analytic decisions. Converting local unit names, calculating age, assigning a resident to a service line, deduplicating encounters, or deciding which status wins are not neutral technical steps. They alter how data will be interpreted.

Lineage should preserve each important transformation. When a dashboard value is challenged, the team should be able to trace it back through source fields, business rules, and refresh processes rather than reverse-engineering the calculation from memory.

Refresh timing affects meaning. A “current” dashboard refreshed nightly is not real time. Informatics teams should label refresh frequency and understand latency between source events and analytic availability.

Migration is a special form of transformation. Moving from an old EHR to a new one requires decisions about what to convert, archive, summarize, or leave accessible in a legacy system. More data are not always safer if the conversion cannot preserve context reliably.

Accuracy asks whether data correctly represent the underlying event or state. This often requires comparison with a trusted source or direct validation rather than checking whether a field is populated.

Completeness asks whether required or expected information is present. High completeness can coexist with inaccurate values, so it should not be treated as the only data-quality dimension.

Consistency asks whether the same concept agrees across locations, systems, and time where agreement is expected. Inconsistent terminology or duplicate patient identities can undermine longitudinal analysis.

Timeliness asks whether data are available soon enough for their intended decision. A quarterly dataset may be suitable for strategic review but unsafe for same-day clinical intervention.

Validity asks whether data conform to expected formats, domains, and rules. A temperature of 785°F is probably invalid even though it is numeric. Plausibility rules can detect errors, but overly narrow rules can reject legitimate extremes.

Uniqueness addresses unintended duplication. Duplicate encounters, patients, or messages can inflate metrics and create clinical confusion.

Data governance defines who can decide what data mean, who may use them, and how they are managed. It is broader than database administration. Governance addresses definitions, quality, access, retention, stewardship, sharing, and acceptable use.

Data ownership and stewardship should be distinguished. Organizations use these terms differently, but a useful model is that accountable business/clinical leaders own decisions while stewards maintain definitions, quality processes, and operational coordination.

A data dictionary is a governance artifact. It should define important elements and metrics in plain language, identify source systems and owners, document calculation logic, and note limitations.

Access should reflect purpose and minimum necessary principles. Analysts do not automatically need unrestricted clinical data because their work is beneficial. Role, purpose, sensitivity, and legal requirements should shape access.

Retention and deletion are also governance decisions. Keeping everything forever increases cost, privacy exposure, and discovery burden. Deleting too aggressively can violate legal, clinical, or research obligations. Retention schedules should be deliberate.