Data Acquisition, Storage, and SQL

Introduction

Business analytics depends not only on sophisticated models but also on the quality, accessibility, and governance of the data supplied to those models. Data acquisition is the process of obtaining data from internal and external sources; data storage is the organization of that data so it can be accessed reliably; and SQL is the principal language used to retrieve, transform, and summarize data in relational systems.

In professional settings, analysts rarely work with a single clean spreadsheet. They may combine transaction records from an enterprise resource planning system, customer information from a customer relationship management platform, product data from an API, and external economic indicators downloaded from a public website. These sources differ in structure, update frequency, data types, identifiers, quality, security requirements, and legal constraints.

A robust analytics workflow therefore treats data acquisition as an engineering and governance activity rather than a simple download operation. The central objective is to create a trustworthy path from source systems to analytical insight while preserving accuracy, traceability, security, and operational efficiency.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish major data sources and acquisition methods.
2. Read and write structured data using files and programmatic interfaces.
3. Explain relational database concepts, schemas, keys, constraints, and normalization.
4. Construct SQL queries using filtering, projection, sorting, joins, grouping, aggregation, and subqueries.
5. Explain transactions, isolation, consistency, and database reliability.
6. Design secure and maintainable connections to databases and APIs.
7. Identify data-quality, privacy, ethical, and governance risks.
8. Integrate heterogeneous data sources into an analytics-ready workflow.

1. The Data Acquisition Landscape

1.1 Common data sources

Data sources can be classified by origin, structure, ownership, and access method.

A source is not automatically reliable because it is internal, official, or machine-readable. Reliability requires examining how the data is produced, transformed, validated, and maintained.

1.2 Batch and streaming acquisition

Batch acquisition collects data periodically—for example, every night or every hour. It is appropriate when decisions do not require immediate information and is usually simpler to operate. Streaming acquisition processes records continuously or in small time windows. It is appropriate for fraud detection, operational monitoring, and real-time personalization, but introduces additional challenges involving event order, late-arriving records, duplicate messages, and fault recovery.

The choice between batch and streaming should be based on business latency requirements, cost, source capability, processing complexity, and the consequences of stale information. A real-time architecture is not automatically superior if the decision process only occurs once per day.

1.3 Acquisition workflow

A disciplined acquisition workflow generally includes:

1. Source discovery: identify owners, definitions, update frequency, and access conditions.
2. Schema inspection: examine columns, data types, keys, missingness, units, and encoding.
3. Authentication and authorization: obtain approved credentials and least-privilege access.
4. Extraction: retrieve data using files, SQL, APIs, or enterprise connectors.
5. Validation: test row counts, data types, ranges, uniqueness, referential integrity, and freshness.
6. Transformation: standardize names, dates, units, categories, and identifiers.
7. Storage: preserve raw data and publish curated analytical data.
8. Monitoring: record failures, latency, quality metrics, and lineage.

This sequence is iterative. A failed quality test may require returning to source discovery or schema inspection rather than silently correcting the data.

2. Files and Programmatic Acquisition

2.1 Delimited files

CSV files are widely used because they are simple and portable. However, CSV has no universal type system. A value such as 00125 may represent a customer identifier rather than the number 125. Dates may use different conventions, and commas or quotation marks inside text can cause parsing errors.

A reliable import process should explicitly specify:

• character encoding, preferably UTF-8 where supported;
• delimiter and quotation rules;
• column names and expected data types;
• date and time formats, including time zones;
• treatment of missing values;
• rules for duplicate records and malformed rows.

A useful practice is to retain the original file unchanged and create a validated derivative. This preserves evidence of what was received and supports reproducibility.

2.2 JSON and nested data

JSON represents objects, arrays, and nested structures. It is common in APIs. Before analysis, nested records often need to be flattened into relational tables. For example, an order object may contain an order identifier, customer details, and an array of line items. Flattening the line items into a separate table prevents repeated order-level information and supports correct aggregation.

The analyst must distinguish between a missing field, a null value, an empty array, and a field containing an invalid value. These states may have different business meanings.

2.3 Columnar formats

Parquet and similar columnar formats store data by column rather than by row. They are efficient for analytical workloads because queries frequently use only a subset of columns. They also preserve data types more reliably than CSV and often support compression. Their use is particularly suitable for data lakes and large historical datasets.

2.4 APIs and web services

