DS0-001 Practice Test 2 — 25 Questions

Question 1

A database architect is setting up an Always On Availability Group in SQL Server. To ensure that the secondary replica can be used for read-only workloads without impacting the primary node's performance, how should the 'Readable Secondary' property be configured?

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

Accepted Answer

Set to 'Read-intent only' to allow access based on application connection strings.. The Readable Secondary property allows you to designate which replicas accept read-only connections. Setting this to 'Read-intent only' is a common practice for offloading read-only reporting queries.

Answer

Set the synchronous commit mode to 'Asynchronous' on all nodes.

Answer

Set to 'Yes' and force all writes through the secondary replica.

Answer

Set to 'No' and route traffic via a secondary listener.

Question 2

Which document is essential to establish the legal basis for processing personal data under GDPR between a controller and a processor?

Accepted Answer

Data Processing Agreement (DPA). A Data Processing Agreement (DPA) is a mandatory contract under GDPR that specifies the responsibilities of the data controller and the data processor.

Answer

Service Level Agreement (SLA)

Answer

Acceptable Use Policy (AUP)

Answer

Data Privacy Impact Assessment (DPIA)

Question 3

A company needs to audit all administrative actions taken on their database server. Which feature should the DBA enable?

Accepted Answer

Database Audit Logging. Database Auditing logs all DDL and DML operations, providing the evidence needed for compliance reporting.

Answer

Data Partitioning

Answer

Query Optimization

Answer

Transaction Log Backup

Question 4

When working with time-series data, what does a 'lag' feature represent in an analysis?

Accepted Answer

The value of a variable from a previous period. A lag feature represents the value of a variable at a previous time step, which is useful for trend analysis.

Answer

The time taken to query the data

Answer

The difference between two time stamps

Answer

The sampling rate of the sensors

Question 5

A company is using a cloud-based data lake. To satisfy data sovereignty requirements, they must ensure data remains in a specific region. How can this be enforced?

Accepted Answer

Regional Resource Policies. Cloud providers use resource location constraints and policy-based tags to restrict where data can be physically stored.

Answer

Database Load Balancing

Answer

Database Indexing

Answer

Data Compression

Question 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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