Integrate data from Microsoft SQL Server to Databricks using Matillion

Creating a User in Microsoft SQL Server

The following instructions demonstrate how to create a user in a Microsoft SQL Server database. These steps cover both the authentication at the SQL Server instance level (login) and the authorization at the database level (user).

1. Create a Login at the Server Level

A login is required for authentication to connect to SQL Server. You can create either a SQL Server-authenticated login or a Windows-authenticated login.

Example 1: SQL Server Authentication

CREATE LOGIN my_new_login WITH PASSWORD = 'StrongPassword!2024';

Example 2: Windows Authentication

CREATE LOGIN [DOMAIN\username] FROM WINDOWS;

2. Create a Database User for the Login

Once the login exists, create a corresponding database user in a specific database, which allows the login to connect and access that database.

``` -- Switch context to your database USE [YourDatabase]; GO

CREATE USER [my_new_user] FOR LOGIN [my_new_login]; ```

Note: The database user name (

my_new_user

) does not have to match the login name, but it is common practice for clarity.

3. (Optional) Grant Permissions or Assign Roles

You may grant permissions directly or add the user to a predefined database role.

Example: Add User to db_datareader Role

ALTER ROLE db_datareader ADD MEMBER [my_new_user];

Example: Grant Specific Permissions

GRANT SELECT, INSERT ON dbo.YourTable TO [my_new_user];

4. Verify the User

You can verify the newly created user with the following query:

SELECT name, type_desc
FROM sys.database_principals
WHERE name = 'my_new_user';

Cautions: - Always use a strong password for SQL Server authentication. - Restrict privileges based on the principle of least privilege. - Database names, logins, and users are case-sensitive on some collations—use consistent naming.

Checking the JDBC driver

The JDBC driver for Microsoft SQL Server is already installed in Matillion Data Productivity Cloud, so there is no need to download or install it manually.

To confirm that the driver is present:

1. Open Matillion Data Productivity Cloud and create a new Orchestration pipeline.
2. Add a Database Query component to your pipeline.
3. When configuring the Database Query component, click the dropdown list next to Database Type.
4. Check the list for Microsoft SQL Server. If it appears in the dropdown, the JDBC driver is installed and available for use.

If you need to check the version of the installed JDBC driver, refer to the instructions provided at the following URL:
https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/#database-driver-versions

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your Microsoft SQL Server database, you must configure the database to accept incoming connections from the appropriate sources, depending on your deployment:

• Full SaaS Agent Configuration:
  Allow incoming connections from the IP addresses listed at: Matillion Security Documentation – Allowing IP Addresses. These IP ranges are used by Matillion’s Fully Managed (SaaS) agents to establish connections to your database.

• Hybrid SaaS Deployment:
  Allow incoming connections from your own virtual private cloud (VPC). The specific IP addresses will depend on your VPC configuration. For assistance checking connectivity from your VPC, you can use the network utilities available at: Matillion Exchange – Check Network Access.

Additionally, if your Microsoft SQL Server database is referenced using a hostname or DNS address, ensure that the Full SaaS or Hybrid SaaS agent can successfully resolve that DNS address to the appropriate IP. This is necessary for establishing a successful connection.

Querying Data from Microsoft SQL Server

This guide provides best practices for querying data from a Microsoft SQL Server database, with an emphasis on once-off initial loads and incremental loads—common strategies when integrating with data platforms such as Databricks. Example SQL queries are provided for both scenarios.

1. Sample SQL SELECT Statements

``` -- Query all columns from the Employees table SELECT * FROM dbo.Employees;

-- Query specific columns with a filter SELECT EmployeeID, FirstName, LastName FROM dbo.Employees WHERE Department = 'Sales';

-- Aggregate data: count employees per department SELECT Department, COUNT(*) as EmployeeCount FROM dbo.Employees GROUP BY Department;

-- Using ORDER BY and limiting results SELECT TOP 10 * FROM dbo.Employees ORDER BY HireDate DESC; ```

2. Datatype Conversion Considerations

When extracting data from SQL Server and loading it into Databricks, be aware that datatype conversion may take place. Some SQL Server datatypes (e.g.,

datetime

nvarchar

money

• Best Practice: Always review schema mappings post-ingestion to ensure that types like

  DATE

  ,

  TIMESTAMP

  , and encoded strings are preserved or transformed as needed.
• Reference: Databricks documentation on Data Type Mapping

3. Initial Load vs. Incremental Load Pattern

The recommended approach is to perform a full (initial) load of the data first, followed by subsequent incremental loads to maintain current data with minimal processing.

a. Initial Load (No Filter)

For the initial load, retrieve all data (no WHERE clause).

SELECT * FROM dbo.Orders;

Database Query Component:
- Table:

dbo.Orders

b. Incremental Load (With Filter)

Incremental loads fetch only new or changed records using a filter—typically based on a datetime or incrementing key.

Example using a timestamp column (

LastModifiedDate

SELECT *
FROM dbo.Orders
WHERE LastModifiedDate > '2024-06-01T00:00:00';

• Update the filter value (watermark) after each successful load so that only new records are fetched in future increments.

Database Query Component:
- Table:

dbo.Orders

LastModifiedDate > ?

See Matillion's article on incremental load strategies for details on filter patterns.

4. Using the Same Database Query Component

Use the same Database Query component for both initial and incremental loads.
- During the initial load, execute the query without a WHERE clause. - For each incremental run, add the filter (e.g., WHERE condition based on the high-watermark column). - This ensures consistency in your pipeline and simplifies management.

References:

• Matillion Incremental Load Strategies

Data Integration Architecture

Loading data in advance of integration—an approach central to the Extract, Load, Transform (ELT) architecture—enables teams to divide and conquer the data integration challenge by splitting it into two manageable steps. By first loading raw data into the target Databricks database and subsequently performing integration and transformation, organizations can decouple the challenges of data movement from those of data enrichment. Data integration inherently requires transformation processes—ranging from data cleaning and normalization to more complex business logic—and the most effective way to manage these is through automated data transformation pipelines. A key advantage of the ELT architecture lies in performing both transformation and integration steps directly inside the Databricks environment. This not only eliminates the need for additional data processing infrastructure, but also leverages the inherent speed, flexibility, and scalability of Databricks, allowing transformations to be executed on-demand and at scale.