
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.
The four lenses ask different questions about the same information environment. Their core questions, terms, and evidence fit into one grid.
| Getting rows out | Making sources agree | Checking a query | Showing 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 capture | Typical terms: matching, crosswalk, transformation, deduplication, ETL, ELT, lineage | Typical terms: SELECT, JOIN, WHERE, GROUP BY, keys, grain, date boundary, status | Typical terms: flowchart, data flow diagram, event, activity, gateway, pool, lane, message flow |
| Useful evidence: source metadata, extraction log, completeness check | Useful evidence: mapping rules, reconciliation, lineage, approved flow | Useful evidence: purpose statement, query text, expected columns and result grain | Useful 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.
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:
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.
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:
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.
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.
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.
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:
The detailed treatment is in Business process models and diagrams.
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 to | Clue words |
|---|---|---|
| Getting a complete population from one file, API, database, or log | Getting rows out | full, incremental, batch, streaming, pagination, watermark, change data capture, schema, completeness |
| Combining records from different systems or making their meanings consistent | Making sources agree | matching, crosswalk, mapping, transformation, deduplication, reconciliation, ETL, ELT, lineage |
| Deciding whether a SQL statement answers its stated reporting question | Checking a query | purpose, SELECT, JOIN, key, grain, WHERE, date boundary, status, GROUP BY, output |
| Representing tasks, decisions, data stores, events, participants, or handoffs | Showing business behavior | flowchart, DFD, BPMN, activity, gateway, pool, lane, sequence flow, message flow |
The matrix uses identical dimensions for every lens. This makes each method answerable against its own purpose, evidence, rules, failures, and output.
| Dimension | Getting rows out | Making sources agree | Checking a query | Showing business behavior |
|---|---|---|---|---|
| Purpose | Capture complete, correctly understood data from a source | Combine unlike records without losing meaning | Decide whether a query is relevant and complete for its purpose | Choose a representation that matches data movement, control sequence, or participant behavior |
| Main object | Source rows, metadata, extraction interface, timing, and completeness | Keys, crosswalks, mappings, transformations, duplicates, lineage, and reconciliation | Query text, source tables, joins, filters, dates, status, grouping, output columns, and grain | Symbols, activities, events, decisions, data stores, participants, sequence flows, and message flows |
| Good evidence | Source definition, schema, extraction log, page count, watermark, and validation result | Mapping rules, common definitions, reconciliation result, lineage, approved flow, and retention rule | Stated purpose, correct join keys, correct population, expected grain, date boundary, status filter, and output | Model symbols and connections that match the question being shown |
| Relevant rule or standard | NIST CSF 2.0 ID.AM-07 and NIST SP 800-53 SI-10 | NIST SP 800-53 AC-4 and AU-11 | ISO/IEC 9075-2 SQL/Foundation semantics, applied against the business purpose | ISO/IEC 5807 flowchart conventions and OMG BPMN 2.0.2 |
| Common failure | Missed page, partial period, misunderstood field, wrong grain, or invalid input | Wrong crosswalk, duplicate, inconsistent unit or status, lost lineage, or unapproved flow | Wrong join, wrong date field or boundary, missing status, wrong output, or wrong grain | Treating data movement as control sequence, or losing participant responsibility and handoffs |
| Output | Validated source dataset with metadata and extraction evidence | Consistent dataset with mappings, lineage, reconciliation, and approved retention | Query and result whose columns, population, and grain match the purpose | A 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.
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?
Use these four working rules when several tools or systems appear in one situation.
Separating question: Is the situation mainly about collecting rows, making sources agree, judging a query, or showing business behavior?
Signup to continue reading this lesson and unlimited access questions, flashcards, AI notes, and more
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.
1. Define a query in the context of reporting.
A query is a user-initiated ad hoc report.
Get full Oncourse access with lessons, practice questions, flashcards and AI study tools.
Scan to download app
Enter your email to get your 75% OFF code and unlock the full CPA question bank on the app.