Interface tables example: Multi-database pipeline
This tutorial walks you through a complete example of using interface tables to document a multi-database data pipeline with cross-database lineage.
For column reference, required fields, and valid object types, see Interface Tables Reference.
Scenario
A company has data flowing through four systems:
Real-world examples of this pattern:
- A CRM export feeds a storefront database, then a nightly ETL loads a warehouse
- A custom app pushes contacts to an operational database, then a transformation job builds a warehouse
- An on-prem ERP lands in a reporting database, then a BI mart publishes daily views
Step 1: Clear previous data (optional)
Delete in this order to respect foreign key constraints:
-- Clean up before inserting new data
-- IMPORTANT: Delete in this order to avoid FK constraint violations
-- 1. Lineage processes (references lineage)
DELETE FROM import_data_lineage_processes
WHERE processor_database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 2. Lineage (references objects in multiple databases)
DELETE FROM import_data_lineage
WHERE source_database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog')
OR target_database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog')
OR processor_database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 3. Linked sources
DELETE FROM import_linked_sources
WHERE database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 4. Reports
DELETE FROM import_reports
WHERE database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 5. Parameters (references procedures)
DELETE FROM import_parameters
WHERE database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 6. Procedures
DELETE FROM import_procedures
WHERE database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 7. Foreign keys (references tables)
DELETE FROM import_tables_foreign_keys_columns
WHERE database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 8. Primary/unique keys (references tables)
DELETE FROM import_tables_keys_columns
WHERE database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 9. Triggers (references tables)
DELETE FROM import_triggers
WHERE database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 10. Columns (references tables and reports)
DELETE FROM import_columns
WHERE database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
-- 11. Tables (base objects)
DELETE FROM import_tables
WHERE database_name IN ('CRM_Source', 'Ecommerce', 'Analytics_DW', 'Reporting_Catalog');
Step 2: Import tables and views
Add tables, views, and structures to import_tables. See Database objects reference for all valid object_type and object_subtype values.
INSERT INTO import_tables
(database_name, table_schema, table_name, object_type, object_subtype, description, field1)
VALUES
----------------------------
-- DATABASE 1: CRM_Source --
----------------------------
('CRM_Source', 'crm', 'contacts', 'TABLE', 'TABLE',
'CRM contacts - source of customer data.',
'Production'),
('CRM_Source', 'crm', 'accounts', 'TABLE', 'TABLE',
'Company accounts in CRM system.',
'Production'),
('CRM_Source', 'crm', 'interactions', 'TABLE', 'TABLE',
'Customer interaction history.',
'Production'),
--------------------------
-- DATABASE 2: Ecommerce --
--------------------------
('Ecommerce', 'sales', 'customers', 'TABLE', 'TABLE',
'Master table storing customer information. Synced from CRM_Source.',
'Production'),
('Ecommerce', 'sales', 'orders', 'TABLE', 'TABLE',
'Stores all customer orders with status tracking.',
'Production'),
('Ecommerce', 'sales', 'order_items', 'TABLE', 'TABLE',
'Line items for each order, linking orders to products.',
'Production'),
('Ecommerce', 'inventory', 'products', 'TABLE', 'TABLE',
'Product catalog with pricing and stock information.',
'Production'),
('Ecommerce', 'inventory', 'categories', 'TABLE', 'TABLE',
'Product categories for catalog organization.',
'Production'),
------------------------------
-- DATABASE 3: Analytics_DW --
------------------------------
('Analytics_DW', 'dim', 'dim_customer', 'TABLE', 'TABLE',
'Customer dimension - slowly changing dimension type 2.',
'Production'),
('Analytics_DW', 'dim', 'dim_product', 'TABLE', 'TABLE',
'Product dimension with category hierarchy.',
'Production'),
('Analytics_DW', 'dim', 'dim_date', 'TABLE', 'TABLE',
'Date dimension for time-based analysis.',
'Production'),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'TABLE',
'Sales fact table - grain is one row per order line item.',
'Production'),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'TABLE',
'Staging table for order data before transformation.',
'ETL');
-- Views with definition
INSERT INTO import_tables
(database_name, table_schema, table_name, object_type, object_subtype, definition, description, field1)
VALUES
('Ecommerce', 'reports', 'vw_order_summary', 'VIEW', 'VIEW',
'CREATE VIEW [reports].[vw_order_summary]
AS
SELECT
c.customer_id,
c.full_name AS customer_name,
COUNT(o.order_id) AS total_orders,
SUM(o.total_amount) AS total_spent
FROM [sales].[customers] c
LEFT JOIN [sales].[orders] o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.full_name;',
'Aggregated view showing order totals per customer.',
'Production'),
('Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'VIEW',
'CREATE VIEW [reports].[rpt_daily_sales]
AS
SELECT
d.full_date AS sale_date,
COUNT(DISTINCT f.order_id) AS total_orders,
SUM(f.quantity) AS total_quantity,
SUM(f.total_amount) AS total_revenue
FROM [fact].[fact_sales] f
INNER JOIN [dim].[dim_date] d
ON f.date_key = d.date_key
GROUP BY d.full_date;',
'Daily sales report aggregated from fact_sales.',
'Production');
Step 3: Import columns
Add columns for each table and view. See import_columns reference for all available columns.
INSERT INTO import_columns
(database_name, object_schema, object_name, object_object_type,
column_name, ordinal_position, datatype, nullable, is_identity, is_computed, description, field1)
VALUES
----------------------------
-- DATABASE 1: CRM_Source --
----------------------------
-- contacts table
('CRM_Source', 'crm', 'contacts', 'TABLE',
'contact_id', 1, 'int', 0, 1, 0, 'Unique contact identifier.', NULL),
('CRM_Source', 'crm', 'contacts', 'TABLE',
'email_address', 2, 'varchar(255)', 0, 0, 0, 'Contact email.', 'PII'),
('CRM_Source', 'crm', 'contacts', 'TABLE',
'first_name', 3, 'varchar(100)', 0, 0, 0, 'Contact first name.', 'PII'),
('CRM_Source', 'crm', 'contacts', 'TABLE',
'last_name', 4, 'varchar(100)', 0, 0, 0, 'Contact last name.', 'PII'),
('CRM_Source', 'crm', 'contacts', 'TABLE',
'account_id', 5, 'int', 1, 0, 0, 'Reference to company account.', NULL),
('CRM_Source', 'crm', 'contacts', 'TABLE',
'created_date', 6, 'datetime', 0, 0, 0, 'Record creation date.', NULL),
-- accounts table
('CRM_Source', 'crm', 'accounts', 'TABLE',
'account_id', 1, 'int', 0, 1, 0, 'Unique account identifier.', NULL),
('CRM_Source', 'crm', 'accounts', 'TABLE',
'company_name', 2, 'varchar(200)', 0, 0, 0, 'Company name.', NULL),
('CRM_Source', 'crm', 'accounts', 'TABLE',
'industry', 3, 'varchar(100)', 1, 0, 0, 'Industry classification.', NULL),
-- interactions table
('CRM_Source', 'crm', 'interactions', 'TABLE',
'interaction_id', 1, 'int', 0, 1, 0, 'Unique interaction identifier.', NULL),
('CRM_Source', 'crm', 'interactions', 'TABLE',
'contact_id', 2, 'int', 0, 0, 0, 'Reference to contact.', NULL),
('CRM_Source', 'crm', 'interactions', 'TABLE',
'interaction_type', 3, 'varchar(50)', 0, 0, 0, 'Type: call, email, meeting, purchase_intent.', NULL),
('CRM_Source', 'crm', 'interactions', 'TABLE',
'interaction_date', 4, 'datetime', 0, 0, 0, 'When interaction occurred.', NULL),
('CRM_Source', 'crm', 'interactions', 'TABLE',
'notes', 5, 'nvarchar(max)', 1, 0, 0, 'Interaction notes and details.', NULL),
('CRM_Source', 'crm', 'interactions', 'TABLE',
'lead_score', 6, 'int', 1, 0, 0, 'Lead scoring value from interaction.', NULL),
--------------------------
-- DATABASE 2: Ecommerce --
--------------------------
-- customers table
('Ecommerce', 'sales', 'customers', 'TABLE',
'customer_id', 1, 'int', 0, 1, 0, 'Unique customer identifier, auto-generated.', 'PII'),
('Ecommerce', 'sales', 'customers', 'TABLE',
'email', 2, 'varchar(255)', 0, 0, 0, 'Customer email address, used for login.', 'PII'),
('Ecommerce', 'sales', 'customers', 'TABLE',
'full_name', 3, 'varchar(200)', 0, 0, 0, 'Customer full name.', 'PII'),
('Ecommerce', 'sales', 'customers', 'TABLE',
'crm_contact_id', 4, 'int', 1, 0, 0, 'Reference to CRM_Source contact.', NULL),
('Ecommerce', 'sales', 'customers', 'TABLE',
'created_at', 5, 'datetime', 0, 0, 0, 'Registration timestamp.', NULL),
-- orders table
('Ecommerce', 'sales', 'orders', 'TABLE',
'order_id', 1, 'int', 0, 1, 0, 'Unique order identifier.', NULL),
('Ecommerce', 'sales', 'orders', 'TABLE',
'customer_id', 2, 'int', 0, 0, 0, 'Reference to customer.', NULL),
('Ecommerce', 'sales', 'orders', 'TABLE',
'order_date', 3, 'datetime', 0, 0, 0, 'Date and time when order was placed.', NULL),
('Ecommerce', 'sales', 'orders', 'TABLE',
'status', 4, 'varchar(20)', 0, 0, 0, 'Order status: pending, shipped, delivered, cancelled.', NULL),
('Ecommerce', 'sales', 'orders', 'TABLE',
'total_amount', 5, 'decimal(10,2)', 0, 0, 0, 'Total order value including tax.', NULL),
('Ecommerce', 'sales', 'orders', 'TABLE',
'crm_account_id', 6, 'int', 1, 0, 0, 'Reference to CRM account for B2B orders.', NULL),
('Ecommerce', 'sales', 'orders', 'TABLE',
'source_interaction_id', 7, 'int', 1, 0, 0, 'CRM interaction that triggered this order.', NULL),
-- order_items table
('Ecommerce', 'sales', 'order_items', 'TABLE',
'order_item_id', 1, 'int', 0, 1, 0, 'Unique line item identifier.', NULL),
('Ecommerce', 'sales', 'order_items', 'TABLE',
'order_id', 2, 'int', 0, 0, 0, 'Reference to parent order.', NULL),
('Ecommerce', 'sales', 'order_items', 'TABLE',
'product_id', 3, 'int', 0, 0, 0, 'Reference to purchased product.', NULL),
('Ecommerce', 'sales', 'order_items', 'TABLE',
'quantity', 4, 'int', 0, 0, 0, 'Number of units ordered.', NULL),
('Ecommerce', 'sales', 'order_items', 'TABLE',
'unit_price', 5, 'decimal(10,2)', 0, 0, 0, 'Price per unit at time of purchase.', NULL),
-- products table
('Ecommerce', 'inventory', 'products', 'TABLE',
'product_id', 1, 'int', 0, 1, 0, 'Unique product identifier.', NULL),
('Ecommerce', 'inventory', 'products', 'TABLE',
'category_id', 2, 'int', 1, 0, 0, 'Reference to product category.', NULL),
('Ecommerce', 'inventory', 'products', 'TABLE',
'name', 3, 'varchar(200)', 0, 0, 0, 'Product display name.', NULL),
('Ecommerce', 'inventory', 'products', 'TABLE',
'price', 4, 'decimal(10,2)', 0, 0, 0, 'Current retail price.', NULL),
('Ecommerce', 'inventory', 'products', 'TABLE',
'stock_quantity', 5, 'int', 0, 0, 0, 'Current inventory count.', NULL),
-- categories table
('Ecommerce', 'inventory', 'categories', 'TABLE',
'category_id', 1, 'int', 0, 1, 0, 'Unique category identifier.', NULL),
('Ecommerce', 'inventory', 'categories', 'TABLE',
'name', 2, 'varchar(100)', 0, 0, 0, 'Category display name.', NULL),
------------------------------
-- DATABASE 3: Analytics_DW --
------------------------------
-- dim_customer (SCD Type 2)
('Analytics_DW', 'dim', 'dim_customer', 'TABLE',
'customer_key', 1, 'int', 0, 1, 0, 'Surrogate key for dimension.', NULL),
('Analytics_DW', 'dim', 'dim_customer', 'TABLE',
'customer_id', 2, 'int', 0, 0, 0, 'Business key from source system.', NULL),
('Analytics_DW', 'dim', 'dim_customer', 'TABLE',
'email', 3, 'varchar(255)', 0, 0, 0, 'Customer email.', 'PII'),
('Analytics_DW', 'dim', 'dim_customer', 'TABLE',
'full_name', 4, 'varchar(200)', 0, 0, 0, 'Customer name.', 'PII'),
('Analytics_DW', 'dim', 'dim_customer', 'TABLE',
'effective_date', 5, 'date', 0, 0, 0, 'SCD2 effective date.', NULL),
('Analytics_DW', 'dim', 'dim_customer', 'TABLE',
'expiry_date', 6, 'date', 1, 0, 0, 'SCD2 expiry date (NULL if current).', NULL),
('Analytics_DW', 'dim', 'dim_customer', 'TABLE',
'is_current', 7, 'bit', 0, 0, 0, 'Flag indicating current record.', NULL),
-- dim_product
('Analytics_DW', 'dim', 'dim_product', 'TABLE',
'product_key', 1, 'int', 0, 1, 0, 'Surrogate key for dimension.', NULL),
('Analytics_DW', 'dim', 'dim_product', 'TABLE',
'product_id', 2, 'int', 0, 0, 0, 'Business key from source.', NULL),
('Analytics_DW', 'dim', 'dim_product', 'TABLE',
'product_name', 3, 'varchar(200)', 0, 0, 0, 'Product name.', NULL),
('Analytics_DW', 'dim', 'dim_product', 'TABLE',
'category_name', 4, 'varchar(100)', 0, 0, 0, 'Denormalized category name.', NULL),
('Analytics_DW', 'dim', 'dim_product', 'TABLE',
'current_price', 5, 'decimal(10,2)', 0, 0, 0, 'Current product price.', NULL),
-- dim_date
('Analytics_DW', 'dim', 'dim_date', 'TABLE',
'date_key', 1, 'int', 0, 0, 0, 'Date key in YYYYMMDD format.', NULL),
('Analytics_DW', 'dim', 'dim_date', 'TABLE',
'full_date', 2, 'date', 0, 0, 0, 'Full date value.', NULL),
('Analytics_DW', 'dim', 'dim_date', 'TABLE',
'year', 3, 'int', 0, 0, 0, 'Calendar year.', NULL),
('Analytics_DW', 'dim', 'dim_date', 'TABLE',
'quarter', 4, 'int', 0, 0, 0, 'Calendar quarter (1-4).', NULL),
('Analytics_DW', 'dim', 'dim_date', 'TABLE',
'month', 5, 'int', 0, 0, 0, 'Calendar month (1-12).', NULL),
('Analytics_DW', 'dim', 'dim_date', 'TABLE',
'month_name', 6, 'varchar(20)', 0, 0, 0, 'Month name.', NULL),
-- fact_sales
('Analytics_DW', 'fact', 'fact_sales', 'TABLE',
'sales_key', 1, 'bigint', 0, 1, 0, 'Surrogate key for fact.', NULL),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE',
'date_key', 2, 'int', 0, 0, 0, 'FK to dim_date.', NULL),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE',
'customer_key', 3, 'int', 0, 0, 0, 'FK to dim_customer.', NULL),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE',
'product_key', 4, 'int', 0, 0, 0, 'FK to dim_product.', NULL),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE',
'order_id', 5, 'int', 0, 0, 0, 'Degenerate dimension - source order ID.', NULL),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE',
'quantity', 6, 'int', 0, 0, 0, 'Quantity sold.', NULL),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE',
'unit_price', 7, 'decimal(10,2)', 0, 0, 0, 'Price per unit.', NULL),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE',
'total_amount', 8, 'decimal(12,2)', 0, 0, 0, 'Line total (quantity * unit_price).', NULL),
-- stg_orders (staging)
('Analytics_DW', 'staging', 'stg_orders', 'TABLE',
'order_id', 1, 'int', 0, 0, 0, 'Source order ID.', NULL),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE',
'customer_id', 2, 'int', 0, 0, 0, 'Source customer ID.', NULL),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE',
'order_date', 3, 'datetime', 0, 0, 0, 'Order date from source.', NULL),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE',
'product_id', 4, 'int', 0, 0, 0, 'Source product ID.', NULL),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE',
'quantity', 5, 'int', 0, 0, 0, 'Quantity from source.', NULL),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE',
'unit_price', 6, 'decimal(10,2)', 0, 0, 0, 'Price from source.', NULL),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE',
'load_date', 7, 'datetime', 0, 0, 0, 'ETL load timestamp.', NULL),
-- rpt_daily_sales (VIEW)
('Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW',
'sale_date', 1, 'date', 0, 0, 0, 'Aggregated sale date.', NULL),
('Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW',
'total_orders', 2, 'int', 0, 0, 0, 'Count of orders per day.', NULL),
('Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW',
'total_quantity', 3, 'int', 0, 0, 0, 'Sum of quantities per day.', NULL),
('Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW',
'total_revenue', 4, 'decimal(12,2)', 0, 0, 0, 'Sum of revenue per day.', NULL),
-- vw_order_summary (VIEW in Ecommerce)
('Ecommerce', 'reports', 'vw_order_summary', 'VIEW',
'customer_id', 1, 'int', 0, 0, 0, 'Customer identifier.', NULL),
('Ecommerce', 'reports', 'vw_order_summary', 'VIEW',
'customer_name', 2, 'varchar(200)', 0, 0, 0, 'Customer full name.', NULL),
('Ecommerce', 'reports', 'vw_order_summary', 'VIEW',
'total_orders', 3, 'int', 0, 0, 0, 'Count of customer orders.', NULL),
('Ecommerce', 'reports', 'vw_order_summary', 'VIEW',
'total_spent', 4, 'decimal(12,2)', 0, 0, 0, 'Sum of order amounts.', NULL);
-- Columns for Reporting_Catalog (DATASET, DASHBOARD, VISUALIZATION)
-- Use object_external_id to match parent objects that have external_id
INSERT INTO import_columns
(database_name, object_external_id, object_schema, object_name, object_object_type,
column_name, ordinal_position, datatype, nullable, is_identity, is_computed, description, field1)
VALUES
---------------------------------
-- DATABASE 4: Reporting_Catalog
---------------------------------
-- daily_sales_data (DATASET)
('Reporting_Catalog', 'ds-001', 'datasets', 'daily_sales_data', 'DATASET',
'sale_date', 1, 'date', 0, 0, 0, 'Sale date from warehouse view.', NULL),
('Reporting_Catalog', 'ds-001', 'datasets', 'daily_sales_data', 'DATASET',
'total_orders', 2, 'int', 0, 0, 0, 'Count of orders per day.', NULL),
('Reporting_Catalog', 'ds-001', 'datasets', 'daily_sales_data', 'DATASET',
'total_quantity', 3, 'int', 0, 0, 0, 'Sum of quantities per day.', NULL),
('Reporting_Catalog', 'ds-001', 'datasets', 'daily_sales_data', 'DATASET',
'total_revenue', 4, 'decimal(12,2)', 0, 0, 0, 'Sum of revenue per day.', NULL),
-- executive_sales (DASHBOARD)
('Reporting_Catalog', 'dash-001', 'dashboards', 'executive_sales', 'DASHBOARD',
'total_revenue', 1, 'decimal(12,2)', 0, 0, 0, 'KPI: Total revenue display.', NULL),
('Reporting_Catalog', 'dash-001', 'dashboards', 'executive_sales', 'DASHBOARD',
'total_orders', 2, 'int', 0, 0, 0, 'KPI: Total orders display.', NULL),
('Reporting_Catalog', 'dash-001', 'dashboards', 'executive_sales', 'DASHBOARD',
'report_date', 3, 'date', 0, 0, 0, 'Report filter date.', NULL),
-- executive_sales_revenue_tile (VISUALIZATION)
('Reporting_Catalog', 'vis-010', 'dashboards', 'executive_sales_revenue_tile', 'VISUALIZATION',
'month', 1, 'date', 0, 0, 0, 'Month period for trend.', NULL),
('Reporting_Catalog', 'vis-010', 'dashboards', 'executive_sales_revenue_tile', 'VISUALIZATION',
'revenue', 2, 'decimal(12,2)', 0, 0, 0, 'Monthly revenue value.', NULL);
Step 4: Import primary and unique keys
Define primary keys (PK) and unique keys (UK) for tables. See import_tables_keys_columns reference for all available columns.
INSERT INTO import_tables_keys_columns
(database_name, table_schema, table_name, table_object_type,
key_name, key_type, disabled, column_name, column_order, description)
VALUES
----------------------------
-- DATABASE 1: CRM_Source --
----------------------------
('CRM_Source', 'crm', 'contacts', 'TABLE',
'PK_contacts', 'PK', 0, 'contact_id', 1, 'Primary key'),
('CRM_Source', 'crm', 'accounts', 'TABLE',
'PK_accounts', 'PK', 0, 'account_id', 1, 'Primary key'),
('CRM_Source', 'crm', 'contacts', 'TABLE',
'UK_contacts_email', 'UK', 0, 'email_address', 1, 'Email must be unique'),
('CRM_Source', 'crm', 'interactions', 'TABLE',
'PK_interactions', 'PK', 0, 'interaction_id', 1, 'Primary key'),
--------------------------
-- DATABASE 2: Ecommerce --
--------------------------
('Ecommerce', 'sales', 'customers', 'TABLE',
'PK_customers', 'PK', 0, 'customer_id', 1, 'Primary key'),
('Ecommerce', 'sales', 'orders', 'TABLE',
'PK_orders', 'PK', 0, 'order_id', 1, 'Primary key'),
('Ecommerce', 'sales', 'order_items', 'TABLE',
'PK_order_items', 'PK', 0, 'order_item_id', 1, 'Primary key'),
('Ecommerce', 'inventory', 'products', 'TABLE',
'PK_products', 'PK', 0, 'product_id', 1, 'Primary key'),
('Ecommerce', 'inventory', 'categories', 'TABLE',
'PK_categories', 'PK', 0, 'category_id', 1, 'Primary key'),
('Ecommerce', 'sales', 'customers', 'TABLE',
'UK_customers_email', 'UK', 0, 'email', 1, 'Email must be unique'),
------------------------------
-- DATABASE 3: Analytics_DW --
------------------------------
('Analytics_DW', 'dim', 'dim_customer', 'TABLE',
'PK_dim_customer', 'PK', 0, 'customer_key', 1, 'Surrogate primary key'),
('Analytics_DW', 'dim', 'dim_product', 'TABLE',
'PK_dim_product', 'PK', 0, 'product_key', 1, 'Surrogate primary key'),
('Analytics_DW', 'dim', 'dim_date', 'TABLE',
'PK_dim_date', 'PK', 0, 'date_key', 1, 'Primary key'),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE',
'PK_fact_sales', 'PK', 0, 'sales_key', 1, 'Surrogate primary key');
Step 5: Import foreign keys
Define relationships between tables. See import_tables_foreign_keys_columns reference for all available columns.
INSERT INTO import_tables_foreign_keys_columns
(database_name,
foreign_table_schema, foreign_table_name, foreign_table_object_type,
primary_table_schema, primary_table_name, primary_table_object_type,
foreign_column_name, primary_column_name,
column_pair_order, key_name, description)
VALUES
----------------------------
-- DATABASE 1: CRM_Source --
----------------------------
('CRM_Source',
'crm', 'contacts', 'TABLE',
'crm', 'accounts', 'TABLE',
'account_id', 'account_id',
1, 'FK_contacts_accounts', 'Links contact to company account'),
('CRM_Source',
'crm', 'interactions', 'TABLE',
'crm', 'contacts', 'TABLE',
'contact_id', 'contact_id',
1, 'FK_interactions_contacts', 'Links interaction to contact'),
--------------------------
-- DATABASE 2: Ecommerce --
--------------------------
('Ecommerce',
'sales', 'orders', 'TABLE',
'sales', 'customers', 'TABLE',
'customer_id', 'customer_id',
1, 'FK_orders_customers', 'Links order to customer'),
('Ecommerce',
'sales', 'order_items', 'TABLE',
'sales', 'orders', 'TABLE',
'order_id', 'order_id',
1, 'FK_order_items_orders', 'Links line item to order'),
('Ecommerce',
'sales', 'order_items', 'TABLE',
'inventory', 'products', 'TABLE',
'product_id', 'product_id',
1, 'FK_order_items_products', 'Links line item to product'),
('Ecommerce',
'inventory', 'products', 'TABLE',
'inventory', 'categories', 'TABLE',
'category_id', 'category_id',
1, 'FK_products_categories', 'Links product to category'),
------------------------------
-- DATABASE 3: Analytics_DW --
------------------------------
('Analytics_DW',
'fact', 'fact_sales', 'TABLE',
'dim', 'dim_date', 'TABLE',
'date_key', 'date_key',
1, 'FK_fact_sales_date', 'Links fact to date dimension'),
('Analytics_DW',
'fact', 'fact_sales', 'TABLE',
'dim', 'dim_customer', 'TABLE',
'customer_key', 'customer_key',
1, 'FK_fact_sales_customer', 'Links fact to customer dimension'),
('Analytics_DW',
'fact', 'fact_sales', 'TABLE',
'dim', 'dim_product', 'TABLE',
'product_key', 'product_key',
1, 'FK_fact_sales_product', 'Links fact to product dimension');
Step 6: Import procedures and parameters
Document stored procedures/functions and their parameters. See Parameter modes for valid parameter_mode values.
INSERT INTO import_procedures
(database_name, procedure_schema, procedure_name,
object_type, object_subtype, function_type, language, definition, description, field1)
VALUES
------------------------------
-- DATABASE 3: Analytics_DW --
------------------------------
('Analytics_DW', 'etl', 'sp_load_dim_customer',
'PROCEDURE', 'PROCEDURE', NULL, 'T-SQL',
'CREATE PROCEDURE [etl].[sp_load_dim_customer]
@batch_date DATETIME,
@rows_affected INT OUTPUT
AS
BEGIN
-- SCD Type 2 logic for customer dimension
MERGE [dim].[dim_customer] AS target
USING [staging].[stg_customers] AS source
ON target.customer_id = source.customer_id
WHEN MATCHED AND target.is_current = 1
AND (target.email <> source.email OR target.full_name <> source.full_name)
THEN UPDATE SET expiry_date = @batch_date, is_current = 0
WHEN NOT MATCHED
THEN INSERT (customer_id, email, full_name, effective_date, is_current)
VALUES (source.customer_id, source.email, source.full_name, @batch_date, 1);
SET @rows_affected = @@ROWCOUNT;
END',
'Loads customer dimension using SCD Type 2 pattern.',
'ETL'),
('Analytics_DW', 'etl', 'sp_load_fact_sales',
'PROCEDURE', 'PROCEDURE', NULL, 'T-SQL',
NULL,
'Loads sales fact from staging with dimension key lookups.',
'ETL');
INSERT INTO import_parameters
(database_name, object_schema, object_name, object_object_type,
parameter_name, ordinal_position, parameter_mode, datatype, description)
VALUES
('Analytics_DW', 'etl', 'sp_load_dim_customer', 'PROCEDURE',
'@batch_date', 1, 'IN', 'datetime', 'Date for SCD effective/expiry tracking.'),
('Analytics_DW', 'etl', 'sp_load_dim_customer', 'PROCEDURE',
'@rows_affected', 2, 'OUT', 'int', 'Returns count of rows inserted or updated.'),
('Analytics_DW', 'etl', 'sp_load_fact_sales', 'PROCEDURE',
'@start_date', 1, 'IN', 'datetime', 'Start of date range to load.'),
('Analytics_DW', 'etl', 'sp_load_fact_sales', 'PROCEDURE',
'@end_date', 2, 'IN', 'datetime', 'End of date range to load.');
Step 7: Import triggers
Document table triggers with their timing and event flags. See Trigger types for valid trigger_type values.
INSERT INTO import_triggers
(database_name, table_schema, table_name, table_object_type,
trigger_name, trigger_type, definition,
before, after, instead_of, on_insert, on_update, on_delete, disabled, description)
VALUES
('Ecommerce', 'sales', 'orders', 'TABLE',
'trg_orders_status_change', 'TRIGGER',
'CREATE TRIGGER [sales].[trg_orders_status_change]
ON [sales].[orders]
AFTER UPDATE
AS
BEGIN
IF UPDATE(status)
BEGIN
INSERT INTO [audit].[order_status_log] (order_id, old_status, new_status, changed_at)
SELECT i.order_id, d.status, i.status, GETDATE()
FROM inserted i
INNER JOIN deleted d ON i.order_id = d.order_id
WHERE i.status <> d.status;
END
END',
0, 1, 0, 0, 1, 0, 0, 'Logs order status changes to audit table.');
Step 8: Import reports and datasets
For the Interface Tables Reporting connector, add datasets to import_tables and reports to import_reports.
Object type placement:
DATASET→import_tables(withobject_type = 'DATASET')REPORT,DASHBOARD,VISUALIZATION→import_reports
See BI objects reference for all valid subtypes.
-- Dataset (must be in import_tables with object_type = 'DATASET')
INSERT INTO import_tables
(database_name, table_schema, table_name, object_type, object_subtype,
external_id, definition, description, field1)
VALUES
('Reporting_Catalog', 'datasets', 'daily_sales_data', 'DATASET', 'DATASET',
'ds-001',
'SELECT sale_date, total_orders, total_quantity, total_revenue FROM Analytics_DW.reports.rpt_daily_sales',
'Dataset connecting to Analytics_DW daily sales view. Used by executive dashboard.',
'Production');
-- Reports and dashboards (must be in import_reports)
INSERT INTO import_reports
(database_name, report_schema, report_name, object_type, object_subtype,
url, embed_url, description, location, language, dbms_created, dbms_last_modified,
external_id, folder_external_id,
parent_object_schema, parent_object_name, parent_object_type, parent_object_external_id,
field1)
VALUES
-- Dashboard
('Reporting_Catalog', 'dashboards', 'executive_sales', 'DASHBOARD', 'DASHBOARD',
'https://bi.example.com/reports/executive-sales',
'https://bi.example.com/embed/executive-sales',
'Executive sales dashboard used by leadership. Powered by daily_sales_data dataset.',
'Business KPIs/Executive', 'SQL', '2026-01-01', '2026-01-20',
'dash-001', 'folder-01',
NULL, NULL, NULL, NULL, 'Production'),
-- Visualization (child of dashboard)
('Reporting_Catalog', 'dashboards', 'executive_sales_revenue_tile', 'VISUALIZATION', 'VISUALIZATION',
'https://bi.example.com/reports/executive-sales#revenue',
'https://bi.example.com/embed/executive-sales?tile=revenue',
'Revenue trend tile used inside the executive dashboard.',
'Business KPIs/Executive', 'SQL', '2026-01-01', '2026-01-20',
'vis-010', 'folder-01',
'dashboards', 'executive_sales', 'DASHBOARD', 'dash-001', 'Production');
Step 9: Import cross-database lineage
This is where interface tables shine — showing data flows across multiple systems. See Data lineage for the lineage model and matching rules.
INSERT INTO import_data_lineage
(source_database_name, source_object_schema, source_object_name, source_object_type, source_column_name,
processor_database_name, processor_object_schema, processor_object_name, processor_object_type, processor_process_name,
target_database_name, target_object_schema, target_object_name, target_object_type, target_column_name,
transformation, description, creation_date)
VALUES
-----------------------------------------------
-- FLOW 1: CRM_Source -> Ecommerce (Customer Sync)
-----------------------------------------------
('CRM_Source', 'crm', 'contacts', 'TABLE', 'contact_id',
'Ecommerce', 'sales', 'customers', 'TABLE', 'CRM Customer Sync',
'Ecommerce', 'sales', 'customers', 'TABLE', 'crm_contact_id',
NULL, 'CRM contact ID stored as reference', GETDATE()),
('CRM_Source', 'crm', 'contacts', 'TABLE', 'email_address',
'Ecommerce', 'sales', 'customers', 'TABLE', 'CRM Customer Sync',
'Ecommerce', 'sales', 'customers', 'TABLE', 'email',
'LOWER(TRIM(email_address))', 'Email normalized to lowercase', GETDATE()),
('CRM_Source', 'crm', 'contacts', 'TABLE', 'first_name',
'Ecommerce', 'sales', 'customers', 'TABLE', 'CRM Customer Sync',
'Ecommerce', 'sales', 'customers', 'TABLE', 'full_name',
'CONCAT(first_name, '' '', last_name)', 'Combined into full name', GETDATE()),
('CRM_Source', 'crm', 'contacts', 'TABLE', 'last_name',
'Ecommerce', 'sales', 'customers', 'TABLE', 'CRM Customer Sync',
'Ecommerce', 'sales', 'customers', 'TABLE', 'full_name',
'CONCAT(first_name, '' '', last_name)', 'Combined into full name', GETDATE()),
-----------------------------------------------
-- FLOW 1b: CRM_Source.accounts -> Ecommerce.orders (B2B)
-----------------------------------------------
('CRM_Source', 'crm', 'accounts', 'TABLE', 'account_id',
'Ecommerce', 'sales', 'orders', 'TABLE', 'B2B Account Sync',
'Ecommerce', 'sales', 'orders', 'TABLE', 'crm_account_id',
NULL, 'Links B2B orders to CRM company accounts', GETDATE()),
('CRM_Source', 'crm', 'accounts', 'TABLE', 'company_name',
'Ecommerce', 'sales', 'orders', 'TABLE', 'B2B Account Sync',
'Ecommerce', 'sales', 'orders', 'TABLE', 'status',
'CASE WHEN company_name IS NOT NULL THEN ''b2b'' ELSE ''b2c'' END',
'Account presence determines B2B order flag', GETDATE()),
-----------------------------------------------
-- FLOW 1c: CRM_Source.interactions -> Ecommerce.orders
-----------------------------------------------
('CRM_Source', 'crm', 'interactions', 'TABLE', 'interaction_id',
'Ecommerce', 'sales', 'orders', 'TABLE', 'Interaction to Order Conversion',
'Ecommerce', 'sales', 'orders', 'TABLE', 'source_interaction_id',
NULL, 'Tracks which CRM interaction led to the order', GETDATE()),
('CRM_Source', 'crm', 'interactions', 'TABLE', 'interaction_date',
'Ecommerce', 'sales', 'orders', 'TABLE', 'Interaction to Order Conversion',
'Ecommerce', 'sales', 'orders', 'TABLE', 'order_date',
'DATEADD(day, 1, interaction_date)', 'Order typically placed day after interaction', GETDATE()),
('CRM_Source', 'crm', 'interactions', 'TABLE', 'contact_id',
'Ecommerce', 'sales', 'orders', 'TABLE', 'Interaction to Order Conversion',
'Ecommerce', 'sales', 'orders', 'TABLE', 'customer_id',
'LOOKUP(customers.customer_id WHERE crm_contact_id = contact_id)',
'Resolves CRM contact to Ecommerce customer', GETDATE()),
-----------------------------------------------
-- FLOW 2: Ecommerce -> Analytics_DW (ETL Pipeline)
-----------------------------------------------
-- Step 2a: Ecommerce -> Staging
('Ecommerce', 'sales', 'orders', 'TABLE', 'order_id',
'Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'Extract to Staging',
'Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'order_id',
NULL, 'Direct extract to staging', GETDATE()),
('Ecommerce', 'sales', 'orders', 'TABLE', 'customer_id',
'Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'Extract to Staging',
'Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'customer_id',
NULL, 'Direct extract to staging', GETDATE()),
('Ecommerce', 'sales', 'orders', 'TABLE', 'order_date',
'Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'Extract to Staging',
'Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'order_date',
NULL, 'Direct extract to staging', GETDATE()),
-- Step 2b: Ecommerce -> dim_customer (dimension load)
('Ecommerce', 'sales', 'customers', 'TABLE', 'customer_id',
'Analytics_DW', 'dim', 'dim_customer', 'TABLE', 'Load Customer Dimension',
'Analytics_DW', 'dim', 'dim_customer', 'TABLE', 'customer_id',
NULL, 'Business key', GETDATE()),
('Ecommerce', 'sales', 'customers', 'TABLE', 'email',
'Analytics_DW', 'dim', 'dim_customer', 'TABLE', 'Load Customer Dimension',
'Analytics_DW', 'dim', 'dim_customer', 'TABLE', 'email',
NULL, 'Direct mapping', GETDATE()),
('Ecommerce', 'sales', 'customers', 'TABLE', 'full_name',
'Analytics_DW', 'dim', 'dim_customer', 'TABLE', 'Load Customer Dimension',
'Analytics_DW', 'dim', 'dim_customer', 'TABLE', 'full_name',
NULL, 'Direct mapping', GETDATE()),
-- Step 2c: Ecommerce -> dim_product (dimension load)
('Ecommerce', 'inventory', 'products', 'TABLE', 'product_id',
'Analytics_DW', 'dim', 'dim_product', 'TABLE', 'Load Product Dimension',
'Analytics_DW', 'dim', 'dim_product', 'TABLE', 'product_id',
NULL, 'Business key', GETDATE()),
('Ecommerce', 'inventory', 'products', 'TABLE', 'name',
'Analytics_DW', 'dim', 'dim_product', 'TABLE', 'Load Product Dimension',
'Analytics_DW', 'dim', 'dim_product', 'TABLE', 'product_name',
NULL, 'Renamed column', GETDATE()),
('Ecommerce', 'inventory', 'categories', 'TABLE', 'name',
'Analytics_DW', 'dim', 'dim_product', 'TABLE', 'Load Product Dimension',
'Analytics_DW', 'dim', 'dim_product', 'TABLE', 'category_name',
NULL, 'Denormalized from categories table', GETDATE()),
-----------------------------------------------
-- FLOW 3: Staging -> Fact (within Analytics_DW)
-----------------------------------------------
('Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'order_date',
'Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'Load Fact Sales',
'Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'date_key',
'CONVERT(INT, FORMAT(order_date, ''yyyyMMdd''))', 'Converted to date key', GETDATE()),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'customer_id',
'Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'Load Fact Sales',
'Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'customer_key',
'LOOKUP(dim_customer.customer_key)', 'Surrogate key lookup', GETDATE()),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'quantity',
'Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'Load Fact Sales',
'Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'quantity',
NULL, 'Direct mapping', GETDATE()),
('Analytics_DW', 'staging', 'stg_orders', 'TABLE', 'unit_price',
'Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'Load Fact Sales',
'Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'unit_price',
NULL, 'Direct mapping', GETDATE()),
-----------------------------------------------
-- FLOW 4: Fact -> Report View (within Analytics_DW)
-----------------------------------------------
('Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'date_key',
'Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'Build Daily Sales Report',
'Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'sale_date',
'd.full_date', 'Joined with dim_date to get full date', GETDATE()),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'order_id',
'Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'Build Daily Sales Report',
'Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'total_orders',
'COUNT(DISTINCT order_id)', 'Aggregated order count', GETDATE()),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'quantity',
'Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'Build Daily Sales Report',
'Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'total_quantity',
'SUM(quantity)', 'Aggregated quantity', GETDATE()),
('Analytics_DW', 'fact', 'fact_sales', 'TABLE', 'total_amount',
'Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'Build Daily Sales Report',
'Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'total_revenue',
'SUM(total_amount)', 'Aggregated revenue', GETDATE());
Lineage to objects with external_id
caution
Important: When target objects have external_id set (like DATASET, DASHBOARD, VISUALIZATION), the lineage must include the external_id columns. Schema+name matching alone will fail.
-- FLOW 5: Warehouse view -> Dataset -> Dashboard -> Visualization
-- These objects have external_id, so we must include external_id columns in lineage
-- Step 5a: rpt_daily_sales -> daily_sales_data (DATASET has external_id = 'ds-001')
INSERT INTO import_data_lineage
(source_database_name, source_object_schema, source_object_name, source_object_type, source_column_name,
processor_database_name, processor_object_schema, processor_object_name,
processor_object_external_id, processor_object_type, processor_process_name,
target_database_name, target_object_schema, target_object_name,
target_object_external_id, target_object_type, target_column_name,
description, creation_date)
VALUES
('Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'sale_date',
'Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'Ingest Sales Data',
'Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'sale_date',
'Dataset ingests sale_date from warehouse view', GETDATE()),
('Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'total_orders',
'Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'Ingest Sales Data',
'Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'total_orders',
'Dataset ingests total_orders from warehouse view', GETDATE()),
('Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'total_quantity',
'Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'Ingest Sales Data',
'Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'total_quantity',
'Dataset ingests total_quantity from warehouse view', GETDATE()),
('Analytics_DW', 'reports', 'rpt_daily_sales', 'VIEW', 'total_revenue',
'Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'Ingest Sales Data',
'Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'total_revenue',
'Dataset ingests total_revenue from warehouse view', GETDATE());
-- Step 5b: daily_sales_data -> executive_sales (DASHBOARD has external_id = 'dash-001')
INSERT INTO import_data_lineage
(source_database_name, source_object_schema, source_object_name,
source_object_external_id, source_object_type, source_column_name,
processor_database_name, processor_object_schema, processor_object_name,
processor_object_external_id, processor_object_type, processor_process_name,
target_database_name, target_object_schema, target_object_name,
target_object_external_id, target_object_type, target_column_name,
description, creation_date)
VALUES
('Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'total_revenue',
'Reporting_Catalog', 'dashboards', 'executive_sales',
'dash-001', 'DASHBOARD', 'Render Executive Dashboard',
'Reporting_Catalog', 'dashboards', 'executive_sales',
'dash-001', 'DASHBOARD', 'total_revenue',
'Dashboard displays total_revenue from dataset', GETDATE()),
('Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'total_orders',
'Reporting_Catalog', 'dashboards', 'executive_sales',
'dash-001', 'DASHBOARD', 'Render Executive Dashboard',
'Reporting_Catalog', 'dashboards', 'executive_sales',
'dash-001', 'DASHBOARD', 'total_orders',
'Dashboard displays total_orders from dataset', GETDATE()),
('Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'sale_date',
'Reporting_Catalog', 'dashboards', 'executive_sales',
'dash-001', 'DASHBOARD', 'Render Executive Dashboard',
'Reporting_Catalog', 'dashboards', 'executive_sales',
'dash-001', 'DASHBOARD', 'report_date',
'Dashboard uses sale_date as report_date filter', GETDATE());
-- Step 5c: daily_sales_data -> executive_sales_revenue_tile (VISUALIZATION has external_id = 'vis-010')
INSERT INTO import_data_lineage
(source_database_name, source_object_schema, source_object_name,
source_object_external_id, source_object_type, source_column_name,
processor_database_name, processor_object_schema, processor_object_name,
processor_object_external_id, processor_object_type, processor_process_name,
target_database_name, target_object_schema, target_object_name,
target_object_external_id, target_object_type, target_column_name,
transformation, description, creation_date)
VALUES
('Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'sale_date',
'Reporting_Catalog', 'dashboards', 'executive_sales_revenue_tile',
'vis-010', 'VISUALIZATION', 'Render Revenue Tile',
'Reporting_Catalog', 'dashboards', 'executive_sales_revenue_tile',
'vis-010', 'VISUALIZATION', 'month',
'DATETRUNC(month, sale_date)', 'Visualization aggregates by month', GETDATE()),
('Reporting_Catalog', 'datasets', 'daily_sales_data',
'ds-001', 'DATASET', 'total_revenue',
'Reporting_Catalog', 'dashboards', 'executive_sales_revenue_tile',
'vis-010', 'VISUALIZATION', 'Render Revenue Tile',
'Reporting_Catalog', 'dashboards', 'executive_sales_revenue_tile',
'vis-010', 'VISUALIZATION', 'revenue',
'SUM(total_revenue)', 'Visualization sums revenue by month', GETDATE());
Step 10: Run the import
After populating the interface tables, run the import in Dataedo. See Running the import for detailed instructions.
- For CRM_Source, Ecommerce, and Analytics_DW: Use the Interface Tables connector
- For Reporting_Catalog: Use the Interface Tables Reporting connector
info
Lineage is created during the import of the processor's database_name. Import all databases to see the complete lineage graph.
Key takeaways
- database_name groups objects into one documentation entry - use consistent names across related lineage
- Processor is required for lineage - if no separate transformation exists, use the target as the processor
- Cross-database lineage connects separate documentation entries - sources and targets can reference any
database_name - external_id is critical for lineage - when objects have
external_id, lineage must include theexternal_idcolumns (schema+name matching fails) - Custom fields (
field1, etc.) can store metadata like PII classification or environment status - Delete order matters - respect foreign key constraints when clearing data
- Import order matters - import databases that contain processors to materialize lineage
Next steps
- Review Interface Tables for detailed column references
- Check Dataedo Repository Schema for complete table definitions
