DS0-001 Practice Test 2 — 30 Questions

Question 1

What does the 'P' in the acronym 'KPI' stand for?

Accepted Answer

Performance. KPI stands for Key Performance Indicator.

Answer

Product

Answer

Process

Answer

Planning

Question 2

When joining two tables in SQL where you need to return all records from the left table even if there is no match in the right table, which JOIN type is appropriate?

Accepted Answer

LEFT JOIN. A LEFT JOIN returns all rows from the left table and the matched rows from the right table, filling with NULL if no match exists.

Answer

CROSS JOIN

Answer

INNER JOIN

Answer

FULL OUTER JOIN

Question 3

To comply with CCPA, an organization must track the 'Right to Know' requests. Which component of a Data Governance Framework is most relevant?

Accepted Answer

Data Cataloging. Data Cataloging allows for the inventory and metadata management necessary to respond to data access requests.

Answer

Data Ingestion Pipeline

Answer

Load Balancing

Answer

Data Compression

Question 4

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() function determines the sequence for running calculations.

Answer

GROUP BY

Answer

PARTITION BY

Answer

ROWS BETWEEN

Question 5

You are performing data transformation in Python using Pandas. You have a 'DataFrame' with missing values in the 'Revenue' column. Which method should be used to replace these missing values with the column mean?

Accepted Answer

fillna(). The fillna() method is the standard way to replace NaN values in a Pandas DataFrame.

Answer

replace()

Answer

dropna()

Answer

interpolate()

Answer

map()

Question 6

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. IAM policies attached to the specific bucket (Bucket Policies) are the primary control for S3 access management.

Answer

Update the VPC Route Table

Answer

Enable Public Access Block

Answer

Enable S3 Versioning

Question 7

In a multi-tenant cloud database, which mechanism ensures that tenant A cannot see data belonging to tenant B?

Accepted Answer

Row-Level Security (RLS). Row-Level Security (RLS) restricts rows in a table based on user or application context, effectively enforcing logical isolation.

Answer

Data Replication

Answer

Schema Versioning

Answer

Database Encryption

Question 8

A data analyst is writing a SQL query to retrieve unique department names from the 'Employees' table. Which keyword should be utilized to ensure the results do not contain duplicate entries?

Accepted Answer

DISTINCT. The DISTINCT keyword is used to return only unique values from a specific column.

Answer

GROUP BY

Answer

UNIQUE

Answer

FILTER

Question 9

When analyzing JSON data stored in a relational database using SQL, how do you typically access nested key-value pairs?

Accepted Answer

Using path-based operators. Modern SQL engines provide specific operators or functions (like the '->>' operator in PostgreSQL) to navigate JSON paths.

Answer

Converting to XML first

Answer

Using the SELECT * command

Answer

Using standard JOINs

Question 10

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. IAM frameworks rely on Authentication (who you are), Authorization (what you can do), and Auditing (tracking what you did).

Answer

Network cabling standards

Answer

Database partitioning

Question 11

Which TWO of the following are legitimate ways to optimize SQL query performance?

Accepted Answer

Creating indexes on frequently filtered columns. Creating appropriate indexes and avoiding redundant subqueries significantly improve query efficiency.

Answer

Using a single large table

Answer

Selecting all columns (SELECT *)

Answer

Deleting all system statistics

Question 12

When reviewing a database security configuration, which finding poses the highest risk?

Accepted Answer

Enabled default accounts with default passwords. Default accounts (like 'admin' or 'sa') with default passwords are the most common entry points for attackers.

Answer

Old server hardware

Answer

Lack of database documentation

Answer

Redundant indexes

Question 13

A company is migrating to a cloud data warehouse and must comply with CCPA requirements for data portability. Which action is necessary to ensure compliance regarding user access requests?

Accepted Answer

Implement automated data export routines for individual user requests. Data portability under CCPA requires that the organization can export user data in a readily usable format.

Answer

Enable multi-factor authentication for all warehouse users

Answer

Disable all logging of user activity to prevent PII leakage

Answer

Increase the frequency of full database backups

Question 14

What is the primary purpose of a Data Protection Impact Assessment (DPIA) under GDPR?

Accepted Answer

Identify and mitigate data processing risks. A DPIA identifies and minimizes data protection risks associated with new data processing activities.

Answer

Backup the production database

Answer

Configure firewall rules

Answer

Accelerate database performance

Question 15

An administrator needs to enforce password complexity on the database level. Where should this policy be configured?

Accepted Answer

Database Authentication Policy/IAM. Password policies for database users are typically managed via the database's built-in authentication provider or integrated identity management system.

Answer

Database Trigger

Answer

Backup Schedule

Answer

Stored Procedure

Question 16

What is the first step in a Data Governance program?

Accepted Answer

Data Classification and Inventory. Identifying and classifying data assets is the prerequisite to applying any security controls.

Answer

Hiring a network engineer

Answer

Writing SQL queries

Answer

Purchasing storage hardware

Question 17

Which SQL function is used to convert a NULL value in a query result into a default value, such as 0?

Accepted Answer

COALESCE. COALESCE is a standard function used to return the first non-null value in a list, often used to replace NULLs.

Answer

NVL

Answer

IFNULL

Answer

ISNULL

Question 18

When cleaning a dataset, you notice that column names contain inconsistent casing (e.g., 'Name', 'NAME', 'name'). Which transformation step should be prioritized to facilitate join operations?

Accepted Answer

String Normalization. Standardizing string casing (normalization) is a fundamental prerequisite for accurate merging and joining.

Answer

One-hot encoding

Answer

Data Binning

Answer

Data deduplication

Question 19

A report needs to identify the 'top 10' customers by total spend. In SQL, which clause would you use to return only the first 10 results after sorting by spend descending?

Accepted Answer

LIMIT. The LIMIT clause (or TOP/FETCH FIRST depending on dialect) restricts the result set to the specified number of rows.

Answer

WHERE

Answer

OFFSET

Answer

HAVING

Question 20

A company is conducting a data governance audit. Which TWO of the following items should be verified to ensure proper data security?

Accepted Answer

User access rights are reviewed periodically. Audits verify the implementation of technical controls like encryption and the existence of administrative processes like access reviews.

Answer

The breakroom refrigerator is energy efficient

Answer

The database server has a secondary monitor

Answer

The office printer is connected to a UPS