Free DS0-001 sample questions

Correct answers: The access permissions for the service account used by the reporting application were not changed.; The reporting application’s mapping to the database location was not updated.

Correct answer: Weekly full, daily incremental

Correct answer: Create a new service account exclusively for the business intelligence application.

Correct answer: A primary node with at least one synchronous standby using streaming replication, managed by a tool like Patroni or Stolon.

This architecture meets all requirements. Synchronous streaming replication ensures that a transaction is not considered committed until it has been written to both the primary and the synchronous standby, guaranteeing an RPO of zero. High-availability management tools like Patroni or Stolon use a distributed consensus store (like etcd or Consul) to manage the cluster state, handle leader election, and trigger automatic failover well within the 30-second RTO. They also facilitate routing read queries to the standby nodes.

Correct answer: Use an offline data transfer appliance service provided by the cloud vendor.

Transferring 10TB over a 1 Gbps connection would take approximately 24 hours, leading to significant downtime. Offline data transfer appliance services (like AWS Snowball or Azure Data Box) are designed for this exact scenario. The company can load the 10TB of data onto a physical appliance, ship it to the cloud provider, and have the data loaded directly into their cloud environment. This method is much faster for the initial bulk load than a network transfer over a limited connection, thus minimizing the initial migration downtime. Subsequent changes can be synchronized over the network.

Correct answer: Create a composite index on (`CustomerID`, `OrderDate`) on the `Sales` table.

The presence of a full table scan on the largest table (`Sales`) indicates a missing or ineffective index for the query's `WHERE` clause. Since the filter applies to both `CustomerID` and `OrderDate`, a composite index on these two columns is the most effective solution. This allows the database engine to quickly locate the relevant rows without scanning the entire table, providing a dramatic performance boost. While separate indexes could be used, a single composite index is far more efficient for queries that filter on both columns simultaneously.

Correct answer: Create database views that expose only the non-PII columns and grant developers access to the views.

This solution adheres to the principle of least privilege. By creating views that select only the necessary, non-sensitive columns from the underlying tables, an abstraction layer is created. The developers' direct access to the base tables can be revoked, and they can be granted `SELECT` permissions only on these secure views. This allows them to perform their debugging tasks without ever having access to the PII, effectively mitigating the security risk and helping achieve GDPR compliance.

Correct answer: False

The statement is false. The property being described is `DURABILITY`, which guarantees that once a transaction is committed, it will remain so, even in the event of power loss, crashes, or errors. `CONSISTENCY` ensures that a transaction brings the database from one valid state to another, preserving all predefined rules, constraints, cascades, and triggers.

Correct answer: PowerShell

PowerShell is the native, object-oriented scripting language and automation framework for Windows. It is ideal for this task as it has built-in cmdlets for interacting with SQL Server (`Invoke-Sqlcmd`), file system operations (`Compress-Archive`), and network shares (`Copy-Item`). It allows the entire workflow to be managed within a single, powerful script without installing third-party tools.

Correct answer: Column-oriented database

Column-oriented databases, like Apache Cassandra or HBase, are exceptionally well-suited for time-series data from IoT applications. They excel at high-velocity writes and are designed for massive horizontal scalability. The data model, which stores data by column rather than by row, is highly efficient for the specified query pattern of retrieving data for a specific sensor (partition key) over a time range (clustering key).