Skip to main content

Autogenerating MATE dbt Sources from SOLE Ingestion Tables

You can use the ingestion tables you manage in SOLE configuration files as MATE dbt sources without manually adding new dbt sources every time you create a table with the same schema, columns, etc. The benefits of this approach are:

  • Faster onboarding of new data sources
  • Shorter time to go live for new projects
  • No duplication of effort

Sharing ingestion table definitions allows the

This mechanism is a huge productivity win as you don't have to duplicate your effort and create the table definition twice.

Prerequisites

Before sharing ingestion table definitions between engines, ensure you enable the autogeneration of MATE dbt sources.

Ingesting data

Declaring a table in SOLE and using it in MATE

To start sharing sources between SOLE and MATE, follow the below steps:

  1. Configure a table in the SOLE configuration file dataops/snowflake/databases.template.yml.

  2. Run a pipeline so that the SOLE job prepares the data needed by MATE to generate a new sources.yml file.

  3. Enable at least one MATE job to generate the source file.

    • The name of the generated file is suffixed by CI_PIPELINE_ID.
  4. To use the newly generated file in MATE and add the directory autogenerated_sources in the dbt_project.yml file:

    dataops/modelling/dbt_project.yml
    ## Project
    name: MyProject
    version: 0.1
    config-version: 2

    ## Sources
    model-paths: [models, sources, autogenerated_sources]

By adding autogenerated_sources as an additional sources directory, MATE finds and uses the new source file during the pipeline run.

tip

The new sources.yml file is available only during a pipeline run. It does not get hardcoded in your repository. See Capturing the generated source for more information.

The below examples show in detail how to ingest data from a single source system , ingest data from two source systems, declare MATE tests in SOLE, use tags to build specific tables in MATE, and generate a joined table of manual and autogenerated sources.

Ingesting data from a single source system

In this example, you want to ingest customer data from Salesforce:

/dataops/snowflake/databases.template.sql
databases:
CUSTOMER_DATA:
namespacing: none
orchestrators:
mate:
source_system: SALESFORCE
schemas:
STAGING:
tables:
CUSTOMER:
columns:
CUSTOMER_NAME:
type: INT
CUSTOMER_ADDRESS:
type: VARCHAR
PURCHASES:
columns:
PRODUCT_NAME:
type: VARCHAR
QUANTITY:
type: VARCHAR
PURCHASE_DATE:
type: DATE
PURCHASE_COUNT:
type: INT

Without further setup, you can immediately use the ingestion tables as MATE source tables.

/dataops/modelling/curation/customers.sql
SELECT
CUSTOMER_NAME,
CUSTOMER_ADDRESS
FROM {{ source('DATAOPS_STAGING_SALESFORCE', 'CUSTOMER') }}

The source's name always starts with the DATAOPS_ prefix, followed by the schema name. If source_system is provided, the value thereof is used as a postfix. In this example, STAGING is the schema name, SALESFORCE is the name of the source system, and CUSTOMER is the table name.

Ingesting data from two source systems

In this second example, you want to ingest customer data from Salesforce and SAP:

/dataops/snowflake/databases.template.sql
databases:
CUSTOMER_DATA:
namespacing: none
comment: This is the main DataOps database for environment {{ env.DATAOPS_ENV_NAME }}
orchestrators:
mate:
source_system: SALESFORCE
schemas:
STAGING:
tables:
CUSTOMER:
columns:
ID:
type: INT
CUSTOMER_NAME:
type: INT
CUSTOMER_ADDRESS:
type: VARCHAR
PURCHASES:
columns:
CUSTOMER_ID:
type: INT
PRODUCT_NAME:
type: VARCHAR
QUANTITY:
type: VARCHAR
PURCHASE_DATE:
type: DATE
PURCHASE_COUNT:
type: INT
DB:
namespacing: none
comment: this DB has the same schemas and tables as above, but the source_system key will ensure there is no name conflict
orchestrators:
mate:
source_system: SAP
schemas:
STAGING:
tables:
CUSTOMER:
columns:
ID:
type: INT
CUSTOMER_NAME:
type: INT
CUSTOMER_ADDRESS:
type: VARCHAR
PURCHASES:
columns:
CUSTOMER_ID:
type: INT
PRODUCT_NAME:
type: VARCHAR
QUANTITY:
type: VARCHAR
PURCHASE_DATE:
type: DATE
PURCHASE_COUNT:
type: INT

