DS0-001 Practice Exam 2 — 20 Questions

Question 1

An organization is deploying a new MySQL 8.0 database for an order-entry application. The team needs the database to enforce referential integrity so that an order row cannot reference a customer that does not exist. Which deployment step accomplishes this?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create the orders table with an InnoDB FOREIGN KEY constraint referencing the customers table primary key.. Referential integrity is enforced when the child table carries a foreign key that names the parent table and column. The engine then validates every write against the parent and applies the configured action on parent deletion or update. Uniqueness, privileges, and range checks each constrain a different aspect of the data and none of them can detect a reference to a missing customer.

Answer

Add a CHECK constraint on orders.customer_id that verifies the value is greater than zero.

Answer

Grant the application account only SELECT and INSERT privileges on the orders table.

Answer

Create a unique index on the customer_id column of the orders table so duplicate references are impossible.

Question 2

What is the first step in a Data Governance program?

Accepted Answer

Data Classification and Inventory. A Data Governance program must begin with understanding what data exists, where it resides, and how sensitive it is — this is achieved through Data Classification and Inventory. Without knowing the data landscape, you cannot define policies, roles, or controls. Classification (e.g., public, internal, confidential, restricted) drives the governance rules that follow.

Answer

Hiring a network engineer

Answer

Writing SQL queries

Answer

Purchasing storage hardware

Question 3

Before finalizing a database deployment, the team must ensure high availability. Which configuration allows for automatic failover to a standby server with zero data loss in a SQL Server environment?

Accepted Answer

Synchronous-commit Availability Group. A Synchronous-commit Availability Group in SQL Server ensures zero data loss by committing transactions on the primary replica only after they are hardened on at least one synchronous secondary replica. This provides automatic failover with no data loss, meeting the high availability requirement. It is the correct choice for zero data loss with automatic failover.

Answer

Database Mirroring in High Performance mode

Answer

Log Shipping

Answer

Asynchronous-commit Availability Group

Question 4

In the context of data quality, what does 'Completeness' refer to?

Accepted Answer

The absence of missing values. In data quality, Completeness refers to the degree to which all required data values are present — i.e., the absence of missing values in a dataset. It measures whether records have all expected attributes populated, not whether those values are correct or consistent.

Answer

The consistency of data across sources

Answer

The timeliness of the data

Answer

The accuracy of values

Question 5

When using Python's 'Matplotlib' library, which function is used to add a new figure to the canvas?

Accepted Answer

plt.figure(). In Matplotlib, the plt.figure() function is used to create a new figure object, which serves as the canvas for plotting. Calling plt.figure() adds a new figure to the current pyplot state and returns a Figure instance that can be populated with axes and plots. Without calling plt.figure(), plotting functions like plt.plot() will draw on the current figure, or create one implicitly if none exists.

Answer

plt.axis()

Answer

plt.plot()

Answer

plt.show()

Question 6

A company is planning to migrate a legacy on-premises database to the cloud. Which assessment should be conducted to ensure the application compatibility with the target managed database service?

Accepted Answer

Database compatibility assessment. A database compatibility assessment evaluates whether the legacy database's schema, data types, stored procedures, triggers, and application dependencies will work on the target managed service. Managed cloud databases often lack certain features (e.g., SQL Server Agent, cross-database queries, CLR, or specific system stored procedures), so this assessment identifies incompatibilities before migration and informs remediation planning.

Answer

Network throughput analysis

Answer

Capacity planning report

Answer

Data integrity audit

Question 7

You are deploying a database that requires compliance with FIPS 140-2. What must be configured to ensure the database meets this security standard?

Accepted Answer

FIPS-mode encryption. FIPS 140-2 is a U.S. government standard for cryptographic modules, so compliance requires that the database use FIPS-validated cryptographic modules — typically enabled via a FIPS mode setting that forces all encryption operations through validated algorithms and libraries. Without FIPS-mode encryption, the database may use non-validated ciphers even if encryption is otherwise enabled.

Answer

Database hardening scripts

Answer

User password rotation policies

Answer

Multi-factor authentication (MFA)

Question 8

Which database backup strategy is recommended to ensure that you can restore a database to a specific point in time?

