TutorialKart.comWhat is Data Acquisition in Data Warehousing?
Data Acquisition is the process of collecting data from source systems, preparing it for business use, and loading it into a target system such as a data warehouse, data mart, lakehouse, or reporting database. In a data warehousing context, data acquisition usually includes data extraction, data transformation, and data loading.
The word data acquisition is also used in engineering and measurement systems, where sensors collect physical measurements such as temperature, voltage, pressure, or vibration. In data warehousing, however, the focus is on acquiring business data from applications, databases, files, APIs, legacy systems, and operational platforms so that it can be stored, analyzed, and reported consistently.
A simple data acquisition flow is:
- Identify the source systems that contain useful business data.
- Extract the required fields, rows, files, or events from those sources.
- Validate, clean, standardize, and transform the data.
- Load the prepared data into the target warehouse or analytical store.
- Monitor the load for errors, duplicates, missing values, and late-arriving records.
Data Acquisition, ETL, and ELT: How They Are Related
Data acquisition is broader than only moving data. It covers the complete intake process, including source identification, connectivity, scheduling, data quality checks, security, transformation rules, and load monitoring. In many data warehouse projects, data acquisition is implemented by ETL (Extract, Transform, Load) or ELT pipelines.
In ETL, data is extracted from source systems, transformed in a processing layer, and then loaded into the warehouse. In ELT, data is extracted and loaded first, and the major transformation work happens inside the target warehouse or lakehouse. Both approaches are used for data acquisition depending on architecture, data volume, latency needs, and the tools used by the organization.
| Process | Where it fits in data acquisition | Typical use |
|---|---|---|
| ETL | Extract, transform, then load | Structured warehouse loads with strong cleansing before storage |
| ELT | Extract, load, then transform | Cloud warehouses, large volumes, and flexible transformation after landing |
| CDC | Capture changed records from source systems | Incremental loads and near real-time reporting |
| Streaming acquisition | Collect event data continuously | IoT, logs, clickstream, monitoring, and real-time dashboards |
Types of ETL Tools Used for Data Acquisition
Data Acquisition can be performed by two types of ETL (Extract, Transform, Load) types. They are
- Code based ETL : ETL applications which can be developed using programming languages like SQL, PLSL etc are code based ETL.
- Example : – BASE SAS, SAS ACCESS etc.
- GUI based ETL : ETL applications which are developed using graphical user interface, point and click techniques are known as GUI based ETL applications.
- Example :- Data stage, AB Initio, Informatica, ODI (Oracle Data Integration), data services, data manager, SSIS (SQL Server Integration Services).
Modern data acquisition can also use orchestration tools, cloud integration services, API connectors, message queues, and data streaming platforms. The choice depends on the source type, expected data volume, transformation complexity, cost, skill set, and required refresh frequency.
What is Data Extraction in Data Acquisition?
Data Extraction is the process of reading the data from various types of source systems. The following are the different types of source used to define data extraction.
- ERP Sources.
- SAP.
- ORACLE applications.
- JD Edwards.
- People Soft.
- File Sources.
- XML Files.
- Flat files.
- Relational Sources.
- Oracle.
- SQL server.
- DB2.
- Sybase.
- Legacy sources.
- Main frames.
- As 400.
- Cobal files.
Data extraction may be performed as a full extract, incremental extract, change data capture extract, API pull, file pickup, log-based capture, or streaming event intake. A good extraction design should answer these questions before development starts: which fields are required, how often the data must be refreshed, how deleted records are detected, how source changes are tracked, and how extraction errors are handled.
Common Data Acquisition Source Systems
Business data usually comes from multiple operational systems. Each source has its own structure, update frequency, data quality issues, and access method.
| Source category | Examples | Common acquisition method |
|---|---|---|
| Business applications | ERP, CRM, HRMS, billing, finance applications | Database extract, API connector, application export |
| Relational databases | Oracle, SQL Server, DB2, MySQL, PostgreSQL | SQL queries, replication, CDC, scheduled batch extract |
| Files | CSV, XML, JSON, Excel, fixed-width files | File ingestion, parsing, validation, staging |
| Legacy systems | Mainframe, AS/400, COBOL files | Flat-file extract, batch interface, specialized connector |
| APIs and SaaS platforms | Marketing, payments, support, analytics tools | REST API, pagination, webhooks, scheduled pulls |
| Logs and events | Application logs, clickstream, IoT events | Streaming ingestion, message queues, event hubs |
What is Data Transformation in Data Acquisition?
Data transformation is the process of transforming data and cleansing the data into the required business format. In this Data transformation stage, the following are the activities that take place.
- Data Cleansing.
- Data Scrubing.
- Data Aggregation.
- Data Merging.
Transformation rules may include changing data types, standardizing dates, converting currencies, mapping codes to business descriptions, deriving new columns, validating reference data, handling missing values, and applying business rules. The objective is not only to move data but to make it usable, consistent, and trustworthy for reporting and analysis.
What is Data Cleansing During Data Acquisition?
Data cleansing is the process of filtering the rows that contain null values, removes duplicate rows and removing blank (or) white spaces, it is also known as Data “Santization”.
In practical data warehouse work, data cleansing may also include correcting invalid codes, standardizing case and formats, removing non-printable characters, validating email or phone formats, and checking mandatory fields. Cleansing rules should be documented clearly because they directly affect reports and downstream analytics.
What is Data Scrubbing in Data Acquisition?
Data scrubbing is the process of diverting new attributes or columns which are not available in source system.
More generally, data scrubbing means detecting and fixing incorrect, inconsistent, incomplete, or corrupted data. For example, a customer record may have an invalid postal code, a blank country field, or a duplicate customer identifier. Scrubbing rules help improve data quality before the data is used for dashboards, business intelligence, machine learning, or regulatory reports.
What is Data Aggregation in Data Acquisition?
Data aggregation is the process of calculation the summaries from detailed data.
Aggregation is useful when reports need summary-level data instead of every transaction. For example, daily sales can be aggregated by store, product, region, and date. Aggregation can improve report performance, but the original detail should be retained when users need drill-down analysis or audit support.
What is Data Merging in Data Acquisition?
Data merging is the process of Integrating the data from multiple OLTP source systems. Data merging operation can can be done in two ways :
Horizontal merging
It is the process of merging the records horizontally using Joins. Where Joints combines the fields from multiple data sources based on one or more fields.
Vertical merging
It is the process of merging the records vertically when the two sources are having same metadata (Union). Union combines similar source definitions into a single result set.
Horizontal merging is common when one source contains customer details and another source contains customer transactions. Vertical merging is common when the same kind of data arrives from multiple regions, departments, or branches with the same structure. In both cases, matching keys, duplicate handling, and conflict resolution rules must be defined carefully.
What is Data Loading in Data Acquisition?
Data loading is the process of inserting the data into a target systems. Data loading can be done in two types.
Initial load or full Load
Initial load is the process of inserting source data rows into empty target table. At initial load, all the source data gets loaded into target.
Incremental load or Delta load
Incremental load is the process of inserting only new records after Initial load happens.
Incremental loading may also update changed records and mark deleted records, depending on the business requirement. For example, a customer address may change, an order status may be updated, or a product may be discontinued. A reliable loading process should include restart logic, duplicate prevention, audit columns, row counts, and error logging.
Four Common Data Acquisition Methods
The phrase data acquisition methods can mean different things depending on the field. In data warehousing, the following four methods are commonly used to acquire data from source systems:
- Manual or file-based acquisition: Users or systems provide CSV, Excel, XML, JSON, or fixed-width files for scheduled loading.
- Database acquisition: Data is extracted from relational databases using SQL queries, replication, stored procedures, or change data capture.
- API-based acquisition: Data is collected from SaaS applications, web services, or cloud platforms through APIs and connectors.
- Streaming or event-based acquisition: Data is captured continuously from logs, sensors, applications, queues, or event platforms.
In scientific and engineering data acquisition systems, the methods may be described differently because the sources are sensors and measurement devices. For business intelligence and data warehousing, the method is usually selected based on data freshness, reliability, cost, and source system limitations.
Batch, Real-Time, and Near Real-Time Data Acquisition
Data acquisition pipelines are often classified by how quickly data moves from the source to the target system.
| Acquisition pattern | Meaning | Example use case |
|---|---|---|
| Batch acquisition | Data is collected and loaded at fixed intervals such as hourly, daily, or weekly. | Daily sales reports, month-end finance reports, HR reporting |
| Near real-time acquisition | Data is moved with a short delay, often using CDC or frequent micro-batches. | Inventory updates, customer support dashboards, operational monitoring |
| Real-time acquisition | Data is captured and processed continuously as events occur. | Fraud detection, application monitoring, IoT alerts, live clickstream analytics |
Not every system needs real-time acquisition. A daily batch load may be enough for many reports. Real-time acquisition is useful when decisions must be made immediately and when the organization can support the additional cost, design complexity, and monitoring effort.
Data Acquisition Example for a Sales Data Warehouse
Consider a sales data warehouse that receives information from a billing system, customer relationship management system, and product master database. The acquisition process may work like this:
- Extract invoice transactions from the billing database every night.
- Extract customer details from the CRM system through an API.
- Extract product category and price details from the product master database.
- Clean missing customer codes, invalid dates, and duplicate invoice rows.
- Merge invoice rows with customer and product details.
- Aggregate daily sales by product, customer segment, region, and date.
- Load the prepared data into warehouse fact and dimension tables.
- Store audit details such as load date, source file name, row count, and rejected row count.
This example shows why data acquisition must be designed as a controlled process. If customer IDs do not match, product codes are inconsistent, or invoice rows are duplicated, the final sales dashboard will show incorrect results.
Data Acquisition Quality Checks Before Warehouse Loading
A data acquisition process should include validation checks before and after loading. These checks reduce reporting errors and make failures easier to trace.
- Source connectivity: Confirm that databases, APIs, files, and credentials are available before extraction starts.
- Schema validation: Check whether expected columns, data types, file layouts, and mandatory fields are present.
- Row count reconciliation: Compare source count, extracted count, rejected count, and loaded count.
- Duplicate detection: Check business keys and technical keys to prevent repeated records.
- Null and format checks: Validate mandatory fields, date formats, numeric fields, codes, and reference values.
- Transformation rule review: Verify joins, aggregations, derived columns, and mapping tables.
- Error logging: Capture rejected rows, failure reasons, source details, and load timestamps.
- Restart and recovery: Ensure failed loads can be rerun without corrupting the target data.
Data Acquisition Challenges in Data Warehousing
Data acquisition becomes difficult when source systems are inconsistent or when business rules are not clearly defined. Common challenges include:
- Poor source data quality: Missing values, invalid codes, duplicate rows, and inconsistent naming can affect reports.
- Changing source schemas: A renamed column or changed file layout can break an acquisition pipeline.
- Different refresh needs: Finance data may load daily, while operational dashboards may need near real-time updates.
- Data volume growth: A process that works for thousands of rows may fail or slow down when volumes increase to millions of rows.
- Security and privacy controls: Sensitive data must be protected during extraction, movement, staging, and loading.
- Late-arriving data: Records may arrive after the scheduled load, requiring adjustment or reprocessing logic.
Best Practices for Reliable Data Acquisition
A reliable data acquisition process should be repeatable, traceable, and easy to monitor. The following practices are useful in most data warehouse projects:
- Document source tables, files, APIs, fields, keys, filters, and extraction frequency.
- Use staging tables or landing zones before loading final warehouse tables.
- Keep raw data when audit, replay, or troubleshooting may be required.
- Define clear business rules for cleansing, scrubbing, merging, and aggregation.
- Use incremental loading where possible to reduce processing time.
- Maintain audit columns such as source system, load batch ID, created date, and updated date.
- Set alerts for failed jobs, missing files, schema changes, and abnormal row counts.
- Test acquisition pipelines with normal data, invalid data, duplicate data, and empty files.
Difference Between Data Acquisition and Data Ingestion
Data ingestion usually refers to bringing data into a system. Data acquisition is often used as a wider term that includes identifying source data, extracting it, validating it, transforming it, loading it, and monitoring its quality. In many practical projects, the terms overlap, but data acquisition places more emphasis on the full process of obtaining usable data for business needs.
| Term | Main focus | Example |
|---|---|---|
| Data ingestion | Bringing data into a platform | Loading API data into a cloud storage bucket |
| Data acquisition | Obtaining, preparing, validating, and loading usable data | Extracting CRM data, cleansing it, merging it with sales data, and loading it into a warehouse |
| Data integration | Combining data from multiple systems into a unified view | Creating a single customer view from CRM, billing, and support systems |
Data Acquisition FAQ
What is data acquisition in simple words?
Data acquisition is the process of collecting data from source systems and preparing it for use in another system. In data warehousing, it usually includes extracting data, transforming it into the required format, and loading it into a warehouse or reporting database.
What are the main stages of data acquisition?
The main stages of data acquisition are source identification, data extraction, data validation, data transformation, data loading, and monitoring. In traditional ETL terminology, these stages are commonly summarized as extraction, transformation, and loading.
What are the four methods of data acquisition in data warehousing?
Four common methods are file-based acquisition, database acquisition, API-based acquisition, and streaming or event-based acquisition. The best method depends on the source system, refresh frequency, data volume, and reporting requirements.
How is data acquisition different from ETL?
ETL is one way to implement data acquisition. Data acquisition is the broader process of obtaining usable data from source systems. ETL specifically describes extracting data, transforming it, and loading it into a target system.
Why is data quality important in data acquisition?
Data quality is important because errors in extracted or transformed data can lead to incorrect reports, wrong business decisions, duplicate records, and failed loads. Cleansing, validation, reconciliation, and error logging help keep acquired data reliable.