This tutorial turns a Lake table into a modelled Warehouse table that refreshes on a schedule. Start from a deployed Lake table with loaded data, such as the one from From a source to your first query.
Before you start
A deployed Lake table with loaded data. Know its name and the column that identifies each row.
Permissions: Add/Modify Warehouse Objects to create tables and transformations, and Execute/Abort Warehouse Objects to run transformations. To schedule the transformation, you also need Execute Warehouse Workflow Objects to run the workflow and Modify Warehouse Workflows to switch its schedule on. Your administrator grants these. See Warehouse permissions and row-level security.
Create a logical model and a source system
Every Dimension and Fact table names at least one logical model and one source system, and each source system sits inside a semantic source. You create them once and reuse them. If they already exist, skip to the next section.
Open Warehouse in the sidebar and click New > Semantic source. In the Semantic sources list, click Add. Fill in Source Name and Description, then click Create.
Select the new semantic source and click Add system. Fill in System Name and Description, then click Create.
Click New > Logical model, then Add Model. Fill in Model Name and Description, then click Create.
Open Dashboard, select the Playground view and click the + on the Warehouse card. Choose New dimension or New fact. You can also open Warehouse in the sidebar, click New and choose Dimension or Fact.
In General, enter a Name. Under Configuration, select at least one logical model and one source system. Click Apply.
Open Columns and add your columns. To copy them from a query, click the database icon with the tooltip Import columns from SQL script, paste a query that reads your Lake table, and click Import. The icon works once the General settings are saved. Mark at least one column as PK, the primary key. Until you do, the step shows: "At least one user-defined column must be marked as a Primary Key."
Open Settings and click Deploy in the banner. Deploying creates the table in the Warehouse with your current configuration. Settings then shows the Rows, Size and Partitions figures and a Transformation Runs list. The table is still empty. Preview is unavailable until the table is deployed.
Use a Dimension for things you describe, such as customers, and a Fact for events and measures, such as sales. For the full set of settings, see Create a dimension or fact table.
Write and test the transformation
In the Warehouse tree, right-click your table and choose Add Transformation. The table is already selected in Table. You can also click New > Transformation, or New transformation on the Warehouse card, and choose the table yourself.
Open Query, write the SQL that reads your Lake table, and click Run Query. Wait for Query validated.
Open Mapping. DataMind OS can map columns for you and shows Columns automatically mapped. Review the mapping and click Confirm. While a column has no source, the step shows: "Every active column must have at least one mapping to proceed". It also lists any mandatory column that is still unmapped.
Open Validation and click Test Now. In Test Transformation, pick a date and click Test Now. The message Test run started successfully confirms that the test started. The test fills separate test tables, never your real table.
When the test finishes, click Show Results on the latest run. In Test Results, check the Source Table and Target Output views. If Finalize Transformation is offered, click it, then Finalize in the dialog. You see Transformation finalized successfully, and the transformation has the status Ready.
Important
A workflow run skips a transformation that is not finalised. Until you finalise it, the Execution tab has no Execute Now button either.
Run the transformation
Open Execution and click Execute Now. The Execute Transformation dialog opens with a date that starts at yesterday. Its Transformation Info panel should show StatusReady. Click Execute. The button reads Running... while the run starts.
Follow the run in Run Log. When it has finished, open the table's Settings: Rows shows a count and Transformation Runs lists the run. Open Preview to see the rows.
Schedule the transformation
A new transformation runs in the Warehouse workflow named DWH_Workflow. Its schedule starts off, with the unit Day.
Open the transformation's General step, turn on Schedule and click Apply. A workflow runs the transformation only when it is Ready and its Schedule is on.
Open Workflows in the sidebar. Hover over the DWH_Workflow card and click the play button with the tooltip Run. In Run Workflow, keep the date and click Start. A workflow does not start on its schedule until it has run once.
On the card, click Off so that it reads On. The schedule now starts runs.
To run the transformation apart from the other Warehouse transformations, give it a workflow of its own. You need Add Warehouse Workflows.
Open Workflows and click the + button, which has the tooltip Add Workflow. At the top of the form, click Warehouse. The form then belongs to the Warehouse, so there is no datasource to pick.
Enter a Workflow Name. Under Schedule Settings, set Unit, Frequency, Schedule Day and Start Time. Under Workflow Settings, keep Schedule Status on, and click Create.
The Lake and Warehouse switch appears only if you are allowed to create Warehouse workflows.
Warning
The form opens with Unit set to Minute and Frequency set to 1. The workflow runs every minute until its End Time unless you change these. Set the unit you want before you click Create.
The schedule settings in the form are:
Setting
What it does
Default
Unit
How often the schedule repeats: Minute, Hour, Day or Month
Minute
Frequency
The number of units between runs, from 1 to 100
1
Schedule Day
A day number from 1 to 31 that the schedule uses
1
Start Time
When the schedule starts
00:00
End Time
When the schedule stops. Shown for Minute and Hour only
23:59
Schedule Status
Turns the workflow's schedule on or off
On
Ignore Error
Keeps the schedule starting runs after a failed or aborted run
Off
Batch Size
How many parts of one run can work at the same time
10
Restart Count
How many times a run restarts itself after a temporary database error
0
Check the result
Open Lineage and click Focus. In Search a table, Mind, model or deck…, type your table's name and select it. The map now shows only your table with its upstream items, including your Lake table, and its downstream items. Click Back to leave Focus.
Open Editor, choose your Role and find the table in Database Explorer. Write a query that selects from it and run it, as described in Run your first query. The rows you see come from the Warehouse table. You can also click Open in Editor on the transformation's Query step to open its SQL in a new console.
Your table now refreshes on the schedule you set. To learn how to watch for failed runs, continue with Keep it healthy.
Questions, answered
Why does my new workflow run every minute?
The form starts with Unit set to Minute and Frequency set to 1. Set Unit, Frequency, Schedule Day and Start Time before you click Create. For how each value decides when a run starts, see Schedules and when a workflow starts.
Why can't I set Frequency to 500?
Frequency accepts 1 to 100, and Schedule Day accepts 1 to 31. Enter a value in the range.
My test run shows no rows.
The test runs for the date you pick in Test Transformation. Click Test Now again and pick a date that has data for your query.
Why is there no Finalize step after my test run?
Finalize Transformation appears after a test run has finished successfully while the transformation is not yet Ready. If the transformation is already Ready, there is nothing to finalise. For what finalising does, see Build, test and finalize a transformation.
Why is the Preview tab unavailable?
Preview opens only after the table is deployed. Open Settings and click Deploy.
My table is still empty after the test run. Why?
A test never writes to your table. Run the transformation with Execute Now, or let its workflow run it.
My table did not update overnight.
Check that the workflow card reads On, that the workflow has run at least once, that the transformation is Ready, and that its Schedule is on in General. A workflow run skips a transformation whose Schedule is off. Check also that Unit, Frequency, Start Time and Timezone match the time you expected. Then read the runs and their status in Job runs.
How do I run the transformation now without waiting?
Open Execution and click Execute Now. A run you start this way does not need the transformation's Schedule to be on. To run the whole workflow instead, see Run a workflow by hand.
How do I check that my Warehouse table reads from the Lake table?
Open Lineage, click Focus and select the table. Check that your Lake table appears upstream of it on the map. For more, see Explore and trace lineage.