Accepted Answer

Full and Transaction Log backups. Point-in-time recovery requires a full backup as the base plus a continuous chain of transaction log backups. Restoring the full backup and then replaying transaction logs up to a specific timestamp (STOPAT) reconstructs the database to any moment within the log chain's coverage.

Answer

Differential backups only

Answer

Full backups only

Answer

Copy-only backups

Question 9

An administrator needs to restrict access to a specific S3 bucket so that only the Finance team can view files. Which configuration is the most effective approach?

Accepted Answer

Configure an S3 Bucket Policy. An S3 Bucket Policy is a resource-based IAM policy attached directly to the bucket that defines who can access it and under what conditions. By specifying the Finance team's IAM principals and granting only them s3:GetObject permissions, the administrator can effectively restrict access to the bucket.

Answer

Update the VPC Route Table

Answer

Enable Public Access Block

Answer

Enable S3 Versioning

Question 10

A cloud data platform team must prove to auditors that only approved personnel accessed a regulated dataset during a specific incident window. The platform uses role-based access control and stores query history in the warehouse. Which evidence source most directly demonstrates authorized access during that window?

Accepted Answer

The warehouse access history and query logs filtered to the dataset and incident window, correlated with role grants.. Auditors need object-level access evidence tied to identities and time. Warehouse access history and query logs filtered to the dataset and incident window, correlated with the role grants in effect, show exactly which principals read the regulated data and whether each read was authorized, which is the most direct and defensible proof.

Answer

The change management tickets approved during the incident window for the platform team.

Answer

The data catalog's lineage graph showing which downstream reports consume the regulated dataset.

Answer

The identity provider's sign-in logs showing which users authenticated and from which IP addresses.

Question 11

A security team is implementing an access management strategy. Which THREE of the following are considered essential components of an effective Identity and Access Management (IAM) framework?

Accepted Answer

Authentication mechanisms. Authentication mechanisms (A) are essential because IAM must first verify a subject's claimed identity through credentials such as passwords, MFA, certificates, or biometrics before any access decision can be made. Authorization policies (D) are equally essential, as they define what an authenticated identity is permitted to do, typically expressed through role-based access control (RBAC), attribute-based access control (ABAC), or least-privilege rules. Centralized logging and auditing (C) are also core to IAM because they provide the accountability trail—recording authentication events, privilege changes, and access attempts—needed for compliance, forensic investigation, and continuous monitoring. Network cabling standards (B) belong to physical infrastructure design (e.g., Cat6, fiber) and are unrelated to identity or access management. Database partitioning (E) is a data storage and performance technique for splitting tables or schemas, not an IAM component.

Answer

Network cabling standards

Answer

Database partitioning

Question 12

A data analyst is reviewing a query that joins three tables: orders, customers, and products. The query returns more rows than expected. Which TWO of the following are likely causes of the row multiplication? (Choose two.)

Accepted Answer

The query includes a many-to-many relationship between orders and products without an intermediate bridge table. Row multiplication in multi-table joins typically stems from join cardinality mismatches. A non-unique key on the dimension side causes one fact row to match multiple dimension rows, and a many-to-many relationship without a bridge table causes combinatorial expansion. Join type changes, NULL filters, and sorting affect inclusion or order but not the number of matched rows, so they do not explain the inflated result set.

Answer

The query filters on a column that contains NULL values in the products table

Answer

The query orders the result by a column with duplicate values

Answer

The query uses an INNER JOIN instead of a LEFT JOIN between orders and customers

Question 13

A database administrator is asked to create a new database that will store historical data and will not be updated frequently. The database will be read-only most of the time, and the administrator wants to minimize storage space and improve query performance for large scans. Which SQL Server feature should the administrator implement?

Accepted Answer

Columnstore index. Columnstore indexes use columnar storage and compression, which significantly reduces storage space and improves performance for large scans and analytical queries. They are optimized for read-only or infrequently updated data, making them ideal for historical data. Clustered, nonclustered, and filtered indexes are row-based and do not offer the same compression and scan performance benefits.

Answer

Nonclustered index

Answer

Filtered index

Answer

Clustered index

Question 14

