Data extraction, integration and modeling

On this page

Data meaning, grain and completeness

An online shop's daily file contains order 701 twice at $80. The warehouse contains order 701 once at $80. The file has no column description.

What must be resolved before a margin report uses the data?

A source row is not useful just because it can be copied. Duplicate rows, missing field meaning, time period, and source grain must be understood before records are combined.

Data extraction, integration, query review, and process modeling support reliable information. Extraction gets data out of systems. Integration makes data from unlike systems agree. Query review checks whether SQL answers its stated purpose. Process modeling chooses a diagram that shows data, control, or participants.

This work appears in APIs, files, databases, logs, ETL and ELT jobs, warehouses, reports, control reviews, and process design. Different systems use different formats, identifiers, dates, status labels, and update times. Copied data can still be incomplete or misleading. Extraction asks how source rows are captured. Integration asks how sources are matched and made consistent. SQL review asks whether a query is relevant and complete. Process modeling asks what kind of business behavior a diagram needs to show.

Extraction, integration, queries and processes

The four lenses ask different questions about the same information environment. Their core questions, terms, and evidence fit into one grid.

Getting rows outMaking sources agreeChecking a queryShowing business behavior
Core question: How do the complete, understood rows leave the source?Core question: How do records from different sources become comparable?Core question: Does the SQL return the intended population, columns, and grain?Core question: Does the diagram show data movement, control sequence, or participant behavior?
Typical terms: full, incremental, batch, streaming, API, file, database, change data captureTypical terms: matching, crosswalk, transformation, deduplication, ETL, ELT, lineageTypical terms: SELECT, JOIN, WHERE, GROUP BY, keys, grain, date boundary, statusTypical terms: flowchart, data flow diagram, event, activity, gateway, pool, lane, message flow
Useful evidence: source metadata, extraction log, completeness checkUseful evidence: mapping rules, reconciliation, lineage, approved flowUseful evidence: purpose statement, query text, expected columns and result grainUseful evidence: symbols and connections that match the model's purpose

The columns ask different questions about the same information environment. A file may need extraction controls, while a customer record may need integration work. A query may need purpose and boundary checks, while a process may need a model showing participants and decisions. The lenses can be used together, but they are not stages of one fixed sequence.

Getting complete source data

Getting complete, understood source data

Extraction is reading or copying data from a source without yet making it agree with another source. Full extraction copies the source population. Incremental extraction copies new or changed rows using a watermark or change data capture.

Scheduled batch movement runs at set times. Streaming or event capture moves data as events occur. Common interfaces include files, APIs, database queries, and logs.

The choice depends on several factors:

  • The source's capabilities.
  • The data volume.
  • The required latency.
  • The load placed on the source.
  • Restart behavior.
  • Security requirements.
  • The need to prove completeness.

NIST Cybersecurity Framework 2.0 ID.AM-07 states: “Inventories of data and corresponding metadata are maintained.” NIST SP 800-53 Rev. 5 SI-10 states: “Check the validity of the information inputs.”

Metadata should identify the field meaning, type, time basis, null handling, key, and grain. The detailed treatment is in Extracting and integrating data.

Making sources agree

Integration combines records while preserving their business meaning. ETL transforms data before loading it. ELT loads raw data before transforming it in the target.

Integration may require several actions:

  • Match records through a shared key or crosswalk.
  • Standardize names, types, units, time zones, and status labels.
  • Remove duplicates.
  • Reconcile counts.
  • Retain source lineage.

Common patterns include point-to-point, hub-and-spoke, shared warehouse or lakehouse, and API or event-based integration.

NIST SP 800-53 Rev. 5 AC-4 states: “Enforce approved authorizations for controlling the flow of information within the system and between interconnected systems.” AU-11 states: “Retain audit records for [organization-defined time period] to provide support for after-the-fact investigations of security incidents and to meet regulatory and organizational information retention requirements.” Retention is organization-defined here. No universal number applies.

CRM identifies one customer as 17. Billing identifies the same person as C017. CRM uses the name Mira Chen. Billing uses Mira C.

What must happen before sales are combined?

Answer. The records need matching, a crosswalk, and name standardization. These are integration work, not extraction. The detailed treatment is in Extracting and integrating data.

Checking SQL against purpose

A query can run successfully and still be irrelevant or incomplete. Review its purpose, output columns, result grain, source tables, join keys and cardinality, filters, status, date field, date boundary, and grouping.

The sample rows provide a population for testing the query.

  • O100 is customer C10, ordered 2025-01-31, shipped 2025-02-02, status SHIPPED, with gross sales of $100.
  • O101 is customer C10, ordered 2025-03-01, shipped 2025-03-04, status SHIPPED, with gross sales of $150.
  • O102 is customer C20, ordered 2025-03-31, shipped 2025-04-01, status SHIPPED, with gross sales of $200.
  • O103 is customer C20, ordered 2025-03-15, shipped 2025-03-20, status CANCELLED, with gross sales of $90.
  • O104 is customer C30, ordered 2025-03-31, shipped 2025-03-31, status SHIPPED, with gross sales of $50.

