Definitions and comparisons
What is an operational data store? ODS vs data warehouse explained
By SourceX Editorial · Updated
Short answer
An operational data store (ODS) is a database that integrates current, detailed data from several operational systems into one up-to-date view. IBM's Redbook defines it as subject-oriented, integrated, volatile and current: records are overwritten as sources change. A data warehouse, in Bill Inmon's terms, is nonvolatile and time-variant, built to keep history. For an AI data license, that difference usually decides which system you export from.
Key takeaways
- An operational data store integrates current, detailed records from several systems and overwrites them as sources change.
- By IBM's Redbook definition the ODS is volatile and current; in Inmon's terms the data warehouse is nonvolatile and time-variant.
- A data lake keeps raw data with structure applied on read; the source application holds the original records, including free text.
- Change data capture, time travel and app-level history have short default windows, so history must be landed before it expires.
- For a data license, export event history from the warehouse or an append-only log, text from the source app, and use the ODS as a key crosswalk.
What is an operational data store?#
An operational data store is a database that pulls detailed records from several operational systems, integrates them into one consistent model, and keeps them current so people and applications can query today's state of the business. IBM describes the ODS as holding the most recent snapshot of operational data, updated frequently for near-real-time access, and notes it can also feed an enterprise data warehouse.
IBM's Redbook on building an ODS defines it as an architectural construct containing subject-oriented, integrated, volatile, current (or near-current) and detailed operational information. Each word matters for what the store can and cannot give you later.
Subject-oriented means it is organized around business subjects such as customer, order, job or ticket, not around one application's screens. Integrated means codes, names and keys from different systems are reconciled into one form. Volatile and current-valued mean a record is updated in place when the source changes, so yesterday's value is usually gone. Detailed means it holds individual records, not monthly summaries.
Typical uses are operational reporting, customer 360 lookups for support or sales, and feeding downstream applications that need one clean copy of current data without querying every source system. Many companies never call it an ODS: a nightly replica of the CRM and billing system, merged into one reporting database, often is one in practice.
Operational data store vs data warehouse: what is the difference?#
The core difference is time. An ODS keeps the current value of each record and overwrites it; a data warehouse keeps history and does not change data once loaded. Oracle's data warehousing guide introduces the warehouse through Inmon's four characteristics, subject oriented, integrated, nonvolatile and time variant, and explains that data should not change once entered because the purpose of a warehouse is to analyze what has occurred.
IBM draws the same line from the other side: the enterprise data warehouse is the central repository of historical data, while the ODS holds recent operational data for day-to-day decisions. Both are integrated and subject-oriented, which is why the terms get confused. The table below sets out the differences that matter in practice.
| Dimension | Operational data store | Data warehouse | Source system (OLTP app) |
|---|---|---|---|
| Main job | Current, integrated view for operations | Historical analysis and reporting | Run the business transaction by transaction |
| Time horizon | Current or near-current values | Years of history, time-stamped | Current state plus whatever the app retains |
| Update behavior | Volatile: records updated in place | Nonvolatile: loaded, then not changed | Records edited, merged and deleted by users |
| Data model | Integrated, detailed, normalized or lightly modeled | Integrated, often dimensional (facts and dimensions) | Application-specific, normalized |
| Refresh | Frequent: near real time to nightly | Batch or micro-batch loads | Real time, as users work |
| Typical question | What is this customer's status now? | How did order volume change by quarter? | Create or update this order |
- 01Your work records
- 02Personal details removed
- 03You approve the release
- 04Licensed to AI developers
How is an ODS different from a data lake and from the source system?#
A data lake stores raw data in its native format and applies structure only when it is read, while an ODS stores integrated, cleaned records in a defined schema. Microsoft's architecture guidance describes the lake as schema-on-read and contrasts it with a warehouse that enforces structure as it ingests data, schema-on-write. An ODS sits on the schema-on-write side, but without the warehouse's history.
The source system is the application where work happens: the CRM, help desk, ERP or field service platform. Microsoft describes these online transaction processing (OLTP) systems as recording business interactions as they occur, and notes the common practice of keeping a relevant window of time in the OLTP system and offloading historical data to a data mart or warehouse. That practice is why the source application often holds less history than people expect.
So the four stores line up roughly like this: the source system is the original record and the current truth for its own data; the ODS is an integrated copy of current truth across systems; the warehouse is integrated history; the lake is raw history in many formats, as complete as whatever was landed and kept.
Where does history actually survive in each system?#
History survives only where something was built to keep it, and most mechanisms have a retention window that is shorter than people assume. Before choosing an export source, check which of these mechanisms you actually have switched on and what their limits are.
- Time travel and CDC windows exist for recovery and replication. Treat them as a way to start capturing history now, not as evidence that history already exists.
- An ODS that is refreshed by full reload or upsert keeps none of these unless someone added a history table or snapshot job.
| Mechanism | What it keeps | Documented default or limit | What it means for an export |
|---|---|---|---|
| Warehouse SCD Type 1 (overwrite) | Only the latest attribute value | Old value is replaced; no history kept for that attribute | Exports show today's status on every past record |
| Warehouse SCD Type 2 (add new row) | One row per version with effective and expiration dates | Kimball recommends effective date, expiration date and current-row indicator columns | Best source for how records changed over time |
| SQL Server system-versioned temporal table | Prior row versions in a paired history table | History is built automatically when data is modified; retention is configurable | Can reconstruct past states if it was enabled early enough |
| SQL Server change data capture | Insert, update and delete activity in change tables | Cleanup job retains 4,320 minutes (3 days) by default | A feed, not an archive: changes older than the window are gone unless landed elsewhere |
| Delta Lake time travel | Older table versions (needs both log and data files) | Log history 30 days by default; files deleted from the table become VACUUM candidates after 7 days by default | Useful for recent recovery; running VACUUM can shorten it, and it is not a multi-year record |
| Snowflake Time Travel | Changed or deleted data for a period | 1 day by default; up to 90 days for permanent objects on Enterprise Edition | Same: short-term protection, not history for a dataset |
| Salesforce field history tracking | Old and new values of tracked fields | Up to 18 months in the app and 24 months via API without Field Audit Trail | Source-system change history is shallow unless extended |
Which system should you export from for an AI data license?#
Export from the system that holds the longest consistent, event-level history for the records the buyer wants, then use the ODS only for what it is good at: current reference data and cross-system key mapping. In most companies that means the warehouse for history and the source application for fields and free text the warehouse never loaded.
AI developers generally value sequences: how a ticket moved through statuses, how a quote changed before it closed, how a job was rescheduled. A volatile ODS has already collapsed those sequences into a final state. A Type 2 warehouse dimension, an append-only event table or the source application's own activity log keeps them. The comparison below is the asset to use with your data team.
| Factor | Operational data store | Data warehouse | Source system export |
|---|---|---|---|
| History depth | Usually none; current values only | Often years, depending on load start date and SCD design | Varies by app; often trimmed or capped by retention settings |
| Overwrite behavior | Overwritten on each refresh | Append-only facts; dimensions Type 1 or Type 2 by attribute | Users edit, merge and delete records directly |
| Key stability | Integrated keys across systems, but reassigned on reloads in some builds | Surrogate keys stable within the warehouse; natural keys kept as attributes | Native IDs stable inside the app; merges and migrations can change them |
| Free text and attachments | Often dropped or truncated during integration | Usually excluded from the dimensional model | Most complete: notes, comments, emails, files |
| Cleanliness | Cleaned and standardized | Cleaned, conformed and documented | Raw: duplicates, test records, inconsistent picklists |
| De-identification effort | Lower: fewer fields, standardized codes | Moderate: wide history tables, many joins to check | Highest: free text and attachments hold most personal and confidential details |
| Best use in a license export | Current reference data and key crosswalk | Event history, outcomes and time series | Text, attachments and fields the warehouse never loaded |
Decision checklist: choosing the export source#
Work through these questions with whoever owns the pipelines. If you cannot answer one, that gap is usually the first thing a buyer's technical review will ask about.
- Name the records the dataset is about (tickets, jobs, orders, claims) and the time span the buyer wants.
- For each candidate store, write down the earliest date it holds complete records for that entity, and confirm it by querying, not from memory.
- Check whether each store overwrites or appends: look for effective-date columns, history tables, change tables or snapshot partitions.
- List the attributes that are Type 1 in the warehouse; those columns will show current values on historical rows and must be flagged in the data dictionary.
- Confirm which free-text fields and attachments exist only in the source application, and whether the buyer needs them.
- Test key stability: pick 50 records and trace their IDs across source, ODS and warehouse, including merged or migrated records.
- Check retention settings that may delete history soon (CDC cleanup, time travel windows, app-level history limits) and land the data before they run.
- Estimate de-identification effort per store: count free-text columns, attachment volume and direct identifiers in each candidate extract.
- Choose one primary source per table in the dataset and document why, so the extract can be reproduced if the buyer asks for a refresh.
Worked example: a support organization with an ODS and a warehouse#
This is an illustrative example, not a client case. Picture a company with Zendesk for support, Salesforce for accounts and NetSuite for billing. A nightly job merges all three into an ODS so agents see one customer record. A separate warehouse loads tickets and ticket status events since 2021, with customer tier tracked as Type 2 and region as Type 1.
A buyer wants three years of support conversations with outcomes. The ODS shows every ticket in its final status with the customer's current tier, which would mislabel tickets from when the customer was on a different plan. The warehouse has the status sequence and the tier at the time of each ticket, but no message bodies. Zendesk has the full conversations, but the company merged duplicate users in 2023, so some requester IDs changed.
The defensible export uses the warehouse for ticket events and tier-at-time, Zendesk for message text joined on ticket ID (stable through user merges), and the ODS only as a crosswalk between Zendesk organizations and Salesforce accounts. Region is flagged in the data dictionary as current-value only. De-identification work concentrates on the Zendesk text, where names, emails and account details appear.
What changes when the export is for licensing rather than analytics?#
Three things change: the history you can prove, the rights you can grant and the identifiers you must remove. Licensing needs four separate permissions: access to the data, privacy permission to use it, ownership, and contract rights. Technical access to a warehouse is only the first, and de-identification alone does not create a right to license. Counsel should review the rest for the specific dataset.
On the technical side, buyers look for years of consistent history, typically two or more, and tens of thousands of records or more in one system, assessed deal by deal. That threshold is easier to meet from a warehouse or an append-only log than from an ODS that only knows the present.
SourceX runs sourcing and qualification, rights review, contracting, data preparation, delivery and payment for businesses licensing operational data to AI developers. De-identification is performed by SourceX directly or through its processing partner, and the company approves exactly what is released, once per deal, before anything is shared. The first step is a short company assessment; no data uploads are needed to see if a company qualifies.
Frequently asked questions
What is an operational data store in simple terms?
It is a database that combines current records from several business systems into one clean, consistent copy that is refreshed often. It answers questions about the present, such as a customer's current status, rather than how things changed over time. Records are usually overwritten as the sources change.
Is an operational data store a data warehouse?
IBM lists the ODS as one of three types of data warehouse, alongside the enterprise data warehouse and the data mart, but they differ on time. In Inmon's terms a warehouse is nonvolatile and time-variant, keeping history, while IBM's Redbook describes an ODS as volatile and current. Many architectures feed the warehouse from the ODS or alongside it.
What is the difference between an ODS and a data lake?
A data lake stores raw data in its native format and applies structure when the data is read. An ODS stores integrated, standardized records in a defined schema and keeps only current values. A lake can hold long raw history if it was kept; an ODS normally cannot.
What are examples of an operational data store?
A nightly merge of CRM, billing and support data into one reporting database is a common example, as is a customer master hub that downstream apps read from. Real-time replicas that integrate several transactional systems for operational dashboards also fit the definition. The test is whether it integrates current, detailed data from multiple sources.
Can I license data straight from an ODS?
You can, but it is often the weakest export source because it has overwritten the history AI developers usually want. It is useful for current reference data and for mapping keys across systems. Whether you may license the data at all depends on contracts and privacy terms, not on which system holds it.
Sources
- IBM lists the ODS as a type of data warehouse that holds the most recent snapshot of operational data, is updated frequently and can feed an enterprise data warehouse, which holds historical data. Source
- IBM's Redbook on building an ODS defines it as subject oriented, integrated, volatile, current (or near-current) and detailed operational information. Source
- Oracle introduces the data warehouse through William Inmon's characteristics (subject oriented, integrated, nonvolatile, time variant) and says data should not change once entered. Source
- Microsoft describes OLTP systems as recording business interactions as they occur and notes that history is commonly offloaded to a data mart or warehouse. Source
- Microsoft describes a data lake as raw, native-format storage using schema-on-read, versus a warehouse's schema-on-write. Source
- SQL Server change data capture records insert, update and delete activity, and its cleanup job retains change entries for 4,320 minutes (3 days) by default. Source
- System-versioned temporal tables build version history automatically when data is modified, with an assigned history table. Source
- Delta Lake keeps table history for 30 days by default (delta.logRetentionDuration) and, to time travel, both log and data files must be retained; deleted files become VACUUM candidates after 7 days by default (delta.deletedFileRetentionDuration). Source
- Snowflake Time Travel retention defaults to 1 day and can be set up to 90 days for permanent objects on Enterprise Edition. Source
- Salesforce retains field history for up to 18 months, and up to 24 months via the API, when Field Audit Trail is not enabled. Source
- Kimball Type 1 overwrites the old attribute value so the dimension shows only the current value. Source
- Kimball Type 2 adds a new row per change, with row effective date, expiration date and current row indicator columns. Source
Related resources
See if your company qualifies
A short company assessment. No data uploads are needed.