A DBA is configuring an Always On Availability Group in SQL Server. To ensure that the secondary node can be used for read-only reporting tasks, which setting must be correctly configured in the Availability Group replica settings?

Accepted Answer

Readable Secondary. The Readable Secondary setting in the Availability Group replica configuration determines whether a secondary replica can accept read-only connections. Setting it to 'Read-intent only' or 'Yes' allows reporting queries to be routed to the secondary, offloading the primary. Without this setting, the secondary is not accessible for read-only workloads even if it is synchronized.

Answer

Automatic Failover

Answer

Synchronous Commit

Answer

Primary Role

Question 15

An organization uses a 'bring your own key' (BYOK) model for database encryption. What is the primary benefit?

Accepted Answer

Increased control over encryption keys. The primary benefit of BYOK is increased control over encryption keys: the organization generates and manages its own keys, which are used by the cloud provider for encryption at rest. This gives the organization greater security oversight, the ability to revoke access, and compliance with data sovereignty requirements.

Answer

Easier database installation

Answer

Improved query performance

Answer

Lower storage costs

Question 16

Which DML statement is used to update existing records in a database table?

Accepted Answer

UPDATE. The UPDATE statement modifies the data of existing rows in a table.

Answer

ALTER

Answer

MODIFY

Answer

INSERT

Question 17

You are migrating an on-premises Oracle database to Oracle Cloud Infrastructure (OCI) using a tool that requires the least amount of downtime while keeping the source and destination databases in sync. Which method should you select?

Accepted Answer

Implement Oracle GoldenGate for continuous logical replication.. Oracle GoldenGate performs continuous logical replication by mining redo/archive logs on the source and applying changes to the target, keeping both databases in sync with minimal downtime. This makes it the standard choice for near-zero-downtime migrations where the source must remain available until cutover.

Answer

Perform a cold backup and restore to an OCI block storage volume.

Answer

Use Data Pump Export/Import with manual application of incremental backups.

Answer

Use SQL Developer to manually generate and execute insert scripts for all tables.

Question 18

Which THREE of the following are common SQL constraints?

Accepted Answer

PRIMARY KEY. PRIMARY KEY (C) is a common SQL constraint that uniquely identifies each row in a table and implicitly enforces NOT NULL and uniqueness. UNIQUE (D) is a common SQL constraint that ensures all values in a column or set of columns are distinct, though it typically permits NULL values. CHECK (E) is a common SQL constraint that restricts the values a column can accept based on a Boolean expression, such as CHECK (age >= 18). GROUP BY (A) is a SELECT clause used for aggregating rows into groups, not a constraint, and JOIN (B) is a query operation that combines rows from two or more tables, also not a constraint.

Answer

GROUP BY

Answer

JOIN

Question 19

A healthcare analytics team stores patient records in a PostgreSQL database. Auditors require that any change to the 'diagnosis' column be traceable to a specific database user and timestamp, without relying on application-level logging. Which mechanism should the data engineer implement to meet this requirement?

Accepted Answer

Create an AFTER UPDATE trigger that writes old and new values plus current_user and now() into an audit table.. Database-level auditing is required when changes must be attributable to a specific user and timestamp independent of the application. An UPDATE trigger writing old and new values along with current_user and now() captures this at the engine, covering both application and direct SQL changes. Access controls and statement logging do not produce the per-change, queryable evidence the auditors demand.

Answer

Grant UPDATE on the diagnosis column only to a dedicated service role and revoke it from others.

Answer

Enable PostgreSQL row-level security policies on the patient table.

Answer

Configure PostgreSQL to write all statements to the server log with log_statement = 'all'.

Question 20

A database administrator needs to ensure that a query calculating running totals is performant. Which window function clause is essential to order the calculation sequence?

Accepted Answer

ORDER BY. The ORDER BY clause inside the OVER() specification defines the logical ordering of rows for the window function, which is essential for running totals because the cumulative sum must be computed in a defined sequence. Without ORDER BY, the window frame defaults to the entire partition and the running total is meaningless. ORDER BY establishes the row sequence that makes the cumulative calculation deterministic.

Answer

GROUP BY

Answer

PARTITION BY

Answer

ROWS BETWEEN