CompTIA DataSys+ (DS0-001) (DS0-001) (DS0-001) — Questions 151–225

A cryptographic hash acts as a content fingerprint: if even one byte changes, the hash differs. Recording the hash alongside the archived extract and re-verifying before and after load proves the loaded data is byte-identical to what was archived, providing the non-repudiation evidence regulators require. This is the standard integrity control for ETL provenance.

Classification is only actionable when applied to a known asset inventory with accountable owners. Cataloging assets and attaching labels creates the authoritative record that downstream controls, audits, and retention schedules reference, and it exposes unmanaged data stores that would otherwise be missed.

An AFTER trigger that fires on INSERT, UPDATE, and DELETE can capture all changes and write the user, timestamp, and old and new values to an audit table. AFTER triggers run after the row is modified, so they can access both OLD and NEW values. This requires no application code changes and provides a complete audit trail as required by compliance.

A LEFT JOIN returns every row from the left table plus matched rows from the right, filling unmatched right columns with NULL. That precisely satisfies the requirement to preserve all Left records while including only matching Right records.

CCPA grants consumers the right to receive their personal information in a portable format. Automated export routines let the company fulfil individual access requests promptly and consistently, satisfying the data portability obligation without manual extraction errors.

Off-site or cloud backup storage places copies geographically apart from the primary database, so a site-level disaster cannot destroy both production data and its backups. This separation satisfies the stem's requirement for an essential component of a robust disaster recovery plan.

A full database backup captures a restorable copy of all data and schema objects before any structural change. If the upgrade fails or corrupts objects, this backup provides the rollback point, satisfying the pre-upgrade requirement for recoverability.

A window function with PARTITION BY customer_id ORDER BY order_date and SUM() computes a running total per customer without collapsing rows. The PARTITION BY restarts the sum for each customer, and the ORDER BY defines the accumulation sequence. This returns every order row plus the cumulative total, exactly matching the requirement for a per-customer running total ordered by date.

Always Encrypted performs cryptographic operations client-side, so the database engine never sees plaintext or keys. Storing the column master key in Azure Key Vault keeps key material outside SQL Server, satisfying the requirement that keys be managed externally.

A Database Audit Specification defines which auditable events, such as privileged user actions, are captured at the database level. Configuring it satisfies the requirement to log all privileged activity, since it binds specific actions to an audit target and records them for later review.

Archive logging preserves the sequence of log files after they are filled, so a base backup plus the archived logs can be replayed to any moment before the failure. Without archived logs, only the most recent backup can be restored and all transactions since then are lost. Copying the archives to a separate volume protects them from the same storage failure that destroys the data files.

Moving the transaction log to a dedicated high-performance drive directly addresses the I/O wait bottleneck by isolating sequential log writes from competing data-file reads. This separation reduces disk contention and lowers write latency, satisfying the stem's requirement to alleviate log-drive I/O pressure most effectively.

Database audit logging captures administrative and user actions such as logins, privilege changes, and DDL statements within the database engine itself, providing the granular, tamper-evident record needed to audit all administrative actions on the server.

This command is used to shrink a specific file, such as a transaction log.

Placing the order status filter in the ON clause of a LEFT JOIN preserves all customers from the left table. Customers with no valid orders will have NULL values from the orders table, allowing aggregation to produce NULL or zero AOV. This correctly satisfies the requirement to show all customers while excluding cancelled and returned orders from the calculation.

Retention policies specify how long each data category is kept and when lawful disposal occurs, directly governing the lifecycle's storage and destruction phases. This satisfies the stem's requirement for good lifecycle management by preventing indefinite accumulation and aligning disposal with legal and business obligations.

Stewardship assigns named owners who resolve conflicts, approve changes, and monitor quality for each domain. Without clear accountability, duplicate or contradictory customer records persist because no one is empowered to arbitrate. This practice directly supports trustworthy golden records by making ownership and decision rights explicit across the business units that contribute data.

This query correctly groups rows by category, computes the sum of amount for each group, and then filters the grouped results using HAVING to include only categories where the total exceeds $10,000. The HAVING clause is specifically designed to filter aggregated results, making it the right choice for this scenario.

`plt.figure()` creates a new Figure object and registers it with pyplot's state machine, returning it for subsequent plotting calls. This directly satisfies the stem's requirement to add a new figure to the canvas, unlike axis-level or subplot functions that operate within an existing figure.

Disk latency directly exposes storage-bound bottlenecks: high I/O wait means queries stall waiting on physical reads rather than CPU or network. Monitoring it satisfies the stem's requirement to identify performance bottlenecks, since slow disk throughput commonly throttles database operations before other resources saturate.

