DP-900 Practice Question: Identify considerations for relational data on Azure

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  Create external data source, then external file format, then external table, then use CTAS.

  Why this is correct

  This is the correct order because PolyBase requires defining where the data resides (external data source), how it is formatted (external file format), and then an external table to reference it, before using CTAS to load into the data warehouse.

• ✗

  Create external file format, then external data source, then external table, then use CTAS.

  Why it's wrong here

  This order is incorrect because the external data source must be created before the external file format in PolyBase load workflows. An external file format only defines the serialization details (e.g., delimiter, compression, file type) of the files, but the external data source identifies the actual storage endpoint and location where those files reside. Without a registered data source first, you cannot reliably associate the file format with the target storage environment when the external table is later created. The documented sequence for Azure Synapse Analytics is data source, then file format, then external table, before executing CTAS.

• ✗

  Create external table, then external data source, then external file format, then use CTAS.

  Why it's wrong here

  This order is incorrect because an external table is a metadata object that depends on both an external data source and an external file format. When you run CREATE EXTERNAL TABLE, the statement must be able to resolve the LOCATION against an already-existing external data source, and it must reference an existing external file format to parse the files. Attempting to create the external table before those dependencies exist will cause the statement to fail with a name resolution error. Therefore, the external table must be created only after the data source and file format are in place, and CTAS must come last to load the data.

• ✗

  Use CTAS, then create external data source, then external file format, then external table.

  Why it's wrong here

  This order is incorrect because CTAS (CREATE TABLE AS SELECT) is the final loading step, not a preparatory one. CTAS executes a query that reads from the external table and writes the results into a new native table in the data warehouse; if the external data source, external file format, and external table have not been created yet, the SELECT portion has no valid metadata to query and will fail. In the correct sequence, you define all external objects first, then run CTAS to materialize the data into a local, optimized table. Placing CTAS first inverts the dependency chain and makes the load impossible.

Go deeper

Related to this question