Customer C10 is Alpha. Customer C20 is Beta. Customer C30 is Gamma.

For the purpose “March shipped sales by customer,” use this query:

SELECT o.customer_id, c.customer_name, SUM(o.gross_sales) AS shipped_sales
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE o.ship_date >= DATE '2025-03-01'
  AND o.ship_date < DATE '2025-04-01'
  AND o.status = 'SHIPPED'
GROUP BY o.customer_id, c.customer_name;

The expected output rows are C10, Alpha, $150 and C30, Gamma, $50. O100 is outside the shipping period. O102 ships in April. O103 is cancelled.

For “January gross sales by order date,” use 2025-01-01 inclusive through 2025-02-01 exclusive. O100 belongs to that order-date population, even though it shipped in February.

Joining on order_id to customer_id is a wrong-key error. Omitting the status filter includes O103.

A third test changes only the date field. Replace ship_date with order_date in the March shipped query.

Does the query still fit the purpose?

Answer. No. O102 would be included and would add C20, $200, even though it shipped in April. The detailed treatment is in Reviewing SQL queries.

Showing the right process behavior

Each model has a different primary object. A flowchart uses conventional symbols and connections to show control sequence, tasks, and decisions.

A data flow diagram uses external entities, processes, data stores, and data flows. It shows where information comes from, where it goes, and where it is held. It does not by itself establish the order of control actions.

BPMN uses events, activities, gateways, pools, lanes, sequence flows, and message flows. It shows business behavior, participant responsibility, events, decisions, and handoffs.

For order approval, the models emphasize different facts:

  • A flowchart emphasizes the approval sequence.
  • A DFD emphasizes order data moving among the customer, order system, and credit file.
  • BPMN emphasizes the customer, sales, credit, and warehouse participants and their activities.

The detailed treatment is in Business process models and diagrams.

Classifying the primary data question

Ask: What is the primary thing being decided or shown?

If the situation concerns collecting complete rows from one source, it belongs to getting rows out. If it concerns matching, transforming, or reconciling records from different sources, it belongs to making sources agree.

If it concerns whether SELECT, JOIN, WHERE, GROUP BY, dates, status, keys, or grain fit a stated purpose, it belongs to checking a query. If it concerns tasks, decisions, data movement, events, or participant responsibility, it belongs to showing business behavior.

The clue words identify the center of the problem, not every tool mentioned in the situation.

A situation about...Belongs toClue words
Getting a complete population from one file, API, database, or logGetting rows outfull, incremental, batch, streaming, pagination, watermark, change data capture, schema, completeness
Combining records from different systems or making their meanings consistentMaking sources agreematching, crosswalk, mapping, transformation, deduplication, reconciliation, ETL, ELT, lineage
Deciding whether a SQL statement answers its stated reporting questionChecking a querypurpose, SELECT, JOIN, key, grain, WHERE, date boundary, status, GROUP BY, output
Representing tasks, decisions, data stores, events, participants, or handoffsShowing business behaviorflowchart, DFD, BPMN, activity, gateway, pool, lane, sequence flow, message flow

Each method has its own purpose

The matrix uses identical dimensions for every lens. This makes each method answerable against its own purpose, evidence, rules, failures, and output.

DimensionGetting rows outMaking sources agreeChecking a queryShowing business behavior
PurposeCapture complete, correctly understood data from a sourceCombine unlike records without losing meaningDecide whether a query is relevant and complete for its purposeChoose a representation that matches data movement, control sequence, or participant behavior
Main objectSource rows, metadata, extraction interface, timing, and completenessKeys, crosswalks, mappings, transformations, duplicates, lineage, and reconciliationQuery text, source tables, joins, filters, dates, status, grouping, output columns, and grainSymbols, activities, events, decisions, data stores, participants, sequence flows, and message flows
Good evidenceSource definition, schema, extraction log, page count, watermark, and validation resultMapping rules, common definitions, reconciliation result, lineage, approved flow, and retention ruleStated purpose, correct join keys, correct population, expected grain, date boundary, status filter, and outputModel symbols and connections that match the question being shown
Relevant rule or standardNIST CSF 2.0 ID.AM-07 and NIST SP 800-53 SI-10NIST SP 800-53 AC-4 and AU-11ISO/IEC 9075-2 SQL/Foundation semantics, applied against the business purposeISO/IEC 5807 flowchart conventions and OMG BPMN 2.0.2
Common failureMissed page, partial period, misunderstood field, wrong grain, or invalid inputWrong crosswalk, duplicate, inconsistent unit or status, lost lineage, or unapproved flowWrong join, wrong date field or boundary, missing status, wrong output, or wrong grainTreating data movement as control sequence, or losing participant responsibility and handoffs
OutputValidated source dataset with metadata and extraction evidenceConsistent dataset with mappings, lineage, reconciliation, and approved retentionQuery and result whose columns, population, and grain match the purposeA flowchart, DFD, or BPMN model that answers the stated process question

Identical dimensions prevent a method from being judged by the wrong standard. An extraction is not judged by whether it draws a gateway. A SQL query is not complete merely because its source data was integrated.

