Edit

Tutorial: Create tables in a data warehouse

  • Article

Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric

Learn how to create tables in the data warehouse you created in a previous part of the tutorial.

Create a table

  1. Select Workspaces in the navigation menu.

  2. Select the workspace created in Tutorial: Create a Microsoft Fabric data workspace, such as Data Warehouse Tutorial.

  3. From the item list, select WideWorldImporters with the type of Warehouse.

    Screenshot of the warehouse option that appears in the item list.

  4. From the ribbon, select New SQL query.

    Screenshot of the New SQL query option where it appears on the ribbon.

  5. In the query editor, paste the following code.

    /*
1. Drop the dimension_city table if it already exists.
2. Create the dimension_city table.
3. Drop the fact_sale table if it already exists.
4. Create the fact_sale table.
*/

--dimension_city
DROP TABLE IF EXISTS [dbo].[dimension_city];
CREATE TABLE [dbo].[dimension_city]
    (
        [CityKey] [int] NULL,
        [WWICityID] [int] NULL,
        [City] [varchar](8000) NULL,
        [StateProvince] [varchar](8000) NULL,
        [Country] [varchar](8000) NULL,
        [Continent] [varchar](8000) NULL,
        [SalesTerritory] [varchar](8000) NULL,
        [Region] [varchar](8000) NULL,
        [Subregion] [varchar](8000) NULL,
        [Location] [varchar](8000) NULL,
        [LatestRecordedPopulation] [bigint] NULL,
        [ValidFrom] [datetime2](6) NULL,
        [ValidTo] [datetime2](6) NULL,
        [LineageKey] [int] NULL
    );

--fact_sale

DROP TABLE IF EXISTS [dbo].[fact_sale];

CREATE TABLE [dbo].[fact_sale]

    (
        [SaleKey] [bigint] NULL,
        [CityKey] [int] NULL,
        [CustomerKey] [int] NULL,
        [BillToCustomerKey] [int] NULL,
        [StockItemKey] [int] NULL,
        [InvoiceDateKey] [datetime2](6) NULL,
        [DeliveryDateKey] [datetime2](6) NULL,
        [SalespersonKey] [int] NULL,
        [WWIInvoiceID] [int] NULL,
        [Description] [varchar](8000) NULL,
        [Package] [varchar](8000) NULL,
        [Quantity] [int] NULL,
        [UnitPrice] [decimal](18, 2) NULL,
        [TaxRate] [decimal](18, 3) NULL,
        [TotalExcludingTax] [decimal](29, 2) NULL,
        [TaxAmount] [decimal](38, 6) NULL,
        [Profit] [decimal](18, 2) NULL,
        [TotalIncludingTax] [decimal](38, 6) NULL,
        [TotalDryItems] [int] NULL,
        [TotalChillerItems] [int] NULL,
        [LineageKey] [int] NULL,
        [Month] [int] NULL,
        [Year] [int] NULL,
        [Quarter] [int] NULL
    );

  6. Select Run to execute the query.

    Screenshot of the top corner of the query editor screen, showing where to select Run.

  7. To save this query for reference later, right-click on the query tab, and select Rename.

    Screenshot of the top corner of the query editor screen, showing where to right-click to select the Rename option.

  8. Type Create Tables to change the name of the query.

  9. Press Enter on the keyboard or select anywhere outside the tab to save the change.

  10. Validate the table was created successfully by selecting the refresh button on the ribbon.

    Screenshot of the ribbon on the Home screen, showing where to select the refresh option.

  11. In the Object explorer, verify that you can see the newly created Create Tables query, fact_sale table, and dimension_city table.

    Screenshot of the Explorer pane, showing where to find your tables and newly created query.

Next step

Tutorial: Load data using T-SQL