When ingesting from two different sources, a sample of the generated sources.yml file looks like this. Note the file name suffix CI_PIPELINE_ID:

/dataops/modelling/autogenerated_sources/sources_{CI_PIPELINE_ID}.yml
version: 2
sources:
- name: DATAOPS_STAGING_SALESFORCE
description: ""
database: CUSTOMER_DATA
schema: STAGING
tables:
- name: CUSTOMER
description: ""
columns:
- name: ID
description: ""
- name: CUSTOMER_NAME
description: ""
- name: CUSTOMER_ADDRESS
description: ""
- name: PURCHASES
description: ""
columns:
- name: CUSTOMER_ID
description: ""
- name: PRODUCT_NAME
description: ""
- name: QUANTITY
description: ""
- name: PURCHASE_DATE
description: ""
- name: PURCHASE_COUNT
description: ""
- name: DATAOPS_STAGING_SAP
description: ""
database: DB
schema: STAGING
tables:
- name: CUSTOMER
description: ""
columns:
- name: ID
description: ""
- name: CUSTOMER_NAME
description: ""
- name: CUSTOMER_ADDRESS
description: ""
- name: PURCHASES
description: ""
columns:
- name: CUSTOMER_ID
description: ""
- name: PRODUCT_NAME
description: ""
- name: QUANTITY
description: ""
- name: PURCHASE_DATE
description: ""
- name: PURCHASE_COUNT
description: ""

Without further setup, you can immediately use the ingestion tables as MATE source tables.

/dataops/modelling/curation/customers.sql
SELECT
CUSTOMER_NAME,
(SELECT COUNT(*) FROM {{ source('DATAOPS_STAGING_SAP', 'PURCHASES') }}
WHERE ID = CUSTOMER_ID)
AS "Number Purchases"

FROM {{ source('DATAOPS_STAGING_SALESFORCE', 'CUSTOMER') }}

The source's name always starts with the DATAOPS_ prefix, followed by the schema name. If source_system is provided, the value thereof is used as a postfix. In this example, STAGING is the schema name for the two sources, SALESFORCE and SAP are the names of the source systems, and PURCHASE and CUSTOMER are the table names.

Declaring MATE tests in SOLE

In this third example, you want to use the tags you define on columns in SOLE to run a MATE source testing stage independently for Salesforce and SAP by using a job that uses model selections via tags.

/dataops/snowflake/databases.template.sql
databases:
CUSTOMER_DATA:
namespacing: none
comment: This is the main DataOps database for environment {{ env.DATAOPS_ENV_NAME }}
orchestrators:
mate:
source_system: SALESFORCE
schemas:
STAGING:
tables:
CUSTOMER:
columns:
ID:
type: INT
tests:
- unique
orchestrators:
mate:
tags:
- src.salesforce
CUSTOMER_NAME:
type: INT
CUSTOMER_ADDRESS:
type: VARCHAR
PURCHASES:
columns:
CUSTOMER_ID:
type: INT
PRODUCT_NAME:
type: VARCHAR
QUANTITY:
type: VARCHAR
PURCHASE_DATE:
type: DATE
PURCHASE_COUNT:
type: INT
tests:
- unique
orchestrators:
mate:
tags:
- src.salesforce
DB:
namespacing: none
comment: this DB has the same schemas and tables as above, but the source_system key will ensure there is no name conflict
orchestrators:
mate:
source_system: SAP
schemas:
STAGING:
tables:
CUSTOMER:
columns:
ID:
type: INT
CUSTOMER_NAME:
type: INT
CUSTOMER_ADDRESS:
type: VARCHAR
PURCHASES:
columns:
CUSTOMER_ID:
type: INT
PRODUCT_NAME:
type: VARCHAR
QUANTITY:
type: VARCHAR
PURCHASE_DATE:
type: DATE
PURCHASE_COUNT:
type: INT

