Skip to main content

Data Warehouse Data Extractor

Overview

The Data Warehouse Extractor is a system that extracts the processed data from the Zone Data Warehouse into CSV files. The system will examine all of the tables and views (objects) in the dbo schema and produce at least one file for each. 

 

File Information

Details about the files that will be generated.

File Format

The system produces CSV files that adhere to the RFC-4180 specification for CSV files. 

  • Header Row: Each file will contain a header row
  • Line Terminator: CRLF - Each record is located on a separate line, delimited by a line break
  • Field Terminator: , - Fields are separated by commas
  • Quote Identifier: " - Text fields may or may not be enclosed in double quotes
  • Escape Character: " If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.

File Types

The system will produce two types of files. 
 

  • Full - Contains all of the data from the source object.
  • Partial - Contains a portion of the records for the source object. If a source table or view has a column named FilePart then the system will split the data up into separate files for each distinct FilePart value. All of the partial files combined will represent the entire dataset for the object.

Note, a Partial will be generated for each object where the FilePart is null. This file may or may not contain any data rows.

 

File Names

The file names will be generated with this convention <FileType>_<SourceSchema>_<SourceObject>[_<FilePart>].csv where the portion in brackets is optional and only appears for Partial files. Examples:
 

  • full_dbo_COA.csv - Full file for the [dbo].[COA] object.
  • full_dbo_class.csv - Full file for the [dbo].[class] object.
  • partial_dbo_translines_fx_fx1.csv - Partial file for the [dbo].[translines_fx] object containing rows where [FilePart] = fx1
  • partial_dbo_translines_fx_.csv - Partial file for the [dbo].[translines_fx] object containing rows where [FilePart] is null

File Location

The files will be created in the same Azure Blob Storage account where the SQL Data Warehouse process the files from TacticalConnect. By default the system will create the files in a container named dw_export. The system will overwrite any files with the same name name that already exist in this container. The system will not remove any files from this container. It is up to the customer to remove files if they are no longer needed. Zone recommends that the consumer of these files remove them from the container once they have an opportunity to process them to prevent issues with stale data. 

Note: If the files are not removed, then it could be possible to see stale data files in the dw_export storage container. This could happen if the source data is re-partitioned into different FileParts and a FilePart is no longer used. The system will only generate files for FileParts that exist. If old files are not removed then a partial file for a FilePart that no longer exists may remain in this container.

 

Object Overrides

By default, the system will export every object (table or view) in the dbo schema of the Zone Data Warehouse. This may produce redundant files in the case where a view exists that contains all the data on one of the tables. To prevent unnecessary work, the system has an Override mechanism to exclude objects. The default configuration will exclude the base tables of the standard Data Warehouse implementation for tables where a view already exists.

Exclusion Override

For example, transaction header data is loaded into a transaction_header table. A view named vw_TransactionHeader has also been created that uses the transaction_header table and adds some additional columns. The presence of the view makes the transaction_header table redundant. 
To exclude the transaction_header table from the extraction process an entry should be added to the ExportConfigurationOverride table with the following values:

  • ExplicitIncludeExclude: Exclude - Denotes to exclude the object
  • ExportType: Partial - Because [transaction_header] contains a [FilePart] column, the system would produce Partial files.
  • ExportFileName: partial_dbo_transaction_header following the format above without the file extension
  • SchemaName: dbo - The name of the schema for the source object. Used to match exclusions
  • ObjectName: transaction_header - The name of the source object. Used to match exclusions

System Components

There are four components to the extraction system.
 

  • Zone Data Warehouse - The SQL Data Warehouse where the data is processed. This is the source source system. 
  • Azure Blob Storage - The blob storage destination for the files. This blob storage may be internal, within the Zone Azure tenant, or external. It must be the same blob storage where files from TacticalConnect are processed into the Zone Data Warehouse. 
  • Azure Data Factory - ADF is used to extract the data from the Zone Data Warehouse source and save it into the files that land in the Azure Blob Storage destination. 
  • ZoneFlow - The orchestration engine will execute the ADF pipeline at the appropriate time.

