Build a Temporal Data Solution – The Storage of Data

1. Log in to the Azure portal at https://portal.azure.com ➢ navigate to the Azure SQL you created in Exercise 2.1 ➢ on the Overview blade, choose the Set Server Firewall menu item ➢ confirm that your client IP address is allowed ➢ navigate back to the Overview blade ➢ choose the Connect with… menu item ➢ select Azure Data Studio and follow the instructions to install it, if it is not already installed ➢ and then connect to your Azure SQL database.
2. Right‐click the connection ➢ select New Query from the pop‐up menu ➢ and then execute the following SQL statement to create a new schema:
   CREATE SCHEMA brainwaves

AUTHORIZATION dbo

3. Execute the following SQL statement, which creates a temporal table. A file named createTemporalTables.sql, which contains all temporal tables, is located in the Chapter04/Ch04Ex08 directory on GitHub. All tables must be created; only one statement is provided for brevity.

4. Populate the temporal tables using the INSERT commands in the file populateTemporalTables.sql in the Chapter04/Ch04Ex08 directory on GitHub ➢ and then execute the following SQL queries to observe the inserted data. The next two sets of queries are in the file selectTemporalData.sql.
   SELECT * FROM [brainwaves].[DimMODE]

5. Execute the following SQL statements to view the contents of the history table. Note the unique numbers at the end of the history tables. Replace the ########## with your numbers, which you can find by expanding the temporal table, as shown in Figure 4.31. Since there have not been changes, the history tables are empty.
   SELECT * FROM [brainwaves].[MSSQL_TemporalHistoryFor_##########] –DimMODE

FIGURE 4.31 Finding the history table

6. Note the current time, and then execute updates to the temporal tables. For more UPDATE statements, see the file modifyTemporalTable.sql, which includes some examples for you to use, located in the Chapter04/Ch04Ex08 directory on GitHub at https://github.com/benperk/ADE. Then query the history tables again, as you did in step 5:
   UPDATE [brainwaves].[DimSCENARIO] SET SCENARIO = ‘Flipboard’

WHERE SCENARIO_ID = 2

7. Using the time that you executed the UPDATE statement in step 6 as a reference ➢ execute the following SQL statements. The first SELECT uses a timestamp before you executed the UPDATE queries, and the second SELECT is a time after the queries were executed. The queries and the result are shown in the following text:

SELECT *

FROM [brainwaves].[DimFREQUENCY] FOR SYSTEM_TIME AS OF ‘2022-04-06 15:58:56’

SELECT *

  FROM [brainwaves].[DimFREQUENCY] FOR SYSTEM_TIME AS OF ‘2022-04-06 16:00:00’

The differences between creating and working with a normal table versus a temporal table solution on Azure SQL, as shown in Figure 4.32, have to do primarily with two characteristics.

First, when you create the table itself, bear in mind that a temporal table needs to be versioned and include the required SQL clauses, for example, a start and end date with a data type of DATETIME2 and a PERIOD FOR SYSTEM_TIME SQL statement. The datetimes are passed as parameters to determine the period for system time. The other characteristic is the querying of the history table, which requires a datetime passed to the FOR SYSTEM_TIME AS OF SQL statement. No changes are required for performing regular CRUD activities on a temporal table, and the versioning happens without requiring any action as well. As you learned, there is no history created with an INSERT, because the one initially created is always most current. Only with an UPDATE or DELETE will a row be written to the history table. The action required to add the versioning data to the history table is performed by the DBMS.

FIGURE 4.32 A temporal data solution

If you think that temporal tables seem a bit like slowly changing dimension (SCD) tables, you are right. Both table types are intended to provide a history of changes that happen on tables that do not change very often. And as you learned in Chapter 3, there are numerous types of SCD tables, which provide different historical information. Table 4.6 summarizes the different SCD types. This is a very important concept, and you can expect questions on the exam about this.

TABLE 4.6  Slowly changing dimension types

In Exercise 4.10 you implement a slowly changing dimension table. As you work through the exercise, try to determine which type is being implemented.