Delta Live Tables - Dynamic Target Schema

[MauricioS]

Hi all,

I have a requirement where I need to migrate a few jobs from standard databricks notebooks that are orchestrated by Azure Data Factory to DLT Pipelines, pretty straight forward so far. The tricky part is that the data tables in the catalog are in different schemas depending on the country, e.g. delta_mexico.{table_name}, delta_us.{table_name}, etc.

When started analyzing the effort of moving into DLT pipelines I saw that in the configuration the destination schema seems like a static value:

Can these settings be used with dynamic values depending on some parameters I pass into the pipeline? I would like to re-use code and control to which schema the table will be saved, is this possible or will I need to create a DLT pipeline per country since the target schema es static?

[Walter_C]

As of now there is no option to do this dynamically, based on this you will need to create it per country

[fmadeiro]

@MauricioS Great question!

Databricks Delta Live Tables (DLT) pipelines are very flexible, but by default, the target schema specified in the pipeline configuration (such as target or schema) is fixed. That said, you can implement strategies to enable dynamic schema selection based on parameters. One way to achieve this is by using pipeline parameters to dynamically determine the target schema in your code. Here's an example:

import dlt
import pyspark.sql.functions as F

# Retrieve the target schema from pipeline parameters
# Fallback to a default schema if the parameter is not provided
target_schema = spark.conf.get("pipeline.country_schema", "default_schema")

@dlt.table
def my_table():
    # Read source data
    df = spark.read.format("delta").load("path_to_source_data")
    # Add a processed date column
    return df.withColumn("processed_date", F.current_date())

# Dynamically create the table in the specified target schema
dlt.create_table(
    name=f"{target_schema}.my_table",
    comment="Example of dynamic schema selection",
    path=f"/mnt/delta/{target_schema}/my_table"
)

This approach lets you configure the pipeline to adjust the target schema dynamically while ensuring that default values and error handling keep it robust. Make sure the parameter (pipeline.country_schema) is defined in the pipeline configuration and that appropriate permissions are in place for all schemas being used.

[MauricioS]

hi @fmadeiro - thanks a lot for the reply!

I'm trying to use your solution, and weirdly the DLT Pipeline runs successfully, but, I'm not able to see the table in the Catalog. One quick question, in the pipeline settings should I leave blank the Storage and Target options?