Matching situations to data-work questions

Each situation centers on one primary question. The answer depends on that question and the evidence needed to support it.

Practice

An inventory team must take a nightly copy from a vendor API. The response contains 1,200 SKUs, has a nullable active field, and uses a next_token for additional pages. The team needs the complete population as of 2025-03-31. Which part is the main issue, and why?

CRM identifies one customer as 17 and billing identifies the same person as C017. CRM uses the name Mira Chen and billing uses Mira C. Sales must combine the two systems' records. Which part is the main issue, and why?

An analyst's stated purpose is March shipped sales by customer. The query has the correct customer join and status filter but uses o.ship_date >= DATE '2025-03-01' AND o.ship_date <= DATE '2025-03-31'; ship_date stores date and time. Which part is the main issue, and why?

An operations manager wants a diagram of order approval involving a customer, sales clerk, credit service, and warehouse. It must show tasks, the decision to approve or reject, and handoffs between participants. No data totals or SQL is under review. Which part is the main issue, and why?

Four questions guide data work

Use these four working rules when several tools or systems appear in one situation.

  • Getting rows out: Choose full or incremental, batch or streaming, and the source interface based on completeness, meaning, timing, source load, and validation.
  • Making sources agree: Match keys, transform meanings, remove duplicates, reconcile results, preserve lineage, and use approved flow and organization-defined retention.
  • Checking a query: Test purpose, grain, keys, joins, filters, dates, status, grouping, and output.
  • Showing business behavior: Use a flowchart for control sequence, a DFD for data movement, and BPMN for events, participants, activities, decisions, and handoffs.

Separating question: Is the situation mainly about collecting rows, making sources agree, judging a query, or showing business behavior?

Unlock the full lesson and continue reading

Signup to continue reading this lesson and unlimited access questions, flashcards, AI notes, and more

Scan to download app

Scan to download
UNLOCK FREE ACCESS

Practice Questions: Data extraction, integration and modeling

  1. 1. A CPA in internal audit is reviewing a new first-quarter sales data mart before management uses it for financial analysis and monitoring controls. The CPA must determine whether the extraction scope, transformations, customer classifications, and reconciliation procedures comply with the approved requirements, and whether an unauthorized credit-memo override was improperly included in the load. All amounts in the exhibits are in U.S. dollars.

    • 2. A CPA in internal audit is reviewing the first production run of a sales data mart used for the July 15, 20X5 management revenue report. The CPA must determine whether the data model, source selection, transformations, and reconciliation controls conform to the approved requirements. Use Exhibits 1 through 4 to complete the review.

      • 3. A CPA in internal audit is reviewing the first monthly production run of a new revenue data pipeline. The pipeline extracts posted invoice and credit-memo lines from a legacy billing system and loads a revenue fact table used for financial reporting. The CPA must determine whether the extraction, transformation, integration, change-management, and data-lineage controls operated as designed.

        • 4. A CPA on the internal controls team is reviewing the migration of accounts-payable invoice data from two ERP systems into a supplier-spend data warehouse. Management intends to certify the dashboard as complete and accurate. The pipeline extracts invoice-line data, applies vendor crosswalks and validation rules, quarantines unresolved records, and loads accepted records into the warehouse. Using the exhibits, evaluate the extraction, transformation, loading, data-modeling, and release controls.

          • 5. A CPA on the internal controls team is reviewing the January 20X5 revenue data pipeline used for management reporting. The dashboard must report all posted January sales invoices and credit memos at net document amount and assign customer segment based on the segment effective on each document's accounting date. The CPA must determine the effect of the current extraction and transformation design and recommend a controlled remediation.

            • 6. A newly licensed CPA is validating the December 20X5 consolidated-revenue data pipeline for a U.S. parent. The pipeline combines U.S. dollar transactions with euro-denominated transactions from a European subsidiary. Management uses the target table for its monthly financial reporting analytics. The CPA must determine whether the pipeline followed the approved transformation specification, calculate the corrected target amount, and document the related processing and change-management issues.

              • 7. A CPA on an internal controls team is reviewing the first year-end load from a procurement system into an inventory-cost mart. Management intends to use the mart for financial reporting. The CPA must determine whether the extraction and transformation preserved a complete and accurate population and document the required remediation.

                • 8. A CPA on an internal controls team is reviewing the February 3, 20X5 migration of January sales data from a legacy billing system to a new cloud ERP. The migration is not yet approved for financial-reporting use. Management requires the target data to include all invoices posted during January, including January invoices corrected through the February 3 cutoff. Use the exhibits to identify extraction and transformation defects, calculate corrected totals, and determine the appropriate migration conclusion.

                  Flashcards: Data extraction, integration and modeling

                  1. 1. Define a query in the context of reporting.

                    Show answer

                    A query is a user-initiated ad hoc report.

                  Everything you need for CPA prep

                  Get full Oncourse access with lessons, practice questions, flashcards and AI study tools.

                  GET STARTED FOR FREE
                  75% OFFLimited time offer
                  GET 75% OFF