Exporting to Project Database

Monarch has a built-in DAG to export its project tables to an external Postgresql or Microsoft SQL Server database

Overview

DAG export_project_database will export specific tables and schemas as well as reports to a client's on-premise database so they have a record of data that was moved.

Setup

DB Secrets

The DAG expects to be able to import a CustomSecretsBackend class from custom_secrets_backend.py in the custom directory of the project. The class must implement a get_variable function which has the ability to return the export_db_connection object:

Example custom_secrets_backend.py:

import json
import os


class CustomSecretsBackend:
    @staticmethod
    def get_variable(key: str) -> str | None:
        base_path = os.path.dirname(os.path.abspath(__file__))
        with open(f"{base_path}/custom_secrets.json") as file:
            secrets = json.load(file)
        if key in secrets:
            print(f"Secret {key} found in custom secrets")
            secret = secrets[key]
            return json.dumps(secret)
        else:
            print(f"Secret {key} not found in custom secrets")
            return None

Example custom_secrets.json:

{
    "export_db_connection": {
        "engine": "mssql",
        "user": "sa",
        "password": "examplepassword",
        "host": "hostname",
        "port": "1433",
        "database": "master",
        "reports_schema": "dbo",
        "table_prefix": ""
    }
}

A few notes about this connection object:

• user, password, host, port, and database are all database connection variables
• engine: Can either be postgresql or mssql. Only Postgresql and Microsoft SQL Server are supported as target databases.
• reports_schema: The target schema where reports will be exported
• table_prefix: Will be prepended to the name of the report in the target schema. It is optional.

Monarch Config

export_db_config

This config defines which tables to include or exclude for a given schema:

• schema: The Monarch schema to export (e.g. landing, raw, dbt_transform)
• include_tables: The Monarch tables to include. If this column is populated, the export will only include these tables.
• exclude_tables: The Monarch tables to exclude. If this column is populated, the export will include all tables in the schema except for these.
• target_schema: The target schema where the tables will be exported. Defaults to the value of the schema column if omitted.
• enabled: Whether or not to include this schema for export

Only one of include_tables and exclude_tables can be populated at once. If include_tables is populated, the DAG will export only the listed tables. If exclude_tables is populated, the DAG will export all tables in the schema except for the listed tables. If neither are populated, the DAG will export all tables in the schema.

table_options

This config defines which columns in a given table should be included or excluded.

• schema_name: The Monarch schema to export (e.g. landing, raw, dbt_transform)
• table: The name of the Monarch table to export
• include_columns: The columns to include. If this config column is populated, the exported table will only include these columns.
• exclude_columns: The columns to exclude. If this config column is populated, the exported table will include all columns except for these.
• target_table: The name of the target table. Defaults to the value of the table column if omitted.
• enabled: Whether or not to include this configuration for the table

Only one of include_columns and exclude_columns can be populated at once. If include_columns is populated, the export for the table will include only the listed columns. If exclude_columns is populated, the export will include all columns in the table except for the listed ones. If neither are populated, the export will include all columns in the table.

Running

Once you've set up the custom secrets backend and configs, run DAG export_project_database