Synchronous-commit Availability Group commits on the secondary before acknowledging the primary, so failover to the standby loses no committed transactions. This satisfies the stem's zero-data-loss requirement, unlike asynchronous-commit mode, which permits potential data loss during failover.

Including the actual execution plan captures the plan after execution, showing the operators actually used, including scans, seeks, and their properties. This directly confirms whether the index is being scanned or seeked for the report query, matching the need to verify the operator used.

The IN operator with a subquery tests whether each customer_id appears in the set of customer_id values returned by the orders subquery. This correctly filters customers who have at least one order, regardless of duplicates in the subquery. It is the standard and readable way to express a semi-join in SQL.

Column-level encryption with keys held externally means the DBA sees only ciphertext in the 'diagnosis' column, satisfying the mandate that administrators cannot read plaintext PHI. The application, which holds or retrieves the key, encrypts before insert and decrypts after select, so filtering can still occur on decrypted values or on deterministic tokens, preserving query functionality.

Row-level security policies automatically filter rows based on the tenant identifier associated with the user's session. This ensures that each customer can only access their own data, even though all data resides in the same tables. RLS is a standard technique for multi-tenant isolation in shared databases, enforcing logical separation without requiring separate schemas or databases.

A view stores a predefined SELECT statement, so the join logic is written once and reused. This directly satisfies the scenario's need to simplify complex joins, hiding multi-table syntax behind a single named object that users query as if it were one table.

Completeness measures whether all required values are actually populated, so it refers to the absence of missing values in a dataset. Records with null or absent entries reduce completeness, distinguishing it from accuracy, which concerns correctness of present values.

In a LEFT JOIN, each row from the left table (customers) is combined with matching rows from the right table (orders). If a customer has multiple orders, that customer will appear multiple times in the result set, once for each order. This is expected behavior and the most common reason for duplicate customer rows in a LEFT JOIN result.

Trace flag 1118 forces uniform extent allocations, reducing contention on SGAM pages for tempdb. In SQL Server 2016 and later, this behavior is enabled by default for tempdb, but on older versions or when explicitly disabled, enabling it improves performance. It complements multiple data files by further reducing allocation bottlenecks. This is a recognized tempdb optimization.

A foreign key column references the primary key of a related table, so the database engine rejects inserts or updates that would leave an orphaned row. This directly enforces referential integrity between the two tables, unlike a check constraint or unique index.

A view exposes only selected columns and rows from the Salary table, letting the reporting team query permitted data while salary columns remain hidden. Granting access to the view rather than the base table enforces column-level restriction, satisfying the requirement to prevent unauthorised access to specific columns.

PII is a primary driver of confidential classification because unauthorized disclosure can cause identity theft, regulatory penalties, and reputational harm. Classification policies typically place PII at the confidential or restricted tier, triggering encryption, access controls, and breach notification obligations under laws such as GLBA and state privacy statutes.

Maintaining the data catalog's metadata — definitions, owners, lineage, classifications — is a continuous governance duty. It satisfies the stem because stewardship of business glossaries and technical metadata keeps data discoverable and accountable, which is administrative work performed under governance policy.

DISTINCT eliminates duplicate rows from the result set by comparing all selected column values, returning each unique department name once. It directly satisfies the requirement that results contain no duplicate entries, unlike keywords that filter rows conditionally or aggregate values rather than deduplicating the projected output.

A full backup copies the entire database, providing a complete standalone restore point and resetting the differential and transaction log chain. It satisfies the stem's requirement as one of the three standard backup types, alongside differential and transaction log backups.

Personally Identifiable Information uniquely identifies an individual, so its exposure triggers legal and regulatory duties that generic data lacks. This satisfies the stem's enhanced-protection constraint, since PII demands stricter controls such as encryption, access restrictions and audit trails under regimes like UK GDPR.

Digital signatures use a private key to sign data, and anyone with the public key can verify the signature. This provides non-repudiation because only the holder of the private key could have created the signature. It also ensures integrity and origin authentication, directly meeting the requirement.

HAVING filters grouped rows after aggregation, so it can restrict results to regions whose COUNT of transactions exceeds 100. WHERE cannot reference aggregate functions, making HAVING the only clause that satisfies this post-aggregation filter requirement.

Client-Side Field Level Encryption encrypts specified fields in the driver before they reach mongod, so even a compromised database credential or server cannot read plaintext tenant data. Scoping keys per tenant means one tenant's key material cannot decrypt another tenant's fields, directly addressing the cross-tenant exposure concern stated in the policy.