Without further setup, you can immediately use the tags you define on columns in SOLE to run a MATE source testing.

Test only Salesforce with tag:
extends:
- .modelling_and_transformation_base
- .agent_tag
variables:
TRANSFORM_ACTION: TEST
TRANSFORM_MODEL_SELECTOR: tag:src.salesforce
stage: Source Testing
script:
- /dataops
icon: ${TESTING_ICON}
artifacts:
when: always
reports:
junit: $CI_PROJECT_DIR/report.xml

The two tags src.salesforce applied to the columns ID and CUSTOMER_ID in the SALESFORCE source system are used to run the MATE source testing stage.

Results of running the test look like this:

docs-overview __shadow__

Using tags to build specific tables in MATE

In this third example, you want to use the tags you define on tables in SOLE to run a MATE source to build a specific table by using a job that uses model selections via tags.

/dataops/snowflake/databases.template.sql
databases:
CUSTOMER_DATA:
namespacing: none
comment: This is the main DataOps database for environment {{ env.DATAOPS_ENV_NAME }}
orchestrators:
mate:
source_system: SALESFORCE
schemas:
STAGING:
tables:
CUSTOMER:
orchestrators:
mate:
tags:
- sf_table_tag
columns:
ID:
type: INT
CUSTOMER_NAME:
type: INT
CUSTOMER_ADDRESS:
type: VARCHAR
PURCHASES:
orchestrators:
mate:
tags:
- sf_table_tag
columns:
CUSTOMER_ID:
type: INT
PRODUCT_NAME:
type: VARCHAR
QUANTITY:
type: VARCHAR
PURCHASE_DATE:
type: DATE
PURCHASE_COUNT:
type: INT
DB:
namespacing: none
comment: this DB has the same schemas and tables as above, but the `source_system` tag will ensure there is no name conflict
orchestrators:
mate:
source_system: SAP
schemas:
STAGING:
tables:
CUSTOMER:
columns:
ID:
type: INT
CUSTOMER_NAME:
type: INT
CUSTOMER_ADDRESS:
type: VARCHAR
PURCHASES:
columns:
CUSTOMER_ID:
type: INT
PRODUCT_NAME:
type: VARCHAR
QUANTITY:
type: VARCHAR
PURCHASE_DATE:
type: DATE
PURCHASE_COUNT:
type: INT

Without further setup, you can immediately use the tags you define on tables in SOLE to run a MATE source and build the table.

Build all Models:
extends:
- .modelling_and_transformation_base
- .agent_tag
variables:
TRANSFORM_ACTION: RUN
TRANSFORM_MODEL_SELECTOR: tag:sf_table_tag
stage: Data Transformation
script:
- /dataops
icon: ${TRANSFORM_ICON}

Capturing the generated source

Sometimes, you may need to check all the details of the generated file for troubleshooting purposes, for example. However, since the new sources.yml file is available only during a pipeline run, here are a few simple steps to capture the file:

  1. Export the file as an artifact or from the log of the MATE job.
  2. Upload the file to the project for additional configuration.
  3. Disable the ingestion tables sharing to avoid conflict.
  4. Subsequently, consume the new tables.

The example below shows how to set an export as an artifact condition:

""
Test all Sources:
extends:
- .modelling_and_transformation_base
- .agent_tag
variables:
TRANSFORM_ACTION: TEST
stage: Source Testing
script:
- /dataops
icon: ${TESTING_ICON}
artifacts:
paths:
- $CI_PROJECT_DIR/dataops/modelling/autogenerated_sources/