A REST API commonly exposes resources through HTTP methods such as GET, POST, PUT, PATCH, and DELETE. A request may include a URL, query parameters, headers, and a body. A response usually includes a status code, metadata, and data, often in JSON format.

Important API practices include:

• use documented authentication methods such as OAuth 2.0 or signed tokens;
• implement pagination rather than assuming all records arrive in one response;
• respect rate limits and retry only transient failures;
• use timeouts and bounded retries;
• record request time, endpoint, status code, and retrieval version;
• use idempotent extraction logic where possible;
• validate response schemas before loading data;
• protect tokens and never place secrets in source code or notebooks.

HTTP status codes provide useful diagnostic information. A 200-level response generally indicates success, a 400-level response indicates a client or request problem, and a 500-level response indicates a server-side problem. A 429 response commonly indicates rate limiting. Retry behavior should be different for each category.

2.5 Incremental extraction

Extracting an entire source on every run is inefficient and may overload operational systems. Incremental extraction retrieves only records created or modified since the previous successful run. Common mechanisms include:

• a last_modified timestamp;
• an increasing transaction or sequence identifier;
• database change-data capture;
• an API cursor or continuation token;
• a high-water mark stored by the pipeline.

Incremental processes must account for clock differences, updates arriving late, deleted records, and reprocessing after failure. A small overlap window can reduce the risk of missing records, but requires deduplication.

3. Relational Database Foundations

A relational database organizes information into tables consisting of rows and columns. Each table represents a relation or entity set, while each row represents an occurrence of that entity. A schema defines tables, columns, data types, constraints, views, and relationships.

3.1 Keys and relationships

A primary key uniquely identifies each row. A foreign key references a key in another table and represents a relationship. For example, customer_id may be the primary key of Customer and a foreign key in Order.

Relationships are commonly classified as:

• one-to-one: one row in one table corresponds to one row in another;
• one-to-many: one customer can place many orders;
• many-to-many: students can enroll in many courses and each course can contain many students. This is represented through a junction table such as Enrollment.

A foreign-key constraint protects referential integrity by preventing a child row from referencing a nonexistent parent, subject to the database's configured rules.

3.2 Data types and constraints

Data types communicate meaning and support validation. Typical types include integer, decimal, character, date, timestamp, Boolean, and binary data. Monetary values should generally use an exact decimal type rather than floating-point representation when precise financial arithmetic is required.

Important constraints include:

• NOT NULL: a value is required;
• UNIQUE: duplicate values are prohibited;
• PRIMARY KEY: identifies rows uniquely and is non-null;
• FOREIGN KEY: enforces a relationship;
• CHECK: limits values according to a condition;
• DEFAULT: supplies a value when none is provided.

Constraints should be applied at the database boundary where possible, because validation performed only in an application can be bypassed by another application or user.

3.3 Normalization and analytical design

Normalization reduces redundancy and update anomalies. In simplified form:

• First normal form: fields contain atomic values and repeating groups are separated.
• Second normal form: non-key attributes depend on the whole primary key.
• Third normal form: non-key attributes do not depend on other non-key attributes.

For example, storing customer address details in every order row creates redundancy. A normalized design stores customers separately and references them using customer_id.

Operational systems often favor normalized schemas for consistency and transaction processing. Analytical systems may use dimensional models, including fact and dimension tables, because fewer joins and denormalized structures can improve reporting usability. This is a design trade-off, not a universal rule.

A fact table records measurable events such as sales. Dimension tables describe entities such as products, customers, and dates. The grain of a fact table must be stated explicitly—for example, “one row per product line on an order.” Incorrectly understood grain is a major cause of double-counted measures.

4. SQL as an Analytical Language

SQL is declarative: the analyst specifies the desired result rather than prescribing every physical processing step. The database optimizer chooses an execution plan, although indexes, statistics, query structure, and configuration influence performance.

Assume the following illustrative tables:

• customers(customer_id, customer_name, segment, region)
• orders(order_id, customer_id, order_date, status, total_amount)
• order_items(order_id, product_id, quantity, unit_price)
• products(product_id, product_name, category)

4.1 Selection and projection

Selection filters rows with WHERE; projection chooses columns with SELECT.

SELECT customer_id, customer_name, region
FROM customers
WHERE region = 'West';

Text comparisons and date comparisons may depend on database settings. Explicit date literals and documented collation rules are preferable to assumptions.

4.2 Sorting and limiting

SELECT order_id, order_date, total_amount
FROM orders
WHERE status = 'Completed'
ORDER BY total_amount DESC
FETCH FIRST 10 ROWS ONLY;

