DBTimes
The frameworks help to implement a
data warehouse that follows the Kimball dimensional modeling approach.
Both frameworks automate three phases
of the data warehouse life cycle: loading into the staging area, the ETL
process, and reconciliation.
dwtools - JVM (Scala)
/ Apache Spark
|
dwtools libraries Concepts ·
The
libraries use a Configuration First approach, with the emphasis on
configuration as the customization method. ·
All
three libraries are independent from a usage standpoint, but are packaged
together in a single JAR (dwtools) for end-to-end data warehouse implementation.
Each library has its own configuration schema, set of rules, and APIs.
Spark Connect ·
The
libraries do not support Spark Connect because they rely on classic Spark
APIs (e.g., RDDs and SparkContext). Applications that
use them must run on a cluster with Dedicated access
mode. |
|
DW
Data Source Loader – loads data into
staging Parquet files. ·
Load
actions are described in a .json configuration file. ·
Features
include data versioning and incremental load. ·
Sources
include CSV or delimited files, SQL Server, Oracle, or MongoDB databases, or
a custom source. ·
Can
be extended to accept other sources. Documentation for DW Data Source Loader |
|
|
DW
ETL – implements ETL
to load dimensions and fact tables into Parquet files. ·
The
dimensional model is defined in a configuration file. ·
Supports
o
Creation
of surrogate keys on initial and consecutive loads o
Type
0, 1, 2, and 3 changes o
Initial
and consecutive loads o
Re-runs o
Unknown
keys investigation o
Transaction,
Periodic Snapshot, and Accumulating Snapshot fact tables |
|
|
|
DW
Data Source Comparer – a
reconciliation tool that compares two data sources. ·
Sources
can be Parquet or CSV files, or SQL Server, Oracle, or MongoDB data. ·
Produces
a CSV file with the differences. Documentation for DW Data Source Comparer |
|
|
Sample Implementation The sample
implementation (NFL Data Warehouse) has a separate Scala
application for each library. |
|
https://github.com/vshulman111/dwspark/tree/main/nfl_sample_dw_for_dwtools_2.12-3.3 – implementation
for dwtools_2.12-3.3 https://github.com/vshulman111/dwspark/tree/main/nfl_sample_dw_for_dwtools_2.13-4.0 – implementation
for dwtools_2.13-4.0 |
|
dwtools versions
|
|
dwtools_2.12-3.3 – Spark 3.3.2, Scala 2.12, Java 11 ·
Version
1.0.0 – released 2026-08-19 ·
Version
1.0.1 – released 2026-08-25 o Bug fix: new data
with a different effective date was overriding previous effective dates for a
non-versioned destination. dwtools_2.13-4.0 – Spark 4.0.4, Scala 2.13, Java 17 ·
Version
1.0.1 – released 2026-09-01 ·
Version
1.0.2 – released 2026-09-13 o Added data source
schema evolution to DW Data Source Loader. ·
Version
1.0.3 – released 2026-09-24 o Added an EffectiveDate metadata column to non-versioned destinations
with a unique key. ·
Version
1.1.0 – released 2026-10-07 o Fixed the logic
that determines which effective dates to process on consecutive runs of the
ETL job. |
|
|
|
|
.NET (C#) / SQL Server / SSAS
|
Download the code: SQLServerDotNetFramework.zip |
||
|
Components included |
.NET project – applications |
·
Data Source Loader – loads data into
versioned staging tables in SQL Server. Load actions are described in an .xml
configuration file. The source can be .csv or
delimited files, SQL Server tables, views, or stored procedures, or SQL code. |
|
·
ETL Runner – runs ETL to
load dimensions, fact tables, and the SSAS cube. For each of the three
stages, the load can be full or incremental. It has many other features. |
||
|
·
Data Comparer – used for
reconciliation. |
||
|
SQL Server – Implementation
of a sample data warehouse – NFL Games. About 50% of the code is generic; the
other 50% is used as a template to develop the ETL for a specific dimensional
model. |
·
Sample stored procedures to implement dimension
loads. |
|
|
·
Sample stored
procedures to implement fact loads. |
||
|
·
Generic stored procedures to implement SSAS loads. |
||