User-defined and autogenerated sources

Below is an example that shows the differences between a user-defined source with a freshness clause and an autogenerated source based on the same ingestion table:

version: 2
sources:
- name: MANUALLY_DEFINED_EXAMPLE_SCHEMA_SALESFORCE
freshness:
warn_after:
count: 1
period: day
description: ''
database: CUSTOMER_DATA
schema: EXAMPLE_SCHEMA
tables:
- name: PURCHASES
description: ''
columns:
- name: CUSTOMER_ID
description: ''
- name: PRODUCT_NAME
description: ''
- name: QUANTITY
description: ''
- name: PURCHASE_DATE
description: ''
- name: PURCHASE_COUNT
description: ''
tags:
- src.salesforce
tests:
- unique

--- sources{ci_pipeline_id}.yml
version: 2
sources:
- name: DATAOPS_EXAMPLE_SCHEMA_SALESFORCE
description: ''
database: CUSTOMER_DATA
schema: EXAMPLE_SCHEMA
tables:
- name: CUSTOMER
description: ''
columns:
- name: ID
description: ''
tags:
- src.salesforce
tests:
- unique
- name: CUSTOMER_NAME
description: ''
- name: CUSTOMER_ADDRESS
description: ''
- name: PURCHASES
description: ''
columns:
- name: CUSTOMER_ID
description: ''
- name: PRODUCT_NAME
description: ''
- name: QUANTITY
description: ''
- name: PURCHASE_DATE
description: ''
- name: PURCHASE_COUNT
description: ''
tags:
- src.salesforce
tests:
- unique
- name: DATAOPS_EXAMPLE_SCHEMA_SAP
description: ''
database: DB
schema: EXAMPLE_SCHEMA
tables:
- name: CUSTOMER
description: ''
columns:
- name: ID
description: ''
- name: CUSTOMER_NAME
description: ''
- name: CUSTOMER_ADDRESS
description: ''
- name: PURCHASES
description: ''
columns:
- name: CUSTOMER_ID
description: ''
- name: PRODUCT_NAME
description: ''
- name: QUANTITY
description: ''
- name: PURCHASE_DATE
description: ''
- name: PURCHASE_COUNT
description: ''

Without further setup, you can immediately generate a joined table of manual and autogenerated sources.

example_autogenerated_and_regular_source.sql
{{ config(alias='table_using_autogenerated_and_client_generated_source') -}}

SELECT
CUSTOMER_NAME,
PURCHASE_DATE

FROM {{ source('DATAOPS_EXAMPLE_SCHEMA_SALESFORCE', 'CUSTOMER') }}
JOIN {{ source('MANUALLY_DEFINED_EXAMPLE_SCHEMA_SALESFORCE', 'PURCHASES') }}
ON ID = CUSTOMER_ID

Enabling/Disabling the auto-generation of the MATE source

A new variable named DATAOPS_GENERATE_TRANSFORM_SOURCES has been implemented and set to 0 (False) by default.

Setting this variable to true allows the metadata of any table you create in the SOLE configuration file to be automatically generated and transformed into a ready-to-use MATE dbt sources.yml file. This file contains all the details from the configuration file databases.template.yml in the template project.

To enable autogeneration:

  1. Add the variable DATAOPS_GENERATE_TRANSFORM_SOURCES to the variables.yml file in your project settings.
  2. Set this variable to 1 (True).

Once set, each run of MATE automatically generates ready-to-use MATE sources from SOLE.

With the sources file generated, your next step is to give explicit permission to use the generated dbt sources in your MATE project by making a few changes to the dbt_project.yml file.

Head to ingesting data for the details.

To disable the autogeneration of the MATE sources and stop the sharing of ingestion table definitions, prevent the generation of the sources file by setting the variable DATAOPS_GENERATE_TRANSFORM_SOURCES to 0.