The exact row-limiting syntax differs among database products. A result without ORDER BY has no guaranteed order, even if it appears sorted during testing.

4.3 Joins

An inner join returns rows satisfying the join condition:

SELECT c.customer_name, o.order_id, o.total_amount
FROM customers AS c
JOIN orders AS o
  ON c.customer_id = o.customer_id;

A left join retains every row from the left table and adds matching rows from the right table. Unmatched right-side columns become null.

SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id;

This query can identify customers with no orders by adding an appropriate predicate. Care is required: placing a condition on the right table in the WHERE clause can unintentionally turn a left join into an inner join. For example, a condition intended to preserve customers should often be placed in the ON clause.

A many-to-many join can multiply rows. If one order has three items and is joined to one order-level row, the order attributes appear three times. Summing total_amount after that join will overstate revenue. The analyst must aggregate at the correct grain or use line-level revenue instead.

4.4 Aggregation

Aggregate functions summarize rows. Common functions are COUNT, SUM, AVG, MIN, and MAX.

SELECT region,
       COUNT(*) AS customer_count
FROM customers
GROUP BY region;

A grouped query returns one row per distinct grouping combination. WHERE filters rows before grouping, while HAVING filters groups after aggregation.

SELECT c.region,
       SUM(o.total_amount) AS completed_revenue
FROM customers AS c
JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.status = 'Completed'
GROUP BY c.region
HAVING SUM(o.total_amount) > 100000;

A useful revenue identity for line-item data is:

Here, i indexes order lines, quantityᵢ is the quantity on line i, and unit_priceᵢ is the unit price. The formula assumes that prices are expressed in the same currency and that discounts, taxes, returns, and shipping are either included consistently or handled separately.

4.5 Null values

NULL represents an unknown, missing, or inapplicable value; it is not equivalent to zero or an empty string. Comparisons with NULL do not evaluate to true. Use IS NULL or IS NOT NULL.

SELECT *
FROM customers
WHERE region IS NULL;

COUNT(*) counts rows, whereas COUNT(column_name) generally counts only non-null values in that column. This distinction affects quality reporting and KPI calculations.

4.6 Subqueries and common table expressions

A subquery can express a comparison with an aggregate result:

SELECT customer_id, total_amount
FROM orders
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM orders
);

A common table expression, or CTE, improves readability and permits multi-stage logic:

WITH customer_sales AS (
    SELECT customer_id,
           SUM(total_amount) AS sales
    FROM orders
    WHERE status = 'Completed'
    GROUP BY customer_id
)
SELECT c.customer_name, cs.sales
FROM customer_sales AS cs
JOIN customers AS c
  ON c.customer_id = cs.customer_id
WHERE cs.sales >= 50000;

A CTE is not automatically materialized. Performance depends on the database engine and execution plan.

4.7 Window functions

Window functions calculate across related rows without collapsing them into one row per group. For example, ranking customers by sales while retaining each customer:

SELECT customer_id,
       SUM(total_amount) AS sales,
       RANK() OVER (ORDER BY SUM(total_amount) DESC) AS sales_rank
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id;

A running total can be written as:

SELECT order_date,
       total_amount,
       SUM(total_amount) OVER (
           ORDER BY order_date, order_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_sales
FROM orders
WHERE status = 'Completed';

The ordering columns should provide a deterministic sequence where that matters.

4.8 Data modification

SQL also supports data definition and data manipulation. Examples include CREATE TABLE, INSERT, UPDATE, and DELETE. Destructive operations require particular care. A production UPDATE or DELETE should usually be preceded by a SELECT using the same predicate, executed under an approved change process.

5. Transactions and Reliability

A transaction is a logical unit of work. The classic ACID properties are:

• Atomicity: all intended changes occur, or none do.
• Consistency: constraints and declared rules remain satisfied.
• Isolation: concurrent transactions do not produce impermissible interference.
• Durability: committed changes survive subsequent failures, subject to the system's recovery guarantees.

For example, transferring money requires debiting one account and crediting another in the same transaction. If only one operation succeeds, the database state is incorrect.

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 202;

COMMIT;

If a validation fails, the transaction should be rolled back. Transaction isolation levels represent different trade-offs between concurrency and consistency. Depending on the database, commonly described phenomena include dirty reads, non-repeatable reads, and phantom reads. Stronger isolation can reduce anomalies but may increase locking, waiting, or version-storage costs.

Analytics queries against operational systems can also create contention. Read replicas, data warehouses, snapshots, extraction windows, and workload management can reduce this impact.

6. Secure Connectivity and Responsible Access

Security must be designed into acquisition rather than added after implementation.

6.1 Authentication and authorization

Authentication establishes identity; authorization determines permitted actions. Apply least privilege: an analyst who only needs to read a reporting view should not receive permission to alter production tables. Use separate service identities for separate pipelines and review privileges periodically.

Secrets should be stored in a managed secret store or environment-specific configuration, not in notebooks, Git repositories, SQL files, or error messages. Connections should use encryption in transit, certificate validation, and approved network controls.

6.2 SQL injection

SQL injection occurs when untrusted input is concatenated into a query string. Parameterized queries treat input as data rather than executable SQL.

Unsafe pattern:

query = "SELECT * FROM customers WHERE customer_name = '" + name + "'"

Safer pattern, using a database library's parameter syntax:

query = "SELECT * FROM customers WHERE customer_name = %s"
cursor.execute(query, (name,))

The placeholder syntax varies by library. The principle is universal: use bound parameters, not string concatenation. Allow-list validation is also appropriate for dynamic identifiers such as approved table names or sort fields, which usually cannot be bound as ordinary values.

6.3 Privacy and governance

Data acquisition may involve personal, confidential, or regulated information. Responsible practice includes purpose limitation, data minimization, retention limits, access logging, masking or pseudonymization, and documented legal basis where applicable. Personally identifiable information should not be copied into development environments without a legitimate need and suitable controls.

Data lineage records where data originated, how it was transformed, who accessed it, and which analytical products depend on it. Catalogues, business glossaries, data owners, quality rules, and retention schedules support organizational accountability.

7. Data Quality and Integration

Quality is multidimensional. Important dimensions include:

• accuracy: closeness to the real-world value;
• completeness: presence of required values;
• consistency: agreement across systems;
• validity: conformity to domain and format rules;
• uniqueness: absence of unintended duplicates;
• timeliness: availability when required;
• integrity: preservation of relationships and provenance.

A quality rule should be measurable. For example, a completeness rate may be expressed as:

The formula assumes that the denominator represents all required field instances in the assessment population. A value of 98% may be acceptable for one field and unacceptable for another; thresholds require business interpretation.

Integration commonly fails because identifiers do not align. A customer may appear as C-1042 in a CRM, an integer in a billing system, and an email address in a marketing platform. A master-data process or cross-reference table is needed. Matching based only on names is risky because names change, are misspelled, and may not be unique.

A practical integration design separates:

1. raw layer: immutable source extracts;
2. standardized layer: corrected types, names, and formats;
3. conformed layer: shared identifiers and business definitions;
4. analytical layer: curated tables, aggregates, and features.

This layered approach supports auditability and allows transformations to be rerun when business rules change.

8. Worked Application: Retail Customer Profitability

The following is an illustrative case.

Background

A regional retailer wants to identify profitable customer segments. Order data is stored in a relational database, product categories are maintained in an enterprise product system, and marketing attributes are supplied by an API. Management wants a monthly segment report without exposing unnecessary personal information.

Problem

The first report overstated revenue because order totals were joined to order-item rows and summed repeatedly. In addition, the API used a different customer identifier and delivered records in pages of 100.

Analysis

The analytics team first documented table grain. The orders table contained one row per order, while order_items contained one row per product line. They decided that revenue would be measured from completed order totals and that product-category analysis would use line-level revenue. They also created a cross-reference table mapping the API's customer key to the internal customer_id.

The corrected order-level query was:

SELECT c.segment,
       SUM(o.total_amount) AS revenue,
       COUNT(DISTINCT o.order_id) AS order_count,
       COUNT(DISTINCT o.customer_id) AS customer_count
FROM customers AS c
JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.status = 'Completed'
GROUP BY c.segment;

The use of COUNT(DISTINCT ...) is deliberate. A customer may place many orders, and an order may contain many lines. The appropriate counting unit must match the metric definition.

The API extraction used pagination, stored the retrieval timestamp, validated the response schema, and wrote raw responses before transformation. API credentials were placed in a secret manager. Only a pseudonymous customer key was loaded into the reporting layer.

Solution and outcome

The team implemented a scheduled pipeline with quality checks for duplicate order identifiers, unmatched customer keys, negative quantities, unexpected currencies, and stale API responses. The report was published from curated tables rather than directly from the operational database. Revenue totals reconciled to the finance system, and database workload remained within its agreed limit because extraction used a read replica.

Learning points

1. Define table grain before joining or aggregating.
2. Preserve raw source data and record lineage.
3. Treat pagination and identifiers as engineering concerns, not minor coding details.
4. Align metric definitions with business owners.
5. Use security and privacy controls throughout the pipeline.

9. Performance, Maintainability, and Contemporary Practice

Query performance depends on data volume, indexes, cardinality, join strategy, partitioning, statistics, and hardware. An index can accelerate selective lookups but imposes storage and update costs. Indexes should be chosen from observed access patterns rather than added indiscriminately.

Use EXPLAIN or the database's equivalent to inspect execution plans. Avoid selecting unnecessary columns, filter early when appropriate, avoid accidental Cartesian products, and ensure join predicates use compatible data types. However, readability and correctness should come before premature optimization.

Modern architectures often combine relational warehouses, cloud object storage, transformation frameworks, orchestration tools, data catalogues, and observability platforms. ELT—extract, load, then transform—takes advantage of scalable warehouse computation, whereas ETL transforms before loading. Neither is universally correct; the choice depends on governance, cost, privacy, source constraints, and computational capability.

AI systems increase the need for dependable acquisition. A predictive model trained on incomplete or stale data can produce confident but misleading outputs. Retrieval-augmented systems and automated decision tools also require provenance, access controls, evaluation datasets, and protection against prompt or data injection. Automation does not remove the responsibility to validate source data and explain data use.

Challenges and Limitations

Data acquisition systems face several recurring limitations:

• source systems may change schemas without adequate notice;
• historical corrections may not be visible in incremental extracts;
• APIs may impose quotas or revoke access;
• identifiers may be ambiguous across organizations;
• data quality rules may conflict with legitimate exceptional cases;
• high isolation can reduce operational throughput;
• denormalized analytical tables can become difficult to govern;
• anonymization may reduce analytical utility while not eliminating re-identification risk;
• cloud storage and compute costs can grow through uncontrolled retention and repeated processing.

These challenges require monitoring, documentation, testing, versioning, and clearly assigned ownership. A pipeline is a production system when people depend on its outputs, even if it began as a notebook.

Best-Practice Checklist

• Define the business purpose and required latency before selecting technology.
• Document source ownership, schema, grain, refresh schedule, and quality expectations.
• Preserve raw extracts and maintain lineage.
• Use explicit types, time zones, units, and currency definitions.
• Validate row counts, uniqueness, referential integrity, ranges, and freshness.
• Use parameterized SQL and managed secrets.
• Apply least privilege and encrypt connections.
• Design incremental extraction with recovery and deduplication.
• Test joins for row multiplication and aggregation errors.
• Query production systems responsibly and monitor workload.
• Separate personally identifiable information from general analytical data where possible.
• Make metric definitions and assumptions visible to decision makers.

Chapter Summary

Data acquisition, storage, and SQL form the operational foundation of business analytics. Files, APIs, relational databases, web services, enterprise applications, and event streams each require different acquisition strategies. Reliable workflows discover source meaning, authenticate securely, extract reproducibly, validate quality, transform transparently, and store data in layers that preserve lineage.

Relational databases organize data through tables, keys, relationships, constraints, and schemas. Normalization supports consistency in operational systems, while dimensional and selectively denormalized structures often support analytical access. SQL enables filtering, projection, joins, aggregation, subqueries, common table expressions, and window calculations. Correct results depend on understanding nulls, table grain, join cardinality, and the difference between row-level and group-level logic.

Transactions provide atomicity, consistency, isolation, and durability. Secure connectivity requires least privilege, encryption, managed secrets, and parameterized queries. Data quality and privacy are not optional technical enhancements: they determine whether analytical outputs are trustworthy and responsible. Contemporary data platforms increasingly combine warehouses, object storage, APIs, orchestration, catalogues, and AI-enabled applications, but the fundamental requirements of accurate definitions, strong governance, and reproducible processing remain unchanged.

Key Terms

API: A programmatic interface through which software requests or exchanges data.

Batch processing: Acquisition or computation performed periodically on accumulated data.

Columnar storage: A storage format that organizes values by column to support analytical efficiency.

Common table expression: A named temporary query result introduced with WITH.

Data lineage: A record of data origin, transformations, movement, and use.

Fact table: An analytical table containing measurable events at a defined grain.

Foreign key: A column or set of columns referencing a key in another table.

Grain: The precise level of detail represented by one row in a table.

Incremental extraction: Retrieval of only new or changed source records.

Join: An operation that combines rows from tables using a relationship condition.

Normalization: The organization of relational data to reduce redundancy and update anomalies.

Null: A marker representing an unknown, missing, or inapplicable value.

Primary key: An attribute or attribute set that uniquely identifies a row.

Referential integrity: The requirement that valid relationships between related tables are preserved.

Relational database: A database that represents data through related tables.

SQL injection: An attack in which untrusted input changes the meaning of a database query.

Transaction: A logical unit of database work governed by consistency and recovery rules.

Window function: A function that computes across related rows while retaining row-level output.

Review Questions

Short Answer Questions

1. What is the difference between batch and streaming acquisition?
2. Why is table grain important when joining and aggregating data?
3. Distinguish a primary key from a foreign key.
4. What is the purpose of a parameterized SQL query?
5. Why is NULL not equivalent to zero?

Long Answer Questions

1. Explain the stages of a reliable data acquisition workflow and describe the controls appropriate at each stage.
2. Compare normalized relational schemas with dimensional analytical schemas, including the circumstances in which each is appropriate.
3. Explain inner joins and left joins, and discuss how join multiplication can produce incorrect analytical results.
4. Describe the ACID properties and explain why transaction isolation involves a performance and consistency trade-off.
5. Discuss how privacy, least privilege, lineage, and retention contribute to responsible analytics.

Analytical / Application Questions

1. An order table contains one row per order and an order-item table contains one row per product line. Design a SQL strategy to calculate both total order revenue and category revenue without double counting. State the grain of each intermediate result.
2. An API returns 100 records per page and sometimes times out after the third page. Design an extraction process that supports pagination, retries, recovery, deduplication, and auditability.
3. A company reports that its customer count increased sharply after integrating CRM and billing data. Propose diagnostic SQL checks and data-quality tests to determine whether the increase represents real customers, duplicate identities, or a join error.

Multiple Choice Questions with Answers

Question 1

Which SQL clause filters rows before grouping takes place?

A. HAVING

B. WHERE

C. ORDER BY

D. GROUP BY

Correct Answer: B. WHERE

Question 2

Which constraint uniquely identifies each row in a relational table?

A. Foreign key

B. CHECK constraint

C. Primary key

D. DEFAULT constraint

Correct Answer: C. Primary key

Question 3

What is the main purpose of a left join?

A. To return only matching rows from both tables

B. To retain all rows from the right table

C. To retain all rows from the left table and matching rows from the right

D. To remove duplicate rows automatically

Correct Answer: C. To retain all rows from the left table and matching rows from the right

Question 4

Which practice most directly prevents SQL injection when user input is used in a query?

A. Using a larger database server

B. Concatenating input after converting it to text

C. Using parameterized queries

D. Sorting the input alphabetically

Correct Answer: C. Using parameterized queries

Question 5

What does atomicity mean in a database transaction?

A. Committed data survives a system failure

B. All intended operations occur or none occur

C. Concurrent transactions never wait

D. All values are stored as integers

Correct Answer: B. All intended operations occur or none occur

Question 6

A table contains one row per order line. An order total is repeated on every line. What is the principal risk of summing the order total after joining this table?

A. The query will always return no rows

B. The order total may be counted multiple times

C. The database will convert totals to dates

D. The primary key will be deleted

Correct Answer: B. The order total may be counted multiple times

Question 7

Which expression correctly tests whether a column has no value in SQL?

A. column = NULL

B. column == NULL

C. column IS NULL

D. column HAS NULL

Correct Answer: C. column IS NULL

Question 8

Which approach is most appropriate for retrieving only records changed since the previous pipeline run?

A. Full extraction with no validation

B. Incremental extraction using a reliable change marker

C. Random sampling of source rows

D. Deleting the source after each run

Correct Answer: B. Incremental extraction using a reliable change marker

Question 9

What does a window function generally allow an analyst to do?

A. Calculate across related rows while retaining row-level results

B. Delete all duplicate rows automatically

C. Replace database authentication

D. Convert a relational database into a file

Correct Answer: A. Calculate across related rows while retaining row-level results

Question 10

Which data-quality dimension concerns whether required values are present?

A. Completeness

B. Timeliness

C. Uniqueness

D. Accuracy

Correct Answer: A. Completeness