In this video, we walk through 5 ISC practice questions on data extraction methods and techniques. These questions are from ISC content area 1 on the AICPA CPA exam blueprints: Information Systems and Data Management.
The best way to use this video is to pause each time we get to a new question in the video, and then make your own attempt at the question before watching us go through it.
Data Extraction Methods and Techniques
Data extraction is the process of retrieving data from where it’s stored so it can be analyzed, reported on, or moved into another system. Data lives in many places: databases, business applications, exported files, documents, websites, and physical devices. The right extraction method depends on where the data lives and what form it’s in.
Structured Systems
Structured systems are databases and business applications that store data in organized tables of rows and columns. There are three main ways to get data out of them.
Database retrieval means querying a database directly, usually with a language called SQL. The query specifies exactly which records and fields to return and can filter by conditions such as amount, date, or account. For example, an auditor could pull every journal entry over $10,000 posted on a weekend straight from the ERP’s database.
ETL stands for extract, transform, load. Data is extracted from multiple source systems, transformed into one consistent format, and loaded into a central location such as a data warehouse. A company might use ETL to combine customer data from its CRM, order data from its e-commerce platform, and financial data from its ERP, even though each system formats dates and IDs differently.
An API (application programming interface) is a connection that lets one system request specific data from another and receive it back in a structured format. APIs replace manual exporting and importing, and outside organizations like banks and government agencies often offer them so others can pull their data directly into their own software.
Files and Documents
File extraction pulls data from files that are already in a data format, such as CSV, JSON, or XML. These files usually come from a system that exported the data, so the person using them doesn’t need access to the original system. A CSV file stores each record as a line of values separated by commas, while JSON and XML attach a label to each value.
Document parsing pulls specific data out of documents written for people to read, such as PDFs, Word files, or reports. In these documents the data is mixed in with surrounding text and layout, so it has to be located and separated out first. Pulling interest rates and maturity dates from a stack of PDF loan agreements is a typical example.
External and Specialized Sources
Web scraping extracts data from websites. A tool visits web pages the way a person browsing would and pulls the needed content off the page automatically. It’s typically used when the data is publicly available but the site doesn’t offer an API. When an API is available, it’s the more direct option.
Sensors and IoT (internet of things) devices automatically collect data from the physical world as it happens, such as fuel levels in storage tanks, energy usage, or equipment performance. This matters in industries where operational data, like inventory quantities, feeds into financial reporting.
Extraction Techniques
Techniques describe how an extraction is carried out, regardless of the source.
Full extraction pulls all of the data every time it runs. It’s simple and guarantees nothing is missed, which makes it the natural choice for a first-time load into a new system, but it’s slow for large data sets.
Incremental extraction pulls only data that is new or changed since the last run, usually identified by timestamps or a change log. It’s faster and lighter on the source system. When this tracking is automated, it’s called change data capture (CDC). A common pattern is one full extraction to start, then incremental extractions for ongoing updates.
Online extraction pulls directly from the live source system, while offline extraction works from a copy such as a backup or an exported file.
Batch extraction moves data at scheduled intervals, such as nightly or weekly, which works well for periodic reporting. Real-time, or streaming, extraction moves data continuously as it’s created, which is needed when the receiving system has to stay current, like updating inventory counts within moments of each sale.