Implementation Steps

The system is designed to be easily deployed in the Zone Datawarehouse tenant. The Zone Data Warehouse and Azure Blob Storage components will already exist as they are standard with every Data Warehouse implementation.

Deploy ADF

To create the Azure Data Factory component, run the Deploy DW Extractor Azure DevOps pipeline. This pipeline will create the ADF resource and deploy the components that it requires.

Note: If an ADF instance already exists in the customer's resource group, it will be used, otherwise a new ADF resource will be created.

Direct configuration of the ADF components should not be necessary.

 

Configure Azure Storage

  • Create the Blob Storage Container if it does not already exist and create a SAS token for the customer to use to connect. Use this PowerShell script and the Storage Account credentials from 1Password to generate the SAS Token.
[string]$storageAccountName = ''
[string]$storageAccountKey = ''
[string]$containerName = "dw-export"

$context = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey

$permissions = "rld"  # r = read, l = list, d = delete
$expiry = "2100-01-01"

# Ensure the container exists; create it if missing
$container = Get-AzStorageContainer -Name $containerName -Context $context -ErrorAction SilentlyContinue
if (-not $container) {
    Write-Host "Container '$containerName' not found. Creating..."
    New-AzStorageContainer -Name $containerName -Context $context -Permission Off | Out-Null
    Write-Host "Container '$containerName' created."
}else {Write-Host "Container '$containerName' already exists."}

# Create SAS token scoped to the container
$containerSasToken = New-AzStorageContainerSASToken -Name $containerName -Context $context -Permission $permissions -ExpiryTime $expiry -FullUri

# Output the full SAS URL
Write-Host "Container SAS URL:
" $containerSasToken
  • Save the token into 1Password and provide it to the customer through a secure message. Do not email.

Note: The SAS URL returned by the PowerShell contains the SAS token and additional information. Strictly speaking, the token is everything from the question mark to the end, including the question mark.

 

Database Project Configuration

The SQL Database Project must be configured to enable the system.

  • In Visual Studio, open the project for the customer.
  • If not done previously, create a new feature branch.
  • In the PostDeploy folder, update the Script.PostDeployment1.sql file to uncomment the line:
	:r .\SeedExportConfigurationOverride.sql

Note: If the line does not exist, contact the product engineering team to have the customer project upgraded.

  • In the data folder update the following files:
    • ExportConfigurationOverride.xml - Review the views in the dbo schema and add exclusions if needed.
    • FlowTasks.xml
      • Add a step to FlowId 1 just after the Process Jobs step to execute the ADF pipeline.
      • Copy the Resource id from the Azure Portal ADF properties page for the ADF resource and paste it where indicated within the Parameters
      • Validate the `Ordinal` is correct for this task and any others that may come after. Duplicate Ordinal values are not allowed! 
  <row>
    <FlowId>1</FlowId>
    <Name>DW Data Extractor</Name>
    <Type>RunAdfPipeline</Type>
    <Parameters> {"DataFactoryResourceId":"Paste_ADF_ResourceID_Here","Pipeline":"DW_Extract_Master"}</Parameters>
    <Ordinal>7</Ordinal>
    <ContinueOnError>1</ContinueOnError>
  </row>
  • Once the files are updated, submit a Pull Request through the normal process.
  • Once the Pull Request has been merged and the Build & Publish Customer DW pipeline has completed building the solution, restart ZoneFlow.
  • Wait for ZoneFlow to complete the Deploy Database step
  • Because this changes the FlowTasks, restart ZoneFlow once more.

 

 

Monitoring

The system logs activities into the ExportLog table and saves detailed messages into the ExportLogMessages table. Please use the UpdateLog.sql script to monitor execution progress and view error messages. 

 

Was this article helpful?