Data Integration

DS0-002 · 39 questions

• A new partner feed combines public product catalog data with customer names, email addresses and order histories. The feed is about to be loaded into a shared analytics area that many analysts can query. What should the DBA do first?
• A legacy data warehouse is fed by a dedicated integration server that pulls rows from several source systems, cleanses and conforms them in its own processing engine, aggregates them and then writes only the finished tables into the warehouse. Which data integration approach does this describe?
• A team is moving to a cloud data warehouse with elastic compute. Their new pipeline copies raw source data into staging tables in the warehouse first, then runs SQL models inside the warehouse to clean and reshape it. Which approach is this, and why does it suit the platform?
• A regulation states that national identification numbers must never be stored in the company's analytics platform, not even temporarily in staging tables. A new feed contains those numbers alongside the sales data analysts need. Which integration approach meets the requirement?
• A native Windows reporting application, not written in Java, must query SQL Server, PostgreSQL and MySQL databases through one standard, language-neutral driver interface that the application already supports. What should the DBA set up on the reporting server?
• A developer is building a Java microservice that must read and write a relational database. The developer asks what the service needs in order to open connections to the database through the standard Java data access API. What is required?
• A company wants partner applications to query a product table over HTTPS as RESTful resources, using standardized URL query options such as $filter, $select and $top instead of custom endpoints for each request type. Which protocol best fits this requirement?
• A partner uploads nightly data files to the company using FTP. An audit finds that the partner's login credentials and the file contents cross the network in clear text. The partner's systems support SSH. Which replacement best addresses the finding?
• A file server hosts a landing folder for nightly extracts. Linux ETL hosts must mount it, and Windows reporting hosts must also access it, each using its operating system's native file-sharing protocol. Security requires that obsolete protocol versions be disabled. Which configuration fits?
• A legacy integration with an insurance carrier sends requests over HTTP as XML documents, each with an envelope containing a header and a body. The available operations and their message formats are defined in a WSDL file supplied by the carrier. Which type of interface is this?
• A nightly job pulls records from a SaaS platform's API, which returns JSON over HTTPS in pages of 100 records with a next-page token and responds with HTTP 429 when a client exceeds its request quota. The job misses records and often fails with 429 errors. Which fix is best?
• A fraud team needs website clickstream events analyzed within a few seconds of each click. Today the web servers write events to log files that a batch job loads into the database once each night. Which data acquisition approach meets the new requirement?
• An analytics team proposes writing a scraper to collect daily prices from a supplier's public web pages. The supplier also offers a documented API to registered partners, and the company is a registered partner. What should the DBA recommend before any scraper is built?
• A company has two integration needs: a mobile app needs a compact, lightweight payload from its API, and an industry partner requires documents that use namespaces and are validated against the partner's published XSD schema. Which pairing of data formats fits these needs?
• A supplier sends a comma-delimited flat file whose first row holds the column names. Some product descriptions contain commas and are wrapped in double quotes. The import splits those rows into extra columns and loads the header as a data row. Which change fixes the import?
• A partner's nightly extract arrives by file transfer, but the import loads only part of it and several rows contain garbled characters. The received file's size also differs from the size stated in the partner's manifest, which includes a SHA-256 hash of the file. What should the DBA do first?
• A nightly load calls a vendor's API, which is unavailable during the vendor's occasional maintenance windows and returns HTTP 503 errors. When that happens the job fails on the first error, and nobody notices until analysts find stale data the next morning. Which improvement should the DBA make?
• After a nightly import, customer names such as José and Müller appear in the database as José and Müller. The source system exports the file as UTF-8, and the load job reads files using the server's default single-byte code page. Which action resolves the problem?
• A European supplier sends a file with dates written as 03/04/2026, meaning 3 April 2026. The import interprets the values as month/day, so many dates land in the wrong month and any value with a day above 12 is rejected. Which fix should the DBA apply?
• A source team inserted a new column into the middle of a daily extract file without notice. The load job maps fields to target columns by their position, so it now fails on type conversion errors or puts values into the wrong columns. Which change best prevents a repeat?
• A source system stores invoice amounts as DECIMAL(12,2), but the target column was created as DECIMAL(8,2). Rows with amounts above 999,999.99 now fail to load with numeric overflow errors. Which fix should the DBA apply?
• After a security hardening project, the ETL service account still connects to the database successfully, but every INSERT into the staging table now fails with a permission denied error. The job needs to insert and read rows in that table only. What should the DBA do?
• An extract query opens its database connection successfully but fails after 30 seconds with a command timeout error. The query scans a very large table without using any filter on an indexed column, although an index exists on the date column the extract needs. What is the best approach?
• A pipeline decrypts incoming files with a data encryption key retrieved from the company's key vault. Last night the security team rotated that key. Files received before the rotation still decrypt correctly, but every file encrypted since then fails with a decryption error. What is the most likely cause?
• A platform uses envelope encryption: each dataset is encrypted with its own data encryption key, and those keys are stored only in encrypted form, wrapped by a master key held in the key management service. A security administrator disabled that master key. Now no dataset can be decrypted. Which statement explains the failure?
• A database server was upgraded to a new major version that uses a stronger default authentication method. The application servers kept their older client drivers. Since the upgrade, connections fail with an error stating that the authentication method is not supported. What should the DBA do?
• A Python integration script runs correctly on a developer's workstation but fails on the production server with an error saying a library function does not exist. Investigation shows the server has an older Python interpreter and older library versions than the workstation. What is the best fix?
• Two integration scripts fail overnight. The first stops immediately, before processing any rows, with the message unexpected token near FROM. The second processes 10,000 rows successfully and then stops with a division by zero error. How should these failures be classified?
• An analyst working in Python must clean a large tabular CSV file before it is loaded into the database: remove duplicate rows, fill in missing values, join it to a lookup table and pivot several columns. Which library is designed for this work?
• A Python data pipeline has two steps. The first performs fast, vectorized mathematical operations on large multidimensional numeric arrays. The second trains a classification model on the prepared data and evaluates it with cross-validation. Which libraries best match these two steps?
• A statistics team works in R and wants one consistent collection of packages for importing, tidying and transforming data. A separate engineering group uses a commercial numeric computing environment with its own language for matrix computations and simulations. Which tools match these two groups?
• A developer asks an AI assistant to write a SQL query for a monthly sales report. The generated query looks well formed and confident, but it references a column named customertier that does not exist anywhere in the database schema. What is this, and what is the best response?
• Developers ask an AI assistant to write a query for last month's sales by region using a one-line prompt. Each time, the assistant returns different table names, a different definition of last month and sometimes nonstandard SQL. Which change best improves the consistency of the results?
• An internal chatbot answers employee questions about HR policy using only what its language model learned in training. Policies change monthly, so many answers are outdated. The company wants answers grounded in the current policy documents without retraining the model each month. Which approach fits?
• An AI tool reviews the customer master table and suggests address corrections, which are currently applied to the table automatically. Audits find that some corrections were wrong and overwrote valid addresses. The business wants to keep using the tool. Which control best addresses the problem?
• Analysts need to query a CRM database, an ERP database and files in cloud object storage together through a single SQL interface. A compliance rule forbids copying the data into a central repository, so each source must remain the system of record. Which approach meets this need?
• Clerks spend hours retyping data from emailed PDF forms into a legacy desktop application. The application has no API, and the vendor does not permit direct writes to its database. Management wants to automate the work without replacing the application. Which approach fits?
• A model trained on customer records performs poorly in testing. Investigation finds that many customers appear several times in the training data and that key fields such as region and account age are often empty. What should the team address first before tuning the model?
• A team must integrate two systems. A modern SaaS CRM offers a documented REST API with authentication and versioning. A legacy terminal-based order application can be used only through its screens, has no API and allows no database access. Which integration approach should the team use for each?