Graph databases store nodes and edges as first-class entities, so relationships are persisted directly rather than inferred through joins. Traversal follows pointers between adjacent nodes, making multi-hop queries across complex, frequently changing relationships far faster than relational or document alternatives.

Replication and failover between nodes depend on timely heartbeat and synchronisation traffic, so high network latency delays detection of failure and risks stale reads. This directly satisfies the high-availability requirement by ensuring rapid, consistent failover.

Row-level security enforces predicates inside the database engine, filtering rows by the executing user's identity regardless of the query submitted. This satisfies the requirement to prevent unauthorised access to sensitive tables, because Microsoft Entra ID-backed users only ever see rows matching their granted predicate, even through ad-hoc queries or reporting tools.

DAM sensors must know which tables and columns hold regulated data before they can monitor access meaningfully. Identifying sensitive data objects scopes the monitoring policy, so alerts and audit rules apply to the assets that actually matter rather than generating noise across the whole database estate.

Partitioning allows for 'partition elimination', which significantly speeds up queries on large datasets.

A database compatibility assessment evaluates schema objects, data types, stored procedures and vendor-specific SQL against the target managed service's engine version, directly satisfying the stem's requirement to confirm application compatibility before migration. It exposes unsupported features and deprecated syntax that would otherwise break the application once cutover occurs.

Statistics feed the query optimiser's cost estimates, so stale or missing statistics cause poor execution plans and slow queries. Regularly refreshing them is a routine maintenance activity, satisfying the stem's requirement for a common database maintenance task.

The ALTER TABLE ... ADD COLUMN statement modifies the table's structure by appending a new column definition. Existing rows receive a NULL value for the new column unless a default is specified, so their data remains intact. This is the standard DDL statement for adding a column to an existing table in SQL.

FIPS-mode encryption enforces FIPS 140-2 validated cryptographic modules and algorithms for data at rest and in transit, satisfying the stem's compliance requirement. Enabling this mode ensures the database's cryptographic operations meet the mandated federal standard.

Normalising the data schema is a logical design task because it restructures relations and attributes—resolving partial and transitive dependencies—without altering physical storage. This satisfies the stem's requirement for logical, not physical, design work, since normalisation operates on the conceptual schema independently of indexes, filegroups or disk layout.

An expression index on the converted timestamp allows the database to use an index when queries filter on local time. Without it, the conversion would prevent index usage, causing full scans. This approach maintains the UTC storage while enabling efficient queries for each region by creating indexes tailored to their time zone.

Deduplicating the lookup table so that each join key appears once ensures the inner join returns at most one lookup row per fact row, eliminating the row multiplication. This can be done with a subquery that groups by the key or uses a window function such as ROW_NUMBER to pick a representative row. It directly addresses the duplicate-key cause of the inflated row count.

Transparent Data Encryption encrypts database files and backups at the page level, with the Database Encryption Key protected by a certificate stored in the master database. This satisfies the policy for data-at-rest encryption without application changes, since SQL Server decrypts data transparently during queries.

TDE encrypts data and log files at rest, and using an EKM provider allows the asymmetric key that protects the Database Encryption Key (DEK) to be stored in an HSM. This satisfies both encryption at rest and HSM key management. TDE with EKM is designed for regulatory compliance scenarios requiring hardware-based key protection. The administrator must configure the EKM provider and create a credential for the HSM.

Database Activity Monitoring inspects and logs all database transactions in real time, including privileged administrator activity, while the firewall component blocks suspicious sessions. Together they detect and prevent exfiltration attempts by a rogue administrator, who bypasses ordinary access controls.

Range partitioning on order_date allows you to define partitions for specific date ranges, such as monthly or yearly. This enables efficient archiving by switching out old partitions, which is a metadata operation. It also improves query performance through partition elimination when filtering on order_date, directly addressing the slow queries.

SSL/TLS encrypts data in transit between clients and the database server, preventing eavesdropping and man-in-the-middle attacks. PCI DSS requires strong cryptography for data transmission over open networks. Enabling SSL/TLS on the database listener ensures that sensitive customer information is protected while moving across the network.

NoSQL document stores shard data across nodes using partition keys, satisfying the horizontal scaling and high availability constraints for unstructured content. Unlike relational engines, they avoid rigid schemas and single-node write bottlenecks, letting each server hold independent document subsets with replication for failover.

A data stewardship program assigns clear accountability for data quality to data owners and stewards within each business domain. It establishes roles, responsibilities, and escalation procedures to resolve issues before they impact regulatory reporting. This directly addresses the need for formal accountability across multiple business units and ensures data quality is managed at the source.

Financial transactions demand ACID guarantees: atomicity, consistency, isolation and durability, enforced through strict schemas and multi-row transactions. A relational engine provides these natively, whereas most NoSQL stores trade full ACID compliance for horizontal scale and flexible schemas.

Materialising the view precomputes and stores the result set, so the visualisation queries a physical table instead of re-executing the underlying joins and aggregations on every refresh. This directly removes the repeated full-view scan that causes the bottleneck, satisfying the large SQL view constraint.

DENSE_RANK() assigns ranks without gaps, so ties receive the same rank and the next rank is the next consecutive integer (e.g., 1,2,2,3). This matches the requirement to assign consecutive ranks without gaps. It is specifically designed for this purpose and is the correct choice.

An S3 bucket policy is a resource-based IAM policy attached directly to the bucket, letting you grant or deny access by principal, such as the Finance team's IAM role, at the bucket level. This satisfies the constraint of restricting the specific bucket to only Finance members.

Dynamic Management Views, such as sys.dm_exec_sessions and sys.dm_exec_connections, expose live session and connection state on SQL Server. Querying them returns the current connection count directly, unlike static catalogs that describe schema objects rather than runtime activity.

Wrapping the indexed column in a function, such as UPPER(column), prevents the optimiser from using the index because the stored values no longer match the computed expression. The database therefore falls back to a full table scan despite the WHERE clause referencing that column.

LEFT preserves all rows from the left table, returning NULLs for unmatched right-side columns. It satisfies the stem's requirement for a standard SQL join type, sitting alongside INNER, RIGHT, FULL OUTER and CROSS in the ANSI specification. Unlike set operators such as UNION, joins combine columns horizontally across related tables.

Denormalisation duplicates data across tables, so a single logical fact is stored in multiple places. When one copy is updated but others are not, insert, update and delete anomalies arise, producing inconsistent query results. This directly satisfies the stem's requirement to identify a genuine drawback of denormalising, unlike performance gains.

A calendar spine guarantees every month appears for every customer, and LEFT JOIN preserves customers with no transactions. Conditional aggregation can then count transactions in M-3 and confirm zero in M-2, M-1, and M, correctly identifying churn even when no rows exist. This approach explicitly handles absent activity, which the definition requires.

GRANT SELECT ON SalesData TO analyst; provides the analyst with the privilege to run SELECT queries against the SalesData table. It does not grant INSERT, UPDATE, or DELETE privileges, so the analyst cannot modify data. This is the standard SQL statement for granting read-only access to a specific table in PostgreSQL and most other relational databases, directly fulfilling the requirement.

GRANT SELECT provides read-only access to the specified table. It allows the user to query data without granting INSERT, UPDATE, or DELETE privileges. This precisely meets the requirement of retrieving data while preventing modifications, following the principle of least privilege.

The full recovery model allows transaction log backups, enabling point-in-time recovery and minimizing data loss. With log backups every 10 minutes, the maximum data loss is 10 minutes, which satisfies the 15-minute requirement. This model is standard for OLTP databases where data loss must be minimized. It supports all restore options, including restoring to a specific point in time.

Third normal form eliminates transitive dependencies, where a non-key attribute depends on another non-key attribute rather than the primary key. Achieving 3NF therefore requires every non-key column to depend solely on the key, satisfying the stem's stated goal of removing those indirect relationships.

Sealing the volume encryption key to the host TPM means the disk is transparently decrypted at boot without an operator-supplied passphrase, and PostgreSQL's data directory, WAL, and temporary files all reside inside that encrypted volume. This satisfies the blanket data-at-rest requirement while removing passphrase management, which is exactly what the platform team requested.

A document-oriented store allows each product document to contain its own set of attributes, so categories with different attributes are represented naturally without schema migrations. This flexibility supports frequent evolution and avoids downtime, matching the requirement for highly variable product attributes across thousands of categories.

This query counts all rows where the Email column has no value, which is exactly the metric the engineer needs before choosing a cleansing approach. The IS NULL predicate is the only standard way to test for missing values reliably, and COUNT(*) includes rows regardless of other column values, giving an accurate total of missing emails.

Aggregate queries collapse rows, so every non-aggregated SELECT column must appear in GROUP BY to define grouping keys. The named column is absent from that clause, leaving the engine unable to determine which value to return per group.